MS SQL Server is a database management system created by Microsoft, and for this article will serve as source for replication using Qlik Replicate, a tool that allows data replication from multiple sources and multiple destinations.
- Enable the database in MS SQL Server to use in replication with Qlik Replicate
Assignment of permissions
According to the documentation provided by Qlik, the user must have “db_owner” permissions on the database and role of “sysAdmin”. Once the connection to the database has been established and using the “Microsoft SQL Server Management Studio” tool, this is done as follows:
First, go to the “Security” section under the connection to the database. Expand “Logins” and go to the login that will have the access. Right click on it and click on “Properties”.
In the properties, go to “Server Roles” and assign add the check in the sysadmin option on the right.
Finally, select “User Mapping” from the left menu, then on the right side select the database and the role at the bottom, which in this case is “db_owner”.
Enable replication in the database
To enable replication, you first need to install certain features if they are not previously installed. It is necessary to open “SQL Server Installation Center”
In the menu on the left, select “Installation” and on the right “New SQL Server stand-alone installation or add features to an existing installation”
Then find the folder where the installation is located.
In this case, since SQL Server is installed, the second option is selected and the characteristics of these instances will appear in the table below. Click on “Next” to continue.
On the next screen select the “SQL Server Replication” option and continue.
A summary of the installation is displayed, press “Install” to continue with the installation.
An installation confirmation screen will appear, close and return to “SQL Server Management Studio”.
Select the option of “Replication” and then “Configure Distribution…”.
On the screen that appears, click “Next” to continue.
Select the option in which the instance will act as its own distributor (upper option, selected in the image).
Select the option you want regarding how the configuration will be carried out with SQL Server Agent, which is in charge of different automatic tasks.
Select the “snapshots” directory, replicas of the data in a certain period and that are not updated. Continue through “Next”.
Assign a name to the distribution database and continue.
Select the interested “Publisher” and continue.
Select the best option according to your criteria, in this case it will be configured immediately.
Finish the “Wizard”.
A summary with the status of the tasks will appear. In this case there was a problem with the agent configuration, but it can continue without problems.
The options in Replication now configured are different from the previously listed.
Full database backup
The last requirement to use the full load and change capture capabilities is a full backup of the database. To do this go to the database in the left menu, right click, then “Tasks” and “Back Up … ”.
Make sure everything is correct, paying attention mainly to the “Backup type” and finish by pressing “OK”.