Should I synchronise SharePoint lists with SQL server database?

SharePoint lists and Microsoft lists are a wonderful way to manage and display information, for example in your intranet or within Microsoft Teams. Organisations that are keen to ensure there is one source of truth for their data and information can use lists together with Power Automate to help maintain the integrity of their data, so that when there is a change to one piece of data this is also reflected elsewhere.

Some organisations that use SQL Server to store and query their data, and also use SharePoint for communication and their intranet, are often keen to ensure that the data that is stored in SQL can also be surfaced in SharePoint lists.

A question that we occasionally get asked is what the best way to synchronise a SharePoint lists with SQL Server database, so information is updated in both systems. Other related questions include:

• How do I import or export data from SQL Server to a SharePoint list?
• Can I connect a SQL server to a SharePoint list?

While synchronisation and data import / exports are technically possible, these approaches have several disadvantages, and is a practice we do not recommend to our clients. In this post we’re going to explore the topic in more detail and suggest why there are better approaches.

What are SharePoint lists?

A SharePoint list (or Microsoft list) is a format for storing and displaying data and content within SharePoint in a set of rows and columns, similar to a table or a spreadsheet. Lists are a highly flexible way to both manage but also display structured information and content within SharePoint, as well as Teams and other part of Microsoft 365. For more detail on see our SharePoint Lists beginner’s guide.

What is SQL Server database?

SQL Server is a relational database management system that is frequently used by organisations to store, manage and query data used in core systems, reporting and business intelligence. It has been around for many years and is still a popular way of storing data in organisations, with SQL skills commonplace among IT professionals.

Why do organisations want to keep the data in lists and SQL synchronised?

It’s not surprising that organisations want to keep their data synchronised between SSL and SharePoint lists. Having one source of truth ensures that data is consistent wherever it appears, across core systems, dashboards, reports and communication channels like the intranet. Bringing data together from different sources is essential for decision making, management and leadership, as well as compliance, risk management and more.

It is essential that the integrity of data is maintained, and querying data dynamically has value. Commonly, digital workplace teams want to surface data and display information that managers and employees need to refer to, in popular channels like Teams, a SharePoint intranet or in SharePoint communication sites. Lists is a very useful format for visualising and displaying data.

Lists are also used to manage and update information too, and teams want to ensure that any information changed in a list is also updated in SQL too. With two-way synchronisation, teams can ensure there is one source of truth.

What are the challenges of synchronising lists and SQL Server?

If you are planning to go ahead and synchronise SharePoint lists and SQL Server, there are various pitfalls to be wary of.

1. You’re going to need to get IT involved

Synchronising lists and SQL is essentially a custom development and will come at a cost, as well as create technical debt which causes various problems down the line. There are connectors on the market, but these will need to be tested and again come with some risk. However you approach it, you will need to get a development resource involved in the synchronisation. If you haven’t got the expertise in-house, you’ll likely have to go external.

2. It needs ongoing maintenance and monitoring

Connectors and / or custom code will require ongoing maintenance and monitoring to make sure the synchronisation is taking place, and then to make any necessary fixes if a problem arises. Again, this results in ongoing costs and reliance on development resources.

3. You risk the integrity of your data

Perhaps the most significant issue with synchronising SharePoint lists and SQLs is that you are actually setting up two places for data to be stored. If the synchronisation fails, it means that you are risking the integrity of your data with two different versions.

If there is an issue and doesn’t get noticed straight away, it might take a lot of unpicking and effort to restore all the data to its correct state, There may also be potential consequence if employees are relying on out of date or incorrect data for decision-making and key processes.

4. SQL and lists are not the same thing

SQL is a relational database, but a SharePoint list is not a relational database. This can limit some of the information you can synchronise; if you are trying to synchronise you really do need to know what you’re doing or further risk your data integrity.

What are the alternatives?

In our view synchronising SQL and lists comes with associated risks, costs and ongoing effort. There are better alternatives.

Using Power BI

Sometimes teams want to visualise their data and content using a SharePoint list, because its an easy and flexible format. However, Power BI offers an alternative (and often superior) option for data visualisation and comes with a ready-made connector for SQL. Here, you can then set up your dashboard and report without undermining the “one source of truth” caused by syncing with lists, and also avoid the costs associated with a custom approach.

Using the Dataverse

The Dataverse is Microsoft’s solution for data storage that’s utilised across Microsoft 365 and the Power Platform. It’s an Azure-based relational database for storing data which can then be surfaced, updated and queried across different Microsoft applications, as well as non-Microsoft applications if required. It is bundled in with Power Apps subscriptions.

Using the Dataverse means your SharePoint list can easily utilise the data contained within it in a more effective and cheaper way thanks to seamless integration which means:

• You don’t have actually have to involve developers; this can be carried out by Power Users all within a low code no code interface
• No customisation and associated costs are required with everything covered in your PowerApps subscription
• You don’t need ongoing maintenance and effort
• You avoid the risk of creating conflicting data.

Overall, using the Dataverse rather than trying to sync a SharePoint list and SQL means lower risk, lower costs, less effort and avoids various other pitfalls.

Need advice on lists, SQL and the Dataverse? Get in touch!

Synchronising SharePoint lists or Microsoft lists and SQL Server has various pitfalls, and in our view there are better alternatives.

If you’d like to discuss lists, SQL, the Dataverse or how you manage and store your data across the digital workplace, then get in touch!