MSSQL: How to use “Link SQL” for query/sync databases in different database servers

Link SQL is very useful technique to connect database servers in same domain and querying them. Using Link SQL we can connect different databases and those details available in following article.

https://msdn.microsoft.com/en-us/library/ff772782.aspx?f=255&MSPPError=-2147217396 


In this we focus on link two SQL servers in same domain then querying and sync data between those two databases. We follow following steps to query two SQL databases in different database servers.

Step 1:

• Make sure can access both database servers from each other.
• Use SQL Management Studio to connect to the remote database server
• If it unable to connect to the remote server then check it enabled named pipe and TCP/IP using SQL server configuration manager

Set TCP/IP enabled and Named Pipes Enabled - 32 bit mode

Set TCP/IP enabled and Named Pipes Enabled

• If Management Studio still unable to access the SQL server remotely then check Windows firewall settings and gave necessary permission to access SQL server through. 
• Setting Inbound Rule to access SQL server remotely

Create Inbound Rule in windows firewall

Set port which configured in SQL server

Step 2:

• Add target SQL server as Link Server.
• There are several ways to add another database server as Link server in SQL (https://msdn.microsoft.com/en-us/library/ff772782.aspx?f=255&MSPPError=-2147217396 ). However we follow following SQL statement to add link SQL server.

EXEC master.dbo.sp_addlinkedserver
   @server = N'XXX-UG07',
   @srvproduct=N'SQL Server' ;

• After add database server as link server then all databases in that server available in current context. Following TSQL code shows how to execute stored procedure remotely then get data from SQL server and finally sync those data with target table.
• Use “OPENQUERY” to execute stored procedure remotely and retrieve data.

DECLARE @BusinessUnit UNIQUEIDENTIFIER
DECLARE @CreatedBy UNIQUEIDENTIFIER
DECLARE @ModifiedBy UNIQUEIDENTIFIER
DECLARE @OwnerId UNIQUEIDENTIFIER
DECLARE @StateCode INT
DECLARE @StatusCode INT

set @BusinessUnit = '72921AD4-3025-E511-80BC-00155D156EA8'
set @CreatedBy = 'DDA81AD4-3025-E511-80BC-00155D156EA8'
set @ModifiedBy = 'DDA81AD4-3025-E511-80BC-00155D156EA8'
set @OwnerId = 'DDA81AD4-3025-E511-80BC-00155D156EA8'
set @StateCode = 0
set @StatusCode = 1


INSERT INTO [CC_MSCRM].[dbo].ContactBase
(Contactid,OwningBusinessUnit, CreatedBy, 
  CreatedOn, ModifiedBy, ModifiedOn,StateCode,StatusCode,
  FirstName,LastName,new_Number,OwnerId,OwnerIdType)

SELECT C1.contactid,@BusinessUnit,@CreatedBy,
    GETUTCDATE(),@ModifiedBy,GETUTCDATE(),@StateCode,@StatusCode,C1.FirstName, 
    C1.LastName, C1.new_AccountNumber,@OwnerId,8
FROM [XXX-UG07].CC_mscrm.dbo.contactbase C1
WHERE C1.contactid IN (SELECT contactid
FROM OPENQUERY([XXX-UG07],'CC_MSCRM.dbo.Test'))