Thursday, April 24, 2014

SharePoint 2013 and SQL Server HA options

I put together the following table to summarize the HA options available for SQL Server 2012 and SharePoint 2013. In short - use sync AlwaysOn where possible!

The difference between sync & async has to do with performance over data integrity.  Sync will wait on confirmations from each end point that the transactions were successful.  Async does not wait.  Async will give better performance, but it's possible to have data loss. 

I'm building this out in a lab environment based on VirtualBox.  My attempt #1 was to get FreeNAS working with Server 2012 to set up the shared disk required by Windows Clustering.  I was able to get the iSCSIInitator in Server 2012 to recognize the NAS drives, but couldn't convince Windows Clustering to use it. So next up will be to see if I can get the same working with Server 2012's iSCSI built in support.

I see this as one benefit of Hyper-V over VirtualBox - Hyper-V has a virtual fiber channel feature built in.  In theory, this would let me set up a virtual SAN environment.  I've been trying the same with VirtualBox (yes, I know...but I did try switching over to Hyper-V on my Windows 8 laptop, but at the time networking wasn't working.  I should give it another try, I know...).

Anway, the table below is a summary of a TechNet article listing each of the SharePoint databases. I found the table format a bit easier to use.

Database Purpose Sync Mirroring Sync AlwaysOn Asyc Mirror/Log Shipping Async AlwaysOn
SharePoint_Config Configuration database Y Y N N
SharePoint_Admin_Content CA content db Y Y N N
WSS_Content Content database Y Y Y Y
AppManagement Service database Y Y Y Y
Search_Service_Application_DB Stores Search app configuration Y Y N N
Search_Service_Application_AnalyticsReportingStoreDB Stores usage analysis data for reports Y Y Y N
Search_Service_Application_CrawlStoreDB Stores the state of the crawled content & crawl history Y Y N N
Search_Service_Application_LinkStoreDB Stores extracts of the content processing component sna click through info Y Y N N
Secure_Store_Service_DB Stores Secure Store credentials Y Y Y Y
SharePoint_Logging Stores health monitoring & usage data Y* Y* Y* N * not recommended
SettingsServiceDB Stores features and settings for hosted customers Y Y Y Y
Usage Profile Service App Stores & manages users and associated info Y Y Y Y
Usage Profile Service App Sync DB Used during a user profile sync Y Y N N
Usage Profile Service Social DB Stores social tags and notes Y Y Y Y
WordAutomationServices Stores info about pending & completed doc conversions Y Y Y Y
Managed Metadata Service App Stores managed metadata  Y Y Y Y
Translation Services DB Stores info about pending & completed doc translations Y Y Y Y
Business Data Connectivity Service DB Stores external content types Y Y Y Y
ProjectWebApp Project Web App database Y Y Y Y
PowerPivotServiceDB Stores data refresh scheduled & usage data No Guidance No Guidance No Guidance No Guidance
PerformancePoint Service DB Stores temp objects & saved comments Y Y Y Y
State Service db Stores temp state info from InfoPath, Exchange, chart web part, Visio services Y Y N N

Source: Supported HA Options for SharePoint 2013