Why You Shouldn’t Use SharePoint Online as a Database
SharePoint is a document management system for storing files, organizing documents, sharing and editing documents collaboration with others. Some users, who may have used MS Excel or MS Access in the past, may think that it’s okay to use SharePoint as a database. However, whatever the advantages of this approach, you should avoid using SharePoint as a database.
Before going into the reasons for discouraging this kind of use, here are some factors that may seem attractive for users to consider using SharePoint as a database:
- You don’t need dba privileges to provision a new schema
- An extensible interface ready for use for data management
- You don’t need to coordinate SharePoint apps with data stored in other places
- Migrating data without the need to sync
Data Organization
There is no database-like data organization in SharePoint and this is an important reason not to use SharePoint as a database. At first glance, SharePoint lists look similar to database tables with all the columns and different data types. However, data is organized differently in these structures. Does SharePoint use SQL Server? SharePoint uses MS SQL Server as a database management system for its operation but SharePoint is not designed to perform the functions of a real database. What is a SharePoint content database? A content database is a database to store SharePoint data for one or more site collections. This is the database located on the MS SQL Server used for the operation of SharePoint.
When compared to relational databases, SharePoint lacks relationships between tables. Here are the data relationships that are missing in SharePoint:
One to One. A record in one table is related to a record in another table of a database. For example, one department can have only one manager, and one manager can manage only one department.
One to Many. A record in one table is related to multiple records in another table. For example, one customer may have many sales orders.
Many to many. Records in two or more tables are related to many records in another table. For example, customers can purchase multiple products, and products can be purchased by multiple customers.
Missing Database Features
Database features are missing (or practically missing) in SharePoint Online and SharePoint on-premises. Let’s look briefly at these missing features from SharePoint.
Primary keys and foreign keys
A database uses unique identifiers, which include primary keys and foreign keys, to identify a record and link tables that have parent-child relationships. A primary key uniquely identifies a record in the table; a table can have only one primary key. A primary key consists of one or more columns and cannot have NULL values. A foreign key is a column or a set of columns that refers to a primary key in another table and links data between tables. There are no relationships between SharePoint lists, and these lists cannot be linked the same way as tables in a database.
SQL language
SQL (Structured Query Language) is a standard language that allows you to write code for complex queries in databases. When using a real database, you can save queries and re-purpose them. This functionality is missing in SharePoint. SharePoint supports only CAML, Odata and search queries. You shouldn’t query a SharePoint database (the MS SQL database used by SharePoint to store SharePoint content) directly by using SQL because it may cause stability issues and violate the EULA (end user license agreement).
Stored procedures
Stored procedures are part of SQL language. A stored procedure is a group of SQL statements, that is, a reusable code block that is created and stored in the database. Stored procedures are supported by most database management systems (DBMS). Stored procedures provide a list of benefits. You can centralize data access logic in a single place with the ability to optimize. As for security, users don’t need read/write permissions on underlying tables if execute rights to a stored procedure are granted. You cannot use stored procedures when using SharePoint.
Transactions
A transaction is a unit of work that represents any change in a database and that can be considered as “a whole”. An example of a transaction is when you withdraw money from one bank account (source) and transfer money to another bank account (destination). These two actions are elements of one transaction. If the first action is succeeded but the second action is failed, money is sent back to the source bank account to maintain the integrity of a transaction. This is transaction rollback. Similarly, changes are rolled back in a database when a transaction fails, and data is not lost. SharePoint doesn’t support transaction rollback.
Indexing and query optimization
Indexing and query optimization are used to improve database performance. Fields that are frequently used can be pre-indexed, and overall performance is improved. Databases can be optimized for data retrieval by using optimization techniques such as creating pointers to where data is stored in the database. This approach is much faster than searching the entire database. Indexing in SharePoint is present to improve search of content stored in SharePoint; however, functionality is limited comparing to a database that you can use directly. In particular, SharePoint is limited to 20 indexed columns in a list.
Large items and binary data
Most modern databases can store large data and binary data (such as video files, images, audio files, etc.) directly in the database. Binary data can be stored as files attachments in SharePoint lists, but this is not recommended. In terms of functionality, binary attachments in SharePoint are from having the same performance as binary fields in a relational database. If you store too many items with binary attachments in SharePoint lists, performance can degrade significantly. SharePoint doesn’t create a new database table for each list and library in the SharePoint content database. All data you see in SharePoint lists and libraries is stored in one single table of the content DB.
SharePoint cannot handle many items the same way a database can. You should limit the number of items in a SharePoint list to 2000 (in a root list and contained folders in the list). If you need to upload files in SharePoint, upload them to a SharePoint library, and don’t use SharePoint lists for this purpose.
Access outside of SharePoint
You can access SharePoint lists outside SharePoint only by using programming interfaces or XML. You should not use lists to store data in a SharePoint repository that must be accessed by external applications because of poor integration. In contrast, relational databases have all the needed functionality for integration and working with external applications.
Conclusion
There are many reasons not to use SharePoint as a database. SharePoint is intended for other purposes. Using SharePoint as a database may cause significant disadvantages and performance degradation. You cannot substitute a relational database with SharePoint. Lists are not called tables in SharePoint for a reason. The main purpose of SharePoint is storing documents centrally and editing them together. Use the right tool for each of your business needs.
Whatever you use, whether MS SQL, Oracle or SharePoint, don’t forget to back up your data. NAKIVO Backup & Replication is a universal data protection solution that supports Oracle backup, MS SQL backup, Office 365 backup (including SharePoint Online backup), and SharePoint Server backup.
