Out of the Shadows: Explore a Database Projects’ Inner Workings using SSDT Schema Compare
Overview
In Jungian psychology, the concept of the shadow is often symbolized as the hidden side of a person's psyche. A codebase is not all that different; it is just as complex in its own way, so it requires exploration and understanding.
By acknowledging and examining those complexities, a developer can form a more complete and balanced understanding of “the shadow” within their solution. SQL Server Data Tools (SSDT) aids in this journey of self-discovery, so to speak.
Schema Compare, a powerful comparison tool available in SSDT, can reveal hidden or lesser-known aspects of a project’s code, such as a "tiny" change to a query or an unknown dependency between environments.
While the utility offers a broad range of benefits – among them, maintaining source control, ensuring consistent deployments, and upholding code standards – a practical use is known to assist in streamlining production support issues.
When a release goes sideways, for example, the core comparison feature produces a line-by-line diffing between lower and live environments. Developers can then use the results to quickly identify the schema modifications that could be causing the issue.
This type of exploration of what lies beneath ultimately reduces risk in the development lifecycles.
With its’ ability to help us learn about our codebase on a deeper level, it is no wonder that Schema Compare can have an almost transformative effect on some of those who use it. Great potential here for learning and improvement, and an inviting journey.
What will you find?
/**/
This article will demonstrate how to create a database schema comparison file in a Visual Studio SQL Server Database project.
Caveats
A few things to know before you go.
Not so bad.
Prerequisites
Basic system requirements:
Not too terrible.
Steps to Compare Schemas in a Visual Studio Project
Comparing schemas follows a general process in Visual Studio.
A Schema Compare tab will appear.
The tab is divided into two major areas: Source on the left, Target on the right.
Source = Development SQL database
Target = Test SQL database
We would like to see the differences between the Development and Test databases, with Development being compared against Test.
4. In the dropdown, click Select Source.
5. Specify the development database or local project in the next dialog.
6. Click Browse to set up a new database connection. Select History to browse existing connections.
7. Click Connect to establish the Source connection.
The dropdown will populate with the selected source.
8. ?Do the same for the Target.
9. Click the wheel icon in the tab. Through this window you can specify the items that you want to include and exclude.
领英推荐
The first tab, General, concern comparison settings. Click Reset to use default settings.
Lots of stuff to unpack here, as they say!
The second tab, Object Types, handles the inclusions/exclusions of the database things to be compared.
Application-scoped objects relate to the project and database itself. Non-application-scoped objects feature administrative and configuration objects are the server-level, so logins, credentials, routes, etc.
It is worth pointing out the commands available in the command bar, which make Schema Compare quite a reliable companion when managing project updates.
10. Click the Compare button once the source and target databases have been selected and the compare options are set.
The comparison may take a minute or two, depending on the size of the database.
The comparison results will be displayed in the same window.
The juice: each item can be accessed to view a line-by-line comparison.
A few items to note:
If Development, for example, contains a table that the Test db does not, a “+” appears. This is a new object.
Reversing the comparison will display a “-“ sign, as the Test environment does not contain the table object, therefore registering it as delete candidate.
At this point, we are ready to update the database. Use caution when clicking the Update button, as this applies the changes from the source database or project to the target database. It is indeed possible that you may make changes to the wrong place.
Before doing that, create backups in case something goes sideways.
11. Click Update once ready.
Allow a few moments for the update to process.
After the process has completed, you do not need to update the database – this operation has done everything for you, unless an error is returned.
12.?Save the Schema Compare file.
A file is created with each schema compare operation. The extension is .scmp. A new file does not need to be recreated every time a comparison needs to happen. Once saved, will be located within the database project within the solution.
All you need to do is open the comparison file within the Solution Explorer, confirm source, target and comparison direction and click Compare.
And that’s it. Compare one database to another, or a project folder to database and reveal how they differ with minimal effort.
/**/
Next Steps and Suggestions
A few suggestions.
Simple and practical. Find what works for you and your situation but always use caution when updating your target.
References