SQL Anywhere database replication

Author

MobiLink design

Carl Kayser

2005-04-12, 1:23 pm

As in the previous post, we use the "natural" primary/foreign keys from our
business model with the remote ID added in. No PK pools are used since
multiple copies do not exist across the remotes.

For converting to MobiLink it has been suggested to use singular PKs and PK
pools - the consolidated database is ASE. So we would (essentially) have to
have two indexes per table: one for synchronization and the other for
referential integrity. (After all, we do want the collected data on the
remotes to be clean.) Well, we do about 2,000,000 inserts (and deletes) a
month in several batches. It seems awkward to me and would probably impose
a (consolidated) performance hit to do this. We would also need some PK
pools on our ~500 remotes since there could be remote deletes/inserts on a
few of the tables.

Has anyone made this design shift (with either SQL Remote or MobiLink) and
have any comments?

Breck Carter [TeamSybase]

2005-04-13, 7:23 am

If natural keys work and you are happy with them, there is no reason
to change just because you're considering MobiLink.

Also, key pools are no longer necessary with the introduction of
DEFAULT GLOBAL AUTOINCREMENT. But that's a moot point if you're using
natural keys.

Breck

On 12 Apr 2005 09:48:03 -0700, "Carl Kayser" <kayser_c@bls.gov> wrote:

>As in the previous post, we use the "natural" primary/foreign keys from our
>business model with the remote ID added in. No PK pools are used since
>multiple copies do not exist across the remotes.
>
>For converting to MobiLink it has been suggested to use singular PKs and PK
>pools - the consolidated database is ASE. So we would (essentially) have to
>have two indexes per table: one for synchronization and the other for
>referential integrity. (After all, we do want the collected data on the
>remotes to be clean.) Well, we do about 2,000,000 inserts (and deletes) a
>month in several batches. It seems awkward to me and would probably impose
>a (consolidated) performance hit to do this. We would also need some PK
>pools on our ~500 remotes since there could be remote deletes/inserts on a
>few of the tables.
>
>Has anyone made this design shift (with either SQL Remote or MobiLink) and
>have any comments?
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Carl Kayser

2005-04-13, 11:23 am

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:rjqp5119t1q1m4m
k74mcfujogtgrk6kumj@
4ax.com...
> If natural keys work and you are happy with them, there is no reason
> to change just because you're considering MobiLink.
>
> Also, key pools are no longer necessary with the introduction of
> DEFAULT GLOBAL AUTOINCREMENT. But that's a moot point if you're using
> natural keys.

Thanks for your comments. If we do use surrogates I think that we would
have to use consolidated ASE identity inserts and PK pools (working from
2,147,483,647 down) on the remotes. Perhaps we'll find out even more on the
afternoon of April 27 from you and Walt Tallman.

>
> Breck
>
> On 12 Apr 2005 09:48:03 -0700, "Carl Kayser" <kayser_c@bls.gov> wrote:
>
our[color=darkred]
PK[color=darkred]
to[color=darkred]
a[color=darkred]
impose[color=darkred
]
a[color=darkred]
and[color=darkred]
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com

Breck Carter [TeamSybase]

2005-04-14, 7:23 am

On 13 Apr 2005 08:20:30 -0700, "Carl Kayser" <kayser_c@bls.gov> wrote:

>Thanks for your comments. If we do use surrogates I think that we would
>have to use consolidated ASE identity inserts and PK pools (working from
>2,147,483,647 down) on the remotes. Perhaps we'll find out even more on the
>afternoon of April 27 from you and Walt Tallman.

FWIW DEFAULT GLOBAL AUTOINCREMENT works just fine with consolidated
databases that also insert rows... you just implicitly assign
"partition zero" to the consolidated and let it proceed with
identity/autoincrement/sequence generation of 1, 2, 3. Each remote
uses its own (higher) partition. Unsigned integers allow larger
ranges, signed bigints even better, unsigned bigints are the best. PK
pools are no longer necessary.

This subject probably will be discussed on April 27 :)

Breck
--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Sponsored Links