Code Prostitute

SPS 2007 does not have any support for automatic failover to the mirror server.

Microsoft suggested using a SQL Alias or the following approach:

The approach for failover is

Ø If the SQL server falls over, use the stsadm command RenameServer to change the db server name, then stop & restart IIS & the timer service.

Ø If a single db other than configdb falls over, detach & reattach the db using the UI or via stsadm commands.

Ø If configdb falls over, the admin must use PSConfig.exe’s configdb command, or the renameserver command. If they use the latter, they’ll have to detach & reattach the other db’s on the same server, to move those back to their original sql server location.

The above approach seemed to be pretty complicated so I decided to use the SQL Alias approach and have written a windows service to monitor the things, here is an overview of the process:

* using cliconfg.exe, create an alias on each SharePoint server in the farm with a name of the currently configured SQL Server that SharePoint is pointed to. Set the value of the alias to the same server name. Example, if SharePoint is using server1 as the database server, create an alias called server1 and set the value of the alias to server1.
o the cliconfg.exe creates a registry value called server1 with the name of the alias in the following key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo

The data for the server1 value will be DBMSSOCN,server1

* My service periodically queries the database_mirroring_witnesses catalog of the witness server to see what the principal server is for the SharePoint_Config database. This object resides in the master database. Once you query this catalog there is a column that has the current principal server information.
* Remotely query the registries of all the SharePoint servers in the farm to see what database server the SQL alias created above is pointing to. If the alias is pointed at a different server than what the witness server thinks is the principal, remotely update the alias’s value in the registry and run IISRESET.exe /noforce.

The account that runs my service needed to have select access to the databse_mirroring_witnesses catalog on the Witness server.

Also, since I am only querying the witness server to see what the principal server is for the SharePoint_Config database, I am assuming that if this database fails over to the mirror, all the databases have failed over and I am redirecting SharePoint via the alias on all SharePoint servers in the farm.

** I took this from a comment on some site for my own permanent reference **