Creating a SharePoint DataLake with SQL Server using Enzo Unified

In this blog post I will show how you can easily copy a SharePoint list to a SQL Server table, and keep the data updated one a specific frequency, allowing you to easily create a DataLake for your SharePoint lists. This will work with SharePoint 2013 and higher, and with SharePoint Online. While you can spend a large amount of time learning the SharePoint APIs and its many subtleties, it is far more efficient to configure simple replication jobs that will work under most scenarios.

The information provided in this post will help you get started in setting a replication of SharePoint lists to a SQL Server database, so that you can query the local SQL Server database from Excel, Reporting tools, or even directly to the database. You should also note that Enzo Unified provides direct real-time access to SharePoint Lists through native SQL commands so you can view, manage and update SharePoint List Items.

image

 

Installing Enzo Unified

To try the steps provided in this lab, you will need the latest version of Enzo Unified (1.7 or higher) provided here:  http://www.enzounified.com/download. The download page also contains installation instructions.

Enzo Unified Configuration

Once Enzo Unified has been installed, start Enzo Manager (located in the Enzo Manager directory where Enzo was installed). Click on File –> Connect and enter the local Enzo connection information. 

NOTE:  Enzo Unified is a Windows Service that looks like SQL Server; you must connect Enzo Manager to Enzo Unified which by default is running on port 9550. The password should be the one you specified during the installation steps. The following screen shows typical connection settings against Enzo Unified:

image

Create Connection Strings

Next, you will need to create “Central Connection Strings” so that Enzo will know how to connect to the source system (SharePoint) and the destination database (SQL Server). You manage connection strings from the Configuration –> Manage Connection Strings menu. In the screen below, you can see that a few connection strings have been created. The first one is actually a connection string to Enzo Unified, which we will need later.

image

The next step is to configure the SharePoint adapter by specifying the credentials used by Enzo Unified. Configuring the SharePoint adapter is trivial: three parameters are needed: a SharePoint login name, the password for the login, and the URL to your SharePoint site. You should make sure the login has enough rights to access SharePoint lists and access SharePoint Fields.

image

Once the configuration to the SharePoint site is complete, you can execute commands against Enzo Unified using SQL Server Management Studio.

Fetch records from SharePoint using SQL Server Management Studio

To try the above configuration, open SQL Server Management Studio, and connect to Enzo Unified (not SQL Server). From the same machine where Enzo is running, a typical connection screen looks like this:

image

Once you are connected to Enzo Unified, and assuming your SharePoint site has a list called Enzo Test, you can run simple SQL commands like this:

SELECT * FROM SharePoint.[list@Enzo Test]

Create a Virtual Table

You will also need to create a Virtual Table in Enzo so that the SharePoint list looks like a table in Enzo Unified. A Virtual Table is made of columns that match the SharePoint list you want to replicate. To do this, open Enzo Manager, select the SharePoint adapter, and create a new Virtual Table by clicking on the NEW icon; provide a name for the Virtual Table, and select the columns to create through a picker. In the example below, I am creating a Virtual Table called vEnzoTest, which mirrors a SharePoint List called ‘Enzo Test’.

image

The picker allows you to execute the SQL command to validate it is working. Clicking OK will automatically add all the requested columns to the Virtual Table.

Make sure to pick the ID and Modified columns; this will be required later.

image

Once completed, I can run commands like this against the SharePoint adapter using SQL Server Management Studio:

SELECT * FROM SharePoint.vEnzoTest

SELECT * FROM SharePoint.vEnzoTest WHERE Title ID > 100

The difference with the previous SQL command is that the virtual table will only return the columns specified by the Virtual Table.

Configure Data Sync Jobs

Once the Virtual Table has been created, you can add new Jobs to copy the SharePoint data into SQL Server, and keep updates synchronized with the SQL Server table. A simple configuration screen allows you to setup the data sync jobs. You can choose which operations to replicate, the destination table, and a schedule for data updates. In the example below I am setting up a 3 data sync jobs of the vPosts Virtual Table: initialization, insert/update, and delete, updated every 5 minutes.

image

You can also use Enzo Manager to monitor the data sync jobs, or run them manually.

Once the jobs have been created, you can simply connect to the SQL Server database (not Enzo Unified) and see the replicated data. For example, you can connect to SQL Server, and run the following statement assuming the above destination table (as shown in the screenshot) has been created.

SELECT * FROM DataLake.SharePoint.vPosts

Conclusion

This post shows you how to easily configure Enzo Unified to replicate SharePoint lists to a local SQL Server database to enable reporting and other data integration projects such as a Data Lake.

About Herve Roggero

Herve Roggero, Microsoft Azure MVP, @hroggero, is the founder of Enzo Unified (http://www.enzounified.com/). Herve's experience includes software development, architecture, database administration and senior management with both global corporations and startup companies. Herve holds multiple certifications, including an MCDBA, MCSE, MCSD. He also holds a Master's degree in Business Administration from Indiana University. Herve is the co-author of "PRO SQL Azure" and “PRO SQL Server 2012 Practices” from Apress, a PluralSight author, and runs the Azure Florida Association.

Print | posted @ Monday, June 19, 2017 4:16 PM

Comments on this entry:

No comments posted yet.

Post A Comment
Title:
Name:
Email:
Comment:
Verification: