Configuring the DRS Synchronization Task

EXEC sys.sp_cdc_enable_db;

If the current Azure SQL Database does not support CDC, CDC fails to be enabled at the database level, and an exception occurs. In this case, run the subsequent SQL statements to upgrade the Azure SQL Database and enable the database-level CDC.

Query the service level of the source database and record the result for later restoration:

• The following editions do not support CDC: Basic, Standard S0, Standard S1, and Standard S2.
• The billing varies depending on the edition of Azure SQL Database.

SELECT 
    database_name = DB_NAME(),
    edition,
    service_objective
FROM sys.database_service_objectives;

Then run the following SQL statements in sequence to enable the service edition of the source database:

(The 5.b.i, 5.b.ii, and 5.b.iii SQL statements are optional. If the database-level CDC fails to be enabled, run the 5.b.i, 5.b.ii, and 5.b.iii SQL statements, refresh the database, and enable the database-level CDC again.)

1. Upgrade the database to the Premium edition of Azure SQL Database that supports CDC.

   ALTER DATABASE [smsa_mig]
   MODIFY (EDITION = 'Premium', SERVICE_OBJECTIVE = 'P1');

2. Disable database-level DDL triggers:

   DISABLE TRIGGER ALL ON DATABASE

3. Disable database-level CDC.

   EXECsys.sp_cdc_disable_db;

4. Enable database-level CDC.

   EXEC sys.sp_cdc_enable_db;

   After CDC is enabled for the source database, check the service edition of the source database again. The service edition has been upgraded to Premium.

BEGIN TRY
    DECLARE @source_name VARCHAR(400);
    DECLARE @sql VARCHAR(1000);
    DECLARE @dbname VARCHAR(100);
    DECLARE @schemaname VARCHAR(100), @enable BIT;

    SET @dbname = 'smsa_migration'; -- Ensure that the database name is correct.
    SET @schemaname = 'SalesLT'; -- Change the schema to the one to be processed (for example, SalesLT or dbo).
    SET @enable = 1;                 -- 1: Enable CDC. 0: Disable CDC.

    DECLARE the_cursor CURSOR FAST_FORWARD FOR
    SELECT table_name
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_CATALOG = @dbname 
      AND TABLE_SCHEMA = @schemaname 
      AND TABLE_NAME != 'systranschemas'
      AND TABLE_TYPE = 'BASE TABLE'; -- Key correction: Select only base tables and exclude views.

    OPEN the_cursor;
    FETCH NEXT FROM the_cursor INTO @source_name;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @enable = 1
        BEGIN
            SET @sql = 
                'USE ' + @dbname + '; ' +
                'EXEC sys.sp_cdc_enable_table ' +
                '@source_schema = N''' + @schemaname + ''', ' +  -- Schema name
                '@source_name = N''' + @source_name + ''', ' +    -- Table name
                '@role_name = NULL;';  -- Set the role name as required. NULL indicates that the access is not restricted.
        END
        ELSE
        BEGIN
            SET @sql = 
                'USE ' + @dbname + '; ' +
                'EXEC sys.sp_cdc_disable_table ' +
                '@source_schema = N''' + @schemaname + ''', ' +  -- Schema name
                '@source_name = N''' + @source_name + ''', ' +    -- Table name
                '@capture_instance = ''all'';'; -- Disable all instances.
        END

        PRINT @sql; -- Debug and output the generated SQL.
        EXEC(@sql);
        FETCH NEXT FROM the_cursor INTO @source_name;
    END

    CLOSE the_cursor;
    DEALLOCATE the_cursor;
    SELECT 'Successful';
END TRY
BEGIN CATCH
    IF CURSOR_STATUS('global', 'the_cursor') >= 0
    BEGIN
        CLOSE the_cursor;
        DEALLOCATE the_cursor;
    END

    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH

select name, is_tracked_by_cdc from sys.tables where is_tracked_by_cdc = '1';