Step-by-Step Guide to Configuring Database Mirroring in SharePoint 2010

by Ради Атанасов 24. September 2010 09:16

I was looking up some material on database mirroring, and I could find a good step-by-step post on setting up such functionality. So here it is!

Read these posts before you begin - you should really understand the concepts if you are to undertake this, especially in a production environment.

http://technet.microsoft.com/en-us/library/dd207314.aspx

http://blogs.technet.com/b/wbaer/archive/2010/05/03/database-mirroring-in-sharepoint-2010.aspx

http://www.sharepointbandit.com/2010/08/configure-database-mirroring-on.html

My SQL instances are based on the SQL Server 2008 R2 Standard SKU, but they we're upgraded from SQL Express 2008 R2, hence the sqlexpress names:

Mcm-server-1\sqlexpress (Primary) & Mcm-server-2\sqlexpress (Mirror)

SQL Service Account: mcm\svc_sql

Database to sync: WSS_Content_ProductionDB

For mirroring to work, the versions of SQL must be the same on both servers.

1. Configure permissions on the mirror server according to the details from the technet article:

  • The Central Administration application pool account should be a member of the dbcreator and securityadmin fixed server roles.
  • All application pool accounts, the default content access accounts, and any accounts required for service applications should have SQL Server logins, although they should not be assigned to SQL Server fixed server or fixed database roles.
  • Members of the Farm Administrators SharePoint group should also have SQL Server logins and should be members of the same SQL Server roles as the Central Administration application pool account.

2. Ensure that no firewall is blocking connectivity between the servers. In particular, you will create a TCP connection over a port, by default 5022.

3. Make sure that the database is in the Full recovery model

clip_image001

4. Perform a FULL backup of the database you are going to mirror:

clip_image002

5. Place the backup to a place where the mirror server can access it. I dropped it on a network share:

clip_image003

6. Restore the database on the mirror SQL Server. Notice how I have referenced the UNC path - SQL's interface here is not the best, you have to type in the location and file name for it to find the backup. See the next point before you proceed.

clip_image004

clip_image005

7. On the restore page you MUST select a RESTORE WITH NORECOVERY under the Options menu. This is required for DB mirroring to work:

clip_image006

8. Assuming your restore was successful, the database will always show as "Restoring…":

clip_image007

At this stage you could proceed with setting up mirroring.

9. Go to the Mirroring settings page on the Primary server's database in the Database Properties section. Go ahead and click Configure Security…

clip_image008

10. Run through the wizard. Select to set up a witness server if the situation requires one. The wizard will ask you to configure the endpoints for communication on all instances. Make sure the port is open over TCP and that you select to encrypt the data.

clip_image009

clip_image010

11. The next screen lets you specify database mirroring service accounts. In my case I left them blank as all SQL instances are running under the same service account. Read this article for more info on service accounts: http://msdn.microsoft.com/en-us/library/ms189434.aspx

clip_image011

12. Continue the wizard and your endpoints will get created:

clip_image012

13. Click close and you will get the next message with some information. Click the Start Mirroring button and hopefully it works (:

clip_image013

14. You should get no errors and your Mirroring page should look similar to mine. (I postponed the configuration of my witness):

clip_image014

15. Your databases will indicate that mirroring is configured:

clip_image015

clip_image016

(ignore the ArchiveDB in the screenshot, I had to set it up twice...)

16. To check if it is all working you could use the "Launch Database Mirroring Monitor" tool:

clip_image017

This will provide you with information, which is near real time. I uploaded a large document on the Primary server in SharePoint and refreshed the tool a few times:

clip_image018

Now it is time to tell SharePoint that we have a failover database set up. SharePoint must know this to change its connection string when the mirrors swap. To do this we must use PowerShell:

$db = get-spdatabase | where {$_.Name -eq "WSS_Content_ProductionDB"}

$db.AddFailoverServiceInstance("MCM-SERVER-2\sqlexpress")

$db.Update()

In Central Administration you will see the setting populated. Read Bill Baer's post on how exactly this setting is used.

clip_image019

As a final test, go back in SQL, go on the mirroring page and click the Failover button. This will ultimately swap the roles of your two databases.

clip_image020

Click it and make sure SharePoint is still working for you! Hope this helps!

Tags: , , ,

Missing the “Mirroring” page in SQL Server Database Properties

by Ради Атанасов 24. September 2010 06:30

I was working on setting up database mirroring on a SharePoint 2010 installation, and my “Mirroring” tab was missing in “Database Properties” in SQL Management Studio.

Configuring it through scripts was possible, but I still wanted the interface.

Here is a screenshot with the option missing:

clip_image001

My installation of SQL Server 2008 R2 Standard was upgraded from SQL Express 2008 R2. The catch here is that the “Upgrade SKU” options don’t install the "Complete” set of SQL Management Tools. So you must go through the installer and explicitly install this toolset.

clip_image001[5]

 

Now the mirroring option will be available:

clip_image001[7]

Tags: , ,

SQL Disk Space Errors in SBS 2003 R2

by Radi Atanassov 14. September 2010 00:55

I was working on a VM and stumbled upon a TEMPDB issue. I was getting disk is full errors…:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdb.mdf: Operating system error 112(There is not enough space on the disk.) encountered.

Could not allocate space for object '<temporary system object: >' in database 'tempdb' because the 'PRIMARY' filegroup is full.

This may occur for other databases as well, or for that matter, other files.

I had a look around and had appropriate disk space, so I new that wasn't the issue. SQL services we're running under a service account, so I started looking around to make sure the service account has enough rights to use large amounts of space. I couldn't expand the TEMPDB size manually in SQL Server Management Studio and Quota's in AD/Group Policy we're not configured, so that wasn't the problem.

If you right-click on the drive where TEMPDB is located, you will see that SBS has quota enabled by default (in my case it was the C: drive and that would have the setting ON by default).

Here's the Quota tab where you could disable it:

clip_image001

Turning quota management off will allow the service account to use up disk space and enlarge its TEMPDB. This should be only turned off on a development environment or if you really know what you are doing. It is also best practice to relocate the TEMPDB on a different disk for performance reasons.

Hope this helps!

Tags: ,

English

Microsoft Certified Master

SharePoint Server MVP

Radi Atanassov SharePoint MVP

About Me

SharePoint architect, consultant and solution developer. Owner and Chief SharePoint Architect of OneBit Software.

Аз съм консултант, архитект и разработчик на SharePoint решения. Собственик и Главен Архитект на OneBit Software.

Feeds

Get RSS Feed (Bulgarian)
Get RSS Feed (English)    
Get RSS Feed  (Both)        

I blog about technical concepts in English, as they benefit the whole community, but sometimes I blog about stuff specific to Bulgaria, and those I post in Bulgarian. The above feed URL's let you choose what to subscribe to.

My SharePoint Forum Activity

SharePoint User Group Bulgaria

login