We are going to implement Cluster Server on Win 2003 SQL 2000 SP3
Application is web based and there are some batch process .

Need clarification on this

1. If the active node fails and when second node takes over , and if users are modifying data , what happens to these modifications. Basically what precautions need to be taken in the application (asp, web service) so that failover is transparent to users .

2. We have Identity columns in some of the tables and using @@identity ( no triggers) to get the inserted value .
After insertion of a row and selecting the @@identity value , if failover occurs , is this value same on the other server .
Any issues need to be addressed ?

Thanks

Srinivas

Hello, Srinivas!
You wrote on Tue, 17 Feb 2004 22:56:07 -0800:

MS> We are going to implement Cluster Server on Win 2003 SQL 2000 SP3
MS> Application is web based and there are some batch process . Need
MS> clarification on this 1. If the active node fails and when second
MS> node takes over , and if users are modifying data , what happens to
MS> these modifications. Basically what precautions need to be taken in
MS> the application (asp, web service) so that failover is transparent
MS> to users . 2. We have Identity columns in some of the tables and
MS> using @@identity ( no triggers) to get the inserted value . After
MS> insertion of a row and selecting the @@identity value , if
MS> failover occurs , is this value same on the other server . Any
MS> issues need to be addressed ? Thanks Srinivas

When the active nodes fails, all open transactions at the time of the
failure are rolled back (and all the commited transactions are rolled
forward) as soon as the instance open databases on the new node.

SQL Server uses the standard recovery mechanism that guarantee
consistency when an instance is restarted.

About the identity problem, it depends much on how you have
implemented your system and how transactions are handled.

If the transaction is open when the failure occurs, it is rolled back
meaning that the inserted row will never show up when the
server starts on the second server (and as a side effect you will
have a "hole" in sequence because an already issued number is
never issued again, even if the number has been discarded because
of a rollback).

If, however, the transaction has already been commited when
the failure occurs, then the row with the assigned identity number
will show up on the second server.

From an application perspective, the connection gets disconnected,
and remains disconnected, until the instance is restarted on the
second node, so you need to implement some application logic
to try to reconnect automatically (once or several times within a
pre-defined time period) if you want the failure to be trasparent to
users.

Remember that since the instance gets restarted, and the connection
is no more the same, the value of @@IDENTITY (and any other
connection-specific variable) is lost, so what happens to the
already issued identity number in your application depends pretty
much on your implementation logic.

HTH
--
Gianluca Hotz - SQL Server MVP
http://italy.mvps.org - http://www.ghotz.com
http://www.ugiss.org - http://www.ugidotnet.org

Srinivas,

If a failover occurs your applications (web based or not) need to handle the
error caused by the disconnect and reconnect again. When they reconnect
they will now be pointed at the other node but they won't know that. Any
transactions you were in will be rolled back on the server side when the
disconnect happens (or when the recovery happens). So if the app was in the
process of inserting 20 rows and only got thru say 10 of them there will be
10 identity values thrown away. If you wrapped all of this in a transaction
your app will have to redo those 20 Inserts and will get 20 new identity
values.

--

Andrew J. Kelly
SQL Server MVP


"M A Srinivas" <anonymous (AT) discussions (DOT) microsoft.com> wrote

what precautions need to be taken in the application (asp, web service) so
that failover is transparent to users .

Hello Srinivas -
Answers to your questions:
1) Failover pretty much means stopping the sql service in the owning node
and restarting it in the other node. If there are data modifications, all
the uncommitted data will be rolled back. If the current connection breaks
due to the stoppage of sql service, and if your web server/application
reconnect after certain timeout interval, the connections will go through
to the other node. But this reconnection in the application has to be
configured after certain time interval. Primarily this time interval is to
give time for the SQL to come online in the other node.

2) Select @@identity is valid only for the current session. Even not taking
the failover into consideration, if you open another session and do a
@@identity, you will not get the correct value - rather you will get
"null". So after failover the @@identity will not work as this is quite
specific to the session.

Regards,
Chandra
--------------------

Need clarification on this

1. If the active node fails and when second node takes over , and if
users are modifying data , what happens to these modifications. Basically
what precautions need to be taken in the application (asp, web service) so
that failover is transparent to users .

2. We have Identity columns in some of the tables and using @@identity (
no triggers) to get the inserted value .
After insertion of a row and selecting the @@identity value , if failover
occurs , is this value same on the other server .
Any issues need to be addressed ?

Thanks

Srinivas