Microsoft
70-467 · Question #169
70-467 Question #169: Real Exam Question with Answer & Explanation
The correct answer is C: Back up the master key of the SSISDB database.. Restoring an SSIS catalog (SSISDB) to another server requires both the encrypted database backup and the SSISDB master key used to protect its encryption hierarchy.
Question
You have two servers named Served and Server2. Both servers have SQL Server 2012 installed. Both servers have a SQL Server Integration Services (SSIS) catalog. You are preparing a disaster recovery plan for a business intelligence (BI) solution. You need to ensure that the SSIS catalog from Server1 can be restored to Server2. Which two actions should you perform on Server1? Each Answer presents part of the solution.
Options
- ABack up the master database.
- BRun sp.configure 'clr enabled', 0.
- CBack up the master key of the SSISDB database.
- DBack up the master key of the master database.
- EBack up the SSISDB database.
- FRun sp_configure 'clr enabled', 1.
Explanation
Restoring an SSIS catalog (SSISDB) to another server requires both the encrypted database backup and the SSISDB master key used to protect its encryption hierarchy.
Common mistakes.
- A. The master database does not contain SSIS catalog data or its encryption keys, so backing it up does not contribute to restoring SSISDB on another server.
- B. Running sp_configure 'clr enabled', 0 disables CLR integration, which SSISDB requires to function - this would break the catalog rather than help recover it.
- D. The master database master key protects server-level objects, not the SSISDB encryption hierarchy, so it is not needed to restore the SSIS catalog.
- F. Enabling CLR with sp_configure 'clr enabled', 1 is a prerequisite step performed on Server2 before restoring, not a backup action to perform on Server1.
Concept tested. SSIS catalog backup and disaster recovery to another server
Reference. https://learn.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog
Community Discussion
No community discussion yet for this question.