Solved

Microsoft SQL Server Database Mirroring - Automatic Failover

Posted on 2014-01-22
13
1,516 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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
 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

860 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