cancel
Showing results for 
Search instead for 
Did you mean: 

manage WS and MSA REPLICATION

suznCB
Participant
0 Kudos
339
we will build the MSA replication way from Logical connection of already existed WS toward a replicated database, I still feel some conflicts, would you help please to understand some issues

When creating replication definition, if we add "replicate DDL" as follow:

create database replication definition repdef

with primary at Logicalconnectionname.databasename

replicate DDL

Does it mean that the replication will be bidirectional??

The already existed WS replication replicates the whole database by setting sp_reptostandby ‘all’

I want to replicate the same database and its transactions to the MSA subscription but don’t want them to return to the replication definition

- The WS is managed by a replication server, we will build another replication server to manage MSA, after building a route between two rep servers, what should we do with the RSSD database? do we need to update it to be replicated, and define a rep agent for it??

- when installing the rep server that manages MSA using rs_inti, the connection toward both rep definition and subscription will be built automatically, is it right? I think it is a physical connection? Do we need a logical connection in MSA as we will use it as standby application (i.e the subscription will not use unless we did a switch role to it)

- while installing a rep server when we define the primary database, do we add the logicalconnection name.the databasename?

- Would you please suggest some tutorials about doing switch over in MSA, the switch over in WS replication is simple, I think it is not in MSA.

Accepted Solutions (1)

Accepted Solutions (1)

Mark_A_Parsons
Contributor
0 Kudos

Addressing the most recent comment/question about enabling replication out of the RSSD (required if parent RS instance is to be the source of a route) ...

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

Two preparatory steps to make life a bit easier:

1. sub_daemon_sleep_time (RS config)

Reconfigure both RS instances to process the create route command in a more timely manner; in each RS run the following:

configure replication server set sub_daemon_sleep_time to '10'
go

... afterwards each RS instance will need to be bounced (when convenient).

create route causes ~15 subscriptions to be created and reducing the sub_daemon_sleep_time from 120 (seconds) to 10 (seconds) means the create route command will complete more quickly; alternatively don't reconfigure this setting but keep in mind the create route could take a few minutes to complete.

NOTE: technically this config change only needs to be made in the target RS but it doesn't hurt to go ahead and make the change everywhere (eg, in case you decide to create a route in the opposite direction); this config change also helps in the scenario where a large volume of (table/database) subscriptions need to be created in a short period of time

2. rsprim (RS primary user login/password)

While running create route the two repservers will talk to each other via their 'primary' user logins (default is rsprim); each RS instance will use its local 'primary' user (rsprim) and password to connect to the other RS, so the same login & password must be avalable in both RS instances.

NOTE: This assumes both RS instances have been setup with the same 'primary' user login (default is rsprim); if different names are in use then make sure both logins have been created in both RS instances (making sure each login's password is the same in both RS instances; making sure both logins have the same user permissions).

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

Steps to enable replication of the RSSD depends on which type of RSSD is in use.

eRSSD (embedded RSSD using SQL Anywhere)

An addiitonal prerequisite is to define a repagent entry in the interfaces file, default name is RSname_eRSSD_ra (eg, RS401_eRSSD_ra); the host should be the same as the associated RSname and RSname_eRSSD entries; just pick a port # that is not in use, eg:

RS401
        master tcp ether ubu20a 9500
        query  tcp ether ubu20a 9500
RS401_eRSSD
        master tcp ether ubu20a 9505
        query  tcp ether ubu20a 9505
RS401_eRSSD_ra
        master tcp ether ubu20a 9510
        query  tcp ether ubu20a 9510

Simply running create route should be sufficient to accomplish the task; RS should automagically configure the SQLAnywhere/eRSSD database for replication and perform all the steps to get it up and running; out in the OS there should be a new process that includes the string 'ltm' (short for log transfer manager - an old name for rep agent).

In the RS intance admin who should show an active REP AGENT thread for the eRSSD connection.

RSSD (stored in ASE)

There are a few steps needed to get replication working for a ASE/RSSD ...

-- in the RS instance:

alter connection to <ASE_name>.<RSSD_db_name> set log transfer on
go

-- in the ASE/RSSD database:

sp_config_rep_agent enable,<RSSD_db_name>,<RS_ra_login>,<RS_ra_pwd>
go
sp_start_rep_agent <RSSD_db_name>
go

If everything works then sp_who should show an active REP AGENT spid in ASE, and admin who should show an active REP AGENT thread for the RSSD connection.

At this point it should be possible to run create route.

NOTE: keep any eye on all log files; if there are any issues with any of these commands then details should show up in the various log files.

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

To verify the create route succeeded the following should show an Active route in both RSSDs:

-- log into RS and connect to associated RSSD:

connect
go

-- check status of routes:

rs_helproute
go
      -- once 'create route' completes the route should show as Active, eg:

      RS401 ----> RS400    Active

Once the route has been successfully created the contents of the RSSDs will be kept in sync automatically via normal replication; as with other replicated databases it will be necessary to monitor for any issues that cause a RS thread to go down (eg, REP AGENT and DSI threads for the RSSD) and respond accordingly.

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

Don't believe the RSSDs will be kept in sync? Run a simple test ...

-- in the source RS:

create error class test_class
go

-- in both RS instances:

connect
go
rs_helpclass test_class
go 
      -- if replication is working then test_class will show up in both RSSDs
      -- with the source RS showing as the parent of the class

-- in the source RS:

drop error class test_class
go

-- in both RS instances:

connect
go
rs_helpclass test_class
go
      -- if replication is working then test_class will no longer exist in either RSSD
Mark_A_Parsons
Contributor
0 Kudos

When in doubt, try it out! (in dev/test first)

suznCB
Participant
0 Kudos

Thanks for this valuable info

suznCB
Participant
0 Kudos

Dear Mark

Thank you for help

Now we created the route sucessfuly, and created rep def and subscription for MSA,but we are stuck in the materialization stage

We need to take a dump from primary db to load it in subscription db but how WS standby db will affect ?, and how about secondary truncation point between 3 databases, ?

We think to suspend connection from rep server1 to WS standby, and suspend connection from rep server 2 toward subscription db , then take a dump so the trans on primary after dump will remain in rep server2 queue to execute on subscription after load complete, is this enough to syncronize 3 dbs together,

Mark_A_Parsons
Contributor

I'll post some info to your recent question which appears to have similiar questions.

Answers (1)

Answers (1)

bonusbrevis
Participant

Hi,

The 'replicate DDL' clause only means that the replication will not only move data operations (DML - SELECT/INSERT/DELETE/UPDATE) but also DDL, like ALTER TABLE, CREATE PROCEDURE, etc. In this way you can control whether schema changes will replicate to the target or not.

What do you mean by 'but don’t want them to return to the replication definition'? The MSA replication from a logical WS pair is one-way only, from the logical to the MSA database. There is no bidirectional replication unless you define another path back to the logical from the MSA.

When you have more than one repserver, the RSSDs from both repservers are in sync after you establish the route.

You do not need a logical connection for the MSA, a logical connection only exists for Warmstandby pairs.

The physical connection to the DB server that you will use in MSA does have to exist first, like any other database connection.

Here is an example of an MSA connection created for a logical pair

create database replication definition MSA_DBX_dbdef

with primary at prod_standby.prod_DBZ

replicate DDL

replicate tables

replicate functions

replicate transactions

replicate system procedures

and here is its subscription at the MSA target

define subscription MSA_DBX_dbsub

for database replication definition MSA_DBX_dbdef

with primary at prod_standby.prod_DBZ

with replicate at DBX.mydb

subscribe to truncate table

use dump marker

There is no 'switchover' for MSA. Once you activate the subscription, the data will begin moving from the logical to the MSA, only in that direction. You would need to drop the subscription to stop the replication from the logical.

HTH

sladebe
Active Participant
0 Kudos

Re: You do not need a logical connection for the MSA, a logical connection only exists for Warmstandby pairs.

But in your example, you create the subscription off of the logical connection (which is the typical thing to do). Your "you do not need" is referring to an edge case. The normal thing to do is create an MSA subscription off the logical connection (so that if you switch active, your MSA replication doesn't need to be changed)

Also, just to clarify for the person asking the question, "define subscription" and "use dump marker" mean that you'll initialize the new subscription replicate using a db dump from the primary that gets loaded to the new MSA subscription replicate.

suznCB
Participant
0 Kudos

Thanks for replying

Referring to your answer: "When you have more than one rep server, the RSSDs from both rep-servers are in sync after you establish the route.",

do they sync automatically? as I remember when we installed the first replication server in the past by rs_init, there were a check box to make RSSD replicated, and we didn't check it cause we didn't have another replication server, but now as we develope our replication system, we think we should update that config related to RSSD, but we don't know what is the suitable command to do that?

also, how can we check that the two RSSDs are really synchronized?

Related to 'switchover' , we are planning to move to MSA as a disaster recovery solution when the WS is down, (maybe the data center where the WS servers is damaged,) we can move to MSA as its related servers (second rep server and the ASE server ) in the far distance second data center, so what are the correct steps to do that?

Regards

suznCB
Participant
0 Kudos

Thanks for replying