Solved

SQL mirroring

Posted on 2013-06-20
3
209 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
3 Comments
 
LVL 39

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 9

Expert Comment

by:MattSQL
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now