Force a failover of all database with mirroring: This code can be used to force an failover against all databases with mirroring enabled:
Declare @string sysname
declare Mirror_cursor cursor fast_forward
select [name] from sys.databases where database_id >4 order by name
declare @dbname sysname
Fetch next from mirror_cursor into @dbname
while (@@fetch_status -1)
set @string =(SELECT mirroring_role_desc FROM sys.database_mirroring WHERE database_id = DB_ID (@dbname))
if @string =’PRINCIPAL’
set @string = ‘ALTER DATABASE ‘ + @dbname +’ SET PARTNER FAILOVER’
set @string =”
fetch next from mirror_cursor into @dbname
I will use this as an non scheduled job on servers with mirroring enabled. It can be used for the operators whent hey need to fail or failback databases.
To remove mirroring on database: To remove mirroring we can just use the mirror wizard and choose to break the mirror. After that the mirror will be in restoring state. This is how to fix this:
RESTORE DATABASE mydbname WITH RECOVERY;
When the databse is in workign mode again we might need to remove the mirror setting by runnign this code:
ALTER DATABASE mydbname SET PARTNER OFF
Dirty way of deleting files and more
I had problems to log on to the xyz server. One way to get access to the disks and clean out old files is to use our old friend xp_cmdshell. xp_cmdshell is not enabled by default but on most of our servers we have already enabled it and if we need to do it on a new server we can run sp_configure to do that.
To find the files we are looking for we use this:
EXEC master..xp_cmdshell ‘dir f:\’
Do this until the right folder is found.
when you have found the folder with the files you can either delete single files or the whole directory:
EXEC master..xp_cmdshell ‘rd /s /Q f:\backup\2005\full
This will delete all files and folders , including the root folder “full”. This without any yes no queries.
Now, recreate the delete root folder, if it is needed:
EXEC master..xp_cmdshell ‘md f:\backup\2005\differential’
That is the whole. If you need any help with commando prompt commandos you can type help in an cmd window. Everything you can do in an cmd window, you can do with xp_cmdshell. Thatg includes start scripts, ping and much more.
Trouble shoot mirrorign endpoint problems. I had some problems with the mirroring endping on the XYZ servers. This is some nice code to have when checking conectivity.
– list all endpoints: select * from sys.endpoints
make sure thatt he name of thenedpoint is the same on all nodesm incudling the witness.
– If the endpint is having a wrong name, drop it:
drop endpoint mirroring
– If needed, create a new nedpoint:
CREATE ENDPOINT mirroring
AS TCP (LISTENER_PORT=7022 )
FOR DATABASE_MIRRORING (ROLE=WITNESS)
– Grant connect right to the user:
GRANT CONNECT ON ENDPOINT::mirroring TO [rds\it-got-vtbos-sql];
– make sure that thet correct user owns the endpoint:
ALTER AUTHORIZATION ON endpoint::mirroring TO [rds\it-got-vtbos-sql];
In wrong endpoint is used or the user is not having connect rights, mirroring will nto work with automatic failover.
Från Khan sql dba – mcitp www.addarr.com