You administer a Microsoft SQL Server 2012 databasethat contains a table named OrderDetail.
You discover that the NCI_OrderDetail_CustomerID non-clustered index is fragmented.
You need to reduce fragmentation.
You need to achieve this goal without taking the index offline.
Which Transact-SQL batch should you use?

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 all files exceptSales_2.ndf are corrupt. You need to recover the corrupted data in the
minimum amount of time. What should you do?

You administer a Microsoft SQL Server 2012 server.
You plan to deploy new features to an application.
You need to evaluate existing and potential clustered and non-clustered indexes that will improve performance.
What should you do?

You administer a Microsoft SQL Server 2012.
A process that normally runs in less than 10 seconds has been running for more than an hour.
You examine the application log and discover that the process is using session ID 60.
You need to find out whether the process is being blocked. Which Transact-SQL statement should you use?

You create an availability group that has replicas named HA/Server01 and HA/Server02. Currently, HA/
Server01 is the primary replica.
You have multiple queries that read data and produce reports from the database.
You need to offload the reporting workload to the secondary replica when HA/Server01 is the primary replica.
What should you do?

You administer two Microsoft SQL Server 2012 servers.
Each server resides in a different, untrusted domain.
You plan to configure database mirroring.
You need to be able to create database mirroring endpoints on both servers.
What should you do?

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 do not provide the required
detail.
The following error message is an example error message:
“The job failed. The Job was invoked by User CONTOSO\ServiceAccount. The last step to run was step 1
(Subplan_1).”
You need to ensure that all available details of the job step failures for SQL Server Agent jobs are retained.
What should you do?

You administer a Microsoft SQL Server 2012 instance. You need to configure a new database to support
FILETABLES. What should you do? Choose all that apply.

You administer two instances of Microsoft SQL Server 2012. You deploy an application that uses a database on
the named instance. The application is unable to connect to the database on the named instance. You need to
ensure that the application can connect to the named instance. What should you do?

You administer a Microsoft SQL Server 2012 database. You configure Transparent Data Encryption (TDE) on
the Orders database by using the following statements:
<code>
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘MyPassword1!’
CREATE CERTIFICATE TDE_Certificate WITH SUBJECT = ‘TDE Certificate’;
BACKUP CERTIFICATE TDE_Certificate TO FILE = ”d:\TDE_Certificate.cer’
WITH PRIVATE KEY (FILE = ‘D:\TDE_Certificate.key’, ENCRYPTION BY PASSWORD =
‘MyPassword1!’);
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
ALTER DATABASE Orders SET ENCRYPTION ON;
<code>
You attempt to restore the Orders database and the restore fails. You copy the encryption file to the original
location. A hardware failure occurs and so a new server must be installed and configured. After installing SQL
Server to the new server, you restore the Orders database and copy the encryption files to their original
location. However, you are unable to access the database. You need to be able to restore the database.Which
Transact-SQL statement should you use before attempting the restore?