Replicate SELECT reserve_identity within a Stored Procedure in a warm standby setup

[Former Member]

Hi ,

We have a use case where an ASE database is setup in a Warm Standby using SAP Sybase Replication Server

• Primary :  “PDS.mydb”
• Standby :  “RDS.mydb”
• A stored procedure “mydb..myTest_sp” uses the following SELECT statement

select @myLogId  = reserve_identity( 'mydb..myLog', 1 )

• The procedure also inserts a row into another base table eg. “mydb..myAppTab” using @myLogId as one of the values.

Result ( in a standard Warm Standby setup )

• After execution of the stored procedure, Only logged transactions ( DMLs ) are replicated
• The effect of the above SELECT calling function “reserve_identity” is not replicated
• From ISQL prompt following query reports current value @PDS.mydb and older value @RDS.mydb

select next_identity('mydb..myLog')

Table DDL:

create table myLog (

myLog_Id   numeric(20,0) identity

)

Question:

• Using “sp_setrepproc” to replicate the stored procedure “mydb..myTest_sp” replicates execution of the stored procedure @RDS.mydb
• Is this the best method to replicate the usage of SELECT “reserve_identity” inside the stored procedure ?

Thanks & Regards

Shiva

[Former Member]

Hi Mark,

Thanks.  Noted your recommendations.

1. Yes, in the above stated example myLog is solely used as a key generator

2. The identity gap that might be generated due to PDS or RDS shutdown hard - is taken care.

3. Modifying the stored procedure code is not allowed.

Stored procedure replication is implemented. It is noted execution of stored procedure is logged and replicated.

There is another case. 

Table DDL:

create table myTran (

  myTran_Id    numeric(20,0) identity  ,

  myMessage  varchar(255)  not null  ,

  myKey          numeric(20,0) not null

)

go

Procedure DDL:

create procedure myTran_sp

(

@myKey numeric(20) output )

as

select @myKey = convert( numeric(20,0), reserve_identity( 'mydb..myTran', 1 ) )

INSERT mydb..myTran (myMessage, myKey) VALUES ( 'testing', @myKey )

go

Though stored procedure replication executes the stored procedure at RDS, while tracing it is noted there are additional independent grouped transactions INSERT into and DELETE from table "mydb..myTran".

Some insert fails at RDS reporting of duplicate key error WHEN the stored procedure is replicated.

Based on the traces, suspecting there might be other sources (other than stored procedures) in their application which might be causing the identity gap by using "reserve_identity"

When stored procedure is not replicated everything is OK, except that the next value for tables with IDENTITY column will not be in sync.  One way to solve would be as you had mentioned write a script to periodically check and keep RDS in sync.

Question:

- Is there any other method you think might solve this issue globally.

Thanks & Regards,

Shiva

[Former Member]

Hi Shiva,

If you main concern is to get the exact same value in your table "myLog" (or "myKey"), I will not set the store procedure for replicate, but I will set directly the table and let SRS replicate the "identity" value.

Doing so, when new IDs are generated, the change in "myLog" will be replicated as well as your other user table (I assume the ID will be used as primary key, or similar, in other user table)

In the same way, if you hit some hard shutdown at primary (Active DB) which introduce a gap in IDs, you will have the same gap at replicate (Standby).

If your concern is to not get gaps in IDs, so, as Mark suggest, you may not use "identity" but your own "logic" to get next ID.

Storing it (this next ID from your own logic) in the same table (but without "identity" column) still set for replicate instead of the store procedure, will generate a change (DML) which will also be replicated at replicate (Standby).

Regards

Yvan

Message was edited by: Yvan LACRAMPE

[Former Member]

Hi Yvan,

Thanks for your suggestion.

The next value of IDENTITY is fetched inside a stored procedure using SELECT statement.

Example:

1. select @myKey = convert( numeric(20,0), reserve_identity( 'mydb..myTran', 1 ) )

2. select @myLogId  = reserve_identity( 'mydb..myLog', 1 )

The logic of the stored procedure is not allowed to be modified.

The database is setup in a warm standby replication using SAP SRS.

So in order to replicate the result of SELECT ( example above ), one way is to replicate the stored procedure.

Thanks & Regards,

Shiva

[Former Member]

Hi Shiva,

Yes, I understand that, the "select" statement just reserve a value for the next inserted row of your user table.

But, then, you will insert that row, do you? in such case the reserved value will be used and will be replicated with the "insert" DML (SRS will be able to insert an identity value (the same as at primary), it will not "ask" replicate ASE to generate one again).

Does it work like this for you?

If not, what is the issue if table is set for replicate and not the store procedure?

Regards

Yvan

[Former Member]

Hi Shiva,

I read back your first post on this thread, and I think I get what you mean.

Definitively: "select reserve_identity()" is never replicated as is, because it is a "Select" statement.

The reserved identity value is/are expected to be replicated by the "Insert" statement following in the same transaction.

In your case, if you issue "select reserve_identity("myID", 1)" over a user table named "myID" having the identity column and never fire the "Insert" statement on that user table, but only on others ones, yes, this will lead to the Standby side not be aware/updated of what could be the
next valid identity value (it will be fine for the others user tables).

I’m not sure you could get a nice solution because of the way you use “reserve_identity()” (which is not what it aim to).

Without setting the store procedure for replicate, the issue will be when you will “switch active”. In
such case you may have to add an additional step in your “switch” process and process a call to
“dbcc set_identity_burn_max(dbname, objname, option)” in order to set the next identity value you want on the Standby side (which is in process to became the Active)

Having the store procedure set for replicate (instead of only the table), the risk is to get the store
procedure call not replicated in same order than at primary (transaction are replicated in “commit” order).

But I do not really see/get why you hit “Duplicate Key” error in such case, the user table populated inside the store procedure, is it also populated outside the store procedure? (which can explain)

Do you have a way to avoid this?

Regards

Yvan

[Former Member]

Hi Yvan,

Yes now you have got it.

Unfortunately we are not allowed to change any business logic ( stored procedures and others )

Yes, another method as you have suggested is to use "dbcc set_identity_burn_max" as an additional step when standby is made active.  This will help to sync the next IDs for the related tables.

But actually they are reluctant to accept this method.

Thanks & Regards,

Shiva