SQLH2 V2 Deployment Guide

Document Sample
SQLH2 V2 Deployment Guide Powered By Docstoc
					                    Microsoft SQL Server Health and History Tool
                                     Version 2
                                Deployment Guide




Introduction............................................................................................................................................... 2

Design Considerations ........................................................................................................................... 2

Components ............................................................................................................................................... 3
  Required Components ............................................................................................................................... 3
          1.   The SQLH2 Collector ....................................................................................................... 3
          2.   The SQLH2 Repository .................................................................................................... 3
  Optional Components ................................................................................................................................ 3
          1.   The SQLH2 Performance Collector ................................................................................ 3
          2.   The SQLH2 Reports ......................................................................................................... 3

Getting Started With Setup .................................................................................................................. 3
         1.   Locating the SQLH2 Tool ................................................................................................ 3
         2.   Installing SQLH2 Collector .............................................................................................. 3
         3.   Creating the SQLH2 Repository and Configuring the SQLH2 Collector .................... 4

Frequency of Collection ......................................................................................................................... 4

Appendix A – What SQLH2 Collects .................................................................................................. 5
  Hardware and Configuration (Windows Management Instrumentation) ........................................ 5
  Outages and Events ................................................................................................................................ 5
  SQL Server (General) .............................................................................................................................. 6
  SQL Server (SQL Server 2005 Only) ..................................................................................................... 6

Appendix B – Unsupported items ...................................................................................................... 7

Appendix C – Working on localized platforms .............................................................................. 7
  /D switch ................................................................................................................................................... 7
  How to know when to use /D switch .................................................................................................... 8
  /d switch ................................................................................................................................................... 8

Appendix D – common errors .............................................................................................................. 8
  Invalid class: SELECT * FROM Win32_Product ................................................................................... 8
  SQL Server does not exist or access denied........................................................................................ 8
  The conversion of a char data type to a datetime data type resulted in an out-of-range
  datetime value. ........................................................................................................................................ 9
  Event is not in an expected format ....................................................................................................... 9
  Cannot read log entry number ###. ................................................................................................... 9

Introduction

The Microsoft SQL Server Health and History Tool (referred to as SQLH2 throughout this
document) is designed to allow you to collect information from instances of SQL Server, store this
information, and run reports against the data in order to determine how SQL Server is being
used.

Version 2 of SQLH2 captures the system configuration, the server settings, and the hardware
specifics for an instance of SQL Server. This version is compatible with all editions of Microsoft
SQL Server 2005 (Express, Workgroup, Standard, Enterprise, and Datacenter Edition) and
Microsoft SQL Server 2000 (MSDE, Developer, Standard, Enterprise, and Datacenter Edition).
This version does not capture information from instances of SQL Server version 6.0, 6.5, or 7.0;
the tool does not even report that those versions are installed.

You can choose whether to send collected information to Microsoft. If you choose to
upload data to Microsoft, the information will remain anonymous. This is accomplished by
tagging the collected information with a random identification number. No information is
uploaded that could identify your company as the source of the data, such as security
information or specific user database information. If you choose to upload information to
Microsoft, the SQL Server product team can use the data when deciding how to focus resources
on the features and services that customers use most frequently. Choosing to upload your
information helps Microsoft to create better database products for our customers.

This document is designed to quickly help you to learn about, install, configure, and use SQLH2.
Reviewing this information and then installing and configuring SQLH2 for a single server can take
as little as 30 minutes.

If you would like to share your comments with SQLH2 team, please send mails to
sqlh2@microsoft.com


Design Considerations

A primary design goal for SQLH2 was to make it simple to deploy and to manage:

              The default values of SQLH2 should be adequate for most SQLH2 deployments.
              SQLH2 makes minimal use of the registry, and all configuration values are
               written to an XML file. This XML file can be easily modified, archived and centrally
               maintained by you.
              SQLH2 does not require you to deploy or manage any software on any target
               system. SQLH2 has this flexibility because it is not intended to be a replacement
               for real-time monitoring systems. SQLH2 "pulls" all information across the wire,
               rather than using an agent to "push" the information. Because SQLH2 does not have
               to distribute, configure, and manage agent executables across an enterprise, you can
               very easily deploy, manage, and re-deploy SQLH2, or you can completely remove it
               without leaving a trace.
Components
SQLH2 has a modular architecture. It is comprised of discrete components that can be deployed
in a variety of different topologies.


Required Components
    1. The SQLH2 Collector is the primary application of SQLH2. It has three main functions:
        To collect data.
        To place the data in the SQLH2 Repository.
        To manage the Export Functions of SQLH2 data.

    2. The SQLH2 Repository is a SQL Server database that stores the data that SQLH2
       collects. SQLH2 runs all reports against this repository.


Optional Components
    1. The SQLH2 Performance Collector is a stand-alone service of Microsoft Windows that
       collects and stores performance counter data from selected servers. You should install
       this optional component if you are interested in collecting performance counters along
       with system information. The SQLH2 Collector gathers the data that this service collects
       and stores this data in the repository when you run the SQLH2 Collector. The
       Performance Collector is optional, and is available for download from the Microsoft
       Download Center (search for SQL Server with keyword SQLH2):
       http://www.microsoft.com/downloads/search.aspx

    2. The SQLH2 Reports are a set of reports that provide different views on the data that
       the tool collects. You must have SQL Server Reporting Services installed and running in
       order to use the reports. The SQLH2 Reports component is optional, and is available for
       download from the Microsoft Download Center (search for SQL Server with keyword
       SQLH2): http://www.microsoft.com/downloads/search.aspx


Getting Started With Setup
1. Locating the SQLH2 Tool
You can find SQLH2 version 2 in the following locations:
      SQL Server 2005 Beta 2 Resource Kit CD
      Microsoft.com download site
      The Microsoft Beta download site https://beta.microsoft.com/
      The Microsoft Developer Network (MSDN) Downloads site (requires you to have an
MSDN subscription).

2. Installing SQLH2 Collector
First, decide where you want to install the SQLH2 Collector. The SQLH2 Collector is the heart of
the SQLH2 system. It is a stand-alone executable that you run from the command prompt, and it
has its own installation program.

   The SQLH2 Collector can collect data from many different target machines. A target machine
    does not have to be running SQL Server to be a valid target machine; in fact, a target
    machine does not even need to have SQL Server installed.
   The SQLH2 Collector is designed to collect the appropriate information from the target
    machine, and can automatically discover all of the instances of SQL Server on a target
    machine. You simply point the SQLH2 Collector to one or more target machines, and allow it
    to run.
   Because the SQLH2 Collector requires special permissions to be able to seek out and discover
    all of the information on a given machine, it is recommended you run the SQLH2
    Collector with the same credentials as that of a typical system administrator.
    However, to reduce security exposure and to add a level of safety to the management of
    such an account, SQLH2 is engineered to run by default as a “READ_ONLY” application.
   The SQLH2 Collector does not change any settings on a target machine, nor does it
    in any way alter databases, registry entries, or any file system objects.
   To install the SQLH2 Collector, run the H2Setup.msi file.
    NOTE: Version 2 of the SQLH2 Collector requires the SQL Server bcp.exe utility to import or
    export data. The easiest way to get bcp.exe is to install the SQL Server client tools on the
    same machine.
   For the SQLH2 tool to be able to collect a full complement of information, the SQLH2
    Collector must have adequate privileges to each database, typically that of a full database
    administrator.

3. Creating the SQLH2 Repository and Configuring the SQLH2 Collector
At the end of the SQLH2 installation process, the SQLH2 Configuration Utility will start. This utility
is part of the SQLH2 package, and you can use it to perform different installation and
configuration tasks.

The wizard will guide you through the steps that are necessary to create the SQLH2 Repository
and to configure the SQLH2 Collector, so that you can run SQLH2 as soon as the installation is
complete.
    1. First, choose a machine and an instance of SQL Server that you want to use to host the
        SQLH2 Repository database. The only requirement is that the machine that you choose
        for the SQLH2 Repository must be accessible by the SQLH2 Collector using Windows
        Authentication. You can choose to use a single machine for all of the SQLH2
        components.
    2. Choose whether to allow the data from the SQLH2 Repository to be shared with
        Microsoft. If you allow SQLH2 to send data, the SQLH2 Collector will create a compressed
        file and will send it to Microsoft over a secure Internet connection every time you run the
        SQLH2 tool. This requires that your SQLH2 collection machine have direct access to the
        Internet.
    3. After you enter all of the necessary information, the wizard will create the repository
        database and all of the necessary objects. The SQLH2 Collector configuration file is then
        updated to point to the newly created repository.
    4. Next, create the list of computers from which SQLH2 should collect information (the
        SQLH2 target servers). By default, the list contains only the local computer.
    5. Last, create a scheduled task for SQLH2. The wizard will create a task from your input by
        using the schtasks.exe utility.
        NOTE: The schtasks.exe utility is only shipped with Windows Server 2003 and
        Windows XP. In earlier Windows environments (Windows 2000), the wizard cannot
        create a task, so you must create the task manually.


Frequency of Collection
SQLH2 takes snapshots of all target machine system and SQL Server settings as often as you
want. We recommend running SQLH2 twice per week to record changes to your target
systems.

You can also install and use the optional SQLH2 Performance Collector (a Windows service that is
running as long as the service is “ON”) to collect performance data. The SQLH2 Performance
Collector data files can grow quite large, especially if you monitor numerous servers and/or
numerous performance counters, and if you run the collection frequently. The frequency of
collection is indicated in the H2PerfConfigFile.xml file in the directory where you installed the
SQLH2 Performance Collector. We recommend using the default value of 120 seconds
between performance counter collections.


Appendix A – What SQLH2 Collects

The SQLH2 Collector gathers a wealth of information from each target server. The specifics of
the information that SQLH2 gathers are provided here, in the interest of full disclosure so that
you can be aware of exactly what information is collected and what information will be uploaded
to Microsoft, assuming you choose to share this information. As discussed earlier in this
document, information uploaded to Microsoft by using the SQLH2 tool is completely anonymous.


Hardware and Configuration (Windows Management Instrumentation)
       Computer system (DomainRole, Manufacturer, Model, NumberOfProcessors,
        SystemStartupOptions, TotalPhysicalMemory)
       Processors (prc_id, ExtClock, DeviceID, CpuStatus, CurrentClockSpeed)
       Disk drives (Idx, ConfigManagerErrorCode, ConfigManagerUserConfig, InterfaceTypeID,
        BytesPerSector, ModelID, Partitions, PowerManagementSupported, SCSIBus,
        SCSILogicalUnit, SCSIPort, SCSITargetId, Size, TotalCylinders, TotalHeads)
       NIC (card_id, AdapterTypeID, Availability, ConfigManagerErrorCode,
        ConfigManagerUserConfig, Idx, MACAddress, MaxSpeed, Speed,
        PowerManagementSupported, TimeOfLastReset)
       NIC configuration (Idx, DHCPEnabled, IPEnabled, IPFilterSecurityEnabled, IPXEnabled,
        TcpipNetbiosOptions)
       Operating System configuration (BootDevice, BuildNumber, BuildType, Caption, CodeSet,
        CountryCode, CSDVersion, CSName, CurrentTimeZone, Debug, Distrib,
        FreePhysicalMemory, FreeSpaceInPagingFiles, FreeVirtualMemory, InstallDate,
        LastBootUpTime, LocalDateTime, Locale, MaxProcessMemorySize, NumberOfProcesses,
        NumberOfUsers, OSLanguage, OSProductSuite, PAEEnabled, ServicePackMajorVersion,
        ServicePackMinorVersion, SizeStoredInPagingFiles, SystemDevice, SystemDirectory,
        TotalVirtualMemorySize, TotalVisibleMemorySize, Version, WindowsDirectory)
       Installed products (prod_id, InstallDate, InstallState)
       Installed services
       Running tasks (task_id, ProcessId, SessionId, CreationDate)
       Event Log configuration (type, FileSize, MaxFileSize, OverwriteOutDated, Name,
        OverwritePolicy, Status)
       Installed updates [using the registry] (qfe_id, InstalledDate, InstalledBy)


Outages and Events
       Operating system outages (start, finish, IsDirty, state, version, duration)
       SQL Server outages (start, finish, state, pid, version, duration)
     Event Log history (type, start, start_index, finish, finish_index, e_count, IsHistory, IsGap)
     Dr. Watson for SQL Server events (source, timegenerated, info[bucket])
     SQL Server access violations or assertions (time, message)
     Dumps [using the file system] (fname, fsize, ftime)

SQL Server (General)
     Instances of SQL Server (major, minor, build, version, edition, plevel)
     Syscurconfigs (value, config)
     Dr. Watson enabled (yes/no)
     SQL Errorlog (path, logsize, depth)
     Databases (dbid, name, CompatibilityLevel, CreateDate, DataSpaceUsage,
      IndexSpaceUsage, dbSize, SpaceAvailable, IsSystem, txSize, txSpaceAvailable,
      txLastBackup, oRecoveryModel, oPageVerify, oUserAccess, oAutoClose, oAutoShrink,
      oAutoCreateStat, oAutoUpdateStat, oOffline, oReadOnly, oSelectIntoBulkCopy,
      oTruncateLogOnCheckpoint, oPublished, oSubscribed, oMergePublished,
      oMergeSubscribed, oFullTextEnabled, oDbChaining)
     FileGroup (dbid, fgid, name, IsDefault, IsReadOnly, IsOffline, fgSize, type)
     DBfiles (dbid, fgid, fid, name, IsPrimary, IsReadOnly, physicalName, fSize, fSpaceUsed,
      MaxSize, GrowthType, Growth, IsLog)
     Running profiler traces (trace_id, property, value)
     Trace history (trace_id, start, finish)


SQL Server (SQL Server 2005 Only)
     Instances of SQL Server (Collation, EngineEdition, IsClustered, IsFullTextInstalled,
      IsIntegratedSecurityOnly, IsSingleUser, LicenseType, NumLicenses, IsAD)
     Databases (dbid, oBrokerEnabled, oReadCommittedSnapshotOn, SnapshotIsolationState,
      Collation, oDistributor, qIndexedViews, mirroringSafetyLevel, mirroringWitness,
      IsRosetta)
     Dataspaces (dbid, dsid, name, IsDefault, type)
     Partition schemes
     Partition functions (fnId, type, fanout, boundary_value_on_right)
     Partition parameters [aggregated]
     Database objects [aggregated]
     Columns [aggregated]
     Indexes [aggregated]
     Triggers [aggregated]
     Assemblies [aggregated]
     Assembly types [aggregated]
     Event notifications [aggregated]
     Notification subscriptions [aggregated]
     Cursors [aggregated]
     Endpoints [aggregated]
     Linked servers [aggregated]
     Internal tables [aggregated]
     Backups [aggregated]
     Text In Row property (OBJECTPROPERTY('TableTextInRowLimit') ) [aggregated]
     Global Trace Flags (DBCC TRACESTATUS (-1))
     iMail settings (hostId, isProfile, isAccount)
     Service Broker settings [aggregated] (dbid, Routes, Queues, ActivatedQueues,
      MessageTypes, TypedMessageTypes)
     Reporting Services – number of reports run
       Full Text info [aggregated] (dbid, Catalogs, Tables, LastCrawl, BackgroundIndexes)
       Suspect Page table [aggregated]
       Certificates [aggregated]
       sys.dm_exec_query_optimizer_info


Appendix B – Unsupported items

There are certain limitations to SQLH2 usage.
    SQLH2 should not be used to collect information from target machines that are localized.
        SQLH2 relies on text analysis in the Event Log and will not recognize messages in
        languages other than English. This is also true for date and number formatting in the
        Event Log. However, an updated version of SQLH2 (2.0.24) provides certain support (See
        Appendix C for more details).
    SQLH2 does not support Microsoft failover clusters hosting SQL Server instances. SQLH2
        is not able to detect a clustered instance and will not be able to connect to that server to
        collect SQL Server related information. Analyzing Event Logs on clusters is also a
        complicated subject and may lead to inaccurate uptime results. Supplying the virtual
        name as a target may produce inadequate results. Local (non-clustered) instances
        running on cluster nodes are fully supported as targets (as they don‟t differ from non-
        clustered SQL Server installations). Non SQL Server information from physical cluster
        nodes is also collected appropriately.


Appendix C – Working on localized platforms

Starting from version 2.0.24, SQLH2 provides better support for localized platforms. Although
certain scenarios still can not be handled (for example, locale was changed to one with different
format).

There are a few requirements that need to be satisfied:
1.      All target machines as well as the SQLH2 collector machine must have the same format
for dates
2.      Number format on SQLH2 collector machine should have Decimal symbol „.‟
3.      Depending on the settings (default language for SQL Server connections and similar) it
may be necessary to run SQLH2 with command-line argument /D (it will make SQLH2 issue SET
DATEFORMAT for each connection).


/D switch
Adding the /D switch on the SQLH2 command line accepts parameters in the same format as SQL
Server‟s SET DATEFORMAT command – lower or uppercase combinations of letters „M‟, „D‟ and
„Y‟, specifying position for Month, Day and Year.

Below is the syntax to force „European‟ format DMY (example: 30.09.04 means September 30,
2004)

        Sqlh2 /DDMY
How to know when to use /D switch
If you see the following error message in the H2log.txt, the date format on your SQLH2 collector
machine is different from default format of the SQL Server connection you are getting the error
from.

ERROR: The conversion of a char data type to a datetime data type
resulted in an out-of-range datetime value.

For example if your SQLH2 collector machine‟s locale is English UK, your default date format is
DMY (30/09/04 means September 30, 2004). In this case you should use /D switch with DMY
setting.


/d switch
Version 2.0.25 introduces /d switch to better support optional Performance Collector in localized
environments. SQLH2 loads performance data collected by Performance Collector using BCP
utility. The switch makes SQLH2 to call BCP using R switch. R switch forces BCP to process
datetime data using client-side format (as opposite to server-side).

The usage of the switch shown below

        Sqlh2 /d

NOTE: SQLH2 command line switches can be used in any order and any combination. The
following example will force „European‟ date format along with forcing BCP R switch:

        Sqlh2 /DDMY /d




Appendix D – common errors
SQLH2 records errors to mgr_run_error table as well as the H2log.txt file.

Invalid class: SELECT * FROM Win32_Product
This is one of the most frequently encountered errors. It indicates that the WMI provider for the
class is not installed on the box (it is not installed by default on Windows Server 2003).
To install this class on the target machine (Windows Server 2003):
     Go to Add/Remove Programs -> Add/Remove Windows Components.
     Select “Management and Monitoring Tools” and click on “Details”.
     Make sure “WMI Windows Installer Provider” is checked and proceed with
        installation.


SQL Server does not exist or access denied
SQLH2 scans the registry on the target machine and discovers all installed SQL Server instances.
It then tries to connect to each one of them. The error message is a generic one returned for
unsuccessful connection. Most likely it indicates that the requested SQL Server instance is not
currently running.
If access to the instance is not granted to the account used to run SQLH2, the message will likely
be
Login failed for user "…".


The conversion of a char data type to a datetime data type resulted in an out-of-
range datetime value.
This error is returned by SQL Server. The format of the supplied date is different from expected.
Command-line switch /D is specifically designed to resolve this issue. See Appendix C for more
details.


Event is not in an expected format
The most probable cause of this error is date format mismatch. Format used to record time to
the log is different from the one from SQLH2 collector machine. See Appendix C for more details.


Cannot read log entry number ###.
This error usually indicates that the event log on the target machine is corrupted. It‟s easy to
verify using Event Viewer – try to open the suspected event log and check if you can read events
from it (sometimes Event Viewer is able to show the list of events, but an actual attempt to read
some events fails).

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:30
posted:9/19/2011
language:English
pages:9