Solved

SQL mirroring

Posted on 2013-06-20
3
210 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 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 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

My client sends data in an Excel file to me to load them into Staging database. The file contains many sheets that they have same structure. In this article, I would like to share the simple way to load data of multiple sheets by using SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

932 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

12 Experts available now in Live!

Get 1:1 Help Now