SQL Server Best Practices For SharePoint 2019

SQL Server is the major core block and the main backbone for any SharePoint farm to ensure the performance, availability. Poorly SQL design and configurations lead to poorly SharePoint performance!

In this section, we’ll list the most common SQL Server Recommendations for SharePoint:

Supported SQL Server Versions for SharePoint 2019

Below are the Supported SQL Server versions for SharePoint 2019:

SQL Server 2016 Standard or Enterprise Edition.
SQL Server 2017 Standard or Enterprise Edition.
SQL Server 2019 Standard or Enterprise Edition.
Any SQL Server future versions that support 130 compatibility level.

For SharePoint 2019 and 2016, it’s recommended to use the SQL Server Enterprise Edition to get additional capabilities for performance, availability, security, and management.

You may also like to read Supported SQL Server Version for SharePoint 2016

Unsupported SQL Server Versions for SharePoint 2019

Below are the unsupported SQL Server versions for SharePoint 2019:

Any SQL Server version lower than SQL Server 2016.
SQL Server Express.
SQL Server 2017 on Linux.
Azure SQL Database (non-managed instance).

SQL Server Reporting Services integration Mode is no longer supported for SharePoint Server 2019.

SQL Server Performance Recommendations for SharePoint

There are three main factors that used to measure the SQL Server performance:

Disk I/O subsystem.
Memory.
CPU.

In this section, we’re gonna list the common SQL Server best practices for SharePoint from Performance perspectives:

For production deployment, It’s recommended to use a dedicated SQL Server that does NOT
- Host databases for other applications,
- Run other farm roles.

The only exception is deploying a SharePoint on a Single-Server (standalone) farm for development and learning purposes.

It’s recommended to use a single SQL Server instance per database server to ensure the high performance and facilitate the maintenance operations.
It’s strongly recommended to don’t use the default SQL Configuration for SharePoint.
It’s mandatory to set the Max Degree of Parallelism to the supported value 1.

Set Max Degree of Parallelism to 1 for SharePoint

The SharePoint Configuration Wizard will fail to create the configuration database if the max degree of parallelism is set to any other number.

It’s preferred to use Windows Core for SQL Server.
The NTFS disk allocation size should be 64K, instead of 4K.
It’s recommended to use multiple data files for heavy-use content databases.
- The number of content database data files should be less than or equal to the number of core CPUs.
- The data files size should be set at an equal size.
- It’s strongly recommended to use the SQL Server backup and recovery tool when you use multiple data files for a content database.
Separate database data and transaction log files across different disks.
Use faster and separate physical hard disks for
- tempdb data files and transaction logs.
- Content Databases.
- Transaction Logs.
- Usage Database.
- Search databases expect search administration database.
It’s recommended to use RAID 10 specifically for the drive that stores tempdb data files.

SharePoint Server supports all RAID types.

Don’t use the default auto-growth.

You should set auto-growth values to a fixed number of megabytes instead of a percentage.

Disable auto-create statistics for all SharePoint content databases.

Enabling auto-create statistics is not supported for SharePoint Server.

You may also like to check Supported Windows Server versions for SharePoint 2019

SQL Server Security Recommendations for SharePoint

In this section, we’re gonna list the common SQL Server best practices for SharePoint from Security perspectives:

Use named instance instead of the default one.
Use SQL Alias and configure it for all SharePoint Servers within the farm using cliconfg.exe.

Use Kerberos to improve authentication performance as well as to increase security.
Use Windows Mode Authentication, instead of Mixed-Mode Authentication if possible.
Disable System Account SQL Authentication if possible.
Instead of using the default ports, use different non-default ports for TCP and UDP.
Block the UDP port 1434.
Block the TCP port 1433.

Don’t forget to create new Inbound Rules for the new ports on Windows Firwall.

Other SQL Server Recommendations for SharePoint

In this section, we’re gonna list additional SQL Server best practices for SharePoint:

Install the latest supported service packs and updates for SQL Server.
Set the Default Collation setting to Latin1_General_CI_AS_KS_WS.
Don’t restrict the database size to avoid unexpected behavior when the capacity is exceeded.
It’s recommended to use Resource Governor with sharePoint to manage workloads as well as monitor and control the server resources (CPU, Memory) consumption when the system is under load.

Limit the crawl component consumption to 10% of Server CPU.

The disk drivers that store databases should not be shared by other applications, such as IIS logs and paging file.
It’s recommended to measure the I/O subsystem using the Diskspd Utility.
Unlike SharePoint 2013, in the SharePoint Servers 2016 and 2019,
- SQL statistics are NOT managed by the health rule.
- Auto Update Statistics for the content database should be set to TRUE.
Exclude all SQL files from Antivirus Scanning:
- .mdf
- .ldf
- .bak

SQL Server Memory Requirements for SharePoint

The estimated memory required for SQL Servers within the SharePoint farm mainly depends on the size of content databases.

As mentioned below, bigger content database size, higher memory required.

Therefore, the minimum recommended requirement for

Up to 2 terabytes: 32 GB.
Up to 5 terabytes: 64 GB.
More than 5 terabytes: > 64 GB.

Set Memory Settings for SQL Server

By default, SQL Server can change its memory requirements dynamically based on the available resources.

Although the dynamic memory use for SQL Server is recommended, however, setting this configuration may be useful when you want to manage and configure an instance of SQL Server in conjunction with the memory requirements of other applications that run on the same server.

To manually manage and configure SQL Server Memory Settings, you should do the following:

As System Admin, log in to the SQL Server Management Studio.
Right-Click on the server instance, Select “Properties”.

SQL Server Properties for SharePoint 2019

On the right side, Click on “Memory”.

SET Min and Max Memory for SharePoint SQL Server

Min SQL Server Memory

Setting the min server memory helps you to guarantee the minimum amount of memory available to the buffer pool of the SQL Server instance.

The default min server memory setting is 0,
The recommended min server memory setting should be the default.

Max SQL Server Memory

Setting the max server memory helps you to prevent the SQL Server buffer pool from using more than the specified amount of memory.

The default max server memory setting is 2147483647,
The minimum memory value for max server memory is 16 MB.
The recommended max server memory setting should be less than the amount of the installed RAM.

Calculating Max SQL Server Memory

Consider the installed physical memory is 32 GB, so the recommended max server memory setting should be less than this amount as the following:

(32*1024) - (the memory required for the operating system + other allocated memory for other systems + any other instances of SQL Server) in MB

Changing the max server memory may require to restart SQL Server to release the memory.

Do not set min server memory and max server memory to the same value.

SQL Server Network Requirements for SharePoint

SQL Server requires at least 1Gbps network connectivity and latency that should not be greater than 1ms.
It’s recommended that SharePoint servers have two network adapters:
- One to handle user traffic,
- Another to handle communication with the database servers that are running SQL Server.

SQL Server High Availability for SharePoint 2019

SharePoint supports the below SQL Server High Availability solutions:

SQL Server Clustering (doesn’t provide storage high availability),
Database Mirroring (deprecated),
SQL Server AlwaysOn Availability Groups and AlwaysOn Failover Cluster Instances. (The recommended solution)

SharePoint Server does not support read-only replicas. It only supports the primary replica in the availability group.

Learn how to Configure SQL Server AlwaysOn Availability Groups for SharePoint Server.

SharePoint Databases List

As we earlier mentioned, SharePoint Server 2019 databases can be hosted in Microsoft SQL Server 2016 and Microsoft SQL Server 2017.

In this section, we’ll list the SharePoint databases types categorized as the following:

SQL Server system databases.
SharePoint System databases.
SharePoint Service application databases.

SQL Server system databases

In this section, we’ll briefly explore the SQL Server system databases that used by SharePoint Server:

Master database.
Model database.
msdb database.
tempdb database.

Master database

It stores all SQL Server instance system-level information includes
- logins,
- configurations,
- Other databases.
It must be located on the same SQL Server instance that SharePoint uses.
It must always exist on a SQL Server and can’t be dropped.
The default recovery model is Simple.

Model database

It serves as a template for all future databases created on the SQL Server Instance.
It must be located on the same SQL Server instance that SharePoint uses.
It must always exist on a SQL Server and can’t be dropped.
Its database owner (sa) and can’t be changed.
The default recovery model is Full.
All newly created databases use the same recovery model as the model database.

msdb database

It is used by SQL Server Agent for scheduling alerts and jobs.
It must be located on the same SQL Server instance that SharePoint uses.
It must always exist on a SQL Server and can’t be dropped.
The default recovery model is Simple.
It’s recommended to place the msdb transaction log on fault-tolerant storage.

tempdb database

It is used to hold all temporary tables, temporary stored procedures, and any other temporary storage needs.
It is recreated every time SQL Server starts.
Backup and restore are not allowed on tempdb.

The default recovery model is simple, it’s not recommended to change it on the production environment.
It’s recommended to place the tempdb on a RAID 10 array.
The number of tempdb data files is mainly depend on the number of (logical) processors on the machine. so as per Microsoft Recommendations,
- If the number of logical processors is less than or equal to eight, use the same number of data files as logical processors.
- If the number of logical processors is greater than eight, use eight data files. Then if contention continues, add four data files at a time until the contention decreases to acceptable levels, or make changes to the workload/code , but do not add more than the number of logical processors.
The tempdb data files size should be set at an equal size.
It’s recommended to separate tempdb database data and transaction log files across different disks.
It’s recommended to set the initial size 1024 MB, auto-growth 1024 MB for data files.
It’s recommended to set the initial size 2048 MB, auto-growth 1024 MB for Log files.

SharePoint System databases

System databases are automatically created when you run the SharePoint Products Configuration Wizard.

SharePoint Configuration Database (SharePoint_Config).
SharePoint Central Administration Content Database.
SharePoint Content Database.

SharePoint Configuration Database

It’s a mandatory database that created when you run the SharePoint Configuration Wizard.

SharePoint-Server-2016-Configuration-Wizard-Database-Settings

The configuration database defines your SharePoint farm, it keeps all information about the following:

IIS Web Sites.
Web Application.
SharePoint Database.
Site templates.
Web Part packages.
Distributed Cache configuration objects.
The state of all servers that run the Distributed Cache service within the farm.

SharePoint Configuration Database for SharePoint 2019

It also contains specific data for SharePoint Server farm settings, such as

Default quota settings,
Blocked file types.

You may also like to read SharePoint 2016: Failed to create the configuration database.

SharePoint_Config Recommendations

The default database name is “SharePoint_Config“.

It’s not supported to change the SharePoint_Config database name after its created

The “SharePoint_Config” must be located with the Central Administration database (SharePoint_AdminContent_GUID) on the same SQL Server instance.
Only one configuration database is supported per farm.
The default recovery model is FULL.

It’s recommended to don’t change the default recovery model for the configuration database, and take regularly backups to truncate the log files.

You may also like to read the transaction log for database ‘SharePoint_Config’ is full due to LOG_BACKUP

SharePoint farm must be first taken offline before performing backup and restore for the configuration database.

Central Administration Content Database

It’s the Content database for the Central Administration web application created automatically by the SharePoint Configuration Wizard.

The default database name is “SharePoint_AdminContent_GUID“.
It’s must be located with the “SharePoint_Config” on the same database instance.
Only one Central Administration content database is supported per farm.
The default recovery model is FULL.

SharePoint farm must be first taken offline before performing backup and restore for the Central Administration content database.

SharePoint Content Database

It’s a database that stores content for one or multiple site collections for a web application.

The default database name is “WSS_Content“.
The default recovery model is FULL.
It’s strongly recommended to use Windows Authentication when you create a new content database.
It’s recommended to use multiple data files for heavy-use content databases.
- The number of content database data files should be less than or equal to the number of core CPUs.
- The data files size should be set at an equal size.
- It’s strongly recommended to use the SQL Server backup and recovery tool when you use multiple data files for a content database.
The supported limit for SharePoint Content Databases per farm is 500 content databases.

SharePoint Server 2019 supports 100TB of data for all content databases per farm.

Each web application can hold multiple content databases.
Each content database is associated with multiple site collections.
Keep content databases below 200 GB for general usage.
If the content database holds more than one site collection, the site collection should not exceed 100 GB.

You may also like to read SharePoint 2019 Limitations

Content database includes

Site files and documents,
Document libraries,
List data,
SharePoint Apps.
Web Part properties,
Audit logs,
Sandboxed solutions,
User names and rights.
Project Server 2019 data and objects.

Service Application Databases

The Service Application Databases automatically created when you deploy a new service application based on the server role in the MinRole feature.

You may also like to read SharePoint 2016: Convert Server Role

In this section, we’ll explore the below Service Application Databases:

User Profile service application databases.
State service application database.
Managed Metadata service application database.
Project Server service application database.
PerformancePoint service application database.
App Management database.
Secure Store Service database.
Usage and Health Data Collection database.
Search service application databases.
Business Data Connectivity service application database.

User Profile service application

It’s used to synchronize User Profiles from Active Directory to SharePoint.

As we know, the Forefront Identity Manager is no longer available in SharePoint 2016, it only has an Active Directory Import option to sync user profiles from the Active Directory in one way, and to get the same two-way sync features that we had before, you can use Microsoft Identity Manager.

User Profile service application databases

In SharePoint Servers 2019 and 2016, the User Profile service has three databases:

Social DB: stores social tags and notes created by users. – Although the notes and tags are completely deprecated in SharePoint 2019 and 2016. this database will be created.
Synchronization: stores configuration and staging data for use when profile data is being synchronized with Active Directory.
Profile DB: stores and manages users and associated information like
- User’s social network information,
- Users memberships in distribution lists and sites.

State Service service application

It’s used to stores temporary session state information for

InfoPath Forms Services,
Exchange Server,
Chart Web Part,
Visio Services.

State Service service application can only be configured using PowerShell.

State service application database

State Service service application has only one database that its default name is StateService_GUID.
The default recovery model is FULL.

Managed Metadata Service Application

Managed Metadata is a central repository that stores taxonomy information in a hierarchy structure.

Managed Metadata service application database

Managed Metadata service application database stores managed metadata and syndicated content types.

Managed Metadata Service Application has only one database that its default name is Managed Metadata Service_GUID.
The default recovery model is FULL.

Project Server service application

One of the biggest changes in Project Server 2019 and Project Server 2016 is the installation process.

The SharePoint Server 2019 MSI file will contain the installation files for Project Server 2019 as well.
Project Server 2019 runs as a service application in SharePoint Server 2019.
Project Server 2019 is only available on SharePoint Server 2019 Enterprise.

Project Server Service Application for Project Server 2019

Although Project Server 2019 is included as part of the SharePoint Server 2019 Enterprise installation, it still requires a separate license.

You may also like to read

Project Server service application database

In Project Server 2019/2016, a single SharePoint Content Database holds the project data and the content to facilitate the database operations, such as backup and restore, migration, etc.

For each Project Web App Instance, Project Server creates a separate database that contains the following:

All Project and Portfolio data.
Time tracking and Timesheet data.
SharePoint project site data.

Project Server database for Project Server 2019

The default recovery model for the Project Server Content database is FULL.

PerformancePoint service application

It is a performance management service that used to monitor and analyze your business by building KPIs, dashboards.

Silverlight is not available in PerformancePoint Services in SharePoint Server 2019.

PerformancePoint Services service application database

The PerformancePoint Services database stores temporary objects and persisted user comments and settings.

PerformancePoint Service Application has only one database that its default name is PerformancePoint Service Application _GUID.
The default recovery model is FULL.

App Management Service Application

App management service application is a prerequisite service for

Workflow Manager.
SharePoint Add-Ins.
Hybrid Scenarios.

App Management Service Application for SharePoint Server 2019

You may also like to read Sorry, something went wrong App Management Shared Service Proxy is not installed

App Management database

The App Management database is used to stores the app licenses and permissions that are downloaded from the App Catalog or SharePoint Store.

App Management Service Application has only one database that its default name is AppMng_Service_DB_GUID.
The default recovery model is FULL.

Secure Store Service Application

The Secure Store Service provides credential delegation and access to other services.

Secure Store Service database

The Secure Store Service provides a database that is used to store credentials such as account names and passwords.

Secure Store Service Application has only one database that its default name is Secure_Store_Service_DB _GUID.
The default recovery model is FULL.

Usage and Health Data Collection Service Application

The Usage and Health Data Collection Service Application is used to writes usage and health data to the Logs folder and to the logging database.

It can be configured and managed via Central Administration.

Configure usage and health data collection for SharePoint 2019

Only one Usage and Health Data Collection service application instance is supported per farm.

Usage and Health Data Collection database

The Usage and Health Data Collection database stores health data logs and usage data temporarily.
It can be used for reporting, Performance analysis, and diagnostics.
It is the only SharePoint database that supports schema modifications.

It’s not supported to directly query or modify the SharePoint databases except Wss_Logging database

It has only one database that its default name is WSS_Logging.
The default recovery model is Simple.

The Wss_Logging database should not be part of the Availability Group, for more details check The operation cannot be performed on database Wss_Logging.

Search Service Application

SharePoint 2019 has both a classic and modern search experience. Both search experiences use the same search index to find search results.

Neither classic search experience nor modern search experience can be turned off.

The classic search experience is available on

Publishing sites,
Classic team sites,
Search Center.

The modern search experience is available on

SharePoint home page,
Communication sites,
Modern team sites.

Search service application databases

The Search service application has the below four databases:

Search Administration: The Search Administration database hosts the Search service application configuration and access control list for the crawl component.
Analytics Reporting: The Analytics Reporting database stores the results for usage analysis reports and extracts information from the Link database when needed.
Crawl: The Crawl database stores the state of the crawled data and the crawl history.
Link: The Link database stores the information that is extracted by the content processing component and the click-through information.

In SharePoint, SQL Server data compression is only supported for the Search service application databases.

Business Data Connectivity service application

The Business Data Connectivity Service provides connectivity and CRUD operations to external data sources and displays these data inside the sharepoint External List.

In SharePoint 2019/2016, Business Data Connectivity service provisioned automatically by MinRole.

You may also like to read the External List Limitations

Business Data Connectivity service application database

It stores external content types and related objects.
Only one Business Data Connectivity database is supported per farm.
It has only one database that its default name is Bdc_Service_DB_GUID.
The default recovery model is FULL.

SQL Server Service Accounts for SharePoint

Below is the list of SQL Server Service accounts that used to configure and deploy SQL Server for SharePoint Farm.

Account	Description	Rights
SQLAdmin	It’s used to install and configure SQL Server	Local Admin on all SQL Servers
SQLSrv	It’s used to run the SQL Engine Widows Service	In the Local Security Policy, it should have Log on as a service.
SQLAgent	It’s used to run the SQL Server Agent Service	In the Local Security Policy, it should have Log on as a service.

You may also like to read SharePoint 2019: Service Accounts Recommendations.

Applies To

SharePoint 2019.
SharePoint 2016.

Conclusion

In conclusion, we’ve listed the common recommendations and SQL Server best practices for SharePoint 2019 and SharePoint 2016.

Additionally, we’ve explored Supported and Unsupported SQL Server Versions for SharePoint 2019.

Finally, we have explored the SQL Server Databases list in SharePoint 2019 and SQL Server Service Accounts for SharePoint 2019 and 2016.

References

Have a Question?

If you have any related questions, please don’t hesitate to Ask it at deBUG.to Community.