Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Manually failing over a mirror without access to Primary Server Expand / Collapse
Author
Message
Posted Friday, August 15, 2014 7:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:46 AM
Points: 5,676, Visits: 6,488
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?


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1603695
Posted Friday, August 15, 2014 7:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:56 AM
Points: 5,487, Visits: 10,335
Brandie

Is it a witness you're looking for?

John
Post #1603697
Posted Friday, August 15, 2014 7:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:46 AM
Points: 5,676, Visits: 6,488
John Mitchell-245523 (8/15/2014)
Brandie

Is it a witness you're looking for?

John


I wish. Corporate set up the mirroring without a witness server. @=(


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1603701
Posted Tuesday, August 19, 2014 8:00 AM This worked for the OP Answer marked as solution


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:55 AM
Points: 6,752, Visits: 14,397
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"
Post #1604933
Posted Tuesday, August 19, 2014 8:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:46 AM
Points: 5,676, Visits: 6,488
Thanks, Perry. I appreciate the assist.

Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1604934
Posted Thursday, August 21, 2014 11:19 AM This worked for the OP Answer marked as solution


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:46 AM
Points: 5,676, Visits: 6,488
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.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1605935
Posted Thursday, August 21, 2014 11:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:55 AM
Points: 6,752, Visits: 14,397
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"
Post #1605946
Posted Thursday, August 21, 2014 11:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:46 AM
Points: 5,676, Visits: 6,488
And that's what happens when I forget how to read a post all the way through. @=)

Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1605948
Posted Thursday, August 21, 2014 12:45 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 10:30 AM
Points: 322, Visits: 983
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?
Post #1605965
Posted Thursday, August 21, 2014 10:15 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:54 PM
Points: 3,087, Visits: 2,686
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.



Post #1606099
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse