sqldba2013
asked on
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
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
Vijaya Reddy Pinnapa Reddy
Look at the below microsoft article for automatic failover commands
ASKER
<<Look at the below microsoft article for automatic failover commands >>
The Link is not available.
The Link is not available.
ASKER
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.
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.
ASKER
Increasing points from 250 to 500.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Add a Database Mirroring Witness Using Windows Authentication (Transact-SQL)
http://technet.microsoft.com/en-us/library/ms190430.aspx
ASKER
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)?
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)?
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
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
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
--