MS SQL 2000 Client connectivity

Hi,
We have a requirement to test connectivity of clients to
the SQL 2000 database. I am very new to this :-( ... well,
what I need to accomplish is when the database is running
all the clients should be able to connect to it also when
the SQL system goes down for some maintenance the clients
should not abort the connection but should keep on trying
to reestablish the connection. Once the SQL server comes
back online the clients must be able to reconnect to the
database with out any intervention.
My questions are,
1.) What is the client software required to accomplish
this' I also need to know if it is possible to set a
timeout period for the reconnection to the database.
2.) Does the default Client tools that come with SQL2000
support this kind of re-connection?
3.) What happens to the transaction that might be
happening when the server goes down? Can we recall/recover
these transactions?
Please help.
Thanks,
Karthik S SLoaded question....Let me see if I can answer the best I can.
Normally clients use MDAC (affectionately know as ODBC to most people) to co
nnect to SQL Server. A connection to SQL Server should not be persistent.
When an application runs and needs data from the database, it connects, gets
data, disconnects. So, if
you need to do maintenance, most people do it in the off hours, night time,
etc. This way it does not impact users during the day. If the server goes
down and there is a transaction in progress, that transaction will be rolled
back if it is not in a co
mplete state, and the application should trap the error for this. If it is
in a complete state, Begin Trans and End Trans are present for the whole tra
nsaction, when the server recovers, it will roll forward, meaning commit the
changes to the database.
MDAC is part of the client tool of SQL 2000.|||Answers inline:
1.) What is the client software required to accomplish
this' I also need to know if it is possible to set a
timeout period for the reconnection to the database.
A: You would need to write you code to trap connection failed and query
cancelled type messages and to retry after X seconds for Y number of times.
2.) Does the default Client tools that come with SQL2000
support this kind of re-connection?
A: Sort of. If you have a query up and running in Query Analyzer and the
SQL Server is shutdown, then the results window in Query Analyzer will
report an error. If you leave the window up and wait for SQL Server to
start again, you can just execute the query again and it will start over
without needing to go through the connection dialog. But it will not
automatically retry the query for you, you have to execute it again.
3.) What happens to the transaction that might be
happening when the server goes down? Can we recall/recover
these transactions?
A: If the transaction was not complete then it will be rolled back. Your
application has to be written to recognize that the transaction did not
complete and take whatever action is appropriate for your application.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.