Archive

Archive for the ‘Cluster’ Category

Enable Instant File Initialization to help improve of the performance for big databases which reside on Cluster environment

December 24, 2012 Leave a comment

To get the better performance for big environment like where database size more than 200 GB at cluster environment then  we can  enable  Instant File Initialization (“Perform volume maintenance tasks”) for your SQL Server startup/service account. This option allows for much faster data file allocations (CREATE AND ALTER FILE) but DOES NOT WORK FOR LOG FILE ALLOCATIONS. This is enabled for each instance via the “Perform volume maintenance tasks” local security policy. In a cluster we  have to grant the right on all nodes. If there are multiple instances on a server or cluster, we have  grant this right to each instance’s security group. I have tested the same in our test environment and it is really very good option during allocation of the large space .

 

This permission keeps SQL Server from “zeroing out” new space when you create or expand a data file (it is not applied to log files).  This helps performance for CREATE DATABASE, ALTER DATABASE, RESTORE, and AUTOGROW. It can have a significant positive impact on how long it takes to create or expand a data file, but there is a small security risk in doing so. That is because a file “delete” really just deallocated the space and a new allocation can reuse that space which may still have data in it. When you do not zero out the existing space there is a possibility that someone could read data that you thought had been deleted. It is very common to turn Instant File Initialization on. Many shops consider the increased performance benefit to far outweigh the small security risk, but you must weigh the cost and benefits within environment.

 

How to grant this right/permission (tested on Windows 2008) to each instance of SQL Server:

 

  • Run lusrmgr.msc on the server to find the appropriate group name for each instance of SQL Server. For example: SQLServer2005MSSQLUser$SERENITYHOME$KAYLEE (SQL 2005 named instance), SQLServerMSSQLUser$SerenityHome$WASH (SQL 2008 named instance), or SQLServerMSSQLUser$SerenityHome$MSSQLSERVER (SQL 2008 default instance).
  • Run secpol.msc on the server.
  • Under Security Settings on the left, go to Local Policies and under that to User Rights Assignment.
  • Under Policy on the right side, go to “Perform volume maintenance tasks” and double click on it
  • On the Local Security Setting tab click on the “Add User or Group” button
  • In “Select Users, Computers, or Group”
    • Click on “Locations” and choose either your local computer name (for local groups/standalone) or your domain (for domain groups/clusters)
    • Click on “Object Types” and check “Groups”
    • In “Enter the object names to select” enter your SQL Server group created by SQL setup (standalone) or your cluster domain group (for clusters).
    • Choose “OK”
    • Restart SQL Server

Script to check SQL Server Cluster Nodes


Following Script will help your to check SQL Server Cluster Nodes, which will query clustered server instance and determine how many nodes exist in the SQL Server cluster

For SQL Server 2000

– Returns the list of nodes on which the virtual server can run. Such information is useful in failover clustering environments.

– If the current server instance is not a clustered server, fn_virtualservernodes returns an empty rowset.

SELECT * FROM ::fn_virtualservernodes()

 

For SQL Server 2005 /2008 / 2008 R2

– determine the nodes on a SQL Server clustered server instance:

SELECT * FROM sys.dm_os_cluster_nodes

For SQL Server 2005/2008/2008 R2 and SQL Server 2011 (Denali)

– VIEW SERVER STATE permission are required to execute this function

– current server instance is not a clustered server, fn_virtualservernodes returns an empty rowset.

SELECT * FROM fn_virtualservernodes()

 

Following Script will help your to check SQL Server Cluster Nodes, which will query clustered server instance and determine how many nodes exist in the SQL Server cluster

 

For SQL Server 2000

 

– Returns the list of nodes on which the virtual server can run. Such information is useful in failover clustering environments.

– If the current server instance is not a clustered server, fn_virtualservernodes returns an empty rowset.

 

SELECT * FROM ::fn_virtualservernodes()

 

For SQL Server 2005 /2008 / 2008 R2

 

– determine the nodes on a SQL Server clustered server instance:

 

SELECT * FROM sys.dm_os_cluster_nodes

 

For SQL Server 2005/2008/2008 R2 and SQL Server 2011 (Denali)

 

– VIEW SERVER STATE permission are required to execute this function

– current server instance is not a clustered server, fn_virtualservernodes returns an empty rowset.

 

SELECT * FROM fn_virtualservernodes()

Categories: Cluster

Manually Changing a Checkpoint Key in sql 2008 Cluster

July 28, 2010 1 comment
Categories: Cluster

Bringing SQL Server Online Outside Cluster Services


Bringing SQL Server Online Outside Cluster Services

Sometimes a SQL Server instance will not come online due to problems that prevent the Cluster service from getting a successful IsAlive result from the SQL Server instance. In these cases, it might be valuable to bring the SQL Server instance online from a command prompt so that you can perform troubleshooting and emergency maintenance.

This technique involves bringing a SQL Server instance online outside the control of the Service Control Manager. The SQL Server resource will appear to be offline from the Failover Cluster Management console and the Cluster service will not be managing the SQL Server instance. No failover or restart of the SQL Server instance will occur in this state. After it is online, the SQL Server instance will be available to clients on the network.

This technique is typically used to bring up the SQL Server instance when configuration issues are causing SQL Server to fail. Microsoft recommends that you only use this technique for intermediate troubleshooting steps because automated failover of the SQL Server instance is not available in this state. The technique involves the following steps:

  1. Bring all SQL Server disk resources online via the Failover Cluster Management console.
  2. Bring all SQL Server IP address resources online via the Failover Cluster Management console.
  3. Bring the SQL Server network name resource online via the Failover Cluster Management console.
  4. From the Command Prompt window, navigate to your SQL Server directory and execute the following code:
    sqlservr.exe –c –s<instance name>
  5. Leave the Command Prompt window open and do not minimize it. Closing the window or logging off will stop the SQL Server.
  6. To stop the server, press CTRL+C. You will be prompted for confirmation of shutdown.
Categories: Cluster

Troubleshooting SQL Server 2008 Failover Clusters


https://rajibsqldba.wordpress.com/?attachment_id=122

This document is publised by microsft and I am bit modified it to make user friendly.

Categories: Cluster

SQL server 2008 two node cluster installation step by step.

Categories: Cluster

SQL server 2008 single node Installation step by step

Categories: Cluster