March 23, 2011 at 4:09 am
Hi experts,
May need your advice on the correct to failover a database mirroring in SQL Server 2005/2008.
Kindly advice which is the correct method as below:
Method 1:
--Run on principal
USE master
GO
ALTER DATABASE dbName SET SAFETY FULL
GO
ALTER DATABASE dbName SET PARTNER FAILOVER
GO--Run on new principal
USE master
GO
ALTER DATABASE dbName SET SAFETY OFF
GO
Method 2:
--Run on principal
ALTER DATABASE dbName SET PARTNER OFF
RESTORE DATABASE dbName WITH RECOVERY
--Run on mirror
ALTER DATABASE dbName SET PARTNER OFF
RESTORE DATABASE dbName WITH RECOVERY
I also found this from a website advice to run the following t-sql if the original principal db is no longer available (e.g. physical server completely crash)
--Run on mirror if principal isn't available
USE
master
GO
ALTER DATABASE dbName SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
GO
Currently the database in my principal server is in "In Recovery" mode due to the drive T: (store tempdb) is disconnected. Kindly advice what should I do now.
Regards,monkeyDBAWe dont grow when things are easy; We grow when we face challenges.
March 23, 2011 at 6:26 am
jimmy.liew (3/23/2011)
Hi experts,May need your advice on the correct to failover a database mirroring in SQL Server 2005/2008.
Kindly advice which is the correct method as below:
Method 1:
--Run on principal
USE master
GO
ALTER DATABASE dbName SET SAFETY FULL
GO
ALTER DATABASE dbName SET PARTNER FAILOVER
GO--Run on new principal
USE master
GO
ALTER DATABASE dbName SET SAFETY OFF
GO
Method 2:
--Run on principal
ALTER DATABASE dbName SET PARTNER OFF
RESTORE DATABASE dbName WITH RECOVERY
--Run on mirror
ALTER DATABASE dbName SET PARTNER OFF
RESTORE DATABASE dbName WITH RECOVERY
Currently the database in my principal server is in "In Recovery" mode due to the drive T: (store tempdb) is disconnected. Kindly advice what should I do now.
Method 1 above implies that you are running async mirroring since you are turning on Full safety before you failover. This code is syntacticly correct.
Method 2 does not fail over. It simply turns off mirroring on both the principal and the mirror.
If you are having problems with Tempdb, why don't you move Tempdb to a drive that is available and restart SQL Server ?
March 25, 2011 at 8:21 am
First solution will be to move tempdb to other drive and start sql server.
after that you will not require to failover the mirror database.:-)
April 8, 2011 at 8:25 am
Assumptions:
1. Currently both sides of mirror ARE synchronized.
2. Your desired primary is now the mirror because of an operating failure.
Solution:
1. Take your primary offline and fix the TempDB problem.
2. Bring your primary back and verify that it behaves as the mirror (brute force solution is to take full backup of current primary, wipe out existing mirror (future primary), and restore from full backup, then reconfigure mirroring using 'new' copy as mirror.
3. Force your primary offline long enough for failover to occur. There are several techniques for doing so, depending on db activity:
(a) physically disconnecting the server (NOT recommended, but very effective! [at least in 2005])
(b) pausing service long enough (use administrative tools or SQL Server tools to do this) for failover to occur.
(c) using T-SQL commands as referenced above to force failover.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply