Syncronizing primary and multiple standby database (WS + MSA)

[suznCB]

Dears ,

kindky help in this critical issue

We have WS pair managed by rep server 1

db1 is primary database

db2 is standby database

We want having another standby db3 , we are planning to build MSA from WS logical connection, MSA will be managed by rep sever2 so we created a route between two rep servers, and checked connection between two rep servers, and checked that rssd 1 is replicated into rssd2(in rep server2)

Now, We will do the following:

create database replication definition repdef with primary at <logical connection name.db>

Create physical connection to db2

define subscription sub to db3 with primary at .... replicated to db3 use dump marker

Then dump db1 and load it into db3

BUT , the deep question is how to resync the three databases ?

Are there some steps to do before taking a dump from db1 , like what we do in WS when we reset secondary truncation point and do rs_zeroltm at rssd?

If there are, docthose steps will affect on secondary truncation point in WS db2?

And what the steps should we do after load db1 dump into db3?

For any failuer in one of those dbs

Is there a scenario to resync either WS or MSA independently, or we should resync them together, i mean when db2 is out of syncronization, do we need to resync db2 and db3 , or db3 shouldn't affected

and the same question when db3 is out of sulyncronization?

any hint will be useful, please

Best regards

Also how can we

[sladebe]

Re: define subscription sub to db3 with primary at .... replicated to db3 use dump marker
Should this be?

define subscription sub to db3 
FOR DATABASE REPLICATION DEFINITION myrepdef
with primary at .... replicated to db3 use dump marker

[Mark_A_Parsons · Accepted solution]

As I mentioned in a recent comment it would be of benefit to review the syncronization scenarios addressed in the manuals:Replicate Database Resynchonization for ASE

If you're running Repserver 15.6+ then I'd skip all the issues with subscriptions and dump markers and simply:

• create subscription / without materialization
• suspend connection to replicate.db
• resume connection to replicate.db skip to resync marker
• [in the PDB] execute rs_marker 'resync database'
• dump primary dafabase
• load dump into replicate database
• resume connection to replicate.db

This is covered in the previously mentioned link to the manuals, in particular the sub-section titled Resynchronizing if There is No Support for the Resync Database Marker

Perform the above steps for each replicate database that you need/want to resync. [Yes, you can resync multiple replicate databases at the same time ... after issuing the resume connection / skip to resync marker for each replicate database then you only need to issue a single rs_marker 'resync database' followed by a single dump of the primary database.]

The truncation point should only be active in a database that is being replicated out of. For the purpose of resyncing a replicate database there should be no reason to reset the truncation point. And since rs_zeroltm is used in conjunction with resetting a truncation point, there should be no reason to run rs_zeroltm if all you're doing is resyncing a replicate database.

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

As for post load database operations in the replicate database this gets a bit more complicated as it depends on whether or not server level credentials (logins, roles, etc) are in sync between the primary and replicate master databases; this doesn't necessarily mean the master databases must be part of replication but the post load database steps are more detailed if the master databases are not in sync.

If the database dump comes from a database that is serving as the primary database in a replication scheme then after running load database and online database it will be necessary to disable the repagaent and truncation point (both brought over in the database dump) in the newly loaded database; sp_stop_repagent and sp_config_rep_agent/disable should be sufficient. [Alternatively, sp_stop_rep_agent and dbcc settrunc(ltm,ignore) should also be sufficient; actually, this may be preferred ... I don't recall if the switch active command knows how to configure a repagent or if it assumes the repagent is already configured ... should probably run some tests to verify this]

In the replicate database it will also be necessary to insure database level credentials (ie, user, aliases, roles) are in sync with the local dataserver's credentials (ie, logins, roles); unfortunately neither Sybase nor SAP has ever taken the time to provide a method for performing this particular step of syncing dataserver and database credentials (to be honest this operation must be performed when copying databases between any two dataservers ... not just for replicate database resyncs); this operation requires understanding the relationship between dataserver level login and role ids and how they are mapped to database level user and role ids (think suid vs uid, srid vs lrid), and gets (more) complicated when the set of logins/roles is not identical between dataservers (eg, production vs dev/test); invalid/incorrect mappings between dataserver and database credentials can lead to users being unable to use a database and/or accessing a database under someone else's login and/or having scrambled permissions. The 'simple' solution is to have a complete list of all DDL to recreate all users, aliases, groups, roles and permissions in the database after the load database and online database commands have been completed.

[suznCB · Accepted solution]

Thanks in advance

Just to check that I know what we will do:

when we dump primary db with resync marker , do we should load it on db2( tha stand by database in WS pair if it already sync with primary) or no need to do that?

do we should suspend connection to db2 or not? For transactins that are done after dump to not truncate them from primary db log if they transfer to db2

Do you advice for performance issue to use multiple scanner for rep agent in primary database?

We suppose that if WS is syncronized (db1 and db2) we can re sync db3 only.

Regards

[Mark_A_Parsons · Accepted solution]

The steps outlined in the answer (above) are to be applied for each database that is out of sync; if db2 is not out of sync then leave db2 alone (do not resume connection / skip to resync marker, do not load the db1 dump into db2). Leave db2 alone and transactions should continue to flow (from db1 to db2) as normal.

If db3 is the only database that is out of sync then perform the steps outlined in the answer (above) for db3 and only db3.

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

As for the other questions ... multiple scanner for repagent ... db repdef issue ... for each new/different issue I suggest you ask a new question.

[suznCB · Accepted solution]

thanks a lot for your explaning deep issues

The last question please,

When we built WS in the past we created logical connection using dump marker

And for each time we resync db2 in the past we did the steps that related to dbcc settrunc ltm , ignore then valud then in rssd rs_zeroltm

So we will suppose that for the step

[in the PDB] execute rs_marker 'resync database"

It will not affect the dump marker send previously to db2, right?

But for future issues when maybe need ro resync the 3 databases, we will do the steps you mentioned above , is there a conflict with first dump marker we were use in the past when we built WS ? and can we be sure that rep server 15.7 manages truncation point internally ,

The issue is critica for us, and it is the first time we work deeply in replication system

Thanks for help

[Mark_A_Parsons · Accepted solution]

You can continue to resync db2 (WS/replicate db) as you've been doing but keep in mind that each time you reset the trunc point you're risking the loss of transactions flowing to db3 (ie, you'll want to resync db2 and db3 if you insist on resetting the trunc point); if you can guarantee with 100% certainty that there are *NO* transactions being created in db1 at the time then you could forgo resyncing db3 ... but you're asking for trouble if you're not 100% sure there is no/zero/zip/zilch transactional activity in db1 while modifying the trunc point.

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

The 'resync' steps I outlined in the parent answer (above) can be used for resyncing an individual database without the need to modify the trunc point; this is true for a single replicate database, for multiple replicate databases, for WS/replicate databases, for MSA/replicate databases.

The only time you should need to reset the trunc point is if you managed to break replication in the pdb (eg, pdb log fills up and replication has to be broken so that pdb activity doesn't come to a screeching halt).

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

As for the 'dump marker' originally used to sync db2 ... that 'dump marker' has long since been processed by the DSI for db2 and discarded; said 'dump marker' no longer exists anywhere in the replication system; that 'dump marker' is a one-time string sent through the replication system and processed (in near real time) by the DSI for the target/replicate database.

[suznCB · Accepted solution]

Mr. Mark

Reffering to the link

Resynchronizing if There is No Support for the Resync Database Marker

We did the steps and the first mentioned message appeared on rep server log which tells that it is waiting for dump marker, but in taking a dump step we didn't find the second mentioned message in the rep server log and the dsi connection didn't suspend automatically, so we suspended it manually but after load the dump and resume connection we found that there where transactions in the rep server queue they transfered to subscription database althogh the same trans were hold in the dump and already applied on this subscription db so we faced duplicate issue,

Would you advice why rep server didn't receive dump marker after it received the resync marker ?

and as they mentioned in the step 3 there should not be open trans in system log of primary, but in our case we want to do resync without stop users activities on primary database, would you advice please ?

The last issue is:

Reffering to the link

https://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc01251.1571200/doc/html/n...

The steps is aware of truncation point !

Which one of the tow manuals is more suitable to do in our case?

[Mark_A_Parsons · Accepted solution]

I've got a WS<-->MSA setup similar to what you're using.

I had no problems applying the resync steps for both the WS/standby as well as the MSA/replicate db's (I ran each one separately) ... suspend/resume DSI ... DSI waiting for resync marker ... rs_marker/resync database ... DSI waiting for dump marker ... dump database ... DSI receives dump marker and suspends ... load/online database ... manually resume DSI ... verified no lost transactions.

Did your dump complete successfully? Can you verify you dumped the WS/active db and not some other database?

[Mark_A_Parsons · Accepted solution]

As for the question about the truncation point and that link to infocenter.sybase.com ... pay attention to what's actually said: "10. Release the secondary truncation point from the replicate database" ... key word being 'replicate' database.

That link is referring to the fact that when you dump a primary database the trunc point is brought along with the database dump which means that after you load that dump into the replicate database you must drop the trunc point from the replicate database.

I covered this same topic in my answer (above); see the section that starts with: "As for post load database operations in the replicate database..."

[suznCB · Accepted solution]

"Did your dump complete successfully? Can you verify you dumped the WS/active db and not some other database? "

Yes, dump is done successfully

We dump the active database in WS

Did you stop user' activities on active db each time you resynced dbs( in particual during dump process)? If not, how did you control the issue to not faced duplicate issue?

[Mark_A_Parsons · Accepted solution]

There's no need to suspend user activity in the primary database; if your DSI reported dup transactions (into the replicate database) I'm assuming that was because of how/when you manually suspended the DSI; resolving the issue of why the DSI didn't see the dump marker would probably also address the dup issue.

[suznCB · Accepted solution]

Dear Mark,

referring to our problem related to not receiving dump marker by DSI, I think the source of this problem is the connection that we created to subscription db, as we ran:

create connection to db3
set error class to es_sqlserver_error_class
set function string class to rs_sqlserver_function_class
set username maintenanceUser
set password maintenanceUserPS

without adding "use dump marker"

WE droped that connection and recreated it,

as follow

create connection to db3
set error class to es_sqlserver_error_class
set function string class to rs_sqlserver_function_class
set username maintenanceUser
set password maintenanceUserPS
with dsi_suspended
as standby for LOGICALDS.LOGICALDB
use dump marker

We got an error because of referring to logical connection in rep server2 while we should use it in the rep server that control that logical connection

So we recreated it again without useing dump marker and ran

define subscription db3 .. use dump marker 

and continued the Resync strps but still facing the same issue that DSI didn't receive dump marker and didn't suspend automatically!

I was focusing on this dump marker to help dsi to receive it in some way, but I failed.

I will investigate in this issue, if I success I will repost the answer here for other guys.

Thanks for your comments

Regards