HP StorageWorks HP PolyServe Software for Microsoft SQL Server 4.
Legal and notice information © Copyright 2004, 2010 Hewlett-Packard Development Company, L.P. Confidential computer software. Valid license from HP required for possession, use or copying. Consistent with FAR 12.211 and 12.212, Commercial Computer Software, Computer Software Documentation, and Technical Data for Commercial Items are licensed to the U.S. Government under vendor's standard commercial license. The information contained herein is subject to change without notice.
Contents 1 Introduction ...................................................................... 9 Virtual SQL Servers .............................................................................................. 9 Active-Passive architecture .............................................................................. 9 Plan Your SQL configuration ......................................................................... 10 Active-Active ......................................................................
SQL Server 2005 SP2 required for Full Text Search ................................... “Instance-Aware” and “Non-Instance-Aware” components ......................... SQL instance cannot be virtualized if the directories of the SQL Instance are not in the same path ............................................................................. Set permissions on existing PSFS filesystems ............................................. Installation considerations for SSAS 2005 instances ...........................
SQL Server 2005 hotfix 2153 ................................................................ Service pack and hotfix names ............................................................... Use of a hidden administrative share ...................................................... Installation log files ..................................................................................... Run the Multi-Node Update Wizard for SQL Server ................................................
Stop the SQL Service ......................................................................................... Database backups ............................................................................................. Configure SQL Mail and SQL Agent Mail ............................................................. Uninstall SQL Server instances ............................................................................ 96 96 97 97 6 Configure SQL Server Analysis Services ...........................
Select tasks from a menu ............................................................................ 119 Virtual SQL Servers and Virtual SQL instances ........................................ 119 Virtual SSAS and Virtual SSAS instances ................................................ 119 8 Instance Aliasing ........................................................... 121 Overview .......................................................................................................
1. Plan the upgrade .................................................................................. 2. Test the upgrade ................................................................................... 3. Back up the current machine state ........................................................... 4. Notify users of upgrade start .................................................................. 5. Apply the upgrade ................................................................................ 6.
1 Introduction HP PolyServe Software for Microsoft SQL Server (HP PolyServe Software) provides high availability for SQL Server databases located on PSFS cluster filesystems. High availability support is also provided for MS DTC and Microsoft SQL Server 2005/2008 Analysis Services. Virtual SQL Servers HP PolyServe Software uses Virtual SQL Servers to provide failover support and high availability.
A profile is a container consisting of the SQL data (that is, databases and logs) and the corresponding registry setting necessary for starting an instance of SQL Server. The Active-Passive architecture uses an “N+1” model in which there is a local profile (an “N” profile”) for each node participating in the Virtual SQL Server plus a cluster profile (the “+” profile) that runs on the active node for the Virtual SQL Server.
Active-Active In this configuration, the nodes in the matrix back up each others’ Virtual SQL Servers. In the following example, the Virtual SQL Server “virtual1” is primary on node1 and has a backup on node2. The SQL instance “instance1” is associated with this virtual server and exists on both nodes. Clients can access the virtual server using virtual1\instance1. Similarly, Virtual SQL Server “virtual2” is primary on node 2 and has a backup on node1.
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.” A similar situation occurs when a Virtual SQL Server is moved to maintenance mode (for example, to apply a service pack).
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). HP PolyServe Software 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. • Updates registry keys and then starts SQL Server and SQL Agent on the backup node.
Introduction
2 Install SQL Server instances This chapter describes how to install SQL Server 2005 and 2008 instances. You can also install SSAS 2005 and 2008 instances. SQL Server installation overview Before starting the installation, you should be aware of the following. SQL Server and operating system support HP PolyServe Software can be used with SQL Server 2005/2008 Standard and Enterprise Editions (32-bit and 64-bit).
http://support.microsoft.com/kb/934164 • SQL Server and the SQL Agent can be configured to use either the Local System account or a Domain account. However, certain SQL Server features are not available if you use the Local System account. Consult the Microsoft documentation for more information about these accounts and the features they support. • Multiple instances per node are supported. • A primary SQL instance and its backup SQL instances must have the same name.
Using Mixed Mode authentication SQL Server 2008 requires that a strong SA password be specified at installation time. The criteria for a strong password includes the following requirements: • The password must be more than 8 characters in length. • The password must contain both uppercase and lowercase letters. • The password must contain numbers or non-alphanumeric characters such as #, %, or ^. For more information, see the following Microsoft article: http://msdn.microsoft.
• Analysis Services. A separate service that does online data analysis for data mining and reporting. • Reporting Services. The server and client components required to do reporting and data presentation development. • Server Agent. The service that handles the management and maintenance jobs for the instance. We recommend that you install the Non-Instance-Aware components only once per server. Any future install templates should not include these components.
File streaming implications Although the SQL Filestream can be enabled during an installation of SQL 2008, the actual BLOB cannot be put on an HP PolyServe clustered filesystem (PSFS). Account provisioning In SQL Server 2005, the Windows Builtin\Administrator local group was automatically given administrative permissions for the SQL instance. This is no longer the case with SQL Server 2008.
• 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 NOTE: If you are installing a retail version of SQL Server, be sure to enter the product key provided with your software. The product key is required for all retail versions of SQL Server.
• Windows Powershell: http://www.microsoft.com/windowsserver2003/technologies/management/ powershell/default.mspx Installation considerations for SSAS 2008 instances SSAS 2008 instances are installed through the SQL Server 2008 instance installation procedure (see “Install SQL Server 2008 instances” on page 24). When you create a template for the installation, select Analysis Services on the Template Features Wizard screen. The SSAS instance will then be installed.
installed and managed by reference to the name of the instance (or simply the server name if the Default instance is used). The Non-Instance-Aware components are installed once per server and shared by all instances active on that server. The Instance-Aware components are: • Database Engine. The “Default” or named instance itself. • Full Text Search Engine. A child instance-aware service installed only if the Database Engine is installed on the node. • Analysis Services.
SQL instance cannot be virtualized if the directories of the SQL Instance are not in the same path SQL Instance directories such as Data, Log, Backup, and so on should be in the same path. For example, if you are installing an instance TEST, the path for the above directories should be: C:\Program Files\Microsoft SQL Server\MSSQL10.TEST\MSSQL\Log C:\Program Files\Microsoft SQL Server\MSSQL10.TEST\MSSQL\Backup C:\Program Files\Microsoft SQL Server\MSSQL10.
NOTE: If you are installing a retail version of SQL Server, be sure to enter the product key provided with your software. The product key is required for all retail versions of SQL Server. If you do not specify a product key, the installer will add a sample product key (“PID=123456789ABCD”) to the template.ini file. However, MSDN versions of SQL Server already have the product keys embedded in the installation files.
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. An installation “product” provides instructions for installing a particular version of SQL Server.
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 2008.
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. Several features are selected by default, as shown by the checkmarks in the “Use” column. HP PolyServe Software for Microsoft SQL Server 4.
NOTE: To install a SSAS instance, select Analysis Services. If you are installing only a SSAS instance, be sure that Database Services is not selected. NOTE: The SQL Browser component cannot be edited. 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.
“Define the Instances to Install” dialog You can define multiple SQL Server 2008 instances and specify the nodes on which each instance should be installed. Instance: Select either Default Instance or Named Instance. Instance Name: If you are installing a default instance, the default instance name MSSQLSERVER will be filled in. If you are installing a named instance, enter the appropriate name for the instance.
Select the Accounts for SQL Instance Administration In SQL Server 2005, the Windows Builtin\Administrator local group was automatically given administrative permissions for the SQL instance. This is no longer the case with SQL Server 2008. Instead, when you install an instance, you will need to specify the users and/or groups that should have administrative permissions for the instance.
The User\Group section of the dialog lists the accounts that are specified in the domain controller and belong to the local Administrators group on the nodes selected for the SQL Server installation. Select one or more of those accounts to add them to the Account Name field. (Hold down the Ctrl to select multiple accounts.) When you click Add, the accounts are added to the Accounts dialog. Click Next to continue.
Define File Streaming. The Microsoft FILESTREAM feature is not supported for PSFS filesystems; however you can enable it at your own risk for data stored on a PSFS share. To enable the feature, click Enable FILESTREAM for File I/O Streaming access and then enter a Windows share name such as a CIFS share. SQL Server Architecture. If the server has 64-bit hardware but you want to install the 32-bit version of SQL Server 2008, click x86. The FILESTREAM feature cannot be used with the “x86” option.
After entering your credentials, the wizard will display a list of conditions that must be met for the installation. If these prerequistes are in place on the node, click OK. You will then be asked whether you want to proceed with the installation. When you click Yes, the wizard will start installing the instances. HP PolyServe Software for Microsoft SQL Server 4.
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.
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. An installation “product” provides instructions for installing a particular version of SQL Server.
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.
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. Several features are selected by default, as shown by the checkmarks in the “Use” column. HP PolyServe Software for Microsoft SQL Server 4.
NOTE: To install a SSAS instance, select Analysis Services. If you are installing only a SSAS instance, be sure that Database Services is not selected. Click Expand 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.
Instance Name: Enter 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. If you click Install on specific servers, you can select the servers for the instance. When you have completed your selections, click Add and the instances to be created will appear in the table at the bottom of the screen.
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.
Other installation tasks Adjust the Maximum Memory setting for SQL Server HP 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.
--Set the reserved % set @percent=15 create table #tmp([Index] int, [Name] sysname, [Internal_Value] int null, [Character_Value] sysname null) insert #tmp exec master..xp_msver select @mem=(1-(@percent/100.))*Internal_Value from #tmp where [Name]='PhysicalMemory' drop table #tmp exec sp_configure 'max server memory',@mem reconfigure with override GO Requirement for BUILTIN\Administrators group HP PolyServe Software uses Windows authentication (LocalSystem) to connect to SQL Server to make changes.
are installing SQL Service on a backup node, verify that no other applications are currently using that port. To see the port assignment for a SQL Server, select Programs > Microsoft SQL Server > Server Network Utility. Then, on the SQL Server Network Utility window, click TCP/IP. The port assignment will then be displayed. You can change the port assignment if necessary. HP PolyServe Software for Microsoft SQL Server 4.
Install SQL Server instances
3 Upgrade SQL Server/SSAS 2005 instances in place This chapter describes how to upgrade SQL Server/SSAS 2005 instances to SQL Server/SSAS 2008 instances. The installation is called “in place” because the target SQL Server/SSAS 2005 instance is replaced with a SQL Server/SSAS 2008 instance. The old SQL Server/SSAS 2005 data files are automatically converted to the new format. All shared components and instance-specific components are also upgraded.
Prerequisites SQL Server 2008 prerequisites SQL Server 2008 requires that the following be installed: • Windows Installer 4.5 • • • • NET Framework 3.5 SP1 SQL Server Native Client SQL Server Setup Support Files Powershell 1.0 In-place upgrade prerequisites Before starting the upgrade, complete these steps: • Back up all databases that will be upgraded. • Prepare databases for the upgrade. Use Microsoft Upgrade Advisor or a similar tool.
• Upgrading in place is not recommended for certain SQL Server components such as some DTS packages. Also, Notification Services cannot be upgraded in place. • When the in-place upgrade process is running, avoid making any changes to the legacy SQL Server 2005 system. • All instance-specific components are upgraded; Microsoft does not support having different versions of instance-specific components.
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. Click Next on the Welcome Screen to go to the next window. NOTE: You can change your input on the windows at any time. Click Back to return to the appropriate window and then reenter your information.
The following example shows a completed dialog. To create a new product, click Add to open the Add Product dialog. Supply the following information on the dialog: Product Name: Enter a unique 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 IPU. HP PolyServe Software for Microsoft SQL Server 4.
Version: The version is always SQL 2008. Media Location: Enter the location of the product files (either a PSFS filesystem or a CIFS share referenced by UNC path) or click Browse to locate the files. The Wizard will take care of the correct platform type (upgrading an x86 SQL Server 2K5 instance to an x86 SQL 2K8 instance, and upgrading an x64 SQL Server 2K5 instance to an x64 SQL 2K8 instance). For information about editing or deleting a product, see “Add, edit, or delete products” on page 58.
“Select the Instances to Upgrade” dialog Use this dialog to select the instances that you want to upgrade. The dialog lists all SQL Server/SSAS 2005 instances, both virtualized and unvirtualized. Before selecting a virtualized instance, be sure that the Virtual SQL Server/Virtual SSAS is in maintenance mode. The upgrade cannot proceed if the Virtual SQL Server/Virtual SSAS is still in operate mode. When you select an instance, all the nodes on which the instance has been installed will be selected.
When you have completed your selections, click Next. If a Virtual SQL Server/Virtual SSAS for a selected instance has not been placed in maintenance mode, you will see the following error. Click Details to see a list of instances that are not in maintenance mode. Note these instances and then exit from the wizard, place the instances into maintenance mode, and restart the wizard. “Select Template File” dialog A template file records inputs for attributes of certain features being upgraded.
When you click Create to create a new template file, the Template Feature Wizard appears. This wizard allows you to select the features for which you will be supplying attributes. Click Expand to see the components. (The following example has been expanded.) Several features are selected by default, as shown by the checkmarks in the “Use” column. NOTE: The default values conform to the standard parameter values suggested by Microsoft and can be used for the in-place upgrade.
Add or remove checkmarks to indicate the components for which you will be providing attributes and enter the necessary values and IDs. When you have completed your entries, click Save to save the template as an .INI file. The template must be saved to a PSFS filesystem or a common share. This file will be used on all of the nodes during the in-place upgrade. NOTE: After the template file has been created, it can be used for subsequent upgrades. If necessary, the file can be modified.
You will then see the following alert. Click Yes or No as appropriate. Note that the upgrade procedure cannot be reversed. The wizard reports the status of the upgrade, including all nodes participating in the upgrade, the number of remaining operations, and any errors encountered. To run the upgrade in the background, click Background. HP PolyServe Software for Microsoft SQL Server 4.
When the upgrade is complete, a summary message will be displayed. If the upgrade fails on any node, the summary will report that the upgrade failed. The description will list the number of nodes on which the upgrade failed. You will need to correct the issues on those nodes and then restart the upgrade wizard.
Move Virtual SQL Servers/Virtual SSAS to operate mode When the upgrade is complete, you can return the Virtual SQL Servers/Virtual SSAS to operate mode. Select each virtual server on the PolyServe Management Console, right-click, and select Operate. If an error occurred on a Virtual SQL Server/Virtual SSAS during the in-place upgrade, the following message will be displayed. A similar error appears for each backup node experiencing a failure.
Add, edit, or delete products An installation product provides instructions for installing SQL Server/SSAS instances. These products can be created, edited, or deleted on the “Select the Microsoft product to upgrade” screen. To create a new product, click Add to open the Add Product dialog.
Supply the following information on the dialog: Product Name: Enter a unique 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 IPU. Version: The version is always SQL 2008. Media Location: Enter the location of the product files (either a PSFS filesystem or a CIFS share referenced by UNC path) or click Browse to locate the files.
To delete a product, click Remove on the “Select the Microsoft product to upgrade” screen. A deleted product cannot be recovered. If you want to use the product later on, you will need to recreate it.
4 Update SQL Server instances This chapter describes how to install Service Packs and Hotfixes on SQL Server instances. In general, you can install any updates as needed. However, HP prequalifies Service Packs to ensure compatibility with HP PolyServe Software. Check the compatibility matrix on www.hp.com for a list of prequalified Service Packs. http://h18006.www1.hp.com/products/storage/software/polyserve/support/ compatibility.
Maintenance mode is required for update operations Before installing a service pack or hotfix on a virtual SQL or SSAS instance, the Virtual SQL Server (or Virtual SSAS) associated with the instance must be moved to maintenance mode. NOTE: Be sure to put any virtualized SQL or SSAS instances into maintenance mode before attempting to patch the active node. You can patch any passive backup nodes without putting the Virtual SQL or SSAS Server into maintenance mode.
to place the tempfolder 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.
NOTE: Instead of installing hotfix 2153, we recommend that you install SP2, which includes the fixes in Hotfix 2153. 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.
NOTE: The SQL installation logs are also copied to the following directory: %SystemDrive%\Program Files\Polyserve\MatrixServer\conf\ debug\SQLLogs The directory can grow large over time, particularly if the instances are patched regularly. To limit the size of the directory, it should be emptied periodically on all nodes.
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/2008 updates.) 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 Update” dialog Use this dialog to create a new update “product” or select an existing update product.
To add a new update product, click Add to open the Add Product dialog. Following is an example for a SQL Server 2008 update. The next example is for a SQL Server 2005 update. HP PolyServe Software for Microsoft SQL Server 4.
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 update product. Type. Select either Service Pack or Hotfix. Version. Select SQL Server 2005 or 2008. 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.
Click Next to go to the next dialog. “Select the Instances to Update” dialog Select the SQL Server instances that you want to update. NOTE: The dialog lists all of the SQL Server instances on the node, regardless of the SQL Server version. Be sure that the instances you select match the SQL Server product version you specified earlier. HP PolyServe Software for Microsoft SQL Server 4.
To install the update on all of the nodes configured for a particular instance, just click the instance name. If you want to install the update only on certain nodes, expand the instance in the Name/Server column and then check the appropriate nodes. Enter any additional parameters specific to the service pack or hotfix you are installing in the Additional Parameters field. The help for the service pack or hotfix lists the available parameters.
%Program Files%\Microsoft SQL Server\90\Setup Bootstrap\LOG\ Hotfix The SQL 2008 log is at: %Program Files%\Microsoft SQL Server\100\Setup Bootstrap\LOG\ Hotfix HP PolyServe Software for Microsoft SQL Server 4.
Update SQL Server instances
5 Configure Virtual SQL Servers 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.
Virtual IP: Specify the IP address that will be associated with this Virtual SQL Server. Name: Enter the NetBIOS name for this Virtual SQL Server; the name can include up to 15 characters. Application name: An “application” provides a tag to group Virtual SQL Servers and related virtual SQL instances on the Applications tab. All of the resources associated with the application can then be treated as a unit on the Applications tab.
is specified as the Default Coordinator, HP PolyServe Software cannot virtualize MSDTC. (Note that if MSDTC is virtualized and the Default Coordinator is then set to a Remote Host, the MSDTC will no longer be managed by the Virtual SQL Server.) The following limitations exist when using a Virtual SQL Server to manage MSDTC. • Because only one MSDTC resource exists per machine, you can define MSDTC support for only one Virtual SQL Server per machine.
NOTE: When you modify the configuration of an existing Virtual SQL Server, you can move interfaces between the Available and Members columns, in effect changing the interfaces on which the Virtual SQL Server is configured. However, you cannot move the interface on which the Virtual SQL Server is currently active from the Members column to the Available column. When you click OK, a progress report will display the tasks required to create the Virtual SQL Server. Click Run to start the operation.
mx vsql add [--policy autofailback|nofailback] [--application ] [--msdtc] [--force yes|no] ([ ...]) See the HP PolyServe Matrix Server command reference guide for information about the arguments. 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.
• An instance of SQL Server cannot be virtualized if the physical computer name is changed after the SQL instance is installed and before the instance is virtualized. 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.
Port: Enter the port number for TCP/IP Sockets that the SQL Server instance should listen on. Specifying a port is required. Virtual data root: Specify the full path that will be used for the data files and directories for this virtual SQL Server instance. This location will store the SQL Data Root that was created when the SQL Server instance was installed. (In mx commands, the virtual data root is referred to as the vpath.
NOTE: A progress window will report each step as the SQL instance is virtualized. The step “Virtualize on as primary” may take as long as five minutes to complete. To add a Virtual SQL Instance from the command line, use this command: mx vsqlinstance add See the HP PolyServe Matrix Server command reference guide for information about the arguments.
The monitors can be customized for your environment by setting the Advanced options. When you click Advanced on the dialog used to virtualize SQL instances or SSAS instances, 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. Timeout and Failure Severity: This setting works with the virtual host policy (either AUTOFAILBACK or NOFAILBACK) to determine what happens when the monitor probe fails.
NOFAILOVER. When the monitor probe fails, the virtual host 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. To use this option, you must also specify IGNORE as the Event Severity on the Scripts tab. AUTORECOVER. If the virtual host policy is AUTOFAILBACK, when the probe fails, the ClusterPulse process automatically begins failover of the associated virtual host to a backup node.
Script Pathnames: The 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.
Stop scripts must also handle recovery from events that may cause them to run unsuccessfully. For example, if the system encounters a problem, the script will fail and exit non-zero. The service could then become active on another node, causing the Pre Stop script to run on the original node even though the Post Start script did not complete successfully. Use custom scripts to modify Start/Stop activities The monitor performs certain starting or stopping activities.
or the action performed by the scripts is not critical, but is important enough that you want to keep a record of it. Script ordering Script ordering determines the order in which the Post Start and Post Stop scripts are run when a virtual host moves from one node to another. If you do not configure the monitor with Post Start and Post Stop scripts, the script ordering configuration has no effect. There are two settings: SERIAL and PARALLEL. SERIAL. This is the default setting.
of information that needs to be entered. You can obtain the information needed for an alias on the HP PolyServe Management Console. To create an alias, use the SQL Server Client Network Utility (C:\Windows\ system32\cliconfig.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.
A client can then connect to the databases by specifying the server alias on the Connect to SQL Server dialog. Rehost a Virtual SQL Server The “Rehost” option can be used to change the primary node for a Virtual SQL Server. The Rehost option is available on the HP PolyServe Management Console and can be accessed using either of these methods: • On the Applications tab, right-click on the Virtual SQL Server (in the Name column) and then select Rehost. (Or use drag and drop.
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 Manage msdtc as necessary to allow or disallow virtualization of MSDTC. If MSDTC was previously virtualized under the Virtual SQL Server, Manage msdtc must be checked to continue the virtualization on the new primary node. After making your changes, click OK. A progress report then appears.
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. Click this button again to retry the failed task. • Undo.
Performance monitoring with perfmon The Windows perfmon utility can be used to collect performance statistics for Virtual SQL/SSAS instances based on the performance counters installed with SQL Server. Virtual SQL/SSAS instances are monitored in the same manner as stand-alone SQL/SSAS instances or instances installed on a MSCS cluster. You must have the appropriate permissions to access the target server (via a physical or virtual hostname).
To place a Virtual SQL Server in maintenance mode from the command line, use this command: mx vsql maintain To return the Virtual SQL Server to operation mode, use this command: mx vsql operate Create a notifier for the Registry Replicator To assist with monitoring HP PolyServe Software, 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.
Virtual SQL Server procedures Modify the configuration To modify the configuration of a Virtual SQL Server, select it on the PolyServe Management Console window, right-click, and select Properties. You can then change the configuration. NOTE: If MSDTC was previously virtualized under the Virtual SQL Server, 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.
• Use the mx vsql update command and include or exclude the --msdtc option as appropriate. HP PolyServe Software 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, HP PolyServe Software 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.
Remove from Server option The Remove from Server option provided on the Management Console should be used only under the direction of PolyServe Technical Support. This option does not perform the necessary clean-up steps. If you need to remove a server from a Virtual SQL Server configuration, use one of these methods while all servers participating in the Virtual SQL Server are up and running: Drag and Drop, the Rehost Virtual SQL Server dialog, or the Virtual SQL Server Properties dialog.
mx vsqlinstance disable ... Clear monitor errors When a monitor error occurs, the monitor stops operating until the error is cleared. Check the Applications tab for monitor errors (a red E is displayed in the server column associated with the monitor). To clear an error, select the monitor, right-click, and select Clear Last Error. To clear an error from the command line, use this command: mx vsqlinstance clear ...
View status for a Virtual SQL instance The PolyServe Management Console shows the status of all Virtual SQL Instances configured in the matrix. To view status from the command line, use this command: mx vsqlinstance status [arguments] The arguments are: [--up|--down] [--primary|--backup] [--enabled|--disabled] [--active|--inactive] [--showApplication] With no arguments, the command shows status for all SQL monitors.
Configure SQL Mail and SQL Agent Mail The setup for SQL Mail and/or SQL Agent Mail is the same with or without HP PolyServe Software 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 Knowledge Base articles for more information: http://support.microsoft.com/kb/263556 http://support.microsoft.
Configure Virtual SQL Servers
6 Configure SQL Server Analysis Services Microsoft SQL Server Analysis Services (SSAS) provides online analytical processing (OLAP) and data mining features. This chapter describes how to configure SSAS for high availability. Overview Two or more cluster nodes are required to configure SSAS for high availability. In this configuration, SSAS is accessed via a virtual host (a Virtual SSAS). One cluster node is primary for the Virtual SSAS and the other nodes are backups.
Create a Virtual SSAS To create a Virtual SSAS, start the PolyServe Management Console and then select Matrix > Add > Add Virtual Analysis Services. The Add Virtual Analysis Services window appears. Virtual IP: Specify the IP address that will be associated with this Virtual SSAS. Name: Enter the NetBIOS name for this Virtual SSAS. The name can include up to 15 characters. Application name: An “application” provides a tag to group Virtual SSASs and related virtual SSAS instances on the Applications tab.
backup node, the ClusterPulse process watches the health of the nodes higher in the list of servers for that Virtual SSAS. When the health of one of these nodes is equal to or greater than the backup node where the Virtual SSAS currently resides, the Virtual SSAS will automatically attempt to fail back to that node. • NOFAILBACK. This policy is intended to minimize failovers and is the default.
To add a Virtual SSAS from the command line, use this command: mx vssas add [--policy autofailback|nofailback] [--application [--force yes|no] ([ ...]) See the HP PolyServe Matrix Server command reference guide for information about the arguments. Progress reports When you perform operations such as creating a Virtual SSAS, adding a virtual SSAS instance, or moving a Virtual SSAS to maintenance mode, a progress report is displayed.
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. The progress report provides two options that can be used if a task fails: • Run. Click this button again to retry the failed task. • Undo. Click this button to undo all tasks in the list and return the Virtual SASS to its original state. You can also cancel the operation at any time by clicking Cancel.
NOTE: HP PolyServe Software uses the following rules to determine the SSAS instances that can be assigned to the Virtual SSAS. The Name field does not include instances that do not meet these requirements. • The SSAS instance must exist on all of the nodes providing network interfaces for the Virtual SSAS. (The instance must have the same name on all of the nodes.) • The SSAS instance cannot be in use on any of the nodes as a primary for another Virtual SSAS.
The Advanced button on the Add Analysis Services Service window displays options that can be used to tailor the monitor for your environment. These options are the same as the advanced options for SQL monitors. See “Advanced options for monitors” on page 80, for more information. To add a SSAS instance from the Command Prompt: mx vssasinstance add --vpath [arguments] See the HP PolyServe Matrix Server command reference guide for information about the arguments.
Monitoring Virtual SSAS instances SQL Server Profiler When you start a new trace with SQL Server Profiler, you are prompted for the server name. The browse feature associated with the server name prompt cannot locate Virtual SSAS named or default instances. Instead, enter the name of the Virtual SSAS instance as the server name.
the Virtual SSAS instead of the localhost and you can use the tool to manage SSAS instances associated with the Virtual SSAS. Performance monitoring with perfmon The Windows perfmon utility can be used to collect performance statistics for Virtual SSAS instances based on the performance counters installed with SQL Server. Virtual SSAS instances are monitored in the same manner as stand-alone SSAS instances or instances installed on a MSCS cluster.
Rehost a Virtual SSAS The “Rehost” option can be used to change the primary node for a Virtual SSAS. You can use “drag and drop” on the Applications tab to move the Virtual SSAS from the current primary node to another node, or you can select the Rehost option using either of these methods: • On the Applications tab, right-click the Virtual SSAS (in the Name column) and then select Rehost. (Or use drag and drop.) • On the Servers or Virtual Hosts tab, locate the Virtual SSAS, right-click, and select Rehost.
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 SSAS. 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. Click this button again to retry the failed task. • Undo.
mx vssas move [--policy autofailback|nofailback] [--application ][--force yes|no] ([ ...]) The --force yes option causes the move to complete even if the operation encounters an error. Other Virtual SSAS procedures Maintenance mode When you need to apply a service pack or perform other SSAS maintenance, you can move a Virtual SSAS to maintenance mode. This mode disconnects all clients accessing the SSAS instances associated with the Virtual SSAS.
Add a new node to a Virtual SSAS The new node must be running Matrix Server and HP PolyServe Software and must belong to the same cluster as the nodes configured for the Virtual SSAS. To add the node to a Virtual SSAS, right-click on the Virtual SSAS, select Properties, and then add the node on the Update Virtual Analysis dialog.
To enable or disable a Virtual SSAS from the command line, use these commands: mx vssas enable mx vssas disable Delete a Virtual SSAS To remove a Virtual SSAS, select it on the PolyServe Management Console, right-click, and select Delete. To delete a Virtual SSAS from the command line, use this command: mx vssas delete [--force yes|no] View status for a Virtual SSAS The PolyServe Management Console shows the status of all Virtual SSASs configured in the matrix.
to fail over the Virtual SSAS to a backup node. If a backup node is not available or if the Policy configuration of the monitor is NOFAILOVER, the Virtual SSAS will remain active on the original node and the status reported for the monitor will be “UP Active (disabled).” To disable a Virtual SSAS instance 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.
for this instance already exist. To reuse these data files with the new Virtual SSAS Instance, click Yes. If you do not want to use the existing datafiles, click No. NOTE: The selected virtual path root (or vpath) directory must match the name of the instance being revirtualized. If the names do not match, the instance will not start. View status for a Virtual SSAS instance The PolyServe Management Console shows the status of the Virtual SSAS instances.
7 Using the Applications tab Overview The Applications tab on the PolyServe Management Console shows all SQL Server resources (Virtual SQL Servers and virtual SQL instances, Virtual SSAS and virtual SSAS instances) configured in the matrix and enables you to manage and monitor them from a single screen. The SQL Server resources are grouped under the applications that you specified when you created the resources. The applications and resources appear in the rows of the table.
The columns show the configuration of each Virtual SQL Server. A “P” indicates the primary server; a number indicates that the server is a 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. Everything is okay in this matrix. Virtual SSAS and Virtual SSAS instances The Applications tab shows the configuration of the Virtual SSAS and Virtual SSAS instances.
name assigned to the Virtual SSAS. In this example, the Virtual SSAS is configured on two servers. In the server columns, the “P” indicates the primary server for the Virtual SSAS; a number indicates that the server is a backup. The green checkmark specifies the server on which the Virtual SSAS is currently active. You can use the Status column to locate any problems in the matrix. Everything is okay in this matrix.
the following example for a virtual SQL instance monitor, the monitor error is on the primary server, tmr11s410. To clear the error, right-click in the server cell for the monitor and select Clear last error. Manage the SQL configuration The Applications tab provides two ways to manage the SQL configuration. You can use “drag and drop” to make changes, or you can right-click on an object and then select the appropriate menu item.
In the following simple example, Virtual SQL Server VSQL1 is providing Instance1, with the primary on node1 and the backup on node2. Virtual SQL Server VSQL2 is also providing an Instance1, with the primary on node3 and the backup on node 2. The Virtual SQL Servers are sharing a backup node for Instance 1, as it is unlikely that both Virtual SQL Servers will fail over at the same time. However, with drag and drop, it is possible to move the primary for VSQL2 to node2 and the backup to node1.
Using the Applications tab
8 Instance Aliasing The Instance Aliasing feature can be used to map your existing legacy SQL connections to the Virtual SQL Server connections used with HP PolyServe Software. 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.
Move the mapping file The Instance Aliasing mapping file, ia.conf, is installed with HP PolyServe Software for Microsoft SQL Server. By default, the file is placed in the following location on the drive where you installed HP PolyServe Software: %Program Files%\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.
Configure Instance Aliasing settings The Instance Aliasing feature needs some configuration information to operate properly. Click Advanced 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. Refresh rate synch interval.
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. From legacy IP. Enter the IP address to which clients connected when using the legacy default instance. Port. The port used by the legacy default instance must be 1433. To virtual IP.
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. To Virtual IP.
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. Modify Instance Aliasing mappings To change the mapping for a default or named legacy SQL Server instance, select the appropriate entry on the Instance Aliasing window and then click Edit. You can then change the mapping as necessary on the Edit Default Alias or Edit Named Alias dialog. Your changes will be added to the ia.
EXEC sp_addlinkedserver @server='mylinkedserver', @srvproduct='', @provider ='SQLNCLI', @datasrc ='virtualSQL1\Instance1' HP PolyServe Software for Microsoft SQL Server 4.
Instance Aliasing
9 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
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.
%Program Files%\Microsoft SQL Server\90\Setup Bootstrap\LOG\ HotFix Uninstall issues You may encounter various issues when uninstalling SQL Server 2005 instances. The following Microsoft Knowledge Base articles can help with failed uninstalls. • If you receive an error when uninstalling multiple named instances of SQL Server 2005, see the following article: http://support.microsoft.
1. Ping the Virtual SQL Server. For example: C:\>ping vqar13s11 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.
3. Nslookup the Virtual SQL Server. For example: C:\>nslookup vqar13s11 Server: qadc1.ad1.polyserve.com Address: 99.11.0.31 Name: vqar13s11.ad1.polyserve.com Address: 99.11.13.181 NOTE: If the nslookup fails, make sure an A record is created in DNS for the hostname. 4. Nslookup the Virtual SQL Server IP address. For example: C:\>nslookup 99.11.13.181 Server: qadc1.ad1.polyserve.com Address: 99.11.0.31 Name: vqar13s11.ad1.polyserve.com Address: 99.11.13.
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. Kerberos authentication is used only if the following prerequisites are met: • Both the client and server computers are running Windows 2000 SP3 or higher. • Both the client and server computers are part of the same domain or trusted domains.
A valid SPN for SQL Server is assigned to two types of containers. When the SQL Server service account is a domain administrator or the local system account, an SPN for SQL Server is automatically registered and assigned to the “hostname” container when SQL Server starts up. If the SQL Server service account is not a domain administrator or the local system account, the SPN for the SQL Server container is the service account.
http://support.microsoft.com/kb/319723 • Microsoft NTLM http://msdn.microsoft.com/en-us/library/aa378749(VS.85).aspx querySpn.vbs script ' Copyright (c) Microsoft Corporation 2004 ' File: querySpn.
Else 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.Echo " " & WScript.
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.
[-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 publisher_password] [-PublisherSecurityMode [0|1]] // set to 1 for SQL authentication [-SubscriberLogin subscriber_login] [-SubscriberP
3. Right-click Lsa, point to New, and then click DWORD Value. 4. Type DisableLoopbackCheck, and then press ENTER. 5. Right-click DisableLoopbackCheck, and then click Modify. 6. In the Value data box, type 1and then click OK. 7. Exit Registry Editor. 8. Restart the computer for the change to take effect. NOTE: When Matrix Server is installed, it can create the appropriate registry key automatically if desired.
To correct this problem, you will need to set the service account for the instance to either a domain user or LocalSystem on all nodes. When using SQL Server Configuration Manager to change the user for the service account, be sure to type the new user name using NTLM-style credentials (DOMAIN\user). You can also browse for the appropriate user account.
The service cannot be started: The following system error occurred: The request address is not valid in its context. This error occurs when an attempt is made to start a virtualized named SSAS instance on a primary node when the default SSAS instance is also installed on that node and has been started natively. By default, the default instance is configured to listen on 0.0.0.0:2383. An attempt to start another instance with another IP address, but listening on port 2383, will fail with the above message.
When you create the distributor, a Replicator Monitor should also be created for you. Configuring publication database(s) and selecting qualified subscriber(s) is similar to configuring a stand-alone server, as shown below. HP PolyServe Software for Microsoft SQL Server 4.
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 HP PolyServe Software for Microsoft SQL Server. Next, specify the database object(s) as part of the publication. HP PolyServe Software for Microsoft SQL Server 4.
Now accept and commit the settings for the publication. After the publication is initialized, specify the target subscriber and database for a push subscription.
Specify the desired frequency for each data distribution. HP PolyServe Software for Microsoft SQL Server 4.
Initialize the subscriber’s data. Specify the subscription mode.
Finally, commit and start the subscription. HP PolyServe Software for Microsoft SQL Server 4.
Troubleshooting
10 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.
2. Test the upgrade It is important to run the upgrade in a test environment to ensure that the upgrade will function successfully on the production server. Testing also ensures that there will be no surprises during or after the upgrade. For example, to test a SQL server upgrade, you should create a typical workload mix of your application’s user transactions and then measure different machine parameters such as CPU or I/O load. When the test environment is ready, complete these steps: 1.
.Net CLR version If you are unable to create an identical test machine, we recommend that you create a scaled-down version of your production machine and test any upgrades on it. Be sure to scale down the workload as well. Although a scaled-down machine deviates from the real environment, it will enable you to verify that the dependency among components does not affect SQL server availability after the upgrade. 3.
6. Confirm the upgrade If the upgrade was successful, check the SQL error logs and event logs for any errors. Also check basic database access by connecting with the Query Analyzer and querying one of the databases.If the upgrade failed, return the machine to the exact machine state before the upgrade. To do this, perform a restore using your backup files. 7. Notify users of upgrade completion When the upgrade is complete, notify users that they can resume their regular database activities. 8.
• If you need to reboot the machine, all of the instances will be affected. Upgrade procedure To perform the upgrade, complete these steps: 1. Move the Virtual SQL Server (VSQL) associated with the instance to maintenance mode. Select the Virtual SQL Server on the PolyServe Management Console, right-click, and select Maintain. 2. If the instance being upgraded is disabled on any backup nodes, select the instance on the PolyServe Management Console, right-click, and select Enable before proceeding. 3.
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. Clients will see a small downtime from two failovers (a failover to the backup node before the upgrade, and a failback after the upgrade) instead of a full upgrade downtime as in a non-clustered environment.
A Configure SQL Server Reporting Services HP PolyServe Software 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.
2. Create a Virtual SQL Server to serve as the configuration database. This Virtual SQL Server does not need to be on the same node(s) as the Report Server, making the SQL Server installation in step 1 optional. 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 73.) 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. 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. Configure Report Server for SQL Server 2008 For SQL Server 2008, take these steps: 1. Configure the Service Account. 2. Configure the Web Service URL. 3.
4. Configure Report Server on each node as described below. 5. Configure the Report Manager URL. 6. Configure E-mail Settings. 7. Configure the Execution Account. 8. Configure Encryption Keys.
9. On the Scale-out Deployment page, add servers to the deployment. See the Microsoft Reporting Services documentation for more information about configuring Report Server. Configure Report Server for SQL Server 2005 For SQL Server 2005, take these steps: 1. 2. 3. 4. 5. 6. Start Report Server. Configure Report Server Virtual Directory. Configure Report Manager Virtual Directory. Configure Windows Service Identity. Configure Web Service Identity.
7. 8. 162 Configure Backup/Restore Encryption Keys. Initialize Report Services.
9. 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: HP PolyServe Software for Microsoft SQL Server 4.
1. 164 Create a Matrix Server virtual host that clients can use to access the reporting services database. On the PolyServe Management Console, select Matrix > Add > Add Virtual Host. Configure the virtual host on all nodes that will act as reporting servers.
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. 3. Click Advanced and go to the Virtual Hosts tab. Select the virtual host that you created in step 1. HP PolyServe Software for Microsoft SQL Server 4.
4. 166 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. (In this example, REPORTSERVER$INSTANCE1.) Then enter Yes to start the dependent services and set the number of retries to one. HP PolyServe Software for Microsoft SQL Server 4.
6. Click Advanced. 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.
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. Similarly, if either of the NTSERVICE device monitors detect a failure, the associated virtual host can fail over to a backup node.
Configure SQL Server Reporting Services
B Hosting Configurations for MS DTC The information in this appendix applies to MS DTC when it is managed by a Virtual SQL Server (the “Manage msdtc” option is enabled in the Virtual SQL Server configuration). If you are configuring MS DTC for high availability, as described in Chapter 4, refer to that chapter for MS DTC configuration information.
HP PolyServe Software for Microsoft 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.
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. HP PolyServe Software for Microsoft SQL Server 4.
3. Right-click the My Computer folder to open its Properties dialog. 4. Select the MSDTC tab.
5. Ensure that Use local coordinator is checked and then click Security Configuration. HP PolyServe Software for Microsoft SQL Server 4.
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 • No Authentication Required. • Enable XA Transactions 7. Click 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 Manage msdtc to enable DTC management.
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. HP PolyServe Software for Microsoft SQL Server 4.
The following procedure can be used to set up a remote node to host DTC. The procedure is a recommended method of configuration; however, your application needs may differ. 178 1. Open Component Services: Start > Programs > Administrative Tools 2.
3. Right click the My Computer folder to open the Properties dialog. 4. Select the MSDTC tab. HP PolyServe Software for Microsoft SQL Server 4.
5. 180 Ensure that Use local coordinator is checked and then click Security Configuration.
6. On the Security tab, the following Security Settings should be checked: • Network DTC Access • Allow Remote Clients • Allow Remote Administration • Allow Inbound • Allow Outbound • No Authentication Required. • Enable XA Transactions 7. Click 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. Complete the following steps on each local node: 1.
3. Right click the My Computer folder to bring up its properties dialog. 4. Select the MSDTC tab.
5. Ensure that Use local coordinator is unchecked and, at the Remote coordinator host name prompt, specify the remote node that is hosting DTC. Then stop the MSDTC service from Run > services.msc > Distributed Transaction Coordinator. 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. 7. Set the startup method for the Distributed Transaction Coordinator service to Manual.
References The following sources are for MSDTC on Windows 2003. In general, the information also applies to MSDTC on Windows 2008, with the exception of the minor GUI change described in the following article: http://msdn.microsoft.com/en-us/library/aa561924(BTS.20).aspx 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.
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 HP PolyServe Software for Microsoft SQL Server 4.
Hosting Configurations for MS DTC
C Support and other resources HP technical support For worldwide technical support information, see the HP support website: http://www.hp.
Support and other resources
Index A active-active configuration, 11 Active-Passive architecture, 9 Analysis Services service, restart, 104 Applications tab drag and drop, 118 manage configuration, 118 monitor error, 117 overview, 115 SQL resources, 115 Virtual SQL Server, 115 Virtual SSAS, 116 authentication, Windows, 42 AUTOFAILBACK policy Virtual SSAS, 100 B BUILTIN\Administrators group requirement, 42 C configuration Instance Aliasing, 122 SQL clients, 85 Virtual SQL Instance, 77 Virtual SQL Server, 73 Virtual SSAS, 99 D DNS re
installation, SQL instance install SQL Server 2005 instance, 34 install SQL Server 2008 instance, 24 Maximum Memory setting, 41 operating system support, 15 overview, 15 prerequisites, 15 Instance Aliasing configure, 122 delete mappings, 126 ia.
S server, remove from Virtual SQL Server configuration, 94 SQL Agent Mail, configure, 97 SQL Aqent, 96 SQL clients configure, 85 SQL configuration active-active, 11 many-to-one, 11 planning for, 10 SQL instance map legacy instances, 121 overview, 15 requirements for virtualization, 78 uninstall, 97 upgrade, 61 virtualization failure, 140 SQL Mail, configure, 97 SQL Server 2005 installation considerations, 21 installation prerequisites, 15 installation procedure, 34 supported OS, 15 upgrade considerations, 6
T V TCP/IP port assignments, 42 technical support HP, 187 service locator website, 187 troubleshooting authenticate with virtual host name, 139 authentication failure with replication, 138 distributor configuration error, 142 DNS issues, 131 installation failures, 130 installation via Terminal Services client, 130 remove MSCS, 140 SQL instance does not start, 141 SQL Server 2005 installation, 129 SSAS instance does not start, 141 uninstall failures, 131 uninstall SQL instance, 97 Virtual SQL Instance acce
Virtual SSAS add node, 111 backup interface, 101 configure, 99 create, 100 delete, 112 DNS requirement, 99 enable or disable, 111 failback policy, 100 maintenance mode, 110 modify configuration, 111 on Applications tab, 116 primary interface, 101 progress report, 102 rehost, 108 view status, 112 Virtual SSAS Instance instance does not start, 141 Virtual SSAS instance add to Virtual SSAS, 103 delete, 113 enable or disable, 112 modify configuration, 112 monitor error, 117 monitoring, 106 on Applications tab,
