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 standby database.
Create a test table to replicate in the primary database:
create table T1( a int, b char(10) ), go
This SQL statement is replicated by the user defined in the ddl_username Mirror Replication Agent configuration parameter. After the time interval indicated in the scan_sleep_max Mirror Replication Agent configuration parameter, the T1 table is replicated to the target Microsoft SQL Server instance.
Grant permissions to any new or existing object to be replicated in the primary database:
grant all on T1 to public go
This SQL statement is also replicated to the standby database by the user defined in the ddl_username Mirror Replication Agent configuration parameter.
Connect to the standby Microsoft SQL Server database as a regular user, not the maintenance user.
After the table is marked and the Mirror 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”.
Mirror Replication Agent applies only committed transactions to a standby database.
insert into T1 values ( 42, ‘foo’ ) go
Use your preferred Microsoft SQL Server query tool to examine the standby site for results, and compare the contents of your test table from both the primary and standby sites.
To test replication