January 13, 2010 at 8:06 pm
Does anybody know of an easy way to make a replicated copy of a database a master copy? Essentially what I would like to do is that in the event of a failure of our master (or published) database we would like to point our applications to the replica (transactional replication subscriber). I did some testing today and ran into a bunch of foreign key errors and identity issues on running inserts. Updates seemed to work though. Has anybody done this or should I not even go down this path and look to use mirroring or log shipping in the event of a failure? I'm kind of hoping there's some sort of a sp_configure or something that I can run against the database to make it not think that it's a replica.
January 14, 2010 at 12:44 pm
If you intend to provide HA to your principal database then I would like to say that Replication is NOT a HA solution. It is used to reduce the overhead on your principle by distributing copies of your articles to various subscribers.
Instead, using Log Shipping or DB Mirroring is a much better option for what you intend to do in case of a failover.
Will the copy of primary database reside in your local environment or at a remote location?
HTH...
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
January 14, 2010 at 8:48 pm
I know the configuration is not optimal however it's what I have at the time. In any event, I found what I need to do to make a replica active. Essentially I just need to reseed all of the tables by running sp_MSForEachTable 'dbcc checkident (''?'', reseed)' before I point any of the applications to the DB.
January 15, 2010 at 11:24 am
FWIW, I like to think of all the replication alternatives and group them by granularity. I find it easier to narrow down the potential solutions that way.
In your case, as was stated log shipping and mirroring fit better because it looks like you want to copy the entire database. Those 2 technologies were built specifically for that purpose. Replication & DTS/SSIS, are built for a table/article granularity level. Certainly you *can* replicate 100% of the database, but I think that indeed log shipping and mirroring might be better options for you.
Good luck with everything and let us know how it all comes out...Troy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply