Copying data from one SQL server table to another SQL Server table

Hi Readers,
I had a requirement where I had to copy data from one SQL Server located in U.S. (Source Server) to SQL Server located in India (Destination Server). The copy has to be triggered when data is inserted at Source Server table.
I encountered various issues starting from Linked server connection, collation issues to distributed transaction coordinator issues.
Finally, I have collected all steps that I followed in my project and steps needed to handle all issues. And here is my blog talking about them. Feel free to drop any suggestions or comments for further enhancement of this blog.

 

Scenario:-

Let us assume you have ‘EmployeeDetails’ table present in Source (Local SQL Server) and Destination Server (‘MyDestinationSQLServer’). With every Insert in EmployeeDetails table in local server, we want the records to be inserted in destination server table as well.

 

Steps Followed:-

Majorly, there were below 5 steps that were followed:-
1. Create local login in both source and destination SQL server (Same Credentials to be used).
2. Create Linked Servers. Link your Source Server to Destination Server.
3. Enabling Distributed Transaction Coordinator service and properties
4. Handling collation issues if any.
5. Writing stored procedure and trigger for copying data from source server to destination server.
 

Detailed Steps:-

We will look into each step in details now:-
 

1. Create local login. This step should be followed in both source and destination machines.

a. Login to the SQL Server
b. Go to Server->Security->Logins->New Login (I have used ‘Admin’ and ‘P@ssw0rd’ as credentials.)

c. Give the user ‘sysadmin’ server role from Server Roles tab.
 

2. Create linked server. You will be required to link source server to destination server to copy data.

a. Run below script from source server (Local server in this blog) to create linked server.
b. Make sure to update destination server name and Database name. (Currently used name is ‘MyDestinationSQLServer’ and ‘MyDatabase’)

USE [master]

GO

EXEC master.dbo.sp_addlinkedserver @server = N'MyDestinationSQLServer', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'MyDestinationSQLServer ', @catalog=N'MyDatabase'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MyDestinationSQLServer ',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MyDestinationSQLServer ',@useself=N'False',@locallogin=N'admin',@rmtuser=N'admin',@rmtpassword='P@ssw0rd'

GO

EXEC master.dbo.sp_serveroption @server=N'MyDestinationSQLServer', @optname=N'collation compatible', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MyDestinationSQLServer', @optname=N'data access', @optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @server=N'MyDestinationSQLServer', @optname=N'dist', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MyDestinationSQLServer', @optname=N'pub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MyDestinationSQLServer', @optname=N'rpc', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MyDestinationSQLServer', @optname=N'rpc out', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MyDestinationSQLServer', @optname=N'sub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MyDestinationSQLServer', @optname=N'connect timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'MyDestinationSQLServer', @optname=N'collation name', @optvalue=null

GO

EXEC master.dbo.sp_serveroption @server=N'MyDestinationSQLServer', @optname=N'lazy schema validation', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MyDestinationSQLServer', @optname=N'query timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'MyDestinationSQLServer', @optname=N'use remote collation', @optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @server=N'MyDestinationSQLServer', @optname=N'remote proc transaction promotion', @optvalue=N'true'

GO

c. After the linked server is created successfully. Test the Connection
Go to Server -> Server Objects -> Linked Servers -> MyDestinationSQLServer -> Right Click -> Test Connection.
You will see below popup when linked server connection test is successful.

 

3. Distributed Transaction Coordinator service should be running. This step should be followed in both source and destination server machines.

a. Go To Services.msc.
b. Start DTC Service
c. Set Startup Type to Automatic

 

4. Allow DTC in windows firewall. This step should be followed in both source and destination server machines.

a. Go to control panel -> Window Firewall -> Allow an app or feature through Windows Firewall
b. Scroll down to DTC feature
c. Enable domain and private communication

 

5. Update local DTC security settings. This step should be followed in both source and destination server machines.

a. Go to Control Panel -> Administrative Tools -> Component Services
b. Expand Component Services -> Computers -> My Computer -> Distributed Transaction Coordinator -> Local DTC.

c. Right click -> Properties -> Security tab
d. Enable network DTC access, allow inbound and outbound communication with no authentication required as shown in screenshot below:-

 

6. Resolve Collation issues.

I had different collations in columns of source table and destination table in actual production scenario. Source and Destination Servers were having different collations as mentioned below
Source Server Collation: SQL_Latin1_General_CP1_CI_AI
Destination Server Collation: Latin1_General_CI_AI
So while creating stored procedure, it was failing on where clause in equal to operator with below error:-
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation.
To resolve issue on specific columns ‘COLLATE DATABASE_DEFAULT’ has to be added when comparing columns. Something like shown below

INNER JOIN [Mydatabase].[dbo].EmployeeDetails SourceTable ON 

DestTable.EmpId COLLATE DATABASE_DEFAULT = SourceTable.EmpId COLLATE 

DATABASE_DEFAULT where SourceTable.EmpId = @SourceEmpId

7. Write Script to copy data from one server to another.

In this blog, we are considering a simple example of ‘EmployeeDetails’ table with majorly 5 columns – Unique Id, Employee Id, Name, Address and Phone Number

a. Script to create table is given below

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[EmployeeDetails](

[Id] [int] IDENTITY(1,1) NOT NULL,

[EmpId] [nvarchar](50) NOT NULL,

[Name] [nvarchar](50) NOT NULL,

[Address] [nvarchar](50) NOT NULL,

[Phone] [nvarchar](50) NOT NULL,

CONSTRAINT [PK_EmployeeDetails] PRIMARY KEY CLUSTERED

(

[EmpId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 

ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

b. Stored Procedure to copy data from Source Server table to Destination Server EmployeeDetails table.
Below stored procedure will give you collation issue while running as collation of column EmpId in Source and Destination table was different

Create Procedure [dbo].[InsertEmployeeDetails]

@SourceEmpId nvarchar(10)

AS

declare @DestEmpExists int

Select @DestEmpExists = Count(*) from 

[MyDestinationSQLServer].[MyDatabase].[dbo].EmployeeDetails where 

EmpId = @SourceEmpId

If @DestEmpExists = 0

BEGIN

PRINT 'Inserting Employee Information in Destination Employee Table'

Insert Into 

[MyDestinationSQLServer].[MyDatabase].[dbo].EmployeeDetails (EmpId, 

Name, Address, Phone)

Select EmpId, Name, Address, Phone from 

[MyDatabase].[dbo].EmployeeDetails where EmpId = @SourceEmpId

END

ELSE If @DestEmpExists = 1

BEGIN

PRINT 'Updating Employee Information in Destination Employee Table'

Update [MyDestinationSQLServer].[MyDatabase].[dbo].EmployeeDetails

SET Name = SourceTable.Name, Address = SourceTable.Address, Phone = 

SourceTable.Phone

FROM [MyDestinationSQLServer].[MyDatabase].[dbo].EmployeeDetails DestTable

INNER JOIN [MyDatabase].[dbo].EmployeeDetails SourceTable ON 

DestTable.EmpId = SourceTable.EmpId where SourceTable.EmpId = @SourceEmpId

END

Error received was
Msg 468, Level 16, State 9, Procedure InsertEmployeeDetails, Line 28
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation.

To resolve this issue, ‘COLLATE DATABASE_DEFAULT’ was added when comparing columns. Below is the updated stored procedure with collation issues fixed.

Create Procedure [dbo].[InsertEmployeeDetails]

@SourceEmpId nvarchar(10)

AS

declare @DestEmpExists int

Select @DestEmpExists = Count(*) from 

[MyDestinationSQLServer].[MyDatabase].[dbo].EmployeeDetails where EmpId = 

@SourceEmpId

If @DestEmpExists = 0

BEGIN

PRINT 'Inserting Employee Information in Destination Employee Table'

Insert Into [MyDestinationSQLServer].[MyDatabase].[dbo].EmployeeDetails 

(EmpId, Name, Address, Phone)

Select EmpId, Name, Address, Phone from 

[MyDatabase].[dbo].EmployeeDetails where EmpId = @SourceEmpId

END

ELSE If @DestEmpExists = 1

BEGIN

PRINT 'Updating Employee Information in Destination Employee Table'

Update [MyDestinationSQLServer].[MyDatabase].[dbo].EmployeeDetails

SET Name = SourceTable.Name, Address = SourceTable.Address, Phone = 

SourceTable.Phone

FROM [MyDestinationSQLServer].[MyDatabase].[dbo].EmployeeDetails DestTable

INNER JOIN [MyDatabase].[dbo].EmployeeDetails SourceTable ON 

DestTable.EmpId COLLATE DATABASE_DEFAULT = SourceTable.EmpId COLLATE 

DATABASE_DEFAULT where SourceTable.EmpId = @SourceEmpId

END

c. Trigger created on EmployeeDetails table in Source server.

Create TRIGGER [dbo].[Trg_InsertEmployeeDetails] 

ON [dbo].[EmployeeDetails]

AFTER INSERT, UPDATE

AS

BEGIN

SET XACT_ABORT ON

BEGIN DISTRIBUTED TRANSACTION

--1. Declare variables

declare @InsertedEmpId nvarchar(50)

--2. Fetch values from Table

Select @InsertedEmpId = EmpId from inserted

exec InsertEmployeeDetails @SourceEmpId = @InsertedEmpId

COMMIT TRANSACTION

SET XACT_ABORT OFF

END

Hope this will help you. Suggestion and Feedback is welcome.