Question Re: switch to MSA subscription in Technology Q&A

switch to MSA subscription

suznCB — Sun, 04 Sep 2022 11:49:23 GMT

Dears,

what are the steps should we do to switch over our active database in MSA replication, we set MSA replication between active database (replication definition) and a standby database (replication subscription) , but we didn't found tutorials to help us how to make that subscription the new active database, if this is the case, should we drop MSA replication and keep only one active database or we cane switch role between definition and subscription as we do by using active switch command in WS pair?

Re: switch to MSA subscription

sladebe — Tue, 06 Sep 2022 16:50:13 GMT

MSA replication (aka whole database repdef subscription replication) is a different thing from warm standby replication.

First you need to setup warm standby replication with a logical connection, active (primary) ASE server and a standby ASE server. For example:

[100] TEST_RS.-12:27:41-1> admin logical_status; -mvert
Logical Connection Name:        [102] LOGICAL_CONN.test_db
Active Connection Name:         [104] PRIMARYASE.test_db
Active Conn State:              Active/
Standby Connection Name:        [105] STANDBYASE.test_db
Standby Conn State:             Active/
Controller RS :                 [16777317] TEST_RS

Then setup a database replication definition specifying "with primary at LOGICAL_CONN.test_db"

Then setup a MSA database subscription. This works if the MSA subscription replicate is already setup and in sync with the primary and the primary is quiesced (no data or schema changes)

create subscription SUB_REPLICATE_ASE_test_db
for database replication definition test_db_repdef with primary at LOGICAL_CONN.test_db
with replicate at SUB_REPLICATE_ASE.test_db
without materialization  -- only if the MSA replicate db is already loaded and primary is quiesced

If you want to setup the MSA subscription replicate using a dump from the primary, without having to quiesce the primary, you'll have to use define subscription with the "use dump marker" option. Make sure there are no triggers in the new MSA replicate db accidentally copied from the primary. Also remember to disable warm standby secondary truncation point options in the loaded-from-primary MSA replicate (use sp_config_rep_agent with the "disable" option)

Ben

Re: switch to MSA subscription

suznCB — Wed, 07 Sep 2022 07:04:29 GMT

thanks for replying

so, in the scenario above, how to switch the primary database to SUB_REPLICATE_ASE.test_db? and connect the clients to it?

let's say that WS pair (PRIMRYASE.test_db, STANDBYASE.test_db) in the same location, and some disaster is occurred, so we want to move the work to the SUB_REPLICATE_ASE.test_db which is in a far location.

in another scenario:

if PRIMRYASE.test_db is down, and we switch to STANDBYASE.test_db, and broke the WS pair,

here we should re-setup the subscription as

create subscription SUB_REPLICATE_ASE_test_db
for database replication definition test_db_repdef with primary at STANDBYASE.test_db
with replicate at SUB_REPLICATE_ASE.test_db

here, we want to test the switch work from STANDBYASE.test_db (which is the only active database) to a SUB_REPLICATE_ASE.test_db (this case will be required when STANDBYASE.test_db is down )

so, what are the steps here to do this switching?

in both scenarios,should we drop MSA replication and connect clients to SUB_REPLICATE_ASE.test_db ??

Regards

Re: switch to MSA subscription

sladebe — Wed, 07 Sep 2022 15:48:30 GMT

Re: let's say that WS pair (PRIMRYASE.test_db, STANDBYASE.test_db) in the same location, and some disaster is occurred, so we want to move the work to the SUB_REPLICATE_ASE.test_db which is in a far location.

Basically, you would then have to re-setup replication from the beginning with SUB_REPLICATE_ASE.test_db as the new primary (err.. "active"). Remember that you can setup the new warm standby via dump load without quiescing the new primary if you use the dump marker. Also, you can setup the new subscription replicate (where ever that might be) using define subscription and the dump marker without quiescing the new primary.

Re: if PRIMRYASE.test_db is down, and we switch to STANDBYASE.test_db, and broke the WS pair,

If PRIMRYASE.test_db goes down and you run "switch active" to make STANDBYASE.test_db the new primary/active, you can then drop the (now standby) PRIMRYASE.test_db connection. You'll have a warm standby logical connection without an actual warm standby ASE server (that's allowed). If your MSA whole database repdef/subscription was setup to use the logical connection as the source (LOGICAL_CONN.test_db), it won't care. It will just keep replicating off the logical connection. You don't have to re-setup the MSA subscription.

Re: switch to MSA subscription

suznCB — Thu, 08 Sep 2022 06:48:40 GMT

Thanks for help

Re: Basically, you would then have to re-setup replication from the beginning with SUB_REPLICATE_ASE.test_db as the new primary

actually here we will face one of two cases:

the first one is to re-setup the MSA as:

create  replication definition  repdef   with primary at SUB_REPLICATE_ASE.test_db

but how define subscription with replicate at that logical connection? Is it possible?

The second case, when a disaster is occurred in the first location where WS pair is, we will move to SUB_REPLICATE_ASE.test_db which will be the only database that we have, here at the replication server , we should drop the subscription that is replicated to SUB_REPLICATE_ASE.test_db, and drop the physical connection to it, and connect clients to the SUB_REPLICATE_ASE.test_db,

Then we can plan to rebuild our replication system as fail back procedure, do I miss something in those scenarios?

Re: You'll have a warm standby logical connection without an actual warm standby ASE server (that's allowed)...

but after a while the Replication server stable queues will be full and the size of active database transaction log will be increased because of truncation point, here we need to drop the whole WS, drop connections to both standby and active databases, also drop logical connection, and as a result we will have just one active database STANDBYASE.test_db which is not belong to any replication system, Here we want to re-setup a new MSA between it and the SUB_REPLICATE_ASE.test_db in far location.

Regards

Re: switch to MSA subscription

sladebe — Wed, 28 Sep 2022 13:49:32 GMT

"the first one is to re-setup the MSA as:
create replication definition repdef with primary at SUB_REPLICATE_ASE.test_db"

I'm not sure I understand what you're trying to do.

You have:

Warm standby logical connection LOGICAL_CONN.test_db
For that warm standby logical connection you have PRIMARYASE.test_db as the primary/active and STANDBYASE.test_db as the warm standby
You have the database replication definition test_db_repdef with primary at LOGICAL_CONN.test_db
You have the subscription SUB_REPLICATE_ASE_test_db replicating from LOGICAL_CONN.test_db to SUB_REPLICATE_ASE.test_db

Are you trying to make SUB_REPLICATE_ASE.test_db the new active/primary? Which server would then be the new warm standby and which server would be the new subscription replicate?