Many a times, you would need to identify the difference in database schema for two SQL databases so that you can take certain course of action. There are a lot of tools in market which can do this, but you would need to pay for them to get full difference or to use them on continuous basis. However, Microsoft Visual Studio has this functionality built-in for you and if you happen to use Visual Studio as your code development tool, this functionality is basically free. So in this scenario, it also prevents hassle of learning another tool. In this blog post, we’ll learn how to do the same using Visual Studio.

Caution – I am going to demo this Visual Studio Enterprise. I’m not sure what all sku’s it is associated with, so if you cannot repeat steps below, you might want to check that. 

Create a SQL Database project

First, we need to create a new SQL database project in Visual Studio. For this, go to File -> New Project -> Select SQL server database project:

Once its created, we’ll need to specify the source and target sql server databases for it to compare.

We’ll be using sample sql server database for this post’s purpose. They are officially available at https://github.com/Microsoft/sql-server-samples. However, you can do the steps on sql databases of your choice.

Specify the target SQL database version

Right click on project in Visual Studio, select properties and open Project Settings. Set target SQL server version here:

This step is very important if you not only want to see the results, but also want to auto-generate sql script for the difference.

Specify source and target sql database

Again, right click project and select ‘Schema compare’:

In the schema compare window, select dropdown to the left and then select source:

This will open a pop-up wizard. Select database and then click ‘Select Connection’:

Now go to browse and specify the source sql connection details as per your environment and then click connect:

Click ok again once the wizard closes.

Now, click the dropdown on the right side and then select target:

Again, we need to specify target sql database connection details.

The middle option allows you to switch source and target sql database.

Start schema comparison process

To start comparison process, now click the compare button enabled at top of left drop-down:

Just for reference purposes, one of table named dbo.BuildVersion contains an extra column named BuildDate in our source database(and this is not present in target sql database):

Let’s start comparison process within Visual Studio. It might take some time to finish depending upon your network connection, etc. Once its completed, you should be able to view results like below:

As you can see, it does not seem to of much info. Also, as mentioned earlier, in our case, we have only difference in one of the sql tables.

To get more information about difference, we need to select the object, say, table in our case:

Now, we would be able to view the difference clearly.

Generate SQL schema difference

To auto-generate the sql script that needs to be executed at target sql database to nullify the schema difference, we need to click generate button just right to the compare button.

This will initiate a process and you would be able to see and save the schema difference.

Deploy/Publish schema changes

Besides generating the schema difference, we can also choose to deploy the changes to target SQL database as well. For this, we just need to click the update button just right to the compare button:

This will initiate a process in other window to deploy the schema changes. You would also be able to see the progress for same:

This action is also known as publishing of sql schema. Once its complete, we should be able to see our changes in target sql database as well:

Modifying report generation or deployment options

As you can see, there are other options in toolbar, once the schema comparison is generated, which will allow to select view or group results. There is also a checkbox in front of the each sql object, which can be checked or unchecked to include/remove that object from publishing/reporting.

Automation options

It’s good to be able to do it using GUI or graphical interface. However this is a cumbersome  and time taking process. If you are in DevOps or you need to do above on a frequent basis or at large scale, chances are like that you would like to automate the whole process. In one of the upcoming posts, we would discuss how to generate/publish the schema difference using command line tool as well and automate the same.