Required database, user accounts, and schemas

Before you install the CDC Replication Engine for Microsoft SQL Server, you need to ensure that database, user accounts and schema requirements have been satisfied.

Configuring a Microsoft SQL Server database

When you configure CDC Replication, you are prompted for the name of the Microsoft SQL Server database you want CDC Replication to connect to and replicate data. Before installing CDC Replication, ensure that this Microsoft SQL Server database exists and that you have created and set up a database user that has access to it.

Configuring a Microsoft SQL Server user account

If you plan on using SQL authentication to allow CDC Replication to connect to your Microsoft SQL Server database, you must create a user account with SQL authentication that has the following privileges for the Microsoft SQL Server instance:

  • If you are using CDC Replication as a source of replicated data, you have the following options to configure the database user:

    • sysadmin: This option is the simplest because it enables CDC Replication to execute the necessary statements to set up additional items that are necessary for replication.
    • Trusted database: With this option, a number of SQL statements that require sysadmin privileges are delegated to stored procedures that permit being run as sysadmin while the CDC Replication user has lower (db_owner) privileges. This deployment has some overhead and would be optimal for an environment where sysadmin privileges are prevented. This solution entails creation of the iidrtrusted database with several stored procedures that execute with sysadmin privileges on behalf of the user. The installed samples directory contains these SQL script files to help set up the database and user:

      createdb-sql-nosa.sql
      Creates the iidrtrusted database with the trustworthy property.
      createproc-sql-nosa.sql
      Creates the stored procedures in iidrtrusted database that execute with sysadmin privileges.
      grantuser-sql-nosa.sql
      Grants a user sufficient permissions to use the iidrtrusted database and the distribution database.

      The user must also have the db_owner role on the replication source database.

      Procedure

      1. Execute the sample stored procedure createdb-sql-nosa.sq.
      2. Create the CDC Replication user. Grant it the server role bulkadmin and map the user to one or more databases to be used as the source to the distribution database and the iidrtrusted database.
      3. Execute the sample stored procedure createproc-sql-nosa.sql.
      4. Edit the sample stored procedure grantuser-sql-nosa.sql so that the user variable is set to the CDC Replication user and then execute it.
    • db_owner: If you are prevented from having services connect to the database as a sysadmin user, you can use this option to invoke a number of statements manually while setting up the user and the database for replication. You provide the db_owner role to the CDC Replication user and avoid the need to have a trusted database. To achieve such a deployment, the CDC Replication user must be provided with the db_owner role that is mapped to the replicated source database and the public role that is mapped to the distribution database. A number of additional privileges that are listed in installation directory\samples\grantuser-sql-nosa.sql must be added to the db_owner fixed role, and the SQL Server log reading agent must be enabled. Refer to installation directory\samples\enablerepl-sql-nosa.sql for a sample on possible steps to enable the SQL Server log reading agent.

      Procedure

      1. Give the CDC Replication user the db_owner role that is mapped to the source database and the public role that is mapped to the distribution database.
      2. Grant the privileges that are granted in the script grantuser-sql-nosa.sql to the db_owner role.
      3. Review the script enablerepl-sql-nosa.sql for example steps to enable the log-reading agent.
  • If you are using CDC Replication as a target of replicated data, at minimum you must specify db_owner privileges for the database and bulkadmin as the server role. If you prefer, you can also specify sysadmin privileges for the user account.

When you configure CDC Replication, you are prompted for the name of the Microsoft SQL Server database you want CDC Replication to connect to and the user name and password of the Microsoft SQL Server user that has access to this database.

Setting up a Windows user account

Create or choose an existing Windows account that you will use to install, configure, or upgrade CDC Replication. The user account privileges influence what the CDC Replication service can access. At a minimum you need "Standard User" privileges and "Log on as a service" and "Debug" security policy settings. When configuring CDC Replication to replicate data from transaction log backups only, then the "Debug" security privilege is not needed. You can choose to grant the security policies manually or they could be granted while creating or running the CDC Replication service.

Configuring a Microsoft SQL Server schema, user name or database owner (dbo)

Create or choose an existing schema, user name or dbo for your database metadata tables. You will have to specify this value when you configure CDC Replication.