make database subscription a standby in MSA

[suznCB]

Dears, kindly help in the following:
we have a MSA replication between two databases where the whole primary database is set as database replication definition, yesterday, someone logged in to the subscription databases and added some records to one of its table which is replicated , we think the replication should be stopped related to data integrity issue, but the replication still up and no thread become down!

how can we set the subscription to be a standby database like in Warm standby replication?

we need to move to that subscription manually only when the primary database is unavailable due to a disaster

Regards

[Mark_A_Parsons · Accepted solution]

To keep these types of issues from occurring the DBA will need to implement some level of security to keep unauthorized users from making direct data changes in the replicate database, eg:

• lock logins in the replicate dataserver; if replicating the master database you'll need to insure new logins are also locked in the replicate dataserver; only unlock the logins if/when a 'switch' operation is performend and the dataserver now becomes the primary; this gets trickier if you also happen to have logins that should remain locked at all times
• do not provide the network address (of the replicate dataserver) to unauthorized users; only publish the network address if/when a 'switch' operation is performed and the dataserver now becomes the primary
• start the replicate dataserver on a private/different version of the interfaces file; during a 'switch' operation either bounce the dataserver and bring up on the publicly available interfaces file or manually start additional listeners on the publicly known addresses

This same issue (unauthorized users modifying the replicate database) is not limited to MSA setups; this same issue can occur in WS setups, too.

---------------------

The replication server has no concept of 'data integrity'.

While the replication server software does come with some tools (rs_subcmp, Data Assurance (DA)) that can be used to assist with 'data integrity', it is up to the (replication) DBA to determine if/when/how to use these tools.

---------------------

re: 'how can we set the subscription' and 'we need to move that subscription' ...

Subscriptions have nothing to do with unauthorized access to the replicate database.