Freigeben über

Shipping rowsets across databases using Service Broker

  • Artikel

[Attachment: ShippingRowsets.zip]

While there are technologies such as log shipping, SQL Server Replication Service and database mirroring available for various scenarios that require maintaining a copy of the data in a secondary source, several customers have asked how they could use Service Broker for securely and reliably sending tables or rowsets from one database to another. Since there were no examples available for demonstrating how this task can be performed I decided to cook up a very simple sample. In this sample, we will use the FOR XML clause to convert a rowset into an XML document, send it from one database to another (which could be possibly residing on a different SQL Server instance and on anoher machine) using Service Broker, receive the XML message at the target and convert it back to a rowset using OPENXML to insert it into a table. What is really cool about this sample is that it is entirely written in T-SQL and does not require any magic from CLR. Also this is a very basic example and does not fully demonstrate the power of nested FOR XML queries and XQuery which are both available in SQL Server 2005.

The database holding the original table is called primary_db and it contains a table called contacts which looks as follows:

We can obtain an XML document of this table by using the FOR XML clause with the SELECT statement as follows:

   SELECT * FROM CONTACTS FOR XML AUTO, TYPE, ROOT('phoneBook')

The result looks as follows:

We can create an XML valued variable in T-SQL and set this to the XML above as follows:

   DECLARE @body AS XML;
SET @body = (SELECT *
FROM CONTACTS
FOR XML AUTO, TYPE, ROOT('phoneBook'));

The XML document can be shipped from one database to another database using Service Broker as shown in this schematic diagram:

We can use this XML variable as the message body in the SEND statement to send the XML dataset from the initiating service in the local database to the target service in some remote database as follows:

   DECLARE @dh UNIQUEIDENTIFIER;
BEGIN TRANSACTION;
BEGIN DIALOG @dh
FROM SERVICE replication_client
TO SERVICE 'replication_service'
ON CONTRACT replication_contract;
SEND ON CONVERSATION @dh MESSAGE TYPE insert_rowset
(@body);
COMMIT;

On the receiver's side, we can convert the XML message back into a rowset and insert the rows into a table as follows:

   DECLARE @body XML;
DECLARE @docHandle INT;
BEGIN TRANSACTION;
RECEIVE TOP(1) @body=message_body FROM service_queue;
EXEC sp_xml_preparedocument @docHandle OUTPUT, @body;
INSERT INTO contacts
SELECT * FROM OPENXML(@docHandle, N'/phoneBook/contacts')
WITH CONTACTS;
EXEC sp_xml_removedocument @docHandle;
COMMIT;

The attached file (see top of the post) contains T-SQL scripts for creating two databases -- [primary_db], the database containing the original [contacts] table and [secondary_db], the database containing the replicated contacts table. [secondary_db] contains a service called [replication_serivce] that implements the [replication_contract]. The queue bound to the replication_service has an internally activated stored proc that receives messages and if they are of the [insert_rowset] message type then it converts the XML body into a rowset and inserts it into the [contacts] table. The [primary_db] database also contains a stored proc called [sp_ship_new_rows] that looks for new rows added since the last time rows were shipped and sends them to the [replication_service] service from a local service called [replication_client].

Exercise: The internally activated stored proc is not too falt tolerant. For example, if the incoming message contains duplicate rows, the INSERT would fail resulting in the stored proc to be rolledback. Modify the stored proc to handle such exceptions and error the dialog if caught.

Comments