Configure an Azure SQL database

The following steps cover how to configure an Azure SQL database for use with Datastream.

To configure an Azure SQL database:

Enable change data capture (CDC) for your source Azure SQL database. To do it, connect to the database using Azure Data Studio or SQL Server Management Studio and run the following command:
```
EXEC sys.sp_cdc_enable_db;
GO
```
Enable CDC on the tables for which you need to capture changes:
```
EXEC sys.sp_cdc_enable_table
@source_schema = N'SCHEMA_NAME',
@source_name = N'TABLE_NAME',
@role_name = NULL
GO
```
Note: You need to run the command for each table for which you want to enable CDC.

Replace the following:
- SCHEMA_NAME: the name of the schema to which the tables belong
- TABLE_NAME: the name of the table for which you want to enable CDC
Start the SQL Server Agent and make sure it's running at all times. If the SQL Server Agent remains down for an extended period, the logs might get truncated, leading to a permanent loss of the change data that wasn't read by Datastream.

For information about running the SQL Server Agent, see Start, stop, or restart an instance of the SQL Server Agent.

Note: We recommend setting the automatic restart on for the SQL Server Agent service to prevent the agent from being down for a longer time.
Enable snapshot isolation.

When you backfill data from your SQL Server database, it's important to ensure consistent snapshots. If you don't apply the settings described in this section, changes made to the database during the backfill process might lead to duplicates or incorrect results, especially for tables without primary keys.

Enabling snapshot isolation creates a temporary view of your database at the start of the backfill process. This ensures that the data being copied remains consistent, even if other users are making changes to the live tables at the same time. Enabling snapshot isolation might have a slight performance impact, but it's essential for reliable data extraction.

To enable snapshot isolation:
1. Connect to your database using a SQL Server client.
2. Run the following command:
```
ALTER DATABASE DATABASE_NAME SET ALLOW_SNAPSHOT_ISOLATION ON;
```
Replace DATABASE_NAME with the name of you database.

Create a Datastream user:

Connect to the master database and create a login:

USE master;
CREATE LOGIN YOUR_LOGIN WITH PASSWORD = 'PASSWORD';

Connect to the source database and create a user for your login:

USE DATABASE_NAME
CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN;

Assign the db_owner and db_denydatawriter roles to your user:

EXEC sp_addrolemember 'db_owner', 'USER_NAME';
EXEC sp_addrolemember 'db_denydatawriter', 'USER_NAME';

What's next

Learn more about how Datastream works with SQL Server sources.