This blog post has the goal of being able to coach you through setting up database mirroring for SQL Server 2008 R2 and SharePoint 2010 as a complete package.  If you follow the steps carefully step by step then you will be able to mirror your whole 2010 environment.   Screen shots of SQL Management Studio and Central Admin are included, as well as T-SQL scripts and PowerShell scripts necessary.

Prerequisites

There are several prerequisites to set up before undertaking setting up mirroring.  They include:

1. Run ALL SQL Server instances with a domain account.  I set up SP2010\SPSQL for this.   Any domain service account will do.
2. Principal, Mirror, and Witness are need to be running same version of SQL Server.  In my instances I have the Principal and Mirror running SQL Server 2008 R2 and the witness server running SQL Server 2008 R2 Express Edition.
3. Your SQL Servers you are using for a Principal and Mirror need to have 1ms latency between them.
4. They also should have 1GB/s throughput between them.   These two measurements will ensure that when your farm is experiencing heavy use, it won’t falsely detect a failed Principal and fail over.

Ready to Go

First, shut down your SharePoint servers so they will not interact with the databases.

Start to manually set up mirroring for one of your SharePoint databases.  The reason to do this is to take advantage of all the wizards in setting up endpoints and other tasks like this.  The other reason is that the configuration wizard once you are finished shows you the exact values you need to put into SQL scripts later on in this article as endpoints. 

An endpoint looks like this:  TCP://SQL1.sp2010.local:5022

Once we do one database we can script the rest.   I chose the WordConversion database for this as it is practically not used, so very small, and at the end of the list:

NOTE:  You cannot mirror the following databases:   SP2010_SharePoint_People_Sync, SP2010_SharePoint_Analytics_Stager1.  It is also not recommended to mirror the SP2010_SharePoint_Usage database.  As a matter of fact, if you try to mirror the Usage database, you will get the following error:

Databases that are intended for transitory storage only are usually configured not to use the full recovery model for backups, as they are not needed for DR.  I used the names of MY databases.

SQL Management Studio Mirroring Configuration Wizard

I right-click the database, select “Tasks->Mirror…”

The next window shows the Mirroring Wizard:

Click “Configure Security”

Next->

Select “Yes” for including a witness server.  This is so we can take advantage of Asynchronous Mirroring with Automatic Failover.  Next->

Yes we want to save the security configuration on all 3 of the instances – Principal, Mirror, and Witness.

Set up the Principal first.  My principal is SQL1. 

Next is SQL 2 which is my Mirror instance.

The next service is my Witness Instance.  to enable this, I needed to start the SQL Browser Service on my SQL Express Witness instance as follows:

I also needed to ensure that TCP/IP was enabled as a protocol

Now I can go and configure the Witness Instance:

Next we will specify service accounts:

Now the wizard will set up all of the items:

Click Finish. 

Endpoints re created on all 3 instances. 

Select start mirroring  – we still need to set up all the accounts and restore  backup, but if you don’t you’ll lose the endpoints setup.  Copy the values from those 3 endpoint setups as we will need them.   You don’t REALLY have to do this if you know the format, but to me setting up one by way of the Configuration Wizard helps me to ensure that I can connect to each of the SQL instances and that my endpoints are accurate.   It is a backstop check to ensure what I’m going to do for the rest of the databases via T-SQL is accurate.

Now we will go back and transfer logins. 

Run the following SQL script to generate logins necessary on the MIRROR:

SELECT
'create login [' + p.name + '] ' +
case when p.type in('U','G') then 'from windows ' else '' end +
'with ' +
case when p.type = 'S' then 'password = ' + master.sys.fn_varbintohexstr(l.password_hash) + ' hashed, ' +
'sid = ' + master.sys.fn_varbintohexstr(l.sid) + ', check_expiration = ' +
case when l.is_policy_checked > 0 then 'ON, ' else 'OFF, ' end + 'check_policy = ' + case when l.is_expiration_checked > 0 then 'ON, ' else 'OFF, ' end +
case when l.credential_id > 0 then 'credential = ' + c.name + ', ' else '' end
else '' end +
'default_database = ' + p.default_database_name +
case when len(p.default_language_name) > 0 then ', default_language = ' + p.default_language_name else '' end

FROM sys.server_principals p
LEFT JOIN sys.sql_logins l
ON p.principal_id = l.principal_id
LEFT JOIN sys.credentials c
ON  l.credential_id = c.credential_id
WHERE p.type in('S','U','G')
AND p.name <> 'sa'

This script’s output generates the T-SQL input that we will need to run on the MIRROR

create login [##MS_PolicyEventProcessingLogin##] with password = 0x01005e7e88007a0c23e5bef893202e8e76167992f6d5acc53a98 hashed, sid = 0x4eaf544d095570419cf280e6c9d103a6, check_expiration = ON, check_policy = OFF, default_database = master, default_language = us_english
create login [##MS_PolicyTsqlExecutionLogin##] with password = 0x0100d581c668f91c13174c84b608b1337c6635533e1ac36c0632 hashed, sid = 0x014ea8886b841c4ca1f7ed32489bbf62, check_expiration = ON, check_policy = OFF, default_database = master, default_language = us_english
create login [NT AUTHORITY\SYSTEM] from windows with default_database = master, default_language = us_english
create login [NT SERVICE\MSSQLSERVER] from windows with default_database = master, default_language = us_english
create login [SQL1\Dave] from windows with default_database = master, default_language = us_english
create login [NT SERVICE\SQLSERVERAGENT] from windows with default_database = master, default_language = us_english
create login [SP2010\dave] from windows with default_database = master, default_language = us_english
create login [SP2010\spadmin] from windows with default_database = master, default_language = us_english
create login [SP2010\SPFarm] from windows with default_database = master, default_language = us_english
create login [SP2010\SPMySitesAppPool] from windows with default_database = master, default_language = us_english
create login [SP2010\SPSites1AppPool] from windows with default_database = master, default_language = us_english
create login [SP2010\SPAppPool] from windows with default_database = master, default_language = us_english
create login [SP2010\SPSearch] from windows with default_database = master, default_language = us_english
create login [SP2010\SPSearchAppPool] from windows with default_database = master, default_language = us_english
create login [SP2010\SPUserCode] from windows with default_database = master, default_language = us_english
create login [SP2010\SPSearchCrawl] from windows with default_database = master, default_language = us_english
create login [sp2010\spsql] from windows with default_database = master, default_language = us_english

Run this script on the MIRROR to duplicate all the logins from your PRIMARY.

Next, run the following script

--use master
DECLARE @name VARCHAR(4000) -- database name 
DECLARE @path VARCHAR(4000) -- path for backup files 
DECLARE @fileName VARCHAR(4000) -- filename for backup 
DECLARE @logfileName VARCHAR(4000) -- logfilename for backup 
DECLARE @fileDate VARCHAR(20) -- used for file name
declare @sql nvarchar(4000)
 
 
SET @path = 'C:\Backups\' 

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR 
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb') 
--WHERE name NOT IN ('master','model','msdb','tempdb',’INCLUDE YOUR DB’s)'

CREATE TABLE #TEMPRESTORE
(
CMD VARCHAR(400)
)
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name;
      
WHILE @@FETCH_STATUS = 0  
BEGIN  
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
       SET @logfileName = @path + @name + '_' + @fileDate + '_Log.BAK'     
      
        
        SET @sql = 'ALTER DATABASE "' + @name + '" SET RECOVERY FULL'
        exec sys.sp_executesql @sql;

   
        BACKUP DATABASE @name TO DISK = @fileName with format;
        BACKUP log @name TO DISK = @logfileName with format;
       
        INSERT INTO #TEMPRESTORE VALUES ('RESTORE DATABASE "'+@name+'"
       FROM DISK = '''+@fileName+''' WITH NORECOVERY')
       INSERT INTO #TEMPRESTORE VALUES ('go')
       INSERT INTO #TEMPRESTORE VALUES ('RESTORE LOG "'+@name+'"
       FROM DISK = '''+@logfileName+''' WITH NORECOVERY')
         INSERT INTO #TEMPRESTORE VALUES ('go')
       FETCH NEXT FROM db_cursor INTO @name  
END  

CLOSE db_cursor  
DEALLOCATE db_cursor
SELECT * FROM #TEMPRESTORE
DROP TABLE #TEMPRESTORE

The output of this script needs to be saved as well as the actions that it takes.  Here is the output from my execution of the T-SQL, which we will run on the MIRROR:

RESTORE DATABASE "SP2010_SharePoint_ConfigDB"         FROM DISK = 'C:\Backups\SP2010_SharePoint_ConfigDB_20111110.BAK' WITH NORECOVERY
go
RESTORE LOG "SP2010_SharePoint_ConfigDB"         FROM DISK = 'C:\Backups\SP2010_SharePoint_ConfigDB_20111110_Log.BAK' WITH NORECOVERY
go
RESTORE DATABASE "SP2010_SharePoint_CentralAdmin_Content"         FROM DISK = 'C:\Backups\SP2010_SharePoint_CentralAdmin_Content_20111110.BAK' WITH NORECOVERY
go
RESTORE LOG "SP2010_SharePoint_CentralAdmin_Content"         FROM DISK = 'C:\Backups\SP2010_SharePoint_CentralAdmin_Content_20111110_Log.BAK' WITH NORECOVERY
go
RESTORE DATABASE "SP2010_SharePoint_MySites_Content1"         FROM DISK = 'C:\Backups\SP2010_SharePoint_MySites_Content1_20111110.BAK' WITH NORECOVERY
go
RESTORE LOG "SP2010_SharePoint_MySites_Content1"         FROM DISK = 'C:\Backups\SP2010_SharePoint_MySites_Content1_20111110_Log.BAK' WITH NORECOVERY
go
RESTORE DATABASE "SP2010_SharePoint_Sites_Content1"         FROM DISK = 'C:\Backups\SP2010_SharePoint_Sites_Content1_20111110.BAK' WITH NORECOVERY
go
RESTORE LOG "SP2010_SharePoint_Sites_Content1"         FROM DISK = 'C:\Backups\SP2010_SharePoint_Sites_Content1_20111110_Log.BAK' WITH NORECOVERY
go
RESTORE DATABASE "SP2010_SharePoint_State"         FROM DISK = 'C:\Backups\SP2010_SharePoint_State_20111110.BAK' WITH NORECOVERY
go
RESTORE LOG "SP2010_SharePoint_State"         FROM DISK = 'C:\Backups\SP2010_SharePoint_State_20111110_Log.BAK' WITH NORECOVERY
go
RESTORE DATABASE "SP2010_SharePoint_SessionState"         FROM DISK = 'C:\Backups\SP2010_SharePoint_SessionState_20111110.BAK' WITH NORECOVERY
go
RESTORE LOG "SP2010_SharePoint_SessionState"         FROM DISK = 'C:\Backups\SP2010_SharePoint_SessionState_20111110_Log.BAK' WITH NORECOVERY
go
RESTORE DATABASE "SP2010_SharePoint_SecureStore"         FROM DISK = 'C:\Backups\SP2010_SharePoint_SecureStore_20111110.BAK' WITH NORECOVERY
go
RESTORE LOG "SP2010_SharePoint_SecureStore"         FROM DISK = 'C:\Backups\SP2010_SharePoint_SecureStore_20111110_Log.BAK' WITH NORECOVERY
go
RESTORE DATABASE "SP2010_SharePoint_Search"         FROM DISK = 'C:\Backups\SP2010_SharePoint_Search_20111110.BAK' WITH NORECOVERY
go
RESTORE LOG "SP2010_SharePoint_Search"         FROM DISK = 'C:\Backups\SP2010_SharePoint_Search_20111110_Log.BAK' WITH NORECOVERY
go
RESTORE DATABASE "SP2010_SharePoint_MetaData"         FROM DISK = 'C:\Backups\SP2010_SharePoint_MetaData_20111110.BAK' WITH NORECOVERY
go
RESTORE LOG "SP2010_SharePoint_MetaData"         FROM DISK = 'C:\Backups\SP2010_SharePoint_MetaData_20111110_Log.BAK' WITH NORECOVERY
go
RESTORE DATABASE "SP2010_SharePoint_People_Profile"         FROM DISK = 'C:\Backups\SP2010_SharePoint_People_Profile_20111110.BAK' WITH NORECOVERY
go
RESTORE LOG "SP2010_SharePoint_People_Profile"         FROM DISK = 'C:\Backups\SP2010_SharePoint_People_Profile_20111110_Log.BAK' WITH NORECOVERY
go
RESTORE DATABASE "SP2010_SharePoint_Search_CrawlStore1"         FROM DISK = 'C:\Backups\SP2010_SharePoint_Search_CrawlStore1_20111110.BAK' WITH NORECOVERY
go
RESTORE LOG "SP2010_SharePoint_Search_CrawlStore1"         FROM DISK = 'C:\Backups\SP2010_SharePoint_Search_CrawlStore1_20111110_Log.BAK' WITH NORECOVERY
go
RESTORE DATABASE "SP2010_SharePoint_Search_PropertyStore1"         FROM DISK = 'C:\Backups\SP2010_SharePoint_Search_PropertyStore1_20111110.BAK' WITH NORECOVERY
go
RESTORE LOG "SP2010_SharePoint_Search_PropertyStore1"         FROM DISK = 'C:\Backups\SP2010_SharePoint_Search_PropertyStore1_20111110_Log.BAK' WITH NORECOVERY
go
RESTORE DATABASE "SP2010_SharePoint_People_Social"         FROM DISK = 'C:\Backups\SP2010_SharePoint_People_Social_20111110.BAK' WITH NORECOVERY
go
RESTORE LOG "SP2010_SharePoint_People_Social"         FROM DISK = 'C:\Backups\SP2010_SharePoint_People_Social_20111110_Log.BAK' WITH NORECOVERY
go
RESTORE DATABASE "SP2010_SharePoint_WordConversion"         FROM DISK = 'C:\Backups\SP2010_SharePoint_WordConversion_20111110.BAK' WITH NORECOVERY
go
RESTORE LOG "SP2010_SharePoint_WordConversion"         FROM DISK = 'C:\Backups\SP2010_SharePoint_WordConversion_20111110_Log.BAK' WITH NORECOVERY
go
RESTORE DATABASE "SP2010_SharePoint_Analytics_Warehouse"         FROM DISK = 'C:\Backups\SP2010_SharePoint_Analytics_Warehouse_20111110.BAK' WITH NORECOVERY
go
RESTORE LOG "SP2010_SharePoint_Analytics_Warehouse"         FROM DISK = 'C:\Backups\SP2010_SharePoint_Analytics_Warehouse_20111110_Log.BAK' WITH NORECOVERY
go
RESTORE DATABASE "SP2010_SharePoint_BCS"         FROM DISK = 'C:\Backups\SP2010_SharePoint_BCS_20111110.BAK' WITH NORECOVERY
go
RESTORE LOG "SP2010_SharePoint_BCS"         FROM DISK = 'C:\Backups\SP2010_SharePoint_BCS_20111110_Log.BAK' WITH NORECOVERY
go
RESTORE DATABASE "SP2010_SharePoint_FoundationSearch"         FROM DISK = 'C:\Backups\SP2010_SharePoint_FoundationSearch_20111110.BAK' WITH NORECOVERY
go
RESTORE LOG "SP2010_SharePoint_FoundationSearch"         FROM DISK = 'C:\Backups\SP2010_SharePoint_FoundationSearch_20111110_Log.BAK' WITH NORECOVERY
go
RESTORE DATABASE "PerformancePoint Service Application_2757dc3dcb96455799c3e33c55f40b5d"         FROM DISK = 'C:\Backups\PerformancePoint Service Application_2757dc3dcb96455799c3e33c55f40b5d_20111110.BAK' WITH NORECOVERY
go
RESTORE LOG "PerformancePoint Service Application_2757dc3dcb96455799c3e33c55f40b5d"         FROM DISK = 'C:\Backups\PerformancePoint Service Application_2757dc3dcb96455799c3e33c55f40b5d_20111110_Log.BAK' WITH NORECOVERY
go

Next, copy all of the files from your C:\Backups directory on the PRINCIPAL server over to the C:\Backups directory on the MIRROR server.

Run the T-SQL generated by your script which should look similar to the above T-SQL on the MIRROR.  After this executes we see all of the restored DB’s in recovery mode:

 

Now we need to set the MIRROR partners, first on the MIRROR, then on the PRINCIPAL.  Execute the following T-SQL script on the MIRROR:

NOTE:  For the first run make sure the @mirorendpoint variable is pointing to your PRINCIPAL instance.

--use master
DECLARE @name VARCHAR(4000) -- database name 
declare @sql nvarchar(4000)
Declare @mirrorendpoint varchar(4000)
Declare @witnessendpoint varchar(4000)

set @mirrorendpoint ='TCP://SQL1.sp2010.local:5022'
set @witnessendpoint ='TCP://SQL-WITNESS.sp2010.local:5022'

DECLARE db_cursor CURSOR FOR 
SELECT name
FROM master.dbo.sysdatabases
--WHERE name NOT IN ('master','model','msdb','tempdb') 
--WHERE name NOT IN ('master','model','msdb','tempdb','AdventureWorks','AdventureWorksDW','AdventureWorksDW2008','AdventureWorksLT','AdventureWorksLT2008','User Profile Service Application_SocialDB_e5c7c51ed6cc400a93cfbc23bca86fed','User Profile Service Application_SyncDB_4e161aa1381e42e8a7844d99165ec79b','WSS_Logging');
WHERE name NOT IN ('master','model','msdb','tempdb','AdventureWorks','AdventureWorksDW','AdventureWorksDW2008','AdventureWorksLT','AdventureWorksLT2008','SP2010_SharePoint_Analytics_Stager1','SP2010_SharePoint_People_Sync','SP2010_SharePoint_Usage');

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name;
      
WHILE @@FETCH_STATUS = 0  
BEGIN  
         --At HOST_B, set the server instance on HOST_A as a partner (principal server): - repeat for all DBS
     
        
        SET @sql = 'ALTER DATABASE "' + @name + '" SET PARTNER = '''+@mirrorendpoint+''''
        print @sql
        print 'GO'
 
       
FETCH NEXT FROM db_cursor INTO @name  
END  

CLOSE db_cursor  
DEALLOCATE db_cursor

 

This script will generate the output to run on your MIRROR instance as follows:

ALTER DATABASE "SP2010_SharePoint_ConfigDB" SET PARTNER = 'TCP://SQL1.sp2010.local:5022'
GO
ALTER DATABASE "SP2010_SharePoint_CentralAdmin_Content" SET PARTNER = 'TCP://SQL1.sp2010.local:5022'
GO
ALTER DATABASE "SP2010_SharePoint_MySites_Content1" SET PARTNER = 'TCP://SQL1.sp2010.local:5022'
GO
ALTER DATABASE "SP2010_SharePoint_Sites_Content1" SET PARTNER = 'TCP://SQL1.sp2010.local:5022'
GO
ALTER DATABASE "SP2010_SharePoint_State" SET PARTNER = 'TCP://SQL1.sp2010.local:5022'
GO
ALTER DATABASE "SP2010_SharePoint_SessionState" SET PARTNER = 'TCP://SQL1.sp2010.local:5022'
GO
ALTER DATABASE "SP2010_SharePoint_SecureStore" SET PARTNER = 'TCP://SQL1.sp2010.local:5022'
GO
ALTER DATABASE "SP2010_SharePoint_Search" SET PARTNER = 'TCP://SQL1.sp2010.local:5022'
GO
ALTER DATABASE "SP2010_SharePoint_MetaData" SET PARTNER = 'TCP://SQL1.sp2010.local:5022'
GO
ALTER DATABASE "SP2010_SharePoint_People_Profile" SET PARTNER = 'TCP://SQL1.sp2010.local:5022'
GO
ALTER DATABASE "SP2010_SharePoint_Search_CrawlStore1" SET PARTNER = 'TCP://SQL1.sp2010.local:5022'
GO
ALTER DATABASE "SP2010_SharePoint_Search_PropertyStore1" SET PARTNER = 'TCP://SQL1.sp2010.local:5022'
GO
ALTER DATABASE "SP2010_SharePoint_People_Social" SET PARTNER = 'TCP://SQL1.sp2010.local:5022'
GO
ALTER DATABASE "SP2010_SharePoint_WordConversion" SET PARTNER = 'TCP://SQL1.sp2010.local:5022'
GO
ALTER DATABASE "SP2010_SharePoint_Analytics_Warehouse" SET PARTNER = 'TCP://SQL1.sp2010.local:5022'
GO
ALTER DATABASE "SP2010_SharePoint_BCS" SET PARTNER = 'TCP://SQL1.sp2010.local:5022'
GO
ALTER DATABASE "SP2010_SharePoint_FoundationSearch" SET PARTNER = 'TCP://SQL1.sp2010.local:5022'
GO
ALTER DATABASE "PerformancePoint Service Application_2757dc3dcb96455799c3e33c55f40b5d" SET PARTNER = 'TCP://SQL1.sp2010.local:5022'
GO

Run this on the MIRROR.

Next go back and change the @mirrorendpoint variable in the above T-SQL script to point to your MIRROR instance.  Then run the resulting script on the PRINCIPAL.

Next, on the PRINCIPAL, go back and change the statement in the cursor from SET PARTNER to SET WITNESS.  Then change the @mirrorenpoint variable to @witnessendpoint.  Run the script to generate output, and run the output on the PRINCIPAL instance.

The databases should be mirrored now.  You can tell this by looking at the state of the databases on the MIRROR instance – they will look something like this:

Likewise the PRINCIPAL instance will show a mirrored state:

Go back to your SharePoint farm and add the failover database instance to point to the MIRROR server.  We can start up our SharePoint server again now that the databases are mirrored.

You can do this with the following Powershell.  NOTE:  Replace the –inotmatch statements with the names of the specific databases you do not want to point to your MIRROR 

Get-SPDatabase |?{$_.Type.ToString() -inotmatch "SP2010_SharePoint_Analytics_Stager1" 

-and $_.Type.ToString() -inotmatch "SP2010_SharePoint_People_Sync" -and $_.Type.ToString() 

-inotmatch "SP2010_SharePoint_Usage"}| %{$_.AddFailoverServiceInstance("SQL2");$_.Update()}

You can confirm this worked by going into Central Administration and selecting manage Content Databases under Application Management.  Select the Content Database, and you should see the Failover Database Server value populated.

 

This should serve are a step by step extensive guide on exactly how to set up your SharePoint servers for Database Mirroring.

Now you are all ready to perform the test of this by nuking your PRIMARY and watching the fun begin!!!

Credits

I would like to thank two of my classmates from the MCM (R7) program for SharePoint 2010, Aaron Saikovski and Alex Bacchin for some of the content and the original T-SQL scripts for this.