- More Sharepoint Failover Farm Videos
- Storage Accounts: Durable, Highly Available, And Massively Scalable Cloud Storage
- Azure Resource Group: Container That Holds Related Resources For An Azure Solution
- Choose A Disaster Recovery Strategy For SharePoint Server ...
- Azure Active Directory: Synchronize On-premises Directories And Enable Single Sign-on
- Storage Documentation
This is a post I’ve been meaning to write for some time, but I’m only now getting around to it. It’s a quick one, and it’s intended to share a couple of observations and a script that may be of use to those of you who are SharePoint 2010 administrators.
Mirroring and SharePoint
It helps to recover from other farm, when a failure happens at the primary farm. SharePoint Central Administration website's content database and configuration database are to be built separately and must be upheld on the failover farm. Every customization needs to be employed on both farms. One of the great new improvements in SharePoint 2010 is native support for SQL mirroring and automatic failover. In SharePoint 2007 you had to use some complicated SQL aliases to provide failover support. SharePoint 2010 has built in support for database mirrors, and allows you to define a failover SQL server for any databases you have mirrored.
The use of SQL Server mirroring isn’t something that’s unique to SharePoint, and it was possible to leverage mirroring with SharePoint 2007 … though I tended to steer people away from trying it unless they had a very specific reason for doing so and no other approach would work. There were simply too many hoops you needed to jump through in order to get mirroring to work with SharePoint 2007, primarily because SharePoint 2007 wasn’t mirroring-aware. Even if you got it working, it was … finicky.
SharePoint 2010, on the other hand, is fully mirroring-aware through the use of the Failover Partner keyword in connection strings used by SharePoint to connect to its databases.
(Side note: if you aren’t familiar with the Failover Partner keyword, here’s an excellent breakdown by Michael Aspengren on how the SQL Server Native Provider leverages it in mirroring configurations.)
There are plenty of blog posts, articles (like this one from TechNet), and books (like the SharePoint 2010 Disaster Recovery Guide that John Ferringer and I wrote) that talk about how to configure mirroring. It’s not particularly tough to do, and it can really help you in situations where you need a SQL Server-based high availability and/or remote redundancy solution for SharePoint databases.
This isn’t a blog post about setting up mirroring; rather, it’s a post to share some of what I’ve learned (or think I’ve learned) and related “ah-ha” moments when it comes to mirroring.
What Are You Pointing At?
This all started when Jay Strickland (one of the Quality Assurance (QA) folks on my team at Idera) ran into some problems with one of our SharePoint 2010 farms that was used for QA purposes. The farm contained two SQL Server instances, and the database instances were setup such that the databases on the second instance mirrored the databases on the first (principal) instance. Jay had configured SharePoint’s service applications and Web applications for mirroring, so all was good.
But not really. The farm had been running properly for quite some time, but something had gone wrong with the farm’s mirroring configuration – or so it seemed. That’s when Jay pinged me on Skype one day with a question (which I’m paraphrasing here):
Is there any way to tell (from within SharePoint) which SQL Server instance is in-use by SharePoint at any given time for a database that is being mirrored?
It seemed like a simple question that should have a simple answer, but I was at a loss to give Jay anything usable off the top of my head. I told Jay that I’d get back to him and started doing some digging.
The SPDatabase Type
Putting on my developer hat for a second, I recalled that all SharePoint databases are represented by an instance of the SPDatabase type (Microsoft.SharePoint.Administration.Database specifically) or one of the other classes that derive from it, such as SPContentDatabase. Running down the available members for the SPDatabase type, I came up with the following properties and methods that were tied to mirroring in some way:
- FailoverServer
- FailoverServiceInstance
- AddFailoverServiceInstance()
What I thought I would find (but didn’t) was one or more properties and/or methods that would allow me to determine which SQL Server instance was serving as the active connection point for SharePoint requests.
In fact, the more digging that I did, the more that it appeared that SharePoint had no real knowledge of where it was actually connecting to for data in mirrored setups. It was easy enough to specify which database instances should be used for mirroring configurations, but there didn’t appear to be any way to determine (from within SharePoint) if the principal was in-use or if failover to the mirrored instance had taken place.
The Key Takeaway
If you’re familiar with SQL Server mirroring and how it’s implemented, then the following diagram (which I put together for discussion) probably looks familiar:
This diagram illustrates a couple of key points:
More Sharepoint Failover Farm Videos
- SharePoint connects to SQL Server databases using the SQL Server Native Client
- SharePoint supplies a connection string that tells the native client which SQL Server instances (as Data Source and Failover Partner) should be used as part of a mirroring configuration.
- It’s the SQL Server Native Client that actually determines where connections are made, and the results of the Client’s decisions don’t directly surface through SharePoint.
Storage Accounts: Durable, Highly Available, And Massively Scalable Cloud Storage
- Number 3 was the point that I kept getting stuck on. I knew that it was possible to go into SQL Server Management Studio or use SQL Server’s Management Objects (SMO) directly to gain more insight around a mirroring configuration and what was happening in real-time, but I thought that SharePoint must surely surface that information in some form.
Apparently not.
Checking with the Experts
I hate when I can’t nail down a definitive answer. Despite all my reading, I wanted to bounce the conclusions I was drawing off of a few people to make sure I wasn’t missing something obvious (or hidden) with my interpretation.
- I shot Bill Baer (Senior Technical Product Manager for SharePoint and an MCM) a note with my question about information surfacing through SharePoint. If anyone could have given me a definitive answer, it would have been him. Unfortunately, I didn’t hear back from him. In his defense, he’s pretty doggone busy.
- I put a shout out on Twitter, and I did hear back from my good friend Todd Klindt. While he couldn’t claim with absolute certainty that my understanding was on the mark, he did indicate that my understanding was in-line with everything he’d read and conclusions he had drawn.
- I turned to Enrique Lima, another good friend and SQL Server MCM, with my question. Enrique confirmed that SQL SMO would provide some answers, but he didn’t have additional thoughts on how that information might surface through SharePoint.
Long and short: I didn’t receive rock-solid confirmation on my conclusions, but my understanding appeared to be on-the-mark. If anyone knows otherwise, though, I’d love to hear about it (and share the information here – with proper recognition for the source, of course!)
Back to the Farm
In the end, I wasn’t really able to give Jay much help with the QA farm that he was trying to diagnose. Since I couldn’t determine where SharePoint was pointing from within SharePoint itself, I did the next best thing: I threw together a PowerShell script that would dump the (mirroring) configuration for each database in the SharePoint farm.
[sourcecode language=”powershell”]
<#
.SYNOPSIS
SPDBMirrorInfo.ps1
.DESCRIPTION
Examines each of the databases in the SharePoint environment to identify which have failover partners and which don’t.
.NOTES
Author: Sean McDonough
Last Revision: 19-August-2011
#>
function DumpMirroringInfo ()
{
# Make sure we have the required SharePoint snap-in loaded.
$spCmdlets = Get-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction silentlycontinue
if ($spCmdlets -eq $Null)
{ Add-PSSnapin Microsoft.SharePoint.PowerShell }
<#
.SYNOPSIS
SPDBMirrorInfo.ps1
.DESCRIPTION
Examines each of the databases in the SharePoint environment to identify which have failover partners and which don’t.
.NOTES
Author: Sean McDonough
Last Revision: 19-August-2011
#>
function DumpMirroringInfo ()
{
# Make sure we have the required SharePoint snap-in loaded.
$spCmdlets = Get-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction silentlycontinue
if ($spCmdlets -eq $Null)
{ Add-PSSnapin Microsoft.SharePoint.PowerShell }
# Grab databases and determine which have failover support (and which don’t)
$allDatabases = Get-SPDatabase
$dbsWithoutFailover = $allDatabases | Where-Object {$_.FailoverServer -eq $null} | Sort-Object -Property Name
$dbsWithFailover = $allDatabases | Where-Object {$_.FailoverServer -ne $null} | Sort-Object -Property Name
$allDatabases = Get-SPDatabase
$dbsWithoutFailover = $allDatabases | Where-Object {$_.FailoverServer -eq $null} | Sort-Object -Property Name
$dbsWithFailover = $allDatabases | Where-Object {$_.FailoverServer -ne $null} | Sort-Object -Property Name
# Write out unmirrored databases
if ($dbsWithoutFailover -eq $null)
{ Write-Host '`n`nNo databases are configured without a mirroring partner.' }
else
{
Write-Host ('`n`nDatabases without a mirroring partner: {0}' -f $dbsWithoutFailover.Count)
$dbsWithoutFailover | Format-Table -Property Name, Server -AutoSize
}
if ($dbsWithoutFailover -eq $null)
{ Write-Host '`n`nNo databases are configured without a mirroring partner.' }
else
{
Write-Host ('`n`nDatabases without a mirroring partner: {0}' -f $dbsWithoutFailover.Count)
$dbsWithoutFailover | Format-Table -Property Name, Server -AutoSize
}
# Dump results for mirrored databases
if ($dbsWithFailover -eq $null)
{ Write-Host '`nNo databases are configured with a mirroring partner.' }
else
{
Write-Host ('`nDatabases with a mirroring partner: {0}' -f $dbsWithFailover.Count)
$dbsWithFailover | Format-Table -Property Name, Server, FailoverServer -AutoSize
}
if ($dbsWithFailover -eq $null)
{ Write-Host '`nNo databases are configured with a mirroring partner.' }
else
{
Write-Host ('`nDatabases with a mirroring partner: {0}' -f $dbsWithFailover.Count)
$dbsWithFailover | Format-Table -Property Name, Server, FailoverServer -AutoSize
}
# For ease of reading
Write-Host ('`n`n')
}
DumpMirroringInfo
[/sourcecode]
Write-Host ('`n`n')
}
DumpMirroringInfo
[/sourcecode]
The script itself isn’t rocket science, but it did actually prove helpful in identifying some databases that had apparently “lost” their failover partners.
Additional Reading and Resources
- MSDN: Using Database Mirroring
- Whitepaper: Using database mirroring (Office SharePoint Server)
- Blog Post: Clarification on the Failover Partner in the connectionstring in Database Mirror setup
- TechNet: Configure availability by using SQL Server database mirroring (SharePoint Server 2010)
- Book: The SharePoint 2010 Disaster Recovery Guide
- Blog: John Ferringer’s “My Central Admin”
- Blog: Jay Strickland’s “Slinger’s Thoughts
- Company: Idera
- MSDN: SPDatabase members
- MSDN: SQL Server Management Objects (SMO)
- Blog: Bill Baer
- Blog: Todd Klindt’s SharePoint Admin Blog
- Blog: Enrique Lima’s Intentional Thinking
If you haven’t read and still looking for the information on DR farm configuration with MOSS 2007 then you can refer the TechNet article. In this post i have explained how to configure the DR farm with SharePoint 2010 and what are the improvements we have in SharePoint 2010 as compared to MOSS 2007.
You can configure SharePoint 2010 DR farm using SQL Log shipping or SQL database mirroring for SharePoint databases. Before i start SharePoint 2010 DR farm configuration, I want to give an overview of SQL database mirroring, SQL log shipping and what can be used. In this post i am going explain followings.
- Overview to SQL Database mirroring
- Overview to SQL Log shipping
- What can be used for SharePoint 2010 DR Farm configuration
- SharePoint 2010 DR Farm configuration using SQL Database mirroring
- SharePoint 2010 DR Farm configuration Using SQL Log Shipping
- Comparison
- Changes required when you failover
Overview to SQL Database Mirroring:
Database mirroring feature is available in SQL 205 and SQL 2008. Its a solution to provide database high availability and its configured per-database basis. You can only configure the database mirroring for the databases that use the full recovery model. You cannot configure the database mirroring for the System Databases (master, msdb, tempdb & model).
In database mirroring there will be 2 copies of a single database residing on 2 different SQL instance. One SQL server instance act as Primary server and holds the active databases. All the clients or applications connect to this database server. Other SQL Server instance act as Secondary server or mirror server which holds the hot or warm databases. Once the database mirroring has been configured and databases are synchronized state, failover can be performed without a loss of data. While you configure the SQL database mirroring you can also configure the Witness server. This is going to another SQL instance which would be monitoring the primary SQL server instance. If primary SQL Service fails then witness server will automatically failover the databases from primary SQL server instance to Secondary SQL server instance.
There are 2 operating modes for configuring Database mirroring:
- Asynchronous: This doesn’t need the Witness Instance, that means there is no automatic failover. Once the Database server is not available administrator to stop the database mirroring session manually.
- Synchronous: You can configure this with witness server (optional). If you configure with witness server then you get the advantage of automatic failover feature. However, automatic failover requires both the databases to synchronized.
Lets look at the advantages and disadvantages of database mirroring.
- Database Mirroring Advantages:
- Easy setup and maintenance
- Real-time synchronization of transaction log
- Automated failover (in High Availability Mode, Witness Server Instance required)
- Fast & easy Failover
- Using connection strings you can make your application mirroring aware or using SQL alias.
- Database mirroring is configured per database not per SQL server Instance.
- Mirror database always in recovery mode and this database can’t be used for any purpose or database queries.
- Database mirroring can only configured for one database to another database. There can’t be more secondary server.
- Database mirroring can’t be configured for once database once than once.
Note:- Database mirroring can be configured for the databases having Full Recovery Model. Database mirroring can not be configured for the databases which is using Simple Recovery Model. |
Overview to SQL log Shipping:
SQL log shipping allows to backup the transaction logs from primary SQL Server instance in regular interval to one or multiple secondary SQL Server instance. Transaction log backup are restored to all the Secondary SQL Server instances individually. SQL Log Shipping consist of 3 operations:
- Backup transaction log from the primary server
- Copy the transaction log backup to the secondary server or secondary servers
- Restore the transaction log backup to the secondary server instance
Above 3 operation will create 3 jobs on SQL Servers. Backup Job will be only available on Primary SQL Server instance. However, Secondary SQL Server instance contains the Copy job and Restore job. All these job executes in regular interval and you can define the time. SQL agent service has to be on running for log shipping.
- Log Shipping Advantages:
- Flexibility to backup, copy and restore the transaction logs.
- Due to scheduled jobs database corruption can be avoided.
- Multiple secondary databases can be configured
- Secondary databases are in read-only and standby mode and can queried if required.
- Log shipping allows multiple standby databases
- Log Shipping disadvantages:
- No option for automatic failover
- Minimum time to synchronize is a min. which can be configured.
- For failover need to execute SQL Query. not simple as database mirroring.
Note:- Transaction Log Shipping can be configured for the databases having Full Recovery Model. It can not be configured for the databases which is using Simple Recovery Model. |
What can be used for SharePoint 2010 DR Farm Configuration:
For SharePoint 2010 Disaster Recovery Farm configuration you can use both. However, if you use Database mirroring then databases on Secondary SQL Server or Databases on Secondary Datacenter will be on restoration mode which you cant attach to the web application. I would always recommend to use SQL log shipping for SharePoint 2010 DR Farm configuration. Again, SQL database mirroring is mostly used for HA and SQL log shipping is used for DR. On this post i will be explaining DR farm configuration with both and compare at the end so you can choose what’s best for you.
SharePoint 2010 DR Farm Configuration using database mirroring:
To configure the DR Farm using Database mirroring, you need to start a fresh installation of SharePoint 2010 on Secondary Datacenter. You should have below:
- Install and Configure SharePoint 2010 Farm in Secondary datacenter in same AD Forest using Primary SharePoint farm Service accounts
- Install the customizations which are present on primary farm
- Create all the web applications which are present on primary farm
- Create all the Service applications
- Detach the content databases from the web applications and delete the content databases
- Configure the mirroring only for content databases from Primary SQL server to Secondary SQL server. By Default all the content databases uses Full Recovery Model. If you have changed the Recovery Model for to simple for any content databases, it will not allow you to configure database mirroring unless you change the recovery model back to full recovery model.
- Create database mirroring without the Witness server for all the SharePoint Content Databases only. No witness server required as database mirroring would be used for High Availability and auto-failover is not required
To configure Database mirroring using SQL management Studio you can follow the MSDN Article.
Note:-
|
SharePoint 2010 DR Farm Configuration using database log shipping:
To configure the DR Farm using database log shipping, you need to start a fresh installation of SharePoint 2010 on Secondary Datacenter. You should have below:
Azure Resource Group: Container That Holds Related Resources For An Azure Solution
- Install and Configure SharePoint 2010 Farm in Secondary datacenter in same AD Forest using Primary SharePoint farm Service accounts
- Install the customizations which are present on primary farm
- Create all the web applications which are present on primary farm
- Create all the Service applications
- Detach the content databases from the web applications and delete the content databases
- Create the log shipping for the content databases only. By Default all the content databases uses Full Recovery Model.If you have changed the Recovery Model for to simple for any content databases, it will not allow you to configure database mirroring unless you change the recovery model back to full recovery model.
To configure Database log shipping using SQL management Studio you can follow the MSDN Article.
Note:-
RESTORE DATABASE contentdbname WITH RECOVERY |
Comparison of using SQL database mirroring or Log Shipping for SharePoint DR farm.
Database Mirroring | Database Log Shipping |
|
|
Changes required when you failover from Primary SharePoint 2010 to SharePoint 2010 DR Farm.
If you are using SQL database mirroring:
Choose A Disaster Recovery Strategy For SharePoint Server ...
- On the DNS Server make the changes so that all the request can be reached to DR Farm SharePoint 2010 WFEs.
- Run the SQL Query to make the database active. (ALTER DATABASE ContentDatabaseName SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS)
- Attach content databases to the web applications.
- Start a full Crawl process on Search Server.
If you are using SQL database log shipping:
Azure Active Directory: Synchronize On-premises Directories And Enable Single Sign-on
Storage Documentation
After knowing both the options, now you can choose between database mirroring and Database log shipping for DR farm configuration.