Routing data from one SQL Server to another

Is there a way in SQL Server 2012 to have data being written to one table routed to another SQL Server on another host?

To clarify, we are using Hibernate Envars to write audit logs. Hibernate Envars does not allow this data to be sent to another server instead of the server where the audit log was created by Hibernate.

I'd like for these audit logs to be sent to another server. Is there a way to configure SQL Server to write specific tables to another server?

Yes, I can actually think of three ways to accomplish this (well, 4 if you include the Update section ;-). All three methods use an INSTEAD OF INSERT Trigger on the AuditLog table.

For all three methods, first create the AuditLog table on the remote instance (this should be obvious, but stated for completeness).

Option #1

• Set up a Linked Server to the server that you want the data to go to.

• Create a Stored Procedure on the remote server that:

• accepts an NVARCHAR(MAX) input parameter

• converts the input parameter to XML stored in a local variable

• inserts into the AuditLog table from a SELECT statement that parses the XML variable using FROM @XmlVariable.nodes()

-
Create the INSTEAD OF INSERT Trigger on the local instance in which it does something along the lines of:

DECLARE @RowsToSend NVARCHAR(MAX);
SET @RowsToSend = (
SELECT *
FROM INSERTED
FOR XML RAW
);
EXEC [LinkedServerName].[DatabaseName].[SchemaName].[StoredProcedureName]
@RowsToSend;


• The reason for using the Stored Procedure instead of a straight INSERT statement is that DML statements over Linked Servers have definite performance issues (I don't remember the exact cause(s), but I recall there being a problem with them reusing cached plans or something related to execution plans).

Option #2

• Create a SQLCLR INSTEAD OF INSERT Trigger

• Create a SqlConnection using "Context Connection = true;"

• Create a SqlCommand with a CommandText of SELECT * FROM Inserted;

• Create a SqlDataReader via SqlCommand.ExecuteReader();

• Use SqlBulkCopy (String, SqlBulkCopyOptions) with a regular / external connection string to the remote instance

• Call SqlBulkCopy.WriteToServer(SqlDataReader)

• Since this makes a connection to a remote instance, the Assembly needs WITH PERMISSION_SET = EXTERNAL_ACCESS

• Please do not set the database to TRUSTWORTHY ON in order to use EXTERNAL_ACCESS. Instead:

• Sign the Assembly / DLL

• Create an Asymmetric Key in the [master] database from the DLL

• Create a Login from that Asymmetric Key

• Grant the new Login the EXTERNAL ACCESS ASSEMBLY permission

Option #3

• Set up Service Broker to send messages from the local Instance to the remote Instance

-
Package up the rows in a similar manner to how it was done in Option 1:

DECLARE @RowsToSend NVARCHAR(MAX);
SET @RowsToSend = (
SELECT *
FROM INSERTED
FOR XML RAW
);

SEND ... @RowsToSend;


-
See SQL Server Service Broker for details.

• This option requires a little more setup than the other two options, but is better in terms of making the data transfer asynchronous (hence this is better if there is a high volume of inserts of logging data and/or there is latency on the transfer with the other two options as that would slow down the operations generating the log data).

-- UPDATE Uno --

Option #4

• A variation of Option #1 that I have used before (but somehow forgot about until reading @datagod's answer) is to move the data over in batches all throughout the day.

• Create the same Stored Procedure on the remote Instance that receives an NVARCHAR(MAX) input parameter, converts that to XML, and then inserts that via INSERT INTO dbo.AuditLog SELECT c.value('@field1', 'type'), ... FROM @XmlVariable.nodes('/row') t(c);

-
Instead of creating an INSTEAD OF INSERT Trigger, create a Stored Procedure on the local instance that transfers the data along the lines of:

SET XACT_ABORT ON;

BEGIN TRY

BEGIN TRAN;

-- create temp queue table dynamically to adjust for schema changes
SELECT * INTO #TempRows FROM @b WHERE 1 = 0;

-- delete rows first to ensure they don't get transferred again
DELETE tmp
OUTPUT DELETED.*
INTO #TempRows
FROM dbo.AuditLog tmp;

DECLARE @RowsToSend NVARCHAR(MAX);
SET @RowsToSend = (
SELECT *
FROM #TempRows
FOR XML RAW
);

EXEC [LinkedServerName].[DatabaseName].[SchemaName].[StoredProcedureName]
@RowsToSend;

COMMIT TRAN;
END TRY
BEGIN CATCH
IF (XACT_STATE() <> 0)
BEGIN
ROLLBACK TRAN;
END;

THROW;
END CATCH;


-
Create a SQL Agent Job to schedule the Stored Procedure above to run every few minutes

• This has the asynchronous benefit of the Service Broker approach, but with a little less setup.

• With regards to performance: I have used this method for years to move several million rows per day. If you need to move 20 million rows, or more, per day then maybe replacing the XML packaging here with the SqlBulkCopy via SQLCLR approach of Option 2 would be good to try because it would allow you to pass the local data to the remote Instance as a TVP. But otherwise this approach holds up quite well, especially if you stick with the attribute-based XML as I have shown here, which parses faster than element-based XML.

-- UPDATE Dos --

Regarding the ability of FOR XML RAW to handle all possible datatypes and data, I did the following testing:

First, create a test temp table that has columns and dat

StackExchange Database Administrators Q#112424, answer score: 5