Yes, while SharePoint does not have the native ability to recognize a SQL Server 2008 Database Mirroring Session, you can configure it in such a way that you can protect your SharePoint installation with high availability for a lower cost. (It should be noted that SharePoint is recommended to run on a SQL Server cluster)
I recently implemented SharePoint on SQL Mirrors for a client, and the following is the method which I chose to use. (Some images have been redacted)Background
Setting up mirroring on SQL Server to be utilized by a SharePoint installation requires some additional steps to achieve automatic failover. SharePoint is not a mirroring aware product, it does not allow for capturing the FAILOVER PARTNER attribute for connection strings. In order to accomplish this you have a couple of options.
First you can manually alter the connection string settings in the configuration files for SharePoint. As this is not the recommended or selected approach it will not be discussed, other than to state that the danger in this is any upgrade or service pack could cause configuration files to be overwritten and the addition of the FAILOVER PARTNER attribute in the connection strings could be lost.
The selected method of running SharePoint against databases that are mirrored is described in detail below.
As a starting point we used the Microsoft White Paper “Using SQL Server Mirroring with Office SharePoint Server® and Windows SharePoint Services®”. This document can be accessed via the link below.
Using Database Mirroring with Office SharePoint Server and Windows SharePoint Services
The white paper describes how to set up the mirroring, how to set up a SQL Alias for SharePoint to use as a data source, and how to set up SharePoint to use that SQL Alias. When the database mirroring session fails over to the mirror server, the SQL Alias on the SharePoint servers can be modified to point to the mirror server, which has become the principal server, thereby removing the need to reconfigure SharePoint. Because the above white paper does not provide a method for automating the failover from the principal database server to the mirror server, we had to derive how to do this on our own. Following is the detailed documentation of the steps involved in automating the switch over for SharePoint to use the alternate database server.
Remotely updating the SQL Server Alias
The first step to achieving automated failover for SharePoint is to automate the update to the SQL Server Alias on each SharePoint Web Front End server and Application server. The SQL Alias configuration is held within the Windows Registry on each individual server, therefore a process must be run to update the value of the SQL Alias to change the Server attribute portion to match the currently active database principal. The registry value is held at HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo\<SQL Alias Name>. <SQL Alias Name> is a REG_SZ key with a comma separated value that represents the connection type and server and if necessary the port number.
We created A pair of batch files to reside on each server. The pair of batch consists of one file for each database server in the mirroring session. For development we have chosen to place the files within a folder named “SQLServerMirrorScripts” on the root of drive C:. Note, we could have chosen different options for this, such as PowerShell scripts but due to client standards and preference we were limited.
Note the content of one of the batch files:
| set DBSERVERALIAS=SQLAlias set DBSERVER=DBSrv2 reg add HKLM\Software\Microsoft\MSSQLServer\Client\ConnectTo /v %DBSERVERALIAS% /t REG_SZ /d "DBMSSOCN,%DBSERVER%" /f reg query HKLM\Software\Microsoft\MSSQLServer\Client\ConnectTo |
The next step is to find a way to execute the batch files from a remote server, the database servers, but within the process space of the target server, the MOSS servers. In order to accomplish this we are used a utility called psexec.exe. You can attain this utility from Microsoft at Microsoft PSTools Page. This utility is a DOS based program that will remotely execute a process on a server under the currently logged in user context or under the user provided by command line argument. Each database server has another pair of batch files that encapsulate the execution command for the appropriate SQL Alias editing batch file on the remote server. These files, along with the psexec utility executable reside on each database server in a folder called “SQLServerMirrorScripts” in the root of drive C:
Again, each batch file is named for the database server it is going to change the SQL Alias too. Each batch file executes the remote batch file appropriate, designated in its name, to the operation. Below is the content of these batch files. These files exist in the same location on each of the database servers.
| C:\SQLServerMirrorScripts\psexec.exe \\SharePointServer C:\SQLServerMirrorScripts\SQLAlias1.bat -d -e |
At this point we have a process that will allow us to change the SQL Server Alias value from a remote location. When using psexec.exe for the first time from a server, you must manually execute the application and accept the license agreement that will popup. If this step is skipped the process will fail, because it will always wait for license acceptance.
Automating the SQL Server Alias Update
While the previous steps will allow us to modify the SQL Server Alias from each database server, they do not provide for the automatic updating of the alias in response to a database failing over from the principal server to the mirror server. In order to accomplish the automation, several additional steps are required.
Enabling Service Broker for each database
In order to enable automation, there must be a mechanism in place to respond to an event. In order to provide event notification in SQL Server, Service Broker must be enabled for all the mirrored databases and the msdb database. If you are unsure if Service Broker is enabled for your database you can execute the following query to find out.
| USE master Go SELECT name, is_broker_enabled FROM sys.databases |
This will list the databases and a binary value for the state of is_broker_enabled, 0 is no, and 1 is yes.
By default, SharePoint databases are created with Service Broker enabled, but if there is a need to enable it after the database is created, the following command can be issued in SQL Server Management Studio;
| ALTER DATABASE <DATABASE NAME> SET ENABLE_BROKER |
You must also ensure that SQL Server Agent is running. If it is not, you must start the service, either via the services control snap in
or command line.
| net start SQLSERVERAGENT |
Setting Up the Event Notification
DATABASE_MIRRORING_STATE_CHANGE is not one of the events that can be automatically trapped. The event is available as a manually created Event Notification.
You will need to create an event notification named DBMirrorStateChange to raise a notification for the DATABASE_MIRRORING_STATE_CHANGE event, on both database servers in the mirroring session (not the witness):
| If Not Exists (Select 1 From sys.server_event_notifications Where name = 'DBMirrorStateChange') Begin Create Event Notification DBMirrorStateChange On Server For DATABASE_MIRRORING_STATE_CHANGE To Service 'DBMirrorService', 'current database'; End |
This code need only be execute for a single database, denoted above as ‘current database’, in order to create the event notification that will be used for any database the has a mirroring state change.
If it ever becomes necessary to remove an event notification you can use the code below;
The DATABASE_MIRRORING_STATE_CHANGE event notification provides a property that denotes what type of state change has happened. This “state” property will inform us of what type of state change has occurred. The possible states for the event are as follows;
Creating an Alert for Automatic Failover captureWe are interested in State 8 for this step. State 8 indicates an automatic fail over has occurred. This event is raised on the mirror server when a database that is acting as the mirror, changes state to acting as the principal. The mechanism we use for responding to an event notification is an alert. An alert can be created via SQL Server Management Studio (SSMS), or via T-SQL script. The SSMS method will be shown here. Locate and expand the SQL Server Agent node in the SSMS object tree view.
Right click on Alerts and select “New Alert”. Enter a name for the Alert in the Name: attribute box, select WMI event alert in the Type drop down list. The Namespace: value will be populated automatically for the server you are on. Note: you will have to do this on both servers, but you can script the alerts and jobs from the first server and use those scripts on the second server as shown later. In the Query: box you will write the query based on the DATABASE_MIRRORING_STATE_CHANGE event. Select the Response page and check the box for “Execute Job”, then select the “New Job” button. This will create a new job and open the Job properties dialog for you. Enter a job name, owner and select the category as Database Mirroring. If the category “Database Mirroring” is not available select “Uncategorized (Local)”. Enter a description to detail the purpose of the job, and ensure Enabled is checked. Select the Steps page on the left next. And select the “New” Button on the bottom. This will launch the Job Step properties page. Enter a name for the job step you are creating, select the type, in this case we are executing a batch file so select “Operating System (CmdExec)”, and chose the Run As account from the drop down. This will be the user that the process will run under. In the Command box input the batch file for the server you are working on and click “OK”. Back on the Alert properties page now, click “OK” to finish creating the Alert. We now have an Alert that will capture the event that a database has gone from Mirror to Principal and take appropriate action. When the event occurs, the Alert is activated if the state value is 8. The Alert then executes the Job that was created, and the job executes the batch file which executes the psexec utility to execute the batch file on the SharePoint server, which modifies the registry to change the server value for the SQL Server Alias. Creating an Alert for Manual Failover captureA manual fail over is different than an automatic fail over, in that it fires an event with state 7 that occurs on the Principal server where the fail over command originated. Because SharePoint is a multi-database application, it is desired that all databases reside on the same server at all times. For automatic fail overs, it would be rare that the fail over would happen at a database level. Normally it will occur because the server went down, the network card failed, the disks failed, or some other catastrophic event occurred which rendered the Principal server inoperable. Manual fail over occurs when the Fail Over button is pressed in the Mirroring properties of the database or when certain T-SQL commands are issued. Manual fail over occurs at the database level, therefore a mechanism to automate manual fail over for the SharePoint instance, must not only change the SQL Server Alias, but also cause the other SharePoint databases to fail over to the Mirror. We will follow the same steps as for Automatic fail over, but we will add a new step to the job before the alias update step. Locate and expand the SQL Server Agent node in the SSMS object tree view.
Right click on Alerts and select “New Alert”. Enter a name for the Alert in the Name: attribute box, select WMI event alert in the Type drop down list. The Namespace: value will be populated automatically for the server you are on. Note: you will have to do this on both servers, but you can script the alerts and jobs from the first server and use those scripts on the second server as shown later. In the Query: box you will write the query based on the DATABASE_MIRRORING_STATE_CHANGE event. Note that we are looking for State 7 in this event response. Select the Response page and check the box for “Execute Job”, then select the “New Job” button. This will create a new job and open the Job properties dialog for you. Enter a job name, owner and select the category as Database Mirroring. If the category “Database Mirroring” is not available select “Uncategorized (Local)”. Enter a description to detail the purpose of the job, and ensure Enabled is checked. Select the Steps page on the left next. And select the “New” Button on the bottom. This will launch the Job Step properties page.  Enter a name for the job step you are creating, select the type, in this case we are a SQL Server query so select “Transact-SQL script (T-SQL)”. Leave the “Run As” box empty. In the Command box input the code shown and click “OK”.
This code will force all databases on the server to become the “FailOver Partner” or Mirror, which causes the existing Mirror, or “FailOver Partner” to become the principal. Back on the Job properties Steps page now, click “New” to create another step. This step will be identical to the job step for automatic failover, with the exception of which batch file it calls. Enter the values as below and click “Ok”.
Click “OK” on the Job Properties pages. We now have a job with two steps to force all databases to fail over if one does, and to update the SQL Server Alias after that. Back on the Alert Property page, click “Ok” to finish creating the alert. We now have an Alert that will capture the event that a database has been manually failed over and take appropriate action. When the event occurs, the Alert is activated if the state value is 7. The Alert then executes the Job that was created, and the job executes the T-SQL code to fail over the remaining databases and then the batch file which executes the psexec utility to execute the batch file on the SharePoint server, which modifies the registry to change the server value for the SQL Server Alias. Of course you have to do the same for the other server in the mirroring set. The procedure is the same, just in reverse of what is shown here, as for which batch file to call when. That’s all for this post. Feel free to leave comments or ask questions. I’ll be happy to answer any. I’ve also included some useful links below. Useful Linkshttp://msdn.microsoft.com/en-us/library/ms188712.aspx |
Posts
