Migrating SSRS power bi version 15 database to new server

  • Hello,

    I seen many articles, one such as this:


    and its pretty straight forward, backup database, restore, backup keys, restore, and point database to new location... got it... my biggest concern... and worry especially to the user, is the subscriptions... will the subscriptions come over to the new server.. I have about 100+ subscriptions, of course that means 100+ sql agent jobs on the database server that I am migrating from on-prem to another on-prem location... any idea how I can accomplish and make sure, all reports work, users are able to view reports, and most importantly subscriptions continue as is without having to recreate 100+ subscriptions/sqlagent jobs on the new database server that will have the new SSRS power bi server version 15?


    thanks in advance

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • If I remember right, jobs do not migrate over when you do that. BUT since the old system is still available, just script out the jobs and run that script on the new server. You will also want to script out any other system level objects (logins and database mail for example) to make sure you don't suddenly have loss of access or functionality from some users.

    Now, with the above being said, I would STRONGLY encourage you to do a trial run of the migration and see what works and what doesn't. Test things out. Try it. Put the new system into an isolated VLAN, migrate data and keys, then check it out. Keep email disabled initially to prevent spamming everyone. Then test things.

    The last thing you want is to do the migration and find out that something didn't transfer nicely and you have broken reports to hand over to your end users. My approach with things like this is to do a trial migration, have end users validate their reports look right, have a DBA validate that the system objects look right and fix anything that looks wrong with a TSQL script, then schedule some downtime to do the live migration.

    I strongly recommend that you do a test migration before live so you can catch all of the errors, issues, and hiccups. I have 1 SSRS instance that I manage where I can't set the SQL Service to 1 specific AD user. If I set it to that user, it fails to decrypt the keys no matter what I do. If I pick any other AD user though it works fine. AND that AD user works fine on my test SSRS instances, just not live. Been fighting that one for a few years before I gave up.

    But test test test. Don't do a live migration until you are 100% confident with the test. If you go live without doing proper testing, you are likely going to get those 2:00 AM phone calls about a report being broken or the calls from a C-level employee telling you their report they show to the president of the company is busted and you have to fix it in the next 5 minutes.

    Test, find the problems and script fixes, and document the entire thing so that you know the exact steps that need to be done for your specific environment. Are there additional pre or post go live steps that you need to do? For example, do you have any failover software that needs to be dealt with before you turn off the old instance? Do you have any firewall rules that need to be put in place before you turn on the new instance? What will happen if a personal emergency comes up prior to go live and you need to hand this over? Will go live be delayed OR will you hand it over to another to work on? If handing it over, documentation will save them hours of work. Testing and documentation will ensure that you have a smooth process.

    Did I mention that you should do a test before doing this live?

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply