Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Microsoft SQL Server Database Mirroring - Automatic Failover

Posted on 2014-01-22
13
Medium Priority
?
1,629 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
  • 6
  • 2
  • 2
  • +1
11 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

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

Assisted Solution

by:Eugene Z
Eugene Z earned 1000 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 1000 total points
ID: 39805971
0
 

Author Closing Comment

by:sqldba2013
ID: 39828822
--
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

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

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

877 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