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 |
|
karuna
Aged Yak Warrior
582 Posts |
 Posted - 2005-03-16 : 03:16:47
|
Hi,I need some clarifications on these:My sql server setup is that there are 2 boxes running sql servers clustered with each other (that's how the client described abt the enviornment).If this is the enviornment what should I take care when i write my queries, SPs , trigerrs or dts or whatever for that matter...I do everything the normal way or some precautions needed?My client was also talking about some failover stuffs, which really I didnt understand.He said your stored proc, dts should be optimized for running in a clustered enviornment...This is all getting me tense  . Any inputs on this?Karunakaran |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-03-16 : 09:11:22
|
| I'm struggling to think of something I would specifically do to my SProcs if I knew they were being used in a clustered setup.I'd want any batch type operations to be re-startable, but I would want that anyway.Nope, can't think of anything.Ask the client for what in particular they are worried about!Kristen |
 |
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2005-03-16 : 09:28:17
|
| Well, All they said is that my stored procs, triggers or queries or to put it simple way whatever I use should be optimized for Clustered Servers and I should tell them what will happen in case the server fails, in middle way of executing something..Karunakaran |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-03-16 : 09:53:17
|
| As I understand it (hopefully someone more knowledgeable will be along in a moment) your transaction will be rolled back, and within a short while (but quite possibly as long as 5 minutes) the other server will take over.If you don't have any transactions you could be in trouble ... but you'd be in trouble if you got a power cut on a single server too.SoUPDATE MyTable SET ...INSERT INTO OtherTable ...would be bad news, and you should doBEGIN TRANSACTIONUPDATE MyTable SET ..... if any errors and rollbackINSERT INTO OtherTable ..... if any errors and rollback... otherwise COMMITKristen |
|
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2005-03-16 : 10:06:59
|
| Thats what I had in mind, put everything under transaction. But when client came with Failover Cluster I was off the line. What I understand about failover cluster is that we will have 2 boxes which is of identical config, and both running some s/w for cluster management, so if one fails other one takes over. Meanwhile for every transaction or for every execution of the queries the replication will happen. Is my understanding right?Karunakaran |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-03-16 : 10:51:52
|
| I don't think there is any replication, instead the two SQL Servers share a [seperate] disk storage unit. The inactive server sits there checking that the primary server is alive, if the primary server doesn't answer then it activates itself - and presumably rolls back any incomplete transactions.Kristen |
|
|
|
|
|
|
|
|
Queries, SP,Trigerrs and Clustered Servers