The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

We have multiple clients that are in, what we call, a three-tiered replicating environment. This means we have a Main Site that has Remote Sites. Once those Remote Sites are loaded, we create sites in those and extract them. So the main site never really 'knows' about the third level of remote sites.

My question is, will there be a problem with using Global Autoincrements in this type of replicating environment?

Will the databases be able to automatically generate the global database id themselves and have them all still be global, or do I need to hook into the extraction stored procedure and generate them myself?


asked 29 Jan '10, 16:58

Calvin%20Allen's gravatar image

Calvin Allen
accept rate: 25%

edited 29 Jan '10, 18:33

How do you generate the GLOBAL DATABASE ID currently for the regular remotes, i.e. the remotes of the main site? - I guess that will tell whether the additional layer will fit smoothly or must be regarded carefully.

(29 Jan '10, 21:02) Volker Barth

We implemented remote sites before these options were available and as such, its a complete custom setup that uses our own db tables to hold key ranges, plus flat files at each site that indicates what its id value is.

(29 Jan '10, 21:08) Calvin Allen

Well, then the question is whether you custom GLOBAL DATABASE ID (or similar) management is able to guarantee the uniqueness. -- We have used a different kind of three tier setup, namely MS SQL <-> MobiLink <-> ASA main db <-> SQL Remote <-> ASA remotes. In that setup, we used GLOBAL DATABASE IDs on the ASA side and made sure that all MS SQL generated PKs were below a certain barrier (i.e. we reserved something like a ID space for the MS-SQL database). That worked well.

(29 Jan '10, 21:18) Volker Barth

My thoughts were to still use a table in our database, but instead of having ranges for every single table, it would have ranges for database ids, and then I would use the sp_hook_dbxtract_begin to set the global_database_id to the next value in the range for that site.

(29 Jan '10, 23:00) Calvin Allen

IMO the main site (the single top-level consolidated database) should control the assignment of global autoincrement partition numbers, possibly in a single-column table that simply records partition numbers that have been assigned and therefore cannot be assigned again.

The synchronization process between the top and middle levels would then make sure that each middle level database has been assigned N values that it hasn't actually used yet (for creating bottom level databases)... a kind of key pool local to the middle level where you insert the assigned-but-unused-values, delete the values as you use them, and request more from the top level when the number drops below a threshold.

The point behind "global autoincrement" is the word "global" which implies one single point of control... and the only single point is the top level consolidated database.

All this must keep in mind that the middle levels must also have their own partition numbers, as must the top level. I like assigning partition zero to the top level because that works for DBMS's like Oracle and MSS that have autoincrement but not global autoincrement.

permanent link

answered 30 Jan '10, 08:42

Breck%20Carter's gravatar image

Breck Carter
accept rate: 21%

And I would further use the GlobalAutoIncrement event to notify the consolidated when one's database ID is nearly exhausted, so that the cons can reserve the next ID for that remote.

(30 Jan '10, 13:50) Volker Barth

Calvin I'm not an Replication specialist, but as I know if you have an unique GLOBAL DATABASE ID, you should have no problems.

permanent link

answered 29 Jan '10, 18:30

Zote's gravatar image

accept rate: 43%

Comment Text Removed

That's true. I've reworded the question a bit to explain a little more.

(29 Jan '10, 18:34) Calvin Allen
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 29 Jan '10, 16:58

question was seen: 1,015 times

last updated: 30 Jan '10, 08:42