Database Mirroring and SharePoint 2010

I was recently tasked with creating a VM environment that had all of the SharePoint Databases Mirrored.  It didn’t need to be anything fancy, so I did not need a witness server.  I found out something interesting when attempting to mirror the Service Application Databases. SharePoint 2010 now makes it fairly easy to register which databases are mirrored and where the mirror is located for some of the databases through Central Administration.  Not quite all of them though.  First we are going to go ahead and set up mirroring for a content DB so that we can get that out of the way. (This took me a while since everything online always seemed to miss one crucial step.)  Once you complete this little guide, you will have a mirrored database that SharePoint is aware of.  After that, I will go through all of the databases that can be set up for mirroring through Central Administration. (I know! Fun! Right?)

Okay first things first. We need to set up our Database Servers for mirroring.  This is a simple set up and you should be able to get this set up using SQL Server Management Studio [SSMS] with no problems.  I am not a DBA so check with your local expert for production environments.  There are probably better and more secure ways to accomplish this.

You will need at least two Database Servers, one as your primary, and one as your mirror.  Go ahead and install SharePoint and configure everything you want. (Sorry Todd, in the interest of setting up my farm quickly, I used that evil wizard thing that puts GUIDs everywhere. I think it even tried to apply GUIDs to my socks.) Once SharePoint is up and running, start SSMS on your primary database server.  Double check that the database that you want to mirror is set to Full Recovery mode. Next you will need to create a backup of the database that you wish to mirror. The section below can be entered into to SSMS.

BACKUP DATABASE WSS_Content

TO DISK = ‘C:\WSS_Content.bak’

WITH FORMAT

GO

BACKUP LOG WSS_Content

TO DISK = ‘C:\WSS_Content_Log.bak’

WITH FORMAT

Now we have a set of backup files that we can copy over to our mirror server. While we are here, let’s go ahead and set up the mirroring endpoint on the primary database server.

DROP ENDPOINT Mirroring

GO

DROP CERTIFICATE A_PRIMARY_CERT

GO

DROP MASTER KEY

GO

CREATE MASTER KEY

ENCRYPTION BY PASSWORD = ‘Password#1′

GO

CREATE CERTIFICATE A_PRIMARY_CERT

WITH SUBJECT = ‘A_PRIMARY_CERT for database mirroring’,

START_DATE = ’01/11/2006′, EXPIRY_DATE = ’01/01/2099′

GO

CREATE ENDPOINT Mirroring

STATE = STARTED

AS TCP (LISTENER_PORT= 5022, LISTENER_IP = ALL)

FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE A_PRIMARY_CERT,

ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = PARTNER)

GO

BACKUP CERTIFICATE A_PRIMARY_CERT

TO FILE = ‘C:\Certificates\A_PRIMARY_CERT.cer’

GO

This may generate some errors if you have not ever enabled mirroring on the server since it attempts to drop a mirroring endpoint to ensure that we are using a clean mirroring instance. This is ok.  You probably also want to change the certificate password to something  a little more secure than “Password.” Once you do this you will need to copy the two backup files and the certificate to your mirror server.

Once we have that now we need to move over to our mirror server and set up the mirror of our primary database.  Once again let’s open up SMSS this time on the mirror server and try the following.

RESTORE DATABASE WSS_Content

FROM DISK = ‘C:\WSS_Content.bak’

WITH NORECOVERY

GO

RESTORE LOG WSS_Content

FROM DISK = ‘C:\WSS_Content_Log.bak’

WITH NORECOVERY

Once this is complete, you should be able to see the database on the mirror server, stuck in a “restoring” state. Once the database is in place, let’s go ahead and set up the mirroring endpoint here as well.

DROP ENDPOINT Mirroring

GO

DROP CERTIFICATE B_SECONDARY_CERT

GO

DROP MASTER KEY

GO

CREATE MASTER KEY

ENCRYPTION BY PASSWORD = ‘control*88′

GO

CREATE CERTIFICATE B_SECONDARY_CERT

WITH SUBJECT = ‘A_SECONDARY_CERT for database mirroring’,

START_DATE = ’01/11/2006′, EXPIRY_DATE = ’01/01/2099′

GO

CREATE ENDPOINT Mirroring

STATE = STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)

FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE B_SECONDARY_CERT,

ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL)

GO

BACKUP CERTIFICATE B_SECONDARY_CERT

TO FILE = ‘C:\certificates\B_SECONDARY_CERT.cer’

GO

We will need to copy that certificate that we just created back to the primary database server. Once we do that, we need to go ahead and create the logins that we will use for mirroring on both servers. Back to SMSS on the primary.

DROP CERTIFICATE B_SECONDARY_CERT

GO

DROP USER MIRROR_SECONDARY_USER

GO

DROP LOGIN MIRROR_SECONDARY_LOGIN

GO

CREATE LOGIN MIRROR_SECONDARY_LOGIN

WITH PASSWORD = ‘Password#2′

GO

CREATE USER MIRROR_SECONDARY_USER

FOR LOGIN MIRROR_SECONDARY_LOGIN

GO

CREATE CERTIFICATE B_SECONDARY_CERT

AUTHORIZATION MIRROR_SECONDARY_USER

FROM FILE = ‘C:\certificates\B_SECONDARY_CERT.cer’

GO

GRANT CONNECT ON ENDPOINT::Mirroring

TO MIRROR_SECONDARY_LOGIN

GO

Oh looky! There’s another password you will probably want to change.  You may see some errors here as well since the certificate probably does not exist, but we want to make sure that our mirroring environment is as clean as possible. Back over to the mirror server so that we can set up the logins that we will need there.

DROP CERTIFICATE A_PRIMARY_CERT

GO

DROP USER MIRROR_PRIMARY_USER

GO

DROP LOGIN MIRROR_PRIMARY_LOGIN

GO

CREATE LOGIN MIRROR_PRIMARY_LOGIN

WITH PASSWORD = ‘Password#3′

GO

CREATE USER MIRROR_PRIMARY_USER

FOR LOGIN MIRROR_PRIMARY_LOGIN

GO

CREATE CERTIFICATE A_PRIMARY_CERT

AUTHORIZATION MIRROR_PRIMARY_USER

FROM FILE = ‘C:\certificates\A_PRIMARY_CERT.cer’

GO

GRANT CONNECT ON ENDPOINT::Mirroring

TO MIRROR_PRIMARY_LOGIN

GO

Another password that you want to change and we are almost there.  Now all we need to do is make our primary and mirror databases aware of each other. We need to set this on our mirror server first. Of course you need to change the name of your server as appropriate.

ALTER DATABASE WSS_Content SET PARTNER = ‘TCP://<principal-server-name>:5022′

Once that is set, we need to tell our primary to start mirroring the database. Back to the primary for the final SSMS Step.

ALTER DATABASE WSS_Content SET PARTNER = ‘TCP://<mirror-server-name>:5022′

Now when we look at the databases in SSMS on the primary database server it should say (Principal, Synchronized) after the Database name. On our mirror server it should say (Mirror, Synchronized / Restoring…) after the database name.

If everything went smoothly, you now have a mirrored database for your SharePoint farm. Congratulations! There is one small problem however. SharePoint has no clue that this database is mirrored, so if you test out your mirrored instance, SharePoint will suddenly stop working. Go ahead and give it a shot now. On your primary, right click on your WSS_Content database that we just mirrored, then click on “Tasks” and then “Mirror.” When the mirroring page comes up, click on the button labeled “Failover.”

You will get a warning that your mirror and your primary are going to switch. Switching them back is just as easy so go ahead.  Now the database on your primary database server should say (Mirror, Synchronized / Restoring…) and if you check out what used to be your mirror database, it is suddenly your principal.

Try to browse to your site though.  I can almost guarantee that it won’t work.  Fortunately there is an easy fix for this.  Go ahead and switch your databases back. (Log into your mirror server, select your content database and hit the failover button. You should be back where you started.)  Now that you are back to where you started and have calmed down, go ahead and log into your Central Administration and get to the page for managing content databases. Click on the link for your content database that we just mirrored, and on the configuration page that pops up enter the name of your mirror server in the Failover Database Server field.

Just the name of the server, you shouldn’t need the whole mirror endpoint name. Click OK. Now let’s try that whole failover exercise again.

Push the button to switch your databases, and then browse to your site.  Like magic everything just works.  Pat yourself on the back. Here’s some more good news. Since you already have the certificates and endpoints set up, all you need to do to mirror other content databases is backup and restore them, and then set the partners.

Technically you can mirror every SharePoint database, including Service Application Databases, your SharePoint Config Database and your Administration Content Database. The real trick is making SharePoint aware of the mirrored instance so that if the primary does have a hiccup and you have to switch to your mirror, SharePoint won’t bonk out and will just continue along as if nothing happened.

 

For a few of your Service Applications, setting up SharePoint for mirrored databases is as simple as editing the properties of the service application.  For Business Data Connectivity, Managed Metadata, Secure Store, and Word Automation Service Application databases all you need to do is add the name of the mirror server, just like with a content database.  It appears that you can do the same thing with the User Profile Service, but I recall reading somewhere that UPS doesn’t like database mirroring, so use with caution.  You can also mirror the Search Databases and the search index files, but it’s a little bit tougher.  We’ll get to that.

To set up those few Service Applications for mirroring, all you need to do is edit the properties.

As simple as that and your Service Application  won’t have any trouble seeing the mirrored database. Now let’s get the Search services mirrored.  Foundation search is as simple as any other Service Application.  We are going to focus on Enterprise Search since it is a little more involved. In order to add our mirrored databases we need to modify the topology of our Search Service.  In Central Administration, navigate to your Search Administration page and click on the “Modify” button. From here we can add mirror instances to all of our search components.  Go ahead and click on the link to “Administration Component” and then click on “Edit.”

This will pop up a window that allows us to add the Failover Database Server just like in our other Service Applications. Interestingly enough, the Administration Component and Administration Database properties are the same thing. (Same Database, same options, etc.) We can also add a mirror for our Index files here if we so choose.  To do this, click on “Query Component” and then on “Add Mirror.”  You can choose a WFE from your farm and the location where you would like the Index files maintained, and you can choose to set the Index as “Failover only.”

None of these changes will go into effect however until we click the button that says “Apply Topology Changes.”  Click that, then wait a bit, and then your Search Service is set up.  In order to set up your other SharePoint Databases for mirroring, you will need to dig in and open up PowerShell.  I’m not going to describe that here, since other people smarter than me already have.  But this should get you started at least.

Advertisement