Setting up DDL replication

To replicate DDL commands for Microsoft SQL Server, you must create a unique DDL user that is granted privileges to execute all replicated DDL commands at the standby database. The DDL user is the standby database user name included in log transfer language (LTL) for replicating DDL commands to the standby database.

1. Using the Microsoft SQL Server Management Studio or the sqlcmd utility, connect to the standby database, and enter the following command to create a DDL user login:

   CREATE LOGIN ddl_user WITH PASSWORD='password', DEFAULT_DATABASE=standby_db;
   

   Here, password is the DDL user login password, and standby_db is the standby database name.

2. At the standby database, enter the following command to create a DDL user:

   CREATE USER ddl_user FOR LOGIN ddl_user;
   

In addition to the permission to execute all replicated DDL commands at the standby database, the DDL user should also have the impersonate permission granted for all users whose DDL commands may be replicated to the standby database. This impersonate permission is necessary to switch session context in the standby database when executing a DDL command. This user switches context to apply the DDL command using the same privileges and default schema settings as the user who executed the DDL command at the primary database. To provide this context switch, the DDL user must have permission to execute the execute as user Microsoft SQL Server command for any user who might execute DDL commands to be replicated from the primary database.

For example, user1 with a default schema of schema1 executes the following DDL at the primary database:

create table tab1 (id int)

This results in the creation of a table named schema1.tab1 at the primary database. At the standby database, user2 with a default schema of schema2, cannot immediately execute this DDL because it will generate a table named schema2.tab1. Therefore, user2, whose name is specified by the ddl_username configuration parameter, must first execute the following command at the standby database to impersonate user1:

execute as user = 'user1'

The DDL can then be executed with the correct schema by user2 at the standby database, generating a table named schema1.tab1.

There are two ways to grant impersonate permission to the DDL user:

• You can grant database owner permission to the to the DDL user. In doing this, you implicitly grant impersonate permission.

• Alternately, you can grant impersonate permission explicitly with the following Microsoft SQL Server command:

  GRANT IMPERSONATE ON USER::pri_ddl_user TO ddl_user
  

  Here, pri_ddl_user is a user whose DDL is expected to be replicated to the standby database, and ddl_user is the DDL user you created.

When you replicate DDL in Microsoft SQL Server, you must use Microsoft SQL Server as the standby database. You cannot replicate DDL commands from Microsoft SQL Server to non-Microsoft SQL Server standby databases.