Matrix DataBase Solution Pack for SQL Server™ Installation and Administration Guide Matrix DataBase Solution Pack for SQL Server™ 3.4.
Copyright © 2004-2007 PolyServe, Inc. Use, reproduction and distribution of this document and the software it describes are subject to the terms of the software license agreement distributed with the product (“License Agreement”). Any use, reproduction, or distribution of this document or the described software not explicitly permitted pursuant to the License Agreement is strictly prohibited unless prior written permission from PolyServe has been received.
Contents 1 Introduction MxDB for SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Plan Your SQL Configuration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Active-Active. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Many-to-One . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . SQL Monitors and Failover. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Contents iv Copy the Service Pack Files. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Install the Service Pack . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Install a SQL Server Hotfix . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Installation Considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Installation Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Other Installation Tasks. . . . . . .
Contents v Uninstall Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Connection Issues. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . DNS Misconfiguration Causes Connection Failure . . . . . . . . . . Connection Error With Windows Authentication. . . . . . . . . . . . Windows Authentication Fails with SQL Server Replication . . “Registry Replicator Undefined” Alerts . . . . . . . . . . . . . . . . . . . . . . .
Contents vi Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123 References . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124 Index Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
1 Introduction Matrix DataBase Solution Pack for SQL Server™ (MxDB for SQL Server) provides high availability for SQL Server databases located on PolyServe Matrix Server cluster filesystems. MxDB for SQL Server MxDB for SQL Server uses Virtual SQL Servers to provide failover support and high availability. A Virtual SQL Server “virtualizes” the network name and IP address associated with a particular SQL Server/Instance.
Chapter 1: Introduction 2 Plan Your SQL Configuration Before installing SQL Server, it is important to determine how the SQL Server instances should be configured across the matrix. First determine the number of SQL instances that you will need. These instances can be installed on any node in the matrix. Next, determine how you want to associate the instances with Virtual SQL Servers.
Chapter 1: Introduction 3 Node 1 Node 2 Primary: virtual1 instance1 Backup: virtual2 instance2 instance3 Primary: virtual2 instance2 instance3 Backup: virtual1 instance1 Many-to-One In this configuration, one node provides backups for the Virtual SQL Servers running on the other nodes. In the following example, nodes 1, 2, and 3 are each hosting a Virtual SQL Server. These virtual servers all use node 4 as a backup.
Chapter 1: Introduction 4 This configuration supports only one failover at a time. If the Virtual SQL Server on node 1 (“virtual1”) fails over and node 2 then goes down, the Virtual SQL Server on node 2 (“virtual2”) will not be able to fail over. This occurs because “instance1” on node 4 is currently providing access to the databases for the “instance1” associated with the Virtual SQL Server “virtual1.
Chapter 1: Introduction 5 What Happens During a Failover? Failovers can be planned (for example, upgrading the operating system) or unplanned (such as a crash of a primary node). MxDB for SQL Server takes these actions during a failover: • Removes the IP address for the Virtual SQL Server from the original node and then adds it to the backup node. • Starts SQL Server and SQL Agent on the backup node and updates registry keys.
2 Install SQL Server This chapter describes how to install SQL Server 2000 or 2005 instances and Service Packs and certain SQL Server 2000 hotfixes. SQL Server Installation Overview Prerequisites Before installing SQL Server, the following prerequisites should be met: • PolyServe Matrix Server must be installed on each node. You will also need to create PSFS cluster filesystems to store the SQL databases. If you will be using a PSFS filesystem created under Matrix Server 3.
Chapter 2: Install SQL Server 7 Supported SQL Server Versions MxDB for SQL Server can be used with these versions of SQL Server: • SQL Server 2000 SP4 Standard and Enterprise Editions (32-bit only). • SQL Server 2005 Standard and Enterprise Editions (32-bit and 64-bit). SQL Server Installation Checklist The installation procedure consists of the following steps, which are described in detail following this checklist. Action Description Pre-Installation Determine how to configure SQL Server.
Chapter 2: Install SQL Server Action Description Install a Service Pack. Copy Service Pack files to a PSFS filesystem and then install the Service Pack. (Service Pack 4 requires that Service Pack 3 first be installed.) See “Install a SQL Server 2000 Service Pack” on page 32. Install any hotfixes. Copy the hotfix to a PSFS filesystem and then install the hotfix. See “Install a SQL Server Hotfix” on page 35. Install any additional SQL instances.
Chapter 2: Install SQL Server 9 • The SQL Server 2005 instance installation captures and stores the current IP network configuration of the node on which it is installed. If you later need to change the network configuration (for example, to remove a NIC or change its IP), it is necessary to devirtualize the Virtual SQL Servers on the affected node, make the needed changes, and then revirtualize the Virtual SQL Servers.
Chapter 2: Install SQL Server 10 • Use the cacls.exe utility to give the local Administrators group full control and to give Everyone read-only permissions. In the following command, %1 is the mounted folder. cacls %1 /t /m /p Administrators:F Everyone:R For example: cacls c:\psfs\psd1p1 /t /m /p Administrators:F Everyone:R Installation Steps The installation procedure consists of the following steps: 1. Copy the SQL Server 2005 setup files to a PSFS filesystem. 2.
Chapter 2: Install SQL Server 11 NOTE: The setup files need to be copied to a PSFS filesystem only once. Subsequent SQL Server 2005 installations will use the setup files on the PSFS filesystem, and, if the “Copy media to cluster filesystem” task is selected, the SQL Install window will display the next task, “Record unattended installation template,” instead. Location of media: Specify the location of the SQL Server 2005 product files.
Chapter 2: Install SQL Server 12 button to have Matrix Server look for the DVD. (It will scan all nodes in the matrix.) The Search Media feature works only with DVDs. Copy to: Specify a folder to store the SQL Server files that will be copied. (The folder will be created if it does not exist.) This folder must be on a PSFS filesystem that can be accessed by all nodes in the matrix. You can click the browse button (labeled “...”) to locate the folder.
Chapter 2: Install SQL Server 13 Template file name: Specify a name for the template file, for example, template.ini. Select MSSQL 2005 components: Use this pane to select the SQL Server components that you want to install. To install the entire product, select the first line, “SQL Server Product.” You can select or deselect components as necessary. Enter Properties: Specify the logon account and password for each SQL Server component that you selected. (We recommend that you use a Domain account.
Chapter 2: Install SQL Server 14 Install a SQL Server 2005 Instance on Multiple Nodes Before starting the installation, be sure that the folders containing the following files are on a PSFS filesystem that can be accessed by all nodes in the matrix. • The installation file (.ini) that you recorded earlier. • The SQL Server 2005 product files that were copied earlier. This procedure installs the SQL Server instance on the nodes that you select, based on the information in the .ini file.
Chapter 2: Install SQL Server 15 NOTE: For each node, the common path will be appended by the node name and instance name to make a unique path name. Instance: In the Name field, specify the name that should be assigned to this SQL Server instance. If you are installing the default instance, enter default as the name. Nodes: Select the nodes on which the SQL instance should be installed. Click the Add Node button to display the Select Nodes window, which lists the available nodes.
Chapter 2: Install SQL Server 16 • If the instance has not been installed, add the following lines to the .
Chapter 2: Install SQL Server 17 PolyServe Knowledge Base article “Install SQL Server 2005 Hotfix 2153” for more information.) Service Pack and Hotfix Names The installation procedure expects the names of SQL Server service packs and hotfixes to follow the standardized naming schema defined in the following Microsoft Knowledge Base article: http://support.microsoft.com/kb/822499 The installation will fail if the name of a service pack or hotfix has been changed and it no longer matches the schema.
Chapter 2: Install SQL Server 18 Location of hotfix installer file: Specify the location of the Service Pack file. Node containing media: This is the Matrix Server node containing the media. Copy to: Specify a folder to store the file that will be copied. This folder must be on a PSFS filesystem that can be accessed by all nodes in the matrix. When you have completed the information, click the Copy button. A message will appear when the copy is complete. You do not need to record the installation.
Chapter 2: Install SQL Server 19 Location of hotfix installer file: Specify the location of the installer file for the Service Pack. Instance: In the Name field, specify the instance that you are updating. If you are updating the default instance, enter default as the name. Nodes: Click the Add Node button to display the Select Nodes window, which lists the available nodes. Select the nodes on which the SQL instance is installed.
Chapter 2: Install SQL Server 20 on a PSFS volume where the hidden administrative share is not yet created by LAN Manager, the setup will fail. To work around this problem, use the following command to create an administrative share on the PSFS volume. In the command, s:\ is the PSFS volume.
Chapter 2: Install SQL Server 21 Supported SQL Server 2000 Installations SQL Server 200 instances can be installed on the following versions of the operating system: • Windows 2003 SP1 or SP2, 32-bit • Windows 2003 R2 (or R2 with SP2), 32-bit or 64-bit Installation Steps The installation procedure consists of the following steps: 1. Copy the SQL Server 2000 setup files to a PSFS filesystem. 2. Record an installation file for SQL Server 2000. 3. Install a SQL instance on the appropriate nodes. 4.
Chapter 2: Install SQL Server 22 Location of media: Specify the location of the SQL Server 2000 setup files. If you are installing from the SQL Server 2000 distribution media, it should be placed in a local drive on one of the nodes in the matrix. If you are not installing from CD, the setup files must be in a location that can be accessed by the current node. NOTE: MxDB for SQL Server cannot copy from a UNC share. It can copy only from a local CD/DVD or a local drive to the SAN.
Chapter 2: Install SQL Server 23 When you have completed the information, click the Copy button. A message will appear when the copy is complete. Record the Unattended Installation This procedure records the unattended installation and creates the installation file setup.iss. The recording must be made on a node that can access the folder to which you copied the SQL Server 2000 files. NOTE: You only need to record the installation once.
Chapter 2: Install SQL Server 24 The following steps do not show all of the dialog boxes that are displayed by the Wizard. For example, you will be asked to enter user information. 1. Installation Selection. On the Installation Selection window, select Advanced Options. 2. Advanced Options. Select Record Unattended .ISS file. Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
Chapter 2: Install SQL Server 25 3. Instance Name. Select the Default instance or specify a name for a Named instance. (If you will be installing multiple SQL instances, you can specify a different name for each instance at install time.) 4. Setup Type. Click Custom. You will need to use the custom setup so that you can configure SQL Server for use by MxDB for SQL Server. For the Destination Folder, be sure to install the SQL data files on a PSFS filesystem. Install the program files on the local node.
Chapter 2: Install SQL Server 26 5. Select Components. Choose the SQL Server components that you want to install. 6. Service Accounts. You will need to create an account that will be used for SQL Server and SQL Server Agent. We recommend that you use either the Local System account or the same Domain account for all instances so that privileges will be consistent across the matrix. To create an account, select “Customize the settings for each service.” Then select SQL Server in the Services column.
Chapter 2: Install SQL Server 27 Next, select the SQL Server Agent service and chose the appropriate account type. Do not check the “Auto Start Service” box. Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
Chapter 2: Install SQL Server 28 7. Authentication Mode. Although you can use either mode, Mixed Mode allows you to assign a password to the sa login. (The Service Pack installation requires that a password be assigned to the sa account.) 8. Collation Setting. Choose the collation settings appropriate for your site. 9. Network Libraries. Type a port number for TCP/IP Sockets.
Chapter 2: Install SQL Server 29 The recording is now complete and can be used to perform an unattended installation of SQL Server instances on the appropriate nodes. Install a SQL Server 2000 Instance on Multiple Nodes Before starting the installation, be sure that the folders containing the following files are on a PSFS filesystem that can be accessed by all nodes in the matrix: • The installation file (setup.iss) that you recorded in the previous step.
Chapter 2: Install SQL Server 30 Location of media: Specify the location of the recorded installation file. Datafiles path prefix: Specify the common path that will be used for the data files for this SQL Server instance. This location will be used to store the default SQL database provided with the product. You can install your own SQL databases at other locations on the shared storage. NOTE: For each node, the common path will be appended by the node name and instance name to make a unique path name.
Chapter 2: Install SQL Server 31 account information will be used to install SQL Server. If you want to use the local account instead of a domain account, enter (local) as the Domain name. The local account must exist on each node. Nodes: You can now select the nodes for the installation. Click the Add Node button to display the Select Nodes window, which lists the available nodes. Select the appropriate nodes from this window. The nodes you select are added to the list on the Install SQL window.
Chapter 2: Install SQL Server 32 Install a SQL Server 2000 Service Pack This procedure can be used to install SQL Server 2000 Service Pack 3 and higher. Service Pack 4 is required. NOTE: If Service Pack 3 is not already installed, an unattended installation of Service Pack 4 will fail. You will need to first install Service Pack 3 and then install Service Pack 4. This is a known Microsoft issue. NOTE: Be sure to put any virtualized SQL instances into maintenance mode before attempting to patch them.
Chapter 2: Install SQL Server 33 Select a task: Before installing the Service Pack, you will need to copy the media to a PSFS filesystem. Under step “1. Preparation,” click on “Copy media to cluster filesystem.” Location of media: Specify the location of the media for the Service Pack. If you are installing from CD, it should be located in a local drive on one of the nodes in the matrix. If you are not installing from CD, the files must be in a location that can be accessed by the current node.
Chapter 2: Install SQL Server 34 You do not need to record the installation. Service Pack 3 includes an .iss installation file that can be used for the unattended installation and, for Service Pack 4, you will have copied the Service Pack 3 .iss file earlier. Install the Service Pack The Service Pack must be installed on each SQL instance. To begin, select “Install product” under task “2. Installation” on the Install SQL window. The following example shows a completed installation.
Chapter 2: Install SQL Server 35 instance is installed. When you have completed your selections, highlight all of the nodes and then click the Install button. While the installation is in progress, the Status column shows an hourglass. A green checkmark is displayed when the installation completes successfully. If the installation fails, the Status column shows a red X. (If a failure occurs during the installation process, run the installation again.
Chapter 2: Install SQL Server 36 copy the hotfix installer file to a PSFS filesystem. Under step “1. Preparation,” click on “Copy media to cluster filesystem.” Location of hotfix installer file: Specify the location of the installer file for the hotfix. Node containing media: This is the Matrix Server node containing the media. Copy to: Specify a folder to store the files that will be copied. This folder must be on a PSFS filesystem that can be accessed by all nodes in the matrix.
Chapter 2: Install SQL Server 37 Location of hotfix installer file: Specify the location of the installer file for the hotfix. Instance: In the Name field, specify the instance that you are updating. Nodes: Click the Add Node button to display the Select Nodes window, which lists the available nodes. Select the nodes on which the SQL instance is installed. When you have completed your selections, highlight all of the nodes and then click the Install button.
Chapter 2: Install SQL Server 38 Other Installation Tasks Adjust the Maximum Memory Setting for SQL Server PolyServe recommends that you reserve approximately 15% of the system resources for the operating system and Matrix Server. For each SQL instance installed, you should change the Maximum Memory default setting for SQL Server from “all” to 85% (or less) of the total physical RAM on the system. The following script is a template to set the maximum RAM for a particular SQL instance.
Chapter 2: Install SQL Server 39 Requirement for BUILTIN\Administrators Group PolyServe uses Windows authentication (LocalSystem) to connect to SQL Server to make changes. This means that the BUILTIN\Administrators local NT group should be enabled on the primary instance and be part of the SQL Server SysAdmin role. If the BUILTIN\Administrators group must be removed from SQL Server, the following script must be run to explicitly allow LocalSystem account access to SQL Server.
Chapter 2: Install SQL Server Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
3 Configuration and Operation This chapter describes how to perform the following tasks: • Configure Virtual SQL Servers. • Assign SQL instances to Virtual SQL Servers. • Configure SQL clients. • Change the primary node for a Virtual SQL Server. • Perform operational tasks such as enabling or disabling a SQL service monitor. • Stop SQL Server and Matrix Server. • Perform database backups. • Configure SQL mail and SQL Agent mail. • Uninstall SQL Server instances.
Chapter 3: Configuration and Operation 42 MxDB for SQL Server can add the A record when it creates the Virtual SQL Server; however, you will need to add the [PTR] record manually. Create a Virtual SQL Server To create a Virtual SQL Server, start the PolyServe Management Console and then select Matrix > Add > Add Virtual SQL Server. The Add Virtual SQL Server window appears. Virtual IP: Specify the IP address that will be associated with this Virtual SQL Server.
Chapter 3: Configuration and Operation 43 Manage MSDTC: Microsoft Distributed Transaction Coordinator (MSDTC) is used to coordinate transactions between different SQL instances running on different nodes in the cluster. There is one MSDTC service on each machine. When the “Manage MSDTC” box is checked, MxDB for SQL Server virtualizes MSDTC, thus enabling transactions that span one or more Virtual SQL Servers. However, the following limitations exist when using a Virtual SQL Server with MSDTC.
Chapter 3: Configuration and Operation 44 • NOFAILBACK. This policy is intended to minimize failovers and is the default. The Virtual SQL Server remains active on the backup node until a “healthier” node becomes available, at which point the Virtual SQL Server fails over to that node. (On a “healthier” node, more of the services associated with the Virtual SQL Server will be up than on the node currently hosting the Virtual SQL Server. “Up” includes the probe statuses UP and Standby.
Chapter 3: Configuration and Operation 45 To add a Virtual SQL Server from the command line, use this command: mx vsql add [--policy autofailback|nofailback] [--application ] [--msdtc] ([ ...]) Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
Chapter 3: Configuration and Operation 46 Progress Reports When you perform operations such as creating a Virtual SQL Server, adding a virtual SQL instance, or moving a Virtual SQL Server to maintenance mode, a progress report is displayed. The report lists the tasks required to perform the operation. Click Run to start the operation. The tasks are checked off as they are performed. If a particular task fails, it is marked with an X and you are asked if you want to continue the operation.
Chapter 3: Configuration and Operation 47 • A Virtual SQL Server can include both SQL Server 2000 and SQL Server 2005 instances. To add a SQL instance to a Virtual SQL Server, select the Virtual SQL Server on the PolyServe Management Console, right-click, and select Add Virtual SQL Instance. SQL Virtual Server. This field contains the IP address of the Virtual SQL Server that you selected. Name: This field lists all of the SQL instances that are available for assignment to this Virtual SQL Server.
Chapter 3: Configuration and Operation 48 • The SQL instance must exist on all of the nodes providing network interfaces for the Virtual SQL Server. (The instance must have the same name on all of the nodes.) • The SQL instance cannot be in use on any of the nodes as a primary for another Virtual SQL Server. • The SQL instance on the primary node cannot currently be a backup for another Virtual SQL Server.
Chapter 3: Configuration and Operation 49 The frequency is the interval of time, in seconds, at which the monitor runs the probe. The default setting is 20 seconds. NOTE: The probe behavior of the SQL service monitor differs from the behavior of the service monitors provided with Matrix Server. In Matrix Server, the monitor probes the associated service on all nodes on which it is configured. The SQL monitor probes the SQL service only on the node where the Virtual SQL Server is active.
Chapter 3: Configuration and Operation 50 Timeout and Failure Severity: This setting works with the Virtual SQL Server policy (either AUTOFAILBACK or NOFAILBACK) to determine what happens when the SQL monitor probe fails. The default policies (NOFAILBACK for the Virtual SQL Server and AUTORECOVER for the monitor) cause the ClusterPulse process to fail over the Virtual SQL Server to a backup node.
Chapter 3: Configuration and Operation 51 AUTORECOVER. If the Virtual SQL Server policy is AUTOFAILBACK, when the probe fails, the ClusterPulse process automatically begins failover of the associated Virtual SQL Server to a backup node. Failback occurs in accordance with the policy setting for the Virtual SQL Server (either AUTOFAILBACK or NOFAILBACK). See “Configure Virtual SQL Servers” on page 41 for a description of these settings. NOAUTORECOVER.
Chapter 3: Configuration and Operation 52 cannot be activated on the new node, the Virtual SQL Server will fail over to another backup node if possible. Scripts The Scripts tab lets you customize a SQL monitor with Post Start, Post Stop, and Recovery scripts. You can also configure the event severity and script ordering for the monitor. Script Pathnames: The SQL monitor can optionally be configured with the following types of scripts: Recovery script.
Chapter 3: Configuration and Operation 53 server. On all other servers configured for the monitor, the Pre Stop script is run to ensure that the service is not active. Post Start scripts must be robust enough to run when the service is already started, without considering this to be an error. Similarly, Pre Stop scripts must be robust enough to run when the service is already stopped, without considering this to be an error. In both of these cases, the script should exit with a zero exit status.
Chapter 3: Configuration and Operation 54 Event Severity If a PostStart or PostStop script fails or times out, a monitor event is created on the node where the failure or timeout occurred. An event is also created when an SQL monitor probe reports a status of DOWN. (Configuration errors can also cause these events.) You can view the events on the PolyServe Management Console and clear them from the Console after you have fixed the problems that caused them.
Chapter 3: Configuration and Operation 55 PARALLEL. The strict ordering sequence for Post Stop and Post Start scripts is not enforced. The scripts run in parallel across the matrix as a virtual server is in transition. The PARALLEL configuration can speed up failover time for services that do not depend on strict ordering of Post Start and Post Stop scripts.
Chapter 3: Configuration and Operation 56 You will need to enter the following information on the Add Network Library Configuration window. Server alias: Specify the alias that you want to use for this instance. Network library Select TCP/IP. Connection parameters. Enter the Virtual SQL Server’s fully qualified name or its IP address as the server name. Then uncheck “Dynamically determine port” and specify the port number associated with the SQL instance.
Chapter 3: Configuration and Operation 57 A client can then connect to the databases by specifying the server alias on the Connect to SQL Server dialog. Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
Chapter 3: Configuration and Operation 58 Using the Applications Tab The Applications tab shows all of the Virtual SQL Servers and virtual SQL instances configured in the matrix. The Application names assigned to the Virtual SQL Servers are listed in the Name column. When you click on an application, you will see the Virtual SQL Servers associated with that application, as well as the virtual SQL instances assigned to those Virtual SQL Servers.
Chapter 3: Configuration and Operation 59 backup. The green checkmark specifies the server on which the Virtual SQL Server is currently active. You can use the Status column to locate any problems in the matrix. In the above example, the status of application 99.11.14.213 is “Warning” because the Virtual SQL Server has failed over to the first backup server. Manage MxDB for SQL Server You can use the Applications tab to manage Virtual SQL Servers and the associated virtual SQL instances.
Chapter 3: Configuration and Operation 60 Virtual SQL Instances When you right-click on a virtual SQL instance in the Name column, the following options, described under “Other Procedures” on page 62, are available: • Delete – remove the virtual SQL instance. • Properties – modify the configuration of the virtual SQL instance. • Disable/Enable – disable or enable the virtual SQL instance.
Chapter 3: Configuration and Operation 61 To change the primary for the Virtual SQL Server, reorder the network interfaces in the Members column. You can also add or remove interfaces from the Virtual SQL Server configuration. After making your changes, click OK. A progress report then appears. The report lists the tasks required to rehost the Virtual SQL Server. Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
Chapter 3: Configuration and Operation 62 Click Run to start the rehost operation. The tasks are checked off as they are performed. When all tasks are completed successfully, the PolyServe Management Console will show the new primary node for the Virtual SQL Server. If a particular task fails, it is marked with an X on the progress report and you are asked if you want to continue the operation. The progress report provides two options that can be used if a task fails: • Run.
Chapter 3: Configuration and Operation 63 maintenance on the backup instance first and then reenable the SQL service monitor for that instance. The backup instance will then be available for the other primary instance. To move a Virtual SQL Server to maintenance mode, select the Virtual SQL Server on the PolyServe Management Console, right-click, and select Maintain. NOTE: Before placing the Virtual SQL Server into maintenance mode, be sure that it is running on the primary node.
Chapter 3: Configuration and Operation 64 Virtual SQL Server Procedures Modify the Configuration To modify the configuration of a Virtual SQL Server, select it on the Management Console window, right-click, and select Properties. You can then change the configuration. The following restrictions apply: • The primary network interface cannot be changed.
Chapter 3: Configuration and Operation 65 View Status for a Virtual SQL Server The PolyServe Management Console shows the status of all Virtual SQL Servers configured in the matrix.
Chapter 3: Configuration and Operation 66 Server will remain active on the original node and the status reported for the monitor will be “UP Active (disabled).” To disable a SQL service monitor, select it on the PolyServe Management Console, right-click, and select Disable. To reenable the monitor, select it on the Management Console, right-click, and select Enable. Use the following commands to enable or disable the monitor from the command line: mx vsqlinstance enable ...
Chapter 3: Configuration and Operation 67 To delete a Virtual SQL Instance from the command line, use this command: mx vsqlinstance delete View Status for a Virtual SQL Instance The PolyServe Management Console shows the status of all Virtual SQL Instances configured in the matrix.
Chapter 3: Configuration and Operation 68 Configure SQL Mail and SQL Agent Mail The setup for SQL Mail and/or SQL Agent Mail is the same with or without MxDB for SQL Server installed. It is recommended that you use domain authentication, and that the same domain user be configured for each of the following: • SQL Server startup account • SQL Server Agent startup account • MAPI profile See the following Microsoft Knowlege Base articles for additional information: http://support.microsoft.
4 Troubleshooting This chapter describes the following issues: • Install/uninstall issues: – SQL Server 2005 installation takes too long – Installations via a Microsoft Terminal Services client – Installer reports a failure – Uninstall problems • Connection issues: – DNS misconfiguration causes Virtual SQL Server connection to fail – Connection error with Windows authentication – Windows authentication fails with SQL Server replication • “Registry replicator undefined” alert messages • Error when creating a
Chapter 4: Troubleshooting 70 See the following Microsoft Knowledge Base article for more information about the problem and its resolution. You will need to contact Microsoft to obtain the hotfix described in the article. http://support.microsoft.com/kb/910070 If you choose to implement the workaround specified in the Microsoft article, do not use Method 1, which disables TCP/IP. Matrix Server requires TCP/IP to function.
Chapter 4: Troubleshooting 71 For Service Pack 2, check the summary.txt file in the following directory: %ProgramFiles%\Microsoft SQL Server\90\Setup Bootstrap\LOG\HotFix SQL Server 2000 Core and Service Packs Check the following locations. (The core files are overwritten on every install of the core product and any service packs or hotfixes.) • %windir%\sqlsp.log • %windir%\sqlstp.log SQL Server 2000 Hotfixes See the following Microsoft article: http://support.microsoft.
Chapter 4: Troubleshooting 72 • If a SQL Server 2005 uninstall fails for any other reason, check the following article: http://support.microsoft.com/kb/909967/en-us Connection Issues DNS Misconfiguration Causes Connection Failure The following error can appear when attempting to connect a Virtual SQL Server running locally on the cluster. SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: XXX.XXX.XXX.
Chapter 4: Troubleshooting 73 2. Ping the virtual SQL Server IP address. For example: C:\>ping -a 99.11.13.181 Pinging vqar13s11.ad1.polyserve.com [99.11.13.181] with 32 bytes of data: Reply from 99.11.13.181: bytes=32 time<1ms TTL=128 Reply from 99.11.13.181: bytes=32 time<1ms TTL=128 Reply from 99.11.13.181: bytes=32 time<1ms TTL=128 Reply from 99.11.13.181: bytes=32 time<1ms TTL=128 Ping statistics for 99.11.13.
Chapter 4: Troubleshooting Address: 74 99.11.0.31 181.13.11.99.in-addr.arpa name = vqar13s11.ad1.polyserve.com > 99.11.13.181 Server: qadc1.ad1.polyserve.com Address: 99.11.0.31 181.13.11.99.in-addr.arpa vqar13s11.ad1.polyserve.com name = When a SQL client uses integrated security (SSPI) to connect to a SQL Server, the SQL driver authenticates the client via the strong network authentication, Kerberos first. If Kerberos is not available, NTLM authentication is then used to authenticate the client.
Chapter 4: Troubleshooting 75 Whether the SPN is valid depends entirely on DNS name/address resolution. If the client-formed SPN is invalid, the SSPI interface retries by looking up an SPN in Active Directory. If an SPN does not exist in AD for the SQL Server, Kerberos authentication is not used and the logon switches to an NTLM authentication. A valid SPN for SQL Server is assigned to two types of containers.
Chapter 4: Troubleshooting 76 How to: Enable Kerberos Authentication on a SQL Server Failover Cluster How to use Kerberos Authentication in SQL Server Microsoft NTLM querySpn.vbs Script ' Copyright (c) Microsoft Corporation 2004 ' File: querySpn.
Chapter 4: Troubleshooting 77 Wscript.Echo "User Logon: " & oRecordset.Fields("samAccountName") End If If DUMP_SPNs Then '--- Display the SPNs on the object --vSPNs = oRecordset.Fields("servicePrincipalName") For Each vName in vSPNs Wscript.Echo "-- " + vName Next End If Wscript.Echo oRecordset.MoveNext Wend End If oRecordset.Close oConnection.Close Sub ShowUsage() Wscript.Echo " USAGE: " & WScript.ScriptName & _ " SpnToFind [GC Servername or Forestname]" Wscript.Echo Wscript.Echo " EXAMPLES: " Wscript.
Chapter 4: Troubleshooting 78 Connection Error With Windows Authentication When connecting to a SQL Server instance using local Windows authentication, you may receive the following error: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. This error is addressed in the following Microsoft Knowledge Base article: http://support.microsoft.com/default.
Chapter 4: Troubleshooting 79 [-DistributorSecurityMode [0|1]] // set to 1 for SQL authentication [-SubscriberLogin subscriber_login] [-SubscriberPassword subscriber_password] [-SubscriberSecurityMode [0|1]] // set to 1 for SQL authentication For the merge agent job: [-DistributorLogin distributor_login] [-DistributorPassword distributor_password] [-DistributorSecurityMode [0|1]] // set to 1 for SQL authentication [-PublisherLogin publisher_login] [-PublisherNetwork publisher_network] [-PublisherPassword
Chapter 4: Troubleshooting 80 To fix the registry keys, take these steps: 1. Run the command mx vsqlinstance dump. In the output, each VSQL line contains a variable called masterdatabase. The following example is for an instance named INST1: masterdatabase=S:\\Inst1\MSSQL$Inst1\data\master.mdf 2. Browse to the following location in the registry. HKEY_LOCAL_MACHINE/SOFTWARE/PolyServe/MatrixServer/RegistryReplicat or/MetaKeys 3.
Chapter 4: Troubleshooting 81 See the following Microsoft Knowledge Base articles for more information about removing MSCS. Windows Server 2003: http://support.microsoft.com/kb/282227 Windows 2000 Advanced Server: http://support.microsoft.com/kb/257928 Error When Creating a Distributor for Replication Typically you connect to MxDB for SQL Server via ,. However, this connection method does not work when configuring a distributor for replication and results in the following error.
Chapter 4: Troubleshooting 82 Instead, we recommend that you launch Enterprise Manager either locally on the active node or within the local subnet. You can then create the distributor. When you create the distributor, a Replicator Monitor should also be created for you. Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
Chapter 4: Troubleshooting 83 Configuring publication database(s) and selecting qualified subscriber(s) is similar to configuring a stand-alone server, as shown below. Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
Chapter 4: Troubleshooting 84 Next, create a publication and specify the desired replication model. NOTE: Queued updating is supported only if the changes are queued in a SQL Server database. Queued updating via MSMQ is not supported for MxDB for SQL Server. Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
Chapter 4: Troubleshooting Next, specify the database object(s) as part of the publication. Now accept and commit the settings for the publication. Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
Chapter 4: Troubleshooting After the publication is initialized, specify the target subscriber and database for a push subscription. Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
Chapter 4: Troubleshooting Specify the desired frequency for each data distribution. Initialize the subscriber’s data. Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
Chapter 4: Troubleshooting Specify the subscription mode. Finally, commit and start the subscription. Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
Chapter 4: Troubleshooting 89 MS DTC Not Configured for Windows 2003 When running a distributed transaction against a SQL2000-linked server on a computer that is running Windows 2003, you may encounter the following error. Server: Msg 7391, Level 16, State 1, Line 2 The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator.
Chapter 4: Troubleshooting 3. Right-click My Computer > Properties. 4. Select the MS DTC tab > Security Configuration. 5. Select the Network DTC Access check box. 6. Select the Allow Remote Clients checkbox. Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
Chapter 4: Troubleshooting 7. Verify that DTC Logon Account is set to NT Authority\NetworkService. 8. Click OK to commit. 9. Close the Component Services dialog. 10. Reboot the computer for the changes to take effect. Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
5 Upgrade Production SQL Servers This chapter describes how to upgrade production SQL servers and components of the underlying environment that can affect SQL server availability. The procedures include upgrades to SQL instances and general machine upgrades such as Microsoft Windows service packs, firmware, and third-party software. The upgrade procedures minimize interruptions to SQL server availability by leveraging the clustering capabilities of Matrix Server.
Chapter 5: Upgrade Production SQL Servers 93 • When to upgrade? Query users for the best time to perform the upgrade, and consider a time window in which availability requirements are less strict. • What if something goes wrong? You will need to determine what is a valid machine state, and how to back it up and restore it in the minimum amount of time. Also ensure that all support agreements are in place and that contact information is updated.
Chapter 5: Upgrade Production SQL Servers 94 Also ensure that the component versions match. The following generic components should be identical on the test and production machines. • Number of SQL instances • .
Chapter 5: Upgrade Production SQL Servers 95 • Logins • DTS packages Before starting the upgrade, verify that you can restore the backup onto your test machine. 4. Notify Users of Upgrade Start For a planned upgrade, users should anticipate application down time and prepare for it. However, notifying users that the upgrade is starting shows that the process is under control and that the plan is being executed properly. 5.
Chapter 5: Upgrade Production SQL Servers 96 Upgrade SQL Instances Be sure to apply the procedure to your test system before upgrading a production machine. Upgrade Considerations When performing an upgrade, you should be aware of the following: • Because the upgrade is done per instance, it should be repeated for each instance. Microsoft supports a machine running SQL instances with different versions. • Rolling upgrades are not supported. You cannot access the database while the upgrade is running.
Chapter 5: Upgrade Production SQL Servers 97 2. If a node is a backup member of multiple Virtual SQL Servers, the Virtual SQL Instance on that node will be disabled. Enable the Virtual SQL Instance by selecting the monitor on the PolyServe Management Console, right-click, and then select Enable. 3. Upgrade the SQL instance on the backup node. 4. Repeat steps 2 and 3 for each backup Virtual SQL Instance. 5. Upgrade the instance on the primary node. 6. Return the Virtual SQL Server to operational mode.
Chapter 5: Upgrade Production SQL Servers 98 General Machine Upgrades This procedure applies to the following types of upgrades: • Operating system, include service packs and hotfixes • Firmware • Third-party software These upgrades may require a reboot; however, you can minimize machine downtime by perform a rolling upgrade. In this scenario, you will need to fail over the Virtual SQL Server from the primary node, and then upgrade that node while the Virtual SQL Server resides on the backup node.
Chapter 5: Upgrade Production SQL Servers 99 need to disable an instance associated with the Virtual SQL Server on the backup node currently hosting the Virtual SQL Server. The Virtual SQL Server will then fall back to the primary node. Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
A Configure SQL Server Reporting Services MxDB for SQL Server can be used to provide High Availability for SQL Server Reporting Services that are deployed in a scale-out configuration. In this configuration, the matrix nodes host the reporting service generation and scheduling. The report definitions are in the reporting service database, which is a SQL instance located on a PSFS filesystem that can be accessed by all of the nodes. Clients use a Matrix Server virtual host to access the matrix nodes.
Appendix A: Configure SQL Server Reporting Services 101 3. Create a Virtual SQL Instance for the configuration database. Select the Virtual SQL Server created in step 1, right-click, and select Add Virtual SQL Instance. In the Name field, select the SQL Server instance with the reporting database. Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
Appendix A: Configure SQL Server Reporting Services 102 4. Configure Report Server on each node: a. Start Report Server. b. Configure Report Server Virtual Directory. c. Configure Report Manager Virtual Directory. d. Configure Windows Service Identity. e. Configure Web Service Identity. f. Configure Database Connection, specifying the Virtual SQL Instance that you created in step 3. g. Configure Backup/Restore Encryption Keys. h. Initialize Report Services. Copyright © 1999-2007 PolyServe, Inc.
Appendix A: Configure SQL Server Reporting Services i. 103 Configure Email/Execution Account as desired. See the Microsoft Reporting Services documentation for more information about configuring Report Server. Configure High Availability Support All of the reporting services should now point to the same configuration database. To provide High Availability for the reporting services, take the following steps: 1.
Appendix A: Configure SQL Server Reporting Services 104 2. Create an NTSERVICE device monitor for the IIS service on each node. (Select one of the servers, right-click, and select Add Device Monitor.) The name of the IIS NT service is W3SVC. Specify”Yes” to start the dependent services and set the number of retries to one. Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
Appendix A: Configure SQL Server Reporting Services 105 3. Click the Advanced button and go to the Virtual Hosts tab. Select the virtual host that you created in step 1. 4. Go to the Server tab and select all of the nodes that are acting as reporting servers. Then click OK. 5. Create a second NTSERVICE monitor for the reporting services NT service on each node. The name of the NT service is REPORTSERVER${instancename}, where {instancename} is the actual instance where Report Server is installed.
Appendix A: Configure SQL Server Reporting Services 106 6. Click the Advanced button. Go to the Virtual Hosts tab and select the virtual host configured in step 1. Next, go to the Servers tab and select all servers that will be acting as reporting servers. Then click OK. The configuration looks like this on the PolyServe Management Console. Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
Appendix A: Configure SQL Server Reporting Services 107 7. Create a report and publish it as desired. Clients use the IP address of the virtual host to access the reporting database instead of using the server’s IP address. For example: http:///ReportServer If the primary node for the virtual host goes down, the virtual host will failover to a backup node and access will continue.
B Host MS DTC on a Remote Node Overview The Microsoft Distributed Transaction Coordinator (MS DTC) is the transaction manager responsible for coordinating transaction atomicity across multiple resource managers. It ensures that an update of data (work) is either committed or rolled back on all servers that are participating in the transaction. As shown below, the client connects to Server A and invokes a remote call to Server B, which employs MS DTC to manage the transaction across these two servers.
Appendix B: Host MS DTC on a Remote Node 109 MxDB for SQL Server utilizes PolyServe’s shared cluster filesystem, in which all nodes in the cluster can simultaneously read/write to a file on the shared disk. On failover, the backup node simply starts up SQL Server and mounts the data on the shared disk. There is relatively little cost in transition or data movement.
Appendix B: Host MS DTC on a Remote Node 110 Set Up the Remote Node for Hosting DTC By having a dedicated node hosting DTC, the Virtual SQL Server can move freely around in the matrix. Also, more than one Virtual SQL Server can be active on a single node with 100 percent support to enlist in a transaction. The following procedure can be used to set up a node to host DTC (for this example, we will use QAR4S5, which resides in a cluster outside the virtual cluster shown above). 1.
Appendix B: Host MS DTC on a Remote Node 111 4. Select the MSDTC tab. 5. Ensure that the Use local coordinator box is checked and then select Security Configuration. Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
Appendix B: Host MS DTC on a Remote Node 6. Ensure that Network DTC Access is set properly. 7. Select OK to commit the changes. Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
Appendix B: Host MS DTC on a Remote Node 113 Set Up Local Nodes to Use Remote DTC When another node is used to host DTC, every local node participating in a virtual SQL must be configured manually. In the following procedure, we will configure two distinct Virtual SQLs (vSQLTest1 and vSQLTest2). vSQLTest1 consists of two local nodes, QAR14S17 and QAR14S18 (node 1 and node 2 respectively). vSQLTest2 also consists of two local nodes, QAR14S31 and QAR14S32 (node 4 and node 5 respectively).
Appendix B: Host MS DTC on a Remote Node 114 5. Ensure that the Use local coordinator box is unchecked, specify the remote DTC hosting node, and then stop the currently running DTC. 6. Open SQL Service Manager: Start > Programs > Microsoft SQL Server. 7. Select Distributed Transaction Coordinator from the Services dropdown box. 8. Uncheck Auto-Start service when OS starts. Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
Appendix B: Host MS DTC on a Remote Node 115 9. Repeat steps 1 through 8 for QAR14s18, QAR14S31, and QAR14S32 (node 2, node 4, and node 5 respectively). NOTE: Steps 6-8 can be done with the NT Services manager by setting the Startup Type to Manual. Also, the SQL Server service must be restarted to allow the new DTC change to take affect. Create Virtual SQL Servers After all nodes participating in the virtual hosts are configured for remote DTC hosting, the next step is to create the Virtual SQL Server.
Appendix B: Host MS DTC on a Remote Node 116 4. Specify the policy for recoveries. 5. Add QAR14S17 as primary and QAR14S18 as backup 1. 6. Select OK to commit. 7. When the virtual host has started, right-click on the virtual host to add Instance 1 to it. Create vSQLTest2 This process is identical to creating vSQLTest1 except that QAR14S31 is assigned as the primary node and QAR14S32 as the backup node. 1. On the PolyServe Management Console, select Matrix > Add > Add Virtual SQL Server. 2.
Appendix B: Host MS DTC on a Remote Node 117 6. Select OK to commit. 7. When the virtual host has started, right-click on the virtual host to add Instance 1 to it. If no errors are encountered during Virtual SQL Server creation, verify that SQL Instance1 is started on each respective primary node. Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
Appendix B: Host MS DTC on a Remote Node 118 Test Remote DTC We will perform a test from each of the Virtual SQL Servers. Test from vSQLTEST1 This test is quite simple. We are going to connect to vSQLTest1 and, from there, we will create a linked server to vSQLTest2. “Linked server” is the Microsoft term for a remote heterogeneous server that is registered within the local server. Once registered, it is possible to do remote procedure calls and access remote data from the local server.
Appendix B: Host MS DTC on a Remote Node 3. Map local logins to linked server logins: EXEC sp_addlinkedsrvlogin @rmtsrvname='linkedsrv' ,@useself='false' ,@locallogin=null ,@rmtuser='sa' ,@rmtpassword='*******' 4. Enable Data Access and RPC on the linked server: exec sp_serveroption 'linkedsrv','data access','true' exec sp_serveroption 'linkedsrv','rpc','true' Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
Appendix B: Host MS DTC on a Remote Node 120 exec sp_serveroption 'linkedsrv','rpc out','true' 5. Invoke a query that requires a valid DTC (remotely hosted in this case) to succeed. A rowcnt value should be returned from the query: begin tran select * into Northwind.dbo.o1 from linkedsrv.Northwind.dbo.Orders select @@rowcount as [rowcnt] commit tran 6.
Appendix B: Host MS DTC on a Remote Node 1. Use Query Analyzer and open a new connection to vSQLTEST2. 2. Register vSQLTEST1 as a linked server: EXEC sp_addlinkedserver @server='linkedsrv' ,@srvproduct='' ,@provider='SQLOLEDB' ,@datasrc='vSQLTest1\Instance1' Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
Appendix B: Host MS DTC on a Remote Node 3. Map local logins to linked server logins: EXEC sp_addlinkedsrvlogin @rmtsrvname='linkedsrv' ,@useself='false' ,@locallogin=null ,@rmtuser='sa' ,@rmtpassword='*******' 4. Enable Data Access and RPC on the linked server: exec sp_serveroption 'linkedsrv','data access','true' exec sp_serveroption 'linkedsrv','rpc','true' Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
Appendix B: Host MS DTC on a Remote Node 123 exec sp_serveroption 'linkedsrv','rpc out','true' 5. Invoke a query that requires a valid DTC (remotely hosted in this case) to succeed. A rowcnt value should be returned from the query: begin tran select * into Northwind.dbo.o1 from linkedsrv.Northwind.dbo.Orders select @@rowcount as [rowcnt] commit tran 6.
Appendix B: Host MS DTC on a Remote Node 124 If possible, it is recommended that you implement this configuration rather than virtualizing DTC locally on the node hosting SQL. This extra saving on overhead can surely benefit the local SQL service or the local system processes overall. References The following sources provide additional information. How To Set Up a Local MS DTC to Point to a Remote MS DTC: http://support.microsoft.
Appendix B: Host MS DTC on a Remote Node How To Troubleshoot MS DTC Firewall Issues: http://support.microsoft.com/kb/306843 MS KBs on DTC: http://support.microsoft.com/search/default.aspx?qu=dtc+firewall Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
Index A overview 7 prerequisites 6 SQL Server 2000 copy product files 21 copy Service Pack 32 install a SQL instance 29 install hotfixes 35 install Service Pack 34 overview 20 record template file 23 SQL Server Install Wizard 24 SQL Server 2005 copy product files 10 install a SQL instance 14 mixed mode 15 overview 8 record template file 12 supported SQL Server versions 7 via Terminal Services client 70 active-active configuration 2 Applications tab 58 AUTOFAILBACK policy 43 availability 2 C configuration
Index 127 O recovery script 52 registry keys 5 registry replicator alerts 79 rehosting, Virtual SQL Server 60 replication error creating distributor 81 Windows authentication fails 78 replication on SQL instances 46 Reporting Services, configure with MxDB for SQL Server 100 service accounts 26 SQL service restart, configure 48 stop 67 SQL service monitor 48 custom scripts 52 custom starting/stopping actions 53 errors, clear 66 errors, view 66 event severity 54 failover 4 failover policy 49 Post Start sc
Index 128 U upgrades backups 94 firmware 98 general machine 98 operating system 98 planning 92 process 92 SQL instances considerations 96 procedures 97 testing 93 third-party software 98 V Virtual SQL Instance add to Virtual SQL Server 46 delete 66 disable 65 enable 65 manage via Applications tab 60 modify configuration 65 optional components 48 probe configuration 48 remove from server 65 replication 46 requirements for 47 Virtual SQL Server 63 backup interface 44 configure 41 delete 64 DNS requirement
