70-463 · Question #47
You install a SQL Server 2012 database engine instance on a production server. A month later, you install SQL Server 2012 Integration Services (SSIS). You must develop an SSIS project and deploy it…
The correct answer is A. Enable CLR Integration. G. Start the SQL Server Agent service. Creating the SSIS catalog (SSISDB) requires CLR Integration enabled on the SQL Server instance, and SQL Server Agent must be running to execute the automatic log retention cleanup job.
Question
You install a SQL Server 2012 database engine instance on a production server. A month later, you install SQL Server 2012 Integration Services (SSIS). You must develop an SSIS project and deploy it to the server by using the Project Deployment model. Operations log records that are outside the configured retention period must be cleaned automatically. You need to create the SSIS catalog on the production server and ensure that the operations log cleaning requirement is met. What should you do? (Each correct answer presents part of the solution. Choose all that apply.)
Options
- AEnable CLR Integration.
- BEnable FILESTREAM with Full Access.
- CEnable the Resource Governor.
- DChange the recovery mode of the msdb database to FULL.
- EChange the Server-wide Default Logging Level in SSISDB to Verbose.
- FStart the SQL Server Browser service.
- GStart the SQL Server Agent service.
How the community answered
(66 responses)- A73% (48)
- B2% (1)
- C2% (1)
- D5% (3)
- E8% (5)
- F12% (8)
Why each option
Creating the SSIS catalog (SSISDB) requires CLR Integration enabled on the SQL Server instance, and SQL Server Agent must be running to execute the automatic log retention cleanup job.
SSISDB is built on CLR-based stored procedures and functions, so CLR Integration must be enabled on the SQL Server instance before the catalog can be created or will function correctly.
FILESTREAM with Full Access is not a prerequisite for SSISDB catalog creation or its log retention mechanism.
Resource Governor manages server resource pools and workload groups and has no dependency relationship with SSIS catalog creation.
The recovery model of msdb does not affect SSISDB catalog creation or its log cleanup behavior, which is managed through a separate SQL Agent job.
Changing the Server-wide Default Logging Level to Verbose increases log entry detail but does not enable or schedule automatic purging of expired log records.
SQL Server Browser enables client discovery of named SQL Server instances on a network but is not required for SSIS catalog creation or operation.
SQL Server Agent runs the 'SSIS Server Maintenance Job' that is automatically created alongside the SSISDB catalog - this job periodically deletes operations log records older than the configured retention period, meeting the automatic cleanup requirement.
Concept tested: SSIS catalog creation prerequisites and automatic log retention
Source: https://learn.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog
Topics
Community Discussion
No community discussion yet for this question.