Category: SQL Server BI

How to display data in SSRS Report from multiple datasets

Since SSRS 2008 R2 multiple datasets can be joined in a single data region on the report. As a result of the expression field we can display the first matching value for the specified name from secondary dataset that contains name/value pairs.

SQL Reporting Services comes with three lookup functions having four parameters as given below

  1. Lookup (source_expression, destination_expression, result_expression, dataset)
  2. LookupSet (source_expression, destination_expression, result_expression, dataset)
  3. MultilookUp (source_expression, destination_expression, result_expression, dataset)

 

LookUp Functions Parameters

  • source_expression. Evaluated in the current scope and that specifies the name or key to look up. For example, =Fields!AgentID.Value.
  • destination_expression. Evaluated for each row in a dataset and that specifies the name or key to match on. For example, =Fields!Agent_ID.Value.
  • result_expression. Evaluated for the row in the dataset where source_expression = destination_expression, and that specifies the value to retrieve. For example, =Fields!TeamLeader.Value.
  • dataset. Name of a dataset in the report. For example, “Agents”

 

Lookup LookupSet MultiLookup
Retrieves the value from the specified dataset for a name/value pair where there is a 1-to-1 relationship. Retrieves multiple values for a single name or key field where there is a 1-to-many relationship Retrieves a set of values from a dataset for name-value pairs where each pair has a 1-to-1 relationship
For example, for an ID field in a table, you can use Lookup to retrieve the corresponding Name field from a dataset that is not bound to the data region For example, for a customer identifier in a table, you can use LookupSet to retrieve all the associated phone numbers for that customer from a dataset that is not bound to the data region For example, for a multivalue parameter that is based on primary key identifiers, you can use Multilookup in an expression in a text box in a table to retrieve associated values from a dataset that is not bound to the parameter or to the table.
Returns the result expression value Returns the set of result expression values

Because LookupSet/MultiLookup returns a collection of objects, you cannot display the result expression directly in a text box. You can concatenate the value of each object in the collection as a string

Use the Visual Basic function Join create a delimited string from a set of objects. Use a comma as a separator to combine the objects in a single line. In some renderers, you might use a Visual Basic line feed (vbCrLF) as a separator to list each value on a new line.

=Join(LookupSet(Fields!SiteGroupID.Value, Fields!ID.Value, Fields!SiteCategoryName.Value, “Category”),”,”)

=Join(MultiLookup(Split(Fields!SiteCategoryList.Value,”,”), Fields!ID.Value,Fields!SiteCategoryName.Value,”Category”)),”, “)

  • Evaluated after all filter applied
  • Only one level of lookup is supported. A source, destination, or result expression cannot include a reference to a lookup function.
  • Source and destination expressions must evaluate to the same data type. The return type is the same as the data type of the evaluated result expression.
  • Source, destination, and result expressions cannot include references to report or group variables.
  • Cannot be used as an expression for the following report items:
    • Dynamic connection strings for a data source
    • Calculated fields in a dataset
    • Query parameters in a dataset
    • Filters in a dataset
    • Report parameters
    • The Report.Language property

 

SSRS Lookup Example

SSRS Lookup report

Dataset1 includes an Agent Name identifier (agent_name_tpi) field. Dataset2 contains the corresponding AgentName and total calls answered AnsCalls.

In the following expression, Lookup compares the value of agent_name_tpi to AgentName in each row of the dataset2 and, when a match is found, returns the value of the AnsCalls field for that row.

SQL Server Reporting Services Data Alerts

SQL Server 2012 introduced data alerts as a way to notify users of changes to report data, enabling them to view reports only when changes in the data may have business implications. By using this data driven alerting solution you no longer have to seek out information, it comes to you. 

Data alert messages are sent by email. Depending on the importance of the information, you can choose to send messages more or less frequently and only when results change. You can specify multiple email recipients and this way keep others informed to enhance efficiency and collaboration.

Good example of the data alerts can be sending out monthly sales report data only if total sales amount is less than a specified amount.

 

Data Alerts Key areas

  • To create data alerts on reports, the reports must be saved or uploaded to a SharePoint library. This means that you cannot create alerts on reports saved to a report server in native mode or your computer. Also, you cannot create alerts embedded in custom applications.
  • You can create data alerts on reports with data source configured to use stored credentials, or no credentials. You cannot create alerts on reports configured to use integrated security credentials or prompt for credentials. The report is run as part of processing the alert definition and the processing fails without credentials.
  • SQL Server alerting database is automatically created with Reporting Services in SharePoint mode install. Data alert definitions and alerting metadata are saved in the alerting database. By default, this database is named ReportingServices<GUID>_Alerting.
  • When the data alert definition saved, alerting creates a SQL Server Agent job for the alert definition. The job includes a job schedule. The schedule is based on the recurrence pattern defined on the alert definition. Running the job initiates the processing of the data alert definition.
  • An alert has only one owner, the person who created it. The alert owner can view information about your data alerts and delete and edit your data alert definitions
  • Alerting administrators, users with SharePoint Manage Alerts permission, can manage data alerts at the site level. They can view lists of alerts by each site user and delete alerts.
  • Reporting Services data alerts are different from SharePoint alert. SharePoint alerts on any document type, including reports. SharePoint alerts are sent when the document definition changes. Data alerts are sent when the data shown in a report satisfied rules in the alert definitions.
  • Multiple alerts on a report and multiple users can create the same or different alerts on a report
  • Report has to have data regions, to create an alert on it.

 

Here is Microsoft diagram for data alert definition

data alerts process

 

 Creating data alert definition

  1. Locate the SharePoint library that contains the report that you want to create a data alert definition for.
  2. Run the report. Verify that the report shows the data that you want to receive alert messages about. NOTE: The parameter values you chose to run the report are saved in the alert definition and will be used when report is rerun as a step in processing the alert definition. To use different parameter values, you must create a new alert definition
  3. On the Actions menu, click New Data Alert. The Data Alert Designer opens, showing the first 100 rows of the first data feed that the report generates in a table.

actions to create new data alert

4. Select data feed in the Report data name drop-down list.

  1. Click Add rule. In the list, select the column that you want to use in the rule, and then select a comparison operator and enter the threshold value.
  2. Select an option in the recurrence list. Depending on the type of recurrence, enter an interval.
  3. Enter the email addresses of alert message recipients. Separate addresses with semicolons. If the email address of the person who created the alert definition is available, it is added to the Recipient(s) box.
  1. Click Save.

 

data alerts scheduling

 

Data Alerts Maintenance

Once setup, alerts can be maintained by right clicking on the report requiring alert maintenance, and then selecting Manage Data Alerts 

The alert maintenance screen provides the following information: last run, status, sent alerts.  Alert can be edited to change items, such as, the rule criteria, the dataset, or the email recipient.

Reference

http://technet.microsoft.com/en-us/library/gg492251%28v=sql.110%29.aspx

http://technet.microsoft.com/en-us/library/gg492252.aspx

http://en.wikipedia.org/wiki/List_of_SMS_gateways

 

Microsoft’s SQL Server 2014 new features

HotLap Melbourne was a great opportunity to find out what’s new in the next release of SQL Server, SQL Server 2014. It was nicely presented by Peter Ward from www.wardyit.com. 2 major topics were covered Database Engine Improvements & Power BI.

 

Key notes summary

SQL Server 2014 Benefits

Achieved through

SQL Server 2014 Features

Mission Critical Performance (getting high reliability as well as being fast
  • In-Memory Built-In for OLTP, DW, In memory analytics
  • Enhanced Security & Scalability
  • High Availability using AlwaysOn
  • Live support for mission critical solutions
  • Memory Optimised tables & Indexes
  • Compiling TSQL to dll
  • ColumnStore Indexes Clustered and Updateable
  • Buffer poll extension using SSD drives
  • Managing locks priority for online operations when rebuild index or switching table partitions
  • Enhancements to AlwaysOn, readable secondary replicas (up to 8)
  • New security roles for non sysadmin DBA
  • I/O Resource Governor
Platform for Hybrid Cloud (reduced cost per compute)

 

  • Hybrid Cloud Solutions
  • On-ramp to cloud
  • Complete & consistent platform
  • Cloud backup manual/auto with point in time restore
  • Cloud DR easy deploy and recovery
  • Cloud bursting – Ease to migrate and run SQL server in the cloud
  • Migration Wizards to Windows Azure VM
Faster Insight from Any Data (consume it from anywhere)

 

  • Easy data access
  • Familiar tools
  • Complete BI platform
  • Ability to combine external/internal/unstructured data
  • Excel/Office 365 enhancements
  • BI models spanning cloud/on-premises

 

Power BI explained

 

SQL Server Power BI

 

SSRS Reports Caching

In SSRS there are two options for pre-loading SSRS Reports: caching and snapshots

  • A cached instance of a report is based on the intermediate format of a report. The report server generally caches one instance of a report based on the report name. However, if a report can contain different data based on query parameters, multiple versions of the report may be cached at any given time. The first user who runs the report with a unique region code creates a cached report that contains data for that parameters. Subsequent users who request the report using the same parameters get the cached copy.
  • The other option is running a report from the report snapshot. Snapshot is a report that contains layout information and data refreshed at a specific point in time. A report snapshot is usually created and refreshed on a schedule, allowing you to time exactly when report and data processing will occur. A report snapshot is stored in the intermediary form in the ReportServer database, where it is subsequently retrieved when a user or subscription requests the report. When a report snapshot is updated, it is overwritten with a new instance. The report server does not save previous versions of a report snapshot unless you specifically set options to add it to report history.

Note: Copy of the processed cached report after the execution is saved into into ReportServerTempDB. Data source with stored credentials on the report server must be used in order to implement caching.

 

ReportServer Database ReportServerTempDB
Stores information about the report schema, report property, data sources, parameters, stores the folder hierarchy, report Execution log Stores only the cached copy of the report data
Always exists until changes are made to the RDL Schema Expires based Expiry settings
Helps to access the structure Helps to improve the performance of report execution as it is loading data from cache
Data always exists during SSRS service restarts Services restarts will clear the Temp Data, all cached instances are reinstated when the Report Server Web service comes back online

Refreshing the Cache

A cached report is replaced with a newer version when a user selects the report after the previously cached copy has expired. Reports that are configured to run as cached instances are removed from the cache at regular intervals based on expiration settings. You can set a report’s expiration in minutes or at a scheduled time, as determined by the data’s immediacy requirement. You cannot delete reports from the cache directly unless you use the SOAP API.

To configure cache expiration, you can use a shared schedule or report-specific schedule. If you use a shared schedule and it is subsequently paused, the cache does not expire while the schedule is inoperative. If the shared schedule is subsequently deleted, a copy of the schedule settings is saved as a report-specific schedule.

If a schedule expires or if the scheduling engine is unavailable at a cache expiration date, the report server runs a live report until scheduled operations can be resumed (by either extending the schedule or starting the scheduling service).

Preloading the Cache

To improve server performance, you can preload the cache. You can preload the cache with a collection of parameterised report instances in two ways:

  1. Create a cache refresh plan. When you create a refresh plan, you can specify a schedule for a single report or specify a shared schedule.
  2. Create a data-driven subscription that uses the Null Delivery Provider. When you specify the Null Delivery Provider as the method of delivery in the subscription, the report server targets the report server database as the delivery destination and uses a specialized rendering extension called the null rendering extension. In contrast with other delivery extensions, the Null Delivery Provider does not have delivery settings that you can configure through a subscription definition.

When you specify a schedule or when you create the data-driven subscription, you schedule how often the reports are delivered to the cache. In order for new copies to be delivered to the cache, the old copies must have expired. Therefore, the Execution properties of the report must be configured to include cache expiration settings. The expiration setting must be consistent with the subscription schedule that you define. 

For example, if you create a subscription that runs every night, the cache should also expire every night prior to the subscription’s run time. If the Execution properties do not include expiration times, newer deliveries are disregarded.

 

To schedule the expiration of a cached report

      1. Start Report Manager (SSRS Native Mode).
      2. In Report Manager, navigate to the Contents page. Navigate to the report for which you want to set caching properties, hover over the item, and click the drop-down arrow.
      3. In the drop-down menu, click Manage.
      4. In the left frame, click the Processing Options.
      5. On the page, select Always run this report with the most recent data.
      6. Select one of the following two cache options and configure expiration as follows:
        • To configure a cached copy to expire after a particular time period, click Cache a temporary copy of the report. Expire copy of report after a number of minutes. Type the number of minutes for report expiration.
        • To configure a cached copy to expire on a schedule, click Cache a temporary copy of the report. Expire copy of report on the following schedule. Click Configure, or select a shared schedule to control report expiration. My Schedule runs every morning

        Processing Options: Always run this report with the most recent data

      7. Click Apply.
      8. To create Cache Refresh Plan which create a schedule for preloading the cache with temporary copies of data for a report click Cache Refresh Options and click on New Cache Refresh Plan.
      9. Fill in the blanks, set Shared schedule. Then click on OK button.

ssrs cache refresh options

 

Global Site Schedules: one to generate cached report and the other to delete cashed report

expired cache schedule

report cache schedule

 

Some useful T-SQL queries (SSRS caching)

Check logging Enable:

Use [ReportServer]

go

select * from ConfigurationInfo where Name=’EnableExecutionLogging’

go

Get the current retention period for the Execution Log

Use [ReportServer]

go

select * from ConfigurationInfo where Name=’ExecutionLogDaysKept’

go

Execution Log details, including 
  • InstanceName
  • ReportPath
  • UserName
  • ExecutionId 
  • RequestType: {Interactive, Subscription}
  • Format  rendering
  • Parameters 
  • ReportAction: { Render, BookmarkNavigation, DocumentMapNavigation, DrillThrough, FindString, GetDocumentMap, Toggle, Sort }
  • TimeStart and TimeEnd
  • TimeDataRetrieval, TimeProcessing, TimeRendering
  • Source: { Live, Cache, Snapshot, History, AdHoc, Session, Rdce }
  • Status
  • ByteCount
  • RowCount
  • AdditionalInfoIn short, an XML property bag containing additional information about the execution.

select * from ExecutionLog 

select * from ExecutionLog2

select * from ExecutionLog3 

 

  • ExecutionLog is for backward compatibility and available with SQL 2008 onward.
  • ExecutionLog2 is introduced with SQL 2008 
  • ExecutionLog3 is introduced with SQL 2008 R2. 

ExecutionLog2 and ExecutionLog3 are similar the difference in column name ReportPath – ItemPath, ReportAction – ItemAction

 
Cashed reports metadata:

SELECT C.Name, EC.*, SD.*

FROM ReportServerTempDB.dbo.ExecutionCache AS EC

INNER JOIN ReportServer.dbo.Catalog AS C ON EC.ReportID = C.ItemID

INNER JOIN ReportServerTempDB.dbo.SnapshotData AS SD ON SD.SnapshotDataID = EC.SnapshotDataID 

SQL Saturday Melbourne

April-5th saw the first SQL Saturday in Melbourne. With more than 300 people registered and even more attended, international and local speakers and exhibitors it was really good staged and organised event for SQL Server community in Melbourne.

Here is the schedule of the event. There was a good mix of topics for all skill levels.

20 sessions on SQL Server 2014, BI, Data Warehousing, DBA, Database/Application Development and Database Administration.

 

Start Time

Keynote & Sponsor Sessions – Room: H.116

Track 1 – Room: H.125

Track 2 – Room: H.126

Track 3 – Room: H.235

Track 4 – Room: H.237

Track 5 – Room: H.238

08:45 AM Mike Ward

Keynote –

Level: Non-Technical

         
10:00 AM   Darren Gosbell

Drop your DAX

Level: Intermediate

Peter Myers

Introduction to SQL Server Data Mining

Level: Intermediate

Grant Paisley

Kick-ass Ad-hoc Cube Browser build in SSRS 2014

Level: Intermediate

Peter Ward

Hybrid Cloud Scenarios with SQL Server

Level: Intermediate

Shehap El-Nagar

T-SQL Performance Guidelines for better DB stress powers

Level: Advanced

11:30 AM   Henry Rooney

I have the data now what!

Level: Beginner

Julie Koesmarno

Building Your Myth Busting Lab With Power BI

Level: Beginner

Mohamed Kabiruddin

Agile BI with SQL Server 2014 and TFS 2013

Level: Intermediate

Matthew Winter

Introduction to SQL Server 2012 Parallel Data Warehouse

Level: Beginner

Greg Low

Why compression matters so much for SQL Server and BI

Level: Intermediate

01:15 PM   SQL Server MVPs

MVP Round Table Discussion

Level: Beginner

Peter Avenant

VARIGENCE AUSTRALIA – Build an Integrated Metadata Framework

Level: Beginner

     
02:15 PM   Paul te Braak

Analytical Data Mining – Using Data Mining to Understand Data

Level: Intermediate

Rod Colledge

Hour of Power BI

Level: Beginner

Warwick Rudd

Performance Eye for the SQL Guy

Level: Beginner

Konstantin Khomyakov

Data Quality Services (Deep dive)

Level: Intermediate

Rob Farley

The Incredible Shrinking Execution Plan

Level: Intermediate

03:45 PM   Rolf Tesmer

Leveraging SQL Spatial Analytics for Making Business Decisions

Level: Intermediate

Craig Bryden

Introducing Microsoft Power Query

Level: Intermediate

Victor Isakov

Understanding SQL Server 2014’s In-Memory OLTP Table Technology

Level: Intermediate

Martin Cairney

Automating Deployments with Powershell

Level: Advanced

Martin Catherall

Event notifications for the proactive DBA

Level: Intermediate

 

 

SSIS “Multiple-step OLE DB operation generated errors”

Product: SQL Server Integration Services SSIS

Package description: Processing text files by importing them into database. The final step in the process was to execute stored procedure to set various flags in the database for all file names processed.

 

SSIS control flow extract

 

I used “for each loop container” with ADO enumerator and Execute SQL task with OLE DB type connection.

For each loop container with ADO enumerator

 

SQL statement – direct input, stored procedure with 1 parameter where parameter mapped to the proper variable name & data type.

Problem:

Error: [Execute SQL Task] Error: Executing the query “EXEC dbo.StoredProcedire ?” failed with the following error: “Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.” Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

 

Possible Solution

In order for SSIS to properly map the query parameter to the variable, it needs the correct naming convention.  In my case, I’m using an OLEDB connection type so the first Parameter Name in the list it’s looking for is a 0

My confusion came that the parameter I used in SQL Task was assigned in For each loop container and it had index of 1.

So the set up should be like below to avoid any errors

 

Execute SQL Task parameter mapping

For Each Loop Variable Mappings

 

Here is the link from Microsoft technet site: http://technet.microsoft.com/en-us/library/ms140355.aspx

The parameter markers that you use in parameterized SQL statements depend on the connection type that the Execute SQL task uses.

Connection type Parameter marker
ADO ?
ADO.NET and SQLMOBILE @<parameter name>
ODBC ?
EXCEL and OLE DB ?

Parameter names that you use depend on the connection type that the Execute SQL task uses.

Connection type Parameter name
ADO Param1, Param2, …
ADO.NET and SQLMOBILE @<parameter name>
ODBC 1, 2, 3, …
EXCEL and OLE DB 0, 1, 2, 3, …

 

Other reasons this particular error could come up:

  1. Parameter data type is set incorrectly comparing with the datatypes of the parameters in the store procedure in the database
  2. Number of ? passed in the store procedure in Execute SQL Task don’t match with the number of parameters in the store procedure in the database
  3. Number of query parameters don’t match the number of variables mapped
  4. Order of parameters don’t match order of the variables

System.OutOfMemoryException Error in MS Reporting Services

A client was trying to view MS SQL Reportign Services report in the IE browser.

I received the error message looking like this:

reporting services error - outofmemory

That was not much help.

I logged onto my SQL Server 2008R2 and looked in Event Log and I found the following error message:

Exception information:

Exception type: OutOfMemoryException
Exception message: Exception of type ‘System.OutOfMemoryException’ was thrown

Reporting Service was not in running state either

 

Here what I found Microsoft website MS Knowledge Base Article 909678

You may receive the “System.OutOfMemoryException” error message when you use SQL Server Reporting Services

To resolve this issue, use one of the following methods.

Method 1

Add sufficient physical memory to the computer.

Method 2

Schedule reports to run at off-hours when memory constraints are lower.

Method 3

Adjust the MemoryLimit setting accordingly.

Note: When you render a report through the Reporting Services Web service, the Reporting Services Web service obtains the MemoryLimit setting from the Machine.config file. However, a scheduled report is rendered by the Report Server Windows service. The Report Server Windows service obtains the MemoryLimit setting from the RSReportServer.config file.

Method 4

Upgrade to a 64-bit version of Microsoft SQL Server 2005 Reporting Services to allow Reporting Services to utilize more memory.

Method 5

Redesign the report. To do this, use one of the following methods.

Redesign the report queries. You can reduce memory consumption by redesigning the report queries in the following ways:

Return less data in the report queries.

Use a better restriction on the WHERE clause of the report queries.

Move complex aggregations to the data source.

Method B

Export the report to a different format. You can reduce memory consumption by using a different format to display the report.

Method C

Simplify the report design.

You can reduce memory consumption by simplifying the report design in the following ways:

Include fewer data regions or controls in the report.

Use a drill through report to display details.

 

Here my Action Plan

1. Configure Max server memory for SQL Server

 

EXEC sp_configure 'show advanced options', 1

RECONFIGURE WITH OVERRIDE

EXEC sp_configure 'max server memory (MB)', 2048

RECONFIGURE WITH OVERRIDE

EXEC sp_configure 'show advanced options', 0

RECONFIGURE WITH OVERRIDE

Note: Only 4GB RAM was available

 

2. Set working set memory in reportsevice config file

Reporting Services can use all available memory, to override default behaviour you need to configure an upper limit on the total amount of memory resources that are allocated to Reporting Services server applications.

WorkingSetMaximum and WorkingSetMinimum define the range of available memory. This setting does not appear in the RSReportServer.config file unless you add it manually.

When the value for WorkingSetMaximum is reached, the report server does not accept new requests. Requests that are currently in progress are allowed to complete. New requests are accepted only when memory use falls below the value specified through WorkingSetMaximum

Add Line 3 to RSReportServer.config file (Line 1 & 2 should be in the file)

Line 1. <MemorySafetyMargin>80</MemorySafetyMargin>
Line 2. <MemoryThreshold>90</MemoryThreshold>
Line 3. <WorkingSetMaximum>3000000</WorkingSetMaximum>

3. Restart Reporting Service in Configuration Manager

 

No more OutofMemory errors

Introducing Microsoft SQL Server 2014

SQL Server 2014 was announced during Mcrosoft TechEd 2013 North America keynote.

The biggest selling points were

  • In-Memory capabilities built into the core database for OLTP and data warehousing. “Memory-optimized” tables are completely in memory and they don’t use pages for storage.
  • Increase performance by extending SQL Server in-memory buffer pool to SSDs
  • Hybrid Cloud Platform where cloud can be leveraged to extend the scalability and availability of on-premises database applications as well as reduce costs
  • Backup to Azure Storage and AlwaysOn integration with Windows Azure Infrastructure Services
  • BI Insights which simplifies access to internal or external data, analysing the data and unlocking it with powerful BI tools built into Excel and SharePoint.

It’s good to see SQL2014 is not for cloud only release. It brings new features and improvements for developers, administrators and power users. SQL Server 2014 will likely arrive early next year.

 

Sql Server Evolution through the ages

Ref: http://blogs.technet.com/b/dataplatforminsider/archive/2013/06/03/sql-server-2014-unlocking-real-time-insights.aspx

 

Read more about the new features of the SQL Server 2014:

Reporting Services migration and upgrade

There are two distinct options in updating SQL Server environment: upgrade and migrate.

  • Upgrade.It involves actual upgrade of technology on the servers and instances where they installed at the moment. Sometimes it’s called “in place” upgrade.To upgrade you need to run the SQL Server Installation Wizard. This will upgrade the report server program files, database, and all application data. You need to check what are prerequisites are, what versions and editions can be migrated. For SQL Server 2012 click here
  • Migrate. It includes more steps as upgrade as you need to install a new environment, copy your metadata and data to the new environment, and finally configure the new environment to use existing content.Before committing to migration review requirements to determine whether your hardware and software can support edition and version of SQL Server Reporting Services SSRS

Reporting Services migration checklist

  • Back up the encryption key. Use the Reporting Services Configuration tool to back up the key
  • Back up the report server database
  • Back up the following report server configuration files:
    • Rsreportserver.config
    • Rswebapplication.config
    • Rssvrpolicy.config
    • Rsmgrpolicy.config (Native mode only)
    • Reportingservicesservice.exe.config (Native mode only).
    • Web.config forthe report server ASP.NET applications).
    • Web.config for the Report Manager ASP.NET application (Native mode only).
    • Machine.config (for ASP.NET if you modified it for report server operations).
  • Move the report server database and other application files from your existing installation to a new SQL Server installation (use Install but do not configure option).
The easiest way to move the report server databases is to attach and detach them. However, this approach requires that you take the report server offline while you detach the database. Backup and restore is a better choice if you want to minimize service disruptions, use T-SQL commands script to backup and restore report server database and report server tempdb database
  • Configure the report server by following few easy steps
    1. Start the Reporting Services Configuration Manager and open a connection to the report server
    2. On the Database page, click Change Database. Click Next
    3. Click Choose an existing report server database. Click Next
    4. Select the SQL Server that now hosts the report server database and click Test Connection. Click Next
    5. In Database Name, select the report server database that you want to use. Click Next
    6. In Credentials, specify the credentials that the report server will use to connect to the report server database. Click Next
    7. Click Next and then Finish
  • Restore the encryption keys. This step is necessary for enabling reversible encryption on pre-existing connection strings and credentials that are already in the report server database.
  • Edit RSReportServer.config to include any custom settings from the previous installation
  • Optionally, configure custom Access Control Lists (ACLs) for the new Reporting Services Windows service group.
  • Test your installation
  • Remove unused applications and tools after you have confirmed that the new instance is fully operational

Note: A Reporting Services installation requires that the SQL Server Database Engine instance include the RSExecRole role. Role creation, login registration, and role assignments occur when you set the report server database connection through the Reporting Services Configuration tool.

 

Reporting services Migration tools

  1. Reporting Services Migration Tool.  It’s a Microsoft original tool. It provides UI and commnd line utility to migrates reports and other artifacts from one report server to another report server. It can also be used as a backup and restore tool for Reporting Services. Source and target server must be SQL Server Reporting Services 2008 R2 or later. Unfortunately the Target server must be SharePoint integrated mode only at the time of writing.
  2. Reporting Services Scripter. It’s a .NET Windows Forms application. It helps in scripting and transfer of all Microsoft SQL Server Reporting Services catalog items. It can also be used to easily move items on mass from one Reporting Services folder to another on the same server. Reporting Services Scripter can also transfer all catalog item properties such as descriptions, history options, execution options (including report specific and shared schedules), subscriptions (normal and data driven) and server side report parameters. Available only for SQL2000, SQL2005, SQL2008.
  3. Reportsync. The tool can be used to sync SSRS report between two report SSRS servers. Other use include download rdls from SSRS to local PC, upload files to a SSRS server, automatically attach datasources on upload