SQL Anywhere database replication - One Way replication ?

Author

One Way replication ?

TPS

2005-08-22, 3:23 am

From a previous post :

"Simply drop the publications on the remotes after you've extracted the
remote databases. Because there are no
publications, no changes will be sent back to the consolidated, but because
a consolidated user will still exist on the
remote database, the remote database will still send confirmation messages
back to the consolidated database to confirm
that they've successfully applied messages from the remote."

My custom extraction script for the remotes looks like:

username = dw_users.GetItemString(cnt,"username");

sw.WriteLine("connect using
'uid=dba;pwd=sanleon
;dbf=c:\\remotes\\{0}\\{1}.db';" ,username,username);

sw.WriteLine("REVOKE PUBLISH FROM PERF_DEV;");

sw.WriteLine("GRANT CONNECT to {0} IDENTIFIED by PERFORM;",username);

sw.WriteLine("GRANT RESOURCE,REMOTE DBA to {0};",username);

sw.WriteLine("COMMENT on USER {0} IS 'COMMENT';",username);

sw.WriteLine("GRANT publish to {0};",username);

sw.WriteLine("GRANT MEMBERSHIP in GROUP PERFUSER to {0};",username);

sw.WriteLine("GRANT MEMBERSHIP in GROUP ITECH to {0};",username);

sw.WriteLine("CREATE REMOTE TYPE \"FTP\" ADDRESS '{0}';",username);

sw.WriteLine("CREATE REMOTE TYPE \"FILE\" ADDRESS '';");

sw.WriteLine("GRANT REMOTE TO PERF_DEV TYPE \"FTP\" address 'PERF_DEV';");

sw.WriteLine("CREATE SUBSCRIPTION to DBA.KeyPoolData('{0}') for
PERF_DEV;",username);

sw.WriteLine("CREATE SUBSCRIPTION to DBA.SUPPORT_TABLES for PERF_DEV;");

sw.WriteLine("CREATE SUBSCRIPTION to DBA.templates for PERF_DEV;");

sw.WriteLine("START SUBSCRIPTION to DBA.KeyPoolData('{0}') for
PERF_DEV;",username);

sw.WriteLine("START SUBSCRIPTION to DBA.SUPPORT_TABLES for PERF_DEV;");

sw.WriteLine("START SUBSCRIPTION to DBA.templates for PERF_DEV;");

sw.WriteLine("REMOTE RESET {0};",username);

sw.WriteLine("disconnect all;");

Would I just need to add the drop publications after the start subscription
lines??

Thanks

TPS

Rob Waywell

2005-08-22, 1:23 pm

This doesn't look like an extraction script, rather it looks like a script
to create a new remote user. Try running dbxtract -v ... to see what a full
extraction process would look like.

When the previous post said "Simply drop the publications on the remotes" it
was referring to the remote databases, not the remote user on the
consolidated database.

--
-----------------------------------------------
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
"TPS" <ts@scientexlc.com> wrote in message news:4309694d$1@foru
ms-1-dub...
> From a previous post :
>
>
> "Simply drop the publications on the remotes after you've extracted the
> remote databases. Because there are no
> publications, no changes will be sent back to the consolidated, but
> because a consolidated user will still exist on the
> remote database, the remote database will still send confirmation messages
> back to the consolidated database to confirm
> that they've successfully applied messages from the remote."
>
>
>
> My custom extraction script for the remotes looks like:
>
>
>
> username = dw_users.GetItemString(cnt,"username");
>
> sw.WriteLine("connect using
> 'uid=dba;pwd=sanleon
;dbf=c:\\remotes\\{0}\\{1}.db';" ,username,username);

>
> sw.WriteLine("REVOKE PUBLISH FROM PERF_DEV;");
>
> sw.WriteLine("GRANT CONNECT to {0} IDENTIFIED by PERFORM;",username);
>
> sw.WriteLine("GRANT RESOURCE,REMOTE DBA to {0};",username);
>
> sw.WriteLine("COMMENT on USER {0} IS 'COMMENT';",username);
>
> sw.WriteLine("GRANT publish to {0};",username);
>
> sw.WriteLine("GRANT MEMBERSHIP in GROUP PERFUSER to {0};",username);
>
> sw.WriteLine("GRANT MEMBERSHIP in GROUP ITECH to {0};",username);
>
> sw.WriteLine("CREATE REMOTE TYPE \"FTP\" ADDRESS '{0}';",username);
>
> sw.WriteLine("CREATE REMOTE TYPE \"FILE\" ADDRESS '';");
>
> sw.WriteLine("GRANT REMOTE TO PERF_DEV TYPE \"FTP\" address 'PERF_DEV';");
>
> sw.WriteLine("CREATE SUBSCRIPTION to DBA.KeyPoolData('{0}') for
> PERF_DEV;",username);
>
> sw.WriteLine("CREATE SUBSCRIPTION to DBA.SUPPORT_TABLES for PERF_DEV;");
>
> sw.WriteLine("CREATE SUBSCRIPTION to DBA.templates for PERF_DEV;");
>
> sw.WriteLine("START SUBSCRIPTION to DBA.KeyPoolData('{0}') for
> PERF_DEV;",username);
>
> sw.WriteLine("START SUBSCRIPTION to DBA.SUPPORT_TABLES for PERF_DEV;");
>
> sw.WriteLine("START SUBSCRIPTION to DBA.templates for PERF_DEV;");
>
> sw.WriteLine("REMOTE RESET {0};",username);
>
> sw.WriteLine("disconnect all;");
>
>
>
> Would I just need to add the drop publications after the start
> subscription lines??
>
>
>
> Thanks
>
> TPS
>
>
>
>

TPS

2005-08-22, 8:23 pm

This is not the entire script. Just the portion setting up the replication
piece. My extraction works like:
Copy consolidated database
Run a server side script
Run a client side script

This portion of the script is on the remote database.
It revokes the publisher (since this is a copy of the consolidated)
It creates the remote user (which is the publisher on the
consolidated)
It creates a publisher (which is the remote user in the consilidated)
Creates and starts subscriptions.

What I need to know is if the drop publications for 1 way replication goes
after starting the subscriptions????

This method works since I have been doing it for the last 5 years. What I
need to do now is have databases that only replicate 1 way.

Developers will have 2 way replication. (~20 users)
Customer will only have 1 way replication (~2500 users)

This database without any user records is about 150Mb in size. Only some of
the tables will have replication enabled.
User databases will be anywhere form 150Mb to 2 GB in size depending on how
many user records are created.

Thanks
TPS

"Rob Waywell" <rwaywell@no_spam.ianywhere.com> wrote in message
news:430a0328$1@foru
ms-2-dub...
> This doesn't look like an extraction script, rather it looks like a script
> to create a new remote user. Try running dbxtract -v ... to see what a
> full extraction process would look like.
>
> When the previous post said "Simply drop the publications on the remotes"
> it was referring to the remote databases, not the remote user on the
> consolidated database.
>
> --
> -----------------------------------------------
> 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
> "TPS" <ts@scientexlc.com> wrote in message news:4309694d$1@foru
ms-1-dub...
>
>

Rob Waywell

2005-08-22, 8:23 pm

some changes:

It creates the *consolidated* user (which is the publisher on the
consolidated)
[color=darkred]

Drop the publications (no need to create subscriptions since you will be
dropping the publications anyway)

--
-----------------------------------------------
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
"TPS" <ts@scientexlc.com> wrote in message news:430a1d2d@forums
-1-dub...[color=darkred]
> This is not the entire script. Just the portion setting up the replication
> piece. My extraction works like:
> Copy consolidated database
> Run a server side script
> Run a client side script
>
> This portion of the script is on the remote database.
> It revokes the publisher (since this is a copy of the consolidated)
> It creates the remote user (which is the publisher on the
> consolidated)
> It creates a publisher (which is the remote user in the consilidated)
> Creates and starts subscriptions.
>
> What I need to know is if the drop publications for 1 way replication goes
> after starting the subscriptions????
>
> This method works since I have been doing it for the last 5 years. What I
> need to do now is have databases that only replicate 1 way.
>
> Developers will have 2 way replication. (~20 users)
> Customer will only have 1 way replication (~2500 users)
>
> This database without any user records is about 150Mb in size. Only some
> of the tables will have replication enabled.
> User databases will be anywhere form 150Mb to 2 GB in size depending on
> how many user records are created.
>
> Thanks
> TPS
>
> "Rob Waywell" <rwaywell@no_spam.ianywhere.com> wrote in message
> news:430a0328$1@foru
ms-2-dub...
>
>

Sponsored Links