Bah, ar trebuii sa citesc de doua ori inainte sa postez... Am fost derutat de folosirea termenului 'sincronizare' in titlu, dar e vorba de o folosire creativa a unui termen consacrat ;)
Modul ideal de trimitere de date dinspre/spre SQL Express este Service Broker. Creeaza un serviciu in SQL Express si unul in baza de date centrala, foloseste tool-ul de la www.codeplex.com/slm pentru configurare, si urmeaza pattern-ul descris in http://blogs.msdn.com/remusrusanu/archive/2007/04/24/reusing-conversations.aspx pentru a trimite mesajele. Mesajele in general sint create intr-un trigger si processate ca XML.
Stiu ca in general e o chestia destul de noua si putzina lume are know-how in domeniu, asa ca mai bine pun un exemple. O sa creez doua tabele, a si b, si vreau ca orice operatie in a sa fie replicata in b (pentru simplificare o s a fact totul local in tempdb).
use tempdb
GO
-- sender service, will be used by trigger to send FROM
--
create queue q
create service s on queue q
GO
-- 'replicated' table
--
create table a (a int primary key);
go
-- 'replication' trigger. creates a datagram from the INSERTED and DELETED pseudotables
-- uses a dumb one message per dialog
-- see http://blogs.msdn.com/remusrusanu/archive/2007/04/24/reusing-conversations.aspx
-- for a proper way of using dialogs
--
create trigger a_t
on a for insert, update, delete
as
BEGIN
DECLARE @payload XML, @h UNIQUEIDENTIFIER;
WITH XMLNAMESPACES (DEFAULT 'htpp://tempuri.org/2007/05/04/sqlserver.ro/rremus')
SELECT @payload = (SELECT
(SELECT * FROM DELETED FOR XML PATH('DELETED'), TYPE),
(SELECT * FROM INSERTED FOR XML PATH('INSERTED'), TYPE)
FOR XML PATH('DATAGRAM'), TYPE);
BEGIN DIALOG CONVERSATION @h
FROM SERVICE s
TO SERVICE 't'
WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @h (@payload);
END
GO
-- Test the trigger
--
insert into a values (1)
go
-- We sent but the target doesn't yet exists.
-- message is pending in xmit queue
--
select cast(message_body as xml),* from sys.transmission_queue
go
-- create a target service
--
create queue qt
create service t on queue [qt] ([DEFAULT]);
go
-- create a 'replica' table
--
create table b (a int);
GO
-- this procedures shreds the XML datagrams into
-- deletes/inserts into the 'replica' table
-- a proper SSB procedure needs to take care
-- of properly ending the dialogs:
-- see http://blogs.msdn.com/remusrusanu/archive/2007/05/02/recycling-conversations.aspx
-- Also this procedure is highly inneficient,
-- see http://blogs.msdn.com/remusrusanu/archive/2006/10/14/writing-service-broker-procedures.aspx
--
create procedure usp_t
AS
BEGIN
DECLARE @h UNIQUEIDENTIFIER, @mt SYSNAME, @mb XML;
SET NOCOUNT ON;
BEGIN TRANSACTION;
RECEIVE TOP(1)
@h = conversation_handle,
@mt = message_type_name,
@mb = CAST(message_body as XML)
FROM qt;
IF @@ROWCOUNT>0
BEGIN
IF @mt = N'DEFAULT'
BEGIN
WITH XMLNAMESPACES (DEFAULT 'htpp://tempuri.org/2007/05/04/sqlserver.ro/rremus')
DELETE FROM b
WHERE b.a IN (
SELECT t.n.value('a[1]', 'int') as a
FROM @mb.nodes('//DATAGRAM/DELETED') T(n));
WITH XMLNAMESPACES (DEFAULT 'htpp://tempuri.org/2007/05/04/sqlserver.ro/rremus')
INSERT INTO b (a)
SELECT t.n.value('a[1]', 'int') as a
FROM @mb.nodes('//DATAGRAM/INSERTED') T(n);
END
END
COMMIT
END
go
-- Associate the procedure with the queue
--
alter queue qt
with activation (
status = on,
max_queue_readers = 1,
procedure_name = [usp_t],
execute as owner);
go
Odata ce functioneaza scenariul local, potzi muta serviciul target pe serverul central, configura securitate si transport (cu www.codeplex.com/slm). Evident, procesarea de XMl trebuie sa corespunda cu schema tabelelor tale.
Acest pattern este foarte des folosit de customeri pentru real-time ETL (Extraction-Transfomation-Load) ca sa incarce datele din OLTP in DW pentru analiza si reporting. In general se interpune si un pas de SSIS pentru transformare, dar in cazul tau ai intrebat cum sa trimitzi efectiv replicare de date, fara transformare.