Related Service

Custom Software Development

Know more about our Software Outsourcing Service

Learn More

How to compare two SQL Server Databases using SQL Server Data Tools

Published: Nov 10, 2020

Overview

The new-edge tools have taken over the IT space like a storm. Many tools bring revolutionary changes and one of them is SQL. SQL Server data tools are used to develop and compare different databases. There are many scenarios where an SQL software programmer or database administrator needs to compare two databases. There are many tools available for comparison of schema & data of two databases.

But, if we have SQL Server Data Tools (SSDT), we don’t need any other additional tool. It is like a one-stop-shop. So, in this blog, we will learn everything about SQL server data tools from installation usage to comparison and other essential aspects. Let’s begin with the installation process.

Installation

SSDT can be installed as part of Visual Studio Installation. Visual Studio Community Edition also support this.

Installation
Image: SSDT as part of Visual Studio Installation

Comparing Schemas of Two Databases

Following are steps to compare the schema of two databases:

  1. From Visual Studio, Open Tools 🡪 SQL Server 🡪 New Schema Comparison. It will open a screen for Schema Comparison.

    Schema Comparison Screen
    Image: Schema Comparison Screen
  2. Click on “Select source”. After clicking on this, You will see a pop-up window. This window will have three options to choose from.
    • SQL Server Database Project can be selected. This project file (.sqlproj) should be there in the current solution.
    • Databases from Microsoft SQL Server can be selected.
    • Data-tier application file (.dacpac) can be selected.
  3. Select similarly for target schema.
  4. Click on compare. It will show a comparison result. Here, it will show all types of objects (Table, View, Stored Procedure, Function, etc.) which are not the same in both databases. It will show results by Group of Action:
    • Delete: This displays the objects which are already available in the target database not unavailable in the source database. These objects will be deleted from the target schema in the update process.
    • Change: Objects are available in both databases, but the schema is different in both databases. These objects will be modified in the target schema during the update process.
    • Add: In this process Objects which are available in the server database, but not in the target database. These objects will be created in the target schema during the update process.

We can check/uncheck the objects, those changes will be applied to the Target database.

Next click on “Update” to apply changes to the target database.

Or User can select an option to generate a script.

Schema Comparison Result
Image: Schema Comparison Result

Notes

  • Dependencies must be maintained in Object selection. However, SSDT automatically selects relevant objects.
    For Example
    In AdventureWorks Database, EmployeeDepartmentHistory depends on Department, Employee and Shift tables.
    Now consider that this table exists in the source schema, but not in the target schema.

    1. If you have selected the EmployeeDepartmentHistory table only, those 3 tables will be automatically selected. But, checkboxes of these 3 tables will be disabled.
    2. If all four tables are included in the selection and if you exclude the Employee table, it will disable the checkbox for the Employee table. It means The Employee table is still included. However, once we exclude EmployeeDepartmentHistory, it will enable the checkbox again for the Employee table.

Similar validation will be done for Change or Delete Action objects also.

Disabled checkbox might be shown with indeterminate or checked status based on Action.

Dependencies in object selection
Image: Dependencies in object selection
  • Enable Script option if the target database is available. Not available for SQL Server Database Project or Dacpac file.
  • By default, equal objects are not shown in results, but it can be visible by clicking “Show Equal Objects” from the toolbar.
  • Objects are default grouped by Action (Delete/Change/Add). It can be changed to Schema or Type (Table/View/Function etc.) from other options in the toolbar.
  • Comparison can be saved as a SchemaCompare file (.scmp). Excluded objects will remain excluded while reopening the file.

Compare Data of two databases

Following are steps to compare data of two databases:

  • From Visual Studio, Open Tools 🡪 SQL Server 🡪 New Data Comparison. It will open a new wizard screen for Data Comparison.

    Data Comparison Database selection
    Image: Data Comparison Database selection
  • Select the Source Database & Target database.
  • Select Data Compare Options: Which type of records should be compared:
    1. Different Records
    2. Only in Source
    3. Only in Target
    4. Identical Records
  • By clicking the next button, it will list Tables & Views in the next Screen, which are there in both the tables within the same schema. Here, you can select/unselect tables & views, which will be compared. Also, if you wish to exclude some fields of tables, those fields can be omitted and this will not be compared.Click on Finish to start the comparison.If we choose the Finish option from the first screen from the wizard, it will consider all selections and start the comparison.

    Table View Selection Options
    Image: Table View Selection Options
  • It will show the result for all tables/views.
    Data Comparison Result
    Image: Data Comparison Result

    After the Table name is entered in the first column, the next columns will show Row count for Different Records, Only in Source, Only in Target, Identical Records.Once we select one table from the list, it will show compared data in different tabs of the bottom pane.

    First, we need to select the table name from the above list, and then need to select required rows from the bottom pane.

    By default, all tables will be selected which have unique records or at least one record in the database. All the records except identical records (which cannot be selected) will be selected by default.

    Different records, the primary key column(s) will be displayed a single time, then it will show all columns one by one for source & target database.

    For Example

    In the given Image, For Lineage table, Lineage Key is the primary key, which is displayed only once, then Table Name (Source), Table Name (Target), Data Load Completed (Source), Data Load Completed (Target) so on.

    If data is the same, it is shown in Grey Color, while different data is displayed with Black Bold font.

  • To complete the whole creation of the database click on Finish selection, then click “Generate Script” or “Update Target” to complete the process.

Note: For both the processes mentioned above has default options. Some options can be changed while others can be compared using a comparison file or from Tools 🡪 Options 🡪 SQL Server Tools in Visual Studio. Also, features may vary in different versions/editions of Visual Studio.

Conclusion

Through this blog, we hope, we were able to elucidate users on Microsoft SQL Server Data tools and their comprehensive usage. This step-by-step guide will help you compare the schema, strings & data of two different databases. It is very helpful for SQL developers & database administrators for managing databases in different situations.

Comments

  • Leave a message...

Related Articles

Things you must know before choosing Power BI

Nov 26, 2020

What are ORMs and How does it work?

Nov 24, 2020

Introduction of Azure DevOps Pipelines

Nov 19, 2020