Rajendra Gupta
Highlight the changes in the script

SQL Server Schema Compare extension in Azure Data Studio

December 5, 2019 by

This article explores the SQL Server Schema Compare extension in the Azure Data Studio.

Introduction

We might have different copies of a database in different environments such as production, QA, development, etc. Sometimes we get a requirement to compare these databases for metadata such as tables, views, stored procedures, indexes and share the report with development teams. It helps to synchronize these databases and test the development codes.

You might think of using third-party tools such as Redgate SQL Compare, Apex SQL compare. We might use complex stored procedures for these schema comparisons.

Overview of Azure Data Studio

Azure Data Studio is a cross-platform tool with many useful extensions to make the DBA and developer life more comfortable. We have covered many such useful features in previous articles. Azure Data Studio provides SQL Server Schema Compare extension for comparison of .dacpac files and databases. It also provides an option to apply any difference from source to a target database.

Before we proceed with this article, you can go ahead and download the November release of Azure Data Studio for the appropriate OS from the link.

Download November release of Azure Data Studio

If you have previous versions of Azure Data Studio installed on your system, I would recommend you upgrade it to November release because it contains the General Availability of Schema Compare and SQL Server .dacpac extensions.

Test environment setup

For the demonstration in this article, let’s create two sample databases with the following script:

  • Create [SourceDB] and [TargetDB] in SQL instance

  • Create a SQL table and stored procedure in both [SourceDB] and [TargetDB]

Install SQL Server Schema Compare extension

Search for Schema Compare extension in the Marketplace. You can see a white star in the upper left corner of extension that shows it is a recommended extension by Azure Data Studio:

Schema Compare extension

Click on the Install button, and it quickly installs the SQL Server Schema Compare. We do not need a restart of Azure Data Studio:

Install Schema Compare extension

Explore Schema Compare extension

We can launch a Schema Compare extension from the database context menu.

Right-click on the source database and then choose Schema Compare:

Launch Schema Compare extension

It launches Schema Compare with a populated source (source database from which we launched wizard) and blank target database:

Homepage of the Schema Compare

We need to right-click on the ellipsis near the target connection:

Target connection

It opens the following Schema Compare window:

Target connection details

Here, you get options to compare databases or data-tier application files (DACPAC). We can compare the schema in the following ways:

Source

Destination

Database

Database

Database

DACPAC file

DACPAC file

DACPAC file

DACPAC file

database

Source (Database) to destination (Database) schema comparison

Let’s compare both source (SourceDB) and destination (TargetDB) database; we created earlier in the article:

Compare Database source to destinaton database

Click OK, and we can see both source and target database details in the below screenshot:

Click on Compare

Click on Compare, and it starts the comparison, as shown below. It might take longer depending upon the database size and objects:

Initializing comparison

We do not see any schema difference between both the databases. Schema Compare also highlights no schema difference:

no schema difference

In the source database, let’s make the following changes:

  • Drop the existing table Employee and stored procedure [USP_EmpName]

  • Create an Employee table with an additional column [EmpLocation]

  • Create a Stored procedure to retrieve this additional column

Again, click on Compare, and you can see the output in Azure Data Studio Schema Compare.

In the following screenshot, we can see it shows the difference in both the table and stored procedure:

Comparison report

Click on the table, and it shows the comparison details as per the following screenshot:

Highlight the changes in the script

Here, we can see that the source table contains [EmpName] and [Country] columns while the target contains [EmpName] columns only.

Explore Schema Compare menu options

Let’s go over to menu options in Schema Compare extension of Azure Data Studio:

Schema Compare menu options

  • Compare: As shown earlier, it is used to compare the source and target specified
  • Stop: Schema comparison might take longer depending upon the schema and objects. We can stop the comparison using this button
  • Generate Script: We can use this option for generating the script of changes that we can directly apply to the target database. We cannot use this option while comparing two DACPAC’s

    Let’s click on Generate script for the comparison we performed between the source and destination database. It generates the script for the targetDB

    Generate Script

    In the problem area, we can see problems with the line number, pointer to the line. We can click on any problem, and it takes you to a problematic line.

    It might make you wonder why Azure Data Studio is generating a problematic code.

    syntax errors

    It is an SQLCMD code, and SQL Server does not recognize it. We can execute this script in SQLCMD mode only. Click on Enable SQLCMD as highlighted below. It changes the option from Enable SQLCMD to Disable SQLCMD:

    Enable SQLCMD

    Once we enable the SQLCMD mode, we do not see any problems for generating script:

    No problems found

    Click on Run, and it executes the script in the target database:

    Execute th script on target database

    After the applied script, both database schemas should be in synchronized status. Go back to Schema Compare and again start the comparison process for validation purposes. In the following screenshot, we can verify that there is no schema difference between the source and target database:

    validate the modified changes

  • Apply: We can directly apply the changes in the target database using this option. Let’s click on it and verify it. Click on Yes in following the prompt dialog

    Apply change to target

    In the Tasks, it gives the message that schema changes are successful

    Apply schema change message

    You can verify it by again generating the comparison report

  • Options: We get multiple configuration options for schema comparison. For example, Block on possible data loss, Drop constraints not in source, ignore permissions, and ignore partitions. We should be careful while making changes in these rules as it might affect the schema comparison in both the databases and can generate inconsistent results

    Schema compare options

  • Switch Direction: It swaps the source and target connections. For example, at present, I have the following source and destination

    Switch direction

    Click on Switch direction. It swaps the connections and performs schema comparison as well. In the following screenshot, we can see swap connections. We do not see any differences in the schema; therefore, it gives a message that no schema differences were found:

    validation

  • Open .scmp\Save .scmp: Schema comparison extension also allows you to save the comparison in a XML format. We can refer to this XML later once required. Click on Save. SCMP first and provide a location with a file name. The XML file looks like as shown below. It contains the version, source, target and the current state of all the options. We can open this .scmp file using the open .scmp option and provide the path of the file

    SCMP file format

Source (Database) to destination (.DACPAC) schema comparison

DACPAC file contains database model including all database objects like table, views, logins, and procedures. It is an abbreviation of the Data-tier Application package. You can read more about DACPAC in Microsoft docs.

Let’s generate a DACPAC file for the target database. Connect to SQL instance in SSMS.

Right-click on the database |Menu |Tasks| Extract Data-tier Application:

Extract Data-tier Application

It opens the extract data-tier application wizard. It automatically takes input for application name, target version. Specify a location for this file and click on Next:

Extract Data-tier Application input

View the summary and click on Next:

Validation and Summary

It creates and saves the DACPAC file in the destination directory:

Build Extract Data-tier Application package

Now go back to Schema Compare in Azure Data Studio and change the target to data-tier application package as shown below:

Schema compare target as DACPAC

It gives a warning message because we want to compare schema from the source database to target a DACPAC file:

Warning message

Click on Yes, and it starts the comparison process. We can see that it gives the comparison result similar to database schema comparison:

Comparision report

Let’s do a few more schema comparison tests. Previously, we compare the database with the same object with a difference in structure.

Now, add a new table in the SourceDB:

Go back to Azure Data Studio, start the Schema Compare wizard again between source and destination database and observe the result.

In the following screenshot, we can see two types of actions:

  • Change: It shows that the object exists in both the source and target database. We need to execute alter statements on the target database for synchronization purposes
  • Add: It shows that a particular object does not exist in the target database. We need to add this object to the target database for synchronization

different actions

Exclude an object in the schema synchronization process

We can exclude a particular object in the synchronization process or generate a script wizard. Remove the check from include column for an object that we want to exclude.

In the following screenshot, we have excluded [Employee] and [USP_EmpName] objects:

exclude object

Let’s generate the script, and in the script, we can see that it includes T-SQL for CREATE TABLE statement only:

validate the script

Backup target database before making changes

It is always advisable to take a database backup before making any changes. It helps us to move back to the current state of the database if required.

We might want the Schema Compare extension to take care of this requirement. Click on Options in Schema Compare and put a check on the Backup Database Before Changes:

Backup target database before making changes

Click OK, and it gives the following warning message because the options have changed:

Click Yes on warning message

Click Yes, and it compares the schema with the modified option. Click Yes for applying the changes in the target database:

Click on Apply

It applies the changes to the target database, but it is not as per our requirement, right?

validate the schema report

We have enabled the option to take a database backup before making changes. We did not get any message or prompt for database backup.

Let’s check database backup history from the MSDB system database using the following query:

We can see a full database backup in the default backup directory.

Note: You should be careful before enabling the backup option before making a change to the database. In the case of the vast database, we might face an issue due to backup drive space. It might also take longer as well for database backup:

Validate full database backup

Conclusion

In this article, we explored the Schema Compare extension in Azure Data Studio. We can compare databases, DACPAC files together using this extension. I find it useful, and you should give a try to compare objects without complex T-SQL or third-party tools.

Rajendra Gupta
2,457 Views