Friday, December 9, 2011

The 2010 SharePoint databases, purposes and mirroring supportability

I have scrutinized the article Database types and descriptions (SharePoint Server 2010) and here is the result - a table which gives an overall understanding what datatables SharePoint 2010 uses and what for, plus their ability to be mirrored.
* Url to set Failover field sometimes is missing since I haven't tested by myself
*** "No??" Indicates the msdn documentation gives "No", but in CA UI I see an option to set Failover
***The easy way to set failover:
1. Make sure that you have configured a mirror
2. Run the script
2.1. suitable  in case that all your mirrors on the one box:

Param([string]$FailoverInstance = $(Read-Host "Enter the Mirror (Partner) SQL Instance(like server\instance)")) 
get-spserviceinstance -all | foreach-object { 
    if ($_.typeName -eq "Microsoft SharePoint Foundation Database") { 
        foreach ($Database in $_.Databases){ 
             
            write-host "Updating FailoverPartner on" $Database.Name "to" $FailoverInstance 
            $Database.AddFailoverServiceInstance($FailoverInstance) 
            $Database.Update() 
            write-host "Successfully Updated Failover Partner on" $Database.Name "to" $FailoverInstance 
        } 
         
    } 
}

2.2.
For individual database, run this simple script.
 $db = get-spdatabase | where {$_.Name -eq "db_name"}   
 $db.AddFailoverServiceInstance("db02");
 $db.Update() 

****In case of the issue with Failover, try to find an answer from SharePoint 2010 Failover doesn't work




Default Database name
Purpose
Mirror supportability
Default recovery  mode
Url to set Failover
1
SharePoint_Config
contains data about SharePoint databases, Internet Information Services (IIS) Web sites, Web applications, trusted solutions, Web Part packages, site templates, and Web application and farm settings specific to SharePoint 2010 Products, such as default quota settings and blocked file types.
Yes
Full
PowerShell
2
SharePoint_AdminContent
 It stores all site content, including site documents or files in document libraries, list data, and Web Part properties, in addition to user names and rights for the Central Administration site collection
Yes
Full
PowerShell
3
WSS_Content
Content databases store all content for a site collection, including site documents or files in document libraries, list data, Web Part properties, audit logs, and sandboxed solutions, in addition to user names and rights.
Yes
Full
_admin/CNTDBADM.aspx
4
WSS_UsageApplication
 It stores health monitoring and usage data temporarily, and can be used for reporting and diagnostics.
Yes, but not recommended. Can be easily recreated in the case of failure
Simple
PowerShell
5
Bdc_Service_DB_
stores external content types and related objects
Yes
Full
_admin/ServiceApplications.aspx
6
Application_Registry_server_DB_
stores backward-compatible information that is used to connect to information that is used by the Microsoft Office SharePoint Server 2007 Business Data Catalog API.
Yes
Full
PowerShell
7
SubscriptionSettings_
stores features and settings for hosted customers
Yes
Full

8
Secure_Store_Service_DB_
stores and maps credentials, such as account names and passwords
Yes
Full
_admin/ServiceApplications.aspx
9
StateService
stores temporary state information for InfoPath Forms Services, the chart Web Part, and Visio Services.
Yes
Full
PowerShell
10
WebAnalyticsServiceApplication_StagingDB_
 temporarily stores un-aggregated fact data, asset metadata, and queued batch data for the Web Analytics service application.
No
Full

11
WebAnalyticsServiceApplication_ReportingDB_
 stores aggregated standard report tables, fact data aggregated by groups of sites, date and asset metadata, and diagnostics information for the Web Analytics service application.
Yes
Full
PowerShell
12
Search_Service_Application_DB_
hosts the Search service application configuration and access control list (ACL), and best bets for the crawl component. This database is accessed for every user and administrative action
Yes
Simple
searchfarmdashboard.aspx
13
Search_Service_Application_CrawlStoreDB_
stores the state of the crawled data and the crawl history
Yes
Simple
searchfarmdashboard.aspx
14
Search_Service_Application_PropertyStoreDB_
stores information that is associated with the crawled data, including properties, history, and crawl queues
Yes
Simple
searchfarmdashboard.aspx
15
User Profile Service Application_ProfileDB_
 stores and manages users and associated information. It also stores information about a user's social network in addition to memberships in distribution lists and sites.
Yes
Simple
_admin/ServiceApplications.aspx
16
User Profile Service Application_SyncDB_
stores configuration and staging data for use when profile data is being synchronized with directory services such as Active Directory
No??
Simple
_admin/ServiceApplications.aspx
17
User Profile Service Application_SocialDB_
stores social tags and notes created by users, along with their respective URLs
Yes
Simple
_admin/ServiceApplications.aspx
18
Managed Metadata Service_
stores managed metadata and syndicated content types
Yes
Full
_admin/ServiceApplications.aspx
19
WordAutomationServices_
stores information about pending and completed document conversions
Yes
Full

20
PerformancePoint Service Application_
stores temporary objects, persisted filter values, and user comments
Yes
Full
_admin/ServiceApplications.aspx
21
ProjectServer_Draft
 contains data for editing projects. This database also hosts the tables used by the Project Queue. Data in the Draft database is not directly accessible by end users.
Yes, but you must follow additional steps.
Full

22
ProjectServer_Published
contains a copy of all of the projects that have been published. The Published database also contains tables that are specific to Project Server (timesheets, resources, custom fields, security definitions, and other metadata). This database also hosts the tables used by the Timesheet Queue. Data in the Published database is not directly accessible by end user
Yes, but you must follow additional steps.
Full

23
ProjectServer_Archive_
 stores the backup data of projects, resources, calendars, enterprise custom fields, the enterprise global Project Web Access view definitions, Project Web Access system settings, and category and group security settings as set up by the Project Web Access administrator. Data in the Archive database is not directly accessible by end users.
Yes, but you must follow additional steps.
Full

24
ProjectServer_Reporting
 is the repository for the entire portfolio of projects in Project Server. These tables present stable snapshots of each project plan based on the last time a project was published to Project Server and include de-normalized time phased data, allowing for advanced reporting capabilities outside of the Project client. Data in the Reporting database is accessible by end users.
Yes, but you must follow additional steps.
Full

25
FASTSearchAdminDatabase
stores and manages data related to administration of FAST Search Server 2010 for SharePoint. This includes search setting groups, keywords, synonyms, document and site promotions and demotions, term entity extractor inclusions and exclusions, spell check exclusions, best bets, visual best bets, and search schema metadata.
No
Full

26
DefaultPowerPivotServiceApplicationDB
stores the location of cached or loaded PowerPivot data files, data refresh schedules, and PowerPivot usage data that is copied from the central usage data collection database.
Yes, but mirroring is managed solely through SQL Server: SharePoint Server 2010 is not aware of the PowerPivot database.
Full

27
RSDB
stores all report metadata including report definitions, report history and snapshots, and scheduling information.
No
Full

28
RSTempDB
stores all the temporary snapshots while reports are running.
No
Full