You need to reduce the amount of memory used for cached query plans
DRAG DROP
You administer a Microsoft SQL Server 2012 instance.
An application executes a large volume of dynamic queries.
You need to reduce the amount of memory used for cached query plans.
What should you do? (To answer, move the appropriate statements from the list of
statements to the answer area and arrange them in the correct order.)
you need to implement a contained database
You administer a Microsoft SQL Server 2012 environment. One of the SQL Server 2012
instances contains a database named Sales.
You plan to migrate Sales to Windows Azure SQL Database.
To do so, you need to implement a contained database.
What should you do? (Each correct answer presents part of the solution. Choose all that
apply.)
You need to identify all previous times that the package execution failed because of a primary key violation
You administer a SQL Server Integration Services (SSIS) solution in the SSIS catalog. A
SQL Server Agent job is used to execute a package daily with the basic logging level.
Recently, the package execution failed because of a primary key violation when the package
inserted data into the destination table.
You need to identify all previous times that the package execution failed because of a
primary key violation.
What should you do?
What should you create?
You administer a Microsoft SQL Server 2012 database named Contoso on a server named
Server01.
You need to track all SELECT statements issued in the Contoso database only by users in a
role named Sales.
What should you create?
You need to ensure that auditing is configured to meet these requirements
You are developing a SQL Server Integration Services (SSIS) package to load data into a
Windows Azure SQL Database database. The package consists of several data flow tasks.
The package has the following auditing requirements:
If a data flow task fails, a Transact-SQL (T-SQL) script must be executed.
The T-SQL script must be executed only once per data flow task that fails, regardless of the
nature of the error.
You need to ensure that auditing is configured to meet these requirements.
What should you do?
What should you create?
You administer a Microsoft SQL Server 2012 database named Contoso on a server named
Server01.
You need to collect data for a long period of time to troubleshoot wait statistics when
querying Contoso. You also need to ensure minimum impact to the server.
What should you create?
You need to establish the automated validation process by using the least amount of administrative effort
You are developing a SQL Server Integration Services (SSIS) project with multiple packages
to copy data to a Windows Azure SQL Database database.
An automated process must validate all related Environment references, parameter data
types, package references, and referenced assemblies. The automated process must run on
a regular schedule.
You need to establish the automated validation process by using the least amount of
administrative effort.
What should you do?
Which edition should you choose?
You plan to install Microsoft SQL Server 2012 for a web hosting company.
The company plans to host multiple web sites, each supported by a SQL Server database.
You need to select an edition of SQL Server that features backup compression of
databases, basic data integration features, and low total cost of ownership.
Which edition should you choose?
You need to produce reports that combine the custom log messages with the systemgenerated log messages
You are developing a SQL Server Integration Services (SSIS) project by using the Project
Deployment Model. All packages in the project must log custom messages.
You need to produce reports that combine the custom log messages with the systemgenerated log messages. What should you do?
Which feature should you use?
You plan to install a Microsoft SQL Server 2012 instance.
The instance will support a database that has the following requirements:
Store Excel workbooks on the file system.
Access the workbooks through Transact-SQL.
Include the workbooks in database backups.
During installation, you need to ensure that the requirements will be met.
Which feature should you use?