July 6, 2004 at 7:13 am
Hi... have never needed to use SQL replication, until now... Here's my situation. I have three IIS/SQL servers, call them A, B and C for simplicity. Each runs a web app with the same code and DB structure. Each also runs on their own closed network servicing a distinct community of interest. That is, users of "A-Net" will do their data updates on server A, users of "B-Net" on server B, etc. However, the data in each of the A, B, and C databases needs to be sync'ed together. This sync should happen as often as practical (doesn't need to be real-time) but probably at least every hour. After the sync is done all three DBs should contain the same data, no matter on what server it was originally entered.
I know I'm looking at either merge or transaction replication (thinking merge is what I want). I have my DB designed so that the ID fields of my tables are all uniqueidentifiers, except for lookup tables where I may have PKeys that are varchars (like state codes -- varchar(2)). Some of my tables are join tables where the PK is a combo of two uniqueidentifiers. Being a newbie at this I see where merge replication would add uniqueidentifier fields to tables if it needed to. Is it going to slap these on my lookup tables and other tables where PKs aren't a single uniqueidentifier?
Any help, advice, etc on getting started with this would be much appreciated. BTW -- please don't advise on changing server configs. We have to stick with the separate servers for all three nets, although we may end up with dedicated IIS and SQL boxes (i.e. two servers per net). TIA! Steve
July 6, 2004 at 5:38 pm
If you have placed UniqueIdentifier COlumns on all your tables then Merge Replication will not duplicate that! However, it will put UniqueIdentifier Columns on your lookup tables as well. Don't worry about the UniqueIdentifier Columns, they rae only used by the Replication Process.
Whether you implement Merge Replication or Transactional Replication with Immediate Updating Subscribers, you will have to deal with Identity Columns! You will have torange your Identity seeds and you will also have to specify the NOT FOR REPLICATION on all Identity Columsn so that seed remains the same!
July 7, 2004 at 5:00 am
I would use merge replication in this case. You may also want to consider starting the identity seeds for A and B differently to prevent conflicts from occuring during the merge. A's seed might start @ 1 and increment by 2 and B might start @ 2 and increment by 2. By using the differing seeds and the Not For Replication option, as mentioned in previous post, you will avoid some problems.
July 7, 2004 at 6:39 am
Thanks for the advice... I have no identity columns left though -- replaced them all with uniqueidentifiers that get seeded by NEWID(). Will delve into some articles I have on merge replication. thanks again.
July 7, 2004 at 6:00 pm
Are you syaing that you are now using UniqueIdentifier columns as your Primary Key? If so, it would be benficial for you to read up on why this is a BAD idea! UniqueIdentifier as a Primary Key is to wide! You will ,ost likely experience performance issues when your queries are joining onto multiple tables to retrieve data.
July 8, 2004 at 6:43 am
From what I've seen there are many different views on this subject. Yeah, I realize there may be some performance loss but I have yet to experience any, so weighing the pros and cons, since I need unique primary keys across three servers, this was the best way for me to go.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply