BenchMark Quizzes by MDFT Pro

Resolve SSIS Package Failure After Master Database Loss

You work for MDFT Pro, a well-known training agency that uses SQL Server Integration Services (SSIS) to manage nightly data loads for their learning management system. Mark, the Database Operations Manager, runs SQL Server 2019 on an Azure virtual machine that contains an SSISDB database with dozens of SSIS packages for loading student enrollment data, course completions, and assessment results. After a recent disk failure, the master database was lost and had to be recreated from scratch. Mark has discovered that all SSIS packages now fail to run on the virtual machine, and he has assigned you to resolve the issue and restore the ETL processes.

Which four actions should you perform in sequence to resolve the issue?

Choose all correct answers from the options below.

Explanations for each answer:

Add a certificate to an Azure Key Vault is incorrect. Azure Key Vault is not required for restoring SSISDB functionality on a SQL Server instance running on an Azure virtual machine.
Attach the SSISDB database is correct. First, you must attach the SSISDB database since it was not lost, only the master database was affected.
Turn on the TRUSTWORTHY property and the CLR property is correct. The TRUSTWORTHY and CLR properties must be enabled on SSISDB for SSIS packages to function correctly.
Enable Transparent Data Encryption is incorrect. Transparent Data Encryption is not related to the issue with SSIS packages failing after master database loss.
Open the master key for the SSISDB database is correct. The database master key must be opened to decrypt sensitive data in SSIS packages.
Encrypt a copy of the master key by using the service master key is correct. Finally, the database master key must be encrypted by the service master key to restore automatic decryption functionality.

Learn more about The SSIS Catalog:

The SSIS Catalog

Practice Exam

Resolve SSIS Package Failure After Master Database Loss

Discuss this question on social media: