Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL mirroring

Posted on 2013-06-20
3
Medium Priority
?
216 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 2000 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

721 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