Testing replication

After you finish setting up the replication system, you can test replication.

Connect to the primary Microsoft SQL Server database as a regular user, not the maintenance user. Make sure the regular user also exists in the replicate database.

Create a test table to replicate in the primary database:

create table T1( a int, b char(10) ),

go

Grant permissions to any new or existing object to be replicated in the primary database:

grant all on T1 to public

go

Connect to the replicate Microsoft SQL Server database as a regular user, not the maintenance user.

Create a test table in the replicate database:

create table T1 (a int b chart(10))

go

Grant permissions to any new or existing object to be replicated in the replication database so that the Replication Server maintenance user can update this table:

grant all on T1 to public

go

Connect to the Replication Agent and run the following command to mark the table:

pdb_setreptable T1, mark

After the table is marked and the Replication Agent is in a replicating state, insert test data into the test table and commit it.

By default, the work performed by the maintenance user of the primary connection is not replicated. The user ID used to insert transactions cannot be the same as the maintenance user defined in the primary connection. See “Configuring Replication Server for replication from the primary database”.

Replication Agent applies only committed transactions to a replicate database.

insert into T1 values ( 42, ‘foo’ )

go
Use your preferred Microsoft SQL Server query tool to examine the replicate site for results, and compare the contents of your test table from both the primary and replicate sites.