Solved

SQL mirroring

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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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…
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

685 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