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() &lt;&gt; 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 data that could potentially be an issue for converting to a text-based format, especially one that has certain characters reserved and hence need to be escaped if present in the data so as to not cause errors.



-- DROP TABLE #BadValues;
CREATE TABLE #BadValues (Col1 NVARCHAR(15), Col2 GEOMETRY, Col3 XML,
                         Col4 SQL_VARIANT, Col5 VARBINARY(MAX));
INSERT INTO #BadValues (Col1, Col2, Col3, Col4, Col5)
  VALUES (N'" =  &amp; &lt; ', 'LINESTRING(11 10, 20 20)', '&lt;test/&gt;', 23, 0x00125DFFFF);


If anyone is wondering where I got the  symbol from, it is a supplementary character that is part of UTF-16 but not in the base UCS-2 code points / characters. I created by doing the following:

SELECT NCHAR(150150) -- returns "" in a DB with a collation ending in "_SC", else NULL


Now, look at what it returns normally:

SELECT *
FROM   #BadValues;

-- " =  &amp; &lt; 
-- 0x0000000001140000000000002640000000000000244000000000000034400000000000003440
-- &lt;test /&gt;
-- 23
-- 0x00125DFFFF


Great. Now let's throw in the FOR XML RAW clause and see what happens:

SELECT *
FROM   #BadValues
FOR XML RAW;

/*
Msg 6865, Level 16, State 1, Line 1
FOR XML does not support CLR types - cast CLR types explicitly into one of the
supported types in FOR XML queries.
*/


Ok. So we need to convert the GEOMETRY field to something usable. All CLR types (whether custom UDTs or supplied by Microsoft) should have a .ToString() function, so let's try that:

SELECT Col1, Col2.ToString() AS [Col2stringified], Col3, Col4, Col5
FROM   #BadValues
FOR XML RAW;

/*
Msg 6829, Level 16, State 1, Line 1
FOR XML EXPLICIT and RAW modes currently do not support addressing binary data as
URLs in column 'Col5'. Remove the column, or use the BINARY BASE64 mode, or create
the URL directly using the 'dbobject/TABLE[@PK1="V1"]/@COLUMN' syntax.
*/


Well, we got passed the CLR-type error, only to get an error on the VARBINARY field. But it suggests that we use "BINARY BASE64" mode, so let's try that:

SELECT Col1, Col2.ToString() AS [Col2stringified], Col3, Col4, Col5
FROM   #BadValues
FOR XML RAW, BINARY BASE64;

/*
&lt;row Col1="&amp;quot; =  &amp;amp; &amp;lt; " Col2stringified="LINESTRING (11 10, 20 20)" Col4="23"
     Col5="ABJd//8="&gt;
  &lt;Col3&gt;
    &lt;test /&gt;
  &lt;/Col3&gt;
&lt;/row&gt;
*/


Not so bad. This just means that if you have a CLR type -- GEOMETRY, GEOGRAPHY, HIERARCHYID, or a custom UDT -- then you cannot use SELECT * when packaging up the records in the Inserted pseudo-table. In those cases you need to explicitly list out the columns so that you can apply .ToString() to any fields that are of a CLR type.

Please note that this is only when using the XML method of transporting the data. If you did this in SQLCLR using the SqlBulkCopy class (as noted above in a couple of places), then that might transfer directly (though I haven't tried it yet).



-- UPDATE Dos punto Uno --

Continuing the test from the prior Update Dos above, the following (using the same temp table and test row) shows the process of converting the initial table to XML and then to NVARCHAR(MAX), then pass that to a stored procedure that will convert it back to XML and then SELECT from that using the .nodes() function.

DECLARE @RowsToSend NVARCHAR(MAX);

SET @RowsToSend =
(
  SELECT Col1, Col2.ToString() AS [Col2stringified], Col3, Col4, Col5
  FROM   #BadValues
  FOR XML RAW, BINARY BASE64
);

SELECT @RowsToSend;
--=====--   here would be the call to the remote procedure, which would do the following:
DECLARE @RowsToInsert XML;

SET @RowsToInsert = CONVERT(XML, @RowsToSend);

SELECT @RowsToInsert;

SELECT c.value(N'@Col1', N'NVARCHAR(15)') AS [Col1],
       CONVERT(GEOMETRY, c.value(N'@Col2stringified', N'VARCHAR(MAX)')) AS [Col2],
       c.query(N'(./Col3/*)') AS [Col3], -- use .query() to get sub-element
       c.value(N'@Col4', N'INT') AS [Col4],
       c.value(N'@Col5', N'VARBINARY(MAX)') AS [Col5]
FROM   @RowsToInsert.nodes(N'/row') t(c);