WMI – Reporting Services Configuration Manager – Event Viewer

Background

When troubleshooting Reporting Services Configuration Manager WMI calls it can be useful to see whether WMI activities are occurring.

 

TroubleShooting

Event Viewer

Enable Log

WMI calls are logable in the Event Viewer.

But, they are not logged by default.

To enable logging please follow the steps listed below:

Obtaining WMI Events Through Event Viewer
Link

  1. Open Event Viewer. On the View menu, click Show Analytic and Debug Logs
  2. Locate the Trace channel log for WMI under Applications and Service Logs | Microsoft | Windows | WMI Activity
  3. Right-click the Trace log and select Log Properties. Click the Enable Logging check box to start the WMI event tracing
  4. WMI events appear in the event window for WMI-Activity. Double-click an event in the list to see the detailed information. You can view an event in XML View or in Friendly View format.

Logged Events

Tabulate

# Event
1 GroupOperationId = 159602; OperationId = 159602; Operation = IWbemServices::Connect; ClientMachine = QADB; User = dadeniji.adeniji; ClientProcessId = 5024; NamespaceName = \\QA\root\Microsoft\SqlServer\ReportServer
2 GroupOperationId = 159603; OperationId = 159604; Operation = Start IWbemServices::CreateInstanceEnum – __NAMESPACE; ClientMachine = QADB; User = daniel.adeniji; ClientProcessId = 5024; NamespaceName = \\.\root\Microsoft\SqlServer\ReportServer
3 GroupOperationId = 159605; OperationId = 159605; Operation = IWbemServices::Connect; ClientMachine = QADB; User = daniel.adeniji; ClientProcessId = 5024; NamespaceName = \\QADB\root\Microsoft\SqlServer\ReportServer\RS_DATACAP\v12\Admin
4 GroupOperationId = 159606; OperationId = 159607; Operation = Start IWbemServices::CreateInstanceEnum – MSReportServer_ConfigurationSetting; ClientMachine = QADB; User = daniel.adeniji; ClientProcessId = 5024; NamespaceName = \\.\root\Microsoft\SqlServer\ReportServer\RS_DATACAP\v12\Admin
5 ProviderInfo for GroupOperationId = 159606; Operation = Provider::CreateInstanceEnum – MSReportServer_ConfigurationSetting; ProviderName = ReportingServicesWMIProvider; ProviderGuid = {0A0B6A3E-DAA2-4ED9-A603-B1C4ED9515FF}; Path = C:\Program Files (x86)\Microsoft SQL Server\120\Shared\reportingserviceswmiprovider.dll

 

Explanation

  1. Event #1 :- IWebServices Create Instance Enum
    • NamespaceName :- \\QA\root\Microsoft\SqlServer\ReportServer
  2. Event #2 :- IWebServices Create Instance Enum
    • NamespaceName :- \\.\root\Microsoft\SqlServer\ReportServer
  3. Event #3 :- IWebServices Connect
    • NamespaceName :- \\.\root\Microsoft\SqlServer\ReportServer\RS_DATACAP\v12\Admin
  4. Event #4 :- IWebServices Instance Enumerate
    • NamespaceName :- \\.\root\Microsoft\SqlServer\ReportServer\RS_DATACAP\v12\Admin
  5. Event #5 :- Com Object Instantiated
    • COM Object ID :- 0A0B6A3E-DAA2-4ED9-A603-B1C4ED9515FF
    • COM File :- C:\Program Files (x86)\Microsoft SQL Server\120\Shared\reportingserviceswmiprovider.dll

 

Summary

From logging WMI Calls we are able to see the inner workings of the WMI Provider class and IWbemServices interface.

In the case of Sql Server Reporting Services (SSRS) it is an version specific dll ( C:\Program Files (x86)\Microsoft SQL Server\120\Shared\reportingserviceswmiprovider.dll ).

Microsoft Access – Querying SQL Server Table – Schema Stability Lock

Background

A quick follow-up to a post over the weekend.

The forwarding post is titled “SQL Server – Index Rebuild – Blocked / Blocking” and it is here.

In that post we spoke of how a scheduled Business Back Office Job was hung and could not proceed.

Using Adam Mechanic’s sp_whoIsActive, we discovered we had were indeed experiencing session blocking, but not an actual deadlock, which in fact would have triggered a vote as to which session to abort

We stopped the blocking database maintenance job which is an Index Defrag job.  The Index Defrag job simply calls Ola Hallengren’s IndexOptimize Stored Procedure.

TroubleShooting

TroubleShooting – Day 1

Adam Machanic

dbo.sp_WhoIsActive

Code

exec  [dbo].[sp_WhoIsActive]

Image
Session

SQL Text

Explanation
  1. Sessions
    • Login :- app
      • We are familiar with the app account
      • The sessions are the bottom two
    • SQL :- OpenRowSet
      • A SQL Server Profiler Trace we initiated to track the ongoings
    • Status :- suspended // wait_info :- ASYNC_NETWORK_IO
      • used_memory
        • 10, 000 KB ( 10 MB)

 

TroubleShooting – Day 2

Microsoft

Dynamic Management Views

sys.dm_exec_sessions
SQL

declare @appNameMSFTODBC sysname
declare @appNameMSFTOffice sysname
declare @appNameMSFTSSMS sysname
declare @appNameMSFTJavaJDBC sysname

declare @clienInterfaceName sysname

set @appNameMSFTODBC = 'Microsoft® Windows® Operating System'
set @appNameMSFTOffice = 'Office'

set @appNameMSFTSSMS = 'Microsoft SQL Server Management Studio - Query'
set @appNameMSFTJavaJDBC = 'Microsoft JDBC Driver for SQL Server'

set @clienInterfaceName = 'Microsoft JDBC Driver 4.0'

select 
		  tblDES.session_id
		, tblDES.program_name
		, tblDES.transaction_isolation_level
		, tblDES.open_transaction_count
		, tblDES.host_name
		, tblDES.client_interface_name
		, tblDES.client_version
		, tblDES.[status]
		, tblDES.[row_count]
		, tblDES.[prev_error]
		, tblDES.reads
		, tblDES.last_request_start_time
		, tblDES.last_request_end_time
		, [timeSinceLastCommunicationInMinutes]
			= datediff
				(
					  minute
					, tblDES.last_request_end_time
					, getdate()
				)

from  sys.dm_exec_sessions tblDES

where  tblDES.session_id >= 50

and	   (
			(
						
				   ( tblDES.program_name like @appNameMSFTODBC )
				or ( tblDES.program_name like '%' + @appNameMSFTOffice + '%' )

			)
			and
			(

				       ( tblDES.program_name != @appNameMSFTSSMS )
				   and ( tblDES.program_name != @appNameMSFTJavaJDBC )
			)

	   )


Output

 

Microsoft SQL Server Profiler

Image

Explanation

Here is the conversation captured:

  1. SQL:BatchStarting
    • SELECT Config, nValue FROM MSysConf
  2. SQL:BatchStarting
    • SELECT “dbo”.”oe_dep_audit”.”oe_dep_emp_location”,”dbo”.”oe_dep_audit”.”oe_dep_emp_ssn”,”dbo”.”oe_dep_audit”.”oe_year”,”dbo”.”oe_dep_audit”.”oe_dep_no”,”dbo”.”oe_dep_audit”.”oe_dep_session_ID”,”dbo”.”oe_dep_audit”.”oe_dep_record_type”,”dbo”.”oe_dep_audit”.”oe_dep_record_flag” FROM “dbo”.”oe_dep_audit”
      • Gets all the records in table
  3. SQL:BatchStarting
    • SELECT CASE DATABASEPROPERTYEX( DB_NAME(), ‘Updateability’) WHEN ‘READ_ONLY’ THEN ‘Y’ ELSE ‘N’ END
  4. RPC:Completed
    • SQL
      • declare @p1 int
        set @p1=1
        exec sp_prepexec @p1 output,N’@P1 char(2),@P2 char(9),@P3 char(4),@P4 int,@P5 char(10),@P6 char(1),@P7 char(1)’,N’SELECT “oe_dep_emp_location”,”oe_dep_emp_ssn”,”oe_year”,”oe_dep_no”,”oe_dep_session_ID”,”oe_dep_record_type”,”oe_dep_record_flag”,”oe_dep_name”,”oe_dep_birthdate”,”oe_dep_relationship”,”oe_dep_SSN”,”oe_dep_sex”,”oe_dep_disabled”,”oe_dep_medical”,”oe_dep_dental”,”oe_dep_optical”,”oe_dep_legal”,”oe_dep_PCP”,”oe_dep_current_patient”,”oe_dep_deleted” FROM “dbo”.”oe_dep_audit” WHERE “oe_dep_emp_location” = @P1 AND “oe_dep_emp_ssn” = @P2 AND “oe_year” = @P3 AND “oe_dep_no” = @P4 AND “oe_dep_session_ID” = @P5 AND “oe_dep_record_type” = @P6 AND “oe_dep_record_flag” = @P7′,’01’,’000000000′,’2012′,1,’a733167067′,’B’,’ ‘
        select @p1
    • Prepares a fetch Statement
  5. RPC:Completed
    • SQL
      • exec sp_execute 2,’01’,’00140′,’2013′,3,’a08938′,’B’,’ ‘,’01’,’00140′,’2013′,3,’a08938′,’O’,’A’,’01’,’00140′,’2013′,3,’a57784′,’O’,’A’,’01’,’00140′,’2013′,3,’a94593′,’O’,’ ‘,’01’,’00140′,’2013′,4,’a08938′,’B’,’ ‘,’01’,’00140′,’2013′,4,’a08938′,’O’,’A’,’01’,’00140′,’2013′,4,’a577848305′,’O’,’A’,’01’,’0014′,’2013′,4,’a94593′,’O’,’ ‘,’01’,’00154′,’2005′,100,’R50001′,’O’,’ ‘,’01’,’00154′,’2006′,1,’N6000′,’B’,’ ‘

 

Microsoft Network Monitor

Filter

//IP Address
(

    ( IPv4.Address == 10.1.20.182 )

)
and 
(

	(
          not ( Conversation.ProcessName == "Ssms.exe")
    )

	and
        ( 
             not ( Conversation.ProcessName == "PROFILER.exe")
        )
)

Traffic
Image

Explanation
  1. Using a Network monitor tool we can see that there is quite a bit of ongoing Network Activity between the client node running MS Access and the Database Server
  2. The protocols are plain TCP and TDP
  3. And, the ports are the default SQL Server Port of 1433 and the ephemeral ports from the Source Node

 

Summary

When the table queried from MS Access is reasonably large, the database connection is kept opened.  And, the DB table is locked with an object stability lock.

It is a designed behavior by MS Access and it reduces the amount of local resources on the client host.

SQL Server Reporting Services (SSRS) – Error – “Invalid Class”

Background

As part of a much larger work done that we are undertaken, got swallowed into a Windows Management Instrumentation ( WMI ) quicksand.

 

Manifestation

The errors stands up upon launching SQL Server Reporting Services ( SSRS ) and trying to connect to one of the local instances.

 

Error

Report Services Configuration Connection

Launch

Image

SSRS_Connect_20170816_0744PM

 

Explanation

  1. Report Server Instance dropdown
    • empty & greyed out
  2. Connect button
    • disabled

Find

Upon clicking the find button

Image

InvalidClass_20170816_0307PM

 

Explanation

  1. When we clicked the find button to “discover” SSRS Instances on the entered host, we received the error message “Invalid class

 

Troubleshooting

 

WMI Explorer

WMI Explorer allows us to simulate the same WMI queries made by the Application.

 

Clarification

  1. We have 2 SQL Server Instances on the box
    • A named instance, Datacap
    • A default instance
  2. Instances
    • Instance – Datacap
      • Upgrade History :- Recently upgraded
      • Edition :- Enterprise Edition :- Core-Based Licensing
    • Instance – Default
      • Upgrade History – Left as is

 

Images

The screen shot below is what we captured as we navigated the Namespace.

 

Version – DATACAP – v10

 

wmiExplorer_DataCap_v10__20170816_0711PM

Explanation
  1. Tab :- Instances ( 0 )

 

Version – DATACAP – v12

wmiExplorer_DataCap_20170816_0705PM

 

Explanation
  1. Tab :- Instances ( 1 )
    • Instances :- MSReportServer_Instance.Instance Name=’DATACAP’
    • Edition :- Enterprise Edition : Core-Base Licensing
    • InstanceID  :- MSR12.DATACAP
    • IsSharePointIntegrated :- False

 

Version – DEFAULT – v10

wmiExplorer_DefaultInstance_v10DOT15_20170816_0711PM [BrushedUp]

Explanation
  1. Tab :- Instances ( 1 )
    • Instances :- MSReportServer_Instance.Instance Name=’MSSQLSERVER’
    • Edition :- Enterprise Edition
    • InstanceID  :- MSR10_15.MSSQLSERVER
    • IsSharePointIntegrated :- False

 

Summary

Admittedly it is difficult to fully tell of our experiences from a single tool along with a couple of screenshots.

Yet in summary it seems Report Services Configuration Manager is crippled due to one missing instance.

Later we will come back and talk about other diagnostics tools one can use to debug WMI difficulties.

Visual Studio 2010 – Upgrade from RTM to SP1

Background

Just as yourself, got a lot to do, but occasionally get pulled back in.

 

SQL Server 2008-R2 Upgrade

My manager has signed us up to upgrade all SQL Servers to the most recent version, 2016. Or at minimum the version that precedes the latest; btw, that is 2014.

The SQL Instance that I am looking at currently is v2008-R2 and the OS is 2008/R2.

That latest SQL Server supported on Windows 2008-R2 is SQL Server 2014.

 

Upgrade 2008-R2 to 2014

Trying to upgrade v2008-R2 to v2014, but ran into a constraint right away.

Features Rules

Textual

This computer has an installation of Visual Studio 2010 that requires a Service Pack 1 update that is needed for a successful installation of SQL Server based on your feature selection.

To continue, install the required Visual Studio 2010 Service Pack 1 from SQL Server media or from http://go.microsoft.com/fwlink/?LinkID=220649.

Image

Download Visual Studio 2010 SP1

Tried a drive by visiting https://www.microsoft.com/en-us/download/details.aspx?id=23691.

Redirected to https://www.microsoft.com/en-us/download/details.aspx?id=23691.

Download Visual Studio 2010 SP1 – Download 23691

Textual

We are sorry, this download is no longer available.

Image

 

Download Links

Source Link Works
go.microsoft.com/fwlink/?LinkID=220649 Link No
http://www.microsoft.com/en-us/download/details.aspx?id=23691 Link No
download.microsoft.com/download/E/B/A/EBA0A152-F426-47E6-9E3F-EFB686E3CA20/VS2010SP1dvd1.iso  Link  No
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=75568aa6-8107-475d-948a-ef22627e57a5&displaylang=en Link  No
 my.visualstudio.com/Downloads?q=visual%20studio%202010%20service%20pack%201  ( All ) Link  Yes
my.visualstudio.com/Downloads?pid=2300 ( Visual Studio 2010 Service Pack 1 ) Link  Yes

 

Downloaded

Please download the online installer availed here.

BTW the link noted above is from my.visualstudio.com web site.

And, you need a registered user.

If you have yet to register for my.visualstudio.com, please do so.

It is a free.

 

Downloaded Results

Here are results that matches “Visual Studio Service Pack 1

Downloaded

Visual Studio 2010 Service Pack 1 – Details

File Specification

The file’s name is mu_visual_studio_2010_sp1_web_installer_x86_651694.exe.

It is a relatively small file, 795 KB.

It is an online installer and it is a bootstrap as it simply connects to the website and downloads the actual install media.

 

Install Visual Studio 2010 – SP1

Screen Shot

Welcome

Microsoft Visual Studio 2010 Service Pack 1 Setup

Installation Progress

Installation Is Complete

SQL Server – Index Rebuild – Blocked / Blocking

Background

Got a call about a hung database job.

 

Check Current Sessions

sp_whoIsActive

Code


exec sp_whoIsActive

Output

Explanation

  1. Session ID :- 161
    • Alter Index Session
    • Being blocked by Session ID :148
  2. Session ID :- 148
    • Select Statement

 

TroubleShooting

Why Is Select Blocking?

sp_block

Let us issue sp_block against the blocker and blocked sessions.

Code


-- exec sp_help sp_lock
declare @spidIndexOptimize int
declare @spidBlocker int

set @spidIndexOptimize = 161
set @spidBlocker = 148

exec sp_lock
		  @spid1 = @spidIndexOptimize 
		
exec sp_lock
		  @spid1 = @spidBlocker

 

Output

 

Explanation

  • Session ID :- 161
    • Schema Stability
    • Schema Modification
  • Session ID :- 148
    • Schema Stability

 

Why Is Index Reorg / Rebuilt?

sysindexes

Let us see how many records we have and how many records have changed.

Code


select 
		  [table] = 
					  object_schema_name(tblSI.id)
					+ '.'
					+ object_name(tblSI.id)
		, tblSI.indid
		, tblSI.[name]
		, tblSI.[rowcnt]
		, tblSI.[rowmodctr]
from   sysindexes tblSI
where  tblSI.indid In ( 0, 1)
and    tblSI.id = object_id('dbo.event_log_bkp')

;

 

sys.dm_db_index_physical_stats

Fragmentation %

Code


use [rbpivr1]
go

DECLARE @db_id SMALLINT;  
DECLARE @object_id INT;  

SET @db_id = DB_ID(N'rbpivr1');  
SET @object_id = OBJECT_ID('dbo.event_log_bkp');  

IF @db_id IS NULL  
BEGIN;  
    PRINT N'Invalid database';  
END;  
ELSE IF @object_id IS NULL  
BEGIN;  
    PRINT N'Invalid object';  
END;  
ELSE  
BEGIN

	SELECT
			  tblSI.[name]
			--, tblSI.type_desc
			, tblSI.[index_id]
			, tblDIPS.index_type_desc
			, tblDIPS.page_count
			--, tblDIPS.record_count
			--, tblDIPS.*
			, tblDIPS.avg_fragmentation_in_percent 

	FROM   sys.dm_db_index_physical_stats
			(
				  @db_id
				, @object_id
				, NULL
				, NULL 
				, 'LIMITED'
			) tblDIPS

	inner join sys.indexes tblSI
		on   tblDIPS.[object_id] = tblSI.[object_id]
		and  tblDIPS.[index_id] = tblSI.[index_id]
			;  
END;

Output

Explanation

It appears that a couple of indexes are eligible for re-org and others for a rebuild.

Remediation

Handle Blocking

Documentation

Found out that Olla Hallengren’s code has a built-in mechanism for handling blocking.

The documentation is here.

Image

Explanation

  1. WaitAtLowPriorityMaxDuration
    • How long to wait in minutes
  2. WaitAtLowPriorityAbortAfterWait
    • Options
      • NONE
        • Continue waiting for Locks
        • Default Option
      • SELF
        • Abort the online index rebuild operation.
        • Terminate the Index Rebuild Option
      • BLOCKERS
        • Kill user transactions that block the online index rebuild operation.
        • Terminate the Blocker

 

Choice

Our choice, when blocked, will be to wait 10 minutes and terminate the Index Rebuild Option

Code


EXECUTE [AdminDB].dbo.IndexOptimize
	  @Databases = 'USER_DATABASES'
	, @FragmentationLow = NULL
	, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'
	, @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'
	, @FragmentationLevel1 = 5
	, @FragmentationLevel2 = 30
	, @UpdateStatistics = 'ALL'
	, @OnlyModifiedStatistics = 'Y'
	, @LogToTable = 'Y'
	--20170813 9:17 PM dadenji
	, @WaitAtLowPriorityMaxDuration=10
	, @WaitAtLowPriorityAbortAfterWait='SELF'

Generated Code

Here is the code that is generated when we make the change outlined above:

 

References

  1. Michael J Swart ( Database Whisper )
    • The Sch-M Lock is Evil
      Link

SSRS – Validating Smart Host – Using Mozilla Thunderbird

Background

A few posts ago, we provisioned a Smart Host.

Let us quickly test it out.

 

Lineage

Here are other posts in this multi-series:

  1. SSRS – Setting up Smart Host
    Link

Scenario

To make sure that we understand the specificity of Microsoft’s Office 365 SMTP service, we will install a free, durable email client; specifically Mozilla Thunderbird.

 

Wireshark

Filter

  1. tcp.port == 25 || tcp.port == 465 || tcp.port == 587

Mozilla Thunderbird

Download

Download Mozilla Thunderbird from here.

Installation

Installation is straightforward.

Configuration

Images

Session – 1

Configuration
SMTP Server Settings

 

Output
Netstat

WireShark

 

Session – 2

Configuration
SMTP Server Settings

Output
Netstat

WireShark

WireShark – SMTP ( Destination Port 587 ) – 01
WireShark – Explanation
  1. Connection to SMTP Host
WireShark – SMTP ( Destination Port 587 ) – 02

WireShark – Explanation
  1. Reply from SMTP Host

 

Session – 3

Configuration
SMTP Server Settings

WireShark

WireShark – Explanation
  1. We see a fuller conversation

 

Item Port :- 465 /
Connection :- SSL/TLS
Port :- 567 /
Connection Start :- SSL/TLS
Port :- 567 /
Connection Start :- STARTSSL
Server Name smtp.office365.com smtp.office365.com smtp.office365.com
Port 465 587 587
Connection Security SSL/TLS SSL/TLS STARTSSL
Authentication Password Normal Password Normal Password Normal Password
Results SYN/SENT  Truncated Conversation  Full Conversation

 

Network Ports

  1. Port :- 25
    • SMTP
      • Server to Server email
        • Message relay port
      • (-)
        • Not Authenticated
        • Blocked by a lot of firewall
  2. Port :- 465
    • SMTP
      • (-)
        • Not widely adopted
  3. Port 587
    • SMTP
      • Mail Submission Port
      •  (+)
        • Authenticated emails

 

References

  1. Microsoft
    • Office
      • Support
        • POP and IMAP settings for Outlook Office 365 for business
          Link
        • How to set up a multifunction device or application to send email using Office 365
          Link
    • technet
      • blogs.technet.com
        • Andrew Stobart – Useful Wireshark Filters for Mail Flow Troubleshooting
          Link
  2. Mozilla.Org
    • Mozilla Support
      • cannot send mail. Connected to smtp.office365.com but times out.
        Link
  3. Stanford University
    • Stanford | University IT
      • How to Configure Thunderbird for Office 365 Using IMAP
        Link
  4. StackOverflow
    • Network Ports
      • What is the difference between ports 465 and 587?
        Link
  5. JSCAPE
    • Managed File Transfer and Network Solutions
      • John Carl Villanueva
        • Still Confused With SMTP Ports? Read This
          Link
  6. FastMail
    • SSL vs TLS vs STARTTLS
      Link