DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workkloads.

Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • How to Recover a Deleted Table in a SQL Server Database
  • Using AUTHID Parameter in Oracle PL/SQL
  • Restoring the MS SQL Server Database in Easy Steps
  • How To Convert MySQL Database to SQL Server

Trending

  • *You* Can Shape Trend Reports: Join DZone's Software Supply Chain Security Research
  • Automatic Code Transformation With OpenRewrite
  • The Cypress Edge: Next-Level Testing Strategies for React Developers
  • How to Convert XLS to XLSX in Java
  1. DZone
  2. Data Engineering
  3. Databases
  4. Synchronizing MS SQL Server Databases

Synchronizing MS SQL Server Databases

In this article, we’ll look at how to synchronize SQL Server database changes between different servers using dbForge Schema Compare for SQL Server.

By 
Evgeniy Gribkov user avatar
Evgeniy Gribkov
·
Mar. 07, 19 · Tutorial
Likes (6)
Comment
Save
Tweet
Share
27.0K Views

Join the DZone community and get the full member experience.

Join For Free

General Relevance of SQL Server Database Changes Synchronization

When working with databases, we are often faced with the task of synchronizing changes.

Even if a company only uses one production environment, there still needs to be at least one additional environment for testing purposes. This creates a need to transfer changes from the testing environment to the production one.

As the company grows, the number of servers and virtual environments increases, and the synchronization process becomes more complex.

In general, we can lay out the sequence of steps for transferring and synchronizing changes as follows:

  1. Between development environments
  2. From a development environment to a test environment
  3. Between test environments
  4. From a test environment to a pre-production environment
  5. From a pre-production environment to a production one, with the ability to quickly roll the changes back

There are various tools that can be used for synchronizing changes between environments, such as:

  1. Visual Studio Comparer
  2. SQL Server Integration Services
  3. Devart’s dbForge Schema Compare for SQL Server

In this article, we’ll look at how to synchronize SQL Server database changes between different servers using dbForge Schema Compare for SQL Server.

SQL Server Schema Synchronization

We’ll accomplish the task of database schema synchronization with the help of Schema Comparison Tool embedded in Devart’s dbForge Studio for SQL Server. free trials are available. To make things simple, let’s take a source server and a target server.

In this example, we’ll be working with the SRV database, which is used to manage the MS SQL Server RDBM. This database can be accessed freely for any purposes, so you can download it here:

https://github.com/jobgemws/Projects-MS-SQL-Server-DBA/tree/master/SRV

Establishing the Connection

After opening dbForge Studio for SQL Server, you’ll need to go to the "Database Sync" tab and create a new connection by clicking the "New Connection" button:

dbForge Studio 2019

The connection settings window will open, in which you should enter the necessary information to connect to an instance of MS SQL Server (the source server). As you can see, the ‘MFA Authentication’ option is now available in addition to the "MS SQL Server", "Windows" and "Active Directory" authentications. When all the necessary fields are filled in, click the "Test Connection" button to test the connection:

Database Connection Properties

Once the connection is established, the following dialog box will be displayed:

Successfully connected

Press "OK" — first in this box, and then in the connection settings window.

A new connection should now be added:

Database Sync

In a similar way, we need to connect to all necessary MS SQL Server instances (in our example, this will be achieved by creating a connection to the target server).

Setting up the Comparison Process

To adjust the database schema comparison process, press ‘New Schema Comparison’:

New Schema Comparison

A window with schema comparison settings will appear.

We’ll need to specify the following info in the "Source" pane in the left pane of the "Source and target" tab:

  1. type

  2. connection

  3. source database

In the right "Target" pane, we’ll specify:

  1. type

  2. connection

  3. target database

Keep in mind that "database" is not the only accessible type. When required, you can also choose "script folder," "snapshot," "source control," or "backup." However, in this particular case, we’ll choose the "database" type.

After you specify all the necessary settings in the current tab, press "Next" to continue to the next step in adjusting the database schema synchronization process.

If two initially identical databases are being compared, you can just proceed to the comparison process right away by clicking "Compare."

Any tab can be accessed at any time if needed — just click the corresponding element in the left pane of the window.

You can also save the current settings as a .bat file by clicking the "Save Command Line" button in the bottom left side of the window.

In most cases, when changes are being performed between two initially identical databases, pressing "Compare" at this stage will do the job. However, to explore the tool’s full functionality, we’ll press "Next:"

Source and Target

You can tune various settings in the "Options" tab or just leave them in the default state:

Options

You can set the schema mapping by name in the "Schema Mapping" tab:

Schema Mapping

Table and column mapping can be set up in the "Table Mapping" tab:

Table Mapping

The "Object Filter" tab allows you to set the objects for comparison. You can also get back to any of the previous steps at any time.

Comparing Database Schemas

To start the database schema comparison process, click "Compare:"

Object Filter

The database schema comparison settings window will be closed, and a window with the comparison process progress bar will appear instead:

Describing objects

After the process is finished, take a look at the window. You can change the comparison settings by clicking "Edit Comparison" in the top left corner of the screen. To the right of this button, you can see a circle with an arrow icon — it’s a refresh button that will start the MS SQL Server schema comparison process again when being clicked. Also, slightly below, you can find all the servers that were registered earlier:

Edit Comparison

The "File" option in the main menu allows you to save the schema comparison settings as a .scomp file.

Adjusting Synchronization Settings

Now, let’s look at the window’s central top section. Here, you can choose the objects that need to be synchronized by selecting the corresponding checkboxes. Objects from the source are on the left side, while those from the target are on the right. Below, you can see the code of these objects placed in a similar manner. The comparison objects are divided into 4 groups, with the number of objects in each of these groups being accordingly counted.

In this case, we are viewing the code of a specific table, which is located both in the source and in the target. That’s why this object is located in the "Different" group:

Adjusting synchronization settings

When this object is chosen, its code will be transferred from the left side to the right while the database schemas are synchronized for the target.

Here, we’re looking at the code of a representation that is only present in the source. That’s why this object is placed in the "Only in source" section and no code is visible for it on the right side of the screen:

Adjusting synchronization settings

When such an object is chosen, the code for creating it will be generated for the target.

In the following case, we’re looking at the code of a representation that is only present in the target. Therefore, this object is placed in the "Only in target" section, and there is no code visible for it in the left side of the screen:

Adjusting synchronization settings

When such an object is chosen, the code for deleting it will be generated for the target.

Next, to start the database schema comparison process, you’ll need to click either one of the buttons you can see highlighted in red on the following screenshot:

Adjusting synchronization settings

In the "Output" tab, you need to specify how the synchronization process will be carried out. The most common options to choose here are saving the script internally in dbForge Studio for SQL Server or saving it to a file. In this particular case, we need the former option. It is highly recommended to carefully go through the following sequence of tabs while adjusting the synchronization process:

Schema Synchronization Wizard

In the "Options" tab, various options can be set for the database schema synchronization process.

Usually, all options from the "Database backup" section need to be cleared.

In the "Transactions" section, the "Use a single transaction" and "Set transaction isolation level to SERIALIZABLE" options are selected. This helps to avoid situations in which the changes can only be applied partially, i.e., with these options enabled, the changes will be either applied fully or won’t be applied at all:

Image title

The "Summary" tab summarizes and displays the final synchronization settings. You can go back to any of the previous steps if any changes are needed at this point.

Keep in mind that the database schema synchronization settings can be saved as a .bat file; you can do this by pressing the "Save Command Line" button at the bottom left part of the screen.

Generating a Synchronization Script

To start generating the database schema synchronization script, press "Synchronize:"

Generating a synchronization script

The script will be generated and shown in a new window:

Generating a synchronization script

Generating a synchronization script

This script is the code we need to transfer the database schema changes from the source to the target. It can be executed against the target server or saved as a file for later use. Usually, this script should be saved in any case — this allows us to run it on several different servers at once for the same database. You can do this by using groups of registered servers in SSMS, sending the resulting script to all servers from the selected group simultaneously:

Registered Servers


Once the synchronization is over, all previously selected objects should disappear from the schema comparison window:

Schema Comparison Window

Conclusion

As you can see, dbForge Studio for SQL Server allows to quickly generate a database schema synchronization script. When compared with SSIS and Visual Studio Comparer, dbForge Studio for SQL Server is faster and easier to use, e.g., fewer clicks are required during the settings adjustment process.

Sources:

  1. https://www.devart.com/dbforge/sql/studio/
  2. https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-data-tools/hh272690(v=vs.103)
  3. https://github.com/jobgemws/Projects-MS-SQL-Server-DBA/tree/master/SRV
Database sql Microsoft SQL Server Schema Comparison (grammar) Object (computer science) Connection (dance)

Opinions expressed by DZone contributors are their own.

Related

  • How to Recover a Deleted Table in a SQL Server Database
  • Using AUTHID Parameter in Oracle PL/SQL
  • Restoring the MS SQL Server Database in Easy Steps
  • How To Convert MySQL Database to SQL Server

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!