You administer a Microsoft SQL Server 2012 default instance.
The instance is hosted by a server that has a localfirewall configured.
The firewall only allows inbound connections on port 1433.
The server only hosts a single instance of SQL Server.
You need to ensure that the instance is configured to allow remote connections even if the SQL Server is
unresponsive to client connections. What should youdo? Choose all that apply.

You administer a Microsoft SQL Server database named Sales. The database is 3 terabytes in size. The Sales
database is configured as shown in the following table:

You discover that Sales_2.ndf is corrupt. You need to recover the corrupted data in the minimum amountof
time. What should you do?

You administer a Microsoft SQL Server 2012 databasethat has Trustworthy set to On.
You create a stored procedure that returns database-level information from Dynamic Management Views.
You grant User1 access to execute the stored procedure.
You need to ensure that the stored procedure returns the required information when User1 executes the stored
procedure.
You need to achieve this goal by granting the minimum permissions required.
What should you do? (Each correct answer presents acomplete solution. Choose all that apply.)

You administer a Microsoft SQL Server 2012 instancethat contains a financial database hosted on a storage
area network (SAN).
The financial database has the following characteristics:
A data file of 2 terabytes is located on a dedicated LUN (drive D).
A transaction log of 10 GB is located on a dedicated LUN (drive E).
Drive D has 1 terabyte of free disk space.
Drive E has 5 GB of free disk space.
The database is continually modified by users during business hours from Monday through Friday between
09:00 hours and 17:00 hours.
Five percent of the existing data is modified each day.
The Finance department loads large CSV files into anumber of tables each business day at 11:15 hours and
15:15 hours by using the BCP or BULK INSERT commands.
Each data load adds 3 GB of data to the database.
These data load operations must occur in the minimum amount of time.
A full database backup is performed every Sunday at10:00 hours.
Backup operations will be performed every two hours(11:00, 13:00, 15:00, and 17:00) during business hours.
You implement log shipping of the financial database to another SQL Server 2012 instance.
You decide to failover to this secondary database. You need to ensure that all transactions will be replicated to
the secondary database.
Which backup option should you use?

You administer all the deployments of Microsoft SQLServer 2012 in your company. You have two servers in
the same data center that hosts your production database. You need to ensure that the database remains
available if a catastrophic server failure or a disk failure occurs. You also need to maintain transactional
consistency of the data across both servers. You need to achieve these goals without manual intervention.
Which configuration should you use?

You administer a Microsoft SQL Server 2012 database.
You create an availability group named haContosoDbs.
Your primary replica is available at Server01\Contoso01.
You need to configure the availability group to have the highest availability.
You also need to ensure that no data is lost.
Which Transact-SQL statement should you use?

You administer several Microsoft SQL Server 2012 database servers.
Merge replication has been configured for an application that is distributed across offices throughouta wide
area network (WAN).
Many of the tables involved in replication use the XML and varchar(max) data types. Occasionally, merge
replication fails due to timeout errors.
You need to reduce the occurrence of these timeout errors. What should you do?

You create an availability group named HaContoso that has replicas named Server01/HA, Server02/HA, and
Server03/HA.
Currently, Server01l/HA is the primary replica.
You need to ensure that the following requirements are met:
Backup operations occur on Server02/HA.
If Server02/HA is unavailable, backup operations occur on Server03/HA.
Backup operations do not occur on Server01/HA.
How should you configure HaContoso?

You administer a Microsoft SQL Server 2012 instancethat has several SQL Server Agent jobs configured.
When SQL Server Agent jobs fail, the error messagesreturned by the job steps are truncated.
The following error message is an example of the truncated error message:
“Executed as user CONTOSO\ServiceAccount. …0.4035.00 for 64-bit Copyright (C) Microsoft Corp 1984-2011.
All rights reserved. Started 63513 PM Error 2012-06-23 183536.87 Code 0XC001000E Source UserImport
Description Code 0x00000000 Source Log Import Activity Descript… The package execution fa… The step
failed.”
You need to ensure that all the details of the job step failures are retained for SQL Server Agent jobs.
What should you do?

You administer a Microsoft SQL Server 2012 server that has SQL Server Integration Services (SSIS) installed.
You plan to deploy new SSIS packages to the server.
The SSIS packages use the Project Deployment Model together with parameters and Integration Services
environment variables.
You need to configure the SQL Server environment tosupport these packages.
What should you do?