Which Transact-SQL statement should you use?
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 need to offload the reporting workload to the secondary replica when HA/Server01 is the primary replica
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 need to be able to create database mirroring endpoints on both servers
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 need to ensure that all available details of the job step failures for SQL Server Agent jobs are retained
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 need to configure a new database to support FILETABLES
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 need to ensure that the application can connect to the named instance
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 need to be able to restore the database.Which Transact-SQL statement should you use before attempting the
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?
You need to ensure that the login for the ticketingapplication cannot access other production databases
You administer a single server that contains a Microsoft SQL Server 2012 default instance on which several
production databases have been deployed.
You plan to install a new ticketing application that requires the deployment of a database on the server.
The SQL login for this application requires sysadmin permissions.
You need to ensure that the login for the ticketingapplication cannot access other production databases.
What should you do?
You need to ensure that the minimum amount of disk space is used to store the data in the customer table
You need to ensure that AgentPortalUser can send email messages
You are creating an application that will connect to the AgentPortal database by using a SQL login named
AgentPortalUser.
Stored procedures in the database will use sp_send_dbmail to send email messages.
You create a user account in the msdb database for the AgentPortalUser login.
You use the Database Mail Configuration Wizard to create a Database Mail profile.
Security has not been configured for the Database Mail profile.
You need to ensure that AgentPortalUser can send email messages.
What should you do?