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.
Happy Coding!
