How to restore a SQL Server Mirror Partner?
Points of My Scenario:
1. I am both Windows admin and SQL Server sysadmin of two SQL Server 2016 servers.
2. Databases on Server-A are mirrored to Server-B: the servers are at different sites connected by WAN link .
3. Server-B needs to be replaced due to OS corruption that not even Microsoft can resolve.
4. The databases are too large to backup and send from Server-A to a new mirror partner server (e.g. Server-C).
5. I need the new server (Server-C) to use the mirrored database files on the problem Server-B to re-establish the mirror.
Note 1: Server-B is still running, but crashes regularly (2 - 3 times daily), and management wants it to be replaced before it stops running altogether.
Note 2: Server-C will need to be configured with the NetBIOS name of Server-B so that applications at the site will work if/when we fail-over.
QUESTION: How can I make a new server (Server-C) into a mirror partner for Server-A, using the database files from Server-B?
1. I am both Windows admin and SQL Server sysadmin of two SQL Server 2016 servers.
2. Databases on Server-A are mirrored to Server-B: the servers are at different sites connected by WAN link .
3. Server-B needs to be replaced due to OS corruption that not even Microsoft can resolve.
4. The databases are too large to backup and send from Server-A to a new mirror partner server (e.g. Server-C).
5. I need the new server (Server-C) to use the mirrored database files on the problem Server-B to re-establish the mirror.
Note 1: Server-B is still running, but crashes regularly (2 - 3 times daily), and management wants it to be replaced before it stops running altogether.
Note 2: Server-C will need to be configured with the NetBIOS name of Server-B so that applications at the site will work if/when we fail-over.
QUESTION: How can I make a new server (Server-C) into a mirror partner for Server-A, using the database files from Server-B?
Setup server B. Copy accross a recent backup of the database/s from server A. restore to a point in time while leaving the DB in recovery
configure the mirror.
If your OS is corrup, I would not trust that as you never know whether the mirroring seized and they were out of sync. ...
Why use mirroring? look at whether Allways on is an alternative available to you. in a mirrored state server B's sql can only be used if it is an enterprise version and only if you take a snapshot of the existing mirror's state for purposes such as generating reports .....
backup\restrore to recovery is the only option available to you.
distance between the two location? If you are setting up server C. why not set it up on the same premises where Server A is using log shipping
preserving the transaction logs on server A and transferring /restoring them when server C is on site while leaving the DB in recovery mode, and then setting up the mirroring.
Another backup server A DB to an external storage/USB. transport it to where server C is restore from USB while in recovery and then see if there are transaction logs since to get the DB caught up before trying the mirror setup...
configure the mirror.
If your OS is corrup, I would not trust that as you never know whether the mirroring seized and they were out of sync. ...
Why use mirroring? look at whether Allways on is an alternative available to you. in a mirrored state server B's sql can only be used if it is an enterprise version and only if you take a snapshot of the existing mirror's state for purposes such as generating reports .....
backup\restrore to recovery is the only option available to you.
distance between the two location? If you are setting up server C. why not set it up on the same premises where Server A is using log shipping
preserving the transaction logs on server A and transferring /restoring them when server C is on site while leaving the DB in recovery mode, and then setting up the mirroring.
Another backup server A DB to an external storage/USB. transport it to where server C is restore from USB while in recovery and then see if there are transaction logs since to get the DB caught up before trying the mirror setup...
Hi arnold,
Here are my limitations to those options you stated:
1. The backups are too large to send across the WAN link. The servers are continents & oceans apart. By the time the recent backups arrive, they will no longer be valid.
2. Server B is up and running, and mirroring is still working. The fear is that the daily crashing will soon become a permanent failure and the server will no longer be capable of rebooting.
3. Setting up Server C at site of Server A eliminates the purpose of having Server B at site 2 (an ocean away): disaster recovery regulations.
I love the idea of Always On. Therefore, here's my question:
QUESTION: Can I convert the mirror into an Always On setup WITHOUT having to transfer any backups across an ocean? If yes, I would:
(a) Convert mirror of ServerA-ServerB to AlwaysOn of ServerA-ServerB
(b) Setup ServerC and add it as an AlwaysOn node at site 2
(c) Remove ServerB from the AlwaysOn and replace it with a freshly installed server having the same name as ServerB
Note: Keeping the ServerB name will allow site 2's applications to work when DR (disaster recovery) is activated at site 2.
Here are my limitations to those options you stated:
1. The backups are too large to send across the WAN link. The servers are continents & oceans apart. By the time the recent backups arrive, they will no longer be valid.
2. Server B is up and running, and mirroring is still working. The fear is that the daily crashing will soon become a permanent failure and the server will no longer be capable of rebooting.
3. Setting up Server C at site of Server A eliminates the purpose of having Server B at site 2 (an ocean away): disaster recovery regulations.
I love the idea of Always On. Therefore, here's my question:
QUESTION: Can I convert the mirror into an Always On setup WITHOUT having to transfer any backups across an ocean? If yes, I would:
(a) Convert mirror of ServerA-ServerB to AlwaysOn of ServerA-ServerB
(b) Setup ServerC and add it as an AlwaysOn node at site 2
(c) Remove ServerB from the AlwaysOn and replace it with a freshly installed server having the same name as ServerB
Note: Keeping the ServerB name will allow site 2's applications to work when DR (disaster recovery) is activated at site 2.
Which version SQL server do you have enterprise/datacenter?
See discussion how yo convert a secondary db mirror to a live, accessible DB.
https://docs.microsoft.com/en-us/archive/blogs/saponsqlserver/reading-from-database-mirroring-or-alwayson-secondary-replicas-in-sap-configurations
Mirror to always on. SQL server 2012. References suggest the mirroring us on the phase out schedule. SQL server 2016 and after.....
If server C is where server b. Is risky, ever mirroring, restore a transaction log backup on the mirror, but this time bring the DB online. A full bavkup can then be done .....and get this to server C, in recovery mode, restore transaction log backup on server C to get it caught up or look at the process of always on groups .......to build out.
You would need to stage and test mirrored pair to always on availability group in the .....VM.
See discussion how yo convert a secondary db mirror to a live, accessible DB.
https://docs.microsoft.com/en-us/archive/blogs/saponsqlserver/reading-from-database-mirroring-or-alwayson-secondary-replicas-in-sap-configurations
Mirror to always on. SQL server 2012. References suggest the mirroring us on the phase out schedule. SQL server 2016 and after.....
If server C is where server b. Is risky, ever mirroring, restore a transaction log backup on the mirror, but this time bring the DB online. A full bavkup can then be done .....and get this to server C, in recovery mode, restore transaction log backup on server C to get it caught up or look at the process of always on groups .......to build out.
You would need to stage and test mirrored pair to always on availability group in the .....VM.
Hi arnold, based on your advice and insights, I will:
a. Convert mirroring to always on
b. Add Server C to the availability group
c. Remove misbehaving Server B from availability group
d. Rebuild Server B
e. Add the rebuilt Server B to the availability group
f. Remove Server C from availability group and decommission it.
QUESTION: Does the above plan make sense?
a. Convert mirroring to always on
b. Add Server C to the availability group
c. Remove misbehaving Server B from availability group
d. Rebuild Server B
e. Add the rebuilt Server B to the availability group
f. Remove Server C from availability group and decommission it.
QUESTION: Does the above plan make sense?
It does, but because you are experiencing issues with serve B,
Test the transition from a mirrored to analeaysonaathe read-only server B can be the source of the DB backup from which to restore the data on server C.
Test first to make sure you are comfortable with the process and that it works out as expected.
Test the transition from a mirrored to analeaysonaathe read-only server B can be the source of the DB backup from which to restore the data on server C.
Test first to make sure you are comfortable with the process and that it works out as expected.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial