Storing data to a separate SQL database instead of content database

Question

Our team is planning to move away from storing to SharePoint content database and save the data instead to an own designed SQL database. The goal is to use SharePoint just as an interface.

My task is to come with a "Proof of Concept" to actually see the upsides and downsides of this change.

Any possible guidelines, advices or work experience would be much apriciated as I have very little knowledge on where/how to start.

Answer 1

No, it is not possible. The database behind SharePoint data must be a content database, we can move the SharePoint data from one content database to another content database but not to normal SQL database.

While you will try to mount the non-content database, you will see database not found errors.

What is SharePoint content database?

A content database is a database file that stores content for one or more site collections for SharePoint web application. The content can be pages,files,documents,images and much more. So if the Site Collection has more number of SharePoint sites, the content database size grows rapidly.

Answer 2

you cant do it without using the BCS or Custom solution. SharePoint store the data inside the content database only.

You can use the BCS service to achieve your goal:

What is Business Connectivity Services?

With Business Connectivity Services, you can use SharePoint Server and Office clients as interfaces to data that doesn't live in SharePoint Server itself. Business Connectivity Services can connect to data that is available through a database, a web service, or data that is published as an OData source or many other types of external data. Business Connectivity Services does this through out-of-the box or custom connectors. The connectors, as the name implies, are the communication bridge between SharePoint Server and the external system that hosts the external data.

Or you can write your own solution / App for SharePoint to get it done.

Answer 3

By default, SharePoint save the data into content database. You could use external list with BCS to store the data into a separate SQL database.

1.Use SharePoint desginer to Create External Content Type for sql server:

https://www.c-sharpcorner.com/UploadFile/anavijai/create-external-content-type-using-sharepoint-designer-2013/

https://docs.microsoft.com/en-us/sharepoint/dev/general-development/how-to-create-external-content-types-for-sql-server-in-sharepoint

2.Creare an external list with the external Content type:

https://support.office.com/en-us/article/create-an-external-list-6e2d601d-b02f-41e7-ba87-e70297ec6665