Skip to main content

SQL Server Database Environment Preparation

SQL Server Startup and Deployment

Confirm That SQL Server Agent Is Running

Method 1: Check in the SSMS User Interface

  1. Connect to SQL Server with an account that has administrative privileges.
  2. In Object Explorer, expand the instance node.
  3. Check the status of the SQL Server Agent node.

How to determine the status:

  • If the node icon appears normal and the node can be expanded, SQL Server Agent is usually running.
  • If the node has a red stopped icon or the shortcut menu only provides Start, SQL Server Agent is usually not running.

If SQL Server Agent is not running:

  1. Right-click SQL Server Agent.
  2. Click Start.
  3. Wait for the node status to refresh, and then check it again.

Method 2: Check with a SQL Query

Use the following DMV query first:

SELECT
    @@SERVERNAME AS server_name,
    servicename,
    startup_type_desc,
    status_desc,
    last_startup_time,
    service_account
FROM sys.dm_server_services
WHERE servicename LIKE N'SQL Server Agent (%'
   OR servicename = N'SQL Server Agent';
GO

How to interpret the results:

  • status_desc = RUNNING: SQL Server Agent is running.
  • status_desc = STOPPED: SQL Server Agent is not running.
  • No results or a permission error: Ask the DBA to run the query, or use SSMS or SQL Server Configuration Manager to check the service status.

Confirm That the CDC Initialization Account Has the db_owner Role

Grant the Required Role

If the database user already exists but does not have the db_owner role, use an account with higher privileges to run the following statements:

USE [YourDatabase];
GO

ALTER ROLE [db_owner] ADD MEMBER [cdc_init_user];
GO

If the login already exists but the database user has not been created, run the following statements first:

USE [YourDatabase];
GO

CREATE USER [cdc_init_user] FOR LOGIN [cdc_init_user];
ALTER ROLE [db_owner] ADD MEMBER [cdc_init_user];
GO

Enable Database-Level CDC

We recommend validating the configuration in a test database before applying it to a production database.

USE AppDb;
GO

IF (SELECT is_cdc_enabled FROM sys.databases WHERE name = DB_NAME()) = 0
    EXEC sys.sp_cdc_enable_db;
GO

Note:

  • Running the CDC enablement statement immediately after SQL Server Agent starts may return an error.
  • If an error similar to 14258: Cannot perform this operation while SQLServerAgent is starting occurs, wait a moment and try again.

Enable Table-Level CDC

IF NOT EXISTS (
  SELECT 1
  FROM cdc.change_tables
  WHERE source_object_id = OBJECT_ID('dbo.CdcTimeTest')
)
BEGIN
    EXEC sys.sp_cdc_enable_table
        @source_schema = N'dbo',
        @source_name = N'CdcTimeTest',
        @role_name = NULL,
        @supports_net_changes = 1;
END
GO

Recommendations:

  • If no dedicated authorization is required for @role_name, set it to NULL.
  • If the customer environment requires isolated CDC access permissions, grant the required roles according to its permission model.