e enjte, 6 dhjetor 2007

My experience with SQL Server replication

Requirement: Certain windows clients running in SQL Express Edition, xyz application writing into it. Real time (latency of 10 minutes affordable, i dont know y they say its real time) data must come to a remotely located SQL Server Standard edition, now this is a centralised server.

This task came to me surprisingly (as i am not a DBA :-) )and my initial approach was Merge Replication.

Learned, replication is a bad approach due to obvious reasons as SQL Express cannot be a publisher, no SQL Agent etc.

Merge replication works with Subscriber (here SQL Express) and Publisher (here SQL Standard) where changes will could be synchronized between them, Merge Agent will upload changes and later the Synchronization.

The synchronization process brings the list of GUIDs to the Publisher and compares the list with a list of what has changed on the Publisher since the last synchronization with that Subscriber

Now here came the vilian, conflicts, struggled with this (may be coz i am not experienced in DBA) such as row updation happened to be same, key conflicts...well totally it was bad.

As i was given less time to arrive at a tested , working solution.....seeked help from an expert and he suggested to basically pull data from the clients after linking the clients to server (db links) and a small SP scheduled to run....

This however worked (not sure though this solution is scalable) as when more clients are added, schema if changed etc...

But my replication experience was not good...

1 koment:

Anonymous tha...

Could u send me more links on Merge Replication that u came across i'm new to this and need help