Solved

Microsoft SQL Server Database Mirroring - Automatic Failover

Posted on 2014-01-22
13
1,466 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
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
 

Author Comment

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

Assisted Solution

by:EugeneZ
EugeneZ 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 42

Expert Comment

by:EugeneZ
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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…

758 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

19 Experts available now in Live!

Get 1:1 Help Now