Updated MP: SQL Server

The SQL MP which had new version just last month now is updated again. The previous release had some bugs which were addressed and fixed quickly:

  • Fixed CPU Utilization Monitor
  • Fixed SQL Server seed discovery for WoW64 environments
  • Alert severity of Average Wait Time Monitor was changed to Warning, added consecutive sampling to reduce noise, threshold was changed to 250
  • Alert severity of SQL Re-Compilation monitor was changed to Warning, threshold was changed to 25. The monitor was disabled by default.
  • Minor fixes

Great job by the team behind the MP for fixing those bugs quickly. The new version you can find here.

Updated MP: SQL Server

The SQL Server MP has reached version introducing the following changes:

  • New Dashboard for SQL Server 2012 DB
  • New Monitors and Rules – only for SQL 2008 and SQL 2012
  • o Collect DB Active Connections count

    o Collect DB Active Requests count

    o Collect DB Active Sessions count

    o Collect DB Active Transactions count

    o Collect DB Engine Thread count

    o Thread Count monitor

    o Transaction Log Free Space (%) monitor

    o Transaction Log Free Space (%) collection

    o Collect DB Engine CPU Utilization (%)

    o CPU Utilization (%) monitor for DB engine

    o Buffer Cache Hit Ratio monitor

    o Collect DB Engine Page Life Expectancy (s)

    o Page Life Expectancy monitor

    o Collect DB Disk Read Latency (ms)

    o Collect DB Disk Write Latency (ms)

    o Disk Read Latency monitor

    o Disk Write Latency monitor

    o Collect DB Transactions per second count

    o Collect DB Engine Average Wait Time (ms)

    o Average Wait Time monitor

    o Collect DB Engine Stolen Server Memory (MB)

    o Stolen Server Memory monitor

    o Collect DB Allocated Free Space (MB)

    o Collect DB Used Space (MB)

    o Collect DB Disk Free Space (MB)

    o SQL Re-Compilation monitor

  • SPN monitor improved
  • Support for special symbols in DB names.
  • Improved AlwaysOn seed discovery
  • Run As configuration changes to support Low privilege for SQL Server 2012 Cluster
  • Improved performance of AlwaysOn discovery
  • Custom User Policy Discovery and Monitoring performance optimization
  • Hided AG health object from Diagram view
  • Minor changes

The information is taken directly from the guide. It seems changes in the MP are a lot especially the SQL dashboard that was waited since it was shown at MMS 2013 earlier this year. Grab the latest version from here and start implementing it first on your test and development environments.

SQL Analysis Services: “The Cluster Resource ‘Analysis Services (Instance_Name) Could Not Be Brought Up” and “No Connection Could Be Made Because The Target Machine Actively Refused it IP:Port”

If you look at the title you probably think I’ve switched to the dark side Smile and from System Center Specialist I’ve become SQL Specialist Smile. Nope it is not that. If you are dealing with System Center you know the clash with SQL is inevitable. Sooner or later you have to learn a thing or two in SQL. So I’ve been playing these days with installing SQL instances for System Center on a cluster and I’ve bumped into two issues:

  • The Cluster Resource ‘Analysis Services (Instance_Name) Could Not Be Brought Up

I’ve received this error during the installation of SQL instance with Analysis Services on the first node. The error is received at the end of the installation when the cluster role is being brought online.

Reason for the error: Analysis Services files during the setup are set to a location that is different than the default one. Because of that it seems permissions to the folder/s where Analysis Services are put are not set during installation.

Resolution: To all folders where Analysis Services is placed add full NTFS permissions for the Analysis Services service account.

  • No Connection Could Be Made Because The Target Machine Actively Refused it IP:Port

I’ve received this error after installing SQL instance with Analysis services on the first node. In order to check if everything is OK with Analysis Services I’ve started up SQL Management Studio and tried to connect to the SSAS instance but I’ve received the error above. To troubleshoot this error I’ve tried telnet to the IP and the Port but no connection was made.

Reason for the error: SQL Analysis Services sets port 0 during the installation which means dynamic port.

Resolution. In order to be able to connect I’ve set a static port to the SSAS instance. You cannot do that trough SQL Management Studio so you have to find a file named msmdsrv.ini. This file is located in folder named Config of the root directory of where you’ve installed your SSAS instance. Open the file and edit the line <Port>0</Port> by replacing 0 with a port of your desire.

I’ve only seen these error when I have clustered instances. As you know SSAS instance can be required for SCOM or SCSM.

Database as a Service Reference Architecture Guide

Database as a Service is not one of the most popular services but for sure it is gathering more and more usage. It is difficult service to design and built so for sure this architecture guide from Microsoft can help a lot.

New Community MP: SQL File Disk Performance Management Pack for SCOM

This community MP is written by  Bob Cornelissen, David Scheltens, and Pavel Dzemyantsau.

Thank you guys.

In short what this MP offers:

Starting SQL 2005 SQL DBA’s have the possibility to use Dynamic Management Views and Functions to monitor the health of a server instance, diagnose problems, and tune performance. This management pack is specifically geared towards the sys.dm_io_virtual_file_stats view, which returns I/O statistics for data and log files. The view is used by DBA’s to view current counter values for each database file (data or log). It is a current point in time and these values are cumulative values since the last restart of the server. This management pack collects these values for use in viewing and reporting. There are no health state changes or alerts connected to these values. There are short time performance views and two reports with double Y axis to display longer term data. The long term data is translated from cumulative data into amongst others IOPS for each point in time.

More information about the MP and download you can find at TechNet Gallery.

Once again thanks to the creators for sharing this MP.