Posted on

Reporting services database

Reporting Service databases are used to store objects definitions and their metadata. There are placed two databases – Reporting Services Database and Reporting Services Temporary database. Each instance of reporting services in native mode should have defined these two databases in configuration manager. See post Reporting Services, Basic configuration tips .

Reporting Services database

  • Here is stored information regarding reporting services repository like reports, schedules, data sources users etc. Bellow few tables you may interested in.

Reporting services temporary database

  • Here are stored data used for caching reports

The reporting services database structure is more than clear, based on name of tables. Even so I mention two tables you may be interested in. But as Microsoft states you shouldn’t use your custom queries directly against reporting services database. You should use API to get info you need, except of execution log you can access directly from database. See Microsoft web site for more info https://bit.ly/2Jxn9J6.

  • Catalog table – report server items – reports and theirs pats for example
    • here you can find Report Definitions too. But these data are informative only. Do not expect that you if you change report definition here it changes on Report Server too. For such a purpose you should use Reporting Web Services endpoints or Reporting Services Extensions or hack Chunk and Segment tables where are stored the data reporting services work with in binary form
  • ExecutionLogStorage – information regarding repots rendering, processing and data retrieving.
    • Here are few views pre-pared from Microsoft team to audit reporting services data
    • Dbo.ExecutionLog, dbo.ExecutionLog2, dbo.ExecutionLog3

In next few posts I would like to show you work how you can work with Reporting Service tables since it is good tool how to monitor performance of reports and auditing users access to report serves. Stay in touch.

Posted on

SSRS advanced configuration

In this post we take a quick overview on Reporting Services configuration.

There are few configuration sections of Reporting Services based on what part of functionality you would like to control. Here are briefly described fundamental configuration settings with short description.

    • Reporting Services Configuration manager
      • Configuration tool for basic settings of Report Server (Report Service URL, Report Manger URL, Reporting Services state, etc…)
    • Connect to Reporting Services via Management Studio
      • Setting of Execution log expiration, Reporting Services Report Manager text etc.
    • Reporting Services Installation directory
        • Report manager directory C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportManager
          • rsmgrpolicy.config
            • Report Manager policy configuration file.
        • Report Server installation directory C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer
          • rsreportserver.config – Stores configuration settings for feature areas of the Report Server service
            • orting Services URL, Report Manager URL, Extension Configuration (e.a. csv extension delimiter), new Reporting Services Extensions adding, etc.
              <Extension Name="Report Server Email" Type="Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider,ReportingServicesEmailDeliveryProvider"> <MaxRetries>3</MaxRetries> <SecondsBeforeRetry>900</SecondsBeforeRetry> <Configuration> 
<RSEmailDPConfiguration> <SMTPServer>smtpserver.cz</SMTPServer> <SMTPServerPort> </SMTPServerPort> <SMTPAccountName> </SMTPAccountName> <SMTPConnectionTimeout> </SMTPConnectionTimeout> <SMTPServerPickupDirectory> </SMTPServerPickupDirectory> <SMTPUseSSL> </SMTPUseSSL> <SendUsing>2</SendUsing> <SMTPAuthenticate> </SMTPAuthenticate> <From>DWHReports@email.cz</From> <EmbeddedRenderFormats> <RenderingExtension>MHTML</RenderingExtension> </EmbeddedRenderFormats> <PrivilegedUserRenderFormats> </PrivilegedUserRenderFormats> <ExcludedRenderFormats> <RenderingExtension>HTMLOWC</RenderingExtension> <RenderingExtension>NULL</RenderingExtension> <RenderingExtension>RGDI</RenderingExtension> </ExcludedRenderFormats> <SendEmailToUserAlias>True</SendEmailToUserAlias> <DefaultHostName> </DefaultHostName> <PermittedHosts> </PermittedHosts> </RSEmailDPConfiguration> </Configuration> </Extension>
        • web.config – ASP.NET configuration
          • here you can for example change max limit for file upload for example
          <httpRuntime  maxRequestLength="100000" executionTimeout="9000" requestValidationMode="2.0" />
        • rssrvpolicy.config
          • Stores the code access security policies for the server extensions.
            • To set security to extension library
        <CodeGroup class="UnionCodeGroup" version="1" Name="SSRSCustomReportExtension" Description="" PermissionSetName="FullTrust"><IMembershipCondition class="UrlMembershipCondition" version="1" Url="C:\Program Files\Microsoft SQL Server\MSRS10.MSSQL2008\Reporting Services\ReportServer\bin\SSRSCustomReportExtension.dll"/>

        You can find more info on MSDN web site https://bit.ly/2ZkDZ2l.

Posted on

Extended events advanced

 I would like to describe useful queries for extended events in more detail in this post Extended events get description. I will follow my previous post where I put few basic queries to get some extended events info. 

Extended events are triggers occurring in SQL system. Each extended event has its own set of properties -data you get from result. Extended events are collected in packages, events can be combined from different packages. List of packages defined in SQL server instance  

SELECT * FROM sys.dm_xe_packages

 Beside of that each event can fire other actions synchronously int the same thread within event was invoked. Action extend extended events with own data in response.

For example, if you wold like to get sql text of a query it is defined by an action.

SELECT *
  FROM sys.dm_xe_objects o 
 WHERE o.object_type='action'

To filter event data, you can define Predicates. There is a list of objects you can use as predicate source. 

 SELECT *
 FROM sys.dm_xe_objects o 
WHERE o.object_type='pred_source'

List of operators 

SELECT *
  FROM sys.dm_xe_objects o 
 WHERE o.object_type='pred_compare'

 To start collecting data you must define session object which is set of actions, event data, predicates and target object.

 Target object collects output event data by synchronous or asynchronous way. Data can be collected for example to buffer, file, Windows Events, etc.

SELECT * FROM sys.dm_xe_objects o WHERE o.object_type='target' 

 User – friendly description of internal key words which could be returned from events data 

SELECT *
  FROM sys.dm_xe_objects o 
 WHERE o.object_type='map' 

  Relationship between events implemented in created sessions and actions 

SELECT * FROM sys.dm_xe_session_event_actions

 Data types you can expect from data collection. 

SELECT *
  FROM sys.dm_xe_objects o 
 WHERE o.object_type='type'

 Next time I would like to share some solutions implemented using extended events. Stay tuned.