Has anyone resynchronized their RS MSA using a third-party dump utility (NetApp) ?

[former_member350489]

Hi,

we have a RS MSA setup, replicating ten databases from PDS to RDS, using one PRS and one RRS. We resynchronize the RDB's on RDS by running dump and load, but that takes a lot of time for our larger databases. As we also have NetApp SAN on both sites, we consider to materialize by using snapshots. I just would like to know if anyone of you out there has any experience of this in a similar MSA setup ? There is a page in the document "Repserver admin guide" that explains the different steps involved, but there is no detailed example in it. I am specially interested in the "rs_marker" stuff...

All the best

Mike of Sweden

[former_member350489]

We do generate snapshots already today on our PDS, and the steps we perform are :

1.quiesce database SNAPSHOT hold ${DBLIST2} for external dump to "$MANIFEST"

2.Create snapshot (filer snap create -V ${SNAPVOL} ${SNAPVOL}.latest)

3.quiesce database SNAPSHOT release

We use dump and load today for the materialization, but would like to use snapshot in the future. We do not have the current license for applying the snapshot on RDS today but will hopefully get one very soon.

And the "new features" that you are pointing out are exactly the one I am referring to as well ! There is an uncomplete example, that I would like to know a bit more about, like with a real example ? The section is called "Resynchronizing Using a Thirdd-Part Dump Utility",

[former_member182259]

Mikael -

If you notice, in the first command, you have a "quiesce database" command.   This blocks all write activity until the third command.    Consequently, you could treat this as if it were a dump/load taken during a period of no activity.    A sequence that might work for you:

1 - sp_stop_repagent <dbname>

2 - quiesce database .....hold....for eternal dump

3 - While it is dumping, clear the queues in RS using:

     a - sysadmin hibernate_on

     b - sysadmin sqm_purge_queue (outbound queue)

     c - admin who, sqt - check for open transactions in inbound queue

     d - sysadmin sqm_purge_queue (inbound queue if and only if no OPEN transactions)

     e - if any open transactions, alter MSA connection to set dsi_command_convert to 'u2di,i2di'

     f - sysadmin hibernate_off

     g - suspend connection to MSA standby

4 - quiesce database ....release

5 - sp_start_repagent <dbname>

6 - mount database in MSA standby

7 - online database in MSA standby

8 - truncate rs_lastcommit in MSA standby

9 - reconfig rep_agent in MSA standby (e.g. change connect source parameters)

10 - resume connection in RS to MSA standby

11 - if there were any open transactions, do the following:

     a - issue rs_ticket 'back','in','sync' at primary

     b - wait for ticket to arrive in MSA replicate (rs_ticket_history table)

     c - suspend connection to MSA standby

     d - alter MSA connection to set dsi_command_convert to 'none'

     e - resume connection to MSA standby

...complete!!!

The issue with trying to use the RS generated markers that you were thinking of is that the rs_marker command that is sent for the dump database/dump tran includes the log pointers, which RS then uses to purge any committed transactions it has prior to that log pointer.   Finding out what that value should be might be kinda fun.....   *however* if you turn on trace for the repagent ala:

sp_config_rep_agent <db_name>, 'trace_log_file','<filepathname>'

sp_config_rep_agent <db_name>, 'traceon', '9201'

-- monitor for a few minutes 

sp_config_rep_agent <db_name>, 'traceoff', '9201'

During a test prior to restarting the repagent at step 5, it would be interesting to see if it doesn't send the rs_marker for the dump database anyhow.   If it *does*, then all you have to do is follow the resync steps in the RS 15.7 manuals and substitute your snap for the dump and problem should resolve.   Those steps generally are:

1. Stop replication processing by RepAgent. In primary ASE, execute:

          sp_stop_rep_agent <activedb>

2. Suspend the Replication Server DSI connection to the replicate database:

          suspend connection to <standby>.database

3. Instruct Replication Server to remove data from the replicate database outbound queue and wait for a resync marker from the primary database RepAgent:

          resume connection to <standby>.database

          skip to resync marker

4. Instruct RepAgent to start in resync mode and send a resync marker to Replication Server:

a. If the truncation point has not been moved from its original position, in Adaptive Server execute:

          sp_start_rep_agent <activedb>, 'resync'

b. If the truncation point has been moved from its original position (e.g. you disabled the trunc point due to long outage), in primary ASE execute:

          sp_start_rep_agent <activedb>, 'resync purge'

5. In the Replication Server system log, verify that DSI has received and accepted the resync marker from RepAgent by looking for this message:

     DSI for data_server.database received and processed

     Resync Database Marker. Waiting for Dump Marker.

Note: If you are resynchronizing multiple databases, verify that the DSI connection for each of the databases you want to resynchronize has accepted the resync marker.

6. Dump the primary database (using whichever technique you wish)

7. Verify that Replication Server has processed the dump database marker by looking for this message in the Replication Server system log:

     DSI for data_server.database received and processed

     Dump Marker. DSI is now suspended. Resume after database has been

     reloaded.

8. When Replication Server receives the dump marker, the DSI connection automatically suspends.  Verify DSI is suspended by using admin who_is_down

9. Load the dumped database in the standby server and bring the database online

10. Truncate the rs_lastcommit table, rs_ticket_history table and verify suid/userid match

11. After you apply the dump to the replicate database, resume DSI:

     resume connection to data_server.database

[former_member350489]

Hi Jeff,

thanks a lot for that detailed answer of yours !

We will definitely try this as soon as we get the correct license for the NetApp.

Do you know if one can use your approach for mulitple databases at the same time, or do we have to do them one at a time insequence ? The reason I ask is that when I initially setup our MSA environment, the RS could only handle one "marker" at a time. And that is actually how we materialize even today, in sequence, one dump/load at a time...

Thanks

/Mikael

[former_member182259]

Not sure about the rs_marker issue.....that is something that should be reported to support if you can repro.   However, on the database side, there used to be a restriction on the manifest file that the devices being mounted/unmounted for mount database could only affect a maximum of 8 databases.  Not sure if this affects quiesce for backup....it might as the flipside is re-mounting the snapshots.  

[former_member350489]

Ok thanks, I will contact support if/when we hit problems during our tests of the markers in that case.

The restriction however in the manifest file is no more, since we have quiesced, snapshot'ed, copied and mounted more than eight databases when reloading our test environments with snapshots here for a long time (running on ASE 15.7).

[kimon_moschandreou]

Hello,

I have done something similar some time ago. In that case the primary database was freezed and the restore on the replicate side completed, so there was no needed any materialization. Mark has already pointed out how you should work on this

Regards

[former_member188996]

Hi,

Wondering if you have considered direct load materialization feature.  With SRS 15.7.1 SP102, we have provided direct load materialization for ASE to ASE.  It is used with subscriptions to table replication definitions.  Multiple parallel threads can configured to load data from one primary table to its corresponding replicate table.  Materialization progress can be monitored.  The best part is replication to other tables is not suspended during direct load materialization.  It means completely zero downtime materialization. 

Please let me know if direct load will suit your needs.

Regards,

[former_member350489]

Hi Bill,

thanks but I don't think this will be an option, since this probably will take at least the time it takes to dump and load ? And since we replicate our entire databases (we run MSA setup) we just aim for a very fast materialization method of one datyabase at a time.

Cheers

Mikael