Daily Knowledge Drop
Visual Studio has a built in feature called Schema Compare which easily allows for the comparing of two SQL database schemas and the generation of a script to bring them in sync.
In the past I've had to perform database schema comparisons numerous times (admittedly the majority of these times have been in the fairly distant past when proper CI/CD tools and DevOps practices were not in place) - and there were limited tools available. Schema Compare contains the all the features I've required in the past in an incredibly simple to use package.
Installation
The feature is available through the installation of the SQL Server Data Tools Visual Studio component. One can use the Visual Studio Installer to confirm if the component is installed or not:
- Open Visual Studio Installer
- Click Modify on the relevent Visual Studio installation
- Select the Individual components tab
- Search for "sql server data tools"
Once installed, the feature will be available under Tools -> SQL Server -> New Schema Comparison in Visual Studio:
Usage
The usage of the feature is straightforward - when opened you will be presented with two drop down lists, one to select the Source database and one to select the Target database:
A screen with the traditional database connection settings is presented when adding the Source and Target database:
Once the Source and Target databases have been set, the Compare button becomes available - clicking on this will perform the database comparison.
A list of differences between the databases will be presented, drilling down and clicking on the specific item will then show the specific difference.
In the example below, there are differences between the Song tables in the two databases:
- a Length discrepancy on the Artist column
- a Nullability discrepancy on the LengthInSeconds column
Clicking on the Length item, displays the specific difference:
Application
Once the differences have been identified, the next step is bring the Destination database in line with the Source database.
This can be done by updating the target database directly:
This may not always be possible or feasible, so another option is to have a script generated to align the Target to the Source:
The script generated by the tool looks as follows (abbreviated to include only the relevent portion):
--
--
-- some preamble removed for brevity
--
--
USE [$(DatabaseName)];
GO
PRINT N'Altering Table [dbo].[Song]...';
GO
ALTER TABLE [dbo].[Song] ALTER COLUMN [Artist] NVARCHAR (500) NOT NULL;
ALTER TABLE [dbo].[Song] ALTER COLUMN [LengthInSeconds] INT NULL;
GO
PRINT N'Update complete.';
By default all differences are included in the Update or Generate Script but it is possible to uncheck specific differences if you wish to exclude them from the update.
Notes
In a perfect world this feature is never required as DevOps practices ensure that a dev, test and production database are always in sync (or can easily be brought in sync). However in the real world, differences sometimes occur - sometimes its easier to sync a database schema from production to dev instead of creating the dev database from scratch, for example.
In these cases, the Schema Compare feature will prove to be invaluable in tracking down and remedying any differences.
References
How to compare two SQL databases from Visual Studio
Daily Drop 116: 13-07-2022
At the start of 2022 I set myself the goal of learning one new coding related piece of knowledge a day.
It could be anything - some.NET / C# functionality I wasn't aware of, a design practice, a cool new coding technique, or just something I find interesting. It could be something I knew at one point but had forgotten, or something completely new, which I may or may never actually use.
The Daily Drop is a record of these pieces of knowledge - writing about and summarizing them helps re-enforce the information for myself, as well as potentially helps others learn something new as well.On This Page