Matrix DataBase Solution Pack for SQL Server™ Installation and Administration Guide Matrix DataBase Solution Pack for SQL Server™ 3.
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 HP Technical Support HP Storage Website . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 HP NAS Services Website . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 2 Introduction MxDB for SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Active-Passive Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Plan Your SQL Configuration. . . . . . . . . . . . . . . . . . . . . . . .
Contents iv 4 Upgrade SQL Server Instances Upgrade Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Location of Service Packs and Hotfixes. . . . . . . . . . . . . . . . . . . . . Maintenance Mode Is Required for Upgrade Operations . . . . . Upgrade Considerations for SQL Server 2005 . . . . . . . . . . . . . . . SQL Server 2000 Service Pack Considerations . . . . . . . . . . . . . . . SQL Server 2000 Hotfix Considerations . . . . . . . . . . . . . . . . . . . .
Contents v 6 Instance Aliasing Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Move the Mapping File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Configure Instance Aliasing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Configure Instance Aliasing Settings . . . . . . . . . . . . . . . . . . . . . . Map Default Instances . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Contents vi 8. Back Up the Machine State . . . . . . . . . . . . . . . . . . . . . . . . . . . . Upgrade SQL Instances . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Upgrade Considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Upgrade Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Multiple Instance Upgrades . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Other Information . . . . . . . . . . . . . . . . . . . .
1 HP Technical Support Telephone numbers for worldwide technical support are listed on the following HP website: http://www.hp.com/support. From this website, select the country of origin. For example, the North American technical support number is 800-633-3600. NOTE: For continuous quality improvement, calls may be recorded or monitored.
Chapter 1: HP Technical Support 2 HP NAS Services Website The HP NAS Services site allows you to choose from convenient HP Care Pack Services packages or implement a custom support solution delivered by HP ProLiant Storage Server specialists and/or our certified service partners. For more information see us at http://www.hp.com/hps/storage/ns_nas.html. Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
2 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 2: Introduction 4 Active-Passive Architecture MxDB for SQL Server uses an Active-Passive architecture for high-availability. For example, when you virtualize a SQL server instance in a two-node cluster, you declare one node to be the active, or primary, node for the instance and the second node to be the backup. The high-availability engine provided with MxDB for SQL Server then creates the appropriate profiles for the virtualized instance.
Chapter 2: Introduction 5 Then determine how you want to configure the Virtual SQL Servers. Each Virtual SQL Server needs a primary node and one or more backup nodes. The backup nodes for each Virtual SQL Server must contain the same set of SQL instances as the primary node. Following are some sample configurations. NOTE: A primary SQL instance and its backup SQL instances must have the same name.
Chapter 2: Introduction 6 Node 1 Node 2 Primary: virtual1 instance1 Primary: virtual2 instance2 Node 3 Primary: virtual3 instance3 Node 4 Backup: virtual1, virtual2, virtual3 instance1 instance2 instance3 The following configuration also has one node providing a backup for the Virtual SQL Servers on other nodes. In this example, the SQL instances associated with the Virtual SQL Servers all have the same name, “instance1.
Chapter 2: Introduction 7 reenable it. The backup instance will then be available for the other primary instances. SQL Monitors and Failover When you add a SQL instance to a Virtual SQL Server, MxDB for SQL Server creates a service monitor for that instance. The monitor is active on the node currently hosting the Virtual SQL Server and periodically determines whether the SQL service is up. If the monitor detects that the SQL service is down, MxDB for SQL Server can attempt to restart the service.
Chapter 2: Introduction 8 Replication of Registry Keys MxDB for SQL Server replicates the registry keys for the SQL server from the local machine to a location on the PSFS filesystem containing the corresponding “master” SQL databases. The replicator watches the main SQL instance key and, if it changes, persists the key to the shared filesystem. If a Virtual SQL Server fails over to a backup node, that node reads the keys from the PSFS filesystem and applies them back before starting SQL services.
3 Install SQL Server Instances This chapter describes how to install SQL Server 2000 or 2005 instances. SQL Server Installation Overview Before starting the installation, you should be aware of the following. SQL Server and Operating System Support SQL Server 2000 Support MxDB for SQL Server can be used with SQL Server 2000 SP4 Standard and Enterprise Editions (32-bit only).
Chapter 3: Install SQL Server Instances 10 General 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. The filesystems must use the 8 KB block size. If you will be using a PSFS filesystem created under Matrix Server 3.2 or earlier, you will need to enable sparse files on the filesystem. Use the following command to do this.
Chapter 3: Install SQL Server Instances 11 Installation Considerations for SQL Server 2005 Template File The Multi-Node Installer creates an INI template file that is used in the installation. This file should be saved in a location that provides read access for all nodes. The installer encrypts passwords in the file using a PolyServe algorithm. Do not change this file.
Chapter 3: Install SQL Server Instances 12 We recommend that you install the Non-Instance-Aware components only once per server. Any future install templates should not include these components. After each installation, check the logs for information such as whether a reboot is necessary. The following Books On Line (BOL) article provides a summary of the issues associated with installing multiple instances on a single node: http://msdn2.microsoft.com/en-us/library/ms143531.
Chapter 3: Install SQL Server Instances 13 • Mount the filesystem as a mountpoint such as c:\psfs\psd15p1. In Windows Explorer, open the Properties for the folder, go to the “Mounted Volume” properties (this is not the same as the folder security), and set the permission for Administrators/users to Full Control. • 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.
Chapter 3: Install SQL Server Instances 14 Installation Log Files The installation log for SQL Server 2005 instances is located at: %PROGRAMFILES%\Microsoft SQL Server\90\Setup Bootstrap\LOG The installation log for SQL Server 2000 instances is located at: %SYSTEMROOT%\sqlstp.log Make Product Files Available to the Installer The SQL Server product files must be in a location that can be accessed by the Multi-Node Installer. The following locations can be used: • A PSFS filesystem on the SAN storage.
Chapter 3: Install SQL Server Instances 15 The menu in the left pane of the wizard lists the dialogs included in the installation. You can click on any dialog to open it. When you complete a dialog, the wizard places a checkmark in the box preceding the dialog. On the Welcome window, click Next. “Select the Microsoft product to Install” dialog Use this dialog to create a new installation “product” or select an existing installation product.
Chapter 3: Install SQL Server Instances 16 To create a new product, click Add to open the Add Product dialog and then supply the following information. Product Name: Enter a name to identify this product. The name can contain only alphanumeric characters. Product Description: Enter anything you want for the description. Type: The type is always SQL Base Product. Version: Select SQL 2005.
Chapter 3: Install SQL Server Instances 17 “Select the SQL Template File to use” dialog A template (INI) file specifies the SQL Server 2005 components to be installed. On this dialog, you can either browse for an existing INI template file or create a new INI template file. When you click Create to create a new template file, the Template Feature Wizard appears. This wizard allows you to select the SQL Server components that you want to install.
Chapter 3: Install SQL Server Instances 18 The Expand button can be used to see the components in each feature. Add or remove checkmarks to indicate the components that you want to install and then click Save to save the template as an .INI file. The file must be saved in a location that can be accessed by all nodes on which the instance will be installed. Click Next to go to the next dialog.
Chapter 3: Install SQL Server Instances 19 Click Next to go to the next dialog. “Define the Instances to Install” dialog You can define multiple SQL Server 2005 instances and specify the nodes on which each instance should be installed. Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
Chapter 3: Install SQL Server Instances 20 Instance Name: Type Default for the default instance, or specify the appropriate name for a named instance. The IP addresses for the servers in the matrix are listed in the Servers column. To install the instance on all of the servers, click “Install on all servers.” When you click “Install on specific servers,” you can select the servers for the instance.
Chapter 3: Install SQL Server Instances 21 When you click Yes, the wizard will start installing the instances. A message on the PolyServe Management Console will report that the operation is in progress. When the operation is complete, a Summary message will report the number of servers that were installed successfully. If the install fails on one or more nodes, the Summary will include a Details button.
Chapter 3: Install SQL Server Instances 22 The menu in the left pane of the wizard lists the dialogs included in the installation. You can click on any dialog to open it. When you complete a dialog, the wizard places a checkmark in the box preceding the dialog. On the Welcome window, click Next. “Select the Microsoft product to Install” dialog Use this dialog to create a new installation “product” or select an existing installation product.
Chapter 3: Install SQL Server Instances 23 To create a new product, click Add to open the Add Product dialog and then supply the following information. Product Name: Enter a name to identify this product. The name can contain only alphanumeric characters. Product Description: Enter anything you want for the description. Type: The type is always SQL Base Product. Version: Select SQL 2000.
Chapter 3: Install SQL Server Instances 24 Server Installation Wizard will be launched to gather the information needed for the file. Be sure to configure this wizard as described later in this section. Use the Browse button to locate an existing template file or click Create to create a new template file. When you click Create, you will see the following message: Click OK to start the Microsoft SQL Server Installation Wizard, which creates an ISS template file.
Chapter 3: Install SQL Server Instances Installation Selection. On the Installation Selection window, select Advanced Options. Advanced Options. Select Record Unattended .ISS file. 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.) Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
Chapter 3: Install SQL Server Instances 26 Service Accounts. You will need to create an account that will be used for SQL Server and SQL Server Agent. System accounts are required to be Domain user accounts. We recommend that you use the same Domain account for all instances so that privileges will be consistent across the matrix. To create the SQL Server account, select “Customize the settings for each service” and then select SQL Server in the Services column. Do not check the “Auto Start Service” box.
Chapter 3: Install SQL Server Instances 27 Next, select the SQL Server Agent service and chose the appropriate account type. Do not check the “Auto Start Service” box. 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.) Collation Setting. Choose the collation settings appropriate for your site. Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
Chapter 3: Install SQL Server Instances 28 Network Libraries. Type a port number for TCP/IP Sockets. You can enter any unused port number; however, the same port number must be used on the backup nodes for the associated Virtual SQL Server. Do not use port number 0, which configures dynamic port assignment. MxDB for SQL Server needs to know which port the SQL clients will use to access the SQL database. This means that SQL Server cannot use dynamic port assignment.
Chapter 3: Install SQL Server Instances 29 Click Next to go to the next dialog. “Define the Instances to Install” dialog You can define multiple SQL Server 2000 instances and specify the nodes on which each instance should be installed. Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
Chapter 3: Install SQL Server Instances 30 Instance Name: Type Default for the default instance, or specify the appropriate name for a named instance. The IP addresses for the servers in the matrix are listed in the Servers column. To install the instance on all of the servers, click “Install on all servers.” When you click “Install on specific servers,” you can select the servers for the instance. When you have completed your selections, click Add.
Chapter 3: Install SQL Server Instances 31 When you click Yes, the wizard will start installing the instances. A message on the PolyServe Management Console will report that the operation is in progress. When the operation is complete, a Summary message will report the number of servers that were installed successfully. If the install fails on one or more nodes, the Summary will include a Details button.
Chapter 3: Install SQL Server Instances 32 The following script is a template to set the maximum RAM for a particular SQL instance. If multiple instances are installed on the node, you can divide 85% by the number of instances. The exact memory allocation should be studied and defined for your particular environment—some instances may require more memory than others.
Chapter 3: Install SQL Server Instances 33 IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'NT Authority\System') EXEC sp_grantlogin N'NT Authority\System' GO -- Set default database EXEC sp_defaultdb N'NT Authority\System', N'master' GO -- Add LocalSystem to SQL SysAdmin group EXEC sp_addsrvrolemember N'NT Authority\System', N'sysadmin' GO --CHECK SYSLOGINS after adding LocalSystem Acct SELECT name FROM master.dbo.
4 Upgrade SQL Server Instances This chapter describes how to install Service Packs and Hotfixes on SQL Server instances. Upgrade Overview The Multi-Node Upgrade Wizard for SQL Server should be used for all updates of SQL instances that have been virtualized with MxDB for SQL Server. The wizard updates both the local profile and the +1 profile on the active node.
Chapter 4: Upgrade SQL Server Instances 35 Maintenance Mode Is Required for Upgrade Operations Before installing a service pack or hotfix on a virtual SQL instance, the Virtual SQL Server associated with the instance must be moved to maintenance mode.
Chapter 4: Upgrade SQL Server Instances 36 you need these shared components, update them manually after the wizard has completed. The components will then be registered on the node. NOTE: Instead of installing hotfix 2153, we recommend that you install SP2, which includes the fixes in Hotfix 2153.
Chapter 4: Upgrade SQL Server Instances 37 NOTE: Be sure to put any virtualized SQL instances into maintenance mode before attempting to patch the active node. You can patch any passive backup nodes without putting the Virtual SQL Server into maintenance mode. Service Pack Names The installation procedure expects the names of SQL Server service packs to follow the standardized naming schema defined in the following Microsoft Knowledge Base articles: http://support.microsoft.com/kb/842960 http://support.
Chapter 4: Upgrade SQL Server Instances Dlg12=DlgServerNetwork-0 Dlg13=SdStartCopy-0 Dlg14=SdFinish-0 [DlgW2kReboot-0] Result=189 [SdWelcome-0] Result=1 [DlgMachine-0] Type=1 Result=1 [DlgInstallMode-0] Type=2 Result=1 [SdLicense-0] Result=1 [DlgInstanceName-0] InstanceName=MSSQLSERVER Result=0 [DlgMaintainInstall-0] Type=12 Result=1 [DlgUpgrade-0] Result=1 [ServerConnect-0] NTAuthentication=1 SQLAuthentication=0 Result=1 svPassword=. [SdBlankPwdWarningDlg-0] AllowBlank=1 EnterPwd=. ConfirmPwd=.
Chapter 4: Upgrade SQL Server Instances 39 Name=Microsoft SQL Server Version=8.00.000 Company=Microsoft [SdFinish-0] Result=1 bOpt1=0 bOpt2=0 SQL Server 2000 Hotfix Considerations Extract Zip Files Some hotfixes are provided in zip files. Be sure to extract the contacts of the file and review the README before starting the installation. SQL Server 2000 Hotfix 899761 This patch to SQL Server 2000 SP4 cannot be installed via the MxDB for SQL Server installer.
Chapter 4: Upgrade SQL Server Instances 40 Run the Multi-Node Upgrade Wizard for SQL Server Be sure to put any virtualized SQL instances into maintenance mode before attempting to patch them, as described under “Maintenance Mode Is Required for Upgrade Operations” on page 35. Use one of the following procedures to upgrade SQL instances: • For SQL Server 2005 service packs, see “Install SQL Server 2005 Service Packs,” below.
Chapter 4: Upgrade SQL Server Instances 41 The menu in the left pane of the wizard lists the dialogs included in the installation. (The Select Template dialog is not used for SQL Server 2005 upgrades.) You can click on any dialog to open it. When you complete a dialog, the wizard places a checkmark in the box preceding the dialog. On the Welcome window, click Next. “Select the Microsoft product to Upgrade” dialog Use this dialog to create a new upgrade “product” or select an existing upgrade product.
Chapter 4: Upgrade SQL Server Instances 42 Specify the following information on the Add Product dialog. Product Name. Enter a name to identify this product. The name can contain only alphanumeric characters. Product Description. Enter anything you want to identify this upgrade product. Type. Select either Service Pack or Hotfix. Version. Select SQL Server 2005. File Name. Type the name of the service pack or hotfix to be installed or click Browse to locate it. NOTE: Be sure to specify a valid name.
Chapter 4: Upgrade SQL Server Instances Click Next to go to the next dialog. “Select the Instances to Upgrade” dialog Select the SQL Server instances that you want to upgrade. Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
Chapter 4: Upgrade SQL Server Instances 44 To install the upgrade on all of the nodes configured for a particular instance, just click the instance name. If you want to install the upgrade only on certain nodes, expand the instance in the Name/Server column and then check the appropriate nodes. Click Upgrade to begin the upgrade. You will then see a message on the PolyServe Management Console reporting that the operation is in progress.
Chapter 4: Upgrade SQL Server Instances 45 Install SQL Server 2000 Service Packs Use this procedure to install a SQL Server 2000 service pack. To start the Multi-Node Upgrade Wizard, select Tools > Upgrade SQL Server on the PolyServe Management Console. The menu in the left pane of the wizard lists the dialogs included in the installation. You can click on any dialog to open it. When you complete a dialog, the wizard places a checkmark in the box preceding the dialog. On the Welcome window, click Next.
Chapter 4: Upgrade SQL Server Instances 46 To add a new upgrade product, click Add to open the Add Product dialog. The following example shows an upgrade product for SQL Server 2000 SP3. Specify the following information on the Add Product dialog. Product Name. Enter a name to identify this product. The name can contain only alphanumeric characters. Product Description. Enter anything you want to identify this upgrade product. Type. Select either Service Pack or Hotfix.
Chapter 4: Upgrade SQL Server Instances 47 Version. Select SQL Server 2000. Media Location. Type the location of the service pack or click Browse to locate it. Click Next to go to the next dialog. “Select the SQL Template File to use” dialog When installing a SQL Server 2000 service pack, you will need to specify the location of the template file (an .iss file), which contains specifications for the installation. Enter the location of the file on the following dialog, or click Browse to locate the file.
Chapter 4: Upgrade SQL Server Instances Click Next to go to the next dialog. “Select the Instances to Upgrade” dialog Select the SQL Server instances that you want to upgrade. Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
Chapter 4: Upgrade SQL Server Instances 49 To install the upgrade on all of the nodes configured for a particular instance, just click the instance name. If you want to install the upgrade only on certain nodes, expand the instance in the Name/Server column and then check the appropriate nodes. Click Upgrade to begin the upgrade. You will then see a message on the PolyServe Management Console reporting that the operation is in progress.
Chapter 4: Upgrade SQL Server Instances 50 Install SQL Server 2000 Hotfixes Use this procedure to install a SQL Server 2000 hotfix. To start the Multi-Node Upgrade Wizard, select Tools > Upgrade SQL Server on the PolyServe Management Console. The menu in the left pane of the wizard lists the dialogs included in the installation. (The Select Template dialog is not used for SQL Server 2000 hotfixes.) You can click on any dialog to open it.
Chapter 4: Upgrade SQL Server Instances 51 To add a new upgrade product, click Add to open the Add Product dialog. The following example shows an upgrade product for a SQL Server 2000 hotfix. Specify the following information on the Add Product dialog. Product Name. Enter a name to identify this product. The name can contain only alphanumeric characters. Product Description. Enter anything you want to identify this upgrade product. Type. Select either Service Pack or Hotfix.
Chapter 4: Upgrade SQL Server Instances 52 Version. Select SQL Server 2000. File Name. Type the name of the hotfix or click Browse to locate it. Click Next to go to the next dialog. “Installation Credentials” dialog This dialog asks for the credentials of the user account that will be used to run the installation. Type the appropriate name, password, and domain. Click Next to go to the next dialog. “Select the Instances to Upgrade” dialog Select the SQL Server instances that you want to upgrade.
Chapter 4: Upgrade SQL Server Instances 53 To install the upgrade on all of the nodes configured for a particular instance, just click the instance name. If you want to install the upgrade only on certain nodes, expand the instance in the Name/Server column and then check the appropriate nodes. Click Upgrade to begin the upgrade. You will then see a message on the PolyServe Management Console reporting that the operation is in progress.
5 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 5: Configuration and Operation 55 sure to create a DNS entry for each Virtual SQL Server/IP address that you create. 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 5: Configuration and Operation 56 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. MxDB for SQL Server can virtualize MSDTC, thus enabling transactions that span one or more Virtual SQL Servers.
Chapter 5: Configuration and Operation 57 the Virtual SQL Server fails over to a backup node, the ClusterPulse process watches the health of the nodes higher in the list of servers for that Virtual SQL Server. When the health of one of these nodes is equal to or greater than the backup node where the Virtual SQL Server currently resides, the Virtual SQL Server will automatically attempt to fail back to that node. • NOFAILBACK. This policy is intended to minimize failovers and is the default.
Chapter 5: Configuration and Operation 58 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 5: Configuration and Operation 59 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 5: Configuration and Operation 60 • 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. Name: This field lists all of the SQL instances that are available for assignment to this Virtual SQL Server. Select the appropriate SQL instance.
Chapter 5: Configuration and Operation 61 • 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. Port: For SQL Server 2000, this field lists the port that you specified when you installed the SQL instance. For SQL Server 2005, enter the port number for TCP/IP Sockets that the SQL Server instance should listen on.
Chapter 5: Configuration and Operation 62 monitor runs the probe. The default setting is 20 seconds. The timeout is the maximum amount of time that will be waited for the probe to complete. The default timeout interval is 15 seconds. NOTE: The probe behavior of the SQL service monitor differs from the default 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.
Chapter 5: Configuration and Operation 63 Advanced Options for SQL Monitors You can customize the operation of the SQL service monitor by using the Advanced options provided on the Add Virtual SQL Instance window. When you click the Advanced button, you will see two tabs: Policy and Scripts. Policy The Policy tab lets you specify the failover policy and service priority that will apply to the monitor.
Chapter 5: Configuration and Operation 64 You can use the Timeout and Failure Severity attribute to change the failover/failback behavior. There are three settings: NOFAILOVER, AUTORECOVER, and NOAUTORECOVER. NOFAILOVER. When the monitor probe fails, the Virtual SQL Server does not fail over to a backup node. This option is useful when the monitored resource is not critical, but is important enough that you want to keep a record of its health.
Chapter 5: Configuration and Operation 65 activated, and the probe takes place on that node. The monitor instances on other nodes are marked as “standby” on the Management Console. If the Virtual SQL Server fails over to a backup node, the monitor instance on the original node becomes inactive and the probe is no longer run on that node. Matrix Server activates the Virtual SQL Server on the new node, which causes the monitor instance on that node to change status from “standby” to “active.
Chapter 5: Configuration and Operation 66 Script Pathnames: The SQL monitor can optionally be configured with the following types of scripts: Recovery script. Runs after a monitor probe failure is detected, in an attempt to restore the service. Post Start script. Runs after the service has become active on a server. For example, you might want a Post Start script to start a Web server. Pre Stop script. Runs after the service has become inactive on a server.
Chapter 5: Configuration and Operation 67 • Run the custom Start script (if any) If you want to reverse this order, preface the Start script with the prefix [pre] on the Scripts tab. The default order for stopping is: • Run the custom Stop script (if any) • Run the monitor’s stopping activities (if any) If you want to reverse this order, preface the Stop script with the prefix [post] on the Scripts tab.
Chapter 5: Configuration and Operation 68 SERIAL. This is the default setting. When a Virtual SQL Server moves from one node to another, The following strict ordering sequence for running Post Start and Post Stop scripts is enforced: 1. The Post Stop script is run on all nodes where the virtual server should be inactive. 2. ClusterPulse waits for all Post Stop scripts to complete. 3. The Post Start script on the node where the virtual server is becoming active. PARALLEL.
Chapter 5: Configuration and Operation 69 You can obtain the information needed for an alias on the PolyServe Management Console. To create an alias, use the SQL Server Client Network Utility (C:\winnt\system32\cliconfg.exe). Select the Alias tab and then click Add. 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.
Chapter 5: Configuration and Operation 70 The following example creates the alias mySQL for Virtual SQL Server vqar4s6. Port number 40001 is assigned to the SQL instance associated with the databases to be accessed. 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 5: Configuration and Operation 71 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 5: Configuration and Operation 72 Manage MxDB for SQL Server You can use the Applications tab to manage Virtual SQL Servers and the associated virtual SQL instances. You can use “drag and drop” to make changes, or you can right-click on the Virtual SQL Server or instance (in the Name column) and then select the appropriate menu item. “Drag and drop” operations for Virtual SQL Server and virtual SQL instances work in the same manner as Matrix Server virtual hosts and monitors.
Chapter 5: Configuration and Operation 73 However, with drag and drop, it is possible to move the primary for VSQL2 to node2 and the backup to node1. Now neither of the Virtual SQL Servers has a backup. Both node1 and node2 are primary for Instance1 and cannot also act as a backup for another instance having the same name.
Chapter 5: Configuration and Operation 74 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 77, 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 5: Configuration and Operation 75 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. NOTE: Check or uncheck the “Manage msdtc” box as necessary to allow or disallow virtualization of MSDTC. If MSDTC was previously virtualized, “Manage msdtc” must be checked to continue the virtualization on the new primary node. After making your changes, click OK.
Chapter 5: Configuration and Operation 76 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 5: Configuration and Operation 77 Other Procedures Maintenance Mode When you need to apply a service pack or perform other SQL maintenance, you can move a Virtual SQL Server to maintenance mode. This mode disconnects all clients accessing the SQL instances associated with the Virtual SQL Server. All of these SQL instances are shut down on the primary node and all backup nodes and the associated SQL service monitors are disabled.
Chapter 5: Configuration and Operation 78 Create a Notifier for the Registry Replicator To assist with monitoring MxDB for SQL Server, we recommend that you create a notifier for the alerts generated by the Registry Replicator. (Alerts for the Registry Replicator are in the event range 40510 to 40516.) See the PolyServe Matrix Server Administration Guide for information about configuring notifiers.
Chapter 5: Configuration and Operation 79 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. NOTE: If MSDTC was previously virtualized, be sure to check “Manage msdtc” on the Update Virtual SQL Server dialog to continue the virtualization on the new primary node. If this option is not checked, MSDTC will not be virtualized.
Chapter 5: Configuration and Operation 80 MxDB for SQL Server can virtualize MSDTC only when the Default Coordinator for MSDTC is set to “Use local coordinator” on the nodes configured for the Virtual SQL Server. If a Remote Host is specified as the Default Coordinator, MxDB cannot virtualize MSDTC. Enable/Disable a Virtual SQL Server A Virtual SQL Server may be left in a disabled state after it is moved via a “drag and drop” operation. Use the Enable feature to re-enable the Virtual SQL Server.
Chapter 5: Configuration and Operation 81 View Status for a Virtual SQL Server The PolyServe Management Console shows the status of all Virtual SQL Servers configured in the matrix.
Chapter 5: Configuration and Operation 82 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 5: Configuration and Operation 83 To delete a Virtual SQL Instance from the command line, use this command: mx vsqlinstance delete Revirtualize a Virtual SQL Instance Occasionally you may need to delete a Virtual SQL Instance temporarily and restore the instance to its original state. When you recreate the Virtual SQL Instance with the Add SQL Instance dialog, a popup message states that the data files for this instance already exist.
Chapter 5: Configuration and Operation 84 Stop the SQL Service To stop the SQL service on a particular node, first move all Virtual SQL Servers on the node into maintenance mode. You can then use your normal procedure to stop the SQL service. Database Backups When you back up the master database directory, be sure to include the following files. MxDB for SQL Server requires the files for failover operations. • sql.original • sql.preg • vsql.
Chapter 5: Configuration and Operation 85 Uninstall SQL Server Instances To uninstall a SQL Server instance that has been virtualized with MxDB for SQL Server, complete these steps: 1. Remove the Virtual SQL instance from each server. (This step also removes the server from the configuration of the Virtual SQL Server associated with the instance.
6 Instance Aliasing The Instance Aliasing feature can be used to map your existing legacy SQL connections to the Virtual SQL Server connections used with MxDB for SQL Server. SQL clients can then continue to use the legacy connection string to access the SQL database in its new location. Overview The mappings from legacy SQL connections to the new Virtual SQL Server connections are created via the PolyServe Management Console and then stored in the Instance Aliasing mapping file, ia.conf.
Chapter 6: Instance Aliasing 87 Move the Mapping File The Instance Aliasing mapping file, ia.conf, is installed with MxDB for SQL Server. By default, the file is placed in the following location on the drive where you installed MxDB for SQL Server: %PROGRAMFILES%\Polyserve\matrixserver\conf\ia.conf Before configuring Instance Aliasing, you will need to move this file to a PSFS filesystem that can be accessed by all nodes in the matrix.
Chapter 6: Instance Aliasing 88 Configure Instance Aliasing Settings The Instance Aliasing feature needs some configuration information to operate properly. Click the Advanced button on the Instance Aliasing window to open the Advanced Settings dialog. Mapping file location. Specify the location of the ia.conf file. The file should be located on a PSFS filesystem that can be accessed by all nodes in the matrix. All nodes should use the same drive letter or mount point for the filesystem.
Chapter 6: Instance Aliasing 89 After completing your entries, click OK to return to the Instance Aliasing window. Your settings will not take effect until you click OK on the Instance Aliasing window. Map Default Instances If your legacy SQL connections are for default instances, use the Default instance remapping table on the Instance Aliasing window. To create a mapping to the Virtual SQL Server providing access to the new default virtual SQL instance, click New. The Add Default Alias window appears.
Chapter 6: Instance Aliasing 90 Map Named Instances If your legacy SQL connections are for named SQL instances, use the Named instance remapping table on the Instance Aliasing window. To create a mapping to the Virtual SQL Server providing access to the new named virtual SQL instance, click New. The Add Named Alias window appears. From legacy IP. Enter the IP address to which clients connected when using the legacy named instance. Port. Enter the port number used by the legacy named instance.
Chapter 6: Instance Aliasing 91 Sql Browser connection string. Specify the names of the legacy server and the legacy instance. Then select the SQL Server version that applies to the legacy instance. Click OK to return to the Instance Aliasing window. Your mapping entry will be added to the ia.conf file when you click OK on the Instance Aliasing window.
7 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 – Client receives error when connecting via an alias – Cannot authentica
Chapter 7: Troubleshooting 93 Install/Uninstall Issues SQL Server 2005 Installation Takes Too Long Under certain circumstances, the SQL Server 2005 Setup program can take a long time to complete. This is a known Microsoft issue. Microsoft has developed a hotfix that corrects this problem. 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.
Chapter 7: Troubleshooting 94 This Microsoft document describes how to read the files: http://msdn2.microsoft.com/en-us/library/ms144287.aspx SQL Server 2005 Service Packs and Hotfixes For Service Pack 1, check the hotfix.log file in the %windir%\HotFix directory. 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.
Chapter 7: Troubleshooting 95 • If you receive an error when uninstalling multiple named instances of SQL Server 2005, see the following article: http://support.microsoft.com/kb/915854/en-us • If you encounter an error when uninstalling SQL Server 2005 SP1, see the following article: http://support.microsoft.com/kb/919945/en-us • If a SQL Server 2005 uninstall fails for any other reason, check the following article: http://support.microsoft.
Chapter 7: Troubleshooting 96 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.181: Packets: Sent = 4, Received = 4, Lost = 0 (0% loss), Approximate round trip times in milli-seconds: Minimum = 0ms, Maximum = 0ms, Average = 0ms NOTE: If the ping fails, make sure an A record is created in DNS for the hostname. 2. Ping the virtual SQL Server IP address. For example: C:\>ping -a 99.11.13.181 Pinging vqar13s11.ad1.polyserve.
Chapter 7: Troubleshooting 97 NOTE: If the nslookup fails, make sure a PTR record is created in DNS for the virtual IP. Nslookup may require you to “set type=PTR” and lookup the in-addr reverse form of the IP. For example: C:\>nslookup Default Server: qadc1.ad1.polyserve.com Address: 99.11.0.31 > set type=PTR > 181.13.11.99.in-addr.arpa Server: qadc1.ad1.polyserve.com Address: 99.11.0.31 181.13.11.99.in-addr.arpa name = vqar13s11.ad1.polyserve.com > 99.11.13.181 Server: qadc1.ad1.polyserve.
Chapter 7: Troubleshooting 98 • Host: the fully qualified domain name for the computer running the SQL Server service. • Port: the TCP port that the SQL Server service is listening on. An example of a valid SPN for SQL Server is: MSSQLSvc/vqar13s11.ad1.polyserve.com:50004 When connecting to SQL Server via Kerberos, the client SQL driver uses the Winsock API (gethostbyname and gethostbyaddr) to resolve the SQL Server fully qualified name to form an SPN for the target SQL Server.
Chapter 7: Troubleshooting 99 To delete an existing SPN, run setspn with -D. For example: C:\>setspn -D MSSQLSvc/vqar13s11.ad1.polyserve.com:50004 vqar13s11 Unregistering ServicePrincipalNames for CN=Administrator,CN=Users,DC=pdxad1,DC=polyserve,DC=com MSSQLSvc/vqar13s11.pdxad1.polyserve.com:50004 Updated object References The following Microsoft documents provide additional information: Windows 2000 Resource Kit Tool: Setspn.
Chapter 7: Troubleshooting 100 If oRecordSet.EOF and oRecordSet.Bof Then Wscript.Echo "No SPNs found!" Else While Not oRecordset.Eof Wscript.Echo oRecordset.Fields("distinguishedName") vObjClass = oRecordset.Fields("objectClass") strClass = vObjClass( UBound(vObjClass) ) Wscript.Echo "Class: " & strClass If UCase(strClass) = "COMPUTER" Then Wscript.Echo "Computer DNS: " & oRecordset.Fields("dnsHostName") Else Wscript.Echo "User Logon: " & oRecordset.
Chapter 7: Troubleshooting 101 strSPN = WScript.Arguments(0) If WScript.Arguments.Count = 2 Then strGCPath = "GC://" & WScript.Arguments(1) Else '--- Get GC -Set oNSP = GetObject("GC:") For Each oGC in oNSP strGCPath = oGC.ADsPath Next End If End If End Sub 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.
Chapter 7: Troubleshooting 102 -PublisherDB publisher_database [-DistributorLogin distributor_login] [-DistributorPassword distributor_password] [-DistributorSecurityMode [0|1]] // set to 1 for SQL authentication [-PublisherSecurityMode [0|1]] // set to 1 for SQL authentication [-PublisherLogin publisher_login] [-PublisherPassword publisher_password] For the distributor job: [-DistributorLogin distributor_login] [-DistributorPassword distributor_password] [-DistributorSecurityMode [0|1]] // set to 1 for
Chapter 7: Troubleshooting 103 Client Receives Error When Connecting via an Alias A client computer receives the following error message when it connects to a computer running Microsoft Windows Server 2003 by using an alias name (for example, a virtual hostname). System error 52 has occurred. A duplicate name exists on the network. To resolve this problem, complete the steps for Windows Server 2003 in the following Microsoft KB article: http://support.microsoft.
Chapter 7: Troubleshooting 104 NOTE: When Matrix Server is installed, it can create the appropriate registry key automatically if desired. Error When Creating a Virtual SQL Server Because the installation of MSCS (Microsoft Cluster Services) modifies key system files, you must completely remove MSCS before creating a Virtual SQL Server.
Chapter 7: Troubleshooting 105 Cannot Access a Virtual SQL Instance from the Active Node To successfully access a Virtual SQL instance on the active node, the following requirements must be met: • An alias must exist to direct the Virtual SQL instance. MxDB for SQL Server creates this instance. To verify that the alias exists for a SQL Server 2005 instance, use Configuration Manager > Alias. For a SQL Server 2000 instance, Use the Client Network Utility and open the Alias tab.
Chapter 7: Troubleshooting 106 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. This situation is by design. Internally, Microsoft uses @@servername (i.e., vsql\instance) for verification, while Enterprise Manager uses the registered server name (i.e., vsql,port#).
Chapter 7: Troubleshooting When you create the distributor, a Replicator Monitor should also be created for you. Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
Chapter 7: Troubleshooting 108 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 7: Troubleshooting 109 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 7: 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 7: 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 7: Troubleshooting Specify the desired frequency for each data distribution. Initialize the subscriber’s data. Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
Chapter 7: Troubleshooting Specify the subscription mode. Finally, commit and start the subscription. Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
Chapter 7: Troubleshooting 114 MSDTC 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 7: Troubleshooting 3. Right-click My Computer > Properties. 4. Select the MSDTC 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 7: 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.
8 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 8: Upgrade Production SQL Servers 118 • 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 8: Upgrade Production SQL Servers 119 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 8: Upgrade Production SQL Servers 120 • 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 8: Upgrade Production SQL Servers 121 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 8: Upgrade Production SQL Servers 122 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 8: Upgrade Production SQL Servers 123 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 8: Upgrade Production SQL Servers 124 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 126 To create the Virtual SQL Server, select Matrix > Add > Add Virtual SQL Server. (For information about filling out the Add Virtual SQL Server window, see “Configure Virtual SQL Servers” on page 54.) Be sure to configure the Virtual SQL Server on all nodes that will be providing reporting services. 3. Create a Virtual SQL Instance for the configuration database.
Appendix A: Configure SQL Server Reporting Services 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. Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
Appendix A: Configure SQL Server Reporting Services g. Configure Backup/Restore Encryption Keys. h. Initialize Report Services. Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
Appendix A: Configure SQL Server Reporting Services 129 i. 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 130 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 131 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 132 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 133 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 Hosting Configurations for MS DTC 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: Hosting Configurations for MS DTC 135 MxDB for SQL Server utilizes the PSFS 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: Hosting Configurations for MS DTC 136 To enable DTC on the local node, it must be configured to accept incoming transactions and perform outgoing transactions on the network. The following instructions are a recommended method of configuration, although your application needs may differ. 1. Open Component Services via Start > Programs > Administrative Tools. 2. Expand the Component Services folder and browse to the My Computer folder. 3.
Appendix B: Hosting Configurations for MS DTC 137 4. Select the MSDTC tab. 5. Ensure that the Use local coordinator box is checked and then click the Security Configuration button near the bottom of the dialog. Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
Appendix B: Hosting Configurations for MS DTC 6. On the Security Configuration tab, the following Security Settings should be checked: • Network DTC Access • Allow Remote Clients • Allow Remote Administration • Allow Inbound • Allow Outbound • Enable XA Transactions Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
Appendix B: Hosting Configurations for MS DTC 139 7. Select OK to commit the changes. DTC management can be enabled either when creating a Virtual SQL Server or by changing the properties for the Virtual SQL Server. Check the “Manage msdtc” box to enable DTC management. Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
Appendix B: Hosting Configurations for MS DTC 140 Set Up a Remote Node for Hosting DTC DTC can be configured to use a remote host, which can be either a dedicated node in the Matrix Server cluster or a stand-alone server outside of the cluster. This configuration offloads the DTC hosting and allows the Virtual SQL Servers to fail over while maintaining their DTC availability. The following procedure can be used to set up a remote node to host DTC.
Appendix B: Hosting Configurations for MS DTC 141 4. Select the MSDTC tab. 5. Ensure that the Use local coordinator box is checked and then click the Security Configuration button near the bottom of the dialog. Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
Appendix B: Hosting Configurations for MS DTC 142 6. On the Security Configuration tab, the following Security Settings should be checked: • Network DTC Access • Allow Remote Clients • Allow Remote Administration • Allow Inbound • Allow Outbound • Enable XA Transactions 7. Select OK to commit the changes. Set Up Local Nodes to Use Remote DTC When another node is used to host DTC, every local node participating in a Virtual SQL Server must be configured manually.
Appendix B: Hosting Configurations for MS DTC 143 3. Right click on the My Computer folder to bring up its properties dialog. 4. Select the MSDTC tab. 5. Ensure that the Use local coordinator box is unchecked and, at the Remote Host prompt, specify the remote node that is hosting DTC. Then click the Stop button to stop the currently running DTC. Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
Appendix B: Hosting Configurations for MS DTC 144 6. Open SQL Service Manager (Start > Programs > Microsoft SQL Server) and then restart the SQL Server service. This step is necessary to allow the new DTC change to take effect. Summary DTC is quite important in a distributed environment. It is the middle man ensuring that all parties that participated in a transaction agree with the resulting outcome. The transaction is guaranteed to be either 100% committed or rolled back.
Appendix B: Hosting Configurations for MS DTC 145 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.com/kb/260882 How to enable network DTC access in Windows Server 2003: http://support.microsoft.com/kb/817064 How to configure Microsoft Distributed Transaction Coordinator on a Windows Server 2003 cluster: http://support.microsoft.com/kb/301600 Distributed Transactions: http://msdn.microsoft.
Appendix B: Hosting Configurations for MS DTC 146 INFO: Configuring Microsoft Distributed Transaction Coordinator (DTC) to Work Through a Firewall: http://support.microsoft.com/kb/250367 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 G active-active configuration 5 Active-Passive architecture 4 Applications tab 71 AUTOFAILBACK policy 56 availability 4 getting help 1 C configuration DNS, verify 95 Instance Aliasing 87 SQL authentication 101 SQL clients 68 SQL Server 9 virtual SQL instance 59 Virtual SQL Server 54 D Database backups 84 default instance, map legacy instance 89 DNS requirement for Virtual SQL Servers 54 verify configuration 95 E event severity, SQL service monitor 67 F failover 7 failover policy, SQL service
Index 148 L legacy SQL instances, map to virtual instances 86 Post Start script 66 Post Stop script 66 progress report 59 M R maintenance mode 77 many-to-one configuration 5 maximum memory for SQL Server 2000 31 Microsoft SQL Server Installation Wizard 24 MS DTC configure for Windows 2003 114 hosting configurations 134 support for 56 MSCS, remove 104 Multi-Node Install Wizard installation product 15 location of product files 14 SQL 2005 instances 14 Multi-Node Upgrade Wizard SQL Server 2000 hotfix con
Index 149 SQL Server 2005 40 install instances 14 install service packs 40 installation considerations 11 installation log file 14 SQL service restart, configure 61 stop 84 SQL service monitor 61 custom scripts 65 custom starting/stopping actions 66 errors, clear 82 errors, view 82 event severity 67 failover 7 failover policy 63 Post Start script 66 Post Stop script 66 probe configuration 61 probe type 64 recovery script 66 script order 67 service priority 64 timeout and failure severity 63 T TCP/IP port
Index 150 rehost 74 remove server 81 view on Management Console 62 W Windows authentication error 101 Copyright © 1999-2007 PolyServe, Inc. All rights reserved.
