SQL SERVER : Linked server and MSDTC

I recently worked on a project where the client needed to manage a large volume of data. To handle this, they decided to use two sets of databases. The first set, referred to as the Online DB, contains all master tables and some raw transaction tables. The second set, known as the Reporting DB, stores reporting tables that are populated through an ETL process.

The client also required an annual archival process to move data that is over a year old from both the Online and Reporting databases to an Archival DB. This process involves transferring the data from the source databases (Online and Reporting) to the Archival DB and then deleting it from the source databases.

The challenge here is ensuring data integrity, especially if the data is successfully copied to the Archival DB but fails to delete from the source databases. To address this, an atomic transaction must be used. An atomic transaction is an indivisible operation that either fully completes all its intended actions, or if an error occurs at any point, it rolls back, leaving the database unchanged as if the transaction never occurred.

Another challenge in this scenario is the involvement of multiple databases. By default, transactions are limited to a single database, meaning you can't apply a transaction across two separate databases. This is where the Microsoft Distributed Transaction Coordinator (MSDTC) comes into play. MSDTC is a service that manages distributed transactions, allowing you to execute a single transaction across multiple databases. It ensures that all databases involved in the transaction either commit or roll back together, maintaining data integrity across the system.

In a scenario involving multiple databases across different servers, a Linked Server in SQL Server plays a crucial role. A Linked Server allows SQL Server to execute queries and commands against a remote database or another instance of SQL Server as if it were a local database.

Role of Linked Server:

• Cross-Database Queries: It enables you to access tables, views, and other objects in a remote database without needing to switch between databases manually. You can write queries that join tables from both the local and remote databases.

• Distributed Transactions: When combined with MSDTC (Microsoft Distributed Transaction Coordinator), linked servers facilitate distributed transactions. This means you can ensure that operations across multiple databases (on different servers) are executed as part of a single transaction. If any part of the transaction fails, MSDTC and the linked server will roll back changes across all involved databases to maintain consistency.

• Archival Process: In your case, the Linked Server allows you to copy data from one database (such as the Online DB) to another database (like the Archival DB) located on a different server. This makes it easier to automate and manage the process of moving data between different database servers in a distributed system.

Example Use Case:

If you have a linked server set up, you can write a query like:

INSERT INTO [LinkedServerName].[DatabaseName].[Schema].[Table] SELECT * FROM [LocalDatabase].[Schema].[Table] WHERE Date < '2023-01-01';

This query moves data from the local database to a remote one via the linked server.

Setting up a Linked Server in SQL Server allows one SQL Server instance to query another SQL Server or even a different type of database (like Oracle, MySQL, etc.). Here’s how you can set it up:

Steps to Set Up a Linked Server in SQL Server:

1. Open SQL Server Management Studio (SSMS)

• Connect to your SQL Server instance using SSMS.

2. Navigate to the Linked Server Section

• In the Object Explorer, expand the node for your server.

• Expand Server Objects.

• Right-click on Linked Servers and select New Linked Server.

3. Configure the Linked Server

• In the New Linked Server dialog box, provide the following details:

a. Linked Server:

• Enter the name for your linked server. This can be any name, but typically it reflects the remote server's name.

b. Server Type:

• SQL Server: If you are linking another SQL Server instance, select the SQL Server option.

• Other Data Sources: If you're linking to a non-SQL Server database (like Oracle, MySQL, etc.), select Other Data Source and choose the appropriate provider (such as Microsoft OLE DB Provider for SQL Server, Oracle OLE DB Provider, etc.).

c. Connection Settings:

• Data Source: Enter the name or IP address of the remote SQL Server instance or the other database you want to connect to.

• Catalog: Optionally, specify the default database you want to connect to on the remote server.

4. Security Settings

• Click on the Security page in the left menu.

• There are several options for how the linked server handles login credentials:

  • Be made using the login’s current security context: Use the same credentials as the logged-in user.

  • Be made using this security context: Use a specific username and password for the linked server. You can provide the credentials here for connecting to the remote server.

5. Server Options (Optional)

• Under the Server Options tab, configure additional settings such as collation compatibility, RPC (Remote Procedure Calls), etc.

  • RPC Out: If you want to allow stored procedures to be executed on the linked server, set RPC Out to True.

6. Test the Linked Server

• Once the linked server is set up, you can test it by running a query against the linked server using four-part naming in SQL:

SELECT * FROM [LinkedServerName].[DatabaseName].[Schema].[Table]

For example:

SELECT * FROM [MyLinkedServer].[MyDatabase].[dbo].[MyTable]

7. Save and Close

• After configuring the linked server, click OK to save and close the dialog.

Example: Creating a Linked Server via T-SQL

You can also create a linked server using a SQL query, especially for SQL Server-to-SQL Server linking. Here’s a basic example:

EXEC sp_addlinkedserver  	
@server='LinkedServerName',  	
@srvproduct='',  	
@provider='SQLNCLI', 	 	
@datasrc='RemoteServerName';   

EXEC sp_addlinkedsrvlogin  	
@rmtsrvname='LinkedServerName',  	
@useself='False',  	
@rmtuser='RemoteUsername',  	
@rmtpassword='RemotePassword';

Common Linked Server Query:

Once the linked server is set up, you can query it like so:

SELECT * FROM [LinkedServerName].[DatabaseName].[Schema].[Table]

Example:

If the linked server is called RemoteDBServer, and the table you want to query is Employee in the HR schema of the CompanyDB database, your query would look like this:

SELECT * FROM [RemoteDBServer].[CompanyDB].[HR].[Employee]

Steps to Set Up MSDTC:

Setting up Microsoft Distributed Transaction Coordinator (MSDTC) is necessary for managing distributed transactions across multiple databases or systems, such as when using Linked Servers. Here's how you can set up MSDTC on a Windows Server.

1. Enable MSDTC in Windows

MSDTC is part of Windows and comes pre-installed, but it may not be enabled or properly configured. Follow these steps to enable and configure it.

a. Open Component Services

• Press Win + R, type dcomcnfg, and press Enter.

• This will open the Component Services window.

b. Navigate to Distributed Transaction Coordinator

• In Component Services, expand Component Services > Computers > My Computer > Distributed Transaction Coordinator.

c. Configure Local DTC

• Right-click on Local DTC and select Properties.

• Under the Security tab, you'll see various options that need to be configured based on your environment.

2. Configure Security Settings

In the Security tab of the Local DTC Properties, configure the following options:

• Network DTC Access: Make sure this option is checked to allow network access for distributed transactions.

• Allow Remote Clients: Enable this if transactions will be initiated from a remote machine.

• Allow Remote Administration: This allows remote machines to manage the DTC service (optional).

• Transaction Manager Communication:

  • Allow Inbound: Check this to allow other systems to initiate DTC transactions on this server.

  • Allow Outbound: Check this to allow this server to initiate DTC transactions on other systems.

  • No Authentication Required or Mutual Authentication Required: Choose based on your network security requirements. Mutual Authentication is more secure.

• Enable XA Transactions: Check this if you're working with non-SQL databases that support XA transactions, such as Oracle.

After configuring the settings, click OK to apply the changes.

3. Set Up Windows Firewall for MSDTC

MSDTC requires certain ports to be open in the Windows Firewall. If the firewall is enabled, you'll need to create inbound and outbound rules for MSDTC communication.

a. Open Windows Defender Firewall

• Go to Control Panel > Windows Defender Firewall.

• Click on Advanced Settings.

b. Create Inbound Rules

• Select Inbound Rules > New Rule.

• Choose Predefined and select Distributed Transaction Coordinator (DTC) from the list.

• Click Next, then choose Allow the Connection, and click Finish.

c. Create Outbound Rules

• Similarly, create Outbound Rules using the same steps to ensure that MSDTC can communicate with other servers.

4. Start the MSDTC Service

• Open Services (Press Win + R, type services.msc, and press Enter).

• Find the Distributed Transaction Coordinator service, right-click it, and select Start if it is not already running.

5. Verify MSDTC is Running

You can verify that MSDTC is properly running and ready to handle distributed transactions by opening Event Viewer and checking for any MSDTC-related errors under Windows Logs > Application.

Additional Configuration (For Distributed Transactions):

If you're using distributed transactions across multiple servers (e.g., SQL Servers with linked servers), ensure that MSDTC is enabled and configured on all machines involved.

6. Configure MSDTC for Distributed SQL Server Transactions

When using Linked Servers and distributed transactions across multiple SQL Server instances, follow these steps:

a. Enable MSDTC on All Involved SQL Servers

Ensure that MSDTC is enabled and configured as described above on all SQL Server machines involved in the transaction.

b. Enable Distributed Transactions in SQL Server

You need to ensure that SQL Server is configured to allow distributed transactions.

• Open SQL Server Configuration Manager.

• Under SQL Server Network Configuration, right-click Protocols for MSSQLSERVER and ensure TCP/IP is enabled.

• In SQL Server Management Studio (SSMS), run this query to verify that MSDTC is enabled:

EXEC sp_configure 'show advanced options', 1; 
RECONFIGURE; 
EXEC sp_configure 'remote proc trans', 1; 
RECONFIGURE;

c. Verify Distributed Transactions Across Linked Servers

Once MSDTC is set up, you can test distributed transactions across linked servers by running a query that involves both servers in a single transaction:

BEGIN DISTRIBUTED TRANSACTION; 
	-- Query from Local Database 
	INSERT INTO [LocalDatabase].[dbo].[LocalTable] (Column1) 	
	VALUES ('Test Data'); 
	
	-- Query from Linked Server 	
	INSERT INTO [LinkedServer].[RemoteDatabase].[dbo].[RemoteTable] 
	(Column1) VALUES ('Test Data'); 
COMMIT TRANSACTION;

If MSDTC is properly configured, this transaction will succeed and either fully commit or fully roll back if any part of it fails.

Troubleshooting

• Firewall Issues: Ensure that port 135 and a range of dynamic ports are open between servers.

  • Ref : https://learn.microsoft.com/en-us/troubleshoot/windows-server/application-management/configure-dtc-to-work-through-firewalls

• Authentication Issues: If you're using Mutual Authentication, ensure that the machines can resolve each other's names (e.g., DNS must be configured properly).

• MSDTC Logs: Check the MSDTC logs for any issues in Event Viewer under Application Logs.

By setting up and configuring MSDTC correctly, you can ensure distributed transactions across multiple servers are handled safely and effectively.