Migrating SSRS from 2014 to 2019
-
I'm currently migrating a 2014 SQL Server to 2019. Currently, we have a 2014 server name MY_SQL_SERVER and 2019 with server name MY_SQL_SERVER_NEW. Eventually, this 2019 server will be renamed MY_SQL_SERVER and the 2014 will be decommissioned.
I installed SSRS application in 2019 but have not configured it yet. I'm afraid if I do a DB restore then configure SSRS, some issue will come up when the server is renamed.
Any tips on SSRS migration?
TIA
Alex
-
My first thoughts on this are that renaming a SQL instance is tricky and can be problematic. I would recommend picking a name that works for the new server if you can rename it to a new name.
What I mean by if you can is if you don't have any applications or tools that rely on the instance name (not the URL for the web site, but the actual instance name). If an application or tool of some sort relies on the SQL Instance name, then you don't have a lot of options, but I'd still be more willing to reconfigure a few things than fight with the pain that can come with an instance rename.
If you know the risks and process to rename an instance (if memory serves, it requires registry changes), then go for it. If the SQL 2019 is going to exist on the same physical machine as the 2014 AND you plan to rename the 2019 version to match the 2014 version you will need to uninstall the 2014 version to rename the 2019 one which is another reason I'd avoid the rename approach. If you don't rename, you can run these 2 side-by-side on the same server (I think).
As for issues that may come up when you restore the database and rename the instance, the rename shouldn't cause any issues with the user database name.
TL;DR version - I wouldn't rename the SQL instance as it is a pain in the butt and can cause some weird issues. I would give it a new name and short term run the 2 side by side so you can identify issues before doing a full on cutover from 2014 to 2019.
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. -
Yes, it would have made my life easier if there will no renaming. I wish that was a case. 🙁
Unfortunately, the SQL Server is referenced by applications running on an old Sharepoint (2010) intranet.
One good thing about this migration is that I'm migrating the DEV environment which would act as a blueprint to migrate the PROD. So any mistakes I make would help me not to make the same for PROD migration.
-
Is sharepoint referencing the SQL Instance directly, or is it referencing the web interface? If it is referencing the web interface, then you don't have much to worry about as you can make that work pretty easily. If it is referencing the SQL Instance itself, then things may be a bit more complicated and your renaming the instance will be required.
What I would do if it was me though, is install 2019 with the new name, migrate stuff from 2014 to 2019, uninstall 2014, install a fresh 2019 with the proper name, migrate from the older 2019 to the new 2019. It is more steps and more work short term, but saves you having to rename the instance which may or may not go smoothly.
I've just not had much fun with renamed instances. The old name seems to appear in the strangest places. And when I did that rename years back, it was JUST a SQL Instance, no SSRS or SSIS or SSAS tied to it as well. I read all the advice about renaming and decided to risk it anyways because I thought "how hard/bad can it really be?". Learned the hard way why Microsoft doesn't support it nativly as I ran into all sorts of odd issues. Don't remember the issues offhand; it was several years ago and the notes I made to myself say "don't rename unless you have no other options".
Most, if not all, advice I could find at the time was to NOT rename a named instance of SQL Server, but to do a fresh install if you need to rename it. Doing a quick look now, I find very similar advice.
Please do post back on the status of the upgrade and what you end up doing as I am curious if you have better luck than I did. I ended up doing a fresh install and migrating to a new named instance and just updated our tools to point to the new named instance.
Another thing to note is that Sharepoint 2010 and SSRS 2019 does not appear to be a supported combination:
Looking at that link, SQL Server 2014 is as high as you should go if you have SharePoint 2010.
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. -
Thanks for the input and info on SP as well.
As it is right now, I took a SNAPSHOT of the 2019 server. Then I started migrating the 2014 - 2 reporting DBs and configured it. So my SSRS is up and running on 2019 but I still have to do some checks.
-
We did the cut-over yesterday and so far so good. Currently monitoring.
https://www.sqlservercentral.com/blogs/rename-or-change-sql-server-standalone-default-instance
After renaming the HOST and the SQL SERVER with default instance (used link above), I found that:
- SSRS subscriptions will be re-created on the JOB list once you restart SSRS service. It will have the same Job name as with the old server. I did this before the rename. No effect after the rename.
- SSRS needs reconfiguration because it was pointing to the DB of the server name with "NEW" in it.
- SSRS needs reconfiguration on Scale-Out-Deployment because it has 2 records pointing to the same server after rename. Deleted one but really don't know if it has any bearing.
4. I had issues creating/editing subscriptions when I configured SSRS (pre naming). I was getting this error below. Fix was to make the SSRS service account have sysadmin role.
SSRS Subscription: An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database.
- Maybe I didn't check this before but I have LINK SERVERS configured. On 2 of my LINK SERVERS, intellisense is not working for some reason. I can read data from the LINK SERVER but I have the squiggly lines. I don't know why it is not working even with a intellisense cache refresh.
-
I am glad it worked for you!
For me, intellisense has never worked well over a linked server or even cross-database on the same instance. Sometimes it gives me the list of objects, sometime it doesn't. But every time it tells me that the object doesn't exist (red squiggly line under the object name).
Also, thanks for the follow up! This will help anyone else who tries a similar thing in the future!
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. -
This was removed by the editor as SPAM
-
UPDATE:
- Looks like there is an issue with the SSISDB after migration. Long story but to make it short, we had a checklist and someone did not do theirs and just signed off. Having said that, we found out today an issue where deploying SSIS packages is failing. I think it is closely related to this issue and a Database Master Key (resolved DMK using the backup and restore scripts on the link given).
- or it could be related to VS 2017 (SSDT) because you have no option to deploy to SQL 2019.
Research in progress....
-
UPDATE:
With VS 2017 SSDT, uninstalling SSDT and reinstalling it (latest version standalone) adds SQL 2019.
SQL Server 2019 is supported starting from SSDT version 15.8.1. Doing an SSDT update did not add SQL 2019 in our case, only uninstall and reinstall worked.
-
UPDATE on SSISDB issue:
This was the error I'm getting:
TITLE: SQL Server Integration Services
------------------------------
The required components for the 64-bit edition of Integration Services cannot be found. Run SQL Server Setup to install the required components.
A .NET Framework error occurred during execution of user-defined routine or aggregate "deploy_project_internal":
System.Data.SqlClient.SqlException: The required components for the 64-bit edition of Integration Services cannot be found. Run SQL Server Setup to install the required components.
System.Data.SqlClient.SqlException:
On another forum, I learned that there is an upgrade wizard for SSISDB.
C:\Program Files\Microsoft SQL Server\150\DTS\Binn\ISDBUpgradeWizard.exe
The catch is, if try to use this and you already patched your SQL 2019, you would get the error below:
The version of the sql server instance (15.0.4033.1) doesn't match with the version of the upgrade tool(15.0.2000). (Microsoft.SqlServer.IntegrationServices.ISServerDBUpgrade)
Lucky for me, in trying to resolve this issue since yesterday, I decided to install SQL 2019 into my laptop this afternoon. No CUs, no patch. So when I run the wizard, it converted my SSISDB.
Then, I restored the SSISDB into the new production server and we can now deploy the packages!!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply