Storage and SQL Server capacity planning and configuration
In this we will learn about Storage and SQL Server capacity planning and configuration. When it comes to capacity planning, it includes guidance for planning and configuring the storage and SQL Server database tiers in a SharePoint Server installation.
Design and configuration process for SharePoint Servers 2016 and 2019 storage and database tier
These sections provide detailed information about each design step, including storage requirements and best practices:
Gather storage and SQL Server space and I/O requirements
Several SharePoint Server architectural factors influence storage design. The quantity of information enabled features, deployed service applications, number of farms, and availability needs are the most important considerations.
1. Databases used by SharePoint Server
- The databases that are installed with SharePoint Servers 2016 and 2019 depend on the service applications that are used in the environment.
- The SQL Server system databases are used in all SharePoint Server configurations. This also includes a list of the databases that are installed with SharePoint Servers 2016 and 2019.
- Specific location recommendations or requirements exist for several SharePoint Server, SQL Server Database Engine, and SQL Server Reporting Services (SSRS) databases.
- However, if you’re integrating SQL Server further, your environment can include multiple databases, like in the example below. Only SQL Server 2016 RTM Enterprise Edition and SQL Server 2016 SQL Server Analysis Services may be utilised with SQL Server Power Pivot for SharePoint in a SharePoint Server 2016 environment (SSAS).
2. Understand SQL Server and IOPS
- Firstly, on any server that hosts a SQL Server instance, it is very important that the server achieve the fastest response possible from the I/O subsystem.
- Secondly, faster disks or arrays provide sufficient I/O operations per second (IOPS) while maintaining low latency and queuing on all disks.
- Thirdly, you cannot add other types of resources, such as CPU or memory, to compensate for slow response from the I/O subsystem. However, it can influence and cause issues throughout the farm.
- Lastly, before you deploy a new farm, we recommend that you benchmark the I/O subsystem by using the Diskspd Utility . Note that this tool works on all Windows Server versions with all versions of SQL Server.
3. Features that influence the size of content databases
The following SharePoint Server features can significantly affect the size of content databases:
- Firstly, recycle bins Until a document is fully deleted from both the first stage and second stage recycle bin, it occupies space in a content database. Calculate how many documents are deleted each month to determine the effect of recycle bins on the size of content databases.
- Secondly, auditing Audit data can quickly compound and use large amounts of space in a content database, especially if view auditing is turned on. Rather than letting audit data grow without constraint, we recommend that you enable auditing only on the events that are important to meet regulatory needs or internal controls. Use the following guidelines to estimate the space that you must reserve for auditing data:
- Estimate the number of new auditing entries for a site, and multiply this number by 2 KB (entries generally are limited to 4 KB, with an average size of about 1 KB).
- Based on the space that you want to allocate, determine the number of days of audit logs you want to keep.
Choose SQL Server version and edition
We recommend that for SharePoint Servers 2016 and 2019 you consider running your environment on the Enterprise Edition of the following SQL Servers to take advantage of the additional performance, availability, security, and management capabilities that these versions provide.
- Firstly, SQL Server 2014 with Service Pack 1 (SP1) (SharePoint Server 2016 only)
- Secondly, SQL Server 2016 (SharePoint Servers 2016 and 2019)
- Thirdly, SQL Server 2017 RTM (SharePoint Servers 2016 and 2019)
In particular, you should consider your need for the following features:
- Firstly, Backup compression Backup compression can speed up any SharePoint backup, and is available in every edition of SQL Server 2008 and later. By setting the compression option in your backup script, or by configuring the server that is running SQL Server to compress by default, you can significantly reduce the size of your database backups and shipped logs.
- Secondly, transparent data encryption If your security requirements include the need for transparent data encryption, you must use SQL Server Enterprise Edition.
- Thirdly, content deployment If you plan to use the content deployment feature, consider SQL Server Enterprise Edition so that the system can take advantage of database snapshots.
- Then, Remote BLOB storage If you want to take advantage of remote BLOB storage to a database or location outside the files associated with each content database, you must use the Enterprise Edition of:
SharePoint Server 2019
- Firstly, SQL Server 2016
- Secondly, SQL Server 2017 RTM
SharePoint Server 2016
- Firstly, SQL Server 2014 (SP1)
- Secondly, SQL Server 2016
- Lastly, SQL Server 2017 RTM
SharePoint 2013
- Firstly, SQL Server 2008 R2 with SP1
- Secondly, SQL Server 2012 Enterprise Edition
- Microsoft Power Pivot for SharePoint Enables users to share and collaborate on user-generated data models and analysis in Excel on the web while automatically refreshing those analyses. You must have Office on the web to use Excel on the web with Power Pivot for SharePoint and SharePoint Server 2016.
- Lastly, Power Pivot for SharePoint 2013 Enables users to share and collaborate on user-generated data models and analysis in Excel and in the browser while automatically refreshing those analyses. However, it is part of SQL Server 2008 R2 Analysis Services (SSAS) Datacenter and Enterprise Edition, SQL Server 2012 SP1 Analysis Services (SSAS) Enterprise Edition, and SQL Server 2014 Analysis Services (SSAS) Enterprise and Business Intelligence Edition.
Design storage architecture based on capacity and I/O requirements
The storage architecture and disk types that you select for your environment can affect system performance.
1. Choose a storage architecture
SharePoint Server supports storage architectures such as Direct Attached Storage (DAS), Storage Area Network (SAN), and Network Attached Storage (NAS), albeit NAS is only for use with content databases that are configured to use remote BLOB storage. However, your decision is influenced by aspects in your business solution as well as your current infrastructure.
2. Direct Attached Storage (DAS)
A DAS is a digital storage system that connects directly to a server or workstation, without the need for a storage network. Serial Attached SCSI (SAS) and Serial Attached ATA (SATA) are two types of DAS physical discs (SATA). When a shared storage platform can’t guarantee a response time of 20 ms and enough capacity for average and peak IOPS, we propose a DAS design.
3. Storage Area Network (SAN)
A storage area network (SAN) is an architecture that connects remote computer storage devices (such as disc arrays and tape libraries) to servers so that they seem to be locally tied to the operating system (for example, block storage). When the benefits of shared storage are crucial to your company, however, we recommend that you use a SAN.
The benefits of shared storage include the following:
- Firstly, easier to reallocate disk storage between servers.
- Secondly, can serve multiple servers.
- Lastly, no limitations on the number of disks that can be accessed.
4. Network Attached Storage (NAS)
A network-attached storage (NAS) device is a self-contained computer that connects to a network. Its main function is to provide other network devices with file-based data storage services. On the NAS unit, the operating system and associated software offer data storage, file systems, and file access, as well as control of these functions (for example, file storage).
5. Choose disk types
The sorts of discs you utilise in your system might have an impact on its reliability and performance. When all other factors are equal, bigger drives result in a longer mean search time. The following types of discs that SharePoint Server supports:
- Firstly, Small Computer System Interface (SCSI)
- Secondly, Serial Advanced Technology Attachment (SATA)
- Thirdly, Serial-attached SCSI (SAS)
- Then, Fibre Channel (FC)
- Next, Integrated Device Electronics (IDE)
- Lastly, Solid State Drive (SSD) or Flash Disk
6. Choose RAID types
RAID (Redundant Array of Independent Drives) is frequently used to increase individual disc performance (by striping data across several discs) and to guard against individual disc failures. SharePoint Server supports all forms of RAID. However, we advocate using RAID 10 or a vendor-specific RAID solution that provides comparable performance.
Understand network topology requirements
Plan the intra- and inter-farm network connectivity. We recommend that you connect to a low-latency network. The following list, however, contains some best practises and recommendations:
- Firstly, all servers in the farm should have LAN bandwidth and latency to the server that is running SQL Server. Latency should be no greater than 1 millisecond.
- Secondly, we do not recommend a wide area network (WAN) topology in which a server that is running SQL Server deploys remotely from other components of the farm over a network that has a latency greater than 1 ms.
- Thirdly, plan for an adequate WAN network if you plan to use SQL Server the AlwaysOn implementation suite, mirroring, log shipping, or Failover Clustering to keep a remote site up-to-date.
- Lastly, we recommend that web servers and application servers have two network adapters: one network adapter to handle user traffic and the other to handle communication with the servers that are running SQL Server.
Configure SQL Server
Estimate the servers requirements
SharePoint Server is useful in taking advantage of SQL Server scale-out. Always run SQL Server on a dedicated server that isn’t hosting databases for any other application or executing any other farm duties. The only time this guideline does not apply is when the system is deployed on a stand-alone server for development or non-performance-oriented testing.
The following is general guidance for when to deploy an additional server that will run a SQL Server instance:
- Firstly, add an additional database server when you have more than four web servers that are running at capacity.
- Secondly, add an additional database server when your current server has reached its effective resource limits of RAM, CPU, disk IO throughput, disk capacity, or network throughput.
Set SQL Server options
The following SQL Server settings and options configuration should done before you deploy SharePoint Server.
- Firstly, do not enable auto-create statistics on a server that hosts SQL Server and supports SharePoint Server. SharePoint Server configures the required settings upon provisioning and upgrade. Auto-create statistics can significantly change the execution plan of a query from one instance of SQL Server to another instance of SQL Server.
- Secondly, to ensure optimal performance, we strongly recommend that you set max degree of parallelism (MAXDOP) to 1 SQL Server instances that host SharePoint Server databases.
Validate and monitor storage and SQL Server performance
- Firstly, test that your performance and backup solution on your hardware enables you to meet your service level agreements (SLAs). In particular, test the I/O subsystem of the computer that is running SQL Server to make sure that performance is satisfactory.
- Secondly, test the backup solution that you are using to make sure that it can back up the system within the available maintenance window. If the backup solution can’t meet the SLAs your business requires. Then, consider using an incremental backup solution such as Microsoft System Center Data Protection Manager.
- Lastly, it is important to track the following resource components of a server that is running SQL Server: CPU, memory, cache/hit ratio, and I/O subsystem. When one or more of the components seems slow or overburdened, analyze the appropriate strategy based on the current and projected workload.
SQL Server counters to monitor
Monitor the following SQL Server counters to ensure the health of your servers:
- Firstly, General statistics. This object provides counters to monitor general server-wide activity, such as the number of current connections. And the number of users connecting and disconnecting per second from computers that are running an instance of SQL Server.
- Secondly, databases This object provides counters to monitor bulk copy operations, backup and restore throughput, and transaction log activities. Monitor transactions and the transaction log to determine how much user activity is occurring in the database and how full the transaction log is becoming. However, the amount of user activity can determine the performance of the database and affect log size, locking, and replication.
- Thirdly, Locks This object provides information about SQL Server locks on individual resource types.
- Next, Latches This object provides counters to monitor internal SQL Server resource locks called latches. Monitoring the latches to determine user activity and resource usage can help you identify performance bottlenecks.
- Then, SQL Statistics. This object provides counters to monitor compilation and the type of requests sent to an instance of SQL Server.
- After that, Buffer Manager. This object provides counters to monitor how SQL Server uses memory to store data pages, internal data structures, and the procedure cache. This also counters to monitor the physical I/O as SQL Server reads and writes database pages.
Reference: Microsoft Documentation