SCOM: Sql Server Management Pack 6.6.0.0 released

The SQL Server Management Pack has been refreshed with all new dashboards, which look to be fully extensible and designed for larger server farms. Full details on how to configure the dashboards in the SQLServerDashboards.docx guide provided at the download links.

The new dashboards do look sexy though with these screen shots taken from the guides.

Version 6.6.0.0 can be downloaded at

SQL 2005-2012: https://www.microsoft.com/en-us/download/details.aspx?id=10631
SQL 2014: http://www.microsoft.com/en-us/download/details.aspx?id=42573

Again check out the specific guides provided on implementing these dashboards.


Legal Stuff: As always the contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.

Announcing the release of my SQL Server DSC Resource Module mlSqlPs on PowerShellGallery and Github

I've been automating the installation of SQL Server components for 10+ years now so it's no surprise I am a fan of PowerShell and Desired State Configuration (DSC). Given my background in large, high volume, and diverse environments, the main objective I always think about is how the process can be easily replicated time and time again but also easily adapted to fit the needs of diverse configurations.

Unfortunately to date I have found the xSqlPs DSC module falls just short of these needs, but that was also the intent of the DSC Resource Kit Waves so that it would provide the "starting blocks" for organizations to customize and extend the resources. I had already started talking with the team about how to improve the SQL resources, but now that the DSC Resource Kit modules has been made open source on github this opens the door for the community to contribute into the standard set of resources. All changes I mention below are pending pull requests back into the main branch of the xSqlPs resource on Github.

The PowerShell Team is also working on their nuget gallery at PowerShellGallery.com, which is going to greatly assist with the new PowerShellGet feature in PowerShell 5.0. The gallery is still in preview however I am starting to contribute to it and this module will be available there (link below).



To provide these features to everyone straight away I have also launched my own version of the SQL DSC module called mlSqlPs. This will allow me to get changes out quickly, while also working to merge the best of my work back into the main branch.

Version 1.0.0.0 of mlSqlPs DSC Module provides the following resources and enhancements.

• xSqlServerInstall - Enhanced
  Installs SQL Enterprise on target machine.
  This resource has been enhanced to provide capabilities for aligning to SQL best practices (e.g. Data, Log, TempDb paths)
• xSqlHAService
  Enables SQL high availability (HA) service on a given SQL instance.
• xSqlHAEndpoint
  Configures the given instance of SQL high availability service to listen port 5022 with given name, and assigns users that are allowed to communicate through the SQL endpoint.
• xSqlHAGroupconfigures
  An SQL HA group. If the HA group does not exist it will create one with the given name on given SQL instance and add the HA group database(s) to local SQL instance.
• xWaitForSqlHAGroup
  Waits for an SQL HA group to be ready by checking the state of the HA group of a given name in a given interval till either the HA group is discoverable or the number of retries reached its maximum.
• xSqlAlias - New
  Configures Client Aliases in both native and wow6432node paths. Supports both tcp and named pipe protocols.

The module contains a help file in both HTML and MD format, as well as some Sample files.

You can get the module at the following locations:

PowerShell Gallery
https://www.powershellgallery.com/packages/mlSqlPs/

GitHub - Want to contribute let me know
https://github.com/matticusau/mlSqlPs


Are you running Windows PowerShell 5.0? You can also get the module with the new Install-Module cmdlet.

#Search for the module on the default gallery
Find-Module -Name mlSqlPs -Repository PSGallery;

#Install the module from the gallery
Install-Module -Name mlSqlPs -Repository PSGallery;

#Get the list of resources
Get-DscResource -Module mlSqlPs;

And there you go, create your configuration, push or pull the mof to the node and watch the magic.


Keep checking the GitHub repository and PowerShell Gallery for updates on this module.


Legal Stuff: As always the contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.

SCOM: Fixing a blank report list in Ops Mgr Console

Something I came across in SCOM 2012 R2 is that when users try and view Reports from the OpsMgr console wunderbar they could receive a blank list.

Possible Cause #1 - Incorrect SSRS file system permissions

One thing that can impact this is incorrect file system permissions on the SSRS installation location.

1. On the server where the SSRS instance is installed, navigate to the SSRS install folder. Depending on your environment this could be the default of the following or a custom drive:

C:\Program Files\Microsoft SQL Server\MSRS.\Reporting Services

Where is the numerical version of SQL (e.g. 11) and is the instance name. Such as \MSRS11.MSSQLSERVER\Reporting Services
 

In my lab I customised this to the root of a partition like:

D:\MSRS11.MSSQLSERVER\Reporting Services

2. Open the properties of /ReportManager and /ReportServer separately

3. Ensure that a group which would contain the Windows Accounts of the users trying to access the reports has Read & Execute permissions to the relevant folders.

This is from my lab where it is the default, you may choose to secure this back to a group(s) containing just the users granted rights to your reports but you need to ensure these permissions exist.



Possible Cause #2 - Corrupted identities
If you face this issue are you find that some users, or even just the SCOM Administrator, can access the reports but others cannot. This could be following a restore of the "Reporting" component or even a restore/migration/upgrade of the whole SCOM environment.

The key to understand the cause of this is to look at how both SCOM and SSRS configure security.

First access the Administrator section from the OpsMgr Console wunderbar, and view the User Roles. Open the properties of the Report Operator User Role which contains the users who receive a blank list. Click on the Identity tab.

Take note of the ID which is displayed. Copy this to the clipboard.... you will need it ;)

Now navigate to your Report Server url (e.g. http://localhost/Reports).
Click on the Folder Settings button from the home screen.

Here you will be shown the security of the reports. Check if the ID from earlier is shown, chances are your list will contain a different set of IDs.

IMPORTANT: Do not remove any role assignments

Click the "New Role Assignment" button.

Enter the ID into the "Group of user name" and select Browser, My Reports, and Report Builder roles.

Click OK

The security assignments should now contain the ID of your User Role from OpsMgr.

Now if you have your user refresh the Reports in Ops Mgr Console they should have access to the reports. At least that was the solution I found in my lab.

If you find other causes let me know :)


Reference articles/blogs:
Here are some other articles I have since found which provide troubleshooting guidance on this topic as well for your reference.

http://thoughtsonopsmgr.blogspot.com.au/2011/09/empty-reports-in-scom-whats-happening.html
http://thoughtsonopsmgr.blogspot.ch/2010/08/how-to-scope-scom-r2-reports-for.html





Legal Stuff: As always the contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.