SQL Anywhere database replication - Adding another server to replication

Author

Adding another server to replication

Edgard L. Riba

2005-10-27, 7:41 am

Hi,
I have right now a two server replication going on. The consolidated
server is called RS and the remote is called BV. I now need to add
another remote server called MP.

Here is what I've planned to do.

1) Make a copy of the consolidated database and rename it to MP, also
renaming the log file.
2) Delete the publisher, remote user, and message type, but leave the
publication identical.
3) Run the following script on the new remote:

// (a) Create a SQL Remote Message Type
CREATE REMOTE MESSAGE TYPE FILE ADDRESS
'\\\\192.168.1. 7\\D\\INV\\DATA\\MP'
;
// (b) Create users involved in replication
GRANT CONNECT TO MPUser IDENTIFIED BY MPPwd ;
GRANT CONNECT TO RSUser IDENTIFIED BY RSPwd ;
// (c) Grant proper user rights
GRANT PUBLISH TO MPUser ;
GRANT REMOTE TO RSUser TYPE FILE ADDRESS
'\\\\192.168.1. 7\\D\\INV\\DATA\\RS'
SEND EVERY '0:15' ;
// (d) Create Publication
// Done...
// (e) Create Subscription
CREATE SUBSCRIPTION TO RSPub FOR RSUser ;

4) Do the following on the consolidated:
GRANT CONNECT TO MPUser IDENTIFIED BY MPPwd ;
GRANT REMOTE TO MPUser TYPE FILE ADDRESS
'\\\\192.168.1. 7\\D\\INV\\DATA\\MP'
SEND EVERY '0:30' ;
CREATE SUBSCRIPTION TO RSPub FOR MPUser ;

This seems to me covers everything with respect to setting things up.

The questions would be:

1) How do I get the replication going? Is the following ok?
// On the consolidated:
START SUBSCRIPTION TO RSPub FOR MPUser;

// and on the remote:
START SUBSCRIPTION TO RSPub FOR RSUser;

2) I want to test the replication for a couple of days at the consolidated
site. WHen I'm done, I'd like to synchronize the subscription just to make
sure that everything is on the new remote. How do I do this? I read in
the manual the following
SYNCHRONIZE SUBSCRIPTION TO RSPub FOR xxxx;

But where should I run this, at the consolidated or at the remote site?

Thanks in advance,
Edgard

Rob Waywell

2005-10-27, 7:41 am

> // (c) Grant proper user rights
> GRANT PUBLISH TO MPUser ;
> GRANT REMOTE TO RSUser TYPE FILE ADDRESS
> '\\\\192.168.1. 7\\D\\INV\\DATA\\RS'
SEND EVERY '0:15' ;

The second GRANT shoud be GRANT CONSOLIDATED ...

> 1) How do I get the replication going? Is the following ok?
> // On the consolidated:
> START SUBSCRIPTION TO RSPub FOR MPUser;
>
> // and on the remote:
> START SUBSCRIPTION TO RSPub FOR RSUser;
>

We would normally use REMOTE RESET ... for each of these

> 2) I want to test the replication for a couple of days at the
> consolidated site. WHen I'm done, I'd like to synchronize the
> subscription just to make sure that everything is on the new remote.
> How do I do this? I read in the manual the following
> SYNCHRONIZE SUBSCRIPTION TO RSPub FOR xxxx;
>
> But where should I run this, at the consolidated or at the remote site?
>

I don't see any value to doing this since you have already validated your
publication with the existing remote. If you are going to do it, then you
would execute the SYNCHRONIZE SUBSCRIPTION at the Consolidated.

Are both of these remotes Read Only? If not, have you considered any
additional conflict resolution requirements that you may now have with
multiple remotes updating data?

--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports:
http://case-express.sybase.com/cx/c...sc?CASETYPE=Bug

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer
"Edgard L. Riba" <elriba at rimith dot com> wrote in message
news:4351418d$1@foru
ms-2-dub...
> Hi,
> I have right now a two server replication going on. The consolidated
> server is called RS and the remote is called BV. I now need to add
> another remote server called MP.
>
> Here is what I've planned to do.
>
> 1) Make a copy of the consolidated database and rename it to MP, also
> renaming the log file.
> 2) Delete the publisher, remote user, and message type, but leave the
> publication identical.
> 3) Run the following script on the new remote:
>
> // (a) Create a SQL Remote Message Type
> CREATE REMOTE MESSAGE TYPE FILE ADDRESS
> '\\\\192.168.1. 7\\D\\INV\\DATA\\MP'
;
> // (b) Create users involved in replication
> GRANT CONNECT TO MPUser IDENTIFIED BY MPPwd ;
> GRANT CONNECT TO RSUser IDENTIFIED BY RSPwd ;
> // (c) Grant proper user rights
> GRANT PUBLISH TO MPUser ;
> GRANT REMOTE TO RSUser TYPE FILE ADDRESS
> '\\\\192.168.1. 7\\D\\INV\\DATA\\RS'
SEND EVERY '0:15' ;
> // (d) Create Publication
> // Done...
> // (e) Create Subscription
> CREATE SUBSCRIPTION TO RSPub FOR RSUser ;
>
>
> 4) Do the following on the consolidated:
> GRANT CONNECT TO MPUser IDENTIFIED BY MPPwd ;
> GRANT REMOTE TO MPUser TYPE FILE ADDRESS
> '\\\\192.168.1. 7\\D\\INV\\DATA\\MP'
SEND EVERY '0:30' ;
> CREATE SUBSCRIPTION TO RSPub FOR MPUser ;
>
> This seems to me covers everything with respect to setting things up.
>
> The questions would be:
>
> 1) How do I get the replication going? Is the following ok?
> // On the consolidated:
> START SUBSCRIPTION TO RSPub FOR MPUser;
>
> // and on the remote:
> START SUBSCRIPTION TO RSPub FOR RSUser;
>
> 2) I want to test the replication for a couple of days at the
> consolidated site. WHen I'm done, I'd like to synchronize the
> subscription just to make sure that everything is on the new remote.
> How do I do this? I read in the manual the following
> SYNCHRONIZE SUBSCRIPTION TO RSPub FOR xxxx;
>
> But where should I run this, at the consolidated or at the remote site?
>
> Thanks in advance,
> Edgard
>

Edgard L. Riba

2005-10-27, 7:41 am

Hi Rob,

Thanks for responding.

>
> The second GRANT shoud be GRANT CONSOLIDATED ...
>

!!!!!!!!!!

In the current setup I have, I used the GRANT REMOTE syntax on the remote
site invbv :-( In other words, it is currently setup as a remote user.
The weird thing is that replication has been working.

How do I change the RSUser to CONSOLIDATE without messing replication?

> Are both of these remotes Read Only? If not, have you considered any
> additional conflict resolution requirements that you may now have with
> multiple remotes updating data?

Yes. I think I got that covered.

I have one question on the this area though. I have a number of tables
that are updated ONLY at the consolidated site, and replicate to the remote
sites. Should I remove all referential integrity from these tables at the
remote sites (and let referential integrity be handled only at the
consolidated)? What is the recommended practice regarding this?

Thanks,
Edgard

>
>
> --
> -----------------------------------------------
> Robert Waywell
> Sybase Adaptive Server Anywhere Developer - Version 8
> Sybase Certified Professional
>
> Sybase's iAnywhere Solutions
>
> Please respond ONLY to newsgroup
>
> EBF's and Patches: http://downloads.sybase.com
> choose SQL Anywhere Studio >> change 'time frame' to all
>
> To Submit Bug Reports:
> http://case-express.sybase.com/cx/c...sc?CASETYPE=Bug
>
> SQL Anywhere Studio Supported Platforms and Support Status
> http://my.sybase.com/detail?id=1002288
>
> Whitepapers, TechDocs, and bug fixes are all available through the
> iAnywhere
> Developer Community at www.ianywhere.com/developer
> "Edgard L. Riba" <elriba at rimith dot com> wrote in message
> news:4351418d$1@foru
ms-2-dub...
>
>

krisztian pinter

2005-10-27, 7:41 am

On 17 Oct 2005 13:18:56 -0700, Edgard L. Riba <elriba at rimith dot com>=
=

wrote:

>
> !!!!!!!!!!
>
> In the current setup I have, I used the GRANT REMOTE syntax on the rem=
ote
> site invbv :-( In other words, it is currently setup as a remote =

> user.
> The weird thing is that replication has been working.

be very careful about it! we did the same for a large number of remote =

sites,
and the result was awful. both the consolidated and the remote sites tri=
ed
to resolve conflicts, and so the databases became different, and all sor=
t =

of
havoc happened then, which took a month to track down.

> How do I change the RSUser to CONSOLIDATE without messing replication?=

simply PASSTHROUGH the correct GRANT. but if the databases already out o=
f
sync, you need to get rid of diffs manually. not the case if you ruled o=
ut
conflicts.

Edgard L. Riba

2005-10-27, 7:41 am

Hi Krisztian,

Thanks for responding.

Important to know!
[color=darkred]
> How do I change the RSUser to CONSOLIDATE without messing replication?
[color=darkred]

Could you please double check with me if I'm going to do this right? I'm
afraid I have never used PASSTHROUGH mode and I don't quite understand how
it works :-(

Connecting to the RS consolidated site, I execute the following:
PASSTHROUGH ONLY FOR BVUser; --BVUser = the remote user...
GRANT CONSOLIDATE TO RSUser TYPE FILE ADDRESS
'\\\\192.168.1. 7\\D\\INV\\DATA\\RS'
SEND EVERY '0:15' ;
PASSTHROUGH STOP;

If I connect directly to the remote site, can I simply do:
GRANT CONSOLIDATE TO RSUser TYPE FILE ADDRESS
'\\\\192.168.1. 7\\D\\INV\\DATA\\RS'
SEND EVERY '0:15' ;

Would this work as well without disrupting the replication?

Thanks,
Edgard

krisztian pinter

2005-10-27, 7:41 am

On 18 Oct 2005 09:02:19 -0700, Edgard L. Riba <elriba at rimith dot com>=
=

wrote:

> Could you please double check with me if I'm going to do this right?

> Connecting to the RS consolidated site, I execute the following:
> PASSTHROUGH ONLY FOR BVUser; --BVUser =3D the remote user...
> GRANT CONSOLIDATE TO RSUser TYPE FILE ADDRESS
> '\\\\192.168.1. 7\\D\\INV\\DATA\\RS'
SEND EVERY '0:15' ;
> PASSTHROUGH STOP;

that's what we did. worked well.

> If I connect directly to the remote site, can I simply do:
> GRANT CONSOLIDATE TO RSUser TYPE FILE ADDRESS
> '\\\\192.168.1. 7\\D\\INV\\DATA\\RS'
SEND EVERY '0:15' ;

oh, so you can simply log on to the remote! then don't passthrough
anything, just execute this one. why bother? we had no access to
remotes.

> Would this work as well without disrupting the replication?

i don't know about the theory, but in practice, worked for us.

forums.sybase.com

2005-10-27, 7:41 am

Hi Krisztian,

I did it and worked fine. Thanks!
Edgard

"krisztian pinter" <pinterkr@freemail.hu> escribió en el mensaje
news:opsyvszu0ywwfeh
v@kar_wst_pint...
On 18 Oct 2005 09:02:19 -0700, Edgard L. Riba <elriba at rimith dot com>
wrote:

> Could you please double check with me if I'm going to do this right?

> Connecting to the RS consolidated site, I execute the following:
> PASSTHROUGH ONLY FOR BVUser; --BVUser = the remote user...
> GRANT CONSOLIDATE TO RSUser TYPE FILE ADDRESS
> '\\\\192.168.1. 7\\D\\INV\\DATA\\RS'
SEND EVERY '0:15' ;
> PASSTHROUGH STOP;

that's what we did. worked well.

> If I connect directly to the remote site, can I simply do:
> GRANT CONSOLIDATE TO RSUser TYPE FILE ADDRESS
> '\\\\192.168.1. 7\\D\\INV\\DATA\\RS'
SEND EVERY '0:15' ;

oh, so you can simply log on to the remote! then don't passthrough
anything, just execute this one. why bother? we had no access to
remotes.

> Would this work as well without disrupting the replication?

i don't know about the theory, but in practice, worked for us.

Sponsored Links