The SharePoint primer
If you are reading this, odds are you already work with SharePoint. So, isn’t reading a primer a waste of your time? Not really. As with all things tech, and more to the point Microsoft’s enterprise technology offerings, complete mastery is elusive. For instance, your SharePoint deployment should support sharing, collaboration, and content management depending on your organizational needs. But are you getting the most out of your deployment? And by the way, in how many ways can users collaborate?
You can deploy SharePoint, specifically SharePoint 2016, in three ways:
- SharePoint Foundation: This is an entry-level SharePoint deployment that is available for download free. It has limited security and administrative options and basic apps (document libraries, lists, etc)
- Office 365: This is a cloud-based deployment that consists of all the office features with collaboration features enabled via SharePoint
- SharePoint Server: This will be the main focus of this tutorial. It is the enterprise workhorse, with myriads of customization and extension options
Microsoft SQL Server primer
Microsoft SQL Server is a relational database management system (RDBMS) that is available in the client-server architecture. To continue with this tutorial, it would be best if you have some level of Microsoft SQL Server training. It consists of two components, workstation and the server:
- SQL Server workstation: To access the server for database creation, querying, or other management aspects, the developer or operator machine must have SQL Server workstation installed. It consists of components such as SSCM, BIDS, SSMS, SQLEM, and Profiler among others
- SQL Server install: These are components that go into the server install. They consist of services such as SQL Server, SSIS, SQL Server agent, SSAS, SQL Server browser, SSIS, SSRS, and SQL Server full-text search among others. Typically, the SQL server install also contains SQL Server workstation for local management of the server
A marriage made in heaven: SQL Server and SharePoint Server
The current stable edition of SharePoint is 2016, which works best with SQL Server 2014. The SharePoint 2016 server is tightly coupled with its SQL Server database engine which can be either SQL Server 2014 (Service Pack 1) or SQL Server 2016 RTM. It uses the database servers to store content and settings. To get the most out of your deployment, you need to understand these relationships and the importance of each database.
Tips for working with SQL Server and SharePoint
Tip 1: Know thy databases
During installation, SharePoint automatically or the admin manually creates the following databases:
Configuration databases
The central administration content and configuration go to two separate databases collectively termed as configuration databases. They contain the deployment settings consisting of all other databases used, websites created via Internet Information Services, site templates, web applications, blocked file types, default quota and Web Part packages.
Content databases
Content databases contain all content:
- Site documents including documents libraries and their files
- Lists data
- SharePoint apps database
- Objects and data for project server
- Role-based access usernames and associated permissions
Each web app can have more than one content database but each site collection can only be linked to one database. Many a system administrator has spent hours wondering why site data is wrong on restoring databases only to realize the problem is due to pointing site to a wrong database. If you need to link external data such as marketing database software to SharePoint, this is the point to look at.
Service application databases
Even after Microsoft SQL training, I could not pinpoint where to manipulate data for service applications until I learnt about the service application databases.
Tip 2: Understand IOPS and SQL server
When running SharePoint Server, data access is the main performance bottleneck. If your installation is running slow or you are planning a new enterprise deployment, it is essential to appreciate that your system will only run as fast as it can read or write data. That is when I/O operations per second (IOPS) come into play. If I had a penny every time a sysadmin asks me why his/her install is still slow even after increasing RAM, I would have amassed seven dollars by now. This is particularly important if you have external sources of data such as CRM or marketing database software applications that are also writing to your SQL database.
Tip 3: RAID not RAM
This tip is related to tip two above in that IOPS bottlenecks cannot be resolved by increasing RAM. That is like expecting a bigger fuel tank to increase the speed of your car. The issue is hard disk access time. If your install is running slow, use hard disk arrays (RAID) or change to Solid State Disk Drives (SSDD), but don’t go shopping for RAM. When benchmarking your system performance, pay close attention to latency.
Tip 4: Provide adequate storage
Remember tip one? There is a reason it is the first tip; it is the most important. SharePoint Server stores files in SQL Server. Do not limit SQL Server storage. The most critical database is content database. Allow 5GB for configuration databases and a minimum of 100GB for content databases. Alternatively, use the following formula:
Size of database = ((D × V) × S) + (10 KB × (L + (V × D))) where
- D = Expected number of documents
- V = Average number of versions per document
- S = Average expected size of each document
- 10KB is an estimate of the average amount of metadata that will be required by SharePoint. You might change this if you will be storing lots of metadata
Tip 5: Make an allowance for audit and recycle bin database
In SharePoint, there is a two-stage recycle bin and until a document is deleted from both recycle bins, it continues to consume data. To allow for adequate space, estimate how many documents are deleted each cycle to allow the full effect of the recycle bins on the size of content databases.
Audit data can easily spiral out of control and crash your system. It is particularly important not to unnecessarily allow view auditing. Instead of turning on all audit capabilities, it is more prudent to tailor your audit triggers to only those events required by internal or regulatory controls. To estimate the reserve, you require for audit data:
- Estimate the number of auditing entries for each site and multiply it by 2KB
- Multiply the result you get above with the number of days you want to keep the logs for and allocate twice the amount of space in your answer
View all posts by Vin Cannarelli
- 5 tips for working with SQL Server databases to support SharePoint - November 23, 2017