Archive for the ‘Cluster’ Category

How to resolve “Error:11009 No passive nodes were successfully patched” when patching SQL Server 2005 SP4 CU3?

This past week, we had encountered the “Error: 11009 No passive nodes were successfully patched” when patching SQL Server 2005 SP4 CU3 for a clustered instance. Where SP4 is Service Pack 4 and CU 3 is Cumulative Update Package 3. It is critical to note that while SP4 is the latest service pack for SQL Server 2005, Microsoft has released more updates since SP4 and called it Cumulative Update Package 3.  For our scenario, it is a passive/active clustered instance of SQL Server 2005 running on Windows Server 2008 Enterprise edition. After digging around for help, we have found the suggestion on one of the postings to pause the passive node resolved this error. http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/c83861e7-c88f-4a2a-bd4e-1cbd3830facb/

To pause the passive node, you can go to the Failover Cluster Manger, expand the console tree under nodes, right-click on the passive node and then click Pause.

You will need to first pause the passive node, install CU3, reboot (this will cause failover), pause the passive node, install CU3, then reboot (this will cause failover). Problems solved and have a nice day!

REFERENCES:

Error 11009 No passive nodes were successfully patched
http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/c83861e7-c88f-4a2a-bd4e-1cbd3830facb/

How to pause a clustered node
http://technet.microsoft.com/en-us/library/cc731291.aspx

Problems with Service Pack 4 Upgrade for a SQL Server 2005 Clustered Instance on Windows Server 2008 due to lower case in the names of the clustered nodes…

Earlier this week, as a security best practices we had been approved to install service pack 4 for a SQL Server 2005 clustered instance on Windows Server 2008. After we had successfully upgraded to the latest service pack 4, we encountered database mail failure and replication LogReader Agent error just to name two of the issues. After spending hours of researching online, we had found that the root cause of the problem is that the Resource database did not get updated along all the required objects for SQL Server 2005 because the Windows Server 2008 clustered nodes are in lower case. As per http://blogs.msdn.com, “…The datastore of the SQL Server setup presents the node names in the same case, however Windows Installer presents them in the upper case. The setup thinks that we are running it from the passive node and it does not upgrade Resource Database and other system databases.” http://blogs.msdn.com/b/sqlserverfaq/archive/2009/03/16/unable-to-create-maintenance-plans-after-service-pack-2-is-applied-on-sql-server-2005-fail-over-cluster-instance-running-on-windows-2008-cluster.aspx. The workaround involves evicting the nodes, changing the names to upper case, joining the nodes back to the cluster, and then re-running the service pack 4 upgrade and the cumulative update package.

The Resource database contains all the system objects for the SQL Server instance. It is read-only. To find the service pack and the Resource database version, you can run the following query:

SELECT
@@SERVERNAME AS InstanceName
,SERVERPROPERTY('Edition') AS 'Edition'
,SERVERPROPERTY('ProductVersion') AS 'ProductVersion'
,SERVERPROPERTY('ProductLevel') AS 'ProductLevel'
,SERVERPROPERTY('ResourceLastUpdateDateTime') AS 'ResourceLastUpdateDateTime'
,SERVERPROPERTY('ResourceVersion') AS 'ResourceVersion'

UPDATED March 16, 2013:  In our testing environment, we did not have to evict the nodes and then change the node names to UPPER CASE. All we did was to install the Cumulative update package 3 for SQL Server 2005 Service Pack 4 http://support.microsoft.com/kb/2507769. That resolved the issue.

It is critical to be aware that patching for a SQL Server 2005 clustered instance is different from SQL Server 2008 plus clustered instance. In SQL Server 2005 clustered instances, you will need to patch only the active node. The passive node will get updated as well. But, in SQL Server 2008 and R2 clustered instances, you will need to patch both nodes. You will first need to patch the passive node, do a failover, and then patch the current passive node. In addition, don’t forget the hot fixes for the latest service packs. For example, for SQL Server 2005 service pack 4, which is the latest service pack, if you are not careful you will be left out the cumulative update package 3 for SQL Server 2005 service pack 4.

Let it be warned, before upgrading to service pack 4 and the cumulative update package for a SQL Server 2005 clustered instances on Windows Server 2008, make sure you check the cases for the clustered nodes. Make a request to your System Administration team to correct the nodes to upper case before you work on the service pack upgrades and the cumulative update package

REFERENCES:

Unable to create Maintenance Plans or DBMail Fails after service pack/Hotfix is applied on SQL Server 2005 fail over cluster instance running on Windows 2008 cluster
http://blogs.msdn.com/b/sqlserverfaq/archive/2009/03/16/unable-to-create-maintenance-plans-after-service-pack-2-is-applied-on-sql-server-2005-fail-over-cluster-instance-running-on-windows-2008-cluster.aspx

Resource Database
http://msdn.microsoft.com/en-us/library/ms190940(v=sql.90).aspx

How to get notified on SQL Server Agent and/or SQL Server Service restarts in addition to identifying the current active node for the failover cluster?

Unlike your TV, SQL Server can’t be turned off or on as per your desire. For mission critical SQL Servers, the uptime is extremely critical as one or more applications are connected to the databases to support the application users at all times throughout the day and possibly throughout many regions around the world. Planned maintenance downtime required for version upgrades, service pack upgrades, hot fixes, and production deployments or releases of which will require services or host reboots must be coordinated, planned, and approved by the management team. In case of unexpected service interruptions such as SQL Server Agent and SQL Server Service restarts and/or failover for the clusters, you must be notified and find out and document the root cause to prevent future surprises. How can we do that?

As we all know, when we schedule a SQL Server Agent job, one of the possible schedule types is “Start automatically when SQL Server Agent starts.” We can use this feature to notify us via database mail on SQL Server Agent and/or SQL Server Service restarts. When a failover on a cluster occurs, SQL Server service will also get restarted. We will identify the current active node for the failover clusters.

Here are the overview steps and concepts:

  1. Develop the T-SQL code to detect SQL Server Agent and/or SQL Server Service restarts in addition to finding the current active node in the cluster.
  2. Schedule a SQL Server Agent job to run the T-SQL code whenever SQL Server Agent starts.

Here are the implementation steps:

  1. Develop the T-SQL code to detect SQL Server Agent and/or SQL Server Service restarts in addition to finding the current active node in the cluster.
  2. -- Alert on SQL Server Agent and/or SQL Server service restarts
    -- Replace your recipients in line # 14
    
    -- declare the required variables
    DECLARE @Message VARCHAR(MAX)
    , @Subject VARCHAR(123)
    , @Recipients VARCHAR(123)
    , @IsDBMailEnabled BIT
    , @MailProfile VARCHAR(123)
    , @IsClustered VARCHAR(1) -- this is because SERVERPROPERTY('IsClustered') is a sql_variant data type
    , @CurrentNodeName VARCHAR(123)
    , @InstanceName VARCHAR(123)
    , @RestartTime VARCHAR(123)
    
    set @Recipients = 'email@company.com' -- specify the recipients here, separate with ; 
    
    SELECT @InstanceName = CONVERT(VARCHAR, SERVERPROPERTY('ServerName'))
    , @IsClustered = CONVERT (VARCHAR, SERVERPROPERTY('IsClustered'))
    , @CurrentNodeName = CONVERT (VARCHAR, SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))
    
    -- SQL Server service has been restarted
    IF (
    	SELECT DATEDIFF(MI, CREATE_DATE,GETDATE() ) 
    	FROM SYS.DATABASES 
    	WHERE NAME = 'TEMPDB'
    	) <= 3 -- SQL Server service restarted in the past 3 minutes
    	BEGIN
    		SELECT @RestartTime = CONVERT(VARCHAR, create_date, 13)
    		FROM SYS.databases
    		WHERE NAME = 'TEMPDB'
    		
    		SET @Message = @InstanceName + ' SQL Server Agent service and SQL Server service have been restarted at: ' +@RestartTime
    		SET @Subject = @InstanceName + ' SQL Server Agent service and SQL Server service have been restarted!'
    
    		
    		IF @IsClustered = '1'
    			BEGIN
    				SET @Message = @Message + ' and the current active node is: ' +@CurrentNodeName+ '.'
    				SET @Subject = @Subject + ' The current active node is: ' +@CurrentNodeName+ '.' 
    			END
    	END
    ELSE 	
    	BEGIN
    		SET @Message = @InstanceName + ' SQL Server Agent service has been restarted!'
    		SET @Subject = @InstanceName + ' SQL Server Agent service restarted!'
    	END 
    
    SET @Message = @Message + CHAR(10)
    SET @Message = @Message + 'If this is not a planned maintenace. Please verify the status of the databases before restarting any application services and review the log files to find the possible root causes!'	
    
    -- Find the database mail profile name
    create table #dbmail_profile
    (
    profileid int null,
    profilename varchar(125) null,
    accountid int null,
    accountname varchar(125) null,
    sequencenumber int null
    )
    insert #dbmail_profile
    EXECUTE msdb.dbo.sysmail_help_profileaccount_sp;
    
    select @MailProfile = profilename
    from #dbmail_profile
    where sequencenumber = 1
    
    drop table #dbmail_profile
    
    EXEC msdb.dbo.sp_send_dbmail
    	@profile_name = @MailProfile,
    	@recipients = @Recipients,
    	@subject = @Subject,
    	@body_format = 'TEXT',
    	@body = @Message;
    
  3. Schedule a SQL Server Agent job to run the T-SQL code whenever SQL Server Agent starts.
  4. Create a SQL Server Agent job the way as you have always been doing, then on the schedule page when choosing a “schedule type,” select the “Start automatically when SQL Server Agent starts” option.
    New Job Schedule

If you need to also identify the previous active node for the failover cluster in addition to the current active node, you will need to create a table in a “utility” database or the master database to store the cluster nodes information, then on SQL Server service restarts make a comparison of the previous active node and the current active node and embed the information on the notification email.

If you wish to check if other instances of SQL Server are running and you don’t have a third party monitoring system, you can schedule a SQL Server Agent job to ping the host of the other instances or check for their SQL Server Agent and SQL Server services statuses.

 

REFERENCES:

SERVERPROPERTY
http://msdn.microsoft.com/en-us/library/ms174396(v=sql.105).aspx

DATEDIFF
http://msdn.microsoft.com/en-us/library/ms189794(v=sql.105).aspx

How to install SQL Server on an active/passive cluster?

SQL Server clustering is a collection of two or more physical servers known as nodes with access to shared resources forming a public network that serves as the interface for applications. SQL Server service, SQL Server agent service, and disks are example of shared resources. Each of the nodes communicates constantly with each other through an internal private network inside the cluster. This is also known as the heartbeat.  The heartbeat checks whether a node is up and running. Only one node in an active/passive cluster can access to the shared resources at a time. When active node losses heartbeat or communication to the other node in the cluster, the passive node will take over the ownership of shared resources to service all requests coming in to the cluster. This is known as failover.

SQL Server clustering is created on top of Microsoft Cluster Services (MSCS).  MSCS is capable of detecting hardware and software failures and automatically change the control of the resources to a functioning node when needed.  To coordinate the distributed transactions between 2 or more nodes, the Microsoft Distributed Transaction Control (MSDTC) service is needed. Within a cluster, the Quorum Drive contains the update-to-date information about the state of the cluster. Without it, the cluster can’t function at all.

Prior to installing SQL Server on a cluster, you should ask or work with the System Administrators to install and configure the Microsoft Cluster Services (MSCS). In addition, you should have your checklist ready for verification and preparation for the installation. It is recommended that you write down the answer next to each of the check items. The following is an example of such list:

  1. Know the nodes for the cluster and their ip addresses and access to both nodes.
  2. The SQL Server installation product key.
  3. The SQL Server components to be installed.
  4. The SQL Server virtual server name.
  5. Default or named instance?
  6. The shared disks to select.
  7. The SQL Server virtual IP address.
  8. The subnet mask. Usually it is 255.255.255.0
  9. The dedicated domain account for the SQL Server service.
  10. The dedicated domain account for SQL Server Agent service.
  11. The dedicated domain accounts for each of the services to be installed (SSAS, SSIS, and/or SSRS).
  12. The SQL Server Collation to select.
  13. Windows or Mixed authentication mode?
  14. The list of users to be the SQL Server administrator for the instance.
  15. The default locations for the data and log files for the user databases and tempdb and also the location for the backup directory.
  16. Do you need to enable FILESTREAM?

Before running the installation wizard, you should run the validation wizard for your cluster configuration to make sure all components are compliant:

  1. Click the “Start” button then click on “Administrative Tools” and then click on “Failover Cluster Management.”
  2. In “Failover Cluster Manager,” click on the “Validate a Configuration” link.
  3. In “Select Server or a Cluster,” browse to or enter the cluster name or the nodes.
  4. In “Testing Options,” select the option “Run all tests (recommended)” and then click “Next.”
  5. In “Review Storage Status,” select the option “Take the listed services or applications offline, so that the associated disks can be tested. These services or applications might be unavailable for a significant time. They will be brought online again after the tests.”
  6. Click “Next.”
  7. Review the “Failover Cluster Validation” report.

After you had the checklist items filled in and ran the cluster configurations validation check, you are ready for the installation of SQL Server on the cluster. Here we go:

  1. Insert the CD or on the installation folder, run setup.
  2. On the “SQL Server Installation Center” page, click “Installation” link on the left hand column, then click on the link “New SQL Server failover cluster installation” on the right.
  3. On the “Setup Support Files” page, click the “Install” button.
  4. The System Configuration Checker runs a discovery operation on your computer. Review the details by clicking on the “Show details >>” button if necessary. Click the “Ok” button to continue.
  5. On the “Product Key” page, key in the product key and then click the “Next” button.
  6. On the “License Terms” page, check the “I accept the license terms” checkbox and then click the “Next” button to continue.
  7. On the “Setup Support Rules” page, review and correct the errors and warning if there are any before proceeding. If all green, meaning successfully, then click the “Next” button to continue.
  8. On the “Feature Selection” page, select the components that you need not what you wish to have for your installation.
  9. On the “Instance Configuration” page, key in the SQL Server virtual name in the “SQL Server Network Name” textbox and also determine if you wish to have a default instance or a named instance. Make the selection for the corresponding radio button. If you selected the “Named instance” option, you must key in the name for the named instance. By default, the instance named is used as the Instance ID. You can leave as the default. Click the “Next” button to continue.
  10. On the “Disk Space Requirements” page, the installer checks if you have enough disk space on your local disks to install the SQL Server 2008 binaries. You should see the green check mark(s) indicating that you have sufficient amount of disk space for the installation. Click the “Next” button to continue.
  11. On the “Cluster Resource Group” page, select “SQL Server (MSSQLSERVER)” in the drop-down box for “SQL Server cluster resource group name:” or you can type in a new one as the cluster resource group. Click the “Next” button to continue.
  12. On the “Cluster Disk Selection” page, select the shared disks for the SQL Server resource cluster group. Click the “Next” button to continue.
  13. On the “Cluster Network Configuration” page, specify the “IP Address” and “Subnet Mask” for the SQL Server cluster. Deselect the checkbox for “DHCP” as you will be using static IP addresses. Uncheck “IPv6” if it is checked. Click the “Next” button to continue.
  14. On the “Cluster Security Policy” page, select the option “Use service SIDs (recommended).” This is the recommended option for Windows Server 2008 and later.
  15. On the “Server Configuration” page and in the “Service Accounts” tab, specify the “Account Name” and “Password” for the SQL Server Agent service, the SQL Server Database Engine service, and other services that you had selected in the components section page. Microsoft recommends individual accounts for each of the services in addition to following the principle of Least Amount of Privileges.
  16.  Still on the “Server Configuration” page, click the “Collation” tab and then specify the collation as per the application(s) requirements. Don’t select the default unless you are absolutely sure or else it will be a royal pain in the behind to correct this. Stop and confirm the correct collation before moving on. Seriously! Once you have selected the collation, click the “Next” button to continue.
  17. On the “Database Engine Configuration” page and in the “Account Provisioning” tab, select the option “Windows authentication mode” or the option “Mixed Mode (SQL Server authentication and Windows authentication).”  If you had selected the latter, then you must key in the “sa” password. In the “Specify SQL Server administrators” section, click the “Add Current User” button to add the user running the installation as an administrator. You can also add more users by clicking on the “Add…” button.
  18. Still on the “Database Engine Configuration” page, click the “Data Directories” tab to specify and default directories for the data, user databases data and log, tempdb data and log, and backup.
  19. Still on the “Database Engine Configuration” page, click the “FILESTREAM” tab to enable Filestream on this instance if needed. Click the “Next” button to continue.
  20. On the “Error and Usage Reporting” page, check the corresponding optional checkboxes if you wish then click the “Next” button to continue.
  21. On the “Cluster Installation Rules” page, the installation validation system validates the configuration and selection of the specified SQL Server components.  If everything passed, clicked the “Next” button to continue.
  22. On the “Ready to Install” page, you can see the components that were selected. Click the “Install” button to start the installation.
  23. On the “Installation Progress” page, you can see the installation progress status as the bar move to the right end.
  24. Cross your fingers and wait for the “Complete” page and hope to see the green check mark “Your SQL Server 2008 failover cluster installation is complete.” Click the “Close” button to end.

You will now need to add the second node in the failover cluster to enable high-availability.

  1. On the passive node, start the installation.
  2. On the “SQL Server Installation Center” page, click the “Installation” link on the left hand column and click on the “Add node to a SQL Server failover cluster” link on the right.
  3. On the “Setup Support Rules” page,  validate the checks return all successful results, then click the “Ok” button to continue.
  4. On the “Product Key” page, key in the product key and then click the “Next” button.
  5. On the “License Terms” page, check the “I accept the license terms” checkbox and then click the “Next” button to continue.
  6. On the “Cluster Node Configuration” page, select the virtual SQL Server instance name on the “SQL Server instance name” drop-down box. Click the “Next” button to continue.
  7. On the “Service Accounts” page, specify the “Account Name” and “Password” for the SQL Server Agent service, the SQL Server Database Engine service, and other services that you had selected in the components section page. Microsoft recommends individual accounts for each of the services in addition to following the principle of Least Amount of Privileges. Once done, click the “Next” button to continue.
  8. On the “Error and Usage Reporting” page, review and correct the errors and/or warnings if needed and then click the “Next” button to continue.
  9. On the “Ready to Add Node” page, review the configurations and selections you have made and then click “Next” to continue.
  10. On the “Add Node Progress” page, the status progress bar will move to the right end as per the installation progress.
  11. On the “Complete” page, nothing is sweeter than seeing the green check mark next to “Your SQL Server 2008 failover cluster add node operation is complete.” Click the “Close” button to end.

This is what the SQL Server cluster looks like in the Cluster Admin: Screen-shot.

After verifying that the latest services pack is compatible with the application(s) you can then apply the latest service pack or hotfixes on the Active/Passive SQL Server cluster with the following steps:

  1. On the passive node (Node2), apply the service pack or hotfixes.
  2. Reboot the passive node (Node2).
  3. On the active node (Node1), failover the SQL Server resource. The passive node (Node2) that you had already patched will become the active node. To failover the SQL Server resource, open the “Failover Cluster Management,” under “Services and Applications,” right-click on “SQL Server (MSSQLSERVER),” then click on “Move this service or application to another node >” and click on the option to move to the passive node.
  4. On the passive node (Node1), apply the service pack or hotfixes.
  5. Reboot the passive node (Node1).

You can verify the current service pack and version build number by running the following query:

-- Querying the SQL Server Instance level info
SELECT
	SERVERPROPERTY('ServerName') AS [SQLServer]
	,SERVERPROPERTY('ProductVersion') AS [VersionBuild]
	,SERVERPROPERTY ('Edition') AS [Edition]
	,SERVERPROPERTY('ProductLevel') AS [ProductLevel]
	,SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsWindowsAuthOnly]
	,SERVERPROPERTY('IsClustered') AS [IsClustered]
	,SERVERPROPERTY('Collation') AS [Collation]
	,SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [CurrentNodeName]

How to apply service packs or hotfixes on an Active/Passive SQL Server cluster?

As per Microsoft, you need to apply the latest service pack or hotfixes to resolve a bug that was discovered by a SQL Server Agent job failure or stumbled on by your development team. How can you prepare and how should you apply service packs or hotfixes on the Active/Passive SQL Server cluster?

Preparation steps for the production server after you had already tested the service pack or hotfixes on a development server:

  1. Request a scheduled maintenance window of 1 hour or more. Usually in the late evenings or weekends, depending on your business.
  2. Once approved, notify the users or required teams of the scheduled maintenance window.
  3. Download the service pack or hotfixes to a shared drive or to a local drive.
  4. Backup all databases.
  5. Script out all SQL Server Agent jobs.
  6. Script out all the logins and permissions for the logins.

If your System Administration team has third party software to take snapshot of the servers, ask them nicely to do so.

Applying the service pack or hotfixes on the Active/Passive SQL Server cluster:

  1. On the passive node (Node2), apply the service pack or hotfixes.
  2. Reboot the passive node (Node2).
  3. On the active node (Node1), failover the SQL resource. The passive node (Node2) that you had already patched will become the active node.
  4. On the passive node (Node1), apply the service pack or hotfixes.
  5. Reboot the passive node (Node1).

You can verify the current service pack and version build number by running the following query:

-- Querying the SQL Server Instance level info
SELECT	
	SERVERPROPERTY('ServerName') AS [SQLServer]
	,SERVERPROPERTY('ProductVersion') AS [VersionBuild]
	,SERVERPROPERTY ('Edition') AS [Edition]
	,SERVERPROPERTY('ProductLevel') AS [ProductLevel]
	,SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsWindowsAuthOnly]
	,SERVERPROPERTY('IsClustered') AS [IsClustered]
	,SERVERPROPERTY('Collation') AS [Collation]
	,SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [CurrentNodeName]