Skip to Content
DocumentationDatabasesAzure SQL

Azure SQL

Azure SQL connections are created using the SQL Server connection.

Etleap can use Query-based extractions to extract from both Azure SQL DB and Azure SQL Managed Instance. For Azure SQL Managed Instance, Etleap can also extract data using Change Data Capture (CDC).

With Query-based extractions, all data is extracted with SQL queries. For CDC extractions, Etleap will take an initial snapshot of the table using SQL queries and capture incremental changes from the Active Transaction Log and Log Backup Files.

Source Setup

This section of the setup guide should be followed regardless of whether query-based extractions or CDC will be used to extract the data. To configure the source instance for CDC, complete the steps below, and then follow the guide under Change Data Capture (CDC) Setup .

Step 1. Create a Database User

To extract data from the database, you’ll need to create a new Azure SQL user with the minimal permissions to read from the schema you would like Etleap to access.

  1. Create a login and user in your source database 
    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 minimal permission level that grants access to the data you would like to extract
    Choose one of the statements below to assign SELECT permissions at a certain level
    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;

Step 2. Whitelist Etleap’s IP Addresses

Etleap’s IP addresses must be whitelisted in your Azure SQL instance’s network security group. There are different addresses to whitelist depending on your deployment model. Review the IP Whitelisting article to determine which to use.

When creating your connection in Etleap, make sure to provide the public hostname that’s in the format of:

http://my-db-1111111.public.123456abcdef.database.windows.net/
Note

Prefer using an API? Go here  and select SQL Server under the Body header to create your connection via API.

Change Data Capture (CDC) Setup

This setup guide should be followed before creating a connection in Etleap with the CDC option enabled. Ensure the Source Setup steps have been completed first. CDC can be set up with or without granting access to the db_owner.

Caution

Azure SQL DB Limitation

Azure SQL DB is not supported for Change Data Capture, only Azure SQL Managed Instance is compatible with the below setup.

Step 1. Add User Permissions for CDC

The database user requires additional permissions for CDC to work. Follow the steps below to grant the necessary permissions.

  1. Add your user to the db_owner role

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

  2. Grant access to view the server state

    USE master;
GRANT VIEW SERVER STATE TO etleap;

  3. Grant access to the backup logs

    USE msdb;
CREATE USER etleap FOR LOGIN etleap;
 
GRANT SELECT ON msdb.dbo.backupset TO etleap;
GRANT SELECT ON msdb.dbo.backupmediafamily TO etleap;
GRANT SELECT ON msdb.dbo.backupfile TO etleap;

Step 2. Enable CDC at the Database Level

  1. Turn on CDC for the database 
    USE <database name>
GO
EXEC sys.sp_cdc_enable_db
GO
  2. Set the retention period for transactions in the logs using the following command 
    USE <database name>
EXEC sys.sp_cdc_change_job @job_type = 'capture', @pollinginterval = 86399
EXEC sp_cdc_stop_job 'capture'
EXEC sp_cdc_start_job 'capture'

When increasing the polling interval, it is recommended to ensure that your capture job’s max_trans and max_scans parameters are appropriately set to handle your transaction volume and prevent the transaction logs from consuming too much disk space.

See SQL Server: Configure Transaction Log Retention for more information on how to configure the capture job.

Step 3. Enable CDC at the Table Level

  1. For each table with a primary key, run the following query

    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

    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

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

CDC Limitations

Database and Table Limitations

Data Manipulation Language (DML) and Data Definition Language (DDL) Limitations

  • Changes to computed columns  aren’t replicated.
  • Partition switching isn’t supported.
  • When using the WRITETEXT and UPDATETEXT utilities, the resulting changes aren’t captured.
  • The following DML pattern isn’t supported: 
    SELECT * INTO new_table FROM existing_table
  • Renaming tables and columns using sp_rename  isn’t supported.
  • TRUNCATE TABLE events aren’t captured.
  • Changes to default column values and column nullability are not captured.

CDC Error Guides

OverviewBigQuery