Microsoft® SQL Server™ requires resources from the operating system to manage its databases. The most important of these resources is memory. When a SQL Server virtual server is running on its primary server and no failover has occurred, all of that system’s memory is available to SQL Server. In preparation for failover events, it is best to set the sp_configure option min server memory to 0. Using 0 to configure memory allocation will allow SQL Server to dynamically reallocate memory usage during a failover.
When running SQL Server on a cluster in an active/passive configuration, you must ensure that there is enough physical memory on the secondary server to run SQL Server. If there are other applications on the backup and not enough physical memory to run SQL Server and the applications simultaneously, Microsoft Windows NT® pages the applications and SQL Server. This can affect SQL Server performance significantly.
When running two SQL Server virtual servers on a cluster, two instances of a SQL Server virtual server run on the same cluster server after a failover. It is important to consider this situation in case you need to configure SQL Server memory. If both SQL Server virtual servers are configured to use all available memory on a server and the sp_config min server memory option is not set to 0, significant memory contention and performance degradation will happen when they are running on the same system.
If each SQL Server virtual server is configured to run on the same server as any applications or virtual server on the secondary server, very little, if any, performance degradation occurs after a failover. However, it may not be acceptable to have so much memory on a system used only during a failover. In that case, you can configure SQL Server to use more than half the memory. You will see performance degradation after a failover because the operating system will be forced to page portions of each SQL Server virtual server out to disk. You can monitor the performance characteristics of a system while a SQL Server virtual server has been failed over by monitoring Object Memory statistics in Windows NT Performance Monitor. If the Performance Monitor shows a large number of object memory page faults, consider configuring less memory for one or both SQL Server virtual servers, or stopping other programs or processes
Windows NT Clustering Service checks the state of SQL Server with two different functions:
A simple check that verifies that the SQL Server resource is running.
A more thorough check, which tests SQL Server by logging in and performing a simple query on the system catalogs.
If the ISALIVE check cannot connect, the cluster service cannot do the more thorough check of SQL Server status. ISALIVE looks for specific errors generated when there are no more user licenses or no more user connections available. ISALIVE assumes that a server is still working correctly if it gives these messages. Make sure that enough user connections are configured so that ISALIVE can always connect. By default, SQL Server allocates user connections dynamically up to a limit of 32,767; you can check to make sure the default value (0, for self-configuring) has not been changed.
| Server Memory Options | user connections Option |