Aim: To migrate from SQL2005 to SQL 2017
Details: We have an old SQL server 2005 that also has SSRS, SSIS and SSAS components.I am trying to migrate SSIS and SSRS from one SQL server to another. The new server is going to be a shared SQL server and is already running an instance of SSRS and SSIS.
I've installed a second instance of SQL server on the new server and have restored databases on that server. However, I don't have a clue if we can actually have two instances of SSRS and SSIS on the same server?
In terms of setting up SSRS, I've installed IIS on the new server and configured websites with virtual directories similar to SQL2005. However, there are some SQL jobs with long random characters which were created via SSRS. How do I recreate those jobs?
JobID : 86D685E6-F353-46A1-9D1F-0B0EC1336B6E
JobName : 09DF1436-A5C6-4FE8-B325-5C47710E138B
JobOwner : NT AUTHORITY\NETWORK SERVICE
JobCategory : Report Server
JobDescription : This job is owned by a report server process. Modifying this job could result in database incompatibilities. Use Report Manager or Management Studio to update this job.
Yes - you can have multiple instances of SSRS on a single server. However, each instance is going to need a different named virtual directories.
IIS is no longer required for SSRS - it uses its own built-in web server. The SSRS Configuration Manager will setup and create the correct virtual directories.
As for those jobs - they are created by reporting services and will be recreated once you have SSRS installed and configured.
To get this done - first, backup the encryption key from the 2005 instance. Backup the databases - and restore them to the new instance (or shared instance). In the SSRS configuration manager - modify the database to point to the correct databases - and restore the encryption key. Change the virtual directories as needed.
For SSIS - you can have multiple services running but there shouldn't be any need. On the new system you should be setup to use the Integration Services Catalog. You will need to convert your SSIS packages deployment model to use project deployment - and update them for the new environment - then deploy to the catalog. Once deployed, recreate any agent jobs - this will be setup differently since these are deployed to the catalog and cannot be copied across from the old system.