Solved

SQL mirroring

Posted on 2013-06-20
3
215 Views
Last Modified: 2016-02-11
Hello,

I just set up sql mirroring in 2012 and think it is pretty slick.  My question is this: if my primary server goes down and will not come back up, how do I failover on the mirror side?

I know I have to open up a query and run the following:

ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
which I found at the following website: http://msdn.microsoft.com/en-us/library/ms189270(v=sql.105).aspx

But I'm not sure what to do after that.  Any help would be greatly appreciated.
0
Comment
Question by:soadmin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 500 total points
ID: 39264409
At that point the sQL server should be running on that box.  You would need to repoint your applications (or better yet, update your DNS) so that the Primary machine name points to the mirrored DB.

If you use the DNS route, it's one change and anyone looking for it will find the mirrored server.
0
 
LVL 10

Expert Comment

by:Matt Bowler
ID: 39264498
Depending on how the two servers have been set up you may need to duplicate any server scoped objects that the database uses across from the primary to the mirror server.

This includes things like:

Logins,
Credentials,
Proxies,
Agent Jobs,
Linked Servers,
Alerts,
Server Triggers,
Endpoints,
Operators,
Backup devices,
SSIS packages in msdb,
User System Messages,
Service Master key,
Replication publications and subscriptions.

One option is to script out these objects as appropriate on a regular basis on the primary server and copy these scripts to the mirror. The scripts can then be run either regularly or on failover on the mirror to recreate the appropriate objects.

There can be a lot of moving parts involved in a successful mirror failover. I would recommend testing before you need it.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 39264609
But I'm not sure what to do after that.
What you should do after you script/maintain the above is to proactively set your ADO client to failover automatically on your mirror in the event of having the principal becoming available.  To do that, you simply need to change your connection string to add a new parameter.  

For more info on how to do that, please read below for more info...

http://msdn.microsoft.com/en-us/library/5h52hef8.aspx

Changing your ADO string makes a difference into reducing overall downtime since it gives redirection intelligence to the client.

Hope this helps.
0

Featured Post

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question