April 25, 2007 at 1:43 pm
I am planning to setup transactional replication and would need suggestions as this is the first time i am doing it.
I have a Primary Server (Server A) and Secondary Server (Server B). I want both the servers to be in synch all the time which can be accomplished by Transactional replication.
I want to use the secondary server as just a fail over. When the Primary server is down then we can switch the application to the secondary with no or minimal downtime.
Here is what i am planning to do (Correct me if i am wrong)
Primary Server - Publisher and the Distributor
Secondary Server - Subscriber
Using Push Subscription
I have few questions here
1. When the primary is down, secondary would be pointed to application, so once the primary is up will the transactions in secondary would be pushed to primary and will both the servers will be in synch?
2. Do i need to create a distributor in both the servers.
Thanks
April 27, 2007 at 6:00 am
First, transactional replication is not a replacement for clustering - which is what you really want to do. It is not a great failover solution since it cannot automatically redirect your applications, deal with the primary server coming back up, and does not guarantee you will have data loss.
That being said, I would recommend you use a third server as your distributor. If you are using a push subscription with the primary server as your distributor, when your primary server goes down, the distributor has gone down and can therefore not do anything. If you use another server as your distributor, it can at least notify you so you can handle the failure. If you are trying to do this cheaply, make the secondary server the distributor and use pull subscriptions, but remember that the distributor does not have to be a stellar piece of hardware - so spend $1000 and buy one. This will also put all of the replication agents on the distributor or secondary server that would probably be idle otherwise (why tax your production OLTP server if you don't have to?).
Then, to deal with the primary server coming back up, if you have the same hardware on both servers, who's to say which one is the primary server? Have your distributor reverse your replication permanently on a failure and make the working server the primary one. Once the secondary server is back up, run the snapshot agent and you are back where you started. It is best to not have your failover solution to force additional down time once hardware has been replaced.
Do remeber this - transactional replication does not mean the replication data is part of the original transaction. On a failure, you could have data loss if something was written to the transaction log and the failure happened before the log reader picked it up and replicated it.
Lastly, if you have not worked with replication in the past, do not feel bad about getting some help. On the surface, it looks easy to set up, but you can quickly find yourself in a mess that causes performance problems and does not do what you want anyway.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply