SQL Server Schema Comparison using Azure Data Studio
Introduction:
Schema Comparison is an important step in UAT and Production. This is useful method to compare your UAT and Production Database and Schema without running complex SQL Queries.
This article will help you to understand Azure Data Studio which a Database Management Tool by Microsoft is to manage On-premises SQL Server, SQL Server on Azure VM, Azure SQL Database and Managed Instance. Schema Comparison is feature provided in this toll which helps user to compare database present in On-premises or in Azure Cloud.
Azure Data Studio Compare Table, View, Stored Procedure, Functions, Logins, Users
Prerequisites:
1. Download and install Azure Data Studio — Azure Data Studio | Microsoft Docs
2. User must have “db_owner” role to apply those changes.
3. Schema between the database should be similar/same.
4. SQL Server Authentication is recommended.
Step to Compare Schema:
Step 1: Download and install Azure Data Studio — Azure Data Studio | Microsoft Docs
Step 2: Open the Azure Data Studio Tool -> Click on the Feature Icon
Step 3: Search SQL Server Schema Compare and Click on Install
Step 4: Connect to Azure SQL Server
Step 5: Right on Source database (DB1) and click on Compare Schema
Step 6: Choose target Server but click on ellipsis
Step 7: Connect to Target Server and Provide Credentials -> Click on “OK”
Note — You can compare a Dacpac with database or Dacpac with Dacpac
Step 8: Click on Compare Button to Compare Schema of database
Step 9: You can see the comparison Table of two database.
Step 10: Action column display which action its going to get performed on Destination
Delete — Respected object going to get deleted
Add — Respected object going to get added
Change — Respected changes to be made on object
Step 11: Click on Table with “Change” Action, you can see schema comparison between two tables.
Step 12: Click on “Apply” to reflected these changes on Destination Database.