Configure a source SQL Server database

Datastream works with the following types of SQL Server databases:

To set up a source SQL Server database for data streaming, you must enable change data capture (CDC), set up permissions and logging, and define a retention policy. For information about how to configure each type of a SQL Server database, see the sections that follow.

Configuration overview

When replicating data from a SQL Server source, Datastream tracks data manipulation language (DML) changes using transaction logs. This approach reduces the load on the database while maintaining fast data retrieval.

To prevent data loss, it's important that the logs aren't truncated before Datastream reads them. On the other hand, if you keep the log files for too long, they take too much storage space, which might eventually cause the database instance to enter read-only mode.

To make sure that the CDC reader has enough time to read the logs while allowing log truncation to save storage space, you need to apply additional configuration steps, such as changing polling intervals and setting up a truncation safeguard. These steps provide an additional layer of protection to ensure that Datastream can read the data even if there's downtime on the Datastream side or a connectivity issue between the source database and Datastream.