Originally posted on:
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:
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.
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.
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:
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’.
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.
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.
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
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.