How to set up database mirroring on a SharePoint live system

Scenario:
            Let's take a SharePoint 2010 farm installation with two front-ends WFE01 and WFE02 and two SQL Server 2008 R2 instances called SQLDB01 and SQLDB02.
            Let's install a third SQL Server instance (which can be SQL Express) to serve as the Witness in this mirroring scenario. We will use the SQLDB02 hard drive and the instance will be called SQLDB02\Witness.
            When installing the SharePoint farm, point both web front end instances to the same SQL Server SQLDB01. We will keep SQLDB02 separate from the farm, with SQLDB01 mirrored into SQLDB02 for fail-over in case of disaster.

The instructions below are supporting a high-availability synchronous mirroring configuration. This means that a SQL Server serving as a witness needs to be present, to ensure automatic fail-over. Otherwise, some manual configuration is needed in case of disaster. The table below describes all types of mirroring:

Two main actions need to be taken to enable mirroring:

• Configure mirroring in SharePoint Central Administration  at Web Application level - by using the UI
• Configure SQL Mirroring on the SQL Server instances - by running several sql scripts

In detail:

1. Configure mirroring in SharePoint for the web application on port 80 with database WSS_Content
   
   
   
2. Run step by step:

• Run on SQLDB01 for each database you want mirrored. Only mirrored databases will be available in case of disaster. This script will set full recovery on the main WSS_Contect database to be mirrored. Full recovery needs to be set, in order to take a transactional log backup, which is necessary for mirroring.

alter database WSS_Content set recovery full
go

• Take full backups of all SharePoint databases and place the backups into the folder “U:\Backups\”.

backup database WSS_Content to disk = 'U:\Backups\WSS_Content.bak' 
go

• Take transactional log backups and save them with “*_log.bak” into the folder “U:\Backups\”.

BACKUP LOG WSS_Content to disk = 'U:\Backups\WSS_Content_log.bak'
go

• Restore all databases on SQLDB02 in “NORECOVERY” mode.

restore database WSS_Content from disk = 'T:\restores\WSS_Content.bak' with norecovery, 
move 'WSS_Content' TO 'T:\DATA\WSS_Content.mdf',
move 'WSS_Content_log' TO 'U:\LOGS\WSS_Content_log.ldf' 
go

• Restore all transactional logs on SQLDB02 in “NORECOVERY” mode:

restore log WSS_Content from disk = 'T:\restores\WSS_Content_log.bak' with norecovery
go

• Enable mirroring by following the instructions:

--enable mirroring on SQLDB01 - principal, SQLDB02- mirror and SQLDB02\Witness as witness
--run this on SQLDB01 and on SQLDB02
CREATE ENDPOINT Mirroring
     STATE=STARTED
     AS TCP(LISTENER_PORT=5022)
     FOR DATABASE_MIRRORING(
     AUTHENTICATION=WINDOWS NEGOTIATE,
     ENCRYPTION=SUPPORTED,
     ROLE=PARTNER)
     GO 
--run on SQLDB02\Witness - note different port because it has the same ip as the mirror
CREATE ENDPOINT Mirroring
     STATE=STARTED
     AS TCP(LISTENER_PORT=5023)
     FOR DATABASE_MIRRORING(
     AUTHENTICATION=WINDOWS NEGOTIATE,
     ENCRYPTION=SUPPORTED,
     ROLE=WITNESS)
     GO
--run this on the mirrored instance SQLDB02
ALTER DATABASE WSS_Content
SET PARTNER= N'TCP://SQLDB01:5022'
GO
--run this on SQLDB01
ALTER DATABASE WSS_Content
SET PARTNER= N'TCP://SQLDB02:5022'
GO
--run this on SQLDB01 to assign the witness
ALTER DATABASE WSS_Content
SET WITNESS= N'TCP://SQLDB02:5023'
GO

• Test that mirroring is working by checking if each database is marked with “Principal” or “Mirror”.
• Test that mirroring is working by stopping the SQL Server service on SQLDB01 and testing that the site on port 80 is still working. When SQLDB01 is down, the SQLDB02 databases should not show “Mirror” anymore. 
• If you restart the service on SQLDB02 and stop it on SQLDB01, SQLDB01 will once again become the principal. If you restart SQLDB02 now, this will become the mirror. The witness makes sure the only server standing is always the principal to ensure latest data integrity.