Applies to: SharePoint Online and On-Premises

Note: This service requires an additional purchase.

Description

Crow Canyon DataSync Service for SharePoint is a Windows Service that synchronizes any ODBC-compliant database table(s) data to corresponding SharePoint list(s) and vice versa. We can use this as one way sync as well based on the use case. This service is installed in the customer’s intranet and is configurable in terms of which records to sync, mapping from database to SharePoint list columns (vice versa) and the sync frequency. It can sync new records by creating new list items in SharePoint/Database and sync existing records by updating list items.

Data Sync Process:

1. Initial Sync
   1. Initial sync is only one-way sync i.e. database to SharePoint
   2. Service will read data from the database and create/update items in SharePoint based on the unique key configured during the setup

2. Incremental Sync
   1. Incremental sync is two-way sync
   2. SharePoint to database sync has the first preference
      • Service will fetch items created/modified in SharePoint since last sync, and will create/update items in database
      • Service will fetch items created/modified in database since last sync, and will add/update SharePoint list items

Note: Service does not delete items in either SharePoint or database.

Sample Use Cases:

One Way Sync:

For example, there are Network inventory tools like SCCM and LAN Sweeper that store computer and other network component information in database tables. This information is required in SharePoint for many applications that use Asset records for request tracking, reporting etc. Using this service, this data can be easily brought into SharePoint and then automatically updated whenever any change is made in corresponding database record by the network inventory tool. Data from more than one table can be brought into the same list and items can also be created in a SharePoint list manually without affecting the items coming from the database(s).

Two Way Sync:

For example, there is a large list in SharePoint and we would like to create Reports on this large list. SharePoint API has list view threshold limitation that it can fetch 5000 items only at a time. Using this service, we can sync any changes from SharePoint to a database table and then use Power BI etc. tools to build required reports.

This article describes the installation and configuration of Crow Canyon DataSync Service.

Pre-requisites

• An SQL Account that has read permissions on the SQL database to connect to SQL Server and execute permissions on the stored procedure that we may create during the configurations so the service can communicate with SQL database and read the required information. This information will be used in the configurations.
  • If we need SharePoint to Database sync, SQL Account require write permissions on the target database table.
• A SharePoint account that has at least add/edit item permissions on the required SharePoint site. This information will be used in the configurations so the service can communicate with the SharePoint site.
• An SQL Admin Account to create Stored Procedure(s) in the SQL database. This account will be used only to create the stored procedure during the installation time and it will not be used in any configurations.
• Database and SharePoint should be accessible from the server where the service is being installed.
• Microsoft .NET Framework 4.7.2 or above should be installed on the server.
• Local Administrator account on the server to install the service.

Installation Package

1. Configuration.xml (SCCM/Lansweeper)
2. StoredProcedure.txt
3. CrowCanyon.DBSyncService.exe
4. InstallUtil.exe
5. Dependency Dlls (Microsoft.SharePoint.Client.dll, Microsoft.SharePoint.Client.Runtime.dll, Microsoft.Identity.Client.dll, microsoft.identitymodel.dll, Microsoft.IdentityModel.Extensions.dll)
6. List Templates (O365/On-Premises)

Installation Steps

There are multiple steps that need to be performed to get the Crow Canyon DataSync Service installed and running.

1. Prepare the install location
2. Prepare the SharePoint Environment
3. Prepare the Stored Procedure
4. Prepare the Configuration file
5. Install Crow Canyon data sync Service

Prepare the install location

• Downloaded package “CrowCanyon.DBSyncService.zip”, right click, properties and unblock the file if it is blocked.
• Open Command Prompt (Run as Administrator) –> navigate to the Package location –> enter the below command to verify MD5 signature for the package.

• In this example, package download link is like “https://…/MD5_44dd766b53664330143b8a49cecd7362/CrowCanyon.DBSyncService.zip” and the command output is matching with GUID in the download link as expected. Please contact sharepointsupport@crowcanyon.com if this GUID is not matching.
• After MD5 signature is validated, right click on zip file and extract the required package files.
• Paste the installation package at proper location as this location will be the installation location and the logs will generated in this location and service assemblies (*.dll) will be present in the same location
• Place all the below files in required folder and the service will be installed in this location. Right click on each file in the package, properties, unblock if the file is blocked.
  • Configuration.xml
  • CrowCanyon.DBSyncService.exe
  • InstallUtil.exe
  • Dependency assemblies

Note:

• Package contains “Configuration_LANSweeper.xml” and “Configuration_SCCM.xml” files, based on the application that is being configured (LANSweeper VS SCCM), rename the corresponding configuration file to configuration.xml.
• If we are using this for custom database sync other than Lansweeper and SCCM, take any configuration file as starting point and update it with required changes.

Prepare the SharePoint Environment

Create two lists in the target SharePoint site using the list templates available in the package.

1. Create a list for Sync Process Log using “CCSSyncLog.stp” List Template from the package. List name should be “CCSSyncLog”.
   • Index “Created” column, update the default view with filter like “Created” is greater than or equal to “[Today]-7”.
2. Create a list for Sync Time using “CCSSyncTime.stp” List Template from the package. List name should be “CCSSyncTime”.
   • Recreate “SyncLog” Lookup column (Lookup List: CCSSyncLog)

Note:

• For SharePoint Online, use the list templates from “O365” folder in the package
• For SharePoint On Premises, use the list templates from “OnPrem” folder in the package

Create the required list schema in the SharePoint Target list to map the columns from the database to SharePoint columns. These will be used at a later stage while defining the field mappings from database to SharePoint list fields.

Prepare the Stored Procedure

Service can be installed without the stored procedure as well, however it is easy and recommended to use the stored procedure.

Steps to create stored procedure

For the standard integration with SCCM, refer the “StoredProcedue_SCCM.txt” file in package.

For the standard integration with Lansweeper, refer the “Lansweeper_StoredProcedure.txt” file in package.

Login to the SQL Server where the required database present with the admin account and create a stored procedure with the query mentioned in one of the above standard stored procedures as required.

For any custom database sync, take any one of the stored procedure as reference and update as per the requirement.

Grant permissions

An SQL Account that has read permissions on the database to connect to SQL Server and execute permissions on the stored procedure is required to be configured in the configuration file described in below section. Create a new SQL authenticated account and grant below permissions.

• After creating the stored procedure, grant Execute and read permissions on it for the SQL authenticated account created above. The same SQL User account will be used in our configuration file.
• If we need SharePoint to Database sync, grant write permissions on the target database table.
• Login to the SQL Server with the SQL user credentials and make sure the user is able to execute the stored procedure.

Prepare the configuration file

Please refer “Crow Canyon Data Sync Configurations description” section below for detailed description about the configuration file.

Install Crow Canyon Data Sync Service

• Open administrator command prompt (Run as administrator)
• Change the location to setup files location
  • Cd <<setup file path>>
• Run below command:
  • InstallUtil.exe -i “CrowCanyon.DBSyncService.exe”

• In prompts to enter the service account, fill the details and save (Local Administrator account)

• After this step, “Crow Canyon Database Sync Service” starts appearing in service in the services.msc

Configure Data sync service Configurations file description

Setup Configuration.xml file.

It is possible to configure sync settings for multiple databases using our Data Sync service. For every database sync we can add one Sync configuration node.

Every Sync Configuration contains mainly below set of nodes

1. SharePoint Information
2. Database Information
3. Mappings
4. Signature Information
5. Sync Time Information
6. Process Information
7. Log Policy

SyncConfig Node

Every database needs to have its own Sync configuration node. Every sync configuration node need to have its own Sync Configuration Id.

• SyncConfigID Attribute
  • Unique ID provided for a sync configuration. For example, CCSSCCM, CCSLanSweeper1, etc.

SPInfo Node

We need to provide SharePoint List connection information in this node.

SPType Attribute

Type of SharePoint environment Possible values : (On Prem, On Prem CSOM , Office 365)

• For on-premises environment, if the database is in the SharePoint server itself then we can use “On Prem” value otherwise use “On Prem CSOM”
• For SharePoint Online environment, use “Office 365”

SiteURL Node

Enter SharePoint site URL in which the target list exists that we need to use for this sync

Domain Node

If we are configuring for On-premises, this node is required otherwise we can leave this node blank

UserName Node

Specify a user name that has “create and update” item permissions on the SharePoint Sync List

Password Node

Enter user password for the user mentioned in UserName Node

Generally password may have special character so we need to use below syntax for password.

<Password IsSecure=”false”><![CDATA[password]]></Password>

App Client Node

This is required only for O365 Version where the tenant uses Modern Authentication. In this case, we need to register an app for the site collection and grant permissions for the Modern app to access the site.

To create Modern app for the site collection, please use below URL and click on ‘Generate’ for Client ID and Client Secret, specify a domain name as needed.

https://<site collection url>/_layouts/15/AppRegNew.aspx

Fill the following details

Title: Crow Canyon DB Service
App Domain: www.contoso.com
Redirect URI: https://www.contoso.com/default.aspx

Please specify the domain of the organization and Redirect URI with required organization page URL. Above used addresses are a sample addresses we have considered for configurations.

Save the above details to a notepad file.

To grant permissions on the site for the modern app, please use below URL and grant permissions as shown below

https://<site collection url>/_layouts/15/appinv.aspx

Enter Application Client ID obtained above and click on Lookup to auto fetch details as shown below.

Enter below lines in Permission Request XML and click on Ok.

<AppPermissionRequests AllowAppOnlyPolicy="true" >
<AppPermissionRequest Scope="http://sharepoint/content/sitecollection" Right="FullControl" />
</AppPermissionRequests>

Update Configuration.xml with above App Client ID and App Client Secret as shown below.

ListName Node

Specify SharePoint target list title that we need to sync (create/update items) for this sync

UpdateCAML Node

Enter CAML Query that could check if the database entry is already created in SharePoint list. If the service finds the SharePoint list item with this CAML query then it updates the item otherwise it creates the item in SharePoint list so it eliminates the duplicate entries.

• This CAML Query accepts the placeholders. We can use the SharePoint column internal name in “Name” attribute of “FieldRef” tag and we can use database result set column name in “Value” tag text. This way every time before the service creates a new entry for every item in result data set, it checks if the entry is already created in SharePoint.
• Placeholder Syntax: {ValueExp: “[DBColumn Name]”, RegEx=””}
• Please note that in ValueExp, more than one column name and static text can be specified. e.g., we could give “[DBFirstName] [DBLastName]” (note the static space between Db column name expressions)
• A regular expression can also be specified that will extract the matching string from the ValueExp (after replacing DB column values) and then that will be used in the query. e.g. if we want to extract the domain name from SharePoint site URL then you can use something like “https://(.+?)/”

DelayExecution Node

When querying SharePoint to check if the item is already created in SharePoint list using above UpdateCML setting, sometimes SharePoint may throw throttling error (http status codes 429, 406, 503 and 504). This generally happens when syncing large data and for try avoiding this conflict, we can add delay between each operation using this node. In the below sample, we are adding 100 milli seconds delay between each SharePoint operation.

<DelayExecution>100</DelayExecution>

RetryCountAfterThrottle Node

Event after using delay execution setting mentioned above, if we are still receiving the throttle error from SharePoint then we can use this node to retry. Service uses RetryIntervalAfterThrottle node mentioned below for waiting (in seconds) before next retry. See example below, it will retry two times.

<RetryCountAfterThrottle>2</RetryCountAfterThrottle>

RetryIntervalAfterThrottle Node

Event after using delay execution setting mentioned above, if we are still receiving the throttle error from SharePoint then we can use this node along with RetryCountAfterThrottle setting described above. See example below, it will retry after 2 seconds. Generally SharePoint will provide the number of seconds to be waited before retry and this property will be used only in case SharePoint does not give the required wait interval information.

<RetryIntervalAfterThrottle>2</RetryIntervalAfterThrottle>

DBInfo Node:

Provide Database Connection Information

DBType Attribute:

Only “SQLServer” value is supported currently

DBConnectionString Node:

Connection String that is used to communicate with the database server needs to entered in this node.

For example, <DBConnectionString IsSecure=”false”>Server=server\Instance;Database=DBName;User ID=USER;Password=PASSWORD</DBConnectionString>.

Note: we can encrypt the connection string using CrowCanyon.DBSyncPasswordEncryptor.exe tool that is part of the package and use encrypted text in configuration file with IsSecure=”true” in above DBConnectionString node.

This connection string account should have at least read permissions on the source database and execute permissions on the stored procedure.

UpdateQuery Node:

We can either directly use the SQL Query that could read required information from database or stored procedure name in this node.

IsStoredProc Node:

Set this attribute value to True if UpdateQuery has Stored Procedure name else set this attribute to False

Parameters Node:

Define Param for UpdateQuery (Stored Procedure)

Param Node:

• Name Attribute: name of the UpdateQuery Parameter
• ODBCType Attribute: Parameter ODBCType (example, varchar)
• ContextType Attribute: ContextType is Enumeration, possible value are:
  • BatchSize – Number of items to fetch from Db in a single query and then sync to SharePoint. Sync cycle repeats until all the items found.
  • LastID – Db column name for sorting the records with a unique Id. This value is used batch processing to fetch next set of records
  • StartTime – Time at which the last sync finished. This is maintained in SharePoint list by the sync service. This value should be left blank as service automatically refers the last sync record to get this value
  • EndTime – This is the time at which current sync cycle started. It is maintained by the service and should be left blank
• ValueContext Attribute: This will help to get the Parameter Value. This contains the database column name that has the unique identification information like Asset ID.
• DefaultValue Attribute: Default value of the Parameter

UpdateDBSettings Node:

UpdateQuery Node:

Enter stored procedure name that create/updates items in database in this node.

IsStoredProc Node:

Set this attribute value to True if UpdateQuery has Stored Procedure name else set this attribute to False

Parameters Node:

Define Param for UpdateQuery (Stored Procedure)

Param Node:

• Name Attribute: name of the UpdateQuery Parameter, it should match with the input parameter name in stored procedure.
• ODBCType Attribute: Parameter ODBCType (example, varchar)
• ContextType Attribute: Value should always be “SyncList”
• ValueContext Attribute: Value Expression can have SharePoint Column Internal Name enclosed in square bracket([]). For example: [Title]
• DefaultValue Attribute: Default value of the Parameter

Mappings Node:

This section provides ability to map the database column values to SharePoint list columns.

Map Node:

This defines the mapping detail of SharePoint Column.

SPField Attribute:

This defines the SharePoint List column internal name

ValueExp Attribute:

This define the SharePoint Column Value Expression.

Value Expression can have Database Recordset Column Name enclosed in square bracket([]). For example: [AssetName]

We can define multiple placeholders in one Expression to Concatenate the Two Recordset Column value.

RegEx Attribute:

Parse the Value expression value using Regular expression. For Example get the Domain name from Web Url

SPLookupListColName Attribute:

Resolve the lookup column based on specified column internal name in this property instead of resolving value based on display column setting in lookup column setting of the column mentioned in SPField attribute above.

SignatureInfo Node:

• SignatureFieldName Attribute: SharePoint List Column Name (Internal Name of the Column)
• SignatureFieldValue Attribute: Unique Value for this Sync like CCSLansweeper. This helps us to identify the items created by our Data Sync Service.

SyncTimeInfo Node:

• ListName Attribute: This define the CCSSyncTime SharePoint list name

Process Node:

Recurr Attributrte:

• If True, then Sync Process has to run event n number of minutes (based not Recurring interval)
• If False, then process run daily once based on ScanTime

ScanTime Node:

In this node, we can set the Hour at which this sync should start happen every day. Possible Value (0-23) hour of day. If recurrence is disabled then only sync happens daily at the time mentioned in this node.

RecurInterval Node:

This defines the recurrence interval in minutes for the sync. This setting will be used only if recurrence is enabled.

LogPolicy Node

LogLevel Node:

Information Log will be generated if the value is set to 1. For Error log, set this attribute to 0.

LogFileName node:

The log file name can be given in this attribute

SyncLogInfo Node:

SyncLogListName Attribute: CCSSyncLog SharePoint list Name. We can refer this list to see the logs to investigate possible unknown errors.

ServiceSettings:

This tag has the settings SyncInterval node for the service which are common to all the Sync configurations. Based on the time interval set in the SyncInterval Node, service starts after every n minutes.

RetainLogFilesForHours node can be used to automatically delete the service logs. Enter required number of hours to retain the log files in Logs folder.

Example: <RetainLogFilesForHours>10</RetainLogFilesForHours>

Please refer https://www.crowcanyon.help/article/404/ article for standard information that returns stored procedure from LAN Sweeper database.

Please refer https://www.crowcanyon.help/article/405/ article for standard information that returns stored procedure from SCCM database.