SSRS Interview Questions - Part 1 of 3 (On Concepts from TK 70-448)

1.   Creating SSRS Projects and Reports in BIDS

a.    What are the three templates in BIDS?

                          i.          Report server project

                         ii.          Report server project wizard – table or matrix only

                       iii.          Report model project

b.   What are the Project-level Properties and how to modify them?

                          I.          StartItem

                        II.          OverwriteDataSources

                      III.          TargetDataSourceFolder

                      IV.          TargetReportFolder

                        V.          TargetServerURL

c.    What are the Report-Level Properties and how to modify them?

                       iv.          Report layout

·       Physical pages let you precisely control the way the pages will break in the report: Word, PDF, Image

·       Logical pages are calculated at run time based on the number of columns and rows: HTML, Excel

                        v.          Set width and height, and so on

2.   Developing and Modifying Report objects with the Report Designer

a.    Setting up object properties:

                          i.          Fonts,

                         ii.          Colors,

                       iii.          Date,

                       iv.          Number formatting.

b.   Adding Report objects to a Report

                          i.          A header or footer

                         ii.          A text box

                       iii.          An image

                       iv.          A chart

                        v.          A gauge

                       vi.          A Tablix

                     vii.          Other graphical items

c.    How to create a dataset from a data source?

                          i.          Data source - the driver and connection properties to connect to the data. SSRS supports SQL Server, SSAS, XML, report server model, SSIS package, Microsoft .NET Framework data providers, any OLE DB provider, and any Open Database Connectivity (ODBC) driver.

·       Shared - default

·       Private

                         ii.          Command type

·       Text – default (for the query strings)

·       Table – dimmed in most cases

·       Stored Procedure – rarely used

                       iii.          Query string

d.   Working with Advanced Report Object Properties

                          i.          Toggling object visibility

·       Hiding Groups

·       Hiding a Whole Tablix

·       Hiding a Static Row, Column, or Text Box of a Tablix

·       Hiding other Report Items

                         ii.          Defining Report Actions

·       Go to report

·       Go to bookmark

·       Go to URL

                       iii.          Assigning Datasets to Data Regions

·       What are the data regions?

-        A data region is a report item that displays rows of data from the source datasets.

·       What types of data regions in SSRS?

-        SSRS has three data regions: Tablix, chart, and gauge.

·       How to assign datasets to data regions

-        Navigate to the Report Designer’s Design tab, and then drag one of the five report items described in the following sections to the report layout region.

                       iv.          Working with Data Regions

·       Grouping Data by using Data Regions - using a data region with multiple grouping levels

·       Nesting Data Regions – Nesting data regions within other data regions.

·       Filtering Data in SSRS

o   Filtering occurs after data has been retrieved from the source, and it lets you set two or more regions to use the same dataset but show different information to the user.

o   SSRS lets you use filters in many different places:

§  At the dataset

§  At the data region

§  At the row or column groups in a Tablix

§  At the details group in a Tablix

§  At the series or category groups in a chart

·       Sorting Data in Data Regions

·       Applying Aggregates to Data Regions

o   Any of the aggregate functions such as SUM, AVG, COUNT, and many others.

o   A special type of aggregate—called a running aggregate - that adds one row at a time:

§  RowNumber - returns a running count of rows in the dataset.

§  RunningValue

3.   Query Parameters and Report Parameters

a.    When to Use Filters at the Dataset and When to Use Query Parameters?

b.   When to Use Query parameters and When to Use Report parameters?

c.    The Differences between Query and Report Parameters

	Query parameters	Report parameters
Parameter runs on	Database server	Report server
Primary purpose of parameter	To filter data	To interact with user
Parameter properties	Name and value (can be an expression)	Name, type, value, prompt, available values, default values, and so on
Parameter is managed through	Dataset	Report Parameters dialog box

d.    How to Create a Query Parameter or Dataset Parameter?

                          i.          OPTION 1 VIA DATASET PROPERTIES – YOUR DATASET (RIGHT-CLICK)| DATASET PROPERTIES | QUERY OR PARAMETER.

                         ii.          OPTION 2 VIA GRAPHICAL QUERY DESIGNER (WORKS FOR MDX AS WELL) – YOUR DATASET (RIGHT-CLICK)| QUERY.

e.   How to Create a Report Parameter?

                          i.          OPTION 1 – VIA THE PARAMETER FOLDER IN THE IN THE REPORT DATA WINDOW

                         ii.          OPTION 2 – VIA QUERY PARAMETERS

f.     How to Work with Report Parameter

                          i.          HOW TO ASSIGN A LIST OF VALUES THAT USERS CAN SELECT FROM (i.e., BINDING A DATASET TO A PARAMETER)?

Your Report Parameter (right-click)| Report Parameter Properties | Available Values.

                         ii.          HOW TO USE MULTIVALUED PARAMETERS

Your Report Parameter (right-click)| Report Parameter Properties | General | Allow Multiple Values.

                       iii.          HOW TO SET PARAMETER DEFAULTS?

Your Report Parameter (right-click)| Report Parameter Properties | Default Values.

                       iv.          D.      HOW TO SUPPLY THE PARAMETER VALUES THROUGH THE URL USED TO RUN PARAMETERIZED REPORTS?

http://localhost/ReportServer?%2fLogistics%2fTracking+Numbers+by+Month&rs:Command=Render&Department=12

4.   Use Expression and Custom Assembly in SSRS

a.    Extending Report Properties by using Expressions

                        v.          Use built-in expressions types:

o   Field

=Fields!LastName.Value

o   Control flow

=Choose(Fields!NameStyle.Value, “Mr”, “Mrs”, “Miss”)

=IIF(Fields!Amount.Value < 0, “Red”, “Black”)

=Switch(Fields!Amount.Value< 0.90*Fields!Goal.Value, “Red”,

Fields!Amount.Value<Fields!Goal.Value, “Yellow”

, Fields!Amount.Value>=Fields!Goal.Value, “Green”)

o   Built-in function

=Sum(Fields!Quantity.Value)

=(Rate(Fields!TotPmts.Value, - Fields!Payment.Value, Fields!PVal.Value)* 12) * 100

                       vi.          Using the embedded <Code> Element in a Report

o   Create the code - Report menu |Report Properties | Code tab | Write the VB Code

o   Use the code

=Code.GetStockColor(Fields!Quantity.Value, Fields!ReorderPoint.Value)

b.   Extending SSRS with Custom Assemblies - You can use any .NET languages

                     vii.          Create a custom assembly with a full blown Visual Studio and Build the .dll file

                   viii.          Deploy the assembly.

                       ix.           Add a reference to the assembly in your report.

                        x.          Use the assembly - using expressions to access the members of the classes in the assembly

5.   SSRS Deployments

a.    How many ways to deploy SSRS reports?

                          i.          BIDS/SSDT – developers use it

You can use multiple deployment configurations. By default, the Report Designer in BIDS provides three configurations: DebugLocal, Debug, and Release. You should use the DebugLocal configuration to preview the reports in the Preview tab of the Report Designer, the Debug configuration for deployment to test or stage folders on a test server, and the Release configuration for publishing on the final production server or folder. You can also add your own configurations.

Be careful that you do not overwrite configuration properties when you deploy a project because an administrator might have changed the data source properties to connect to a production database server rather than the server the developer used when authoring and debugging the report.

                         ii.          Report Manager (by uploading files) – Admin use it

                       iii.          Report Builder (Save As) – developers and end users use this option

                       iv.          SSMS – only in 2005

                        v.          Report Model and Report Builder 1.0 – only in 2005

b.   How to present reports to end users?

                          i.          You can deploy reports to the local report server so that users can use Report Manager to view the reports. If your company has an existing portal and you want to make the published reports available on the portal, you can embed URLs in reports so that they are published on the portal.

                         ii.          If you use Windows SharePoint Services 3.0 or Microsoft Office SharePoint Server 2007, you and other SharePoint users can use SharePoint Web parts to explore the report server and view the reports.

                       iii.          You can also use the .NET Framework report viewer controls to display the reports in a custom Windows or Web application.

c.    Best Practices on SSRS Deployment

                          i.          Staging the reports—that is, temporarily deploy reports in separate locations.

                         ii.          Restrict users’ access to the data sources with the principle of least privilege

                       iii.          Consider using query time-outs to prevent long-running queries on production servers.

                       iv.          Consider configuring the execution of long-running reports on a schedule and run it only in off-peak hours.

                        v.          Consider saving a snapshot of a report.

                       vi.          Consider creating subscriptions.

d.   How to use reports in your application code?

                          i.          Use Report Server Web service, which contains all of the SSRS objects and methods, to work with SSRS programmatically.

·       The SSRS Web service provides two kinds of endpoint

o   One for report execution, and

o   One for report management

§  ReportService2005 –if SSRS is configured in native mode. 

§  Report Service 2006 - If SSRS is configured in SharePoint integrated mode.

·       To use the objects and methods exposed through a Web service, you need to add a service or a Web reference in your project.

·       You can access the management endpoint WSDL of a report server configured for native mode by using the following URL:

http://<Server Name>:port/ReportServer/ReportService2005.asmx?wsdl

·       There is only one execution endpoint, ReportExecution2005. You can use it to customize report processing and rendering in both native and SharePoint integrated modes. You can access the WSDL file for the execution endpoint of a report server configured for native mode by using the following URL:

http://<Server Name>/ReportServer/ReportExecution2005.asmx?wsdl

                         ii.          Use report viewer controls for Windows applications and Web applications.

·       Both are similar

·       In remote mode, you can use them to process reports on a report server, and in local mode, you can use them to process reports copied to a computer without a report server.

·       If you use remote processing mode, you can use the full functionality of your report server, including all rendering and data extensions. You can use the full power of multiple report servers in a scale-out deployment or multiple processors of a report server in a scale-up deployment. In local processing mode, you get only a subset of the functionality. For example, the control does not process data; data processing is handled by the hosting application. And only PDF, Excel, and Image rendering extensions are available in local mode processing.

6.   SSRS Security

a.    SSRS Permission

                          i.          Securables or Objects are secured through the assignment of permissions to a given role.

SSRS does not have its own users. It takes advantage of the Windows operating system users and groups. Windows users and groups are associated with roles, and that association is valid for a given element inside the report object hierarchy within SSRS. Even though SSRS uses groups and users through Windows Authentication, in Internet deployment scenarios, this might not always be allowed. SSRS 2008 includes a complete framework for extensibility that allows you to have a custom security extension. For example, you could choose to have Forms Authentication with ASP.NET.

                         ii.          Two types of roles: item-level roles and system-level roles.

·       Roles (for both item-level and system-level roles) are created, modified, and deleted through SSMS. But the assignment of roles to users or groups happens through the Report Manager Web interface. 

·       Item-level roles

o   Five item-level roles in SSRS:

§  Browser

§  Content Manager

§  My Reports

§  Publisher

§  Report Builder

o   16 item-level tasks

·       System-level roles

o   They are related to the set of permissions, or tasks, related to the server’s administrative objects and scoped outside the report object hierarchy. Because these roles are scoped outside the report object hierarchy.

o   There are 9 system-level tasks on managing the report server itself, execution, events, jobs, shared schedules, and roles.

§  Execute Report Definitions - Starts execution from a report definition without publishing it to the report server

§  Generate Events - Provides an application with the ability to generate events within the report server namespace

§  Manage Jobs - Views and cancels jobs as they are running

§  Manage Report Server Properties - Views and modifies properties that apply to the report server and to items managed by the report server

§  Manage Report Server Security - Views and modifies system-wide role assignments

§  Manage Roles - Creates, views, modifies, and deletes role definitions

§  Manage Shared Schedules - Creates, views, modifies, and deletes shared schedules used to run or refresh reports

§  View Report Server Properties - Views properties that apply to the report server

§  View Shared Schedules - Views a predefined schedule that has been made available for general use

o   After you create a new system-level role, you can use that role to assign permissions to a given user to access a server object through the Report Manager:  Site Settings | Security

o   Because system-level roles are outside the report object hierarchy, they are defined once for each server. Like item-level roles, they are cumulative and are based on Windows users and groups.

                       iii.          My reports

·       New in SSRS 2008 (? In SQL Server 2005 as well!).

·       My Reports can be enabled or disabled at the server level - To enable My Reports by using SQL Server Management Studio, connect to the report server instance and open the Server Properties page. Then on the General tab, select the Enable a My Reports folder for each user option.

                       iv.          Linked reports and its links to My Reports

·       They are entities that reference an existing report but with different configurations.

·       Linked reports create a constant dependency on the base report. This means that the linked report has no internal .rdl file associated with it but instead takes all the report definitions from the base report. This dependency also implies that the linked report has no data source definitions of its own. You cannot edit the .rdl code associated with a linked report because a linked report has no .rdl code of its own. It refers to the .rdl code of the base report.

·       The “My Reports” item-level role includes the Create Linked Reports and Manage Reports tasks, which allow users to store linked servers inside their individual My Reports folders without having more permissions than are required in the public structures.

b.   Data Sources Security

                          i.          Shared data sources

·       Shared data sources are the objects that represent the connection information to a data repository that a report will use. They are stored in the Reporting Services ReportServer database.

·       Two main elements: the name of the service to which you can connect (often represented as the name of the server or the name of the database instance) and the credentials used to access that repository (in the form of a user name and password).

·       To modify the credentials of a shared data source, navigate to the shared data source in Report Manager, and then click the Data Sources link to display its properties.

·       Four mechanisms for specifying the credentials used to access the data repository, as follows:

o   Credentials supplied by the user running the report - Each user is prompted to type in a user name and password to access the data source. Select this option if the credentials that the user provides are Windows Authentication credentials. Do not select this check box if you are using database authentication (for example, SQL Server Authentication).

o   Credentials stored securely in the report server- Store an encrypted user name and password in the report server database. Choose this option to run a report unattended (for example, reports that are initiated by schedules or events instead of user action). If you are using default security, the user name must be a Windows domain account. Specify the account in this format: <domain>\<username>. The account you specify must have log on locally permissions on the computer that hosts the data source used by the report.

§  Select Use as Windows credentials when connecting to the data source if the credentials are Windows Authentication credentials. Do not select this check box if you are using database authentication (for example, SQL Server Authentication).

§  If you are using database authentication, select Impersonate the authenticated user after a connection has been made to the data source to allow delegation of database credentials, but only if a database server supports impersonation. For SQL Server databases, this option sets the SETUSER function.

o   Windows integrated security - Use the Windows credentials of the current user to access the data source. Choose this option when the credentials that are used to access a data source are the same as those used to log on to the network domain.

§  This option works best when Kerberos authentication is enabled for your domain, or when the data source is on the same computer as the report server. If Kerberos authentication is not enabled, Windows credentials can be passed to one other computer. If additional computer connections are required, you will get an error instead of the data you expect.

§  A report server administrator can disable the use of Windows integrated security for accessing report data sources. If this value is grayed out, the feature is not available.

§  Do not use this option if you plan to schedule or subscribe to this report. Scheduled or unattended report processing requires credentials that can be obtained without user input or the security context of a current user. Only stored credentials provide this capability. For this reason, the report server prevents you from scheduling report or subscription processing if the report is configured for the Windows integrated security credential type. If you choose this option for a report that is already subscribed to or that has scheduled operations, the subscriptions and scheduled operations will stop.

o   Credentials are not required - Specify that credentials are not required to access the data source.

§  Note that if a data source requires a user logon, choosing this option will have no effect. You should only choose this option if the data source connection does not require user credentials.

§  To use this option, you must have previously configured the unattended execution account for your report server deployment. The unattended execution account is used to connect to external data sources when other sources of credentials are not available. If you specify this option and the account is not configured, the connection to the report data source will fail and report processing will not occur.

                         ii.          Private report data sources have identical properties but are managed through the report properties in Report Manager.

c.    Best Practices for Report Scheduling

                          i.          Create a separate database login for running the reports and give this user only the following privileges:

·       Read access to the ReportServer database

·       Read access to the msdb database

·       Read access to all objects from all databases included in a report

                         ii.          Create a report-specific schedule only when either no shared schedule exists or when that schedule is needed for a single report only.

                       iii.          You can create a shared schedule in Report Manager or in SSMS.

                       iv.          You create a report-specific schedule when you define the execution properties of a report or when you subscribe to a report in Report Manager. But you can manage execution properties and subscriptions in SSMS or in Report Manager. Note: Since SQL Server 2008 R2,’ Execution’ has been renamed to “Processing Options’

d.   Best Practices for Report Subscriptions

                          i.          Two running modes: pull delivery model - the user runs the report as needed and push delivery model for your reports— the reports will be pushed out to fulfill a recurring demand.

                         ii.          Two types of subscriptions: Static and Data-driven

·       Static subscription

o   A report subscription is a standing request to have a report delivered at a specific time or in response to an event and in the format and delivery method that you have defined. You can use subscriptions to schedule and automate delivery of a report (i.e., for the pushing mode).

o   For a subscription, you must specify the following information:

§  Delivery method

§  Rendering type

§  Conditions for processing the subscriptions, either on a schedule or when a snapshot of the report is updated on the report server

§  Values for parameters if a report is parameterized.

o   Report subscriptions are set for reports through Report Manager. To define a subscription for a report, in Report Manager, open a report, click the Properties tab, and then click the Subscriptions tab.

o   When the new Subscription option is dimmed, the report probably does not use stored credentials for accessing the data. SSRS needs these credentials stored in its own ReportServer database to execute a report on a schedule.

o   Report subscriptions are useful when a report needs to be sent by e-mail or saved to the file system on a recurring basis but the report needs to be run only one time for each time the schedule executes.

·       Data-driven subscriptions - to address diverse needs of delivery method and address, rendering format, and parameters.

o   A data-driven subscription is useful when you need to customize the report parameters and/or the type of subscription (for example, e-mail or file share). In addition, data-driven subscriptions can run the report several times for each scheduled execution occasion.

§  Your list of subscribers is not fixed and changes often.

§  You need to filter the report based on different parameters for each user, and you need to retrieve these parameters at run time.

§  You need to use different rendering formats and delivery methods for different users, or you have to vary formats and methods for each execution.

o   Steps:

§  Preparing data for data-driven subscription - The data source must provide the information in tabular format. Every row from a query that is used to retrieve the data for the subscription makes a new subscription, and columns returned by the query are used to dynamically specify subscription properties for each subscription. All the data that is needed for a data-driven subscription must be retrieved in a single query.

§  Create a new data-driven subscription by specifying the Subscription Delivery format and Location etc.

7.   Report execution, which includes caching and snapshots.

a.    Understanding Report Execution Behavior - defining how the report source data is handled and what Reporting Services should do with the data after the report is rendered.

                          I.          Report Server receives a request, generally in Hypertext Transport Protocol (HTTP) format, for a given report.

                        II.          Report Server validates the properties associated with the object requested.

                      III.          Report Server accesses the SQL Server catalog database (ReportServer) and extracts the RDL definition of the report.

                      IV.          From the definition, Report Server extracts the information related to data sources.

                        V.          Reporting Services connects to the data sources and executes the commands that retrieve data.

                      VI.          After the data has been retrieved, the data is mixed with the RDL definition and generates an intermediate format report.

                    VII.          The intermediate format report is rendered in the format that was requested by the client; this could be HTML, Excel, PDF, and so on.

b.   Reporting Services has two mechanisms to reduce the overhead associated with a large number of concurrent users, large datasets, and long-running queries: report caching and report snapshots.

                          i.          Report Caching

·       What is it?

SSRS caches the report in the ReportServer database with all the data but without any of the rendering information. If a report exists in the cache, when the next user runs the report, the user gets the data from the cache, and there is no need to reread data from the database. Because the rendering information is not stored in the cache, a user can use any rendering format on the data.

·       How is it outdated?

o   By specifying a number of minutes,

o   By defining a report-specific schedule or a shared schedule, or

o   If the data source credentials are changed or no longer stored on the report server.

·       How is it enabled?

Caching is enabled through the “Execution” properties of a report (or Processing Mode in R2+).

·       How does it work?

With report caching, Reporting Services can create and store a copy of the intermediate format report in a temporary SQL Server database named ReportServerTempDB in the ExecutionCache table. Even when the first request from a user follows the schema presented earlier, the processing of subsequent requests is altered as follows:

o   A request is sent to Report Server.

o   Report Server validates the properties associated with the object requested. If report execution is set to use the cache, and an item already exists in the ExecutionCache table of the ReportServerTempDB database for the ReportID of the requested report and the parameters passed are the same, Report Server renders the report from the stored intermediate format.

o   When a report cache is available, the dataset queries do not execute, and the intermediate format is not created. This greatly increases the response time. However, for every combination of parameters, the report will create a new cache. In other words, if the parameters passed in the second request do not match those of the first request, the process is considered a brand-new request.

o   Can you always create a cache of a report?

No, you can create a cache of a report only when certain requirements, such as having credentials stored in the Report Server, are met.

                         ii.          Report Snapshots

·       Why is it needed?

If you cache a report, the first user who runs the report after the cache has expired will have to wait longer to view the report than the users who follow, because the first user triggers the data being read from the database. You can prevent this problem by creating a snapshot of the data in advance of the first report being run.

·       How to enable it?

Select the Render This Report from An Execution Snapshot option in the “Execution” properties of a report. You can create a snapshot based on a shared or a report-specific schedule. In addition, you can create the first snapshot immediately after you save the execution settings.

·       Why do you need to store multiple snapshots in the report history and how to manage report history?

o   Persistence of previous values in reports might be required by business users for comparison purposes or to be able to refer to those numbers later.

o   Additional history snapshots are stored in the ReportServerTempDB database. The number of report snapshots stored can be and should be controlled. Thus, if you anticipate that your end users might need five years of historical data, or they might want to compare older snapshots with newer ones, for example, you should create a data warehouse and an online analytical processing (OLAP) solution that will allow users to look at archived data.

o   The report history options are available in the “Properties” area of a deployed report.

·       Major differences between report caching and report caching?

o   One difference between the cache and snapshots is that the intermediate format of a report snapshot is stored in the ReportServer database rather than in the temporary database.

o   Also, in contrast to cached copies, snapshots do not allow any change in the query parameters used to render the report.

8.   SSRS System Failure Recovery

When faced with a system failure where you lose your SSRS instance and/or database, you will first need to reinstall SSRS. After the installation is complete, you can optionally restore the latest backup of your report server catalog database (if it was also lost). After the service and catalog databases are in place, your final step is to restore the encryption key onto the newly installed instance of SSRS. This will enable the report server to be able to decrypt the catalog security, reports, and data sources.

If you do not have the database or lose the encryption key, you would have to find all the report projects that had been deployed before and redeploy them to the new server.

setting up a scale-out deployment model for SSRS involves sharing encryption keys between in-

stances pointed to the same report server database and can be done either through Reporting Services Configuration Manager or through the command line.

9.   SSRS Command-Line Utilities

a.    RSConfig.exe - to Manage Report Server Database Connections.

                          i.          Parameters for the connection to the SSRS server and instance.

·       /m remote server name

·       /i instance name

                         ii.          Database Connection Parameters

·       /c - Indicates that you are defining the connection information with other parameters. This parameter does not use a value and is required if you are defining the connection.

·       /s database server name

·       /d database name

·       /a Authentication Method

·       /u username

·       /p password

·       /t - Optionally writes out error messages to the SSRS trace log.

                       iii.          Unattended Account Parameter

·       /e - Indicates that you are setting the data source account to be used for an unattended report execution. This parameter requires that you also use the /u and /p user name and password parameters. Although it is not required, the /t parameter can also be used.

b.   RSKeyMgmt.exe utility assists in the management of the encryption keys for operations such as backup, restore, and create.

                          i.          Parameters for Key Management Tasks

·       /e - Specifies that the SSRS encryption key should be extracted to a file for backup.

·       /a - Specifies that the SSRS encryption key should be restored from a file and overwritten.

·       /d - Deletes the encryption key on the SSRS instance or instances and deletes all the encrypted data.

·       /s - Replaces the existing encryption key with a newly generated one and re-encrypts all the existing content with the new key.

·       /f file path - Specifies the location of the file for the encryption key if the /e or /a parameter is used.

·       /p file password - Specifies the password used to secure the encryption key file so that if the file is found, the encryption key is still secure.

·       /i local SSRS instance name - Optional argument used to specify a named instance of SSRS if the SSRS instance is local to the command-line execution.

·       /t - Captures errors to the SSRS trace log.

Examples

rskeymgmt.exe /e /f c:\SSRS_Keys -p pass@word1

rskeymgmt.exe /a /f c:\SSRS_Keys /p pass@word1 /i SSRSAdmin

rskeymgmt.exe /d

                         ii.          Parameters for Scale-Out Instance Management

·       /j - Adds a remote instance of SSRS to the Report Server database of a local instance. The remote server and instance are specified with the /m and /n parameters, and if a named instance is used locally, it is specified using the /i parameter.

·       /r GUID Installation ID - Removes an instance of SSRS from a scale-out deployment implementation. The instance to be removed is identified by the Installation ID, a unique identifier mapped to the instance and specified in the RSReportServer.config file.

·       /u account name Specifies the account of a local administrator on the server where the remote SSRS instance (the instance that will be joining a scale-out deployment) runs. This parameter is optional if the user who is executing RSKeyMgmt.exe has local administrator rights on the remote server.

·       /v password - This defines the password of the local administrator account specified by the /v parameter.

·       /m remote SSRS Server Name - In connecting to an SSRS instance on a remote computer, this parameter is used to specify the server.

·       /n remote SSRS instance name - This is used in conjunction with the /m parameter to specify the SSRS instance name on a remote computer. If the default instance is used, this is not required.

c.    RS.exe for Microsoft .NET scripting of report management operations

                          i.          How? - The RS.exe tool references a Report Server Script (.rss) file, which contains Microsoft Visual Basic .NET code based on the Web Services Description Language (WSDL) application programming interface (API).

                         ii.          Parameters

·       /i input .rss file - Specifies the .rss file to execute.

·       /s SSRS Server URL - Defines the URL path to the SSRS Report Server virtual directory.

·       /u username - To override the user running the command, this parameter can define a different domain\user account to be used to connect to the Report Server instance.

·       /p password - Used to specify the password for the associated user name of the account defined with the /u parameter.

·       /l timeout seconds - Used to override the default time-out of 60 seconds; the /l (lowercase L) parameter is measured in seconds, with 0 representing an unlimited execution time.

·       /b - Runs the commands in a batch so that if a failure occurs anywhere in the script, the entire operation will be rolled back.

·       /e SOAP endpoint - Defines the Simple Object Access Protocol (SOAP) SSRS Web service endpoint to use for SSRS 2005 installations. By default, the SSRS 2005 management endpoint mgmt2005 is used.

·       /v - Global Variable mapping - If the script contains embedded variables, the /v parameter can pass values into the variables.

·       /t - Captures errors to the SSRS trace log.

                       iii.          Examples:

·       Example 1: An RSscript.rss script is executed against a local instance of SSRS that was installed with the default ReportServer virtual directory for Report Server:

rs.exe /i RSscript.rss /s http://localhost/ReportServer

 

·       Example 2: A script is executed under the corporate\SSRSSvc account while passing the value ProdSQLSvr into the script for the variable named vDataSource:

 

rs.exe /i RSscript.rss /s http://localhost/ReportServer /v

vDataSource=ProdSQLSvr /u Corporate\SSRSSvc /p pass@word1

 

d.   SSRS Scale-out Deployments and High Availability

                          i.          What is it?

·       Connecting more than one report server instance to a shared report server catalog.

                         ii.          Key Components

 

·       Network Load Balancing (NLB) provides the ability for the clients to distribute the requests to multiple report server instances.

·       Multiple report server instances installed on multiple servers allow the report execution workload to be shared where report requests can be directed through NLB to different report server instances.

·       A shared report server catalog provides the connection between the multiple report server instances. The report server catalog databases contain sensitive information such as report data sources and data caching that each of the report servers can access by sharing the encryption key.

·       (Optional) A Microsoft Cluster Service (MSCS) installation with the shared report server catalog can be added to the cluster. This optional element in a scale-out solution provides higher availability because it provides high availability for the shared catalog databases.

o   When you are configuring a scale-out deployment, it is important to also consider that the report server catalog is a single point of failure unless it is added to a clustered instance of the Database Engine.

o   However, the report server is not installed in a cluster, and therefore cluster resource groups do not apply to the Report Server service. Only the report catalog databases can be created on an existing clustered SQL Server Database Engine instance.

o   Thus, for the Report Server service, high availability and scale come through creating an SSRS scale-out deployment environment (cluster for the report server catalog )

                       iii.          How to configure SSRS for a scale-out deployment?

                                    I.          Using Reporting Services Configuration Manager - The main steps involved in connecting a new report server instance to an existing report server installation using Reporting Services Configuration Manager are to point the database connection to the shared repository and then synchronize the encryption keys.

                                  II.          Using the command-line utility RSKeyMgmt.exe – see the RSKeyMgmt.exe section above.

                                III.          Example 1: In the following example, the remote SSRS instance ProdSSRSSvr1 is joined to the scale-out implementation of SSRS shared by the local SSRS instance. The remote local administrator account is Corporate\SSRSSvc with the associated password:

rskeymgmt.exe /j /m ProdSSRSSvr1 /u Corporate\SSRSSvc /v pass@word1

                                IV.          Example 2: This example removes an instance of SSRS that is part of a scale-out deployment. The report server globally unique identifier (GUID) installation ID was acquired from the RS ReportServer.config file:

rskeymgmt.exe /r {632e859c-53513-47113-a9e5-f28a0206a68f}

10.        Changing Report Server Properties in SSMS

At the report server level, SSRS contains properties that apply to the overall instance such as logging, enabling My Reports, and security. Some of these properties are found in the Site Settings link in Report Manager. However, other properties are accessible through SSMS when connected to an SSRS instance.

11.        SSRS 2008 Configuration Files

                                    I.          RSReportserver.config - Primary configuration file that contains settings for the Report Server Web part and Report Manager. It contains rendering settings and HTTPS. In previous releases, Report Manager had its own configuration file named RSWebApplication.config. That file is now obsolete. If you upgraded from a previous installation, the file will not be deleted but the report server will not read any settings from it. If the file exists on your computer, you should delete it. In SQL Server 2008 and later versions, all Report Manager Configuration settings are stored in and read from the RSReportServer.config file.

                                  II.          The settings in Reporting Services Configuration Manager are saved as configuration entries in the RSReportServer.config file. However, the configuration manager also performs critical steps that are not handled by a configuration file, such as creating the virtual Web directories and generating the application as well as creating the structures and initial entries in the report catalog when a new database is specified.

                                III.          Nonetheless, the RSReportServer.config file contains many more advanced settings and properties that are not available to set within Reporting Services Configuration Manager or through the report server properties in SSMS.

                                IV.          RSSrvPolicy.config - Stores policy settings for the Report Server Web service.

                                  V.          RSMgrPolicy.config - Contains policy settings for the Report Manager application. The policy settings of SSRS2005 are located in RSWebApplication.config. SSRS2008 does not use this file, but saves the configurations instead in RSSrvPolicy.config and RSMgrPolicy.config.

                                VI.          Web.config - Contains ASP.NET settings for the Report Server and Report Manager.

                              VII.          ReportingServiceServices.exe.config - Contains trace and logging settings for the

                            VIII.          Report Server service.

                                IX.          RSReportDesigner.config - Contains configuration settings such as rendering devices for the Report Designer application.

                                  X.          RSPreviewPolicy.config - Contains security policies for the server extensions.