Showing posts with label Reporting Services. Show all posts
Showing posts with label Reporting Services. Show all posts

Thursday, April 2, 2015

Using RS Scripter to create deployment script for reporting services

If you are using SSRS then chances are high to come across a scenario where you want to deploy reports developed on development machine to the production server, there are various ways to do this and one of them is to use RS Scripter tool. Here are the steps to do this -

1. Creating Report Deployment Script

  1. Download the Reporting Services Scripter - http://www.sqldbatips.com/showarticle.asp?ID=62
  2. Update the servers.xml; change the reportservice value if your ReportServer name is different.


  3. Run the RSScripter.exe.
  4. Click on Options, and select the options selected in images below( for more information about Scripting Options check the readme file - http://www.sqldbatips.com/samples/code/RSScripter/readme.htm#_scripting_options) – clip_image001
    clip_image002
    clip_image003
  5. Change the SQL 2005 RS EXE Location in the Global tab as per the location of SQL server and Apply – clip_image004
  6. Select SQL 2005 from the Report Server dropdown and click on Get Catalog, all the reports, data source, Schedules, Roles etc. present in report server will be displayed –
    rs1
  7. Select all the reports, data source, schedules and roles you want to transfer.
    rs2
Change the output directory path (Should not be machine/user specific e.g. Desktop) e.g. C:\AClickReports and click on Script. The script for loading the specified items will be generated to the specified folder.

2. Deploying the Reports


  1. Ensure that you have IIS and dot net framework 2.0 is installed on report server.
  2. While installing SQL server 2005 select reporting services option.
  3. Follow the report server configuration steps and make sure "Report Server Configuration Manager" is all green ticks? clip_image008
  4. Follow these steps to publish reports –
    1. Extract the AClickReports folder to the report server, C: Drive.
    2. Open the “RS Scripter Load All Items.cmd” file located in AClickReports folder for editing.
    3. Change the SCRIPTLOCATION Script variable value to the location where AClickReports folder is copied (Should be same to the output directory path selected in step 3.8)
        SET SCRIPTLOCATION=C:\ AClickReports\
      clip_image009
    4. Change the RS Script variable value to the location where Microsoft SQL Server is installed (if it is different from the default C drive).
      SET RS="C:\Program Files\Microsoft SQL Server\90\Tools\Bin\RS.EXE"
    5. Run the RS Scripter Load All Items.cmd batch file, it will publish the reports to the report server.
  5. Follow these steps to verify that reports are deployed correctly
    1. Go to http://localhost/Reports/Pages/Folder.aspx, https://mscblogs.blob.core.windows.net/media/akjoshi/Media/clip_image011_4E8F2F96.jpg
    2. Open the Data Sources you should see data sources selected in step 1.7 https://mscblogs.blob.core.windows.net/media/akjoshi/Media/clip_image013_0EE9F466.jpg
    3. Open the data source and update the data source details as per the configuration of report server(if they are different) and apply. https://mscblogs.blob.core.windows.net/media/akjoshi/Media/rs3_0F7AD2B0.jpg
    4. Go back to Home page and click on the report folder selected in step 1.7 , select one of the reports and click on Edit button, then click on the Data Sources link ; Make sure that the data source is configured properly. https://mscblogs.blob.core.windows.net/media/akjoshi/Media/clip_image017_5D3E3B36.jpg
    5. Repeat the steps from 2.5.2 to 2.5.4 for other data sources and report folders.

Tuesday, July 22, 2014

Migrating Reporting Services Data

Reporting Services defines a migration operation as moving application data files from an existing installation to a new installation. Often, migration includes upgrading to the newest version of the software or to new hardware.

If you are migrating from SQL Server 2005 or 2008 Reporting Services to a new Microsoft SQL Server Code-Named “Denali”, Community Technology Preview 3 (CTP 3) instance, you can usually run Setup to upgrade the report server program files, database, and all application data. However, migrating a report server installation manually is required if you encounter any of the following conditions:
  • Upgrade Advisor detected one or more upgrade blockers.
  • You want to minimize the time the report server is taken offline. Your current installation remains online while you copy application data to a new instance and test the installation as an independent operation.
Migrating to Microsoft SQL Server Code-Named “Denali”, Community Technology Preview 3 (CTP 3) offers significant benefits in deployment and processing, but you should be aware that not all functionality from previous versions is available in Microsoft SQL Server Code-Named “Denali”, Community Technology Preview 3 (CTP 3). Specifically, if you are using a third-party single sign-on technology solution or custom features that integrate with Reporting Services through Internet Information Services (IIS), your current solution will not work on a new report server installation. You will have to redesign your solution for a Microsoft SQL Server Code-Named “Denali”, Community Technology Preview 3 (CTP 3) report server.
Before you migrate, evaluate the new software and test reports and other items to ensure your business requirements are met. The following links provide information to help you determine a migration strategy:
For step-by-step instructions on how to move application data to a new Microsoft SQL Server Code-Named “Denali”, Community Technology Preview 3 (CTP 3) report server instance, see How to: Migrate a Reporting Services Installation.

You can move a SQL Server Code-Named “Denali” Reporting Services (SSRS), Community Technology Preview 3 (CTP 3) Reporting Services installation to a new or different computer by installing a new report server instance and moving the report server database to the new instance. For more information about how to move the database, see Moving the Report Server Databases to Another Computer.
When moving application data, you should be aware of the following concerns and restrictions:
  • Protection of encryption key includes a hash that incorporates machine identity.
  • Report server database names are fixed and cannot be renamed on new computer.

Encryption Key Considerations

Always back up the encryption keys before moving a report server database to a new computer.
Moving a report server installation to another computer will invalidate the hash that protects the encryption keys used to help secure sensitive data stored in the report server database. Each report server instance that uses the database has its copy of the encryption key, which is encrypted with the identity of the service account as it is defined on the current computer. If you change computers, the service will no longer have access to its key, even if you use the same account name on the new computer.
To re-establish reversible encryption on the new report server computer, you must restore the key that you previously backed up. The complete key set that is stored in the report server database consists of a symmetric key value, plus service identity information used to restrict access to the key so that it can be used only by the report server instance that stored it. During key restoration, the report server replaces existing copies of the key with new versions. The new version includes machine and service identity values as defined on the current computer.

Fixed Database Name

You cannot rename the report server database. The identity of the database is recorded in report server stored procedures when the database is created. Renaming either the report server primary or temporary databases will cause errors to occur when the procedures run, invalidating your report server installation.
If the database name from the existing installation is not suited for the new installation, you should consider creating a new database that has the name that you prefer, and then load existing application data using the techniques in the following list:
  • Write a Visual Basic script that calls Report Server Web service SOAP methods to copy data between databases. You can use the RS.exe utility to run the script. For more information about this approach, see Scripting (Reporting Services SSRS).
  • Write code that calls the WMI provider to copy data between databases. For more information about this approach, see Access the Reporting Services WMI Provider.
  • If you have just a few items, you can republish reports, report models, and shared data sources from Report Designer, Model Designer, and Report Builder to the new report server. You must re-create role assignments, subscriptions, shared schedules, report snapshot schedules, custom properties that you set on reports or other items, model item security, and properties that you set on the report server. You will lose report history and report execution log data.

Migrating Reporting Services Data

Reporting Services defines a migration operation as moving application data files from an existing installation to a new installation. Often, migration includes upgrading to the newest version of the software or to new hardware.

If you are migrating from SQL Server 2005 or 2008 Reporting Services to a new Microsoft SQL Server Code-Named “Denali”, Community Technology Preview 3 (CTP 3) instance, you can usually run Setup to upgrade the report server program files, database, and all application data. However, migrating a report server installation manually is required if you encounter any of the following conditions:
  • Upgrade Advisor detected one or more upgrade blockers.
  • You want to minimize the time the report server is taken offline. Your current installation remains online while you copy application data to a new instance and test the installation as an independent operation.
Migrating to Microsoft SQL Server Code-Named “Denali”, Community Technology Preview 3 (CTP 3) offers significant benefits in deployment and processing, but you should be aware that not all functionality from previous versions is available in Microsoft SQL Server Code-Named “Denali”, Community Technology Preview 3 (CTP 3). Specifically, if you are using a third-party single sign-on technology solution or custom features that integrate with Reporting Services through Internet Information Services (IIS), your current solution will not work on a new report server installation. You will have to redesign your solution for a Microsoft SQL Server Code-Named “Denali”, Community Technology Preview 3 (CTP 3) report server.
Before you migrate, evaluate the new software and test reports and other items to ensure your business requirements are met. The following links provide information to help you determine a migration strategy:
For step-by-step instructions on how to move application data to a new Microsoft SQL Server Code-Named “Denali”, Community Technology Preview 3 (CTP 3) report server instance, see How to: Migrate a Reporting Services Installation.

You can move a SQL Server Code-Named “Denali” Reporting Services (SSRS), Community Technology Preview 3 (CTP 3) Reporting Services installation to a new or different computer by installing a new report server instance and moving the report server database to the new instance. For more information about how to move the database, see Moving the Report Server Databases to Another Computer.
When moving application data, you should be aware of the following concerns and restrictions:
  • Protection of encryption key includes a hash that incorporates machine identity.
  • Report server database names are fixed and cannot be renamed on new computer.

Encryption Key Considerations

Always back up the encryption keys before moving a report server database to a new computer.
Moving a report server installation to another computer will invalidate the hash that protects the encryption keys used to help secure sensitive data stored in the report server database. Each report server instance that uses the database has its copy of the encryption key, which is encrypted with the identity of the service account as it is defined on the current computer. If you change computers, the service will no longer have access to its key, even if you use the same account name on the new computer.
To re-establish reversible encryption on the new report server computer, you must restore the key that you previously backed up. The complete key set that is stored in the report server database consists of a symmetric key value, plus service identity information used to restrict access to the key so that it can be used only by the report server instance that stored it. During key restoration, the report server replaces existing copies of the key with new versions. The new version includes machine and service identity values as defined on the current computer.

Fixed Database Name

You cannot rename the report server database. The identity of the database is recorded in report server stored procedures when the database is created. Renaming either the report server primary or temporary databases will cause errors to occur when the procedures run, invalidating your report server installation.
If the database name from the existing installation is not suited for the new installation, you should consider creating a new database that has the name that you prefer, and then load existing application data using the techniques in the following list:
  • Write a Visual Basic script that calls Report Server Web service SOAP methods to copy data between databases. You can use the RS.exe utility to run the script. For more information about this approach, see Scripting (Reporting Services SSRS).
  • Write code that calls the WMI provider to copy data between databases. For more information about this approach, see Access the Reporting Services WMI Provider.
  • If you have just a few items, you can republish reports, report models, and shared data sources from Report Designer, Model Designer, and Report Builder to the new report server. You must re-create role assignments, subscriptions, shared schedules, report snapshot schedules, custom properties that you set on reports or other items, model item security, and properties that you set on the report server. You will lose report history and report execution log data.