Avatar of waltforbes
waltforbes
Flag for Bahamas asked on

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?
Microsoft SQL ServerWindows OSSQL

Avatar of undefined
Last Comment
waltforbes

8/22/2022 - Mon
arnold

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...
waltforbes

ASKER
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.
arnold

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
waltforbes

ASKER
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?
ASKER CERTIFIED SOLUTION
arnold

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
waltforbes

ASKER
Arnold provided me the clarity I needed to determine which path I must take.