Microsoft SQL Server

via SSMS

Create a New User

1. In the SQL Server Management Studio, open Object Explorer.
2. Click <instance_name> → Security → Logins.
3. Right-click Logins and select New Login.
4. On the General page, in the Login name field, type the name for your new user.
5. Select SQL Server authentication.
6. Type a password for the user in the Password and Confirm password field.
7. Make sure User must change password at next login is unchecked.
8. For Default database, select the database containing the tables to extract.

9. On the Securables page click the Search button, select The server <server name>, and click OK.

10. Select the server in the Securables pane.

11. Open the Explicit tab in the Permissions for <server name> pane.

12. Ensure the Connect SQL boxes are checked under the Grant column.

    

13. On the User Mapping page, select the database that contains the tables to be extracted.

14. The public role should be automatically enabled.

    

15. Click OK.

Grant Access to the User

1. In the SQL Server Management Studio, open Object Explorer.
2. Open <Instance name> → Databases → <database name> → Security → Schemas → <schema name>.
3. Right-click on the schema to be accessed by Etleap and click Properties.
4. On the Permissions page, click the Search button.
5. Click the Browse button, select the newly created user, and click OK.

6. Click OK on the search window.
7. Select the user in the Users or roles pane.
8. In the Permissions for <user name> pane, open the Explicit tab.
9. Check the box for Select in the Grant column.

10. Click OK.

11. You can now use this new user to create a SQL Server connection in Etleap.

via SQL

Connect to the source SQL Server instance and run the following steps.

1. Create a user. Replace <secret> with a password of your choice.

CREATE LOGIN etleap WITH PASSWORD = <secret>;
CREATE USER etleap FOR LOGIN etleap;

2. Allow the user to connect to the database.

GRANT CONNECT ON DATABASE::<database> TO etleap;

3. Grant the SELECT permission on the objects you’d like to extract from. This permission can be set at the database, schema, or object level.
4. Select the minimal permission level that grants access to the data you would like to extract.

GRANT SELECT ON DATABASE::<database> TO etleap;
GRANT SELECT ON SCHEMA::<schema_name> TO etleap;
GRANT SELECT ON OBJECT::<schema_name>.<object_name> TO etleap;

5. You can now use this new user to create a SQL Server connection in Etleap.

via SSMS

1. In the SQL Server Management Studio, open Object Explorer.
2. Right-click on <instance_name> → Security → Logins → <etleap user>, and select Properties.
3. On the User Mapping page, select the database to replicate and assign the public and db_owner roles.

via SQL

Connect to the source SQL Server instance and run the following queries.

USE <database name>
ALTER ROLE [db_owner] ADD MEMBER etleap

undefined

Step 1. Set the Database Recovery Model and Backup

1. In the SQL Server Management Studio, open Object Explorer.

2. Right-click on Databases → <database to replicate>, and click Properties.

3. On the Options page, make sure the Recovery model for the publication has been set to Bulk logged or Full:

   

4. Click OK.

5. A full backup must be performed before enabling the database for replication.

6. Right-click on Databases → <database to replicate>, and click Tasks → Back Up.

7. The Backup type dropdown should be set to Full and Destination → Back up to should be set to Disk.

Step 2. Configure Distribution and Backup Access

1. In the SQL Server Management Studio, open Object Explorer.
2. Right-click on <instance_name> → Security → Logins → <etleap user>, and select Properties.
3. On the User Mapping page, select the msdb and master databases, and check the public role.

4. Right-click on the Replication folder, and then choose Configure Distribution.

5. On the Distributor step, select Server will act as its own Distributor. Remote distributors are not supported by DMS.
6. Complete the Configure Distribution wizard.

7. Follow the relevant steps here  to grant the minimal required permissions for the user. Replace DMS_user with the user specified in your Etleap connection.

Step 3. Create a Publication

Follow the steps below to create a publication for each table that you would like to extract data from.

1. Log in to SSMS using the SYSADMIN user account.
2. Start a new query with File → New → Query with Current Connection.
3. Run the following query to get the DBID for the database you want to replicate in Etleap.

SELECT DB_ID('<database_name>');

4. Expand the Replication folder in the Object Explorer.
5. Right-click the Local Publications folder, and choose New Publication.
6. In the New Publication Wizard, click Next.
7. Choose the database where you want to create the publication.
8. Choose Transactional Publication, and then click Next.

9. Expand Tables, choose the tables you want to replicate, and click Next.

10. Click Next to skip filter creation as it is not required.

11. In the Snapshot Agent step, select the Create a snapshot immediately and keep the snapshot available to initialize subscriptions option.

12. Click Next.

    

13. On the Security step, click the Security Settings button.

14. Select the Run under the SQL Server Agent service account and By impersonating the process account option.

15. Click OK.

    

16. Click Next.

17. Select Create the publication.

18. Provide a name of the publication in the format AR_PUBLICATION_000XX where XX is the DBID from step 3. For example:

    • If your DBID is less than 10, you need to name the publication AR_PUBLICATION_0000X (4 zeros).
    • If your DBID is greater than or equal to 10, you need to name the publication AR_PUBLICATION_000XX (3 zeros).

19. Click Finish.

20. Right-click on Replication → Local Publications and click Launch Replication Monitor.

21. Select the publication you just created and open the Agents tab.

22. The Snapshot Agent job should have completed successfully and the Log Reader Agent job should be running.

23. Right-click the Log Reader Agent job and open the Agent Profile.

    

24. Click New and select the Default agent profile to initialize the new profile from.

25. Edit the pollinginterval to 86399, which configures unread changed to be purged after a day.

    

26. Select the new agent profile and click OK.

    

undefined

Step 1. Set the Database Recovery Model and Backup

1. In the SQL Server Management Studio, open Object Explorer.

2. Right-click on Databases → <database to replicate>, and click Properties.

3. On the Options page, make sure the Recovery model for the publication has been set to Bulk logged or Full:

   

4. Click OK.

5. A full backup must be performed before enabling the database for replication.

6. Right-click on Databases → <database to replicate>.

7. Click Tasks → Back Up.

8. The Backup type dropdown should be set to Full and Destination → Back up to should be set to Disk.

Step 2. Enable CDC for the Database and Source Tables

For tables without primary keys, set up MS-CDC for the database.

1. Run the following command as an account that has the sysadmin role assigned to it.

USE <database name>
EXEC sys.sp_cdc_enable_db

2. For each table with unique keys but no primary key, run the following query to set up MS-CDC.

EXEC sys.sp_cdc_enable_table
@source_schema = N'schema_name',
@source_name = N'table_name',
@index_name = N'unique_index_name',
@role_name = NULL,
@supports_net_changes = 1
GO

3. For each table with no primary key or no unique keys, run the following query to set up MS-CDC.

EXEC sys.sp_cdc_enable_table
@source_schema = N'schema_name',
@source_name = N'table_name',
@role_name = NULL
GO

4. Set the retention period for changes to be available on the source using the following commands.

USE dbname
EXEC sys.sp_cdc_change_job @job_type = 'capture', @pollinginterval = 86399
EXEC sp_cdc_stop_job 'capture'
EXEC sp_cdc_start_job 'capture'

The parameter @pollinginterval is measured in seconds with a recommended value set to 86399. This means that the transaction log retains changes for 86,399 seconds (one day) when @pollinginterval = 86399. The procedure EXEC sp_cdc_start_job 'capture' initiates the settings. 5. Follow the relevant steps here  to grant the minimal required permissions for the user.

Replace DMS_user with the user specified in your Etleap connection.

Cloud-Hosted Instance

Unlike self-managed Microsoft SQL Server sources, cloud instances do not support MS-Replication. Therefore, MS-CDC must be used for tables with or without primary keys.

Step 1. Set the Database Recovery Model and Backup

1. In the SQL Server Management Studio, open Object Explorer.

2. Right-click on Databases → <database to replicate>, and click Properties.

3. On the Options page, make sure the Recovery model for the publication has been set to Bulk logged or Full:

   

4. Click OK.

5. A full backup must be performed before enabling the database for replication. Right-click on Databases → <database to replicate>.

6. Click Tasks → Back Up.

7. The Backup type dropdown should be set to Full and Destination → Back up to should be set to Disk.

Step 2. Enable CDC at the Database Level

1. For an instance hosted on RDS, use this query.

EXEC msdb.dbo.rds_cdc_enable_db <database name>
# Allow DMS to access log backups
GRANT EXEC ON msdb.dbo.rds_dms_tlog_download TO rds_user;
GRANT EXEC ON msdb.dbo.rds_dms_tlog_read TO rds_user;
GRANT EXEC ON msdb.dbo.rds_dms_tlog_list_current_lsn TO rds_user;
GRANT EXEC ON msdb.dbo.rds_task_status TO rds_user;

2. For an instance hosted on Azure, use this query.

USE DB_name
GO
EXEC sys.sp_cdc_enable_db
GO

Step 3. Enable CDC at the Table Level

1. For each table with a primary key, run the following query to turn on MS-CDC.

EXEC sys.sp_cdc_enable_table
@source_schema = N'schema_name',
@source_name = N'table_name',
@role_name = NULL,
@supports_net_changes = 1
GO

2. For each table with unique keys but no primary key, run the following query to turn on MS-CDC.

EXEC sys.sp_cdc_enable_table
@source_schema = N'schema_name',
@source_name = N'table_name',
@index_name = N'unique_index_name',
@role_name = NULL,
@supports_net_changes = 1
GO

3. For each table with no primary key nor unique keys, run the following query to turn on MS-CDC.

EXEC sys.sp_cdc_enable_table
@source_schema = N'schema_name',
@source_name = N'table_name',
@role_name = NULL
GO

4. Set the retention period for changes to be available on the source using the following commands.

USE dbname
EXEC sys.sp_cdc_change_job @job_type = 'capture', @pollinginterval = 86399
EXEC sp_cdc_stop_job 'capture'
EXEC sp_cdc_start_job 'capture'

The parameter @pollinginterval is measured in seconds with a recommended value set to 86399. This means that the transaction log retains changes for 86,399 seconds (one day) when @pollinginterval = 86399. The procedure EXEC sp_cdc_start_job 'capture' initiates the settings.

5. Follow the relevant steps here  to grant the minimal required permissions for the user.

Replace DMS_user with the user specified in your Etleap connection.

6. If you are using Amazon RDS with Multi-AZ, make sure that you also set your secondary to have the right values in case of failover.

EXEC rdsadmin..rds_set_configuration 'cdc_capture_pollinginterval', 86399