1 / 35

Objectives

Troubleshooting SQL Server 2000 Virtual Server /Service Pack Setups for Failover Clustering Uttam Parui SQL Server Developer Support Engineer Product Support Services Microsoft Corporation. Objectives. Understand Microsoft® SQL Server™ 2000 virtual server setup process

HarrisCezar
Download Presentation

Objectives

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Troubleshooting SQL Server 2000 Virtual Server /Service Pack Setups for Failover ClusteringUttam Parui SQL Server Developer Support EngineerProduct Support ServicesMicrosoft Corporation

  2. Objectives • Understand Microsoft® SQL Server™ 2000 virtual server setup process • Know what logs to review to troubleshoot a failed virtual server /service pack setup • Troubleshoot virtual server setup failures • Troubleshoot virtual server service pack setup failures • Discuss SQL Server 2000 Service Pack 3 pre-installation checklist for SQL Server 2000 virtual server

  3. Understanding SQL Server 2000 Virtual Server Setup Process • SQL Server 2000 virtual server setup involves a main setup process that starts one unattended installation for every node that is part of the virtual server • The main setup process does the following: • Gathers configuration information for the virtual server (the information is recorded in the setup initialization file, Setup.iss) • Starts unattended setup process (on every node that is a part of the virtual server) using the options specified in Setup.iss • Creates virtual server resources and sets the correct node ownership

  4. Understanding SQL Server 2000 Virtual Server Setup Process (2) • Unattended setup process • The number of unattended setup processes is equal to the number of nodes that are a part of the virtual server • Each unattended setup is completed by separate threads • An unattended setup process that is running on the node where the setup was started (also named primary node): • Copies the required binaries • Registers the components that are required to run SQL Server on that node • Creates databases on the shared drive • Runs initial configuration scripts • The unattended setup process on other nodes (also named secondary nodes) copies the required binaries and registers the components that you need to run SQL Server on that node

  5. Virtual Server Setup Log Files • The main setup process creates a Sqlstp.log file on the node where the virtual server setup was started • Unattended setup processes create log files SqlstpN.log (where N is an integer) on each node where the unattended setup is run • After the unattended setup processes complete successfully on all the nodes, the main setup process creates virtual server resources and sets the correct node ownership • Setup writes detailed messages to the Sqlclstr.log and Cluster.log on the node where the virtual server setup was started

  6. SqlstpN.log (primary node) SqlstpN.log (secondary node) Sqlclstr.log (primary node) Virtual Server Setup Log Files (2) Sqlstp.log Configuration Information Setup is performing required operations on cluster nodes. This may take a few minutes… #### SQL Server Remote Setup - Start Time Running '\\NODE1\C$\SQL2000.ENT\x86\setup\setupsql.exe k=Nb k=Rm k=Cl -SMS -s -f "\\NODE1\C$\SQL2000.ENT\x86\setup\setup.ins" -f1 \\NODE1\admin$\NODE1_MSSQLSERVER.iss -f2 "\\NODE1\admin$\setup.log" -e "stpsilnt._ex" -b -x "C:\WINNT\TEMP\"' (NODE1) ... CProcess::RunUntilComplete [0x94] exit code: 0 Remote process exit code was '0' (NODE1). Running '\\NODE1\C$\SQL2000.ENT\x86\setup\setupsql.exe k=ClSec k=Rm k=Cl -SMS -s -f "\\NODE1\C$\SQL2000.ENT\x86\setup\setup.ins" -f1 \\NODE2\ADMIN$\NODE2_MSSQLSERVER.iss -f2 "\\NODE2\admin$\setup.log" -e "stpsilnt._ex" -x "C:\"' (NODE2) ... CProcess::RunUntilComplete [0x94] exit code: 0 Remote process exit code was '0' (NODE2). #### SQL Server Remote Setup - Stop Time CreateVirtualServerResources

  7. Virtual Server Setup Log Files (3) • Consider a cluster with the node names Node1, and Node2. Lets assume that you ran a SQL Server setup program on Node1 to install a virtual server and setup completed successfully. • Here is a summary of files that will be created on each node: • Node1 (primary node) • Sqlstp.log – created by the main setup process • Sqlstp0.log and Setup.log – created by the unattended setup process on Node1 • Sqlclstr.log – created by the main setup process when it creates the virtual server resources • Node2 (secondary node) • Sqlstp0.log and Setup.log – created by the unattended setup process on Node2

  8. Virtual Server Setup Log Files (4) • Consider a cluster with the node names Node1, and Node2. Lets assume that you ran SQL Server setup program on Node1 to install a virtual server, and setup failed while running the unattended setup on Node1 and unattended setup never started on Node2. • Here is a summary of files that will be created on each node: • Node1: Sqlstp.log, Sqlstp0.log, and Setup.log • Node2: No files will be created

  9. Virtual Server Setup Log Files (5) • Now assume you troubleshoot the issue and then rerun the setup, and the setup completes successfully • Here is a summary of files that will be created on each node • Node1 • Sqlstp.log – created by the main setup process (this is a new file – the main setup process overwrites the previous Sqlstp.log) • Sqlstp1.log and Setup.log – created by the unattended setup process on Node1 • Note: The file name is Sqlstp1.log because there is already a file named Sqlstp0.log on Node1 • Sqlclstr.log – created by the main setup process when it creates the virtual resources • Node2 (secondary node) • Sqlstp0.log and Setup.log – created by the unattended setup process on Node1

  10. Virtual Server Setup Log Files (6) • Note: • Sqlstp.log is overwritten every time the virtual server setup is started • Log files (SqlstpN.log) created by unattended setup are not overwritten • The unattended setup process does not create log files with the same name on both the nodes • The log file name is dependent on the last SqlstpN.log on the node • The virtual server setup process creates log files that start with the characters ‘sqlstp’, whereas the service pack setup logs start with the characters ‘sqlsp’ • So, the main service pack setup process will create Sqlsp.log on the node where the service pack setup was run, and will create SqlspN.log on other nodes that are part of the virtual server

  11. Virtual Server Setup Log Files (7) Summary: Setup logs created by SQL Server 2000 virtual server setup Note: • The default location for SqlstpN.log, Sqlclstr.log and Setup.log is C:\WINNT • The default location for Cluster.log is C:\WINNT\Cluster

  12. Troubleshooting Virtual Server Setup Failure • Basic steps • Review the Sqlstp.log file on the node where you ran the virtual server setup and identify where the setup failed • After you identify where the setup failed, review the appropriate setup files to find the exact error message • Use the keywords in the error message to perform a search on the Microsoft Knowledge Base to find more detailed information about the error, and the solution to the problem

  13. Sample Setup.log Created by Unattended Installation • The meaning of the ResultCode can be found by opening an MS-DOS window and typing net helpmsg ResultCode • If the result code is –ve (for example, -1), net helpmsg will not display any information. For –ve result code, refer to SQL Server 2000 Books Online (BOL) topic “Error Codes for an Unattended Installation.” The next slide has the table from BOL. • [InstallShield Silent] • Version=v5.00.000 • File=Log File • [Application] • Name=Microsoft SQL Server • Version=8.00.000 • Company=Microsoft • Lang=0000 • [Status] • Completed=1 • RebootRequired=0 • [ResponseResult] • ResultCode=0

  14. Error Codes for an Unattended Installation

  15. Troubleshooting Virtual Server Setup Failure (2) Virtual server setup failed with the following error: Setup failed to perform required operations on the cluster nodes • This is a generic message and all you can conclude from this message is that the setup failed while running unattended setup on one or all the cluster nodes • In the following two examples, we will learn how to start with a generic error message, identify the underlying problem, and then with the help of the exact error message and the Microsoft Knowledge Base, resolve the issue

  16. Troubleshooting Virtual Server Setup Failure (3) • Example 1 • The first step will be to review the Sqlstp.log on the node where the virtual server setup was started and discover where the failure occurred • In Sqlstp.log, search for the line:“Setup is performing required operations on cluster nodes. This may take a few minutes...” • Assume this is the error footprint in the Sqlstp.log: 19:18:30 Setup is performing required operations on cluster nodes. This may take a few minutes... 19:18:30 C:\WINNT\TEMP\SqlSetup\Bin\remsetup.exe C:\WINNT\remsetup.ini 19:19:25 Process Exit Code: (2) The system cannot find the file specified. 19:19:25 Begin Action : GetRemsetupRetCode 19:19:25 Installation return status on NODE1 : 2 19:19:25 Installation return status on NODE2 : 2

  17. Troubleshooting Virtual Server Setup Failure (4) • Example 1 (continued) • Note: Installation return status code on both nodes is 2 • net helpmsg 2 • Returns “The system cannot find the file specified” • This message is not very helpful. • To identify the exact error, we must review the SqlstpN.log on both nodes (because the failure occurred on both nodes) • Let us assume that SqlstpN.log on both nodes have the following similar error footprint 19:18:32 [DlgVirtServer] 19:18:32 Result = 1 19:18:32 VirtServerName = SQLVirtualName 19:18:32 IpAddresses = X.X.X.X,255.255.255.0, PUBLIC (Upper port) 19:18:32 ShowDlgVirtualServer returned: -1 19:18:32 ShowDlgVirtualServer: GetLastError returned: 87 19:18:32 The parameter is incorrect

  18. Troubleshooting Virtual Server Setup Failure (5) • Example 1 (continued) • This shows that the unattended setup failed on the nodes with error “87 - The parameter is incorrect” • This error message occurs if any of the cluster resources have special characters • Notice that the name of the public network connection, “PUBLIC (Upper port)”, has the special character ‘(‘ and ‘)’ • Removing the special characters will resolve the issue • This is also documented in the Microsoft Knowledge Base article:302491, “Setup May Fail to Perform Required Operations on the Cluster Nodes When Special Characters are Used in the Resource Name”

  19. Troubleshooting Virtual Server Setup Failure (6) • Example 2 • Lets review another virtual server setup that failed with the same error message:”Setup failed to perform required operations on the cluster nodes” • As in Example 1, open Sqlstp.log on the node where the virtual server setup was started and search for the line:“Setup is performing required operations on cluster nodes. This may take a few minutes...” • Assume this is the error footprint in Sqlstp.log: 14:51:37Setup is performing required operations on cluster nodes. This may take a few minutes... 14:51:37 C:\WINNT\TEMP\SqlSetup\Bin\remsetup.exe C:\WINNT\remsetup.ini 15:04:56 Process Exit Code: (2) The system cannot find the file specified. 15:04:56 Begin Action : GetRemsetupRetCode 15:04:56 Installation return status on Node1 : 0 15:04:56 Installation return status on Node2 : 2 • Note: Installation return status code on Node1 is 0 (which means success) and on Node2 is 2 (indicating failure). To identify the exact error, review the SqlstpN.log on Node2 (because the failure occurred on Node2). SqlstpN.log on Node1 should indicate “Installation Succeeded.”

  20. Troubleshooting Virtual Server Setup Failure (7) • Example 2 (continued) • Assume this is the error footprint in SqlstpN.log on Node2: 15:04:21 ShowDlgInstanceName: GetLastError returned: 50044 15:04:21 End Action DialogShowSdInstanceName 15:04:21 End Action ShowDialogs 15:04:21 Action CleanUpInstall: 15:04:21 StatsGenerate returned: 2 15:04:21 StatsGenerate (0x0,0x1,0xf00000,0x300,1033,0,0x0,0x1000000a,0,0,0 15:04:21 StatsGenerate -1,Administrator) 15:04:21 Installation Failed. • Now we know that the unattended setup failed on Node2 with the error 50044. Unlike the last error message, this one is not very intuitive. • Next, search for this error in the Microsoft Knowledge Base • Visit http://support.microsoft.com and then click “Search the Knowledge Base.” Select “SQL Server” as the Microsoft product and type “GetLastError returned: 50044” in the Search field. This will return the article:318672, “Error Message: Setup failed to perform the required operation on the cluster nodes”

  21. Troubleshooting Virtual Server Setup Failure (8) • Example 3 • Lets review a virtual server setup that failed with the following error message An error occurred while creating virtual server resources for clustering. Setup could not connect to the database to update cluster-specific information in the system tables. • This error indicates that the failure occurred when the virtual server resources were created • Reviewing Sqlstp.log will indicate success (result code 0) on all cluster nodes 14:54:44 Installation return status on Node1 : 0 14:54:44 Installation return status on Node2 : 0 • SqlstpN.log on all nodes will indicate “Installation succeeded” • Note that the setup failed with error:“Setup could not connect to the database to update cluster-specific information in the system tables”

  22. Troubleshooting Virtual Server Setup Failure (9) • Example 3 (continued) • After the virtual resources for SQL Server are created, setup makes an ODBC connection to the SQL Server to update cluster-specific information in the system tables • If it cannot make a connection, it fails with this error message • The root cause for the above error was improper name resolution • In all cases, you can run the setup successfully after fixing the name resolution issue

  23. Troubleshooting Virtual Server Setup Failure (10) • Example 3 (continued) • Verify the following on both nodes : • Ping by name (cluster node names, cluster name, and SQL Server virtual name) • Ping by IP address (node IP addresses, cluster IP address, and SQL Server IP address) • Verify the result of the ping • Verify that the results are correct and as expected • For example, when you ping by the cluster name, make sure your reply is from the correct IP address • Verify how much time it takes for the ping to reply • Sometimes, because of name resolution problems, a ping takes a long time to return a successful echo • If the ping takes a long time, you can temporarily add host file entries on both nodes of the cluster and then see if the ping replies immediately

  24. Troubleshooting Virtual Server Service Pack Setup Failures • The basic steps for troubleshooting a virtual server service pack setup failure are similar to the steps for troubleshooting a virtual server setup failure • Instead of reviewing Sqlstp.log for service pack failures on virtual SQL Server, review Sqlsp.log file on the node where the service pack installation was started • After you identify where the service pack setup failed, review the appropriate service pack setup file (SqlspN.log) to find the exact error message • Use the keywords in the error message to perform a search in the Microsoft Knowledge Base to find more detailed information about the error, and the solution to the problem

  25. Troubleshooting Virtual Server Service Pack Setup Failures (2) • Example 4 • Lets review a virtual server service pack setup that failed with the following error message: Setup failed to perform required operations on the cluster nodes • Notice that this is the same error message that we saw in Examples 1 and 2 • As before, open the file Sqlsp.log on the node where you ran the service pack setup and search for the line:“Setup is performing required operations on cluster nodes. This may take a few minutes...”

  26. Troubleshooting Virtual Server Service Pack Setup Failures (3) • Example 4 (continued) • Lets assume this is the error footprint in Sqlsp.log: 12:54:42 Setup is performing required operations on cluster nodes. This may take a few minutes... 12:54:42 "C:\DOCUME~1\SQLExec\LOCALS~1\Temp\1\SqlSetup\Bin\remsetup.exe" C:\WINNT\remsetup.ini 13:03:08 Process Exit Code: (3010) The requested operation is successful. Changes will not be effective until the system is rebooted. 13:03:08 Begin Action : GetRemsetupRetCode 13:03:08 Installation return status on Node1 : 3010 13:03:08 Installation return status on Node2 : -1 13:03:08 End Action : GetRemsetupRetCode 13:03:08 End Action : GetRemsetupRetCode #### SQL Server Remote Setup - Start Time mm/dd/yy 12:54:42 #### Server ‘Node2' not found on the network (53).

  27. Troubleshooting Virtual Server Service Pack Setup Failures (4) • Example 4 (continued) • Note : Installation return status code on Node1 is 3010 (The requested operation is successful. Changes will not be effective until the system is rebooted) and result code on Node2 is -1 (general failure). sqlspN.log on Node1 will indicate “Installation Succeeded - Reboot Required”. • In this case, no setup files were created on Node2 • Notice the error “Server ‘Node2' not found on the network (53).” in the Sqlsp.log file net helpmsg 53 returns “The network path was not found.” • This indicates network issues • To resolve this, fix the network issue first, and then review the Windows NT® event logs and cluster logs to make sure there are no errors • Make sure that you can: • Ping each node by names and IP address • Connect to the ADMIN$ share on node2 from node1 and vice-versa • After you fix the network issues, re-run the service pack setup

  28. Troubleshooting Virtual Server Service Pack Setup Failures (5) • Example 5 • Lets review a virtual server service pack setup that failed with the following error message: An error occurred while creating virtual server resources for clustering. Cannot create a file when that file already exists. • This error indicates that the failure occurred when the virtual server resources were created • For more details, review Sqlsp.log and Sqlclstr.log on the node where the service pack setup was started • Assume that Sqlsp.log has the following error footprint: 20:22:25 Begin Action : CreateVirtualServerResources 20:22:25 Begin: BeforeFTS() 20:22:25 Begin Action : EnumerateNodes 20:22:25 End Action : EnumerateNodes 20:22:25 End: AfterFTS() 20:22:35 End Action : CreateVirtualServerResources 20:22:35 CreateVirtualServerResources returned: 183 20:28:38 Begin Action : ClusterErrorCleanup

  29. Troubleshooting Virtual Server Service Pack Setup Failures (6) • Example 5 (continued) • Assume that Sqlclstr.log has the following error footprint: cConnectString="ODBC;DSN='';DRIVER={SQL Server};SERVER=VSQL;DATABASE=master;Trusted_Connection=yes" Database 'pubs' cannot be opened because it is offline [sqlclusterSetup.cpp:1295] : 183 (0xb7): ~~~ DoClusterSetupWork • During the virtual server service pack installation, setup queries the databases to make sure that all the database files are on disk resources that have the correct dependencies, and the virtual SQL Server resource, which is necessary for the failover support • When a database is offline, setup cannot access the database and fails with the above message • To work around this problem, bring the inaccessible database online, and then rerun the service pack setup • For more details, in the Knowledge base, search for “[sqlclusterSetup.cpp:1295] : 183” to return the article:320524, “SQL Server Service Pack 2 Setup May Stop Responding in a Windows Clustering Environment If There Is an Offline Database”

  30. SQL Server 2000 Service Pack 3 Pre-Installation Checklist for SQL Server 2000 Virtual Server • Service Pack 3 (SP3) for SQL Server 2000 is a major service pack and it should be thoroughly tested in a test environment first • The basic testing goal is to make sure your applications are working correctly with the changes introduced by SP3 • To make sure your SP3 setup is successful, complete the following pre-installation checklist Checklist • Review the Readme file for SP3 and note any areas that could affect your production environment, and then test these before proceeding • Run DBCC checkdb against all your databases (system and user databases) • Resolve any unexpected errors before continuing

  31. SQL Server 2000 Service Pack 3 Pre-Installation Checklist for SQL Server 2000 Virtual Server (2) • Make sure that you have a recent and accurate backup of your system and user databases, and your full text catalogs • Stop all monitoring software services (on all nodes that are part of the virtual SQL Server) such as Microsoft Operation Manager (MOM) and any third-party products that provide similar functionality • Set this type of software for manual startup and restart the servers before you install SQL Server 2000 SP3 • Verify that all the resources in the SQL Server group that contain the instance of SQL Server come online on all nodes of the cluster • If any resource does not come online, fix that before you upgrade to SP3

  32. SQL Server 2000 Service Pack 3 Pre-Installation Checklist for SQL Server 2000 Virtual Server (3) • Verify that there are no dependencies other than those created by the SQL Server setup on any SQL Server cluster resources • Resources such as file shares, FTP servers, and Microsoft Internet Information Services (IIS) servers must always have a unique network name and an IP address resource • Remove any such non-default dependencies before you install SQL Server 2000 SP3 • While performing the cluster portion of SQL Server 2000 SP3, the setup will reset all cluster resources to the default properties for all resources in the group, whether they are related to SQL Server or not • Make sure to note all settings before you install SQL Server 2000 SP3

  33. SQL Server 2000 Service Pack 3 Pre-Installation Checklist for SQL Server 2000 Virtual Server (4) • SP3 installation will fail if the “Unsigned non-driver installation behavior local security policy for Windows 2000” was set to "Do not allow installation” • Review the Readme file for more information • Set unnecessary services for manual startup on all nodes of cluster and restart nodes before upgrading • For a list of services that should be left running, see the “Prerequisites” section of the Microsoft white paper “SQL Server 2000 Failover Clustering” • Log on all nodes of the cluster using the startup account of the virtual SQL Server service • Set MASTER, MODEL, and MSDB databases to auto grow on Data and log file • Remove Read Only option from user databases and restrict access to DBO • Disable any agent jobs that might interfere with the upgrade

  34. Additional Resources • Microsoft White Paper - SQL Server 2000 Failover Clustering http://www.microsoft.com/SQL/techinfo/administration/2000/failovercluster.asp • Microsoft Knowledge Base Articles 302491, “Setup May Fail to Perform Required Operations on the Cluster Nodes When Special Characters are Used in the Resource Name” 318672, “Error Message: "Setup failed to perform the required operation on the cluster nodes“” 320524, “SQL Server Service Pack 2 Setup May Stop Responding in a Windows Clustering Environment If There Is an Offline Database” 254321, “INF: Clustered SQL Server Do's, Don'ts, and Basic Warnings” 811168, “INF: SQL Server 2000 Service Pack 3 Pre-Installation Checklist for SQL Server 2000 Virtual Server for Failover Clustering” • SQL Server 2000 Books Online topic "Failover Clustering”

More Related