August 15, 2014 at 7:14 am
I am putting together a disaster recovery plan. As part of this, we have databases mirrored over to a server (Server B) to which we have limited access (db_owner as opposed to sysadmin permissions). Server B (the secondary) is in a separate data center from Server A (the primary). I've created a job to initiate failover of the mirrors in case of database failure, but the one contingency I can't seem to cover is data center failure. I've looked everywhere but cannot find a method of failing over mirrored databases if I don't have access to the primary server.
So here's my question (because I need to put it in my plan): If my data center or Server A goes down entirely, how do I fail over the mirror to Server B?
Everything I have found in BOL says I have to do this from the primary server. Maybe I missed a reference somewhere?
August 15, 2014 at 7:27 am
John Mitchell-245523 (8/15/2014)
BrandieIs it a witness you're looking for?
John
I wish. Corporate set up the mirroring without a witness server. @=(
August 19, 2014 at 8:00 am
Brandie Tarvin (8/15/2014)
So here's my question (because I need to put it in my plan): If my data center or Server A goes down entirely, how do I fail over the mirror to Server B?Everything I have found in BOL says I have to do this from the primary server. Maybe I missed a reference somewhere?
You only issue the ALTER DATABASE ... SET PARTNER FAILOVER against the principal when the principal is actually online.
In the situation you're describing, you must either connect to the mirror server and use ALTER DATABASE ... SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS or leave the mirror offline until the principal is brought back online or stop the mirror session to recover the database and start a new mirror session later on.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 19, 2014 at 8:02 am
Thanks, Perry. I appreciate the assist.
August 21, 2014 at 11:19 am
On another note, I recently found that I can also just break the mirror from either the primary or the secondary as per this BOL note. So it all depends on whether or not I want to just set the failover or break the mirror entirely.
Breaking the mirror seems a little last resort to me, though.
August 21, 2014 at 11:37 am
Brandie Tarvin (8/21/2014)
On another note, I recently found that I can also just break the mirror from either the primary or the secondary
Yes, hence my comment
comment
Perry Whittle (8/19/2014)
or stop the mirror session to recover the database and start a new mirror session later on.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 21, 2014 at 11:48 am
And that's what happens when I forget how to read a post all the way through. @=)
August 21, 2014 at 12:45 pm
One more question.
If you do not have access to server B beyond db_owner in the database,
how do you ensure that the logins (server principals) are synced?
August 21, 2014 at 10:15 pm
arnipetursson (8/21/2014)
One more question.If you do not have access to server B beyond db_owner in the database,
how do you ensure that the logins (server principals) are synced?
As db_owner, you cannot do anything except ask someone else to sort it out (e.g. someone with sysadmin or securityadmin priveleges).
You would already need to be doing that - mirroring does not do anything about logins. Logins are at the server level, database mirroring is at the database level.
August 21, 2014 at 11:47 pm
arnipetursson (8/21/2014)
One more question.If you do not have access to server B beyond db_owner in the database,
how do you ensure that the logins (server principals) are synced?
Curious, how did you manage to implement the mirror session in the first place.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 22, 2014 at 4:03 am
happycat59 (8/21/2014)
arnipetursson (8/21/2014)
One more question.If you do not have access to server B beyond db_owner in the database,
how do you ensure that the logins (server principals) are synced?
As db_owner, you cannot do anything except ask someone else to sort it out (e.g. someone with sysadmin or securityadmin priveleges).
You would already need to be doing that - mirroring does not do anything about logins. Logins are at the server level, database mirroring is at the database level.
I've already provided the server owners with a list of jobs and logins I need created on the server. When I failover, all I have to do then is run the update users proc to resync everything. If some logins don't sync because of different SIDs or other issues, I'm db_owner on those databases, so I can just manually add them to the DB after deleting the orphaned login out of the database.
Of course, keeping up with login changes is my responsiblity. Anytime something gets added or removed, I have to communicate that to the server owners. But that's why we use SQL logins and Windows Groups instead of individual logins. When people leave the company or get newly hired, we just make sure they're part of the proper Windows Groups so there's little back and forth on that particular issue.
August 22, 2014 at 4:05 am
Perry Whittle (8/21/2014)
arnipetursson (8/21/2014)
One more question.If you do not have access to server B beyond db_owner in the database,
how do you ensure that the logins (server principals) are synced?
Curious, how did you manage to implement the mirror session in the first place.
The server owners did it for us. When we get to End of Life, we're going to look for a better solution, but we needed this in a hurry so we were granted limited perms on someone else's server. Of course testing the solution requires the server owners cooperation (which is going to be a pain), but it's better than not having a DR solution at all.
August 23, 2014 at 7:24 pm
Hi Brandie,
"I've created a job to initiate failover of the mirrors in case of database failure"
Do you mind sharing how you setup the job?
Regards,
SQLisAwe5oMe.
August 25, 2014 at 6:52 am
SQLisAwE5OmE (8/23/2014)
Hi Brandie,"I've created a job to initiate failover of the mirrors in case of database failure"
Do you mind sharing how you setup the job?
The job I set up is for when I have access to the Data Center and the primary server. It's very simple (with a lot of notes in the job's General tab).
USE master;
GO
ALTER DATABASE DB1 SET PARTNER FAILOVER;
GO
ALTER DATABASE DB2 SET PARTNER FAILOVER;
GO
ALTER DATABASE DB3 SET PARTNER FAILOVER;
GO
ALTER DATABASE DB4 SET PARTNER FAILOVER;
GO
ALTER DATABASE DB5 SET PARTNER FAILOVER;
GO
Then I disabled the job so everyone would know that it shouldn't be run on a schedule and named it "DBA_VendorX_DR_MirrorFailover_Job".
EDIT: I forgot to mention... This vendor uses a table to store path names for reports, files, server and instance names (YAY! Data driven stuff!). So on the DR (secondary) server, I have a job that updates all those keys to point to the DR locations once the mirrors are failed over.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply