70-467 · Question #169
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…
The correct answer is C. Back up the master key of the SSISDB database. E. Back up 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.
How the community answered
(21 responses)- A5% (1)
- C81% (17)
- D5% (1)
- F10% (2)
Why each option
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.
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.
Running sp_configure 'clr enabled', 0 disables CLR integration, which SSISDB requires to function - this would break the catalog rather than help recover it.
The SSISDB database uses a database master key to encrypt sensitive data such as passwords and connection strings stored in the catalog. Backing up this master key from Server1 is required so it can be restored on Server2, allowing the catalog's encryption hierarchy to be re-established before the database backup is restored.
The master database master key protects server-level objects, not the SSISDB encryption hierarchy, so it is not needed to restore the SSIS catalog.
Backing up the SSISDB database captures all catalog objects including projects, packages, environments, and execution history. Without this backup, there is nothing to restore to Server2.
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
Source: https://learn.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog
Topics
Community Discussion
No community discussion yet for this question.