Microsoft SQL Server Database Mirroring - Automatic Failover

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
sqldba2013Asked:
Who is Participating?
 
Vijaya Reddy Pinnapa ReddyCommented:
Look at the below microsoft article for automatic failover commands
0
 
sqldba2013Author Commented:
<<Look at the below microsoft article for automatic failover commands >>

The Link is not available.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
sqldba2013Author Commented:
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
 
sqldba2013Author Commented:
Increasing points from 250 to 500.
0
 
Eugene ZConnect With a Mentor Commented:
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
 
Eugene ZCommented:
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
 
sqldba2013Author Commented:
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
 
Bhavesh ShahLead AnalysistCommented:
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
 
sqldba2013Author Commented:
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
 
sqldba2013Author Commented:
--
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.