January 27, 2002 at 3:27 pm
I'm totally out of my experience on this one so expect something dumb....
I want to keep the databases on two SQL 2K server boxes in sync ("near time" is good enough; reatime not required). Can I use any variation of SQL Replication to do this type of task? Or will I encounter more trouble than I bargained on?
TIA,
Bill
January 27, 2002 at 7:07 pm
Is one of the DBs active and the other stand-by, or are both active. Also, are you using SQL2K Standard or Enterprise Edition?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
January 27, 2002 at 9:13 pm
Both copies of SQL 2K are Standard Edition. One SQL server is the main machine that all user data is saved on ("active"?) while the other server is not accessed directly via users ("stand-by"?).
Bill
January 27, 2002 at 9:24 pm
Transactional replication should work for you. It starts with a point in time snapshot from the live database to the standby database, and from then on you're only transmitting changes, which means the latency should be fairly small.
The live database (the one in use) is the Publisher and the stand-by (the one not directly accessed) is a Subscriber.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
January 28, 2002 at 10:53 am
I agree with Brian, but you need a couple boxes to test the failover and then how you fail back. Keep in mind that replication will fail once the primary fails. The second box will pick up and run fine. BUT when you want to come abck, you will need to synch things up.
Steve Jones
February 2, 2002 at 6:09 pm
Hotek gave a decent talk at PASS about using merge replication for disaster recovery. Basically if the publisher/master crashes, you can force the subscriber to push all changes from it to the publisher after you do a restore, even though it "knows" that the changes should not be sent. Haven't tried it, but looks interesting. Of course the trade off is additional overhead/complexity for merge.
Transactional replication is easier and lighter weight in my opinion and should work well. The headache is when the publisher goes down and you're trying to figure out which data to post back from the subscriber!
Andy
February 6, 2002 at 1:59 pm
1 other major thing to keep in mind is a replication only does the data normally and not stored procedures and views. You can have stored procedures in but I cannot remember if views can be. You will need to script these and add them to the replcation copy of the database. Also user accounts will need to be set up. And you will need to make sure constraints and other important items are scripted so you can apply to the failover, or marked for replication (but then you can get into issues with that). I have suggested it before but you have to look at the entire process and see which is easier backup/restore or replicate (major advantage is speed of recovery).
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply