SQL Share Point Friends Forever William Assaf Kevin

SQL + Share. Point: Friends Forever William Assaf Kevin Grohoske Sparkhound 1

Why? Microsoft SQL Server instances which host Share. Point databases are very similar to other instances, and need the same attention. We will discuss common Share. Point database configuration and maintenance needs. This introductory-level presentation is geared towards the basics of administration in existing environments. 2

Bios and Contact • • • William D Assaf, MCSE Principal Consultant, DBA Manager Sparkhound Inc. , William. Assaf@sparkhound. com SQL PASS Regional Mentor South Central Twitter: @william_a_dba Kevin Grohoske Enterprise Applications Principal Consultant Sparkhound Inc. , Kevin. Grohoske@sparkhound. com Twitter: SQLSaturday Baton Rouge 2017: July 29! 3

SQL Best Practices for Share. Point + Share. Point Best Practices in SQL 4

SQL Best Practices for Share. Point 5

The Number One Emergency Call “Help, my SQL Server has filled up the drive!” Why does this happen? Only one real reason ever exists – a SQL Server Transaction Log file has grown unchecked. 6

The Transaction Log Transactions Windows sees 1 GB file +10% SQL sees 1 GB reservation +10% with ~1 GB free Windows sees 1 GB file Transactions . LDF file SQL has used up the space, the file must grow Windowssees 1. 1 GB 1. 21 GB file SQLsees 1 GB 1 GBreservation with~. 1 GB ~. 11 GB free 7

Database Recovery Model and the Transaction Log Simple • Transactions quickly, automatically cleared from the T-Log • No T-Log backups • No Point in Time Recovery (Full/Diff backups only) Full • Transactions stay in TLog until truncated • T-Log backups taken regularly –or– T-Log will grow unchecked! • Point in Time recovery is possible and desirable 8

The Transaction Log Transactions Windows sees 1 GB file SQL sees 1 GB reservation with ~1 GB free Windows sees 1 GB file A transaction log backup occurs Transactions . LDF file Transactions are copied to a backup, T-Log is emptied Windows sees 1 GB file SQL Server activity begins to fill up the transaction log again 9

Share. Point Database Recovery Models These database should be in SIMPLE recovery mode. • Nightly full backups are still necessary. Config databases, Search and Crawl databases, Usage and Health Data Collection database, Share. Point Profile-related databases, and SQL Server system databases 10

Share. Point Database Recovery Models All other databases, including Content DB's, and the Secure Store, should be in FULL recovery mode. • Nightly full backups plus • at least hourly tran log backups are necessary. T-log backups can be taken as fast as storage can handle (every minute? ) and should match or beat business data loss tolerance goals. http: //technet. microsoft. com/en-us/library/cc 678868. aspx 11

SQL Server Alignment • Host no other databases or applications on the Windows Server that hosts the SQL Server instance, including other Share. Point farm features • Configure each of these on separate volumes (these can be moved, transparent to Share. Point): • Operating System (C: ) • SQL Server Data Files and system databases • SQL Server Log Files • SQL Server Temp. DB (fastest available) • SQL Server and Share. Point backups 12

Database File Locations • Share. Point creates SQL Server databases dynamically, and will use the SQL Server default database file locations in Server Properties. • Moving existing databases is fairly straightforward: • • • Drop the content db from web application (stopping all connections, or manually kill all connections) Detach, move, re-attach database Re-attach the content db to the web application 13

Share. Point SQL Server Properties • Max Degree of Parallelism should be set to 1 • Max Server Memory should be set appropriately 14

Share. Point SQL Server Properties • Appropriate SQL Server instance memory • Total Server Memory minus 2 gb or 10%, whichever is larger. • For a server with 10 gb of RAM: 15

Share. Point Database Settings • Auto Create Statistics on each database should be FALSE • These settings should also never be enabled on ANY SQL Server database: • • Auto_Close Auto_Shrink NEVER! 16

Disaster Recovery Point Objective (RPO) • Point in Time to restore • Defined by Data Loss Tolerance Recovery Time Objective (RTO) • Amount of time before System or Application should be restored, different for each • Allows IT to prioritize recovery and budget 17

SQL Server Disaster Recovery • SQL must back up SQL • The transaction log is a big reason and benefit of this • Offsite the Backups ASAP • • You must back up the System Databases too: • • No DR solution without a copy to another location Master, Model and MSDB databases must be backed up! SAN Replication, VM Server snapshots, VM backups are useful and complimentary but not a replacement for recovery from corruption disasters, for point in time recovery, or for SQL instance recovery. 18

SQL Server Temp. DB • The highest-activity database files on your SQL Server instance will be in the Temp. DB data file. • • The Temp. DB data file(s) should be on the fastest possible storage (SSD) Myth: create Temp. DB data files equal to the number of logical CPU cores 19

SQL Server Temp. DB • Fact: create multiple Temp. DB data files, but probably no more than half of the number of logical CPU cores. • • • There is no simple rule. All the Temp. DB data files must be the same size and have the same autogrowth settings. New in SQL 2016: • SQL Server setup will automatically suggest a configuration for Temp. DB data files based on the number of processors, up to 8. This is certainly better than 1 tempdb data file, but if you have a less than stellar storage system, it could harm performance. 20

Also New in SQL 2016 • In SQL 2016 SP 1, a large number of Enterprise-edition features were moved down to the Standard edition, including: Transparent Data Encryption SQL audit Columnstore indexes Database snapshots Always Encrypted Data Compression • • • (Backup compression was already available and highly recommended) SQL Server 2016 is supported by Share. Point 2013+. SQL Server 2010 supports up to SQL Server 2014. 21

Compression 1. Backup Compression 1. 2. Data Compression 1. 2. 3. As of SQL 2008 R 2 - this is now a Standard edition feature Applied directly to table/index, transparent to the application Can benefit performance –stays compressed in memory! Also Enterprise only, or SQL Server 2016 SP 1 Standard Edition+ Transparent Data Encryption (TDE) 1. 2. TDE conflicts with Backup Compression (in a bad way) prior to SQL 2014 Also Enterprise only, or SQL Server 2016 SP 1 Standard Edition+ 22

Basic SQL Maintenance Plan • Database and T-Log Backups Can’t do Site Collection Backups at the SQL Server level, can do them in Central Administration or Power. Shell. • • Check database integrity Do not Shrink on a schedule! Ever! • In Share. Point 2013+, probably don’t need: • Rebuild Indexes (ONLINE) -or. Reorganize Indexes + Update Statistics • • Health Analyzer will monitor index statistics and automatically “repair” by updating statistics. 23

Reporting Services SSRS is a SQL Server feature included with the SQL license for serving Enterprise-scale web-based reports. Two install options: 1. Standalone – operates as its own webserver, at scale should have its own Windows Server. 2. Share. Point integrated (Share. Point 2013+ only) – operates inside the farm as a Web Application 1. Requires also installing the Reporting Services Add-in Both support scale-out for round-robin report serving. 24

Reporting Services Should I use SSRS native or SSRS in Share. Point integrated mode? Consider security. Standalone SSRS is secured separately from everything else via Windows Auth Groups in SSRS Report Manager. Share. Point Integrated SSRS is secured with Share. Point credentials. • Anonymous access is not possible. Do you already have investment in Share. Point security infrastructure? 25

Always. On Availability Groups Starting with SQL 2012 Enterprise, Availability Groups are an excellent marriage of Database Mirroring and Clustering to provide: 1. Multiple database nodes, each with their own async/sync options 2. Wide geography WAN synchronization is easy 3. Manual or automatic failover 4. Read-only secondary mirrors 5. Third SQL Server can serve as a listener to provide redirection and failover if desired 26

Always. On Availability Groups With Share. Point, Availability Groups should really only be set up to synchronize Content Databases between 2+ distinct Share. Point farms. All SQL system and Share. Point config databases reside locally and should not synchronized. These are farmspecific databases. During a planned failover, you must first dismount the content databases, failover the Availability group, then mount the content databases (easily accomplished with Power. Shell) 27

Share. Point Best Practices In SQL 28

Share. Point Databases List of all Share. Point Databases: Configuration (Share. Point_Config) Central Administration Content (Share. Point_Admin. Content) Content (WSS_Content) App Management (App. Mng_Service_DB) Business Data Connectivity (Bdc_Service_DB) Search Administration (Search_Service_Application_DB) Analytics Reporting (Search_Service_Application_Analytics. Reporting. Store. DB) Crawl (Search_Service_Application_Crawl. Store. DB) 29

Share. Point Databases List of all Share. Point Databases Link (Search_Service_Application_Link. Store. DB) Secure Store (Secure_Store_Service_DB) Usage (WSS_Logging) Subscription Settings (Settings. Service. DB) User Profile (User Profile Service Application_Profile. DB) User Synchronization (User Profile Service Application_Sync. DB) User Profile Social Tagging (User Profile Service Application_Social. DB) 30

Share. Point Databases List of all Share. Point Databases Word Automation (Word. Automation. Services) Managed Metadata Service (Managed Metadata Service) Share. Point Translation Services (Translation. Service) Power. Pivot Service (Default. Power. Pivot. Service. Application. DB) Performance. Point (Performance. Point Service Application) State Service (State. Service) Report Server Catalog (Reporting. Service) Report. Server. Temp. DB (Reporting. Service_<GUID>_Temp. DB) Report Server Alerting (Reporting. Service_<GUID>_Alerting) 31

Share. Point Databases – all have their own unique features SQL guidance: Location requirements General size information and growth factors Read/write characteristics Default recovery model Supported backup tools Recommended scaling method Technet Database types and descriptions: https: //technet. microsoft. com/en-us/library/cc 678868(v=office. 16). aspx 32

Virtualization Considerations To Virtualize or Not: Web and Application Servers: Performance Considerations: Host OS Resources Physical NICS Virtual NICS SQL Server: Bottom Line “…a virtual database server will not outperform a physical server that uses the same configuration. However, if you scale up the virtual database server configuration, you can achieve the same overall throughput at a slightly increased CPU usage cost” 33

Virtualization Considerations 34

Share. Point Blob Storage #1 pain of mature Share. Point implementations is storage (as you all likely know already) Separates structured & unstructured data Maximizes cost effective storage (only purpose) 2 Types: Local RBS Filestream (free) Remote RBS Ave. Point Storage Manager ($) Mexalogix Storage. Point ($) 35

Share. Point Blob Storage Noteworthy Share. Point Server 2016 points: BLOB Store installation package for SQL Server 2014 is the only version of local RBS supported. Site collection backup and restore will download the file contents and upload them back to the server (deep copy). The FILESTREAM provider is the only provider that is currently supported for Share. Point Server 2016 farm database backup and restore operations. Backup and restore in Central Administration — cannot be used with “remote RBS providers”. 36

Share. Point Diagnostic Logging Another cost effective way to save OS disk space Configure/Tune Diagnostics Settings Restrict log disk space usage Use the Verbose setting sparingly Enable event log flooding protection Move the logs off the machine 37

Share. Point Monitoring Proactive Monitoring System Center Management Pack for Share. Point Server Metalogix - Diagnostic Manager Reactive Monitoring Health Analyzer Reports Availability Monitoring Angry Users w/ pitchforks and lanterns 38

Share. Point as a Development Platform Legacy: OOB Solutions (80/20 rule applies) Performance. Point Excel Services Access. App (DOA) Info. Path (Who Am I? Why am I here? ) SP Apps (Sandbox) Full-trust Custom Apps (CSOM) 39

Share. Point as a Development Platform Current: OOB Solutions (80/20 rule applies) Workflows List Forms Power. Pivot Info. Path (I’m not dead yet, but…!) SP Add-In’s (SP & Provider Hosted) Power. Apps SP Apps (JSOM/REST) 40

Share. Point as a Development Platform Future: OOB Solutions (80/20 rule applies) Workflows List Forms Info. Path (Please kill me!) SP Add-In’s (CSOM/JSOM/REST) SP Apps (JSOM/REST) Flow Power. Apps Share. Point Development Framework (SPFx) 41

QUESTIONS ?

THANK YOU !

Useful Links • • • http: //technet. microsoft. com/en-us/library/cc 678868. aspx#Sec 1 http: //technet. microsoft. com/en-us/library/hh 292622. aspx http: //blogs. technet. com/b/sqlpfeil/archive/2012/02/01/four-tips-for-sqltuning-for-sharepoint-part-1. aspx http: //blogs. technet. com/b/sqlpfeil/archive/2012/07/14/four-tips-for-sqltuning-for-sharepoint-part-3 -tempdb. aspx http: //technet. microsoft. com/en-us/library/ff 607733. aspx http: //blogs. msdn. com/b/how 24/archive/2012/11/20/special-sql-serverconfiguration-and-considerations-for-sharepoint-2010. aspx http: //www. sparkhound. com/pages/blogpost. aspx? f=73&bt=&aid=21 http: //technet. microsoft. com/en-us/library/ee 748649(v=office. 15). aspx http: //technet. microsoft. com/en-us/library/bb 326290(v=sql. 105). aspx http: //blogs. msdn. com/b/sambetts/archive/2014/05/16/sharepoint-2013 -on -sql-server-alwayson-2014 -edition. aspx 44

Your User Community • Steel City Baton Rouge • http: //steelcitysql. pass. org/ • Baton Rouge SQL Server User Group • http: //www. brssug. org • SQLSaturday Pensacola 2017 • Jun 3, 2017 SQLSaturday Baton Rouge 2017 • July 29, 2017 SQLSaturday Columbus GA (Phenix City AL) • Usually in the Fall (not announced yet) SQLSaturday Atlanta 2017 • Jul 15, 2017 • • • 45