While deciding the direct execution of SQL queries on SharePoint databases, you should consider the following questions and plan accordingly-
What can be the possible repercussions, if we execute direct SQL queries on the content database?
- Reading from the SharePoint databases programmatically or manually can cause an unexpected locking within Microsoft SQL Server, which can adversely affect the performance.
- Any read operations against the SharePoint databases that originate from the queries, scripts, .DLL files (and so on), which are not provided by 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 the unsupported read activities must stop.
What are unsupported operations on SharePoint Content Databases?
It is clearly unsupported to update, delete or insert the records. The risks are surely far more obvious. Any database changes would definitely break the support, as stated by Microsoft. Examples of such database changes include, but are not limited to the following:
- Adding database triggers.
- Adding the new indexes or changing the existing indexes within the tables.
- Adding, changing, or deleting any primary or foreign key relationships.
- Changing or deleting the existing stored procedures.
- Calling the existing stored procedures directly, except as described in the SharePoint Protocols documentation.
- Adding the new stored procedures.
- Adding, changing or deleting any data in any table of any of the databases for the products.
- Adding, changing or deleting any columns in any table of any of the databases for the products
- Making any modification to the database schema.
- Adding the tables to any of the databases for the products.
- 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.
What are the supported operations on SharePoint Databases?
- Operations that are initiated from the SharePoint administrative user interface.
- SharePoint specific tools and utilities, which are provided directly by Microsoft (for example, Ststadm.exe).
- Changes, which are made programmatically through SharePoint Object Model and are in compliance with the SharePoint SDK documentation.
What happens if unsupported data modification is discovered?
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, which does not include the database modifications.
- Roll back all the database modifications
What 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 or not.
What can be done if the content database still needs to be queried directly for some reason?
Never run the direct SQL queries on the content database in Production environment.
Follow the steps
- Restore the database backup from Production to Development environment.
- Take Database Offline.
- Run SQL queries with [NOLOCK] option.
Before running the steps, mentioned above, make sure the database is not in intermediate stage [nothing is checked out], else the user can get different document count than actual.
Key Takeaways
Based on the facts exposed by Microsoft Documentation on direct query execution on the content databases, the key takeaways are mentioned below.
- This is completely unsupported by the EULA, you agreed to; when you installed SharePoint.
- Your queries are not guaranteed to work after applying any patches or Service packs to SharePoint since Microsoft can change the database schema anytime.
- Directly querying the database can place extra load on a Server and hence performance issues.
- Direct SELECT statements against the database take shared read locks at the default transaction level, so your custom queries might cause the deadlocks and hence stability issues.
- Your custom queries might lead to incorrect data being retrieved.