How to create replicate database while not suspending activity on primary

[Former Member]

I am having a problem reconciling two opposites - not stopping activity on primary database, and creating backup which should be loaded into different (cross) platform. Cross platform migration has a requirement that database which has to be dumped need to be transactionaly quiescent, but I Sybase document ASE-to-ASE Replication Quick Start Guide, in chapter Materialization and Resynchronization, Scenario 3, is saying that "It is assumed that activity cannot be stopped at the primary database while the database dump is in progress."

So, what to do in this situation?

[former_member3043 · Accepted solution]

Hello,

one idea might be the following:

Example:

ASE Linux needs to be migrated to ASE AIX.

First setup MSA replication from ASE Linux to ASE AIX, use the dump marker for the subscription.

The replication won't start until you run a dump database.

Setup a second ASE Linux, where we will do the XPDL commands.

Now dump the database on your production ASE Linux, the dump marker will be given to RS and all changes not captured with this dump will be moved into the inbound / outbound queue. Since the DSI is down, nothing is replicated.

Now load this dump into your second ASE Linux and do your XPDL commands (as described in the manuals). When this is done, dump this database and load it into your ASE AIX and bring it online.

And finally resume your DSI connection again.

The disadvantage is that the outbound queue in the RS needs more space to keep all the transactions and it might take some time before the systems are in sync.

You should test this with a small database so you are familar with the procedure.

Hope this will help.

Best regards,

Stefan

[former_member182259 · Accepted solution]

Stefan's answer is the best answer.   Further, should you not wish to do the XPDL (e.g. you want to change the pagesize), you can bcp out from the intermediate server and bcp into the target.   Then add the minimal pkey indexes, resume the DSI and let it catch up before creating the other indexes (which will block IUD activity unless using ASE 15.7 SP100+ with online index creation capability).  RS doesn't really care how the data got into the replicate - whether loaded via a db dump or whether bcp'd....it just uses the dump marker to know which changes to apply.    Be careful and make sure only ONE dump marker is sent - e.g. disable any thresholds or automatic dump trans during this period that also could be an issue.

Alternatively, you would need to go table by table via bulk materialization and use autocorrection (or better yet the dsi_command_convert variant)...but this can be a bit of a pain if the number of tables are very large....or if there are a lot of indexes (especially clustered indexes on APL tables - remember, 15.0.2+ does allow a sort-of fast bcp on DOL tables with indexes)

[former_member468357 · Accepted solution]

I know it's been a few years since this questions was asked, but we have a similar situation to the original poster.

The difference between the poster and us, is we can afford some downtime to do our dumps.

I find that Stefan's answer is really good, basically gives you the option with no downtime.
But what would be the best solution if downtime is an option? Let's say at least downtime long enough to dump all the DBs while the system is quiesced.

Should we still use dump marker with the cross-platform dump and load right on our actual primary?

I've read that dump marker might not work with the cross-platform dump and load, because the DBs have to be in single user mode to flush the stats before doing the dumps, and while we do this, we need to stop the rep agent on the primary to put the DBs in single user mode. But at that point, before we put the DBs in single user mode for flushstats and dumps, our subscription with dump marker will already be defined, waiting for dump marker. So my question is, is that an option?

Or would it be better, since we have a downtime, to just create subscription without materialization, keep the DSI connection to the replicate suspended, do our dumps while the system is quiesced (with single user and flushstats)?

Basically, after we have all our dumps, the system can go back online, and all the transactions are going to wait in the outbound queue of the replicate DSI connections. During this time, we load the DBs on the replicate, do what we need to be done after an XPDL load and resume the DSI connection when ready.

Would that be a better option? And are both options possible?

thanks!

[former_member468357]

I potentially have a solution when you want to migrate an MSA environment cross-platform doing dump and load without using dump marker, or resync marker and without any downtime on the primary server.

But for this solution to work, you environment should already have a replicated server.
And you have to be sure that the copies of the databases on this replicated server are exactly the same as the primary data server.

So you have PDS1 ----> PRS -----> RDS1

You want to add a new standby PDS2 to this setup, but this new server is different platform with a different endian. This server will eventually become the new Primary.

1.) create a connection to PDS2.

2.) Stop the Rep Agent on PDS1 for the DB you want to migrate to PDS2

3.) Create a DB subscription from PDS1 to PDS2 without materialization. Subscription should become valid right away.

4.) Suspend the distributor from PDS1 in PRS

5.) Restart the Rep Agent on PDS1. At this point all the transactions are going to pile up in the inbound queue in PRS since the distributor is suspended.

6.) Suspend the connection to PDS2

*** Before doing the dump mentioned below, make sure that the queue to RDS1 is flushed.
This is important as we don't want to miss any transaction with the dump.

7.) Take a dump from RDS1. Here you need to do single user and flushstats since it will be a cross-platform dump and load, but it's not a problem since it's a standby. There should be no activity other than the replication on that box.

8.) As soon as the dump is complete, you can resume the distributor from PDS1, RDS1 will start receiving the transactions and it will go to the outbound queue for PDS2 since the connection is suspended.

9.) Take the dump and load it on PDS2.

10.) After the load, do sp_post_xpload or what you need to do after the XPDL.

11.) If you want this server to replicate back to PDS1, configure with MSA/repdef/subscription.

12.) resume the connection to PDS2 and wait for the queue to flush. Now your DB should be the same on all 3 servers and PDS2 will be replicated from PDS1.

[former_member89972]

In RS 15 SP306, there is new flavor of subscription called Direct Load.

No down time needed.

Thus for a table create normal Replication definition and for subscription direct load option below

REPDEF

create replication definition S_36_dbo_DBA_TIME_rd

with primary at PHI2_DEV.SPIN_DV3

with all tables named dbo.'DBA_TIME' ( "THEID" bigint, "THETIME" bigdatetime )

searchable columns ("THEID")

replicate minimal columns

go

REPSUB

create subscription T_07_dbo_DBA_TIME_sub

for S_36_dbo_DBA_TIME_rd

with replicate at PHI2_DEV_8K.SPIN_DV3

without holdlock direct_load

user sa

password 'XXXXX'

subscribe to truncate table

go

This creates a temporary queue for changes from the time load starts and applies them after the initial load is done.

There option to start mutiple connections/threads to pull in parallel but I found single thread avoids locking contention on target side.

HTH

Avinash

[sladebe]

This is an ancient question, but since Luc has recently asked about it, here's my help:

Take a look at:

help.sap.com -> Admin Guide Vol 2 -> Replication System Recovery -> Replicate Database Resynchronization for Adaptive Server -> Database Resynchronization Scenarios -> Resynchronize One or More Replicate Databases Directly from a Primary Database -> Resynchronizing Directly from a Primary Database

The help page doesn't actual say it, but the whole point of the way it's re-sync'ing is to permit updates to the primary database while the re-sync operation is taking place.

Basically, the rep_agent sends a resync dump marker through the replication system

[kimon_moschandreou]

Hi Vojislav,

This is interesting indeed. I have done this in the past in an MSA environment where everything was on wondows and we added a node having ASE on Linux and the database was synchronized using dump marker. All versions were in early 15, ASE 15.0.3 for sure ,RS maybe 15.0. We did it while the users were working, however the activity was not  heavy.

Concerning the cross platform dump/load in practice I have seen many times this process working without having quiesced the databases, however sometimes it doesn't. Ant for sure it is working if there is no transactional activity while dump is running. I also think that endian plays a role here.

I shall check again the manuals also on this.

Best regards,

Kimon Moschandreou

[Former Member]

give up using dump and load.

setup replication table by table.