Migrating Sql Server Reports from one server to another server.

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.

Dear Visitors!
To get Best Deals and Discounts Buy Now from Amazon just by
Clicking This Image

Simply send snap of your product details after delivery at ad22092012@gmail.com for more surprises or additional discounts

 

Infrastructure: Here in my case,I have report on SQL server 2008 which I have to migrate it to SQL server 2012.

Steps

  • 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.

services

  • Now go to new server itself and open the report server configuration manager for configuring report server.

rserverconfigmanager

  • Select the SQL server and connect it.

reportconnect

  • 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

servicesAccont.

  • 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.

webserviceurl

  • 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.

database

  • 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.

rmanagerurl

  • Go to email-setting page,here you can specify  email address for reports.

email

  • Go to execution account page,keep this blank.

exeaccount

  • 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.

encryptionke

  • 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.

scale-out

  • 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

webbrowser

  • 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.

browserdl

  • After uploading .rdf files and when you open the report you may come across the below error

errorpage

  • 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.

datasource

  • Next,go to home folder.select the report and open it.This time error will be gone.

reports

  • 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.

builder

launchbuilder

  • 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.

final

  • 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..

Monitoring Multiple Servers from Master SQL Server

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 :How to monitor multiple sql servers from any one of the master server. Solution:First of all let us see what is it..

Scenario:Let’s say you are at client site and suddenly you are asked to create no of same SQL jobs and Maintenance plan on multiple server which will do the same on each servers.This can be achieved through Multi server administrator option available in sql server.

Dear Visitors!
To get Best Deals and Discounts Buy Now from Amazon just by
Clicking This Image

Simply send snap of your product details after delivery at ad22092012@gmail.com for more surprises or additional discounts

 

Infrastructure: Here in my case there is two instances one is default and other is named instance on same machine. Both instances are sql server 2008  standard edition.

Steps: Open the any one of the sql server database that can be said later Master sql server which will be using as monitoring server for other sql servers.

  • Open the SSMS
  • Go to menu bar and view and select registered server or simply type ctl+Alt+GRegisteredServer
  •  Go to local server groups.right click on it and select new server registration.NewserverRegistration
  • Here you will see new server registration window.

NewserverRegistrationWizards

  • Enter the server name in to the group from drop down option.test the connection and save it.
  • You can add multiple server in this group for querying tsql at one place.
  • After the server registration you will see the added servers list on left side of the SSMS.
  • Right click on Local server group and select new query and just check the output.What ever queries will be fired that gives the output for all added sql servers.

After Registration

  • Now Connect the SSMS and make one server as a Master Multi server administrator

Multiserver

  • Master Wizard will get open here.

MasterWizards

  •  Above Wizard will help you to make it master server.With the help of this server you can make jobs and maintenance plan for multiple servers plus notification alerts as well.
  • Master server operator will be created on master server and each of the target server.
  • Specify the email address for getting multiserver jobs alerts.

NextMaster

  • Add target server here.

Addtarget server   Adding Target

  • You can add more target server here from ‘add connection ‘ button.
  • Next, sql Server will check  the versions of the master and target server are compatible.

VersionCheck

  •  On the Master Server Login Credentials screen security is checked between the master and target servers. If needed, the check box can be selected to create a login and assign it rights to the master server. Click the ‘Next’ button to continue the process.

Login check

  • It will start the configuring the servers.Here you may get the following error while finishing the wizard.

FinishError LocalSystemError

  • To resolving the issues go to sql server configuration manager and change the log on account for all sql agent to domain\user format.

Configuration

  • Also one more you can see here

enlist error

  • To resolve this error you are supposed to go to target servers registry.
  • Please take the backup of registry before making any changes over here.
  • Go to target server open run and type ‘regedit’ and press enter button.Follow the below path given in screen shot and change value for ‘MsxEncryptChannelOptions’ value to ‘0’.

enlist error regedit

  • Now Finish the Configuration Wizards.
  • Go and connect the all instances including target server and Master server.
  • Now to test it,create sql Job on master server.

createJob

  • Create a sql job and here you will see one more option on left page i.e. targets.Now go to this page and add the listed target servers.

TargetJob

  • Here you will see the same job on all target servers.
  • You can Implement the Maintenance plan as well.
  • So done!!!!!
  • Good Luck……….