Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.
Implementation:Migrating SQL server reports from from one server to another server.
Scenario: Lets say,Client wants to migrate their Reports from one server to another server and you are asked to carry out this activities.In this case below steps going to help you lot.
Infrastructure: Here in my case,I have report on SQL server 2008 which I have to migrate it to SQL server 2012.
- First have a look on the report server services status on new server.If it is not running then start the reporting service and make the start-up type automatic.
- Now go to new server itself and open the report server configuration manager for configuring report server.
- Select the SQL server and connect it.
- Here you will see the reporting configuration manager which configure,modify the report server settings.
- On the first page,you can see the sql server instance,Instance Id,Edition,Product version and reporting server status.
- Remaining field will get added after configuring report server.
- Go to service account page,and here you can use built-in account or any domain id for authentication and rights
- Here,I am going to select local system account.After selecting option don’t forget to click on apply button.
- Now,go to web service URL,here you can specify the URL for your reports also you can specify more than one URL for a single report by clicking advance option on this page.It asks for the default URL you can apply this too.
- Go to database page,here you can specify the database for report service.This step is quite interesting,you can walk-through the wizard and assign the database for report server else select the default database for reporting server example,reportserver & report servertempdb.
- Though the reports are present on .rdl file still I would recommend you to restore your previous report server database on new server and select this database as the report server database and den use .rdl file for migrating.
- Go to report manager URL and click on apply button.this will show the folder content on your url for managing the reports using web browser.
- Go to email-setting page,here you can specify email address for reports.
- Go to execution account page,keep this blank.
- Now the next page is about encryption key.The reporting services use the symmetric key to encrypt the credentials,connection string and other important data.The important data present inside report server assigned database.Here in my case it is reportserver database.You can read more about the symmetric key on google.
- If you using this feature don’t forget to take backup of key.This key will be asked if you migrate your reports to another server.
- Last page is scale-out deployment page,the report server which is joined to the deployment that can use the common report server database for encrypted data stored.
- Now open the URL in to the web browser and see whether everything is done properly or not.
- If It works properly you will be able to see something like below screen shot
- Up to here the reporting services have been configured.now lets look how to migrates your report on this newly server.
- For doing so, copy the .rdf(Report definition file) from old server to new server.
- Please note that migration of reports is possible to forward compatibility mode.
- It means,the sql server low version can be migrated to higher version not vice-versa.
- After copying all .rdf file to newly server,go to report server url(ref:above screen shot).Here you will see upload file option.This option is used to upload the .rdl files.Browse the .rdl file and hit ok button.
- After uploading .rdf files and when you open the report you may come across the below error
- This is the common error face by people while migrating the Reports to another server.Error here is basically occurred due to data source.Since the reports server .rdf file is looking for the data source of previous server which is not in this case.So here you have to change the data source to newly server.Simply go to report,drop down and the list will get display.select the manage option.select the data source page and put the connection string of newly server.now test the connection.The connection test must be successfully.Now apply the changes.
- Next,go to home folder.select the report and open it.This time error will be gone.
- So it is done.You have successfully migrated report server to newly server.Thus,if the .rdf files are more you can change their data source.one more question arise here,lets say you want to edit in to report then how to achieve this.lets have a look.
- Go to URL home folder their is reports builder option,using this tool you can edit or build new reports.
- Report builder tool helps you to manage the reports.The new reports can be created as well as modification,uploading done.Lets say,you want to open newly server reports.Give the path of .rdl file and open it.Once recheck your data source property.
- It is done..Hope this tutorial will help you while migrating reports.Please comment or give any suggestion.Any right suggestion will be welcomed.
- Good Luck..