How Do I Remove CDC Objects After I Stop Using a SQL Server Binary Log Reader Connection?

Question

How do I remove CDC objects after I stop using a Fivetran SQL Server Binary Log Reader connection?

Environment

Connector: SQL Server
Incremental sync method: Binary Log Reader

Answer

When you stop using your SQL Server Binary Log Reader connection, you should run the Binary Log Reader uninstall script to remove the stored procedures, permissions, and related objects that Fivetran created during setup. By default, the script leaves CDC objects in place. To remove them, use the -DisableCdc switch or disable CDC manually, as described below.

Running the uninstall script is especially important if your connection used the default ivetran log truncation mode. In this mode, Fivetran calls sp_repldone to advance the SQL Server truncation point. If the connection stops running and no other process calls sp_repldone, the truncation point stops advancing, which can cause transaction log bloat. For more information, see SQL Server Binary Log Reader FAQs.

Prepare for cleanup

Pause or delete the connection to prevent sync failures when the script removes the objects:
i. In Fivetran, go to your SQL Server connection page.
ii. In the top right corner, set the Enabled toggle to OFF.
Use a SQL Server account with permission to run the commands in the script. To disable CDC manually, the account must belong to the sysadmin server role for database-level changes or the db_owner database role for table-level changes.

Run the uninstall script

In Fivetran, go to Account Settings > Downloads.
Download the Binary Log Reader uninstall script for your operating system.

Run the script:

.\BinLogUninstall.ps1 -SqlInstance <instance> -SourceDatabase <database>

The script accepts the following parameters:

(Required) -SqlInstance: The SQL Server instance name, such as localhost\SQLEXPRESS.
(Required) -SourceDatabase: The database from which to remove the stored procedures.
(Optional) -ClrDatabase and -NoDll: Use these parameters together if you installed Binary Log Reader with a shared CLR database. The script removes only the wrapper procedures and database-level permissions in the source database. It doesn't remove the shared CLR assembly, CLR procedures, FivetranUser login, trusted assembly, msdb helper procedures, or the minimal-permissions user in the CLR database and msdb, because other source databases might use them. -ClrDatabase is required with -NoDll.
If you also pass -MinimalPermissionsUser, the script still revokes that login's server-level permissions (VIEW SERVER STATE or VIEW SERVER PERFORMANCE STATE, and ADMINISTER BULK OPERATIONS), even with -NoDll. Other connections that share the login lose these permissions. Don't pass -MinimalPermissionsUser while other connections still use the login.
(Optional) -SqlAdminUser and -SqlPassword: The SQL admin credentials. If you don't provide them, the script uses Windows Authentication. If you provide -SqlAdminUser, you must also provide -SqlPassword.
(Optional) -MinimalPermissionsUser: Use this parameter if you created a dedicated SQL Server login with minimal permissions for Fivetran. The script removes that login's database users and server-level grants. It doesn't drop the login itself, so you must drop the login manually if you no longer need it.
(Optional) -UseDBDatareaderRole: Use this parameter if you added the minimal-permissions user to the db_datareader role. If you don't set it, the script also removes the fivetran_datareader role if it has no remaining members.
(Optional) -DisableCdc: Disables CDC on the source database. For more information, see Remove CDC objects.

To see all available parameters, run the script with the -Help switch.

Remove CDC objects

Disabling CDC at the database level drops all CDC system objects, including change tables, jobs, stored procedures, and functions. Disable CDC only if no other tools or processes depend on it for this database.

Choose one of the following options:

Run the uninstall script with the -DisableCdc switch to remove the setup objects and disable CDC in the same run:
```
.\BinLogUninstall.ps1 -SqlInstance <instance> -SourceDatabase <database> -DisableCdc
```
After running the uninstall script, disable CDC manually in SQL Server:
```
USE <database>
GO
EXEC sys.sp_cdc_disable_db
GO
```

If other tools or processes still depend on CDC, disable only the capture instances associated with Fivetran. A table can have multiple capture instances. Use @capture_instance = N'all' only if you're certain that no other tool or process uses any of the table's capture instances.

To disable the Fivetran capture instance for the table:

List the capture instances for the table:

USE <database>
GO
EXEC sys.sp_cdc_help_change_data_capture
    @source_schema = N'<schema>',
    @source_name = N'<table>'
GO

Identify the capture instance associated with Fivetran. Fivetran-created capture instances are often named fivetran_<object_id>, where <object_id> is the source table's object ID. Always use the name returned by SQL Server. If you dropped and recreated the table after setup, its object ID changes, and the capture instance name may not match the current object ID.
If you aren't sure which capture instance Fivetran created, don't disable any of them.

Disable the Fivetran capture instance. Replace <capture_instance> with the capture instance name you identified in step two:

EXEC sys.sp_cdc_disable_table
    @source_schema = N'<schema>',
    @source_name = N'<table>',
    @capture_instance = N'<capture_instance>'
GO

For more information, see the Microsoft sys.sp_cdc_disable_table documentation.