Testing Replication

When you finish setting up the replication system, test that replication works as intended.

1. Connect to the primary Microsoft SQL Server database as a regular user, rather than as the maintenance user. Make sure the regular user also exists in the replicate database.
2. At the primary Microsoft SQL Server database, create a table named ptab1 to replicate:

   CREATE TABLE ptab1
   (idno int PRIMARY KEY,
   name varchar(20));

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

   grant all on ptab1 to public;

4. Connect to Replication Agent through isql, and mark the ptab1 table for replication:

   pdb_setreptable ptab1, mark
   go

5. Add rows to the ptab1 table:

   insert into ptab1 values (1, 'Burt Gringlesby');
   insert into ptab1 values (2, 'Douglas Wong');
   insert into ptab1 values (3, 'Tom Servo');
   insert into ptab1 values (4, 'Innes del Castillo');
   insert into ptab1 values (5, 'Akiko Yokomoto');
   

6. In Replication Agent, create a replication definition against the primary Microsoft SQL Server database:

   rs_create_repdef ptab1
   go
   Table/Procedure Name     RepDef Name    Status
   ------------------------ -------------- --------
   <MSSQLTableOwner>.PTAB1 "<repdefname>" Created
   (1 row affected)

   Record the name of the replication definition for use later.
7. At the replicate SAP HANA database instance, create a table named PTAB1:

   CREATE TABLE <tableowner>.PTAB1 
   (IDNO INT PRIMARY KEY, 
   NAME VARCHAR(20));

   Note: SAP HANA database is not case-sensitive and uses uppercase characters if lowercase characters are provided.

   If an owner is not specified with the table name, the owner of the table—<tableowner>—is the user who is logged in when the table is created.
8. Grant permissions to any new or existing object to be replicated in the replicate database so that the Replication Server maintenance user can update this table:

   grant all privileges on <tableowner>.PTAB1 to public

9. Log in to Replication Server:

   isql –Usa –Psa_pass –SSAMPLE_RS

   If you have not set up the sample Replication Server instance, enter your Replication Server instance name in place of SAMPLE_RS.
10. Alter the replication definition that you created earlier to point to the correct replicate table:

    alter replication definition <repdefname>
    with replicate table named <tableowner>.ptab1
    go

    Note: SAP HANA database is not case-sensitive and uses uppercase characters if lowercase characters are provided.
11. Create a subscription against the replicate connection to materialize the PTAB1 table on the SAP HANA database.
    Create the ptab1_sub subscription with direct_load option for the <repdefname> replication definition. Replication Server logs in to Replication Agent for Microsoft SQL Server, and Replication Agent issues a SELECT command to select data from the Microsoft SQL Server database, enter:

    create subscription ptab1_sub
    for <repdefname>
    with replicate at rds.rdb
    without holdlock
    direct_load
    user puser password ppwd
    go

    where:

    • rds.rdb is the replicate SAP HANA database connection created earlier.
    • puser is the user who connects to Replication Agent for Microsoft SQL Server.
    • ppwd is the password of the puser. You must enter a password.

    Note: The interfaces file used by the replicate Replication Server must contain an entry for the Replication Agent for Microsoft SQL Server that is used to select data from the Microsoft SQL Server database.

    To create the subscriptions at the replicate Replication Server using the no materilization method, use the create subscription command with the without materialization clause.

    For information about using the create subscription command in direct_load materialization, see Replication Server Reference Manual.

12. Check the subscription status to ensure there are no errors:

    check subscription ptab1_sub
    for <repdefname>
    with replicate at rds.rdb
    go

    If there are errors, see Troubleshooting Subscription Problems.

13. Connect to the Microsoft SQL Server database as a non-maintenance user with the ability to insert rows into the table:

    insert into ptab1 values (10, 'Michel DeFrance');
    insert into ptab1 values (11, 'Dirk Stringer');
    commit;

14. On the Replication Server, check the status of the subscription created with the direct_load option by using the check subscription command. When the subscription is in the VALID state, materialization is complete:

    check subscription ptab1_sub
    for <repdefname>
    with replicate at rds.rdb
    go
    Subscription ptab1_sub is VALID at the replicate.

15. After the status is VALID at the primary and replicate database, on the replicate database, verify that the data is replicated to the replicate PTAB1 table:

    SELECT * FROM <tableowner>.PTAB1

• Resetting the Primary Microsoft SQL Server Database for Replication
  In a test environment, you may occasionally need to reset the replication environment.