-
Title
How to add a schema in replication in a High Availability environment -
Description
How to add a schema in replication in a High Availability environment? -
Cause
General information. -
Resolution
If the schema being added on primary conforms to the wildcard entries in the active config file, then it will be inducted into replication while it is added on primary. It will also be added to the secondary by virtue of replication. All subsequent DMLs to the source schema will replicate across as well. If it does not conform to the wildcards, or if no wildcards are used, then the following steps will apply:
Assumption is, the schema exists on the source and has data in it.
1.Make a copy of the currently active config file, edit the copy to include the new schema in it.
2.On DR site, make a copy of the currently active config file, edit the copy to include the new schema in it.
3.You will need to make sure that the SharePlex Export process is stopped on DR site as we may need to remove its content if the creation of new schema in the target database involves loopback of the messages to the Primary. Normally that should not be the case unless the parameter SP_OCT_REPLICATE_ALL_DDL is set to 1 on DR site.
4.Create the schema on the DR database using cold Export/Import or any other means.
5.Activate the copy of the config file taken in step 1.
6.Once the activation finishes, stop Post on secondary and then activate the copy of the config file taken in step 2.
7.Once the activation finishes on the DR site, start Post on DR site.
8.Now the schema is replicating both ways. But it still needs to be sync’d.
9.Create a config file containing just the owner name and the table names in it, with or without wildcards. Do not activate the config file as it is only used by the “repair using” command below.
10.On the source sp_ctrl, run the following command:
sp_ctrl>repair using <config file> (where the config file refers to the config file i9n step
11. The schema will now be in sync. If it is very large, then the “repair using” will not be a suitable command and we may need to use other means. You may also need to test it out by adding a test schema in a QA environment.