November 26, 2010

BB Note 005 - SQL server Pre checks before SQL Server Cumulative Update. SQL server cummulative update fails

We had a major change failed and I got a chance to talk to an MSSQL Server guru to discuss it.
The notes below are noted to be checked and hopefully this will help other SQL server administrators out there.

1. Bring all the resources in the cluster online before starting the installation.

2. The file system should be NTFS. We have known issues during the installation with FAT32

3. If possible, we suggest you to directly download SP3 CU11 to your local attached hard disk. http://support.microsoft.com/kb/2258854

4. Ensure that you have no compressed drives involved in this installation. If you have, please decompress the drive and run the setup again. Known Issue: please refer to: http://support.microsoft.com/kb/231347

5. Temporarily turn off any Anti-Virus software if possible (Sometimes, the anti-virus monitor/scan services would lock some files that prevent the SP setup to update and upgrade these files).

6. Ensure you physically login at the PC (rather than installing via Remote Desktop Protocol).

7. Ensure the account you are using to run SP3 CU has Administrator privileges. Please also ensure the account has FULL CONTROL permissions on the DATA folder (including all the files under this folder; by default, it is located at: D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data). Known issue: KB 918357 and KB 916766

• Right click on Data -> Properties -> Security -> Grant FULL CONTROL permission to the account you are using to setup SP3 -> click Advance Button -> check the box "Replace Permissions entries on all child objects with entries shown here that apply to child objects" -> click OK to modify permissions on all files and folders inside the Data folder.

8. Please also ensure the account has the FULL CONTROL permission on the following WMI namespace:

· Start -> Run -> wmimgmt.msc
• Right click on "WMI Control (Local)" -> Properties -> Security tab
• Expand the root tree to \root\Microsoft\SqlServer\ServerEvents
• Make sure that the account running SQL Server setup has Full Control on this namespace and all its sub-namespaces.

9. Clean out the %TEMP% folder.
This is to be done BEFORE installation

10. Check if the user account with which we are installing the hotfix has the following rights. Gpedit.msc


• Act as Part of the Operating System = SeTcbPrivilege
• Bypass Traverse Checking = SeChangeNotify
• Lock Pages In Memory = SeLockMemory
• Log on as a Batch Job = SeBatchLogonRight
• Log on as a Service = SeServiceLogonRight
• Replace a Process Level Token = SeAssignPrimaryTokenPrivilege
http://technet.microsoft.com/en-us/library/bb457125.aspx

11. Test the Failover to all nodes

12. Start the installation from the active node

13. Follow the below steps to check if you are able to successfully create the scheduled task. OK

• In the Node 1, Click the Start Menu -> Run
• Type \\Node2 (for ex: \\lab02 where lab02 is the network name of the node 2)
• Check whether the “Scheduled Tasks” is visible
• Explore that and create a new task as “Sample”
• Right Click the task -> Properties
• Type this command in the “Run” cmd /c “Ver”
• In the “Run As” -> provide the user account with which we are trying to install the SQL Server. And provide the password. Click “Ok”
• Run the “Sample” task
• Once it gets executed, Click on to the “Advanced” Menu in the Explorer
• Choose “View Log” to view the log.
• Navigate to the bottom most position and check whether the previously executed job has succeeded.
• If it is succeeded
• Repeat the same steps in the second node.

14. Make sure no one is logged in to the remote node when you are performing the installation.

15. Below are the essential services to be running on the server

Alerter

Automatic Updates <========== This must be running,
Cluster Service
Com+ Application Service <========== This must be running, DONE
Computer Browser
Cryptographic Services <========== This must be running
DHCP Client
DNS Client
Distributed File System
Distributed Link Tracking Client
Distributed Link Tracking Server
Distributed Transaction Coordinator
Event Log
IPSEC Policy Agent
License Logging Service
Logical Disk Manager
Messenger
NTLM Security Support Provider
Net Logon
Network Connectors
Plug and Play
Print Spooler <========== This must be running, for SQL Server 2008 pre-requisite install of .Net 3.5 SP1 DONE. STARTED

Process Control
Remote Procedure Call (RPC) Locator
Remote Procedure Call (RPC) Service
Remote Registry Service <========== This must be running,
Removable Storage
Security Accounts Manager
Server
TCP/IP NetBIOS Helper
Task Scheduler <========== This must be running
Telephony
Terminal Services <======= If being used to perform the installation, Time Service
Windows Management Instrumentation Driver Extensions
Windows Time Service
Workstation
NOTE: Leave any services required by SAN or network adapters on-line

16. Ensure all the connected drives have enough disk space for installation. Notice that all the local attached drives should have MORE (at least 2-3 GB) free space. If not, we suggest you temporarily move the files around to free up some space on these drives and try install again if possible. Starting with SP2, you will see a better error message if the setup fails due to space issues. Known issue – KB 918325

17. Confirm the mssqlsystemresource database files are already in the same location as the master database files. Ensure that all the four files (the 2 MDF files and the 2 LDF files for these databases) are all located in the same folder.

Known issue: Please refer to: http://support.microsoft.com/Default.aspx?id=918695 and SQL Hot fix 50001220

18. Start -> Run -> Services.msc and stop the following services if possible. Sometimes, the installation might proceed after we temporarily disable these SQL related services:

SQL Server instance,
SQL Server Full Text Search,
SQL Server Browser Service and

Indexing Service (if applicable) before running the SP setup.


[In Conclusion][REFERENCE]


You may also like to check some of the articles which are related to SQL server Clustering techniques.

v How to: Create a New SQL Server 2005 Failover Cluster (Setup) http://msdn2.microsoft.com/en-us/library/ms179530.aspx

v Failover Clustering How-to Topics http://msdn2.microsoft.com/en-us/library/ms190990.aspx

v How to: Manually Uninstall a SQL Server 2005 Failover Cluster http://msdn2.microsoft.com/en-us/library/ms180973.aspx

v How to: Add or Remove Nodes in a SQL Server 2005 Failover Cluster (Setup) http://msdn2.microsoft.com/en-us/library/ms191545.aspx

v How to: Enable Kerberos Authentication on a SQL Server Failover Cluster http://msdn2.microsoft.com/en-us/library/ms189585.aspx

v Error message when you install SQL Server 2005 SP2, SQL Server Express SP2, or SQL Server Express with Advanced Services SP2: "SQL Server Setup failed to execute a command for server configuration. CREATE DATABASE failed" http://support.microsoft.com/?id=935371

v SQL Server Setup fails unexpectedly when you try to install SQL Server 2005 Service Pack 2 http://support.microsoft.com/?id=935297

v Error message when you install or uninstall Microsoft SQL Server 2005 or SQL Server 2005 with Service Pack 2 on a Windows Vista-based or a Windows Server Longhorn-based computer http://support.microsoft.com/?id=929150