How to synchronize large SQL Server databases

When synchronizing large databases that contain millions of records, you will come across several challenges The first challenge is to just compare such databases. When the database tables contain millions of records, their comparison through the ApexSQL Data Diff graphical interface will be very slow and in such cases, it is strongly recommend using the ApexSQL Data Diff command line interface. As there’s no need to show millions of records visually in the tool’s grid, such large databases are processed faster.

1. Start the Command Prompt
2. Navigate to the folder where ApexSQL Data Diff is installed (by default that’s C:\Program Files\ApexSQL\ApexSQLDataDiff2015)
3. To compare two live databases and create a synchronization script, run a command in this format: ApexSQLDataDiff.com /s1:<source server> /d1:<source database> /s2:<destination server> /d2:<destination database> /of:<full path to script> /v /f

For example, to compare the AdventureWorks2012 database to the Central database, both on the same SQL Server instance Fujitsu\SQL2012: ApexSQLDataDiff.com /s1:Fujitsu\SQL2012 /d1:AdventureWorks2012 /s2:Fujitsu\SQL2012 /d2:Central /of:E:\sync.sql /v /f

If the compared tables have many different records, the synchronization script will contain a large number of INSERTs, UPDATEs and DELETEs. Such scripts may take up more space than the database itself (as data is much less densely packed when stored as text), so it has to be ensured that there is a lot of available space in the target path. A rough way to estimate a worst-case scenario of how much space is needed is to multiply the size of the database by 4. This factor isn’t magic – it’s what you get when you script one byte as a hexadecimal number in a UTF-16 file: you get 4 bytes in the script file for every byte in the database. Granted, not all data will be so inflated in size in the script file but again, this is a rough estimate of a worst-case scenario. The next challenge that will be faced is how to execute such a large script.

SQL Server Management Studio

One of the options is to use SQL Server Management Studio to execute SQL. However, opening such a large script in SQL Server Management Studio might not be possible, since it can get non-responsive or even crash.

The sqlcmd utility

The sqlcmd command line utility is designed to execute Transact-SQL statements and scripts, and automate Transact-SQL tasks. It’s located in the SQL Server’s Tools\Binn subdirectory (e.g. the default path for SQL Server 2012 is C:\Program Files\Microsoft SQL Server\110\Tools\Binn). To execute a SQL script:

1. Start the Command Prompt
2. Navigate to the folder where the sqlcmd utility is located
3. Run a command in the following format: sqlcmd –S <server name> -d <database name> -i <full path to script> -U <user login ID> –P <login password>

For example, to execute the sync.sql file located in the E:\ root against the Central database on the SQL Server instance Fujitsu\SQL2012, run the following command: sqlcmd -S Fujitsu\SQL2012 -d Central -i E:\sync.sql -U sa –P sqladmin

The drawback of this method is that sqlcmd has issues with large scripts and may fail and show bogus errors such as “Incorrect syntax near”, or “Unclosed quotation mark after”

If you were to open the SQL file in a text editor, find the command line specified in the error message, paste it into SQL Server Management Studio and execute it – there would be no errors, which means that the script is correct Furthermore, this stops the execution even if we specify a high severity level such as 17.

The osql utility

Like sqlcmd, the osql command line utility comes with SQL Server. It’s also located in the Tools\Binn subfolder To execute a SQL script:

1. Start the Command Prompt
2. Navigate to the folder where the osql utility is located
3. Run the command in the following format: osql –H <workstation name> -S <server_name[\instance_name]> -U <user login ID> -P <login password> –i <full path to script>

For example, to execute the sync.sql file located in the E:\ root, against the Central database on the SQL Server instance Fujitsu\SQL2012, run the following command: osql -H Fujitsu -S Fujitsu\SQL2012 -i E:\sync.sql -U sa -P sqladmin

When the file execution is finished – there are no messages that indicate whether it was successful, or it failed

Another drawback is that the osql utility is depreciated and will be removed in the future versions of SQL Server, therefore Microsoft recommends using the sqlcmd utility instead

The ApexSQL Run Script utility

ApexSQL Run Script Utility is a free command line utility designed to execute large SQL scripts. It can execute scripts tens of GBs in size, error free and without timing out. To execute a SQL script with it, do the following:

1. Download ApexSQL Run Script Utility
2. Unzip it into a folder. There’s only the ApexSqlRunScript.exe file
3. Use the command line and navigate to the folder where the file was unzipped
4. Run the utility with the following parameters: ApexSqlRunScript <server name> <database name> <script path>

For example: ApexSqlRunScript Fujitsu\SQL2012 Central E:\sync.sql You‘ll be notified of the number of batches executed and their status

If there is an error in a batch, it will be rolled back If you encounter an error during the SQL execution, you’ll be notified in the Command Prompt

Also, all errors encountered during the SQL script execution are automatically and in detail, logged in its log file named ApexSqlRunScript.log, and stored in the same folder as ApexSqlRunScript.exe. If the file already exists, the latest logs will be added at the end. The log file is very helpful for troubleshooting – besides the SQL Server error descriptions shown in the Command Prompt, the object affected and statement line number, it lists the statements from the batch that failed. Thanks to this detailed info, it’s easier to nail down and solve the issues, especially when the error origin is not specified in the command line error message, for example:

END; ‘.

2013-05-10 16:57:25.557 BEGIN — =============================================== — Synchronization Script for Table: [dbo].[TestTable] — =============================================== Print ‘Synchronization Script for Table: [dbo].[TestTable]’ SET IDENTITY_INSERT [dbo].[TestTable] ON INSERT INTO [dbo].[TestTable] ([f1], [f2], [f3], [f4]) VALUES (1, 0, 0, N0′) INSERT INTO [dbo].[TestTable] ([f1], [f2], [f3], [f4]) VALUES (2, 0, 1, N’0.993857′) INSERT INTO [dbo].[TestTable] ([f1], [f2], [f3], [f4]) VALUES (3, 0, 2, N’1.05394′) INSERT INTO [dbo].[TestTable] ([f1], [f2], [f3], [f4]) VALUES (4, 0, 3, N’0.305654′) INSERT INTO [dbo].[TestTable] ([f1], [f2], [f3], [f4]) VALUES (5, 0, 4, N’2.81439′) INSERT INTO [dbo].[TestTable] ([f1], [f2], [f3], [f4]) VALUES (6, 0, 5, N’0.649527′) INSERT INTO [dbo].[TestTable] ([f1], [f2], [f3], [f4]) VALUES (7, 0, 6, N’1.62164′) INSERT INTO [dbo].[TestTable] ([f1], [f2], [f3], [f4]) VALUES (8, 0, 7, N’1.06883′) INSERT INTO [dbo].[TestTable] ([f1], [f2], [f3], [f4]) VALUES (9, 0, 8, N’1.32762′)

END;

Creating a synchronization script for your databases might not be the last big step you have to take, when synchronizing large databases. The next one, equally important and challenging, is executing this script. Use ApexSQL Run Script, a utility designed to run large scripts, to execute scripts error free.