In our October 2018 edition of Abel Solutions’ Tip of the Month, we learned that there are several methods to connect external data sources in SharePoint including third party integration tools. In this edition, we will explore one of those integration tools – Layer2.
Layer2 offers several products designed to make integrating data in SharePoint much faster and easier. One solution we at Abel Solutions have used very effectively in helping our clients meet their SharePoint integration needs is Layer2 Cloud Connector.
Business Use Case
Cloud Connector can be used for data migration, data backup and one- or two-way data syncing between SharePoint and over 100 systems and applications. As an example, let’s look at a business use case where data housed in a line of business application built on an on-premises database (e.g., SQL, Access or other), which must be synchronized to a list in SharePoint Online using a no-code solution. Further, any changes to the data must be reflected in the corresponding SharePoint list within 30 minutes due to other business processes that rely upon this information.
In this example, we utilize Layer2 Cloud Connector to synchronize the data into SharePoint by mapping specific fields from the database to columns in the SharePoint list. We implement scheduled syncs to run every 30 minutes to ensure that changes to the data in the database are reflected in SharePoint.
Install Cloud Connector
Layer2 Cloud Connector is typically installed on a dedicated Windows computer in the on-premises environment where the data source is located. Cloud Connector can be installed on several versions of Windows server and Windows desktop. For our example, Cloud Connector is installed on a virtual machine running Windows Server 2016 Standard 64-bit with one quad-core processor and 16GB RAM. For larger workloads the system can be scaled up as needed. There are several dependencies that need to be installed including Microsoft .NET Framework 4.5.2 and 3.5 along with Microsoft Management Console 3.0. Installing Cloud Connector is a very straightforward process.
Create and Configure New Connection
Use the Connection Manager console to create and configure a new connection (see Figure 1 below). Before configuring a new connection, you will need to determine which fields will be synced from the on-premises database to the SharePoint list. Work with your business users to identify the required fields/columns.
Figure 1. Create and configure new connection
Configure Data Source and Destination
Next, configure the connection string necessary to connect the data source (see Figure 2) along with the destination SharePoint list (see Figure 3).
Figure 2. Configure Data Source
Figure 3. Configure Destination
Configure Field Mappings
After the required fields/columns have been identified, map the source data fields to the corresponding SharePoint list columns (see Figure 4).
Figure 4. Configure Mappings
Run the Sync
Next, run the synchronization to ensure that it runs without errors (see Figure 5).
Figure 5. Run the Sync
Schedule the Sync
Finally, configure the syncs to run at the desired interval – every 30 minutes in this example (see Figure 6).
Figure 6. Schedule the Sync
You can now check your destination SharePoint list to verify that the data in the corresponding columns is getting updated as expected.
Figure 7. Verify Result
Here we have seen how data can be synchronized from an on-premises database to a SharePoint Online list every 30 minutes using no code. With the ability to sync with over 100 systems and applications, you are sure to find many opportunities where Layer2 Cloud Connector can meet the business requirements of your organization and simplify how data is shared across applications.
This tip written by Abel Solutions Senior SharePoint Consultant John Roper.