Step-by-Step Guide to Configuring Database Mirroring in SharePoint 2010
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:
3. Make sure that the database is in the Full recovery model
4. Perform a FULL backup of the database you are going to mirror:
5. Place the backup to a place where the mirror server can access it. I dropped it on a network share:
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.
7. On the restore page you MUST select a RESTORE WITH NORECOVERY under the Options menu. This is required for DB mirroring to work:
8. Assuming your restore was successful, the database will always show as "Restoring…":
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…
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.
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
12. Continue the wizard and your endpoints will get created:
13. Click close and you will get the next message with some information. Click the Start Mirroring button and hopefully it works (:
14. You should get no errors and your Mirroring page should look similar to mine. (I postponed the configuration of my witness):
15. Your databases will indicate that mirroring is configured:
(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:
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:
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.
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.
Click it and make sure SharePoint is still working for you! Hope this helps!
Ref: http://www.sharepoint.bg/radi/post/Step-by-Step-Guide-to-Configuring-Database-Mirroring-in-SharePoint-2010.aspx
***********************************************************************
Step 2
Step 3
As shown in the below screen, right click on the database, select properties & select Mirroring. Select Configure Security to configure mirroring for the required database.
Follow the screen shots as given below.
Note that here I do not have a Witness server, & hence select “NO” for include Witness server option.
Step 4
Select the Mirror Server Instance as shown below & click connect. Here we have to specify the destination, i.e. Mirror database server’s credentials to connect.
Step 5
Leave service accounts for both Principal & Mirror blank, follow the remaining screen shots.
Step 6
Click on Start Mirroring to start the mirroring from Principal database to Mirror database.
Step 7
As you can see in the below screen shot, the status is, the databases are fully Synchronized.
Step 8
The MirrorDB in the source server is now marked as Principal, Synchronized.
Step 9
The MirrorDB in the destination server is now marked as Mirror, Synchronized (Restoring)
Step 10
You can do manual failover as shown below, by selecting Failover option.
Step 11
The Principal database is now changed to Mirror & the Mirror is changed to Principal as shown in the below screen shots.
Mirroring is a simple process & if failed we can easily reset it up, by restoring the latest backup of the source in the destination & reconfigure Mirroring.
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.
3. Make sure that the database is in the Full recovery model
4. Perform a FULL backup of the database you are going to mirror:
5. Place the backup to a place where the mirror server can access it. I dropped it on a network share:
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.
7. On the restore page you MUST select a RESTORE WITH NORECOVERY under the Options menu. This is required for DB mirroring to work:
8. Assuming your restore was successful, the database will always show as "Restoring…":
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…
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.
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
12. Continue the wizard and your endpoints will get created:
13. Click close and you will get the next message with some information. Click the Start Mirroring button and hopefully it works (:
14. You should get no errors and your Mirroring page should look similar to mine. (I postponed the configuration of my witness):
15. Your databases will indicate that mirroring is configured:
(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:
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:
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.
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.
Click it and make sure SharePoint is still working for you! Hope this helps!
Ref: http://www.sharepoint.bg/radi/post/Step-by-Step-Guide-to-Configuring-Database-Mirroring-in-SharePoint-2010.aspx
***********************************************************************
Here is the detailed step by step implementation of Database Mirroring with screen shots. Note that I have shown here is a High Protection Mirroring scenario.
Here we are setting up a synchronous operating mode.
Step 1
Create MirrorDB in the source database (called as Principal database), for which we are going to start mirroring.
Create a backup of the MirrorDB in the source SQL Server (Principal database) & restore it in the destination server with the same name. Make sure to restore the database with NORECOVERY. The destination server MirrorDB is called as Mirror database.
As shown in the below screen, right click on the database, select properties & select Mirroring. Select Configure Security to configure mirroring for the required database.
Follow the screen shots as given below.
Note that here I do not have a Witness server, & hence select “NO” for include Witness server option.
Step 4
Select the Mirror Server Instance as shown below & click connect. Here we have to specify the destination, i.e. Mirror database server’s credentials to connect.
Step 5
Leave service accounts for both Principal & Mirror blank, follow the remaining screen shots.
Step 6
Click on Start Mirroring to start the mirroring from Principal database to Mirror database.
Step 7
As you can see in the below screen shot, the status is, the databases are fully Synchronized.
Step 8
The MirrorDB in the source server is now marked as Principal, Synchronized.
Step 9
The MirrorDB in the destination server is now marked as Mirror, Synchronized (Restoring)
Step 10
You can do manual failover as shown below, by selecting Failover option.
Step 11
The Principal database is now changed to Mirror & the Mirror is changed to Principal as shown in the below screen shots.
Mirroring is a simple process & if failed we can easily reset it up, by restoring the latest backup of the source in the destination & reconfigure Mirroring.
Comments
Post a Comment