New Virtual SQL/SSAS Install on a Windows Server 2008 Cluster

These are the directions my company uses to install virtual SQL/SSAS instances on a Windows 2008 Cluster.  Hope you enjoy it.

NOTE:  Please refer to the steps outlined on the http://www.mssqltips.com/tip.asp?tip=1687 for virtual SQL clustered installs.

Pre-Requisites:

To install a new SQL/SSAS instance on a Windows Server 2008 cluster, the following must be present before the install can proceed.

  • Cluster Network Name – Requested by the WSE team, the hostname of the cluster.
    Example: HELWD1CL01P 

  • Cluster Network Object – Same as the cluster hostname but its entry in Active directory.
    Examples:  HELWD1D1CL01P$ 

  • Cluster Node(s)– Requested by the WSE team, the hostname of a nodes in the cluster.
    NOTE:  We need only 1 node active in a cluster to perform SQL installs.
    Example:  HELWD1DBCL01P 

  • SQL Service Account – Requested by the DBA team, this SQL service account will own/start all SQL/SSAS instances on a given cluster.  This login must also be added to the SQL Servers NT security group.
    NOTE:  This account needs trusted for deletion set [Active Directory User Property à Delegation à Trust this user for delegation to any service (Kerberos only)].
    Example:  INSPINC\HELTESTSQLSVC 

  • Cluster MSDTC Hostname – Requested by the DBA team, this is the DNS entry for the clustered Microsoft Distributed Transaction Coordinator (MSDTC).
    NOTE:  Each cluster needs only 1 MSDTC host.
    Example:  HELWD1MSDTC01P 

  • Cluster MSDTC VCO – Requested by the DBA team, this is the Active Directory Virtual Computer Object [VCO] for the cluster MSDTC hostname.
    NOTE:  This is the same as the cluster MSDTC hostname.
    Example:  HELWD1MSDTC01P 

  • Cluster MSDTC Storage – Requested by the DBA team, this storage is required for installing a clustered MSDTC.
Mount Point Type [FC/FATA] RAID [1/5] Size [GB]
Z:\ FC 5 1
  • Virtual SQL/SSAS Instance Hostname – Requested by the DBA team, this is the DNS entry for the virtual SQL/SSAS instances.
    NOTE:  The virtual SQL instance hostnames must be in the same IP subnet as the cluster network name/cluster node(s).
    SQL Instance Example:  DWWMAINVSQL5P
    SSAS Instance Example:  DWWVOLAP5P 

  • Virtual SQL/SSAS Instance VCO Requested by the DBA team, this is the Active Directory Virtual Computer Object [VCO] for the virtual SQL/SSAS instance hostname.
    NOTE:  This is the same as the Virtual SQL/SSAS instance hostname.
    Example:  DWWMAINVSQL5P
  • Virtual SQL/SSAS Instance Storage – Requested by the DBA team, this storage is required for installing a virtual SQL/SSAS instance.  All root drive letters are available except A-F, W [Witness] and Z [MSDTC].
    NOTE:  By default we request a root drive letter, 2 data, 1 log and 1 tempdb volume for all SQL instances.  For SSAS instance a root drive letter, 1 data and 1 tempdb volume is required.  Always start with root drive letter G.
    SQL Storage Example:
Mount Point Type [FC/FATA] RAID [1/5] Size [GB]
G:\ FC 5 1
G:\Data1 FC 5 50
G:\Data2 FC 5 50
G:\Logs FC 1 25
G:\TempDB FC 1 25

SSAS Storage Example:

Mount Point Type [FC/FATA] RAID [1/5] Size [GB]
G:\ FC 5 1
G:\Data1 FC 5 50
G:\TempDB FC 1 30

Install of a new virtual SQL/SSAS Instance:

Here are the steps to follow in order to install a new virtual SQL/SSAS instance on a given cluster.

  1. Login to each node of the cluster with the NT credentials.  Verify you can login with any errors.
  2. Verify and add, if needed, the following users and groups to the local administration group on each node of the cluster.
    SQL Service Account
    DBA Security Group
    DBA Service Account
  3. Run the Local Group Policy Editor [gpedit.msc] and verify/add the SQL Service Account to the following policy, if needed:
    Windows Settings –> Security Settings –> Local Policies –> User Rights Assignment
    Lock pages in memory
    Perform volume maintenance tasks 

  4. Run the Registry Editor [regedit] and create, if necessary, the following registry key.
    HKLM –> SYSTEM –> CurrrentControlSet –> Control –> Lsa
    DisableLoopbackCheck [DWORD(32) Value] –> 1 [Value]
  5. Run the Failover Cluster Manager [cluadmin.msc] and install/configure, if needed, the MSDTC clustered instance.
    NOTE:  Refer to instructions on the http://www.mssqltips.com/tip.asp?tip=1709 for detailed steps.
  6. Run the Component Services [comexp.msc] to set cluster DTC options, if needed.

NOTE:  This need to only happen once on the cluster.
Console Root –> Component Services –> My Computer –> DTC –> Clustered DTCs –> <MSDTC Hostname> –> Properties –> Security
{Checkbox Settings}
Network DTC Access
Allow Inbound
Allow Outbound
Enable XA Transactions

  1. Create the following directories under the root drive letter for each virtual SQL/SSAS instance you will be installing.
    NOTE:  You will need to know the instance name for each virtual SQL instance.
    SQL Example:
    <ROOT DRIVE LETTER>:\Data1\MSSQL\Data
    <ROOT DRIVE LETTER>:\Data2\MSSQL\Backup
    <ROOT DRIVE LETTER>:\Data2\MSSQL\Data
    <ROOT DRIVE LETTER>:\Logs\MSSQL\Log
    <ROOT DRIVE LETTER>:\TempDB\MSSSQL\TempDB 

    SSAS Example:
    <ROOT DRIVE LETTER>:\Data1\MSAS\Data
    <ROOT DRIVE LETTER>:\Data1\MSAS\Backup
    <ROOT DRIVE LETTER>:\Data1\MSAS\Log
    <ROOT DRIVE LETTER>:\TempDB\MSAS\TempDB

  2. Create a dummy filename under the root drive letter to identify the virtual SQL instance which will be installed:
    <ROOT DRIVE LETTER>:\<VIRTUAL SQL/SSAS HOSTNAME>$<INSTANCE NAME>_DO_NOT_REMOVE.TXT
  3. Using nslookup, record the IP address for the virtual SQL instance you will be installing.

10.  Run the SQL installation program for the virtual SQL instance you will be installing.
NOTE:  Refer to instructions on the http://www.mssqltips.com/tip.asp?tip=1709 for detailed steps.  Accept default values except for the steps identified below.

Step 7 –
Instance Features [SQL]:
Database Engine Services
SQL Server Replication
Full-Text Search

Instance Features [SSAS]:
Analysis Services

Shared Features:
Business Intelligent Development Studio
Client Tools Connectivity
Integration Services
Client Tools Backwards Compatibility
SQL Server Books Online
Management Tools – Basic & Complete

Step 8
Specify the correct SQL/SSAS Network Name/Named Instance:
Example:
SQL Network Name = DWWMAINVSQL5P Instance = DWMAIN5
SSAS Network Name = DWWVOLAP5P Instance = DWOLAP5

Step 10
Specify the following cluster resource group name:
Example:
SQL      SQL Server (<DWWMAINVSQL5P>)
SSAS   SQL Analysis Services (<DWWVOLAP5P>)

Step 11
UNCHECK any drives specified and check the root drive letter for the SQL/SSAS instance in question.

Step 12
UNCHECK DHCP and specify the IP address of the appropriate SQL/SSAS Network Name.

Step 14 –
Service Accounts:
Click “Use the same account for all SQL Server services” and specify the SQL Service Account login and the password.  The passwords is located in the Party List.

Collation:
Specify the collation Latin1_General_CI_AS for Data Warehouse SQL Instances only.  Use default for all other installs.

Step 15 –
Add the following logins for SQL/SSAS Server administrators.
SQL Service Account
DBA Security Group
DBA Service Account

SQL Server Installs
Click Mixed Mode and specify an ‘sa’ password.

Specify the following values for directories:
Data root           <ROOT DRIVE LETTER>:\Data1\MSSQL
User dir:            <ROOT DRIVE LETTER>:\Data1\MSSQL\Data
User log:           <ROOT DRIVE LETTER>:\Logs\MSSQL\Log
TempDB dir:      <ROOT DRIVE LETTER>:\TempDB\MSSQL\TempDB
TempDB log:     <ROOT DRIVE LETTER>:\TempDB\MSSQL\TempDB
Backup:            <ROOT DRIVE LETTER>:\Data2\MSSQL\Backup

Analysis Services Installs
Specify the following values for directories:
Data root           <ROOT DRIVE LETTER>:\Data1\MSAS\Data
User dir:            <ROOT DRIVE LETTER>:\Data1\MSAS\Log
TempDB dir:      <ROOT DRIVE LETTER>:\TempDB\MSAS\TempDB
Backup:            <ROOT DRIVE LETTER>:\Data1\MSAS\Backup

11.  Repeat step above for as many SQL/SSAS instance as needed.  Once your installs are complete, upgrade to the latest SQL CU hotfix.

Advertisements

Deploying User Defined Performance Counters

Performance Collector Application Box

It is recommended that all user defined performance counter collections for SQL instances [physical or virtual] occur from dedicated application box(s).  Based on your security policy [data center or production vs. test/demo], there maybe 1 or more of these application box(s) setup for this purpose.  The performance collector SQL database, which would store the user defined performance counters, can also be segregated based on security policy.

The dedicated application box(s) will need firewall access opened to the SQL instances they are collecting from.  The firewall rules required for remote performance collection are the same as those required for SMB access.  The same application will need to have firewall access opened to log the data to the performance collector SQL instance.  You may need to disable any virus checking application as it may interfere with remote performance collection.

Here is an example of rules that you may need to set  for access.  Please consult with the network/security team for rules and questions.

Performance Collector Application Box à Monitored SQL Instances

Source Host:                 DBAWPRPL1P ß Sample hostname of the application collection box(s)

Destination Host:           <IP Network Subnet>, <IP Network Subnet>, …, <IP Network Subnet>

Ports:                           TCP – 135-139,445

                                    UDP – 135-139, 445

Performance Collector Application Box à Performance Collector SQL Instances

Source Host:                 DBAWPRPL1P ß Sample hostname of the application collection box(s)

Destination Host:           DBAMONVSQL1P ß Sample performance collector SQL instance

Ports:                           TCP – 1433

                                    UDP – 1434

Setting up System DSN for Performance Collector SQL Instance

Now you must configure a System DSN [System Data Source] so the performance collectors can write to the backend performance collector SQL instance.

  1. Go to Start –> Control Panel –> Administrative Tools.
  2. Open Data Sources (ODBC) tool.
  3. Go to System DSN –> Add.
  4. Choose SQL Server Native Client and click Finish.
  5. Specify the name DSN name, description [if needed] and name of the SQL instance [hostname or hostname\instance].  Click Next.
  6. Choose either an integrated Windows authentication or SQL login.  Click Next.
  7. Specify the default database where the performance collector tables exist.  Click Next.
  8. Specify any additional options you wish, as needed.  Click Finish when complete.

 

Manually Creating User Defined Performance Counters

You now need to create a user defined performance counter collection.  This is where you specify all the counters you wish to collect from the monitored SQL instance.  This procedure assumes you are using Windows 2008 Server for your application collector box.

  1. Go to Start –> Run and type in perfmon.
  2. Open Data Collector Sets –> User Defined à right click New –> Data Collector Set.
  3. Specify a collector name.  It is recommended that you come up with a standard for all monitored SQL instance collector names.  For example:  SMPWPROVVSQL1P_Benchmark_DSN, where the _Benchmark_DSN is also the suffix used for a monitored SQL instance.
  4. Choose Create manually (Advanced) and click Next.
  5. Choose Create data logs –> Performance counter and click Next.
  6. Click Add and specify the name of the remote computer you wish to collect from.  You need to specify \\<HOSTNAME> to a remote SQL instance.  For example:  \\SMPWPROVVSQL1P.  Click Browse.
  7. Once connected, scroll the list of available counters and specify those you wish to collect and click Add.  After you have chosen all counters you wish to collect, click OK.
  8. Change the Sample interval, if you wish, at this time.  Click Finish to complete.
  9. Right click on the newly created user defined counter name and choose Properties.  Change the Run As from SYSTEM to a secure service account.  This service account should have BULK ADMIN and write permission on the performance collector SQL database.  Click OK.

10.  Click on the newly created user defined counter name and double click on DataCollector01.  Under Log format choose SQL.  Under Data Source Name choose the System DSN which was created above.  You may also specify the sample internal time, if needed.

11.  Click Add to add any additional performance counters.  Follow steps above.  Click OK when complete.

12.  Right click on the data collector name and choose Start.  After a few moments you should see the icon for the counter change to a running state.

13.  Login to the performance collector SQL database and verify data is being logged from the SQL instance being monitored.

14.  Repeat as needed.

User Defined Performance Counter Using a Template File

You may also create a user defined performance counter collection from a template file.  This allows you to simply modify a template instead of re-entering all performance counters by hand.  This procedure assumes you are using Windows 2008 Server for your application collector box.

  1. Go to Start –> Run and type in perfmon.
  2. Open Data Collector Sets –> User Defined.  Right click on a user defined counter you wish copy and choose Save Template.  Specify the template name and location of the file.
  3. Edit the XML template file and change values such as the name of the SQL instance you are monitoring, directory folders, databases to monitor, etc.  Save the file when complete.
  4. Open Data Collector Sets –> User Defined –> right click New –> Data Collector Set.
  5. Specify a collector name.  It is recommended that you come up with a standard for all monitored SQL instance collector names.  For example:  SMPWPRSVSQL1P_Benchmark_DSN, where the _Benchmark_DSN is also the suffix used for a monitored SQL instance.
  6. Choose Create from a template (Recommended) and click Next.
  7. Choose Basic and click Browse.  Choose the XML template file you have modified.  Click Next 2 times.
  8. Change the Run As from SYSTEM to a secure service account.  This service account should have BULK ADMIN and write permission on the performance collector SQL database.  Choose Open properties for this data collector set and click Finish.  Verify everything then click OK.
  9. Click on the newly created user defined counter name and double click on DataCollector01.  Under Log format verify it is set to SQL and the correct Data source name is set.  Verify the sample internal time, if needed.

10.  Click Add to add any additional performance counters.  Follow steps above.  Click OK when complete.  You may need to provide credentials for the secure service account again, if needed.

11.  Right click on the data collector name and choose Start.  After a few moments you should see the icon for the counter change to a running state.

12.  Login to the performance collector SQL database and verify data is being logged from the SQL instance being monitored.

13.  Repeat as needed.

Configuring Performance Collector SQL Backend DB/Tables

 To collect performance counter data from SQL instances, you need to choose a SQL instance which will house the performance collector database and tables.  It is recommended that you use a centralized performance collector database server for a given data center location and/or network zone [production versus test/demo].  The database server should have some higher availability [e.g. clustered with failover nodes].

Table Layout

The performance collector SQL backend database consist of 3 core tables.

  • CounterData – This table contains the raw data collected by each of the performance counters for a given SQL instance monitored.  Based on the collection frequency, there will be 1 row for each performance counter collected for the collection.  This table will go in infinitely and needs to be pruned/maintained.

CREATE TABLE [dbo].[CounterData](

       [GUID] [uniqueidentifier] NOT NULL,

       [CounterID] [int] NOT NULL,

       [RecordIndex] [int] NOT NULL,

       [CounterDateTime] [char](24) NOT NULL,

       [CounterValue] [float] NOT NULL,

       [FirstValueA] [int] NULL,

       [FirstValueB] [int] NULL,

       [SecondValueA] [int] NULL,

       [SecondValueB] [int] NULL,

       [MultiCount] [int] NULL,

CONSTRAINT [PK_CounterData] PRIMARY KEY CLUSTERED

([GUID] ASC, [CounterID] ASC, [RecordIndex] ASC))

GO

  • CounterDetails – This table contains a list of performance counters for a given SQL instance monitored.  This table has finite growth based on the number of SQL instances monitored and the performance counters captured.

CREATE TABLE [dbo].[CounterDetails](

       [CounterID] [int] IDENTITY(1,1) NOT NULL,

       [MachineName] [varchar](1024) NOT NULL,

       [ObjectName] [varchar](1024) NOT NULL,

       [CounterName] [varchar](1024) NOT NULL,

       [CounterType] [int] NOT NULL,

       [DefaultScale] [int] NOT NULL,

       [InstanceName] [varchar](1024) NULL,

       [InstanceIndex] [int] NULL,

       [ParentName] [varchar](1024) NULL,

       [ParentObjectID] [int] NULL,

       [TimeBaseA] [int] NULL,

       [TimeBaseB] [int] NULL,

CONSTRAINT [PK_CounterDetails] PRIMARY KEY CLUSTERED

([CounterID] ASC))

GO

  • DisplayToID – Contains information, such as rows captured, for a given SQL instance monitored.

CREATE TABLE [dbo].[DisplayToID](

       [GUID] [uniqueidentifier] NOT NULL,

       [RunID] [int] NULL,

       [DisplayString] [varchar](1024) NOT NULL,

       [LogStartTime] [char](24) NULL,

       [LogStopTime] [char](24) NULL,

       [NumberOfRecords] [int] NULL,

       [MinutesToUTC] [int] NULL,

       [TimeZoneName] [char](32) NULL,

CONSTRAINT [PK_DispalyToID] PRIMARY KEY CLUSTERED

([GUID] ASC),

CONSTRAINT [UQ_DiksplayToID_DisplayString] UNIQUE NONCLUSTERED

([DisplayString] ASC))

GO

Possible Enhancements — Partitioning & Compression

Since the CounterData table contains the raw data we would wish to query and act upon, the design of the table leaves a lot to be desired.  The clustered index is on a composite primary key column [GUID,CounterID,RecordIndex] and there are not other indexes on the table by default.  Also the CounterDateTime column, which stores the time the collection took place, is stored as a CHAR(24) which makes queries very difficult since you need to convert the CHAR(24) to a DATETIME in order to perform range based queries.  There is also the fact that there is no partitioning [for maintenance of the table] or compression [to keep the database size realistic].  There is also the fact that data is inserted into the CounterData table using BULK INSERT, which makes it hard to change the core table since you can’t add/modify any columns definitions.

Given the BULK INSERT attempts to insert data into the CounterData table, there is nothing to say that CounterData can’t be a view instead.  The solution below presumes you are performance collector database is housed on a SQL 2008 Enterprise database instance.

  • Create a new DAYOFYEAR [1-366] partition scheme and function for maintenance of the table.

CREATE PARTITION FUNCTION [PF_PartitionDay](SMALLINT) AS RANGE FOR VALUES

(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79, 80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119, 120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139, 140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159, 160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179, 180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199, 200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219, 220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239, 240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259, 260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279, 280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299, 300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319, 320,321,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339, 340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359, 360,361,362,363,364,365,366)

GO

CREATE PARTITION SCHEME [PS_PartitionDay] AS PARTITION [PF_PartitionDay] ALL TO ([PRIMARY])

GO

  • Drop the current CounterData table, create a new CounterData_TABLE table and build CounterData as a view.

DROP TABLE [dbo].[CounterData]

GO

CREATE TABLE [dbo].[CounterData_TABLE](

       [GUID] [uniqueidentifier] NOT NULL,

       [CounterID] [int] NOT NULL,

       [RecordIndex] [int] NOT NULL,

       [CounterDateTime] [char](24) NOT NULL,

       [CounterValue] [float] NOT NULL,

       [FirstValueA] [int] NULL,

       [FirstValueB] [int] NULL,

       [SecondValueA] [int] NULL,

       [SecondValueB] [int] NULL,

       [MultiCount] [int] NULL,

       [EventDate] [datetime2](7) NOT NULL,

       [DayOfYear] [smallint] NOT NULL,

 CONSTRAINT [PK_CounterData_TABLE] PRIMARY KEY NONCLUSTERED

([GUID] ASC,[CounterID] ASC,[RecordIndex] ASC,[DayOfYear] ASC))

ON [PS_PartitionDay](DayOfYear)

GO

/* Add default values for new EventDate & DayOfYear columns. */

ALTER TABLE [dbo].[CounterData_TABLE] ADD  CONSTRAINT [DF_CounterData_TABLE_EventDate]  DEFAULT (GETDATE()) FOR [EventDate]

GO

ALTER TABLE [dbo].[CounterData_TABLE] ADD  CONSTRAINT [DF_CounterData_TABLE_DayOfYear]  DEFAULT (DATEPART(DAYOFYEAR,GETDATE())) FOR [DayOfYear]

GO

/* New clustered index on EventDate column */

CREATE CLUSTERED INDEX [IDX_CounterData_TABLE_EventDate] ON [dbo].[CounterData_TABLE] ([EventDate] ASC,[DayOfYear] ASC)

ON [PS_PartitionDay](DayOfYear)

GO

/* New clustered index on CounterID column */

CREATE NONCLUSTERED INDEX [IDX_CounterData_TABLE_CounterID] ON [dbo].[CounterData_TABLE] ([CounterID] ASC)

INCLUDE ([EventDate],[DayOfYear])

ON [PS_PartitionDay](DayOfYear)

GO

/* Create new CounterData VIEW. */

CREATE VIEW [dbo].[CounterData] AS

SELECT [GUID]

      ,[CounterID]

      ,[RecordIndex]

      ,[CounterDateTime]

      ,[CounterValue]

      ,[FirstValueA]

      ,[FirstValueB]

      ,[SecondValueA]

      ,[SecondValueB]

      ,[MultiCount]

FROM  [dbo].[CounterData_TABLE]

GO

  • Modify the performance collector tables for ROW compression.

ALTER INDEX ALL ON CounterData_TABLE REBUILD WITH (ONLINE=ON,DATA_COMPRESSION=ROW)

GO

ALTER INDEX ALL ON CounterDetails REBUILD WITH (ONLINE=ON,DATA_COMPRESSION=ROW)

GO

ALTER INDEX ALL ON DisplayToID REBUILD WITH (ONLINE=ON,DATA_COMPRESSION=ROW)

GO

SQL Server Performance Monitoring Through PerfMon

This is a list of Windows performance counters that my company captures to monitor, in real-time, our SQL infrastructure.  The performance counters are captured every 5 [OLTP] or 15 [non-OLTP] seconds, written to a SQL database table(s) in real-time and later viewed through automated reports [SSRS].  We create this method to monitor our SQL infrastructure because: 1)  We didn’t want to spend a lot of $$; 2) This tools comes with Windows and is available for free; and 3) No tool out there gave us the granularity of <1 minute capture and analysis.  We also setup a job to aggregate data older than 14 days to another database for historical analysis.

We capture these performance counters from a trusted Windows box which has the necessary firewall rights the SQL instances [virtual or physical].  I will go into detail, at a later time, on how to create the SQL database table(s) for capturing the data and configuring performance counters to write to the tables automatically.

LogicalDisk (Root Drive Letter or Mount Point {R:\Data1})

  • Avg. Disk Queue Length — Average number of I/O requests [read + write] waiting for disk access.
  • Avg. Disk sec/Read — Average time, in seconds, of a read of data from disk.  Measurement of read latency.
  • Avg. Disk sec/Transfer — Average time, in seconds, of a trasnfers onto the disk.  Measurement of read & write latency.
  • Avg. Disk sec/Write — Average time, in seconds, of a write of data to disk.  Measurement of write latency.
  • Disk Reads/sec — Number of reads from the disk, per second.
  • Disk Transfers/sec — Number of transfers [reads & writes] to disk, per second.
  • Disk Writes/sec — Number of writes to the disk, per second.

Memory

  • Available MBytes — Available physical memory, in MBytes, available for a given process or system use.
  • Pages/sec — Rate at which pages are read from or written to disk to resolve hard page faults.

SQLServer *or* MSSQL$<INSTANCE>

Access Method

  • Page Splits/sec — Number of page splits per second that occur as a result of overflowing index pages.

Buffer Manager

  • Buffer cache hit ratio — Percentage of pages that were found in the buffer pool without having to perform a read from disk.
  • Page life expectancy — Number of seconds a page will stay in the buffer pool without references.

Database Mirroring(_Total)

  • Log Send Queue KB — Total number of KB of log that have not been sent to the mirror server.
  • Redo Queue KB — Total number of KB that redo on the mirror database is behind the hardened log.
  • Send/Receive Ack Time — Milliseconds messages waited for acknowledgement from the partner per second.

Database(_Total) & Database(<DB_NAME>)

  • Data File(s) Size KB — Cumulative size of all the data files in the database.
  • Transactions/sec — Number of transactions started for the database.
  • Write Transactions/sec — Number of transactions which wrote to the database in the last second. <– SQL 2008 ONLY!

General Statistics

  • User Connections — Number of users connected to the SQL instance.

Latches

  • Latch Waits/sec — Number of latch requests that could not be granted immediately and had to wait before being granted.

Locks(_Total)

  • Lock Waits/sec — Number of lock requests that could not be satisfied immediately and required the caller to wait before being granted the lock.

Memory Manager

  • Memory Grants Pending — Current number of processes waiting for a workspace memory grant.

SQL Statistics

  • Batch Requests/sec— Number of batch requests received by the SQL instance, per second.
  • SQL Re-Compilation/sec — Number of SQL recompiles that occurred, per second.

SQL 2008 ONLY — Workload Group Stats(default)

  • Active requests — Number of active requests in the default workgroup.
  • Blocked tasks — Number of blocked tasks in the default workgroup.
  • CPU usage % — A method through which you can measure the CPU consumed by this SQL instance.  Helpful when stacking instances on 1 box.

Network Interface(<NIC Name>)

  • Bytes Total/sec — Rate at which bytes are sent and received over each network adapter, including framing characters.
  • Current Bandwidth — Estimate of the current bandwidth of the network interface in bits per second (BPS).

Process(sqlserver)

  • % Processor Time — Percentage of elapsed time that all of process threads used the processor to execution instructions.  The value is cumulative over all logical processors.
  • % User Time — Percentage of elapsed time that all of process threads spent executing code in user mode.  The value is cumulative over all logical processors.
  • Working Set — Set of memory pages touched recently by the threads in the process.

Processor(%Total)

  • % Processor Time — Percentage of elapsed time that the processor spends to execute a non-Idle thread.  A measurement of overall CPU pressure [up to 100%].

System

  • Context Switches/sec — Combined rate at which all processors on the computer are switched from one thread to another.
  • Processor Queue Length — Number of threads in the processor queue.  Need to divide by the number of logical processors to get actual value.

NOTE:  The firewall ports that you need opened to capture data from the trusted host are the same as those for SMB [TCP & UDP ports 135-139, 445].

Windows 2008 Clustering — My friend and yours

I wondered what I should blog about.  Since I work as a DBA who claims to know nothing but no one at my office believes me, I thought one of my first posts should be on how my company leverages Windows 2008 clustering to reduce infrastructure/SQL licensing costs.

In the past my company built out 2-node Active/Passive Windows 2003 cluster for our production SQL instances.  The problem we run into is that your Passive sits there idle, waiting to be used, and you end up double infrastructure costs to provide high SLA protection.  There was also no way to keep SQL licensing costs down

Two years ago my company implemented an HP Polyserve clustering solution for our SQL 2005 instances.  I will share my feelings on that product at another time.  Our intention was to:

  1. Build out a multi-node cluster to reduce infrastructure.
  2. Run an Active/Active/…/Passive cluster.
  3. Reduce licensing costs.

Since we run externally facing SQL Enterprise SLA instances, we wanted to be able to stack instances on a single node, as needed, and only pay the external license based on the CPUs on the box SQL is running on.  This allowed us to go from 12 CPU license for a legacy application down to a 2 CPU license, saving a substantial amount of $$.

Starting in 2009 we moved away from HP Polyserve to use the new Windows 2008 clustering solution.  We found that Windows 2008 gave us the key benefits we got from HP Polyserve with some extras:

  • Simplified SQL/SSAS 2008 install and upgrade
  • Windows 2008 cluster node management
  • Up to 16 non-heterogeneous nodes in the same cluster
  • Active/Active/…/Passive SQL environment.
  • Up to 18 virtual SQL/SSAS instances
  • Use of port 1433 for virtual SQL instances

Of course Windows 2008 clustering is not without its drawbacks [and there are many].  I know many company’s run SQL on Hyper-V or VMware but given the complexity of licensing and CPU/memory configuration, we have tried to avoid that mess.

And it begins…..

It was so long ago [14 years] when I put up my hand saying “Hey, I’ll be a DBA.”  Now I realize that it was the best/stupidest decision I have ever made.  You ever ask yourself “What if?”  What if I had not done that?  What would I have become?

 

So now I have entered the BLOG-O-SPHERE.  I have no idea what I am going to talk about.  I have no idea if anyone will even listen.  Perhaps it will be my journey into my soul.