使用SQL Server数据库支持SharePoint的5条技巧

SharePoint入门 (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?

如果您正在阅读本文，很可能您已经在使用SharePoint。 因此，阅读底漆不是在浪费您的时间吗？ 并不是的。 与所有技术一样，以及微软的企业技术产品，完全的掌握是难以捉摸的。 例如，根据您的组织需求，SharePoint部署应支持共享，协作和内容管理。 但是，您能从部署中获得最大收益吗？ 顺便说一下，用户可以通过几种方式进行协作？

You can deploy SharePoint, specifically SharePoint 2016, in three ways:

您可以通过三种方式部署SharePoint，尤其是SharePoint 2016：

1. 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)
2. SharePoint Foundation： 这是一个入门级SharePoint部署，可以免费下载。 它具有有限的安全性和管理选项以及基本应用程序（文档库，列表等）
3. Office 365: This is a cloud-based deployment that consists of all the office features with collaboration features enabled via SharePoint
4. Office 365： 这是一个基于云的部署，包含所有办公室功能以及通过SharePoint启用的协作功能
5. SharePoint Server: This will be the main focus of this tutorial. It is the enterprise workhorse, with myriads of customization and extension options
6. SharePoint Server： 这将是本教程的重点。 它是企业的主力军，具有无数的自定义和扩展选项

Microsoft SQL Server primer

Microsoft SQL Server入门

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:

Microsoft SQL Server是一种关系数据库管理系统（RDBMS），可在客户端-服务器体系结构中使用。 要继续本教程，最好是经过一定程度的Microsoft SQL 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工作站：要访问服务器以进行数据库创建，查询或其他管理方面的操作，开发人员或操作员计算机必须安装SQL Server工作站。 它由SSCM，BIDS，SSMS，SQLEM和Profiler等组件组成
• 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 SQL Server安装：这些是进入服务器安装的组件。 它们由诸如SQL Server，SSIS，SQL Server代理，SSAS，SQL Server浏览器，SSIS，SSRS和SQL Server全文搜索之类的服务组成。 通常，SQL Server安装程序还包含用于服务器本地管理SQL Server工作站

A marriage made in heaven: SQL Server and SharePoint Server

天堂般的婚姻：SQL Server和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.

SharePoint的当前稳定版本是2016，最适合与SQL Server 2014一起使用。SharePoint2016服务器与其SQL Server数据库引擎紧密结合，后者可以是SQL Server 2014（Service Pack 1）或SQL Server 2016 RTM。 它使用数据库服务器存储内容和设置。 为了充分利用您的部署，您需要了解这些关系以及每个数据库的重要性。

使用SQL Server和SharePoint的提示 (Tips for working with SQL Server and SharePoint)

Tip 1: Know thy databases

提示1：了解您的数据库

During installation, SharePoint automatically or the admin manually creates the following databases:

在安装过程中，SharePoint自动或管理员手动创建以下数据库：

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.

管理中心的内容和配置进入两个单独的数据库，这些数据库统称为配置数据库。 它们包含由所有其他使用的数据库，通过Internet信息服务创建的网站，站点模板，Web应用程序，阻止的文件类型，默认配额和Web部件包组成的部署设置。

Content databases

内容数据库

Content databases contain all content:

内容数据库包含所有内容：

1. Site documents including documents libraries and their files
   站点文档，包括文档库及其文件
2. Lists data
   列出数据
3. SharePoint apps database
   SharePoint应用程序数据库
4. Objects and data for project server
   项目服务器的对象和数据
5. 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.

每个Web应用程序可以具有多个内容数据库，但是每个网站集只能链接到一个数据库。 许多系统管理员已经花费了数小时来思考为什么站点数据在还原数据库时是错误的，只是为了意识到问题是由于将站点指向错误的数据库。 如果您需要将外部数据（例如市场营销数据库软件）链接到SharePoint，这是需要研究的重点。

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.

即使经过Microsoft SQL培训 ，在了解服务应用程序数据库之前，我也无法查明在何处处理服务应用程序的数据。

Tip 2: Understand IOPS and SQL server

提示2：了解IOPS和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.

运行SharePoint Server时，数据访问是主要的性能瓶颈。 如果您的安装运行缓慢，或者您正在计划进行新的企业部署 ，则必须意识到您的系统将仅以其可以读取或写入数据的速度运行。 那就是每秒I / O操作（IOPS）发挥作用的时候。 如果每次系统管理员问我为什么只花一分钱，即使增加RAM之后，为什么他/她的安装仍然很慢，我现在已经积累了7美元。 如果您拥有外部数据源（例如CRM或市场营销数据库软件应用程序，它们也正在写入SQL数据库），则这一点尤其重要。

Tip 3: RAID not RAM

提示3：RAID不是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.

该技巧与上面的技巧2有关，因为无法通过增加RAM解决IOPS瓶颈。 这就像期望更大的油箱可以提高汽车的速度一样。 问题是硬盘访问时间。 如果您的安装运行缓慢，请使用硬盘阵列（RAID）或更改为固态磁盘驱动器（SSDD），但不要购买RAM。 在对系统性能进行基准测试时，请特别注意延迟。

Tip 4: Provide adequate storage

提示4：提供足够的存储空间

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:

还记得技巧一吗？ 这是第一个提示的原因。 这是最重要的。 SharePoint Server将文件存储在SQL Server中。 不限制SQL Server的存储。 最关键的数据库是内容数据库。 配置数据库至少需要5GB，内容数据库至少需要100GB。 或者，使用以下公式 ：

Size of database = ((D × V) × S) + (10 KB × (L + (V × D))) where

数据库大小=（（D×V）×S）+（10 KB×（L +（V×D））））

1. D = Expected number of documents
   D =预期文件数
2. V = Average number of versions per document
   V =每个文档的平均版本数
3. S = Average expected size of each document
   S =每个文档的平均预期大小
4. 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
   10KB是SharePoint所需的平均元数据量的估计值。 如果要存储大量元数据，则可以更改此设置

Tip 5: Make an allowance for audit and recycle bin database

提示5：允许审核和回收站数据库

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.

在SharePoint中，有两个阶段的回收站，直到从两个回收站中删除文档之前，该文件都会继续使用数据。 为了留出足够的空间，请估计每个周期要删除多少个文档，以使回收站对内容数据库的大小产生完全影响。

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:

审核数据很容易失控，并使系统崩溃。 特别重要的是不要不必要地允许视图审核。 与其启用所有审核功能，不如将审核触发器定制为仅适用于内部或法规控制所要求的事件，这是更为谨慎的做法。 要估算准备金，您需要审计数据：

1. Estimate the number of auditing entries for each site and multiply it by 2KB
   估计每个站点的审核条目数并将其乘以2KB
2. 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
   将以上获得的结果乘以要保留日志的天数，并在答案中分配两倍的空间

翻译自: https://www.sqlshack.com/5-tips-working-sql-server-databases-support-sharepoint/