SQL Server data utilities include a Schema Compare application for developing and comparing different databases.
There are many tools available for comparing the structure and data of the production database.
However, if we have SQL Server Data Tools (SSDT), we don’t need any more tools. It functions as a one-stop shop for comparing and synchronizing databases. So, in this article, we will learn all there is to know about SQL server data tools, from installation to usage to comparison and other important details. Let’s begin with the installation.
– Installation
SSDT can be included in the Visual Studio installation. This is also supported by Visual Studio Community Edition, which makes database creation easier for developers.
– Compare the Schemas of Two SQL Databases
The steps for comparing two SQL databases are as follows:
- From Visual Studio, Open Tools 🡪 SQL Server 🡪 New Schema Comparison. It will open a screen for Schema Comparison.
- Click “Select source.” You will notice a pop-up window after clicking on this. There will be three options available in this window.
- SQL Server Database Project can be selected. The current solution should include this project file (.sqlproj).
- Microsoft SQL Server databases can be chosen.
- It is possible to pick a data-tier application file (.dacpac).
3. Select the target schema similarly.
4. To compare SQL databases, click on compare. A comparison result will be displayed. It will display all categories of database objects (Table, View, Stored Procedure, Function, etc.) that are different between the two databases in this section. Results will be displayed by the Group of Action:
- Delete: The items that are currently available in the target database and are not unavailable in the source database are shown here. During the update process, these items will be removed from the target schema.
- Change: Although both databases have the same set of objects, they have distinct schemas. During the update process, certain items in the target schema will be changed.
- Add: Objects that are accessible in the server database but not the target database are used in this procedure. During the update process, these objects will be generated in the target schema.
The Target database will be updated if we check or uncheck the objects.
To apply changes to the target database, click “Update” next.
However, the user can pick a script generation option.
Change and Delete Action objects will also be validated in the same way.
Depending on the action, a disabled checkbox may display with a ticked or unchecked condition.
- If the destination database name is available, enable the Script option. SQL Server Database Project and Dacpac files are not supported.
- Equal objects are not displayed in results by default, but they may be seen by selecting “Show Equal Objects” from the toolbar.
- The default grouping of objects is by Action (Delete/Change/Add). From other choices in the toolbar, it may be changed to Schema or Type (Table, View, Function, etc.).
- A SchemaCompare file can be used to save a comparison (.scmp). When you reopen the file, excluded objects will still be excluded.
– Comparing SQL data between two databases
The steps to compare data from two databases are as follows:
- From Visual Studio, Open Tools 🡪 SQL Server 🡪 New Data Comparison. It will open a new wizard screen for Data Comparison.
- Select the Source Database and Target database.
- Choose Data Comparison Options. (Note- You may compare and synchronize in SQL Server databases using the Data Compare for SQL Server tool. Use SQL Compare to assess your SQL Server databases and created SQL scripts to check that schema syncing is error-free) Records of the following types should be compared:
- Different Records
- Only in Source
- Only in Target
- Identical Records
- The next screen, which lists the Tables & Views that are present in both the tables inside the same schema, may be accessed by clicking the next button. You may choose or deselect the tables and views that will be compared here. Additionally, you may take out some table fields if you don’t want them to be compared. Finish by choosing to start the comparison. The wizard will take into account all options and begin the comparison if we select the Finish option on the first wizard screen.
- It will display the result for all tables/views.
Following the entry of the Table name in the first column, the following columns will display the row count for Different Records, Only in Source, Only in Target, and Identical Records. When we choose a table from the list, it displays comparable data in several tabs of the bottom pane.
First, choose the table name from the list above, and then select the required rows from the bottom pane.
By default, all tables with unique records or at least one entry in the database will be selected. By default, all records except identical records (which cannot be selected) will be selected.
For different records, the main key column(s) will be displayed once, then all columns for the sources and destinations databases will be displayed one by one.
For Example
In the image, the primary key for the Lineage table is Lineage Key, which is displayed just once, followed by Table Name (Source), Table Name (Target), Data Load Completed (Source), Data Load Completed (Target), and so on.
If the data is the same, it is presented in grey, whereas distinct data is displayed in black bold text.
- To finish the database construction, select Finish, then click “Generate Script” or “Update Target” to finish the process.
