SharePoint and Mirrored Databases

Un-fun thing about Windows SharePoint Services 3.0:  Support for mirrored databases.  I did my testing of SharePoint and our mirrored SQL 2008 servers using our SharePoint 2010 beta instance.  That worked great!  Unfortunately, WSS 3.0 does not have the same native support for mirroring.  Instead, we have to go though a bunch of rigmarole using SQL database aliases, some ugly SQL jobs to run the failovers (all T-SQL is ugly, if you are more used to object-oriented scripting languages), poorly documented SQL alerts to monitor mirrorg, and scheduled tasks to change the local aliases on the SharePoint web front ends. 

There is no single reference for this topic, either… just a bunch of incomplete pointers. Each one assumes that you are either an expert on SQL and know nothing about SharePoint, or that you are a SQL dunce who will have to ask the SQL DBA for help, and that you can comprehend only Sharepoint. What about the SharePoint admin who has to be the DBA because your company does not have one of those?

The most complete reference from Microsoft on SharePoint and SQL mirroring can be found here:
This is a much updated version of the doc originally published in the SharePoint team blog, and later updated and added to the TechNet Library:
This is a really poor document, though, since it spends a lot of time telling you how to set up mirroring, but only hints at how to make SharePoint deal with the new configuration.
There also is a published case study on SharePoint mirroring here:
This study is more fleshed out, but lacks details on how to create and configure SQL alerts.
Fortunately, there is a good ref on SQL alerts and database mirroring here:

While the MS guidance is good at telling you what needs to be done, it is short on the details of how to do it.  We know that we need to accomplish the following tasks to enable SharePoint database failover:

  1. On the SQL Servers, detect when any one SharePoint database has lost “Primary Mirror” status, and failover the remaining databases so that they are all running on the same host.
  2. On the SharePoint web front end servers, we need a script that will test the database server specified in the SQL Server aliases entry, and perform the following actions if it is not the current “Primary” mirror role holder:
    1. Change the alias to the current “Primary” mirror
    2. Cycle SharePoint services

Ideally, we would have the script created in step 1 also send a signal to the web front ends to perform the SQL Alias change and SharePoint services cycle.

Microsoft gives us a sample script for the database monitor and failover script (see bullt “1”, above), but they only hint the remaining scripts that will be required, and they do not teall you the best way to tie all the script together.

Here is what I have come up with so far…

T-SQL Script for Database Mirror Status Monitoring and Failover:

The following is adapted from the MS Sample script.  It has been modified to force failover of all mirrored database on the current server if any mirrored DB has failed over.

conditionalFailover.sql - J. Greg Mackinnon, 2010-04-09
SQL Script to detect condition where not all /synchronized/ mirrored databases
are in the primary role.
Script will force failover of all mirrored databases if above conditions are met.

use master;
--Declare and initialize variables
DECLARE @SyncDBCount int;
DECLARE @PrimaryDBCount int;
SET @SyncDBCount = 0
SET @PrimaryDBCount = 0

--Collect total number of mirrored databases
SET @SyncDBCount = (
  SELECT COUNT(ALL database_id)
  FROM sys.database_mirroring
  WHERE mirroring_state=4
PRINT N'Total Synchronized Mirrored Database Count: '
	+ (CAST(@SyncDBCount AS varchar(4)));

--Collect total number of databases holding the "primary" database role.
SET @PrimaryDBCount = (
  SELECT COUNT(ALL database_id) FROM sys.database_mirroring WHERE
 (mirroring_state=4 and mirroring_role=1)
PRINT N'Total Mirrored Databases holding the "Primary" state: '
	+ (CAST(@PrimaryDBCount AS varchar(4)));

-- Begin IF condition when not all synced mirror DBs are Primary.
IF ((@PrimaryDBCount > 0) and (@PrimaryDBCount < @SyncDBCount)) BEGIN
	Print N'Beginning Failover of mirrored databases...'
	FOR SELECT name FROM sys.databases WHERE database_id IN
		(SELECT database_id
			FROM sys.database_mirroring
			WHERE (mirroring_state=4 and mirroring_role=1))

		DECLARE @name varchar(255)
		DECLARE @cmd varchar(1000)
		OPEN i

		WHILE (@@fetch_status  -1) BEGIN
			IF (@@fetch_status  -2) BEGIN
				set @cmd = 'ALTER Database [' + @name + '] SET PARTNER FAILOVER;'
				PRINT N'Running command: ' + @cmd
				exec (@cmd)
			END --end if
		END --end while
END -- End IF conditional
	PRINT N'No failovers will be performed at this time.'

T-SQL Script to determine availability of a primary mirror for a given SQL Server and Database:

The following script can be run from a client system to determine if the named SQL Server and Database are online, and is the current “Primary” mirror holder.  It can be launched using the “SQLCMD.exe” utility, and requires the SQL Native Client to be present on the client system.  Syntax for launching is:

sqlcmd.exe -S [SQLHost] -i [scriptFile.sql] -v dbName=[dbName]

The script will return a text output stating that the database is in one of three possible states… “Primary”, “Secondary”, or “unknown”.  If the database or database server is not availble, then a SQLCMD error will be returned.  Here is the script:

Detect Mirroring status of a single database
Returns char with value Primary|Secondary|unknown
(detection of "noSync" has been removed as a primary with a downed partner
will also be out of sync, but that does not mean that we don't want to use
the primary)
Database to be queried can be set by changing the @DBname variable

use master;

-- Declare and initialize variables
Declare @DBname AS varchar(128);
Declare @mirState AS char(9);
Set @DBname = '$(dbname)';
-- End Declarations

-- Conditional logic to determine if @DBname is Primary or Secondary
IF (
	select mirroring_role from sys.database_mirroring where (
		database_id IN (select database_id from sys.databases where name = @DBname)
) = 1 set @mirState = 'Primary'
	BEGIN --NOTE:  THIS WILL NOT RETURN THE EXPECTED RESULT for a user in the "Public" role on the DB server.
		-- That's okay, because the "primary" switch does work as expected.
	IF (
	select mirroring_role from sys.database_mirroring where (
		database_id IN (select database_id from sys.databases where name = @DBname)
	) = 2 set @mirState = 'Secondary'
	ELSE set @mirState = 'unknown'
--End Primary/Secondary selection

-- Conditional logic to determine if database is Synchronized
/*IF (
	select mirroring_state from sys.database_mirroring where (
		database_id IN (
			select database_id from sys.databases where name = 'VMware_VCDB'
	) != 4 set @mirState = 'noSync'
--End Sync determination

-- Generate Output
Print N'Database ' + @DBname + N' has status: ' + @mirState;

 PowerShell Script to monitor validity of current DB alias, and update if no longer valid:

This script calls on the T-SQL script above to check on the status of the DB Alias, update it if necessary, and cycle SharePoint services on the web front end if the alias is updated. Be sure to update the script to use your SQL Host, Alias, and Database names before using in your environment.

#Invokes "sqlcmd" script to determine holder of current primary mirror server role for the SharePoint databases.
#Sets SQL aliases accordingly

Set-PSDebug -Strict

##### Start script variables: ###
##### End script variables: #####

##### Start script functions: ###

function mirState {
	#Determines the mirroring state of a specified database
	#REQUIRES: SQLCMD.exe in the path specified in the function,
	#	the "determinMirDBState" sql script, in the path specified.
	#	input strings "$server" (for the SQL server or instance), and "$database" (the database to test)
	#PROVIDES: returns a string with possible values Primary|notThere|stateUnknown|other
	param ([string]$server,[string]$database)

	#Build SQLCMD command:
	[string]$sqlCom = 'c:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE'
	[string]$sqlScript = "c:\local\scripts\sql\determineMirDBState.sql"
	#Invoke the command and capture to string $comOut
	[string]$comOut = & $sqlCom -S $server -i $sqlScript -v dbname=$database 2>&1

	if ($comOut.contains("Primary") -eq $true) { #if RearWindow is the primary role holder then...
	# Removed "Secondary" selection as this string will not be returned in a low-privilege environment.
	#} elseif ($comOut | Select-String "Secondary") {#if RearWindow is the secondary role holder then...
	#	[string]$rc="Secondary"
	# Removed "noSync" detection as a primary with a downed partner will be out of sync, but it is not actionable.
	#} elseif ($comOut | Select-String "noSync"){ #if the mirroed database is not in sync then...
	#	[string]$rc="noSync"

	} elseif ($comOut.contains("not accessible") -eq $true) { #SQL error occured, meaning server is in some way unavailable.
	} elseif (($comOut.contains("unknown") -or ($comOut.contains("Secondary"))) -eq $true) { #db state is anything other than primary
	} else { #something else unexpected.
	return $rc
} #end mirState

function getCurServer {
	#Determines current database alias setting for the string $DBAlias.
	#Requires: An input string $DBAlias.
	#	Assumes the existance of the alias in the registry,
	#	and that the alias is using the "DBMSSOCN" (TCP/IP) connection method.
	#Returns: A string containing the host name of the current database server for the alias
	param ([string]$dbAlias)

	[string]$curAliasSetting = (get-itemproperty 'HKLM:\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo').$dbAlias
	[string]$curServer = $curAliasSetting.Replace("DBMSSOCN,","")
	return $curServer

function setAlias {
	#Sets in the regustry the host name for the provided SQL server alias
	#REQUIRES the names of the SQL host and its alias
	#PROVIDES nothing
	param ([string]$dbHost,[string]$dbAlias)
	[string]$aliasSetting = "DBMSSOCN," + $dbHost
	Set-ItemProperty -Path 'HKLM:\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo' -Name $dbAlias -Value $aliasSetting

function cycleServices {
	#Restarts system services associated with SharePoint
	# This will expedite reconnection to the SharePoint databases in the event of a SQL mirror failover.
	& iisreset
	# We use the "if" conditional below to determine if the service is already running before attempting to restart it.
	if ((Get-Service -Name "spadmin").status -match "Running") {Restart-Service -Name "spadmin"}
	if ((Get-Service -Name "sptimerv3").status -match "Running") {Restart-Service -Name "sptimerv3"}
	if ((Get-Service -Name "spsearch").status -match "Running") {Restart-Service -Name "spsearch"}
	if ((Get-Service -Name "osearch").status -match "Running") {Restart-Service -Name "osearch"}
	Write-Output "SharePoint services cycled."
##### End script functions: #####

##### Start script body: ###
[string]$curServer = getCurServer -dbAlias $dbAlias
Write-Output "The database alias $dbAlias currently is set to: $curServer"

[string]$mirStateVar = mirState -server $curServer -database $dbName
Write-Host "Database $dbName on server $curServer is in state $mirStateVar."

if ($mirStateVar.contains("Primary")) {
	Write-Output "$curServer is already defined as the SQL Alias, and the server is functioning as expected.  Exiting."
} else {
	switch -regex ($curServer) {
		$db1 {[string]$newServer = $db2}
		$db2 {[string]$newServer = $db1}
	setAlias -dbHost $newServer -dbAlias $dbAlias
	Write-Output "Set alias $dbAlias to value $newServer."
	## Removed the "cycleServices function as the databases will get reconnected on their own.
	## You still can run "cycleServices if you want to expedite DB reconnection,
	## but keep in mind that this will cause all of the ASP.NET code to recompile.  Boo!
	#Write-Output "Cycling SharePoint services..."
	Write-Output "Done."
##### End script body: #####

Tying it all together

So now to make it all work togther…
The fastest thing to do is to implement the first T-SQL script as a SQL Server Agent job running on both SQL mirror servers. The jobs are configured to run once per minute, and have been staggered to run 30 seconds apart from each other. The script will not be triggered by databases in a “suspended”, we should be able to avoid job collision between the two servers. (I hope).

We then could implement the PowerShell script (and accompanying T-SQL script) as a scheduled task on the web front end servers. We need to run the script as an account in the PUBLIC role on the SQL servers, which can run as a batch job on the WFEs.
Easy, right? we shall see.

Leave a Reply