Hi Roberto, we're looking at having our offsite spam server have a local sql replication of the spamfilter database at the mainoffice. We are looking at this solution so we don't loose any messages destined for the Q in the event that the internet goes down etc. It seems to work ok except for one problem. And that is the spam quarantine ID. Because it counts + 1 for each email. If they each put a message into spam a conflict occurs. Would it be possible to have spamfilter generate random quarantine id's? This would resolve that problem.

Thanks,

Kevin

Kevin,

The QuarantineID is used as a primary key in the database, and must be unique for each record. It is not possible to assign random values as this will not ensure their uniqueness. The value is automatically incremented by the database platform as new records are added. If you're using Microsoft SQL server, sql replication takes this problem into consideration. Please take a look at the following excerpt from the SQL documentation as it applies to your setup.

Roberto F.
LogSat Software

undefined" CLASS="ASPForums" TITLE="WARNING: URL created by poster. - Managing Identity Values

You can manage identity values by:

• Allowing Microsoft® SQL Server™ 2000 replication to automatically manage identity columns by dynamically allocating ranges of identity values to the Publisher and all the Subscribers.
  
  
• Using the Transact-SQL NOT FOR REPLICATION option when defining the identity column.
  
  
• Using a primary key other than the identity column (for example, a composite key or a rowguid column), if an identity column is not necessary. This strategy eliminates the overhead of managing identity columns on the replicated data.

Automatic Identity Range Handling

The simplest way of handling identity ranges across replicas is to allow SQL Server 2000 to manage identity range handling for you. To use automatic identity range handling, you must first enable the feature at the time the publication is created, assign a set of initial Publisher and Subscriber identity range values, and then assign a threshold value that determines when a new identity range is created.

For example, assigning an identity range from 1000 through 2000 to a Publisher, and a range from 2001 through 3000 to an initial Subscriber, works as follows when combined with a threshold value of 80 percent:

• Newly inserted Publisher rows are assigned identity values from 1000 through 2000. Newly inserted rows on the initial Subscriber will sequence from 2001 through 3000.
  
  
• When 80 percent of either the Publisher identity values or the Subscriber identity values are used, a new identity range is created for forthcoming inserts. In this example, if rows from 1001 through 1800 are used on the Publisher, the threshold has been reached. A new identity range, from 3001 through 4000, is created on the Publisher, and the next inserted row at the Publisher is assigned an identity value of 3001. After the Subscriber reaches the threshold (assuming the Subscriber reached threshold after the Publisher), a new identity range is created on the Subscriber, from 4001 through 5000, and the next inserted row at the Subscriber is assigned an identity value of 4001. The process is repeated as identity ranges are used.
  
  
• As each Subscriber is added, an identity range that is the same size as the initial Subscriber range is added, using the next available starting point.

The threshold setting avoids situations where the Subscribers run out of identity values and become unable to insert new rows until the Distribution Agent or Merge Agent synchronizes with the Subscriber. However, setting the threshold value too low can generate large numbers of unused identity values. The threshold value should be set carefully by evaluating the update frequency at the Subscriber and the synchronization schedule.

For transactional articles enabled for identity range management, the identity ranges at both the Publisher and Subscriber need to be checked and adjusted periodically. The Log Reader Agent does this at the Publisher and the Distribution Agent does this at the Subscriber.

If a Log Reader Agent or Distribution Agent is not running in continuous mode, the check and possible adjustment will be done after all the commands have been processed. When one of the agents is in continuous mode, the check and possible adjustment will be done in a time interval of 10 times the polling interval of the agent after all the commands have been processed. After the agent is started, the first check will be done as soon as the commands have been processed.

Run the Log Reader Agent or the Distribution Agent to adjust the Publisher or Subscriber when the server is out of its identity range. If the agent is running in continuous mode, you may need to restart it for the identity range to be adjusted immediately.

You can also execute sp_adjustpublisheridentityrange to explicitly adjust the identity range at the Publisher based on threshold value for either transactional or merge publications.

You enable automatic identity range handling:

• In SQL Server Enterprise Manager, in the Publication Properties dialog box.
  
  
• By setting the following options in the sp_addmergearticle stored procedure.

Manual Identity Range Handling

You can also manage identity values using a check constraint and the NOT FOR REPLICATION option on the IDENTITY property of a Transact-SQL CREATE TABLE statement. Use the NOT FOR REPLICATION option to specify identity ranges programmatically, or if you are upgrading an existing instance of SQL Server where identity ranges are already being managed through Transact-SQL statements.

Using the NOT FOR REPLICATION statement informs SQL Server 2000 that the replication process gets a waiver when supplying an explicit value and that the local identity value should not be reseeded. Each Publisher using this option receives a reseeding waiver.

The following code example illustrates how to implement identities with different ranges at each Publisher:

• At Publisher A, start at 1 and increment by 1.

  CREATE TABLE authors ( COL1 INT IDENTITY (1, 1) NOT FOR REPLICATION PRIMARY KEY ) 
  

• At Publisher B, start at 1001 and increment by 1.

  CREATE TABLE authors ( COL1 INT IDENTITY (1001, 1) NOT FOR REPLICATION PRIMARY KEY ) 
  

After activating the NOT FOR REPLICATION option, connections from replication agents to Publisher A insert rows with values such as 1, 2, 3, 4. These are replicated to Publisher B without being changed (that is, 1, 2, 3, 4). Connections from replication agents at Publisher B receive values 1001, 1002, 1003, and 1004. Those are replicated to A without being changed. When all data is distributed or merged, both Publishers have values 1, 2, 3, 4, 1001, 1002, 1003, and 1004. The next locally inserted value at Publisher A is 5. The next locally inserted value at Publisher B is 1005.

It is recommended that you always use the NOT FOR REPLICATION option along with the CHECK constraint to ensure that the identity values being assigned are within the allowed range. For example:

CREATE TABLE sales

(sale_id INT IDENTITY(100001,1)

NOT FOR REPLICATION

CHECK NOT FOR REPLICATION (sale_id <= 200000),

sales_region CHAR(2),

CONSTRAINT id_pk PRIMARY KEY (sale_id)

)

Even if someone used SET IDENTITY INSERT, all values inserted locally must obey the range. However, a replication process is still exempt from the check.