Dexstar
asked on
Need Replication Input / Advice
My DB project is part of a commerical application, and I need to extend it to allow for replication. Due to the complex nature of the database, I'm beginning to think that using the built-in Replication functions and wizards are not going to cut it.
I need to set up something where there is one main DB and a bunch of smaller "remote" databases. The tables essentially fall into one of 3 groups:
1) Data is entered/updated at the main DB, and pushed out to the remote DBs.
2) Data is entered/updated at either the main DB or a remote DB and needs to be replicated to all DBs.
3) Data is entered/updated at a remote site and needs to be replicated to the main DB, but not to any of the other remote DBs.
Here's the question(s): Will the built-in replication features work for this, or do I need to roll my own? If they will work, how should I set it up? If they won't work, what sort of thing do I need to do?
I was thinking about using something like this: http://www.winnetmag.com/SQLServer/Article/ArticleID/8759/ for at least the #1 set of tables, but the problem is that the server name could change for each customer. Is there a way to do something like that for variable server names without using dynamic SQL?
I'm pulling out my hair over this. I promise points to anyone who contributes to helping me arrive at a solution.
Dex*
I need to set up something where there is one main DB and a bunch of smaller "remote" databases. The tables essentially fall into one of 3 groups:
1) Data is entered/updated at the main DB, and pushed out to the remote DBs.
2) Data is entered/updated at either the main DB or a remote DB and needs to be replicated to all DBs.
3) Data is entered/updated at a remote site and needs to be replicated to the main DB, but not to any of the other remote DBs.
Here's the question(s): Will the built-in replication features work for this, or do I need to roll my own? If they will work, how should I set it up? If they won't work, what sort of thing do I need to do?
I was thinking about using something like this: http://www.winnetmag.com/SQLServer/Article/ArticleID/8759/ for at least the #1 set of tables, but the problem is that the server name could change for each customer. Is there a way to do something like that for variable server names without using dynamic SQL?
I'm pulling out my hair over this. I promise points to anyone who contributes to helping me arrive at a solution.
Dex*
ASKER
@danblake:
For #1, the data will change very infrequently. I don't think Snapshot replication is good in that case because it seems to copy the entire table each time. The custom replication run a couple of times a day will just send enough data to check for any changes. Or am I wrong?
At least some of the remote sites will be using MSDE. I know MSDE can't be a Publisher, but does that mean it can't participate in two-way replication?
Dex*
For #1, the data will change very infrequently. I don't think Snapshot replication is good in that case because it seems to copy the entire table each time. The custom replication run a couple of times a day will just send enough data to check for any changes. Or am I wrong?
At least some of the remote sites will be using MSDE. I know MSDE can't be a Publisher, but does that mean it can't participate in two-way replication?
Dex*
ASKER
Also, I forgot to mention this:
All the tables have a TIMESTAMP field, plus these 2:
Created (DATETIME) = Set to GETDATE() when created
Updated (DATETIME) = Set to GETDATE() when updated (via a trigger)
Any table that falls in Category #1 above uses an IDENTITY as the primary key. Anything that falls in #2 or #3 uses a UNIQUEIDENTIFIER.
All the tables have a TIMESTAMP field, plus these 2:
Created (DATETIME) = Set to GETDATE() when created
Updated (DATETIME) = Set to GETDATE() when updated (via a trigger)
Any table that falls in Category #1 above uses an IDENTITY as the primary key. Anything that falls in #2 or #3 uses a UNIQUEIDENTIFIER.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Okay, I'm going to close this question because I've got 2 others on the same topic open, and this one is kind of dead. Thanks, Dan, the points are yours.
Dex*
Dex*
-- Tran or snapshot replication built-in feature will work.
2) Data is entered/updated at either the main DB or a remote DB and needs to be replicated to all DBs.
-- Merge replication will work for this (two-way).
3) Data is entered/updated at a remote site and needs to be replicated to the main DB, but not to any of the other remote DBs.
-- Merge replication --> (one-way).
A quick look at this article the custom replication may need to transfer all the indexes to a central server to resolve the differences(based on this article), with merge replication resulting in high network i/o.
The log reader agent will handle this within the merge replication and allow for down-time errors and just resolve the changes that are within the transaction logs of each remote machine.
There are some good replication resources to be found here:
http://www.sqlservercentral.com/articles/articleList.asp?categoryid=25