In this article, you will see how to set-up a SQList replication.
In this replication you will export a number of lists and libraries from a single site - this is the most basic type of replication supported by SQList.
For more complex replications, see our "How to" section.
Firstly, it is worth going through some of the jargon used in this document:
- A site definition contains the details necessary to connect to a single SharePoint site (URL, credentials, etc.). When you create a site definition, you will also give it a name, which has to be unique.
- A database connection contains the details necessary to connect to a SQL Server database (server name, database name, credentials, etc)
- A replication defines which lists, libraries, and sub-sites (where applicable) are exported from a one SharePoint site (defined by a site definition) to a SQL Server database (defined by a database connection).
Important: you cannot have two replications that use the same site definition and database connection.
If you don't have any replication defined, SQList Manager will prompt you so start the wizard to create one:
Just click "Yes" to get started. Subsequently, to create a replication click "New Replication" to start the wizard.
Step 1: Enter the details of the SharePoint site you want to export
The first step in the wizard is to create a new
site definition, or select an existing one if you have defined it previously. Once you have entered the details to connect to the SharePoint site, you can use the "Test Connection" to ensure they are correct and SQList can connect to the site (SQList connects to the SharePoint site's web services via HTTP).
The site definition you create in this step will be used in the replication as the source SharePoint site to export, and it will also be saved for future uses.
A few points worth noting:
- When you enter the Site URL, make sure you do not enter the URL to a specific list, or to a default page, but to the folder of the site you want to export;
- If you choose "Trusted Authentication", make sure you understand how SQList uses Trusted Authentication.
Once you entered the details, click "Next >" to go to the next step, to define the destination database connection.
Step 2: Enter the details of the SQL Server database onto which to export the SharePoint lists
In this step you create a new
database connection, or select an existing one if you have defined it previously. Once you have entered the details to connect to the SQL Server database, you can use the "Test Connection" to ensure they are correct and SQList can connect to the database (SQList connects to the SQL Server database using standard .NET connection via TCP).
The database connection you create will be used in the replication as the destination database of the export, and it will also be saved for future uses.
A few points worth noting:
- If your SQL Server does not use the standard port, you can use the format
localhost\sqlexpress,1234 as "Server Name\Instance";
- If you choose "Trusted Authentication", make sure you understand how SQList uses Trusted Authentication.
Once you entered the details, click "Next >" to go to the next step, to select what to export.
Step 3: Select what type of export you want to use
In this step in the wizard you decide what you want to export. SQList supports two main types of exports:
- Export lists and libraries from this site only: this is the most common type and it is used to export lists and libraries that belong directly to the site you have defined in the site definition;
- Export form this site and its sub-sites: in this export SQList lets you select lists and libraries across site you have defined in the site definition and its sub-sites; this more complex type of export is described in detail in our "How To" section.
For the purpose of this article, we will select the first option.
Click "Next >" to go to the next step and select the lists to export.
Step 4: Select which lists and libraries you want to export
At this point you are presented with the list of all lists and libraries in your SharePoint site.
You have two ways to select which lists and libraries you want SQList to export; this is determined by the "By Default" selection:
- Export only the lists and libraries selected below: using this option, SQList will only export the lists and libraries that you select in the table below. This is the mode to use if you only want to export certain lists onto your SQL database.
- Export all lists and libraries, except those unchecked below: using this option, SQList will export all lists and libraries in your SharePoint site, with the exception of those you de-select in the table below. Be aware that in this mode, SQList will automatically export new lists added to the SharePoint site at a later stage. This is the mode to use if you want to export the entire content of your SharePoint site, with the exception of certain lists (e.g. you may want to exclude SharePoint system lists like "
List Template Gallery" or "Form Templates").
Once you select a default option, you can override it for individual lists by selecting:
- Export List: select or deselect this checkbox to include or exclude the list from being exported;
- Export binary content: use this option to export the binary content of document libraries and image libraries as a blob column in the SQL tables. You may want to export it if you plan to serve those documents via other technologies (e.g. a .NET website), or you may rather not export to save storage on the SQL Server database.
- Export attachments: select this option if you want SQList to export attachments for the lists that have them enabled. Attachments are exported on a separate table in the database.
For our purpose, we select
only "
Big List", "
Categories", "
Customers", and "
Invoices" to be exported; for "
Customers" (which in our case is a Picture library) also the binary data of the image will be exported:
Click "Next >" to complete the wizard.
And you're done!
This is all you need to do to get your SharePoint lists exported as normalised tables into your SQL Server database.
Click "Finish" to save your changes and start the SQList Service.
When the SQList Service is started, SQList Manager switches to the "Service Status" tab, from where you can monitor the correct functioning of SQList. If there are errors,they will be displayed in the "Event Viewer" window.
You can also use the "Table Status" tab to send us an "Event Report" in case you are getting errors that you cannot resolve. When we receive an Event Report we get back to you as soon as possible.
And that is it. You can now take a look at your SQL database and see the tables being created and populated by SQList. Note that you do not need to keep SQList Manager open for the replication to continue as that is done by the SQList Service in the background.
In this example, this is what the SQL tables look like:
Note: the table "
(SQList.Status)": this table is reserved for SQList and should not be used by your queries or applications.
For more ways to export SharePoint lists and libraries to SQL Server, please visit
our "How to" section.