/howto/sharepoint/replication

SharePoint Replication

This section describes how you can configure Enzo to replication data from a SharePoint List to a local SQL Server database. In order to setup the replication job you will need to configure the following in Enzo:

Implementing replication for SharePoint requires that both the SharePoint and the Pipeline adapters are loaded in Enzo.

• Create Connection Strings
  Replication depends on a few connection strings that must be setup.
  
  
• Configure the SharePoint Adapter
  The SharePoint adapter must be configured with an account that has enough rights to read data, access the SharePoint List definition, and access the Change Log from SharePoint.
  
  
• Create a Pipeline
  The replication job leverages the Pipeline adapter, which requires creating a new Pipeline, and at least one Listener.
  
  

While it is possible to configure replication entirely using SQL code, the following instructions use the Enzo Manager for simplicity.

Create Connection String

The first step is to create the following Connection Strings (refer to the Connection Strings section for more information):

• Enzo Loopback
  This connection string should point back to Enzo directly. If you left all default options during the installation of Enzo, this would be a connection string pointing to localhost,9550 using the sa account for example.
  
  This connection string will be called enzo-loopback in later screenshots.
  
  
  
• Pipeline Store
  This connection string should point to a local SQL Server database. This database must be created manually using a standard CREATE DATABASE command; it will be used by the Pipeline adapter to store its replication status and log. Make sure to specify an Initial Catalog that points to the database created for this purpose. The database can be completely empty before being used the first time by the Pipeline adapter; tables will be created automatically by Enzo.
  
  This connection string will be called pipelinestore in later screenshots.
  
  
  
• SQL Server Database
  This connection string should point to a SQL Server database that will store the SharePoint data. For this connection string, the Initial Catalog is not necessary as it will be provided during the creation of the Listener later.
  
  This connection string will be called localhost-datalake in later screenshots.
  

SharePoint Configuration

Next you need to configure the SharePoint adapter so that Enzo can access and manage SharePoint lists and other objects.

You will need the following information to configure a connection to SharePoint:

• Name: A name for this configuration setting (ex: default)
• userName: A valid SharePoint user (ex: user1@mycorp.onmicrosoft.com)
• password: The user password
• url: A valid SharePoint site (ex: https://mycorp.sharepoint.com/teamsite/)

You should create this configuration setting for the 'sa' account assuming the enzo-loopback connection string also uses the 'sa' account. You may want to set this as the default setting, although this is not required.

HINT: To verify that the SharePoint connection settings are valid, simply select the VIEWs tab, click New, and expand the SharePoint List Dropdown box; you should see a list of all available SharePoint lists.

Pipeline Configuration

Next you will configure the Pipeline adapter. This is done in two steps: create a new pipeline and add a Listener to the pipeline. Pipelines are an advanced Change Data Capture technology allowing you to capture changes and forward/replay them to one or more destinations. For more information, see the Change Data Capture section.

New Pipeline

Create a new Configuration Setting in the Pipeline adapter, and specify the following settings:

• enzoConnectionString: Select the enzo-loopback connection string created previously
• storeConnectionString: Select the pipelineStore connection string created previously

Once the configuration setting created, click on the Pipelines tab, and click on New. Enter and save the following information:

• Name: Enter a friendly name for this pipeline (ex: SPData)
• Pipeline Mode: Choose Sync & CDC to copy all existing records first, then keep the data in sync
• Cron Schedule: Enter a CRON schedule (ex: 0 0/5 * * * ? for 5 minute intervals)
• Source System: SharePoint - choose the configuration setting next to it (ex: default)
• Source Table: Click on the refresh link then select the SharePoint list you would like to replicate (ex: Companies)

After the Pipeline has been created and saved, it will start automatically. Because both Sync and CDC were selected, the Companies List will first be entirely extracted and saved in the pipelineStore database; this data is reserved for Enzo's internal use and should not be read directly.

New Listener

Now that the Pipeline has been created, you need to create at least one listener to forward the data to its final destination (in this case, a SQL Server database). In the same screen as the Pipeline that was previously created, click on Add a Listener... (towards the bottom half of the screen) and enter the following information:

• Name: A friendly name for the listener (ex: SPDest)
• Destination System: Database
• Destination Configuration: Enter a previously created destination connection string for SQL Server (ex: localhost-datalake)
• Destination Object/Table: : Enter a Schema and Table name (ex: SharePoint.Companies); if no schema is specified, the DBO schema will be used
• Processing Mode: Each (one call per record)
• Check all boxes: Select all three boxes to forward initialization data, all deletes, and all upsert (insert/update)
• Columns List: Leave blank to save all columns
• Max Retries: 0
• Filter: A SQL-like filter to exlude certain records; leave blank to forward all records

Before saving this Listener, you should first click on the View/Execute DDL Statement button. This screen will show you the CREATE TABLE command that Enzo will run to create the destination table automatically. In this window, you should click on Execute Command Now to create the table; it will not be created later and replication will fail if the table is not created.

Once the destination table has been created, click OK to save the Listener; it will start immediately. You can connect to the SQL Server database and run the following command to view the records being replicated (assuming you used the same names for the database, schema, and table name):

SELECT * FROM Datalake.SharePoint.Companies