Solved

Microsoft SQL Server Database Mirroring - Automatic Failover

Posted on 2014-01-22
13
1,579 Views
Last Modified: 2014-02-02
All,

I am new to DB mirroring, need experts help on this technology.

We have to implement DB Mirroring in one of our PROD server with automatic fail-over (If primary Node has any issues then automatically switchover happens and secondary node becomes primary Node and there will not be any impact on application services).

I have created document to configure DB mirroring using TSQL commands. Please review the attached document and provide below clarification on this technology.

1. For automatic fail-over, what SQL commands we need to execute on Witness server (I have not added witness server SQL commands in the attached document because I am not clear on witness server).
2. Please share SQL commands for automatic fail-over configuration with detail.

NOTE: We have chosen automatic fail-over because the customer is expecting 99.9% application uptime.
DB-mirroring-steps-using-tsql.docx
0
Comment
Question by:sqldba2013
[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
  • 6
  • 2
  • 2
  • +1
13 Comments
 
LVL 9

Expert Comment

by:Vijaya Reddy Pinnapa Reddy
ID: 39799650
Look at the below microsoft article for automatic failover commands
0
 

Author Comment

by:sqldba2013
ID: 39800001
<<Look at the below microsoft article for automatic failover commands >>

The Link is not available.
0
 

Author Comment

by:sqldba2013
ID: 39802079
Hi Experts,

Could you please tel me which IP address we have to specify in the application configuration file

Do we need to specify witness server IP in application settings?

As I said earlier, If primary Node has any issues then automatically switchover happens and secondary node becomes primary Node and there will not be any impact on application services.

Please suggest.
0
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 

Author Comment

by:sqldba2013
ID: 39802081
Increasing points from 250 to 500.
0
 
LVL 43

Assisted Solution

by:Eugene Z
Eugene Z earned 250 total points
ID: 39802489
what are your sql servers verswion\edition\sp#?

it is a main idea of automatic failover --  it is automatic: no need to run anything

for manual failover:
Issue the following statement on the principal server:

ALTER DATABASE database_name SET PARTNER FAILOVER, where database_name is the mirrored database.



for Automatic Failover

read
Role Switching During a Database Mirroring Session (SQL Server)
http://technet.microsoft.com/en-us/library/ms189850.aspx
 
Automatic failover is supported only in database mirroring sessions running with a witness in high-safety mode (high-safety mode with automatic failover). In high-safety mode with automatic failover, once the database is synchronized, if the principal database becomes unavailable, an automatic failover occurs. An automatic failover causes the mirror server to take over the role of principal server and bring its copy of the database online as the principal database. Requiring that the database be synchronized prevents data loss during failover, because every transaction committed on the principal database is also committed on the mirror database.
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 39802504
one more: code to add a Database Mirroring Witness


Add a Database Mirroring Witness Using Windows Authentication (Transact-SQL)
http://technet.microsoft.com/en-us/library/ms190430.aspx
0
 

Author Comment

by:sqldba2013
ID: 39802611
Thanks to all for your suggestions.

Is it necessary to restore principle DB on Witness ?

I have configured DB mirroring with high safety mode (Automatic failover). Which server IP address and SQL server name I have to specify in application configuration file (pri or mirror or witness)?
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 39802680
hi,

no. it is not necessary to have principal db on witness, but you take full backup and keep on secondary server.

check out this pdf, they understand very well.

http://updates.thycotic.net/secretserver/documents/SQLServerMirroring.pdf
0
 

Author Comment

by:sqldba2013
ID: 39802879
Once again Thanks Brichsoft for your suggestion.

I have successfully implemented DB mirroring with Automatic failover.

Now I have to perform manual failover from Principle to Mirror. I have executed below command on Principle server.

USE master
GO
ALTER DATABASE HQ1 set partner failover
go

In the absence of an error message, I can see my primary database go from
<HQ1> (Principal,Synchronized)
to
<HQ1> (Mirror,Synchronized / Restoring)

Now I want to bring the server status as before that is
<HQ1> (Mirror,Synchronized / Restoring)
to
<HQ1> (Principal,Synchronized)

Please guide me which TSQL command I have to run on which server to revert failover status.
0
 
LVL 19

Accepted Solution

by:
Bhavesh Shah earned 250 total points
ID: 39805971
0
 

Author Closing Comment

by:sqldba2013
ID: 39828822
--
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Viewers will learn how the fundamental information of how to create a table.

623 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