Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-03-31 : 07:21:55
|
| Joe writes "This is a two part question. To set the stage, we are operating SQL Server 2000 in a clustered environment. Our database design consists of several individual "databases" running under the same instance of SQL Server. It looks something like this: Accounting DeviceManagement ClientServiceAll of our clients connect to the ClientService database, and the client service database stored procedures call procedures in the other two databases as necessary which, in effect, makes ClientService dependent on the other databases.Our clients are coded to check to make sure they are connected, and if not, to block while trying to reestablish a connection. The idea is that if the active node of the cluster fails, the clients will just block waiting for the other node to activate. In theory, this makes things nice and neat, because downstream clients (those connected to the database clients) never know that the database failed at all. We are able to do this checking in very low level code, and thus don't have to expose the "complications" of clustering to those writing applications. So, the problem and the questions:When SQL server starts, it starts each database in what seems to be a "random" order. Our clients, trying to connect to ClientService, successfully connect as soon as it is up, which is often before Accounting and DeviceManagement start up. They call their ClientService procedures, which fail because the calls to the other databases fail (they aren't started quite yet). This breaks our nice theory. Our client connection code now checks for all databases before "connecting", which solves the aformentioned problem, but we have the reverse problem when we failover the service (databases shut down in random order, causing queries to fail sporadically).Question 1: Is it possible to control the startup and shutdown order of databases in SQL Server, and is it possible to inhibit connections on a database until all required databases are up and running, and conversely can we force connections to the database to close before the database actually starts shutting down databases?Question 2: This is our first attempt at writing cluster aware clients - are there other, more elegant ways to go about this?Thanks in advance,Joe Enzminger" |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-04-01 : 13:04:36
|
quote:
Question 1: Is it possible to control the startup and shutdown order of databases in SQL Server, and is it possible to inhibit connections on a database until all required databases are up and running, and conversely can we force connections to the database to close before the database actually starts shutting down databases?
You can not control the startup and shutdown order of databases in SQL Server. You can force connections to the database to close by killing their transactions (KILL statement) or by restricting the access to the database (ALTER DATABASE statement using WITH ROLLBACK option).quote:
Question 2: This is our first attempt at writing cluster aware clients - are there other, more elegant ways to go about this?
Don't have an answer for this one.Tara |
 |
|
|
|
|
|
|
|
Database dependencies