how to migrate ssis/ssas

3
votes

I am migrating from a local sql server 2012, to a sql server 2016 in AWS.

For the databases I will backup them up, and then restore them in the new server, I will also transfer the logins and jobs. (I have done this a few times in the past).

I am puzzled about how to migrate SSIS and SSAS… is there a script to back it all up and restore in the new server? I have never migrated neither SSIS nor SSAS, not sure what to expect.

1
ssisssas
This is too broad a topic. Did you read a specific article and were confused about one specific step? - Tab Alleman
I agree, but can't avoid it. I am looking for a high-level answer to start with… For example, for the engine itself, I know the migration (side by side) is backuping dbs in A restoring them in B, and then doing the same for the sql logins and sql jobs. I was looking for a similar high-level step list for SSIS and SSAS (2012 to 2016). (my ssis/ssas knowledge is less than limited!) - Chicago1988

1 Answers

1
votes

SSIS - at the core SSIS is either files with a .dtsx extension or a zip file with a .ispac extension

The .ispac should live in the SSISDB on your SQL Server. You can export them out by simply right clicking on the project and select Export

The .dtsx files might live in msdb under sysssispackages but they might also be in the SSIS Package Store, which is a fancy location under the SQL Server installation. Or they might be anywhere on the file system. Best approach is to find the job that runs the packages and pick apart the target to discover the where.

SSAS - They're a big fine model so won't you just back that cube up {Apologies to Juvenile}. An alternative article for backing up SSAS but you will end up with an .abf file which is everything you should need for the restore. You might also take a moment to start scheduling regular backups of your AS instances because it sounds like that's never been done.

You can also fire up SSDT and reverse engineer the existing model (tabular or multidimensional) and deploy from VS.