01. You administer a SQL Server 2014 database instance. You need to configure the SQL Server Database Engine service on a failover cluster.
Which user account should you use?
a) A domain user
b) The BUILTIN\SYSTEM account
c) A local user with Run as Service permissions
d) The SQLBrowser account
02. You are using dynamic management views to monitor an SQL Server server named SQL1. A database administrator named Dba1 must monitor the health of SQL1.
You need to ensure that Dba1 can access dynamic management views for SQL1. The solution must use the principle of least privilege.
Which permissions should you assign to Dba1?
a) VIEW ANY DEFINITION
b) VIEW SERVER STATE
c) VIEW DEFINITION
d) CONTROL SERVER
03. You have a server named Server1 that is hosted in an Azure virtual machine. Server1 contains the following:
- One instance of SQL Server 2016 Enterprise
- 10 databases
- 500 stored procedures
You have a database named Databasel that is hosted on Server1. Database1 contains 100 queries that are executed dynamically from web applications.
You plan to remove data from the procedure cache on Database1. You have the following requirements:
- Changes to Database1 must not affect other databases that are hosted on Server1
- Changes to Database1 must not affect the performance of queries that are stored in other databases.
- The solution must minimize administrative effort.
You need to remove the data from the procedure cache as quickly as possible. What should you do?
a) Run DBCC FREEPROCCACHE.
b) Run ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE CACHE in the context of Database1.
c) Run DBCC DROPCLEANBUFFERS.
d) Write a script that iterates through each stored procedure definition and add WITH RECOMPILE to the definition.
04. You manage a SQL Server 2016 database named SNOWSHOES. Users report that their front-end application displays multiple error messages that identify the SNOWSHOES database. You need to verify SNOWSHOES database integrity.
Your solution must meet the following technical requirements:
- Avoid unnecessarily burdening the database server.
- Validate the contents of every indexed view in the database.
- Verify the integrity of every table in the database.
What should you do?
a) Run DBCC CHECKTABLE on the database.
b) Enable Auto Update Statistics on the database.
c) Schedule the Maintenance Cleanup task to run on the database.
d) Run DBCC CHECKDB on the database.
05. You administer two Microsoft SQL Server 2014 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?
a) Use a server certificate.
b) Configure the SQL Server service account to use Network Service.
c) Use a database certificate.
d) Configure the SQL Server service account to use Local System.
06. You are planning to deploy an instance of SQL Server 2016 Enterprise Edition on an Azure virtual machine (VM). The database will be supporting a retail application that requires a high level of transaction processing performance.
You decide to use a DS3 VM for the deployment. You need to design disk storage use for the deployment. You want to keep the solution as cost effective as possible without compromising performance.
Which disk configuration should you use?
a) Store transaction log files and tempdb on the temporary storage drive. Store data on a standard disk.
b) Store transaction log files and tempdb on one standard disk. Store data on a separate standard disk.
c) Store transaction log files and tempdb on the temporary storage drive. Store data on a premium disk.
d) Store transaction log files and tempdb on one premium disk. Store data on a separate premium disk.
e) Store transaction log files and tempdb on a premium disk. Store data on a standard disk.
07. You plan to deploy an on-premises SQL Server 2014 database to Azure SQL Database. You have the following requirements:
- Maximum database size of 500 GB
- A point-in-time-restore of 35 days
- Maximum database transaction units (DTUs) of 500
You need to choose the correct service tier and performance level. Which service tier should you choose?
b) Premium P4
c) Standard SO
d) Standard S3
08. You manage an on-premises, multi-tier application that has the following configuration:
- Two SQL Server 2012 databases named SQL1 and SQL2
- Two application servers named AppServerl and AppServer2 that run IIS
You plan to move your application to Azure. You need to ensure that during an Azure update cycle or a hardware failure, the application remains available.
Which two deployment configurations should you implement?
a) Deploy AppServer1 and AppServer2 in a single availability set.
b) Deploy all servers in a single availability set.
c) Deploy SQL1 and AppServer1 in a single availability set.
d) Deploy SQL2 and AppServer2 in a single availability set.
e) Deploy SQL1 and SQL2 in a single availability set.
09. You administer a Microsoft SQL Server 2014 database named Contoso on a server named Server01. You need to write messages to the Application Log when users are added to or removed from a fixed server role in Server01.
What should you create?
a) A Database Audit Specification
b) A Policy
c) An Alert
d) A SQL Profiler Trace
e) A Resource Pool
f) An Extended Event session
g) A Server Audit Specification
10. You administer a Microsoft SQL Server 2012 database. Users report that an application that accesses the database displays an error, but the error does not provide meaningful information. No entries are found in the SQL Server log or Windows event logs related to the error.
You need to identify the root cause of the issue by retrieving the error message. What should you do?
a) Create an Extended Events session by using the sqlserver.error_reported event.
b) Create a SQL Profiler session to capture all ErrorLog and EventLog events.
c) Flag all stored procedures for recompilation by using sp_recompile.
d) Execute sp_who.