Migrate SSRS from SQL Server 2008R2 to 2014

I recently had a requirement to migrate a SQL Server instance from 2008R2 to 2014.  The core SQL Server functionality, the engine is always the easy part for me.  I don’t do much with SSRS, so moving the reporting services component was the big unknown.

So let me share with you my biggest take away from this project:  EVERYTHING ABOUT USER CREATED REPORTS IS STORED IN THE SQL Server Reporting databases.  So if you are reading this post you probably are about to move an instance of SSRS, and may be concerned about the many, many reports involved. 

Based on my one experience with this, there is need to move individual reports.  If you follow the process carefully, all of the existing reports will be re-created on the new machine.  It’s not quite magic, but it sure feels like it when everything shows up on the new system.

I didn’t figure this process out alone… Let me give credit to the two blog posts that I used to learn this process in the first place:

https://chayadigital.wordpress.com/2014/04/02/how-to-migrate-reporting-services-from-sql-server-2005-to-sql-server-2012/

http://www.sqlservergeeks.com/sql-server-report-server-migration-from-sql-server-2008-r2-to-sql-server-2012-with-subscription/

The core process of SSRS migration is in both of these posts.  Since they were migrating from 2005 and 2008R2 to 2012 some of the information is slightly different between the versions.

In my case, I migrated from 2008R2 to 2014. 

For the sake of this post I will use the convention of SOURCE to reference the system we are moving SSRS FROM and TARGET to reference the instance we are moving SSRS to.

In general, the high level process is:

  • On the SOURCE system:
    • Backup the SSRS database encryption key
    • Backup both of the SSRS databases
    • (Optional) Backup a small number of SSRS config files (just in case)
  • On the TARGET system:
    • Restore the SSRS databases from the SOURCE system.
    • Restore the SOURCE system SSRS Encryption Key
    • Start the SSRS service and…
    • Observer all of your reports and security settings, exactly as they were on the SOURCE machine.

I did skip over a few details in the above, so don’t use that literally to migrate your system.  The details of the process will follow… Be mindful that I will not show the steps on how to backup and restore a database.  Providing that level of detail takes the focus off of the “more interesting” SSRS components.

I’m also assuming that SSRS is installed and configured to run on the TARGET machine.  There doesn’t need to be any reports running or have any users configured.  It just needs to have an instance up and running.

In fact it’s worth noting that this process does not MERGE the SOURCE SSRS reports with the TARGET SSRS reports.  The end product of this process is a duplication of the SOURCE SSRS.

Here are my detailed notes/steps that I used to migrate SSRS from a SQL Server 2008R2 instance (SOURCE) to a SQL Server 2014 instance (TARGET).

One the SOURCE instance:

  • Using SSRS Configuration Manager, backup the database Encryption Key.  Make sure the backup is successful.  I intentionally chose a simple password (easy to remember) but it didn’t meet the password complexity policy, and failed the backup.

image

  • Take a FULL (COPY_ONLY works too) backup of the SSRS databases, ReportServer and ReportServerTemp (These are the default database names.  Your actual databases may be named differently).
  • There are a few XML Config files you may want to backup.  If these files are not in their default state you may need to reference these files in order to update the same files on the TARGET instance.  The files are in C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer.

    image

On the TARGET instance:

  • Using SSRS Configuration Manager, backup the TARGET SSRS encryption key.  Strictly speaking, this step is not necessary.  If something really bad goes wrong, and you can’t get the migration to work, you will need this key in order to restore the TARGET SSRS back to its original state.

 

    image

 

  • From SQL Server configuration manager (or Services.msc) shut down the SSRS Service.  In the next step we will rename the SSRS databases.  If we don’t shut down the service the rename (next step) will probably fail.
  • Rename the TARGET SSRS databases to something other than what they are now.  In theory we could over write them when we restore the SOURCE databases to this host, but if something goes wrong, we have the old databases ready to rename back.

 

    image

 

  • (Optional) Backup the SSRS configuration files.  Again, just in case something goes wrong.
  • Restore the SOURCE SSRS ReportServer and ReportServerTemp to the TARGET instance.
  • (Optional) Set the TARGET SSRS database compatibility level to SQL Server 2014.  By default, when restoring a database from an older version to a newer version instance, the restored database will still be running at the older version compatibility level.  What’s the point of upgrading if you’re going to stay at the older level?
  • alter database ReportServer set compatibility_level = 120;

    alter database ReportServerTempDB set compatibility_level = 120;

  • Start up the TARGET SSRS service. 
  • Using the backed up SOURCE encryption key restore the key in the TARGET SSRS Configuration Manager.  This step is critical, since it allows the SSRS service to read the database that was migrated over from the SOURCE instance.

 

    image[25]

At this point all of the reports and security settings should now be on the TARGET instance.  It’s magical how the details of those reports are stored as metadata in the SSRS databases.  By simply restoring the databases and providing the encryption key everything we had on the SOURCE SSRS instance is now on the TARGET SSRS instance.

Special thanks to my colleague Kaushal Kabariya (@kaushalkabariya) who started this whole process on his own and had to find out the hard way that report migration is in fact a DBA activity.

Stay curious; keep learning.

0 Comments

Leave a Reply

You must be logged in to post a comment.

Using Gravatars in the comments - get your own and be recognized!

XHTML: These are some of the tags you can use: <a href=""> <b> <blockquote> <code> <em> <i> <strike> <strong>

help with best essay online cheap dissertation proposal ghostwriting site for school literature review editor service usa best reflective essay ghostwriting website for college top article ghostwriter website for university custom creative writing editing service similarities and differences between xylem and phloem cheap content editing websites popular dissertation introduction writing site gb how did the constitution solve the problems of the articles of confederation esl dissertation results writer service gb best writing site for university cheap course work writing websites ca popular mba expository essay advice custom course work ghostwriting websites gb popular personal statement proofreading site usa custom book review writing services usa cheap article writing sites for mba write sociology movie review help writing education blog post five senses essay popular critical analysis essay writer service uk pay for my top expository essay online friends essay custom movie review writer site us best definition essay ghostwriting website for university cheap university essay ghostwriter service personal writer website biography editing sites uk cheap presentation editing services online students should not wear uniforms popular letter writers for hire for masters cheap book review ghostwriters for hire ca best school application letter samples pay to get nursing admission paper thesis proofreading sites best research paper editor sites gb custom personal statement writers sites top college essay writing site for school cheap dissertation methodology ghostwriters service uk best annotated bibliography writing sites us cheap descriptive essay writers website custom descriptive essay ghostwriters sites uk custom course work proofreading for hire uk cause of homelessness essay professional critical essay editor for hire us pay to write zoology dissertation conclusion shakespeare studies ghostwriting websites popular homework editor service us populism vs progressivism chart esl dissertation abstract writer websites au esl thesis ghostwriters for hire for university popular descriptive essay editing website online popular case study ghostwriters services for university custom essay ghostwriter websites for masters help with my remedial math bibliography cheap home work ghostwriter services for university communication essay example help writing geometry cv best case study editing website for university esl essay ghostwriting for hire gb esl essays proofreading site for university top course work writers sites online custom creative essay writers for hire for college esl cv ghostwriter site uk help writing calculus admission essay esl dissertation chapter editing websites for phd cheap custom essay writers website for masters cons of euthanasia cheap critical analysis essay ghostwriting site for college help me write human resource management speech top critical essay writer services ca popular assignment editing service uk pay to get esl reflective essay on usa write world affairs research proposal professional thesis statement editing service gb professional blog post writing site top dissertation results writing service us popular mba essay ghostwriters website gb persuasive essay proofreading website usa custom school essay writing services online best home work ghostwriting websites for mba professional descriptive essay writing website usa esl research paper writers sites gb popular dissertation writing services ca best creative essay editor service for school professional paper editing website for college cheap critical analysis essay editing sites cheap essays writers site dissertation introduction ghostwriter service gb write my religious studies cv top cv writing services us best dissertation abstract writing service online top personal essay ghostwriting websites uk pay to get environmental studies literature review top research proposal proofreading websites for mba help with my english as second language annotated bibliography custom bibliography writer for hire for college professional admission essay ghostwriter for hire uk popular dissertation abstract proofreading services for masters best creative essay ghostwriter services for masters cheap essay ghostwriter sites esl university essay ghostwriter services help writing women and gender studies course work help writing professional rhetorical analysis essay on lincoln esl homework writer service us help with best critical analysis essay on donald trump custom dissertation chapter ghostwriters websites for mba cheap biography ghostwriting for hire au top critical analysis essay ghostwriters service gb pay to do family and consumer science essay write my math letter cheap critical thinking ghostwriting for hire for college write my top book review online custom essay ghostwriter for hire esl definition essay proofreading services uk cheap curriculum vitae writer services uk academic essay on marketing best term paper writers for hire uk esl thesis statement writing websites au top report editor services ca professional writers service for mba help writing poetry admission paper popular expository essay writing sites for mba buy blog online professional papers writing services for masters custom annotated bibliography ghostwriters service us cheap creative writing writer service ca custom personal essay editing for hire for college reflective essay on trump cheap persuasive essay ghostwriting services for school custom dissertation results writer services usa best academic essay ghostwriting site usa top persuasive essay writer site gb esl literature review editor websites cheap critical essay writing websites custom school essay ghostwriter websites for mba cheap application letter ghostwriters service usa analysis essay on anthropology custom dissertation proposal writer websites for mba popular term paper proofreading site au popular thesis statement ghostwriter websites au professional dissertation introduction ghostwriters sites for college custom blog writer services online professional course work ghostwriters service for phd pay to do cheap argumentative essay on lincoln custom speech editor sites usa business writers site best papers writers website ca popular content ghostwriter for hire usa custom application letter writers websites for university top expository essay ghostwriters websites for masters esl university essay ghostwriting service usa professional analysis essay ghostwriter services usa cask of amontillado literary analysis esl phd essay ghostwriting websites usa buy ecology application letter type my top definition essay on founding fathers top blog ghostwriting websites au popular speech writer for hire for college popular dissertation editing site medicine essay writers services help writing top annotated bibliography cheap dissertation proposal writing site for college popular movie review ghostwriter websites for college professional movie review proofreading website for phd esl custom essay proofreading sites au pay to write human resource management report is angelina jolie gay cheap reflective essay ghostwriters service ca cheap college presentation example cheap biography ghostwriter sites for phd cheap personal essay editing for hire for college order political science presentation custom assignment ghostwriters site for college professional course work writing for hire for mba professional speech ghostwriter service for school popular papers ghostwriters websites gb best critical thinking writer sites for phd esl papers writing services online custom essay ghostwriting service au popular dissertation proposal editor sites gb best critical thinking ghostwriter website for phd do my esl cheap essay on hacking macduff family killed professional dissertation methodology ghostwriters sites for mba custom masters essay editing service usa popular creative essay editor for hire for masters pay for human resource management course work cheap course work editor service for mba custom phd essay editor service uk popular application letter writers websites for college esl dissertation proposal writer services for school popular papers ghostwriter site for masters custom dissertation abstract editing for hire for phd professional dissertation introduction proofreading sites us best speech writing site au professional home work ghostwriter site gb book review ghostwriting for hire usa best university dissertation chapter albert j. beveridge popular thesis ghostwriters website for school cheap business plan editing websites for university best resume ghostwriter sites uk pay for my best dissertation methodology professional course work ghostwriter website au best thesis writers for hire for school cheap writing services cheap term paper writing service for masters type my best rhetorical analysis essay on lincoln critical thinking ghostwriters service online popular persuasive essay editing websites au top essay writing sites au esl bibliography writing site for phd esl masters essay writing site for college friendship quotes in of mice and men order top content best papers writers site online professional university university essay advice top dissertation abstract editor services online critical editing sites uk popular persuasive essay writers service uk geology essay writers website pay to do professional phd essay on shakespeare best rhetorical analysis essay writers for hire for school best thesis statement ghostwriters service gb best reflective essay editing websites usa pay to do cheap application letter online top bibliography proofreading websites for phd popular creative writing editor services for school popular cheap essay writing websites for masters academic essay proofreading services usa cheap essays ghostwriter service usa custom blog editor for hire for school professional mba expository essay assistance professional bibliography editor services usa buy human resource management resume best analysis essay writers website online dissertation chapter writer site au write me family and consumer science article professional book review ghostwriters service for university application letter editing service uk popular masters article help after the first death summary top admission essay proofreading websites for school cheap analysis essay writing website best bibliography ghostwriting website uk custom descriptive essay writing sites gb dave barry the ugly truth about beauty write me law annotated bibliography cheap application letter ghostwriters service for mba popular definition essay proofreading for hire for school esl dissertation conclusion ghostwriter service for school cheap mba essay proofreading site ca criminal law essay ghostwriters for hire custom rhetorical analysis essay editor websites au esl writer site for phd analysis editor service dead poets society mr keating cheap biography ghostwriter website top personal statement writers site for mba best best essay writer websites for phd professional article editing site us cheap custom essay ghostwriters sites us best dissertation proposal proofreading service ca popular dissertation chapter editing services uk top letter writer sites for phd cheap dissertation introduction proofreading site uk esl college essay editing service online buy cheap creative essay online best blog writing sites uk dissertation chapter editor for hire pay for cheap university essay on presidential elections popular article review editor for hire gb do my tourism report cheap term paper writer website for phd top home work editing websites for mba write my literature review professional speech ghostwriters services uk best creative writing ghostwriters services for mba professional school scholarship essay examples resume editing websites gb top dissertation abstract proofreading website for mba esl masters dissertation introduction sample best dissertation introduction writer sites for phd custom literature review writer site usa write top personal essay on shakespeare personal statement writer services uk write me medicine blog help with my zoology home work pay for critical analysis essay online help with my custom annotated bibliography popular research proposal writing website gb custom dissertation chapter ghostwriters services help with technology movie review best business plan writers websites for mba rhetorical analysis writer website us cheap phd paper sample professional papers editing service au popular resume ghostwriters service for college professional application letter writer service ca top custom essay editing site for mba custom business plan writer website ca custom personal statement editor websites gb do my custom college essay on trump summary of i have a dream speech type my government dissertation pay for my esl creative essay on shakespeare analysis of a rose for emily popular essay ghostwriter service uk custom business plan writers site uk professional dissertation conclusion writer sites usa professional content proofreading website au presentation writer for hire us esl critical analysis essay ghostwriter for hire for masters thesis writing for hire usa cheap annotated bibliography ghostwriter site us professional college essay writer websites for phd esl essays editing websites gb popular letter proofreading for hire us popular cv writing sites for phd write me composition bibliography esl personal essay ghostwriting website uk help with criminal law essay top college essay proofreading services for phd popular academic essay writing services for mba popular thesis statement ghostwriter service for college custom dissertation proposal writing service uk top dissertation methodology writers site for college professional dissertation results writer site us cheap case study writing site usa essay ghostwriters site ca critical analysis essay ghostwriter service uk best dissertation methodology writer service online cheap research paper ghostwriters websites au best case study editing services au professional business plan ghostwriters sites for school popular essays writers service usa help me write esl cheap essay on donald trump custom thesis proposal ghostwriting site for school esl admission essay ghostwriter websites for phd pay to do zoology dissertation introduction cheap content ghostwriting for hire for masters custom blog writing services online custom definition essay ghostwriting sites for phd professional dissertation methodology ghostwriters site for phd esl best essay writing site for school cheap admission paper proofreading website for phd funny boy book professional essay editor service ca pay to write cheap rhetorical analysis essay custom expository essay ghostwriters sites au order earth science report top annotated bibliography editor sites ca esl cover letter writing website for university top thesis ghostwriters services ca esl rhetorical analysis essay ghostwriters sites for college top personal statement ghostwriting site ca professional personal essay writing website uk top dissertation writing service ca popular thesis proposal editor site write cheap admission essay on trump cheap expository essay editing for hire ca best expository essay writers websites usa best dissertation proposal writer services usa significance of blood in macbeth custom argumentative essay writing websites for school top dissertation methodology writing for hire us best admission essay writers for hire for school help writing best admission essay on presidential elections esl homework proofreading websites au help with medicine papers custom definition essay proofreading service us best cover letter writer sites ca help me write speech admission essay custom critical analysis essay editor sites for university professional presentation editor website for college was hamlet insane cheap scholarship essay editor site gb papers writer services online critical editor sites usa type my esl problem solving online top personal essay ghostwriters services us funeral blues by wh auden write me ancient civilizations personal statement professional dissertation methodology editing website ca order cheap school essay on civil war professional research paper writers service for phd popular blog writers sites au write creative essay on founding fathers popular best essay writer site us best essay editor services gb pay to write finance thesis statement professional curriculum vitae editing websites ca best cover letter ghostwriter websites us professional dissertation proposal writing service gb order botany course work top essays ghostwriting website au characteristics of roman art top application letter writers site au the diary of a napoleonic foot soldier custom descriptive essay writing sites for mba order cheap university essay on hillary cheap admission essay writer service for phd evolution paper topics sonnet 130 shakespeare analysis best creative essay writers sites usa esl thesis writer services for phd critical ghostwriting website gb cheap academic essay ghostwriting services us professional scholarship essay writer for hire online fast food and american culture professional curriculum vitae writing services for university custom college essay proofreading for hire us custom persuasive essay ghostwriting for hire gb the american dream 1930s pay to write medicine research paper dissertation results writers websites gb top definition essay editing websites for phd help me write engineering thesis cheap essay on engineering esl speech writer website for university esl persuasive essay ghostwriters service for college buy social studies presentation best personal statement editor sites professional blog writer website for university sample leadership essay popular creative writing writing site online cheap letter editing sites ca popular speech ghostwriters website for university buy cheap critical analysis essay cheap masters essay editing sites descriptive writing sites us best dissertation methodology writer websites uk pay to write government critical thinking top movie review writers service ca help with top custom essay on hillary clinton esl cheap essay ghostwriter websites usa pay to do esl report online top writing services for school cheap critical analysis essay ghostwriting sites usa cheap course work ghostwriters for hire type my masters essay on hillary top blog ghostwriting services for college cheap resume editor websites online popular thesis proofreading site for masters custom presentation writing service for phd dissertation ghostwriters websites uk top creative essay writers services gb top dissertation proposal ghostwriter websites gb pay to get women and gender studies research paper cheap article review writers services for university pay to do popular custom essay on founding fathers esl bibliography ghostwriter services popular content ghostwriters services uk cheap school essay proofreading sites for mba top research proposal writer sites for college top admission paper ghostwriting websites for masters esl dissertation hypothesis writing for hire gb custom descriptive essay proofreading sites gb popular scholarship essay proofreading website for university cheap creative essay ghostwriter services usa esl rhetorical analysis essay ghostwriters sites gb cheap definition essay writer service for phd help writing professional persuasive essay on hillary clinton custom rhetorical analysis essay editing site for phd professional university essay ghostwriting websites au cheap biography ghostwriter service ca papers ghostwriting sites online position essay example stanley streetcar named desire best dissertation proposal ghostwriter sites for college cheap curriculum vitae editor website for school best analysis essay writing websites for phd order custom descriptive essay on hillary custom dissertation hypothesis ghostwriters service online best article writers site for phd custom thesis writers websites au top resume editing site for college top dissertation chapter writers services for phd buy tourism dissertation methodology tv benefits top expository essay writer websites custom course work writing websites for school custom thesis statement writer for hire for school esl presentation writing services cheap descriptive essay writers services for university esl descriptive essay ghostwriter site ca rei human resources top scholarship essay writer site for phd write me popular best essay help with school essay on usa order professional case study online cheap reflective essay editor services for university top academic essay writers service us custom school personal essay topics popular masters school essay sample help me write definition essay on brexit best case study writers service for masters best content proofreading site for university