SQL Server – Syncing data across SQL Instances using TableDiff.exe

Background

Trying to sync our Beta DB to our Production SQL instance.

Using Tablediff, is the first path we will consider.

 

Lab

 

List of Files

  1. ListofTables.txt
    • Contains list of tables to compare
    • Each line contains the schema and table name
  2. _invoke.com
    • Specifies output folder
    • Creates if does not exist
    • Cleans former files from it
    • Calls _base.cmd
  3. _base.cmd
    • Prepares parameter to be passed to tablediff.exe
    • Invokes tablediff.exe

 

ListofTables.txt


dbo,AdvPartners
dbo,Courses
dbo,Modules
dbo,ModuleTopic
dbo,ProductCatalog
dbo,Products
dbo,Section
dbo,Student

 

_invoke.cmd


@echo off
Setlocal EnableDelayedExpansion

set "_baseFolder=D:\DBScripts"
set "_environment=\ProductionToDR"
set "_prefixFolder=\sqlfiles"

set "_folderOutput=%_baseFolder%\%_environment%\%_prefixFolder%"


If not EXIST "%_folderOutput%" mkdir "%_folderOutput%"

If exist "%_folderOutput%" del "%_folderOutput%"\*.sql /F /Q


FOR /F "tokens=1,2 delims=," %%G IN (listofTables.txt) do (

	set _schema=%%G
	set _table=%%H
	set _object=!_schema! !_table!
	
	call _base.cmd !_schema! !_table! !_folderOutput!
	
	
)	

endlocal

_base.cmd


@echo on
setlocal DisableDelayedExpansion

set "_APP_NAME=tablediff.exe"

if exist "C:\Program Files\Microsoft SQL Server\120\COM\" (
	set "_APP_FOLDER=C:\Program Files\Microsoft SQL Server\120\COM
)else if exist "C:\Program Files\Microsoft SQL Server\90\COM\" (
	set "_APP_FOLDER=C:\Program Files\Microsoft SQL Server\90\COM
)else (
	goto BINARY_NOT_FOUND
)

set "_APP_FULLPATH="%_APP_FOLDER%\%_APP_NAME%""
set "_sourceServer=HRPROD"
set "_sourceDatabase=HRDB"
set "_user=refresher"
set "_password=aunt1"

set "_sourceuser=%_user%"
set "_sourcepassword=%_password%"

set "_sourceSchema=%1"
set "_sourceTable=%2"
set "_folderOutput=%3"

set "_filename=%_folderOutput%\%1.%2.sql"

set "_destinationServer=HRBETA"
set "_destinationDatabase=HRDB"

set "_destinationuser=%_user%"
set "_destinationpassword=%_password%"

set "_destinationSchema=%1"
set "_destinationTable=%2"


%_APP_FULLPATH% -sourceserver %_sourceServer% -sourcedatabase %_sourceDatabase%    -sourceschema %_sourceSchema% -sourcetable %_sourceTable% ^
    -sourceuser %_sourceuser% -sourcepassword %_sourcepassword% ^
	-destinationserver %_destinationServer% -destinationdatabase %_destinationDatabase%    -destinationschema %_destinationSchema% -destinationtable %_destinationTable% ^
    -destinationuser %_destinationuser% -destinationpassword %_destinationpassword%  ^
	-f %_filename%
	 
 
goto completed
	
:BINARY_NOT_FOUND
echo %_APP_NAME% not found!
 
:completed
setlocal EnableDelayedExpansion

Sample Output

There are two outputs:

  1. The run output
  2. And, if there are differences a file that can be used to bring SQL Instance 2 inline with SQL Instance 1

Output

tableDiff-20160309-1239PM

SQL

 

-- Host: HRDBBeta
-- Database: [HRDB]
-- Table: [dbo].[AdvPartners]

UPDATE [dbo].[AdvPartners] SET [address]=N'2090 Bellamy Drive Rd',[city]=N'Mc kees Rocks',[zipcode]=N'15136' WHERE [pID] = 30468

UPDATE [dbo].[AdvPartners] SET [zipcode]=N'77584' WHERE [pID] = 30543

UPDATE [dbo].[AdvPartners] SET [type]=N'DS',[virtualPage]=N'Victory-C1811' WHERE [pID] = 30596

UPDATE [dbo].[AdvPartners] SET [type]=N'DS' WHERE [pID] = 30662

INSERT INTO [dbo].[AdvPartners] ([address],[advCode],[ascID],[city],[customImages],[customText],[customText2],[logo_image],[parentID],[phone_areacode],[phone_number],[pID],[record_created],[registered],[showAddress],[state],[sub_zipcode],[title],[type],[virtualPage],[website],[zipcode]) VALUES (N'601 W Jackson St ',NULL,NULL,N'Bremond',NULL,NULL,NULL,NULL,NULL,N'',N'',31041,N'2016-02-22 06:32:23.503',N'N',N'Y',N'TX',NULL,N'ISD',N'DS',N'IS',NULL,N'40161')

INSERT INTO [dbo].[AdvPartners] ([address],[advCode],[ascID],[city],[customImages],[customText],[customText2],[logo_image],[parentID],[phone_areacode],[phone_number],[pID],[record_created],[registered],[showAddress],[state],[sub_zipcode],[title],[type],[virtualPage],[website],[zipcode]) VALUES (N'507 E Kings St',NULL,NULL,N'Kilgore',NULL,NULL,NULL,NULL,NULL,N'903',N'984-3843 ',31042,N'2016-02-22 06:56:27.663',N'N',N'Y',N'TX',NULL,N'Mr. D`s Lounging Center',N'DS',N'Mr-D-Lounging-Center-C2807',NULL,N'75662')

INSERT INTO [dbo].[AdvPartners] ([address],[advCode],[ascID],[city],[customImages],[customText],[customText2],[logo_image],[parentID],[phone_areacode],[phone_number],[pID],[record_created],[registered],[showAddress],[state],[sub_zipcode],[title],[type],[virtualPage],[website],[zipcode]) VALUES (N'84 Kanerva PLACE RD',NULL,NULL,N'VAN ALSTYNE',NULL,NULL,NULL,NULL,NULL,N'704',N'301-4718',28191,NULL,N'N',N'Y',N'TX',NULL,N'Parkway Lounging Park',N'DS',N'Parkway-Lounging-Park-C2754',NULL,N'18171')


 

 

TableDiff Internals

Record By Record Read based on Primary Key

Based on the table’s primary key, each record in the table is referenced and read


SELECT 
	  [dbo].[BTWTAppointmentSkills].[appointmentID]
	, [dbo].[BTWTAppointmentSkills].[grade]
	, CONVERT(varchar, [dbo].[BTWTAppointmentSkills].[record_created], 121)
             AS [record_created]
	, CONVERT(varchar, [dbo].[BTWTAppointmentSkills].[record_last_updated], 121) 
             AS [record_last_updated]
	, [dbo].[BTWTAppointmentSkills].[skillID]
	, [dbo].[BTWTAppointmentSkills].[timePracticed] 
FROM [dbo].[BTWTAppointmentSkills] WITH (READUNCOMMITTED) 
WHERE [appointmentID] = '79f30360-f07c-4f9d-a3bd-434a18b04b7e' 
AND [skillID] = 43

Good, Bad, and Ugly

List

  1. Good
    • Fast and transparent
  2.  Bad
    • Large non-Unicode and Unicode character and binary data are unsupported
    • Can be slow as data is manipulated using traditional DML ( Insert, Delete, and Updates )  as compared to faster approaches as BCP

 

Bad

Large non-Unicode and Unicode character and binary data

Sample error messages

  1. Column(s) title are not included in this script because they are of type(s) text, ntext, varchar(max), nvarchar(max), varbinary(max), image, timestamp, or xml. Columns of these types cannot be updated by tablediff utility scripts; therefore non-convergence of data can still occur after this script has been applied. If the tablediff utility output references any of these columns, you must update the columns manually if you want them to converge.
    • Columns whose type is text, ntext, varchar(max), varchar(max) are not included in the scripts generated

 

Columns Large

Review Column Type

Issue sp_help ‘table-name’

VehicleServiceWorkTypes-Before

Review Data


select top 10 ID, title, len = datalength(title)
from   [dbo].[VehicleServiceWorkTypes]
order by datalength(title) desc

Output:
reviewData

Alter table


alter table [dbo].[VehicleServiceWorkTypes]
	alter column [title] varchar(8000)

Review Table – Post Alter Column

Issue sp_help ‘table-name’

VehicleServiceWorkTypes-After

 

Generated SQL

Pre

GeneratedSQL-Pre

Post

GeneratedSQL-Post

 

Ugly

  1. When used to tackle multiple tables, foreign key violations are likely to occur because we are unlikely to properly stager foreign key constraints

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s