Preparing a database for SQL Server clustering

Question

we plan to implement a sql server 2005 cluster in the next few months. I wanted to know what steps / precautions should be taken as a database developer when trying to achieve this? we need to change any ado.net code (at the front end) / saved procs, etc. etc.? are there any recommendations that should be followed?

I ask this question: for asp.net load balancing, you must ensure that your code for sessions / applications / caches matches the load balancing environment. (so that you are using inproc sessions, you must rewrite this code so that it works in a load-balanced environment). now it is at the level of your web server. I just wanted to do what needs to be done when trying to scale at the database server level

I'm sorry if this question is stupid. please excuse my limited knowledge on this subject :-)

Answer 1

You do not need to make any changes to the interface to implement the SQL Server cluster, you just connect to the instance of SQL Server as usual.

SQL Server Failover Clustering is not load balancing. It is used to add redundancy if any equipment does not work on your main node. Your other (secondary) node does nothing until your primary one works, in which case the crash recovery will happen automatically, and your database will serve the connections again after a delay of 10-20 seconds.

Another problem is that the secondary node cache is empty, so you can see the performance impact after moving to another resource. You can implement a warm cache on your mirror server using SQL Server database mirroring , but there is no way to do something like this with clustering.

Answer 2

Database clustering is different from load balancing. This is high availability, not scaling.

Basically:

• 2 servers (or nodes) with shared disks (which can belong to only one node at any time)
• one is "active", starting a Windows virtual server and an instance of SQL Server
• one controls the other ("passive")
• You are connecting to a virtual Windows server.

If node 1 shuts down, node 2 takes over. Or it may be unsuccessful manually.

This means: services are disabled on node 1, node 2 takes control of the disks and services and starts. Any connections will be broken and no state or session will be transferred.