Investigating schema changes
Published 11 July 2016
The screenshots on this page are from DLM Dashboard 1.5
This page covers how DLM Dashboard displays changes to database schemas, and explains how you can find out more about schema changes:
Once you've set up DLM Dashboard, the home page looks something like this:
These databases have a green schema name, which means that they're unchanged: no schema changes have been made since they were added.
If you want to rename the initial schema, see Renaming a schema below.
See details of schema changes
When a database schema changes, you can find out more about the changes by clicking Review:
On the Review page, you can see:
- the name of the schema state before the change
- a summary of the changes
- a full list of the changes made
- a diff view of each change
- details of who made the changes, when, and using which tool (if DLM Dashboard can detect it)
You can also download the SQL that made the changes.
Name the changed schema
If the change was drift, and you're happy with the new schema, you need to give the schema a version name or number. You can comment on the change, then click Name this schema.
If you're not happy with the drift, go back to the dashboard to keep the database listed as drifted.
Version 1.4.8 and later: renaming a schema
You can rename a named schema on the History page:
- Click the schema history icon for a database the schema has been on.
- On the History page for that database, find the schema you want to rename and click on its name.
- In the text field that appears, type the new schema name.
- Click Save.
If the change was an update, all you need to do is confirm you've seen the change by clicking Acknowledge. This tells DLM Dashboard that you've recognized the change, and means the database is shown as green again.
Limitations of change details
You can't see change details (when they were made, who by, and the SQL that made them) for some changes:
- Creates, drops, and edits of schemas (objects in a schema will, but not the schema itself).
- Creates, drops, and edits of database level extended properties.
- If a role is dropped, change details will be available for the role, but not for any objects which have permissions on them for the role.
- If an object is dropped because the database it's in is dropped, change details won't be available.
- If you run a stored procedure, and it makes changes to a schema, the SQL you can download is the SQL that executes the stored procedure, without the arguments.
- Moving objects between schemas (ALTER SCHEMA … TRANSFER …) is shown as a drop and a create, and only one of these drop and create events will have change details available.
- For large transactions, between 420 and 512 of the items in the transactions are shown, due to trigger rollover.
- Change details are only shown for a maximum of 1024 transactions.
- Changes made in SQL Source Control will sometimes wrongly appear as having been made in SQL Compare, or "Red Gate Software SQL Tools".
DLM Automation integration
DLM Automation integration is supported in DLM Dashboard 1.5.0 and later.
SQL Doc database documentation is supported in DLM Dashboard 1.6.4 and later.
In DLM Automation version 188.8.131.5282 and later, you can configure the build step to send information about the schemas it builds to DLM Dashboard. DLM Dashboard adds these to its list of recognized schemas, with the name <packageId-packageVersion>.
When a monitored database changes to a schema sent from DLM Automation, DLM Dashboard:
- displays the package ID and version below the schema name, and labels this with the DLM Automation icon
- labels the change as an update , rather than as drift
- adds a link to SQL Doc database documentation (if you've configured DLM Automation to include this in the package)
For instructions on how to configure the DLM Automation build step to send schema and documentation information to DLM Dashboard, choose the tool you use:
For help understanding SQL Doc documentation, see What's in the documentation?
Some schema changes won't be displayed:
When you restore a backup, the changes won't appear on the dashboard. This is because a restore isn't a DDL event, so it doesn't fire the DDL trigger that alerts DLM Dashboard to changes.
DLM Dashboard will pick up these changes later, when another change is made to the same database.
Changes to objects including certificates, keys, index names, users, and more
DLM Dashboard ignores some schema changes by default, such as changes to white space or tSQLt. These changes won't affect the status of a database on the dashboard (ie won't indicate update or drift), won't trigger an email notification, and won't appear on the Review page.
For more details, see Changes ignored by default.
You can also ignore changes to objects you're not interested in by adding filters to your pipeline. For more details, see Using filters.