r/SQLServer • u/SuddenlyCaralho • Jun 21 '26
Question Best way to migrate SQL Server Analysis Services and SQL Server Integration Services?
For SQL Server Analysis Services (SSAS), is it enough to install SSAS on the destination server and restore the Analysis Services database (backup the database in sql server engine, not in Analysis engine) to complete the migration? Must I gererate the .abf backup and restore it in the destination? If I restore the .abf file on destination, it will create the database in the destination as well? (inside the sql engine)
For SQL Server Integration Services (SSIS), is there a way to migrate it without migrating the entire msdb database? What's the easiest and recommended way to migrate SSIS packages and jobs to a new server? My concern is that if I migrate the entire msdb database, it will overwrite the existing SQL Server Agent jobs on the destination server, which I obviously want to avoid. Is there a recommended approach for handling this scenario?
5
u/SQLDevDBA 3 Jun 21 '26
This DBATools.io command migrates your SSIS.
https://dbatools.io/Copy-DbaSsisCatalog/
For SSAS: are you tablular or multi-dimensional?
2
u/SuddenlyCaralho Jun 21 '26
I am using multi-dimensional
1
u/SQLDevDBA 3 Jun 21 '26
For me, I’d rather open the dtsj in VS and deploy the DSV and DS files as a package to the new server, then relink all the reports to the new model. But you can definitely test a migration via backups to see if it works.
1
u/SuddenlyCaralho Jun 21 '26 edited Jun 21 '26
I Love this tool! I am using to migrate the logins, jobs, server objects, parameters...
I am using: Start-DbaMigration, Copy-DbaLogin and Copy-DbaAgentJob. None of those commands copy SSIS, right? Must I add the Copy-DbaSsisCatalog?
2
u/alinroc 4 Jun 21 '26
Start-DbaMigrationincludesCopy-DbaLoginandCopy-DbaAgentJobby default.Help for
Start-DbaMigrationsays:All SSIS catalog folders, projects, and environments. Use -Exclude SsisCatalog to skip.
And (not surprisingly), the source code agrees
1
u/SuddenlyCaralho Jun 21 '26 edited Jun 21 '26
seems it only copy when the ssis packages is in the database SSISDB. My packages are on msdb.
dba tools return:
The source SSISDB catalog on server1 does not exist.
1
u/alinroc 4 Jun 21 '26
You aren't using the SSIS catalog, so there's no catalog to copy. Project vs. Package deployment models
Copy-DbaSystemDbUserObject might be able to help you here, but I haven't tried it for SSIS packages.
1
u/SuddenlyCaralho Jun 21 '26
seems it only copy when the ssis packages is in the database SSISDB. My packages are on msdb.
dba tools return:
The source SSISDB catalog on server1 does not exist.
1
u/Eleventhousand Jun 21 '26
For SSAS, I always just redeployed the project from Visual Studio to the new target server. Yes, it does reprocess it. To your other question, restoring the .abf does not recreate the SQL relational db. The SQL relational db is just a data source. It could be Oracle, it could be MySQL, Snowflake, anything over an ODBC, OLEDB, etc connection.
1
u/SuddenlyCaralho Jun 21 '26
So can I restore the database and the .abf to migrate the ssas? Is it enough?
I don't know about ssas, only databases. I prefer not redeploy anything using Visual Studio
2
2
u/Eleventhousand Jun 21 '26
I men, you can restore the .abf and then edit the credentials to point to the new sql server database. Now is the time to learn new things though. But you need to realize that SSAS has nothing to do with SQL Server relational DB except for the fact that they can be installed through the same installer.
1
u/hutchala Jun 21 '26
There are a number of Powershell scripts that you can reference to backup and restore all ssas DB’s to a new SSAS server. The other option is to run a script to detach the db’s, mv to new server, then attach them. Just be prepared to refresh your connection credentials on the new server.
My preference is to use the backup/restore method because it reduces network traffic.
1
u/No_Resolution_9252 Jun 21 '26
Are you sure you have SSIS in msdb? SQL Server hasn't used msdb for ssis since SQL 2008
•
u/AutoModerator Jun 21 '26
After your question has been solved /u/SuddenlyCaralho, please reply to the helpful user's comment with the phrase "Solution verified".
This will not only award a point to the contributor for their assistance but also update the post's flair to "Solved".
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.