SharePoint 2013/2016 database maintenance
My name is Lyubov Volkova, I am a systems architect in the business solutions development department. In the previous article I talked about monitoring performance counters of SharePoint Server 2013/2016 . Today we will discuss an equally important task - the maintenance of SharePoint 2013/2016 databases.
Any Microsoft SharePoint 2013/2016 farm necessarily includes a SQL server that stores data from various services and components deployed for the corporate portal. As you know, the performance of working with databases has a significant impact on the work of the corporate portal as a whole. If you do not pay attention to the maintenance of the SharePoint database or do it illiterately, then in practice there are the following obvious problems: a decrease in the productivity of work on the corporate portal, which very often results in a gradual increase in the waiting time for pages, forms, data on web pages, and duplication of actions and their inconsistency in terms of administration.
Very often, the development of maintenance plans causes significant difficulties for portal administrators because of the complexity, complexity and the need to possess competencies not only in administering SharePoint, but also in administering SQL Server. Therefore, in the article I performed a review of all key database maintenance operations, gave examples of scripts and practical tips. Using the above descriptions, you will find many recommendations for action. I note that the articles are not exhaustive, because It is not possible to include a description of absolutely all the details and subtleties of servicing the SharePoint databases. In order for you to get more detailed information on a particular topic, there are links to additional publications.
')
The whole variety of databases on which SharePoint work depends can be divided into three groups:
For optimal performance of Microsoft SharePoint 2013/2016 databases, it is crucial that you follow the standard maintenance procedures for all related databases in a timely manner.
The following is a list of recommended SharePoint database maintenance tasks:
These database maintenance tasks can be performed using Transact-SQL commands, the user interface, and the maintenance wizard.
General information about the purpose, location requirements, default recovery models, and other information on each of the databases can be found here .
This article focuses on system administrators, database administrators and technical professionals involved in supporting the work of corporate SharePoint portals.
The DBCC CHECKDB command provides a comprehensive check of the logical and physical integrity of all objects in the specified database and includes the following set of checks:
Example script to check the integrity of the SharePoint content database "WSS_Content":
An example of the results of the execution of the script, indicating the absence of errors:
Examples of results indicating errors and the need to solve a problem for a specific database:
The DBCC CHECKDB command makes extensive use of I / O subsystem, CPU, and memory resources. To reduce the workload on a working system related to consistency checking, it is recommended that you execute the DBCC CHECKDB command for restored backup copies of SharePoint databases on a separate server.
If the DBCC CHECKDB command is executed on a working database, it is recommended that it be performed during non-working hours, limited to checking the integrity of the physical structure of the pages and the record headers and the integrity of the allocation of space in the database.
Index fragmentation is a state in which the key-determined logical order of pages in an index differs from the physical order in which pages are placed in data files. It may also indicate a low data density on the data file pages, which results in inefficient use of disk space, memory, and I / O resources.
The main reasons for index fragmentation are numerous inserts, updates, or deletes data in tables. Figure 1 and Figure 2 show the difference between the new unfragmented index and the same index after a large number of insert, update and delete operations. The red arrow defines the physical order of the index, and the black arrow defines the logical order of the pages.
Figure 1. Unfragmented index (source: Paul S. Randal)
Figure 2. Fragmented Index (source: Paul S. Randal)
Since the insertion, update, and deletion of data in rows of tables and indexes is uneven, each page will have different levels of completeness (data density) at different points in time. In this case, when processing a query that requires scanning part or all of the table indexes, a high degree of fragmentation will entail an increase in the number of page reads, which, in turn, will prevent parallel scanning of data and lead to a significant decrease in performance.
Index fragmentation can lead to poor performance and inefficient use of space. At the same time, even in databases with a low intensity of use, the degree of index fragmentation can grow at a rapid rate.
To reduce index fragmentation, two methods are supported:
Online index rebuild is supported only in SQL Server Enterprise editions. In cases where the release of SQL Server hosting the database in question, or the index itself, does not support online rebuilding of the index, the above procedures are performed offline. Online rebuild may not be available for an index containing columns with large objects, for example, columns with the data type NVARCHAR (MAX), IMAGE, etc.
During the autonomous rebuild process, the table level is locked, as a result of which they become unavailable for recording or even for viewing. Many SharePoint database indexes contain columns with large objects and are therefore always rebuilt using a stand-alone process.
It should be noted that in case of operative rebuilding, short-term locking of tables can be performed, as a result of which any operations, except for SELECT, are prohibited. SharePoint 2013/16 databases use clustered indexes in a special way. When rebuilding such an index offline, an exclusive lock is applied to the table, which completely prevents users from accessing it .
The preferred method of defragmenting an index depends on its degree of fragmentation, and also on whether it can remain online or needs to be processed offline. The following table describes the recommended defragmentation methods for indexes with varying degrees of fragmentation for SharePoint databases:
In SharePoint 2013/2016, the service and components monitoring process can be partially automated using the SharePoint Health Analyzer rules ( checking index statistics and checking index fragmentation for search service crawl databases). Their use allows daily assessment of the state, as well as automatically maintain the indices for the following databases:
You can view a complete list of system tasks related to health analysis in the SharePoint Administration Center by going to the Tracking \ View Job Definitions section. Examples of system tasks:
Many SharePoint databases include stored procedures used by the health analyzer to defrag indexes. Although these rules automatically perform defragmentation, it is possible that you will need to start defragmentation manually. For example, this may require your SLA or some corporate regulations.
You can schedule these stored procedures to run daily, weekly, or monthly depending on your needs and the general indicators of change in your environment.
It is recommended that you allow SharePoint to manage its own database indexes. If you need to manually perform this action, at a minimum it is recommended to set up a daily maintenance schedule so that the proc_DefragmentIndice s procedure and the weekly proc_MSS_DefragSearchIndexes procedure are performed .
Before proceeding with the implementation of a maintenance plan for a fragmented index, it is necessary to determine the most fragmented tables and indices, and only after that develop a plan for their rebuilding and reorganization .
For example, in SharePoint 2013/2016, the AllDocs table, which contains document libraries, associated documents, lists and list items, and the corresponding metadata are highly fragmented. Despite the availability of automated system tasks of the health analyzer, their work is not always effective
The degree of index fragmentation is defined as the proportion of index pages, the logical order of which is different from the physical one. To define it in SQL Server 2014 and SQL Server 2016, you should use the sys.dm_db_index_physical_stats dynamic administrative view.
You can use the following script (for example, the SharePoint content database “WSS_Content”):
The result set returned by sys.dm_db_index_physical_stats includes the following columns:
Example output of the index fragmentation analysis result:
Below is an example of a script that enables the reorganization of one of the indexes of the AllDocs table in the SharePoint content database:
An example script to rebuild all indexes on a separate SharePoint database table:
Sample script to rebuild a separate index
An example of the result obtained (checking defragmentation of the index after rebuilding:
You can use the fill factor to further optimize data storage and index performance. When creating or rebuilding an index, the fill factor (from 1 to 100) determines the percentage of space at the level of each leaf page that can be filled with data. The rest is reserved for further expansion. In most cases, the default fill level 0 set at the server level is optimal (each page can be filled to 100%). However, in SharePoint 2013, you can use a value of 80, which is set at the server level and provides optimal expansion options and minimal fragmentation.
The fill factor (the “fill factor” parameter) is used to fine tune the storage and index performance. When you create or rebuild an index, the fill factor displays the percentage of space occupied by each page in the final level, which allows you to reserve space for free space for future expansion. For example, if you specify a value of 80 for the fill factor, 20 percent of the disk space occupied by it will be reserved on each page of the final level. This disk space will be used to expand the index when new data is added to the base table. Empty space is reserved not at the end of the index, but between the rows of the index.
The fill factor is a percentage from 1 to 100; The default value on the server is 0, which means complete filling of the pages of the final level.
For SharePoint, 80 is best for supporting database growth and reducing index fragmentation.
The following script will allow you to get information from the current default index fill factor settings at the SQL server level:
Example result:
Care should be taken to ensure that the automatic compression of SharePoint databases is not enabled in any way. Compression can be used to reduce the size of a data file or transaction log, but it is a very coarse, resource-intensive process that causes extensive logical fragmentation of browsing in data files and leads to poor performance. Manual compression of individual data files and log files may be acceptable under special circumstances.
Automatic compression is especially harmful because it runs every 30 minutes in the background and tries to compress databases for which the automatic compression option is set. This process is not entirely predictable in that it only compresses databases with more than 25% free space. Automatic compression uses a lot of resources and causes degradation of performance, so that it is undesirable under any circumstances. It should always be turned off.
Database compression can be performed only after deleting a large amount of data, provided that the resulting free space is not expected to be used. It is strongly recommended to remember a few important rules:
Use the DBCC SHRINKDATABASE command to compress data and log files, for example:
To compress individual files, use the DBCC SHRINKFILE command, for example:
In practice, the default recovery models for databases on which SharePoint relies are not always optimal and lead to an increase in transaction log files and inefficient use of disk space, additional backup costs.
The table below summarizes the recommendations for typical configuration of recovery modes for each of the SharePoint databases.
Table 1. Typical recovery mode settings for SharePoint databases
These recommendations can serve as a guideline, but in order to make a decision on the adjustment of the recovery mode of each individual database, we strongly recommend that you at least analyze:
To perform a manual backup of the database on the SQL server (for example, the system model database), you must perform the following steps:
Detailed recommendations for backup and recovery in SharePoint 2013 can be found here .
Using SQL Server maintenance plans, you can automate and schedule important tasks to protect your data. Using maintenance plans in SQL Server, an administrator can plan various operations, including checking database consistency, reorganizing, and rebuilding indexes.
To create a SQL Server 2014/2016 database maintenance plan, follow these steps:
Any Microsoft SharePoint 2013/2016 farm necessarily includes a SQL server that stores data from various services and components deployed for the corporate portal. As you know, the performance of working with databases has a significant impact on the work of the corporate portal as a whole. If you do not pay attention to the maintenance of the SharePoint database or do it illiterately, then in practice there are the following obvious problems: a decrease in the productivity of work on the corporate portal, which very often results in a gradual increase in the waiting time for pages, forms, data on web pages, and duplication of actions and their inconsistency in terms of administration.
Very often, the development of maintenance plans causes significant difficulties for portal administrators because of the complexity, complexity and the need to possess competencies not only in administering SharePoint, but also in administering SQL Server. Therefore, in the article I performed a review of all key database maintenance operations, gave examples of scripts and practical tips. Using the above descriptions, you will find many recommendations for action. I note that the articles are not exhaustive, because It is not possible to include a description of absolutely all the details and subtleties of servicing the SharePoint databases. In order for you to get more detailed information on a particular topic, there are links to additional publications.
')
Introduction
The whole variety of databases on which SharePoint work depends can be divided into three groups:
- SQL server system databases (master, model, msdb, tempdb).
- SharePoint system databases (Configuration and Content Center Administration database).
- SharePoint Server databases that provide data storage for deployed service applications (for example, metadata management services, user profile services, business data connection services, etc.).
For optimal performance of Microsoft SharePoint 2013/2016 databases, it is crucial that you follow the standard maintenance procedures for all related databases in a timely manner.
The following is a list of recommended SharePoint database maintenance tasks:
- Check the integrity of the database.
- Defragmentation indices.
- Index performance optimization using fill factor.
- Compress SharePoint databases.
- Setting recovery mode for databases;
- Backup system databases;
- Develop maintenance plans.
These database maintenance tasks can be performed using Transact-SQL commands, the user interface, and the maintenance wizard.
General information about the purpose, location requirements, default recovery models, and other information on each of the databases can be found here .
This article focuses on system administrators, database administrators and technical professionals involved in supporting the work of corporate SharePoint portals.
Database Integrity Check
The DBCC CHECKDB command provides a comprehensive check of the logical and physical integrity of all objects in the specified database and includes the following set of checks:
- consistency of the allocation of disk space for the specified database;
- the integrity of all pages and structures that make up the table or indexed view;
- consistency of directories in the specified database operating in network mode;
- the contents of each indexed view in the database;
- consistency between metadata tables, files in system folders and files at the link level when storing varbinary (max) data in the file system using FILESTREAM;
- Service Broker data in the database.
Example script to check the integrity of the SharePoint content database "WSS_Content":
DBCC CHECKDB ('WSS_Content') WITH PHYSICAL_ONLY, ALL_ERRORMSGS GO An example of the results of the execution of the script, indicating the absence of errors:
Examples of results indicating errors and the need to solve a problem for a specific database:
The DBCC CHECKDB command makes extensive use of I / O subsystem, CPU, and memory resources. To reduce the workload on a working system related to consistency checking, it is recommended that you execute the DBCC CHECKDB command for restored backup copies of SharePoint databases on a separate server.
If the DBCC CHECKDB command is executed on a working database, it is recommended that it be performed during non-working hours, limited to checking the integrity of the physical structure of the pages and the record headers and the integrity of the allocation of space in the database.
Defragmentation Indexes
Introduction
Index fragmentation is a state in which the key-determined logical order of pages in an index differs from the physical order in which pages are placed in data files. It may also indicate a low data density on the data file pages, which results in inefficient use of disk space, memory, and I / O resources.
The main reasons for index fragmentation are numerous inserts, updates, or deletes data in tables. Figure 1 and Figure 2 show the difference between the new unfragmented index and the same index after a large number of insert, update and delete operations. The red arrow defines the physical order of the index, and the black arrow defines the logical order of the pages.
Figure 1. Unfragmented index (source: Paul S. Randal)
Figure 2. Fragmented Index (source: Paul S. Randal)
Since the insertion, update, and deletion of data in rows of tables and indexes is uneven, each page will have different levels of completeness (data density) at different points in time. In this case, when processing a query that requires scanning part or all of the table indexes, a high degree of fragmentation will entail an increase in the number of page reads, which, in turn, will prevent parallel scanning of data and lead to a significant decrease in performance.
Index fragmentation can lead to poor performance and inefficient use of space. At the same time, even in databases with a low intensity of use, the degree of index fragmentation can grow at a rapid rate.
Reorganization and rebuilding indexes
To reduce index fragmentation, two methods are supported:
- Reorganize the index, which defragments and compresses clustered and non-clustered indexes in tables and views, which can significantly improve the performance of working with indexes. Reorganization is always performed online, so the base table is accessible to users.
- Rebuilding an index, which results in a complete rebuilding of an index using the same columns, index types, attribute uniqueness, and sort order. Rebuilding improves index scan performance. You can rebuild the index online or offline.
Online index rebuild is supported only in SQL Server Enterprise editions. In cases where the release of SQL Server hosting the database in question, or the index itself, does not support online rebuilding of the index, the above procedures are performed offline. Online rebuild may not be available for an index containing columns with large objects, for example, columns with the data type NVARCHAR (MAX), IMAGE, etc.
During the autonomous rebuild process, the table level is locked, as a result of which they become unavailable for recording or even for viewing. Many SharePoint database indexes contain columns with large objects and are therefore always rebuilt using a stand-alone process.
It should be noted that in case of operative rebuilding, short-term locking of tables can be performed, as a result of which any operations, except for SELECT, are prohibited. SharePoint 2013/16 databases use clustered indexes in a special way. When rebuilding such an index offline, an exclusive lock is applied to the table, which completely prevents users from accessing it .
The preferred method of defragmenting an index depends on its degree of fragmentation, and also on whether it can remain online or needs to be processed offline. The following table describes the recommended defragmentation methods for indexes with varying degrees of fragmentation for SharePoint databases:
| Degree of fragmentation | Recommended actions |
|---|---|
| From 5 to 10% | Reorganization (online) |
| From 10 to 75% | Rebuild (live mode) with ALTER INDEX REBUILD WITH (ONLINE = ON) |
| More than 75% | Rebuild (offline) with ALTER INDEX REBUILD WITH (ONLINE = OFF) |
Automating index maintenance through system tasks
Health Analyzer Rules
In SharePoint 2013/2016, the service and components monitoring process can be partially automated using the SharePoint Health Analyzer rules ( checking index statistics and checking index fragmentation for search service crawl databases). Their use allows daily assessment of the state, as well as automatically maintain the indices for the following databases:
- Configuration database
- SharePoint content databases, including the Administration Center content database.
- Database user profiles.
- The social tag database of the user profile service application.
- Word Automation Database.
You can view a complete list of system tasks related to health analysis in the SharePoint Administration Center by going to the Tracking \ View Job Definitions section. Examples of system tasks:
Rules that check index fragmentation
- Database indexes used by SharePoint are fragmented. When you run this rule, the following tasks are performed:
- The rule reports fragmented indexes. This is due to the importance of the index state. As a result of the operation of the health analyzer rules, this rule will always report fragmented indexes to bring recovery operations into effect.
- For each SharePoint database, the restore operation looks for, and in case of a successful search, executes the stored procedure's proc_DefragmentIndices stored procedure. During the execution of this stored procedure, a list of all indexes in the database is built. Each index is rated with its current level of fragmentation. For recovery, any indexes that are more than 30 percent fragmented are considered.
- If the SQL Server edition supports rebuilding indexes online, a corresponding attempt is made to rebuild the index for each index. If this fails, then offline index recovery is performed.
- The rule reports fragmented indexes. This is due to the importance of the index state. As a result of the operation of the health analyzer rules, this rule will always report fragmented indexes to bring recovery operations into effect.
- Search - One or more crawl databases may have fragmented indexes. This rule supports SharePoint search service crawl database indexes. By default, this rule is configured to run only on demand. The rule is run from any server in the farm. When you run this rule, the following tasks are performed:
- The rule confirms that the environment is in a state in which defragmentation of indexes is safe.
- For each crawl database configured for search applications in the local farm, the rule runs the proc_MSS_DefragGathererIndexes stored procedure.
- Each crawl database index in the list will be rebuilt. If the release of SQL Server supports rebuilding indexes online, then the corresponding index rebuild is performed. If rebuilding indexes in this mode fails, the index will be rebuilt offline.
- The rule confirms that the environment is in a state in which defragmentation of indexes is safe.
Running Index Defragment Stored Procedures
Many SharePoint databases include stored procedures used by the health analyzer to defrag indexes. Although these rules automatically perform defragmentation, it is possible that you will need to start defragmentation manually. For example, this may require your SLA or some corporate regulations.
You can schedule these stored procedures to run daily, weekly, or monthly depending on your needs and the general indicators of change in your environment.
It is recommended that you allow SharePoint to manage its own database indexes. If you need to manually perform this action, at a minimum it is recommended to set up a daily maintenance schedule so that the proc_DefragmentIndice s procedure and the weekly proc_MSS_DefragSearchIndexes procedure are performed .
Monitoring index fragmentation using SQL Server
Before proceeding with the implementation of a maintenance plan for a fragmented index, it is necessary to determine the most fragmented tables and indices, and only after that develop a plan for their rebuilding and reorganization .
For example, in SharePoint 2013/2016, the AllDocs table, which contains document libraries, associated documents, lists and list items, and the corresponding metadata are highly fragmented. Despite the availability of automated system tasks of the health analyzer, their work is not always effective
The degree of index fragmentation is defined as the proportion of index pages, the logical order of which is different from the physical one. To define it in SQL Server 2014 and SQL Server 2016, you should use the sys.dm_db_index_physical_stats dynamic administrative view.
You can use the following script (for example, the SharePoint content database “WSS_Content”):
USE [WSS_Content]; GO SELECT obj.type_desc ,tbl.name as 'TableName' ,inx.name as 'IndexName' ,phys.avg_fragmentation_in_percent ,phys.fragment_count ,phys.avg_fragment_size_in_pages FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'DETAILED') as phys --LIMITED/SAMPLED/DETAILED JOIN sys.objects as obj on obj.object_id=phys.object_id JOIN sys.indexes as inx on inx.object_id=phys.object_id JOIN sys.tables as tbl on tbl.object_id=phys.object_id WHERE avg_fragmentation_in_percent > 75 ORDER BY avg_fragmentation_in_percent DESC; The result set returned by sys.dm_db_index_physical_stats includes the following columns:
| Column | Description |
|---|---|
| avg_fragmentation_in_percent | The percentage of logical fragmentation (unordered pages in the index). |
| fragment_count | The number of fragments (physically consistent leaf pages) in the index. |
| avg_fragment_size_in_pages | The average number of pages in one index fragment. |
Example output of the index fragmentation analysis result:
Reorganize the index for the SharePoint database
Below is an example of a script that enables the reorganization of one of the indexes of the AllDocs table in the SharePoint content database:
USE [WSS_Content] GO ALTER INDEX [AllDocs_Url] ON [dbo].[AllDocs] REORGANIZE WITH ( LOB_COMPACTION = ON ) GO Rebuild index for SharePoint database
An example script to rebuild all indexes on a separate SharePoint database table:
USE [WSS_Content] GO ALTER INDEX ALL ON [dbo].[AllDocs] REBUILD WITH ( FILLFACTOR = 80 , SORT_IN_TEMPDB = ON ,STATISTICS_NORECOMPUTE = ON) GO Sample script to rebuild a separate index
USE [WSS_Content] GO ALTER INDEX [AllDocs_Url] ON [dbo].[AllDocs] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = ON , SORT_IN_TEMPDB = ON , IGNORE_DUP_KEY = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON , FILLFACTOR = 80) GO An example of the result obtained (checking defragmentation of the index after rebuilding:
Index Performance Optimization with Fill Factor
You can use the fill factor to further optimize data storage and index performance. When creating or rebuilding an index, the fill factor (from 1 to 100) determines the percentage of space at the level of each leaf page that can be filled with data. The rest is reserved for further expansion. In most cases, the default fill level 0 set at the server level is optimal (each page can be filled to 100%). However, in SharePoint 2013, you can use a value of 80, which is set at the server level and provides optimal expansion options and minimal fragmentation.
The fill factor (the “fill factor” parameter) is used to fine tune the storage and index performance. When you create or rebuild an index, the fill factor displays the percentage of space occupied by each page in the final level, which allows you to reserve space for free space for future expansion. For example, if you specify a value of 80 for the fill factor, 20 percent of the disk space occupied by it will be reserved on each page of the final level. This disk space will be used to expand the index when new data is added to the base table. Empty space is reserved not at the end of the index, but between the rows of the index.
The fill factor is a percentage from 1 to 100; The default value on the server is 0, which means complete filling of the pages of the final level.
For SharePoint, 80 is best for supporting database growth and reducing index fragmentation.
The following script will allow you to get information from the current default index fill factor settings at the SQL server level:
SELECT name,value,minimum, maximum,value_in_use FROM sys.configurations WHERE name IN ( 'fill factor (%)' ) Example result:
Compress SharePoint Database
Automatic database compression
Care should be taken to ensure that the automatic compression of SharePoint databases is not enabled in any way. Compression can be used to reduce the size of a data file or transaction log, but it is a very coarse, resource-intensive process that causes extensive logical fragmentation of browsing in data files and leads to poor performance. Manual compression of individual data files and log files may be acceptable under special circumstances.
Automatic compression is especially harmful because it runs every 30 minutes in the background and tries to compress databases for which the automatic compression option is set. This process is not entirely predictable in that it only compresses databases with more than 25% free space. Automatic compression uses a lot of resources and causes degradation of performance, so that it is undesirable under any circumstances. It should always be turned off.
Recommendations
Database compression can be performed only after deleting a large amount of data, provided that the resulting free space is not expected to be used. It is strongly recommended to remember a few important rules:
- do not enable automatic database compression;
- Do not include database compression in maintenance plans or scheduled tasks;
- compress only those databases for which more than 50% of the content has been deleted;
- compress only the SharePoint content databases;
- perform compression only during non-working hours due to the high load on the basic server subsystems during this operation;
- reorganize or rebuild indexes after compression (depending on the degree of fragmentation);
- The use of the TRUNCATEONLY and EMPTYFILE parameters is not supported by SharePoint databases.
Use the DBCC SHRINKDATABASE command to compress data and log files, for example:
USE [WSS_Content] GO DBCC SHRINKDATABASE(N'WSS_Content') GO To compress individual files, use the DBCC SHRINKFILE command, for example:
USE [WSS_Content] GO DBCC SHRINKFILE (N'WSS_Content', 0) GO Configuring SharePoint Database Recovery Modes
In practice, the default recovery models for databases on which SharePoint relies are not always optimal and lead to an increase in transaction log files and inefficient use of disk space, additional backup costs.
The table below summarizes the recommendations for typical configuration of recovery modes for each of the SharePoint databases.
Table 1. Typical recovery mode settings for SharePoint databases
| Database | |
|---|---|
| Plain | |
| Plain. As a rule, tuning is performed once or very rarely. It is enough to perform a backup after making changes. | |
| Plain | |
| Plain | |
| Central Administration Content Database | Plain. |
| Plain. Changes are usually actively made at the stage of initial or spot configuration of services and components of the SharePoint farm, which has clear short time boundaries. It is more efficient to perform a backup after making completed blocks of changes. | |
| Application Management Service Database | Plain. Typically, in the SharePoint farm is set from 0 to a small number of applications (1-5) SharePoint. Application installations are usually significantly separated in time. More efficiently perform a full backup of the database after installing a separate application |
| Subscription Settings Service Database | Plain. As a rule, SharePoint applications are installed on a small number of sites (1-5). Application installations are usually significantly separated in time. It is more efficient to perform a full backup of the database after installing a separate application. Full. If you intend to install applications on an unlimited number of web sites, installations are often performed |
| Business Connectivity Service | Plain. Setting up and making changes related to the operation of this service is usually short in time and is performed once or a small number of times. It is more efficient to perform a full database backup after the completed blocks for creating / editing data models, external content types and external data lists |
| Managed Metadata service application database | Plain in the case of using directories with rarely changeable content. Full access in case of making changes to the term sets on a regular basis (active creation and editing of hierarchical reference books, tagging of list items and documents). |
| SharePoint Translation Service Application Database | Plain. |
| Full access | |
| PerformancePoint Services Database | Plain in the case of working with a set of indicator panels, in whose settings changes are rarely made. Full access in case of intensive creation / modification of indicator panels |
| Search Administration Database | Plain. Setting up and making changes related to the operation of this service is usually short in time and is performed once or a small number of times. It is more efficient to perform a full backup of the database after the completed setting blocks, changes to the search pattern |
| Simple, if the analysis of search results and intelligent adaptation of the issuance of results, search suggestions is not critical. Full access if the search on the portal and its constant personalized work are critical for business | |
| Plain. Restoring this database from a backup or using a recovery wizard will often be much longer compared to resetting the index and completely crawling the content. | |
| Plain, , . , | |
| Plain. , - . . | |
| - | Plain. , InfoPath, - Visio . InfoPath Visio- -. |
| . . | |
| , - , , . | |
| Simple if recovery time takes less time compared to reconfiguring connections and performing full profile synchronization Full access if the situation is reversed | |
| Simple, if working with social content is not a business critical functionality Full access, if the situation is reversed | |
| Full access |
These recommendations can serve as a guideline, but in order to make a decision on the adjustment of the recovery mode of each individual database, we strongly recommend that you at least analyze:
- The purpose, nature of the stored information, the frequency of data changes, the categories of users and the degree of importance of the data for each of them.
- . , - , ( ), - . , (, .), .
- . .
- . SharePoint ( , -, ) . , - , . , , . : SharePoint , SQL Server .
- In some cases, you can consider the complete exclusion of backup databases of a separate service application. In practice, this is often a search service, if it is allowed to be idle in its work, comparable to the time of full re-creation, configuration and crawling of content.
- Technologies that are planned to be used to provide fault tolerance requirements (if any). You may have to immediately exclude from the simple recovery mode.
Database Backup
To perform a manual backup of the database on the SQL server (for example, the system model database), you must perform the following steps:
- Microsoft SQL Server Management Studio , , , > .
- .
- .
- , .
Detailed recommendations for backup and recovery in SharePoint 2013 can be found here .
Developing service plans
Using SQL Server maintenance plans, you can automate and schedule important tasks to protect your data. Using maintenance plans in SQL Server, an administrator can plan various operations, including checking database consistency, reorganizing, and rebuilding indexes.
To create a SQL Server 2014/2016 database maintenance plan, follow these steps:
- Click the Start button on the taskbar and select All Programs> SQL Server Management Studio .
- SQL Server .
- , .
- , .
- .
- , .
- , .
- , .
10 200 , . .
, , . - , . .
- , .
- « » , , .
:- , , .
- .
- , . , , .
- , .
, , . - « ». . , .
- « » , , , . SharePoint.
- « » , , , .
- , « » .
- , 80 , .
.
- « » .
.
- , .
Further
Source: https://habr.com/ru/post/323046/
All Articles