|
The primary key pool is a table that holds a set of primary key values for each database in the SQL Remote installation. Each remote user receives their own set of primary key values. When a remote user inserts a new row into a table, they use a stored procedure to select a valid primary key from the pool. The pool is maintained by periodically running a procedure at the consolidated database that replenishes the supply.
The method is described using a simple example database consisting of sales representatives and their customers. The tables are much simpler than you would use in a real database; this allows us to focus just on those issues important for replication.
The primary key pool technique requires the following components:
Key pool table A table to hold valid primary key values for each database in the installation.
Replenishment procedure A stored procedure keeps the key pool table filled.
Sharing of key pools Each database in the installation must subscribe to its own set of valid values from the key pool table.
Data entry procedures New rows are entered using a stored procedure that picks the next valid primary key value from the pool and delete that value from the key pool.
The primary key pool table
Replicating the primary key pool
Replenishing the key pool
Using primary keys from the key pool