Share Point Databases What you need to know

Share. Point Databases What you need to know Alan Eardley - @al_eardley SQL Saturday Exeter 25 April 2015 blog. eardley. org. uk

Who Am I? • • Lead Share. Point Technical Architect at CPS Developer, DBA, Business Analyst, Project Manager, Architect SQL Server 7. 0 - 2014 Share. Point 2003 – 2013 Office 365, Azure, Project Server/Online, Dynamics CRM Twitter: @Al_Eardley E-mail: Alan. Eardley@Outlook. com blog. eardley. org. uk

What will I cover? • What does it do? • How does it work? • How does it use SQL? This relates to on-premises Share. Point NOT Office 365 What is Share. Point? Planning • How many databases does it need? • How does it grow? • HA & DR Roll-out • Are there any pre-requisites? • How do I help with the build? blog. eardley. org. uk

Share. Point – What does it do? Document Management Publishing blog. eardley. org. uk Content Management Social Process Management Search Collaboration Business Intelligence

Share. Point – How does it work? Web Applications Client Extranet Site Collections Departments Sites HR Documents IT Knowledge Base Sales Help Desk Issues Upgrade to SP 2013 Risks Intranet My Sites blog. eardley. org. uk Projects Lists & Libraries Upgrade Issues Tasks

Share. Point – How does it work? Business Connectivity Services Managed Metadata Services Excel Services Performance. Point Services Power. Pivot for Share. Point Project Server Search User Profile Service blog. eardley. org. uk

Share. Point – How does it use SQL? Service • Most Service Applications utilise at least Databases should not exceed 200 GB in one database Applications size Content • At least one database per Web Application • Each site collection can have a database Databases With the exception of Records Centre My Sites blog. eardley. org. uk databases • Multiple Databases

Planning – How many databases does it need? • Types of Database – Administration – Content – Service Applications • How many databases will be required? • How large do they need to be? • How fast will they grow? blog. eardley. org. uk

Admin & Content Databases Default Database Name Location Size Initial Size Growth Must be co-located with the Central Administration database Share. Point_Config Small 2 GB Must be located on the same database engine Share. Point_Admin. Content_ instance with the configuration database Small <GUID> 1 GB WSS_Content None <200 GB blog. eardley. org. uk Read/write Scaling Log files grow rapidly 90/10 Will grow if Power. Pivot for Share. Point is installed with default settings 90/10 Varies Up Up Up or Out

Service Applications Default Database Name Bdc_Service_DB_<GUID> Location Size None Small Managed Metadata Service Application_Metadata_<GUID> None Performance. Point Service _<GUID> None Default. Power. Pivot. Service. Applicatio n. DB_<GUID> None Project. Web. App blog. eardley. org. uk None Initial Size Medium Small to Medium 1 GB Read/writ e Scaling 90/10 Up Growth Very slow Growth factors include the amount of managed metadata 80/20 Very slow 80/20 Up per service application Up only Up per web application

Search Default Database Name Location Size The Administration database should fit into RAM on the server so that the server can handle the end-user query load most efficiently. Because of this requirement, it is usually best not to have the Search_Service_Application_DB_< Administration and Crawl databases located on the GUID> same server. Medium Initial Size Growth 10 GB The factors that influence growth include the number of best bets, the number of content sources and crawl rules, the security descriptions for the corpus, and how much traffic. 50/50 Read/write Scaling Search_Service_Application_Analy tics. Reporting. Store. DB_<GUID> None Medium to Large Write-intensive during analytics update 90/10 Search_Service_Application_Crawl Store. DB_<GUID> None Medium Depends on the growth of the document corpus We recommend that if you have sites that have heavy Search_Service_Application_Link. S traffic, the Link database should use separate spindles. Medium to tore. DB_<GUID> Large from other databases blog. eardley. org. uk 90/10 The Link database grows on disk by 1 GB per 1 million documents fed. The click through data grows linearly with query traffic, 1 GB per million queries. 30/70 Mostly Up Scale out by creating additional Analytics Reporting database using a split operation when the main database becomes >200 GB. Scale out by creating additional Crawl database per every 20 million items crawled. Scale out by creating additional Link database per every 60 million documents crawled. Also additional Link database per 100 million expected queries per year.

Single Farm Presentation Application Data blog. eardley. org. uk • Web Front End • Application Server • Workflow Manager • Office Web Apps • SQL Server

High Availability • Mirroring – Share. Point supports mirroring • Clustering – Preferable to mirroring as it is at server level • Always On Availability Groups – Builds on the benefits of Clustering blog. eardley. org. uk

Backup • As a DBA, same as any other application – Full backups – Differential backups – Transaction backups • Check consistency • Fragmentation – Check for index fragmentation – Check for drive fragmentation blog. eardley. org. uk

Recovery Overview Site Collection • • Everyone 30 days Items and documents Automatically moved to site collection recycle bin blog. eardley. org. uk • Site collection administrator • First in – first out • Includes sites

Recovery Content Databases • When a site is required to be restored • When a site collection has been deleted Service Application • When a Service application needs to rebuilt Central Admin • When a farm needs to be recovered blog. eardley. org. uk

Shared Services • Share. Point Service Applications – – – Search User Profile Managed Metadata Secure Store Business Connectivity blog. eardley. org. uk

Multiple Farms, Multiple Data Centres blog. eardley. org. uk

Data Level blog. eardley. org. uk

How Can I Help? Configuration Installation Maintenance blog. eardley. org. uk • Set up SQL Instance correctly • Understand the database usage profiles • Standards and processes • Power. Shell • Monitor • Plan for growth • Backup

Configuration • • Used a dedicated instance Default Collation - Latin 1_General_CI_AS_KS_WS MAXDOP = 1 Disable auto-create statistics Define the maximum memory Make a note of the Port Number the Instance is listening on Single filegroup per DB blog. eardley. org. uk

Support • The general rules to not invalidate Microsoft Support are: – Do not add anything to the Share. Point databases • Stored Procedures • Triggers • Tables • Indexes – Do not run frequent queries against the Share. Point Databases blog. eardley. org. uk

Summary • The number of databases depends on the service applications • The size of the databases will depend on – The number of users – The number of documents and the amount of content – The amount of usage of Share. Point • A database instance for Share. Point has specific requirements • HA and DR strategies are a combination of Share. Point and SQL approaches • Share. Point databases are the same as any other databases blog. eardley. org. uk

Share. Point Database References blog. eardley. org. uk/2015/03/sharepoint-databases-knowledge-base/ • A combination of Microsoft documentation – SQL based – Share. Point based • • • Initial sizes Growth Usage HA/DR Links to Po. Sh to create the databaes blog. eardley. org. uk

Today is brought to you by and in association with blog. eardley. org. uk

Please visit our sponsors blog. eardley. org. uk

Thank-you Alan Eardley – Alan. Eardley@outlook. com blog. eardley. org. uk @al_eardley Office 365 | Share. Point | Project Server | CRM | Recruitment blog. eardley. org. uk