April 4, 2011 at 3:55 am
We have OLTP application that connects to SQL server. We have 2 database servers as primary and backup server. The application connects to primary server. Currently, we do transactional replication to backup server to sync between servers. To switch to backup server, we need to stop the replication first and connect the application to the backup server. Now, the backup server data becomes new and we need to push it to primary server to sync.
What is the best way to sync both servers all the time? Does Transactional replication with updatable subscription work in this scenario? Any ideas or suggestions? We are using SQL 2005 SP3 Std edition.
Thanks
April 4, 2011 at 4:37 am
Database mirrroring is a good option and alternate for replication. You may choose the SAFETY option, which will cause synchronous mirroring (and transactions on Primary will not be committed unless they are not committed on Secondary). OR you may go in for without SAFETY, in which there is a small delay between the primary & secondary. What to choose will depend upon your disaster recovery policies.
Mirroring also allows you automatic failover in case the partner goes down. The only thing is that your applications must use the "Failover Partner" parameter in their connection strings. You can refer the quick reference sheet from my article on Connection Strings at http://www.sqlservercentral.com/articles/Connection+Strings/71669/[/url] (applicable to all ADO/ODBC and OLE DB)
You can get more information about mirroring from: http://technet.microsoft.com/en-us/library/cc917680.aspx
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
April 4, 2011 at 5:53 am
Thanks Nakul. I will discuss with our team for the possibilities to implement DB mirroring.
April 12, 2011 at 6:07 pm
Hi Moorthy,
Yes using DB mirroring with replication is a good option as mirroring supports transaction replication with automatic failover if you do mirroring on publisher database.
Plz go through th BOL article for detail Replication and database mirroring -
-MJ
Please do not print mails and docx unless it is absolutely necessary. Spread environmental awareness.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply