Share via

SharePoint 2010: Configuring SQL Server Mirroring

  • Article

Here is we will configure SharePoint 2010 environment high availability with SQL Mirroring.

Microsoft SQL Server database mirroring provides availability support by sending transactions directly from a principal database and server to a mirror database and server when the transaction log buffer for the principal database is written to disk. For availability within a Microsoft SharePoint Server 2010 farm, you use high-availability database mirroring, also known as high-safety mode with automatic failover. High-availability database mirroring involves three server instances: a principal, a mirror, and a witness. The witness server enables SQL Server to automatically fail over from the principal server to the mirror server. Failover from the principal database to the mirror database typically takes several seconds.

Within a SharePoint Server 2010 farm, mirroring can provide redundancy for the content and configuration databases, and for many service databases. Even if your databases are mirrored to the same server, each database fails over individually. The following figure shows how mirroring is configured to provide availability within a SharePoint Server 2010 farm.

http://tmitegypt.files.wordpress.com/2013/05/mirroring_concept.png?w=487

For more information about Configure availability by using SQL Server database mirroring (SharePoint Server 2010) refer to http://technet.microsoft.com/en-us/library/dd207314(v=office.14).aspx

our environment will mainly contain Principal, Mirror and Witness SQL servers.

SharePoint side configuration:

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()}

SQL Side:

  • 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

  • Set all database recovery model to Full
  • The next step is to Backup all databases and logs on the Principal server except the system databases (master, temp, msdb, and model), you can do this by creating new Maintenance Plan to backup all databases then select tasks Back Up Database Task

http://tmitegypt.files.wordpress.com/2013/05/sqlbackup.png?w=487

 

 

http://tmitegypt.files.wordpress.com/2013/05/sqlbackup_trn.png?w=487

 

  • Restore all databases on SQL Mirroring server with below configuration:

http://tmitegypt.files.wordpress.com/2013/05/recoverystate.png?w=487

  • Restore Log files over the database with below configuration:

http://tmitegypt.files.wordpress.com/2013/05/recoverystate1.png?w=487

Configure Mirroring:

  • Right click on the database to configure DB Mirroring >> Tasks >> Mirror

http://tmitegypt.files.wordpress.com/2013/05/mirror1.png

  • On Mirror windows click on Configure Security

http://tmitegypt.files.wordpress.com/2013/05/mirror21.png

  • On Principal database click next

http://tmitegypt.files.wordpress.com/2013/05/mirror41.png

  • On Mirror Database from Server instance select Mirror server and click Connect and select SQL authentication then click OK

http://tmitegypt.files.wordpress.com/2013/05/mirror51.png

http://tmitegypt.files.wordpress.com/2013/05/mirror61.png

  • On Witness server configuration select witness server instance and click connect same as previous step.

http://tmitegypt.files.wordpress.com/2013/05/mirror71.png

  • On service account page type services account and if principal, mirror and witness server on same domain click finish.

http://tmitegypt.files.wordpress.com/2013/05/mirror8.png?w=487

  • On summary page check configuration and click finish.

http://tmitegypt.files.wordpress.com/2013/05/mirror91.png

http://tmitegypt.files.wordpress.com/2013/05/mirror101.png

  • Message appears, click on Start Mirroring.

http://tmitegypt.files.wordpress.com/2013/05/mirror111.png

http://tmitegypt.files.wordpress.com/2013/05/mirror121.png

 

http://tmitegypt.files.wordpress.com/2013/05/db_mirror_01.png

http://tmitegypt.files.wordpress.com/2013/05/db_mirror_02.png

Go ahead restart SQL 1st server :) do not worry seconds failover will do the work.

Note: For who are using FBA "Form based Authentication" you will need to configure connection string under Provider under IIS to include Mirror server using Failover Partner as below:

Server=SQL01;Failover Partner=SQL02;Database=aspnetdb;User ID=**;Password=