June 21, 2015 at 3:11 am
Good Day Folks,
I am new to SQL server central and I am in a bit of a dilemma. I have been asked to implement a replication between 2 sql servers, whereas when the Primary Server is down, It will redirect to the second server (that contains all the recent data from the primary server) and when the primary server is up, that secondary server will again synchronize the data to the primary server so when the users access the web application on the primary server, the data will be the recent one. I have read that Transactional replication is the way to go and after that make it a peer to peer replication. But then I am using SQL Server 2008 R2 standard edition, so the peer to peer is not available on my edition. I have read that this also can be done thru mirroring but I do not know if my requirement on the failover scenario will be catered. I need expert advise on this one because my hair is about to run out :hehe:
Thanks,
Chris
June 21, 2015 at 7:47 am
Hi,
Transactional Replication will not help you in this case.
Transactional Replication will move the data from the primary to the secondary, but not the other way around if the primary fails.
You'll be able to achieve that using Synchronous Mirroring with automatic failover..
June 21, 2015 at 11:25 pm
Thank You, I will look into this solution and try my best to understand and implement it on my scenario.
June 22, 2015 at 1:36 pm
Note though that with Database Mirroring of you want automatic failover you need a third SQL Server to act as a witness.
Joie Andrew
"Since 1982"
June 22, 2015 at 11:35 pm
As for the third database server, can I just use the mirror as a witness also? Should the witness be installed on a different instance?
June 23, 2015 at 12:00 am
As for the third database server, can I just use the mirror as a witness also? Should the witness be installed on a different instance?
I think the witness has to be on a separate instance. As far as the server is concerned I think it can reside on the same server as the primary or mirror, but shouldn't. Remember that the job of the witness is to setup quorum with the other servers involved in mirroring. Kind of pointless to put it on the same server as one of the instances involved in mirroring is on since if you loose that one server quorum is lost.
Quorum: How a Witness Affects Database Availability
Joie Andrew
"Since 1982"
June 23, 2015 at 1:30 am
Noted with thanks on that one. I have now implemented mirroring without automatic failover. But now, I should do an automatic failover because this is what was required, Assuming the failover succeeded, all the users are redirected to the Mirror, and when the Principal server goes back online. Will the data changed on the mirror server be synchronized back to the principal server automatically? Or should I do it manually? I am very grateful to the advises and help that I am getting.
June 23, 2015 at 2:08 am
Two things to note about this:
- Has the connection string the application uses to connect to the database changed to support mirroring? If not the application will not automatically redirect traffic
- Once the automatic failover occurs the "mirror" is no longer the mirror, it is the principal. Once the old principal comes back online it will be referred to as the mirror. You will need to schedule time and do a manual failover to fail back to the old instance as the primary. This is by design as to not cause further outages when your old primary comes back online
Joie Andrew
"Since 1982"
June 24, 2015 at 1:45 am
It seems that my main question plus a lot more has been answered, thank you for saving me time. We are now testing the failover scenarios and adding the parameter "Failover Partner=[Mirror ServerName]" to the application connection string.
Thank you very much for all of your help. I am now reading database mirroring best practices from here https://technet.microsoft.com/en-us/library/cc917681.aspx.
Once again I am very grateful to you guys.
June 24, 2015 at 9:39 am
alex_fajardo2002 (6/23/2015)
Will the data changed on the mirror server be synchronized back to the principal server automatically? Or should I do it manually? I am very grateful to the advises and help that I am getting.
If the mirror session is set to synchronous then there will be no data loss, the new principal will replay any logged transactions when it starts up
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply