Data Compare Sql Server

Data Compare Sql Server Average ratng: 3,7/5 6212 votes

You could cursor through all of the tables read the T-SQL from one server, get the T-SQL for the same object from the other server and do a comparison. To compare the data you can write scripts that read row by row and do a comparison of the data. Compare and synchronize SQL Server data Compare data in SQL Server indexed views: Compare data from SSMS Compare database with source control project Compare database with SQL database backups without restoring Narrow a data comparison to affected rows only Row level recovery from a database backup. Oracle Database vs. SQL Server Oracle Database is ranked 1st in Relational Databases with 32 reviews vs SQL Server which is ranked 3rd in Relational Databases with 29 reviews. The top reviewer of Oracle Database writes 'Enables us to combine all the sophisticated architectures in the market'.

Synchronizing two database is a common scenario but the task sometimes take so much time and effort to synchronize two databases perfectly. Some developers maintain script to sync the databases and runs those script time to time. I found it works well but maintain the script properly is a difficult job if more than one developer works in a single database. In this blog post we will discuss how we can sync two databases using SQL Server Database Project, its advantages and disadvantages.
SQL Server Database Project is a wonderful tool and rescue for database developer and help them to perform some time consuming task easily. We can use this project for sync database schema as well as data itself. In this post our main concern is to sync data. So let's create a SQL Server Database Project first. To add database project open visual studio and click New Project from the File menu. Then select SQL Server Template and SQL Server Database Project. Give it a meaningful name and click ok.

Redgate compare data

Our database project is ready now and we can use it to compare data between two databases. Before go further lets setup a environment. We have created two database stg_database and prod_database and both database has a single table name Person. Below is the table script.
Now we insert some sample data in the Person table for both database. Below is the insert script.
After inserting the record in the table we update one record of stg_database table so that we can visualize the effect perfectly. Below is the update script.

Below screenshot shows the two database records.
Server

Let's do some manual comparison before we use our data project.
Id 2 & 3: Id swap in production database.
Id 4: Last Name and Address updated in stg_database
Id 6: Exists in stg_database but not in prod_database
Now we will use our Database Project and examine how the data comparison works. To start a comparison open a data comparison window by clicking Tools menu from visual studio then SQL Server then New Data Comparison.. Follow the below image
Figure: Initiate new data comparison
In the New Data Comparison window we need to configure source and target database and comparison options. It has four different options to compare records in database, in our case we will select all of them. See the below image and configure the source and target database and select data compare options.
Figure: Configure source and target and Data Compare Options.
Clicking next will forward us to a new page where we can select tables and views that we want to compare. Also we can choose the columns that we actually interested to compare. Please see the below image.
Figure: Choose tables and columns that will be compared.
Clicking Finish will start data comparison and when finish it will open a SqlDataCompare window in visual studio. The lower part of the SqlDataCompare window looks like the below image.

It shows four different tabs. The first tab shows the records that are different in compared databases. In our case it says four records differ in source and target. Since the data project compare the data based on primary key, it will identify the record different based on the primary key. Second tab shows the records that are exists in source but not in target. In our case it has one record, record 6 is in stg_database but not in prod_database. Third tab shows the records that are in target but not in source and fourth tab shows the identical records. Let's see what happen when we update the target database based on the comparison.
In source database id 2 contains the record for Tamim Iqbal and 3 contains record for Mushfiq Rahim, in target id 2 contains record for Mushfiq Rahim and 3 contains record for Tamim Iqbal. So if we run the update, target id will swap but information does not lost. Id 4 will updated by the update record in source database. Id 5 source and target contains different records see the above image marked portion. If we run update target will lost Somuya Sarkar record since it will replace by Nasir Hossain :(. See the below image for final result.
Figure: Records of target database after sync with source.
So what can we do if we want both Nasir Hossain and Somuya Sarkar in target database. There is no easy way, for this kind of scenario we need to declare composite primary key so that the data project compare the data based on those keys. Let's try to do an experiment and see what happens.
We will drop our Person table and recreate it with composite primary key (Id, EmailId). Below is the script.
Data insertion and update will be same as before. After thatwhen run the data comparison again the data comparison result window looks likethe below image.
Figure: Different Records tab.
Now it shows only one record in Different Records Tab. Andsee the below image, the Only in Target tab we see there are three records.

We uncheck the record 5 because we want it in our targetdatabase. Below snapshot shows the target database after running updateoperation, prod_database sync with the stg_database database.
Figure: Update result when use composite primary key.

Database Compare Sql Server

Wesee that it is dangerous to use Data Project blindly for data compare andupdate. In this case there is a possibility that we lose data. Actually it willwork to update/sync database table that is not affected by user interaction.And we can use this tools for update configurations related table easily thatis not usually changed by user interactions. Before use update any databaseusing Data Tool carefully check the changes.

It’s common knowledge that running database changes directly in production, without thorough testing first, should be avoided.

Hasbi rabbi jallallah by shafeeq. If there are enough resources in the environment, there would be at least one testing and one production SQL Server instance. However, that introduces another challenge. When everything is set up in the test, and runs smoothly and as expected, how can it easily be applied to the production instance?

Synchronizing several objects can be done manually, but applying multiple, complex changes to a live, high transaction database is something that can start an avalanche of problems, if not properly tested and executed.

What to do if wrong changes were applied to the production database, or if something had gone wrong and ended up with the messed-up production database and the incorrect object schemas in the test database. How to compare SQL schemas, and more importantly, how to safely synchronize them?

To compare and synchronize in any of the scenarios describes above, here is what can be done:

  1. Compare the database objects in the production and test databases, using a SQL script such as the following one:

    There are various SQL scripts with different approaches that can be used to compare database object schemas. What they all have in common is that they use system objects.

    The disadvantage of SQL scripts like this is that it doesn’t compare all object types, it treats object definition only as text and it compares only objects with exactly the same names. So, it cannot be used to compare the tables, Customers and tblCustomers, for example. Also, the system object structure has to be well known.

  2. Once the different objects are found, synchronize them.

    The easiest scenarios are to use an Alter script for the object or drop the object and recreate it using the object DDL script. These methods are applicable only if there are no dependencies.

    For tables, all records have to be stored first, and then drop and recreate a table using the generated script:

    1. Run SQL Server Management Studio
    2. In Object Explorer, expand the Databases node
    3. Right-click a database, open the Tasks sub-menu -> Generate scripts:
    4. In the Choose objects tab, select the objects to script:
    5. In the Set scripting options tab, click the Advanced button and make sure the Types of data to script option is set to Schema only:

    Besides the described disadvantages, the manual synchronization method is not recommended as it:

  • Doesn’t handle object dependencies. Dependencies are easily overlooked, dependent objects are not scripted, SQL is not executed in the correct order. When SQL is executed without recreating all parent objects first, SQL will fail
  • Is time-consuming – finding all different objects and creating synchronization SQL for them takes a lot of time. If developers didn’t document each and every change to a detail, this is close to impossible
  • Error prone – huge databases with a large number of objects are not easy to maintain nor is keeping track of all changes manually. They usually involve complex dependencies which increase the problems manual synchronization brings

Instead of synchronizing databases manually and risk making a mess on the production instance, use SQL database compare tool ApexSQL Diff.

ApexSQL Diff is a SQL Server database comparison and synchronization tool which detects differences between database objects and resolves them without errors. It generates comprehensive reports on the found differences and can automate the synchronization process between live databases, backups, snapshots, script folders and source control projects.
Cool free fonts without downloading download.

Follow these steps to set up the automation for the schema synchronization process:

  1. Start ApexSQL Diff
  2. Click the New button in the Project management window:
  3. On the Source side of the Data source tab:
    • Select Database from the Source drop-down list
    • Specify the SQL instance with the test database from the Server drop-down list
    • Specify the authentication method for that SQL instance (and a valid set of credentials if SQL Server authentication is chosen)
    • Specify the test database name in the Database drop-down list
  4. On the Destination side of the Data source tab:
    • Select Database from the Destination drop-down list
    • Specify the SQL instance with the production database from the Server drop-down list
    • Specify the authentication method for that SQL instance (and a valid set of credentials if SQL Server authentication is chosen)
    • Specify the production database name in the Database drop-down list:


      • Note: Check the Remember password option, if using SQL Server authentication, as the CLI won’t be able to connect to the source and/or destination data source, unless a password is specified as a switch in the CLI.

  5. Click the Compare button in the bottom-right corner of the New project window once everything is set
  6. After the comparison process is done, select objects from the Results grid that will be included in the synchronization process:
  7. Click the Save button, from the Home tab, to save the project:

    The saved project file will be used later for automating the process.

  8. Click the Synchronize button from the Home tab, to initiate the Synchronization wizard:
  9. Select the Synchronize now action in the Output options step:
  10. Once everything is set up, click the Synchronize button in the bottom-right corner of the Synchronization wizard
  11. After the synchronization is finished, the Results window will be shown:

Automating schema sync process

Since the first synchronization was successful and the project file was created, the automation of process can be done by creating a PowerShell script.

In this example, we used SQL Server authentication and the login password is encrypted in the saved project file. Learn more about ways of handling login credentials.

Next, the command that will call ApexSQL Diff and execute its parameters, is provided:

Additionally, the “no differences detected” return code can be utilized in this PowerShell script, so that before each schema synchronization, comparison will be initiated and if there are no schema changes, the job will go back to sleep.

Scheduling schema sync process

Once the PowerShell script is created and saved, it can be easily scheduled in one of the several ways. Learn more about ways of scheduling ApexSQL tools.

There is no need to worry about database synchronization any more. ApexSQL Diff will compare all database objects, check for dependencies and script them along with the objects, make sure the scripts are executed in the correct order, and create a full database backup before the synchronization. All this can be scheduled to run unattended.

Related posts:

April 4, 2013
Comments are closed.