Link to home
Start Free TrialLog in
Avatar of Dexstar
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*
Avatar of danblake
danblake

1) Data is entered/updated at the main DB, and pushed out to the remote DBs.
-- 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

Avatar of Dexstar

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*
Avatar of Dexstar

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.
ASKER CERTIFIED SOLUTION
Avatar of danblake
danblake

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Dexstar

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*