Consultant Field Note: Resyncing methods for MSA-aware databases
Hot off the Consulting Experience Press!
I have an MSA architecture that uses a Warm Standby Logical pair as the primary source of the data. I was called to migrate the MSA over to a new server onto the Linux platform. When I set the MSA in place I simply dropped the existing MSA configuration and re-implemented the MSA script pointing to the new ASE Server. the MSA subscription used the syntax “use dump marker.” A simple dump and load and I was done.
Well, reality got in the way of the theory. My Customer had an automated script that upon any DSI or replication thread being down, this program would resume any downed connections. In resyncing for MSA using the dump and load method, you must never allow the DSI thread going to the Replicate MSA database to be connected else you will have records being applied before you have done the dump. The result to this unhappy tale it that I was indeed out of sync before realizing I could stop the automated DSI connection script. I was now faced with several options to re-sync the data:
1. Use the replication client program rs_subcmp and bring the records from Primary to Replicate MSA. Additionally I would need to set the dsi_command_convert parameter to have any “inserts” be applied as “deletes followed by inserts.” This is to avoid duplicate records being in the replication queue as well as in the rs_subcmp query.
2. Use the original method of removing the MSA subscription script and doing the dump and load (and this time making sure the automated DSI connection script is down!). This was very briefly described above.
3. Use an alternate resyncing strategy that allows us to synchronize but at the same time not destroy ther integrity of our replication scripts. And also, we need to ensure that the automated DSI connection script is down!
Option 3 is the preferred method we should use. Lets go through an actual scenario. I have changed the Server and Database names (to protect the Innocent):
1. Suspend the Replication Server DSI connection to the database. In the Replication Server: suspend connection to dataserver.database
2. We require that our Replication Server start to remove existing and any future replicated data from the MSA outbound queue until we see a special marker called “resync.” In the Replication Server: resume connection to data_server.database skip to resync marker
3. We need to check for no open transactions in the Replication Server system log. In the Replication Server: admin who, sqm .
4. In the primary database, issue a special rs_marker: execute rs_marker ‘resync database’
5. Check the Replication Server log and look for the following message:
DSI for data_server.database received and processed Resync Database Marker. Waiting for Dump Marker.
6. Now we can dump the primary database.
7. Check the Replication Server log and look for the following message:
DSI for data_server.database received and processed Dump Marker. DSI is now suspended. Resume after database has been reloaded.
Note the DSI connection was automatically suspended. When we do our load we must ensure that the DSI thread is not connected to the MSA replicate database server. This is why we need to stop and automated scripts to stand up and downed DSI connections.
8. Now we can load the dump into the replicate MSA database.
9. Resume the DSI. In the Replication Server: resume connection to data_server.database
10. We are done.
This Field Notes Series is dedicated to observations from the field taken from personal consulting experiences. The examples used have been created for this blog and do not reflect any existing SAP Customer configuration.
Hi Mark...Good to hear from you! And yes - disabling the automated dump database/tran must be done as we want to control this 100%. Thanks for your comments.