Was immer mal wieder gefragt wird ist, was man denn genau für SharePoint Datenbanken für Maintenance Tasks machen muss und welche Operationen auf SharePoint Datenbanken erlaubt sind und welche nicht.
Ich habe im Folgenden mal alles, was eigentlich auch im Technet bekannt aber verstreut oder unübersichtlich ist, zusammengefasst. Zusätzlich gibt es einen SQL Best Practice Skript, der die benötigten Aktionen automaisch erstellt. Viel Spass beim lesen
So long, Samuel
Inhalt
1 Database Maintenance Plan for SharePoint. 2
1.1 Databases in SharePoint 2013. 2
1.3 Supported and Unsupported Chagnes on SharePoint DBs. 5
1.3.1 Unsupported Database Changes. 5
1.3.2 Supported database modifications. 6
1.3.3 Read operations addendum.. 7
1 Database Maintenance Plan for SharePoint
1.1 Databases in SharePoint 2013
Source: http://technet.microsoft.com/en-us/library/cc678868(v=office.15).aspx
Database | Recommended Recovery Model | Backup Method | Size | Characteristics | Notes |
Config | Simple | SP BackkupSQL Backup | Small | Read intensisve |
|
Central Administration Content | Full | SP BackupSQL Backup | Small | Varies |
|
Content Databases | Full | SP BackupSQL Backup | Small to Big | Varies by Usage |
|
App Management | Full | SP BackupSQL Backup | Small | Write heavy |
|
Business data Connectivity | Full | SP BackupSQL Backup | Small | Read heavy |
|
Search – Admin | Simple | SP BackupSQL Backup | Medium | Read Write |
|
Search – Analytics | Simple | SP BackupSQL Backup | Medium to Large | Write heavy |
|
Search – Crawl | Simple | SP BackupSQL Backup | Medium | Read heavy |
|
Search – Link | Simple | SP BackupSQL Backup | Medium to Large | Write heavy |
|
Secure Store | Full | SP BackupSQL Backup | Small | Equal Read Write |
|
Usage | Simple | SQL Backup | XLarge | Write heavy |
|
Subscription Settings | Full | SP BackupSQL Backup | Small | Read heavy |
|
User Profile – Profile | Simple | SP BackkupSQL Backup | Medium to Large | Read heavy |
|
User Profile – Sync | Simple | SP BackupSQL Backup | Medium to Large | Equal Read Write |
|
User Profile – Social | Simple | SP BackupSQL Backup | Small to XLarge | Read heavy |
|
Word Automation | Full | SP BackupSQL Backup | Small | Read heavy |
|
Managed Metadata | Full | SP BackupSQL Backup | Medium | Read heavy |
|
Machine Translation | Full | SP BackupSQL Backup | Small | Read heavy |
|
Project Server | Full | SP BackupSQL Backup | Small to Medium | Read heavy |
|
PowerPivot | Full | SP BackupSQL Backup | Small | Read heavy |
|
Performance Point | Full | SP BackupSQL Backup | Small | Read heavy |
|
State Service | Full | SP BackupSQL Backup | Medium to Large | Read heavy |
|
Master | Simple | SQL Backup | Small | Varies | |
Model | Full | SQL Backup | Small | Varies | |
Msdb | Simple | SQL Backup | Small | Varies | |
Tempdb | Simple | SQL Backup | Medium | Varies |
|
Report Server – Catalog | Full | SQL Backup | Small | Read heavy |
|
Report Server – Temp | Full | SQL Backup | Small to XLarge | Read heavy |
|
Report server – Alerting | Full | SQL Backup | Small to XLarge | Equal Read Write heavy |
|
1.2 Database Maintenance
Source: http://technet.microsoft.com/en-us/library/cc262731(v=office.14).aspx
These advices were published for SharePoint 2010 but mainly still apply for SharePoint 2013. Only the Timer Jobs might have changed that do automatic Index Maintenance. For all Databases that are used for SharePoint 2013 Content do regularly Database Maintenance.
Action | Databases | Frequency | Notes | ||||||||
DBCC CHECKDB | All SharePoint DBs | Weekly of before each Full Backup |
|
||||||||
Index Rebuilding | All SharePoint DBs | Depends on:- If Database has a Timer Job
– Fragmentation Level
Fragmentation methods
|
|
||||||||
AutoShrink | All SharePoint DBs | Never |
|
||||||||
Maintenance Cleanup | All SharePoint DBs | Weekly or with scheduled maintenance plans |
1.3 Supported and Unsupported Chagnes on SharePoint DBs
The Microsoft Office server products store data in Microsoft SQL Server databases. These products use various stored procedures for regular processing. Therefore, the Microsoft SQL Server databases are important to the successful operation of these products.
SharePoint Products were tested by using a database structure as designed by the SharePoint Development Team and were approved for release based on that structure. Microsoft cannot reliably predict the effect to the operation of these products when parties other than the Microsoft SharePoint Development Team or Microsoft SharePoint Support agents make changes to the database schema, modify its data, or execute ad hoc queries against the SharePoint databases. Exceptions are described in the „Supported Database Modifications“ section.
1.3.1 Unsupported Database Changes
Examples of unsupported database changes include, but are not limited to, the following:
- Adding database triggers
- Adding new indexes or changing existing indexes within tables
- Adding, changing, or deleting any primary or foreign key relationships
- Changing or deleting existing stored procedures
- Calling existing stored procedures directly, except as described in the SharePoint Protocols documentation
- Adding new stored procedures
- Adding, changing, or deleting any data in any table of any of the databases for SharePoint
- Adding, changing, or deleting any columns in any table of any of the databases for SharePoint
- Making any modification to the database schema
- Adding tables to any of the databases for SharePoint
- Changing the database collation
- Running DBCC_CHECKDB WITH REPAIR_ALLOW_DATA_LOSS (However, running DBCC_CHECKDB WITH REPAIR_FAST and REPAIR_REBUILD is supported, as these commands only update the indexes of the associated database.)
- Enabling SQL Server change data capture (CDC)
- Enabling SQL Server transactional replication
- Enabling SQL Server merge replication
If an unsupported database modification is discovered during a support call, the customer must perform one of the following procedures at a minimum:
- Perform a database restoration from the last known good backup that did not include the database modifications
- Roll back all the database modifications
If a previous version of the database that does not include the unsupported modifications is unavailable, or if the customer cannot roll back the database modifications, the customer must recover the data manually. The database must be restored to an unmodified state before Microsoft SharePoint Support can provide any data migration assistance.
If it is determined that a database change is necessary, a support case should be opened to determine whether a product defect exists and should be addressed.
1.3.2 Supported database modifications
Exceptions to the prohibition against database modifications are made for specific usage scenarios:
- Operations that are initiated from the SharePoint administrative user interface
- SharePoint specific tools and utilities that are provided directly by Microsoft (for example, Ststadm.exe)
- Changes that are made programmatically through the SharePoint Object Model and that are in compliance with the SharePoint SDK documentation
- Activities that are in compliance with the SharePoint Protocols documentation
Additionally, in rare circumstances during a support incident, Microsoft SharePoint Support agents may give customers scripts that modify the databases that are used by SharePoint. In these cases, all modifications are reviewed by the SharePoint Development Team to ensure that the operations being performed will not result in an unstable or unsupported database state. Database changes that are made with the guidance of a Microsoft SharePoint Support agent during the course of a support incident will not result in an unsupported database state. Customers may not reapply the scripts or changes provided by Microsoft SharePoint Support outside of a support incident.
1.3.3 Read operations addendum
Reading from the SharePoint databases programmatically, or manually, can cause unexpected locking within Microsoft SQL Server which can adversely affect performance. Any read operations against the SharePoint databases that originate from queries, scripts, .dll files (and so on) that are not provided by the Microsoft SharePoint Development Team or by Microsoft SharePoint Support will be considered unsupported if they are identified as a barrier to the resolution of a Microsoft support engagement.
If unsupported read operations are identified as a barrier to the resolution of support engagement, the database will be considered to be in an unsupported state. To return the database to a supported state, all unsupported read activities must stop.
2 SQL Maintenance Script
Source: http://ola.hallengren.com/scripts/MaintenanceSolution.sql
The SQL Server Maintenance Solution comprises scripts for running backups, integrity checks, and index and statistics maintenance on all editions of Microsoft SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, and SQL Server 2014. The solution is based on stored procedures, the sqlcmd utility, and SQL Server Agent jobs. I designed the solution for the most mission-critical environments, and it is used in many organizations around the world. Numerous SQL Server community experts recommend the SQL Server Maintenance Solution, which has been a Gold winner in the 2013, 2012, 2011, and 2010 SQL Server Magazine Awards. The SQL Server Maintenance Solution is free.