Ok, third time lucky on posting this message, god knows what's going on with the lists ;-)
Hi Jason, I've little experience with DB replication but from the description of your problem and your issues replicating identity columns my suggestion would be to move away from them. You really have a couple of options, the first of which is to use ranges of your primary keys, so server one gets 1-10,000 and server 2 gets 10,001 through to 20,000 etc etc, this is good because the server continues to manage the ID for you but it probably wont scale all too well :-s all depends on the data growth you're likely to see, is your data consistent of does it continue to grow? The second option and my preferred choice is to use G/UUIDs as your primary keys having the applications generate them upon insert. This is quite common practice and I use this on several of my projects here with great success (no replication but LOTS of data imports which is a similar process), do you think that would go some way to solving your issues? Ensuring that the primary key on the table is universally unique regardless of where the data is coming from certainly sounds like something you should be looking towards for the new project. Just a thought. :-) Rob > I'm coming up short looking for some information. I have a version 1 > product that when we got around to implementing sql 2000 merge > replication, it turned out to be a nightmare due to the database > design, which was fine for single-server, but we just did too many > things along the way that don't work well for merge replication. > Specifically, identity fields are handled terribly, and I don't blame > MS, as it's a tricky problem, but I need to be able to insert new data > on either server and have it replicate. > > We're about to start version 2, from scratch, and we really want to do > this right. We would like a list of best practices for the DB DESIGN > portion. There is a wealth of material about administration, but > that's not quite what I want. I would settle for a list of things not > to do. > > And specifically regarding identity fields, I would like to avoid them > completely this time. What is the best way to handle primary keys? We > will probably have 80-100 tables and several million records total, in > case it matters. We will also consider using SQL Server 2008 or > perhaps Postgres if a strong case can be made, but 2008 is very new, > and we don't have much experience with Postgres. > > Any and all advice is appreciated. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3185 Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
