Configuring SQL Server Mirroring for SharePoint 2010

 

by Sam Hassani on 10/25/2011 8:15 AM

Category: SharePoint 2010; Administration

​A couple of weeks ago at the SharePoint conference Chris and I delivered 3 sessions, one of which was “Deploying SharePoint 2010 as a Mission Critical Application”, in which we covered high availability options for SharePoint 2010. One of the key demonstrations showed the end-to-end configuration of high availability within a farm using SQL Server mirroring for the database tier. As promised, the PowerShell and TSQL we used is provided here in this blog post.

Firstly, credit to Mike Watson, for providing the TSQL that we’ve leveraged here: http://blogs.msdn.com/b/mikewat/archive/2008/07/30/sql-mirroring-setup-made-easy.aspx

The PowerShell demonstrated to set up a failover SQL instance for databases in a SharePoint farm is detailed below. To return all databases with no failover instance configured (except for the Logging database):

Get-SPDatabase | ? {$_.Type -ne “Microsoft.SharePoint.Administration.SPUsageDatabase”} | ? {!$_.FailoverServiceInstance}

To configure a failover instance for all databases (except for the Logging database):

Get-SPDatabase | ? {$_.Type -ne “Microsoft.SharePoint.Administration.SPUsageDatabase”} | ? {!$_.FailoverServiceInstance} | ForEach ($_) {$_.AddFailoverServiceInstance(“SERVERNAME\INSTANCENAME”); $_.Update()}

Moving over to SQL, to end-to-end mirroring configuration of databases from one SQL Server instance to another is as follows.

Run the following command on the Principal, Mirror and Witness servers, to create the mirroring endpoints:

CREATE ENDPOINT Endpoint_Mirroring — name

STATE=STARTED — Endpoint will be started and ready

AS TCP(LISTENER_PORT=5022 — Endpoint will use port 5022

, LISTENER_IP=ALL) — Endpoint will listen on all IP addresses

FOR DATABASE_MIRRORING — Specifies mirroring as the endpoint purpose

(AUTHENTICATION = WINDOWS[Negotiate] — Will negotiate NTLM or Kerberos for authentication

, ENCRYPTION = SUPPORTED, — Mirroring traffic will be encrypted

ROLE=ALL); — This endpoint can be principal, mirror, or a witness

The next step is to set up full recovery model for all databases to be mirrored. Run the following on the Principal server to generate the script to achieve this:

Select ‘USE MASTER; ALTER DATABASE [‘ + name + ‘] SET RECOVERY FULL;’ from sys.databases where database_id > 4

The next step is to Backup all databases and logs on the Principal server. Select the output of this query, paste, and run in the query window to execute. This will backup all databases on the server except the system databases (master, temp, msdb, and model).

SELECT ‘BACKUP DATABASE [‘ + name + ‘] TO DISK = ”C:\Backup\’ + name + ‘.bak” WITH FORMAT;’ from sys.databases where database_id > 4

SELECT ‘BACKUP LOG [‘ + name + ‘] TO DISK = ”C:\Backup\log_’ + name + ‘.bak” WITH FORMAT;’ from sys.databases where database_id > 4

Now we need to generate the Restore scripts by running the following on the Principal server. The output can then be run on the Mirror server. Remember to make sure to copy the backups to the mirror server before attempting to restore! We will be restoring databases with logs with NORECOVERY.

SELECT ‘RESTORE DATABASE [‘ + name + ‘] FROM DISK = ”c:\Backup\’ + name + ‘.bak” WITH MOVE ”’ + name + ”’ TO ”C:\Program Files\Microsoft SQL Server\MSSQL10_50.MIRRORING\MSSQL\DATA\’ + name + ‘.mdf”, MOVE ”’ + name + ‘_Log” TO ”C:\Program Files\Microsoft SQL Server\MSSQL10_50.MIRRORING\MSSQL\DATA\’ + name + ‘_Log.LDF”, NORECOVERY;’ from sys.databases where database_id > 4

SELECT ‘RESTORE LOG [‘ + name + ‘] FROM DISK = ”c:\Backup\log_’ + name + ‘.bak” WITH NORECOVERY;’ from sys.databases where database_id > 4

At this point, something we didn’t demo during our SPC session, but may be required, is to copy SQL Server logins from the Principal server to the Mirror server as described here: http://support.microsoft.com/kb/918992

The next stage is to set up the mirroring partnerships for each database. On the Mirror server, select the output of this query, paste, and run in a query window to execute (change principal server name):

SELECT ‘ALTER DATABASE [‘ + name + ‘] SET PARTNER = ”TCP://PRINCIPAL.domain.com:5022”;’ from sys.databases where database_id > 4

Next we grant permissions on the endpoint on the Mirror (change “DOMAIN\user” to Principal SQL Server Service account):

use [master]

GO

GRANT CONNECT ON ENDPOINT::[Endpoint_Mirroring] TO [DOMAIN\user]

GO

Now we set up the partnership on the Principal Server, select the output of this query, paste, and run in a query window to execute (change mirror and witness server names):

SELECT ‘ALTER DATABASE [‘ + name + ‘] SET PARTNER = ”TCP://MIRROR.contoso.local:5022”;’ from sys.databases where database_id > 4

SELECT ‘ALTER DATABASE [‘ + name + ‘] SET WITNESS = ”TCP://WITNESS.contoso.local:5022”;’ from sys.databases where database_id > 4

And there we have it, the end-to-end configuration of mirroring within a SharePoint farm!

Please let us know if you have any comments, questions or feedback!

Advertisements