SQL Server 2000 Clustering Tips

Tip: Prefix the cluster nodes names, and the virtual cluster names, with appropriate prefixes so that you don’t get the names confused.

Explanation: Sometimes, it is easy to confuse the names of the cluster nodes, and the virtual server names. For example, in a two-node cluster, there are two cluster node names and two virtual cluster names (one for the Windows 2000 cluster, and one for the SQL Server cluster).

One way to help prevent confusion of these names is to add an appropriate prefix to them. For example:

n1_: For node one of the cluster
n2_: For node two of the cluster
vc_: For the virtual name of the Windows 2000 cluster
vsql_For the virtual name of the SQL Server cluster
You can choose any prefix scheme that makes sense for you.

Version: 7.0, 2000

Date Added: 3-8-2002

*****

Tip: When you install SQL Server 2000 Clustering, the Full-Text Service is installed. If do not plan to use this service, you should configure it so that its failure will not cause SQL Server to failover.

Explanation: By default, when SQL Server 2000 clustering is installed, the Full-Text service is configured for failover along with all of the other SQL Server services. In addition, it is configured in such a way that if the Full-Text service fails for any reason, that it will automatically cause all of the SQL Server services to failover. If you are not using the Full-Text service, you don’t want this to happen.

To prevent the failure of the Fail-Text service from failing over all of the other SQL Server services, you must follow these configuration steps:

Start Cluster Administrator.
Display all of the SQL Server services on the right side of the screen.
Right-click on the “SQL Server Fulltext” resource, and choose “Properties.”
Select the “Advanced” tab from the SQL Server Fulltext Properties screen.
Deselect the checkbox next to “Affect the group.”
Click “OK.”
By taking this step, you have told the Cluster Service not to automatically failover all of the SQL Server resources should the Full-Text service fail for any reason. If it should fail, you will see a warning message in Cluster Administrator, but no other cluster resources will be affected.

Once you have done this, if you don’t use the Full-Text Service, you can use the Cluster Administrator to take this resource off-line. But don’t try to delete this resource, as it can cause your SQL Server cluster installation to become corrupt.

Version: 2000

Date Added: 3-8-2002

*****

Tip: If you want to find out the names of the nodes in your SQL Server cluster, you can with the fn_virtualservernodes() table function.

Explanation: To display all of the names of the nodes actively participating in your SQL Server cluster, run this statement:

select * from ::fn_virtualservernodes()

Version: 2000

Date Added: 3-8-2002

*****

Tip: You can affect how long it will take your SQL Server to failover from the primary node to the second node by changing the “recovery interval” SQL Server setting.

Explanation: When SQL Server fails from one node to another, it must go through the automatic recovery process when it restarts on the new node. This means that any transactions that were not written to the database when the failover occurred must be rolled forward or back as appropriate via the transaction log. This can take some time if there are a lot of transactions in the buffer that had yet to be written to the database when the failover occurred.

You can directly affect the recovery period by changing SQL Server’s “recovery interval” setting. By default, this setting is set to “0″, which means that it is set to automatic configuration. In effect, this means that the recovery period should not be longer than one minute.

But some DBAs change the “recovery interval” to be a specific number, such as five. When you assign a number to the “recovery interval” setting, what you are doing is telling SQL Server not to take more than this number of minutes to recover. For example, if five is used as the setting, this tells SQL Server that it can take up to five minutes to recover. This, in effect, reduces how often checkpoints are performed in SQL Server. A checkpoint is when dirty buffers in the SQL Server cache are written to the database.

As you can see, if you set the “recovery interval” to some number other than “0″, then it will take longer for a failover to occur, as it takes longer for the automatic recovery process to run. In most cases, you should leave the “recovery interval” set to the default value of “0″. This will help ensure that failover times are kept as short as possible.

Version: 7.0, 2000

Date Added: 3-8-2002

*****

Tip: When configuring the IP address and subnet mask of the private “heartbeat” network in a cluster, select a subnet other than the one you use for your public network, and ideally, select one that is not currently used by your company.

Explanation: The private “heartbeat” network is used by a cluster for internal communication between the nodes of the cluster. This network is isolated from all other networks in your company by the fact that it runs over its own network cable (often directly wired from network card to network card using a special cross-over cable).

To prevent any potential for IP address conflicts, should someone make a mistake and connect the internal network to the public network, the internal network should be on its own subnet. This way, should someone make a mistake and connect the internal network to the public network, then there won’t be any potential for IP address conflicts and other strange problems arising.

While this is not a requirement, consider it a good preventative measure against future potential problems.

Version: 7.0, 2000

Date Added: 3-11-2002

*****

Tip: Don’t locate the Windows 2000 paging (swap) file on a shared array. Place it on a local drive in each node in the cluster.

Explanation: Since the paging file is specific to a specific computer, not to the cluster, the paging file should be located on the nodes of the cluster, not the shared array. Not only will performance suffer if you do this, it could create cluster-related problems during failover.

Version: 7.0, 2000

Date Added: 5-23-2002

Continues…



Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |