What should you do?

You are deploying a Microsoft SQL Server database that will support a mixed OLTP and OLAP workload. The target virtual machine has four CPUs.
You need to ensure that reports do not use all available system resources.
What should you do?
A. Enable Auto Close.
B. Increase the value for the Minimum System Memory setting.
C. Set MAXDOP to half the number of CPUs available.
D. Increase the value for the Minimum Memory per query setting.

Answer: C
Explanation:
When an instance of SQL Server runs on a computer that has more than one microprocessor or CPU, it detects the best degree of parallelism, that is, the number of processors employed to run a single statement, for each parallel plan execution. You can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution.

What should you do?

You are the administrator of a Microsoft SQL Server 2014 server.
Some applications consume significant resources. You need to manage the server workload by restricting resource-intensive applications
You need to dynamically limit resource consumption.
What should you do?
A. Configure Resource Pools, Workload Groups, and Classifier Function, and then enable the Resource Governor
B. Set up Service Broker to ensure that application are not allowed to consume more than the specified amount of resource
C. Create a new rule for each application that sets the resource limit allowed
D. Create a new plan Guide with a Scope Type of sql and define the resource limits for each application

Answer: A
Explanation:
In the SQL Server Resource Governor, a resource pool represents a subset of the physical resources of an instance of the Database Engine. Resource Governor enables you to specify limits on the amount of CPU, physical IO, and memory that incoming application requests can use within the resource pool. Each resource pool can contain one or more workload groups. When a session is started, the Resource Governor classifier assigns the session to a specific workload group, and the session must run using the resources assigned to the workload group.
References:https://docs.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor-resource-pool

Which edition should you choose?

You plan to install Microsoft SQL Server 2014 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?
A. Express Edition with Tools
B. Standard Edition
C. Web Edition
D. Express Edition with Advanced Services

Answer: B
Explanation:

What should you configure?

You administer a Microsoft SQL Server 2014 Enterprise Edition server that uses 64 cores. You discover performance issues when large amounts of data are written to tables under heavy system load. You need to limit the number of cores that handle I/O.
What should you configure?
A. Processor affinity
B. Lightweight pooling
C. Max worker threads
D. I/O affinity

Answer: D
Explanation:
The affinity Input-Output (I/O) mask Server Configuration Option.
To carry out multitasking, Microsoft Windows 2000 and Windows Server 2003 sometimes move process threads among different processors. Although efficient from an operating system point of view, this activity can reduce Microsoft SQL Server performance under heavy system loads, as each processor cache is repeatedly reloaded with data. Assigning processors to specific threads can improve performance under these conditions by eliminating processor reloads; such an association between a thread and a processor is called processor affinity.
References: http://msdn.microsoft.com/en-us/library/ms189629.aspx

Which feature should you use?

You are a database administrator for a Microsoft SQL Server 2014 environment.
You want to deploy a new application that will scale out the workload to at least five different SQL Server instances.
You need to ensure that for each copy of the database, users are able to read and write data that will then be synchronized between all of the database instances.
Which feature should you use?
A. Database Mirroring
B. Peer-to-Peer Replication
C. Log Shipping
D. Availability Groups

Answer: B
Explanation:
Peer-to-peer replication provides a scale-out and high-availability solution by maintaining copies of data across multiple server instances, also referred to as nodes. Built on the foundation of transactional replication, peer-to-peer replication propagates transactionally consistent changes in near real-time. This enables applications that require scale-out of read operations to distribute the reads from clients across multiple nodes. Because data is maintained across the nodes in near real-time, peer-to-peer replication provides data redundancy, which increases the availability of data.
References:https://docs.microsoft.com/en-us/sql/relational-databases/replication/transactional/peer-to-peer-transactional-replication

What should you do?

You administer a Microsoft SQL Server 2014 database instance.
You plan to migrate the database to Windows Azure SQL Database. You verify that all objects contained in the database are compatible with Windows Azure SQL Database.
You need to ensure that database users and required server logins are migrated to Windows Azure SQL Database.
What should you do?
A. Use the copy database wizard
B. Use the Database Transfer wizard
C. Use SQL Server Management Studio to deploy the database to Windows Azure SQL Database
D. Backup the database from the local server and restore it to Windows Azure SQL Database

Answer: C
Explanation:
You would need to use either the SQL Server Management Studio or Transact-SQL.
References: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-cloud-migrate

Which backup option should you use?

You administer a Microsoft SQL Server 2014 instance that contains a financial database hosted on a storage area network (SAN).
The financial database has the following characteristics:
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 a number 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 at 10:00 hours. Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours.
On Wednesday at 10:00 hours, the development team requests you to refresh the database on a development server by using the most recent version.
You need to perform a full database backup that will be restored on the development server.
Which backup option should you use?
A. NORECOVERY
B. FULL
C. NO_CHECKSUM
D. CHECKSUM
E. Differential
F. BULK_LOGGED
G. STANDBY
H. RESTART
I. SKIP
J. Transaction log
K. DBO ONLY
L. COPY_ONLY
M. SIMPLE
N. CONTINUE AFTER ERROR

Answer: L
Explanation:
COPY_ONLY specifies that the backup is a copy-only backup, which does not affect the normal sequence of backups. A copy-only backup is created independently of your regularly scheduled, conventional backups. A copy-only backup does not affect your overall backup and restore procedures for the database.
References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql

Which Transact-SQL query should you use?

Your database contains a table named Purchases. The table includes a DATETIME column named PurchaseTime that stores the date and time each purchase is made. There is a non- clustered index on the PurchaseTime column. The business team wants a report that displays the total number of purchases made on the current day. You need to write a query that will return the correct results in the most efficient manner.
Which Transact-SQL query should you use?
A. SELECT COUNT(*)FROM PurchasesWHERE PurchaseTime = CONVERT(DATE, GETDATE())
B. SELECT COUNT(*)FROM PurchasesWHERE PurchaseTime = GETDATE()
C. SELECT COUNT(*)FROM PurchasesWHERE CONVERT(VARCHAR, PurchaseTime, 112) =CONVERT(VARCHAR, GETDATE(), 112)
D. SELECT COUNT(*)FROM PurchasesWHERE PurchaseTime >= CONVERT(DATE, GETDATE())AND PurchaseTime <DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))

Answer: D
Explanation:
To compare a time with date we must use >= and > operators, and not the = operator.

What should you create?

You administer a Microsoft SQL Server 2014 database named Contoso on a server named Server01.
You need to be notified immediately when fatal errors occur on 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

Answer: C
Explanation:
SQL Server has alerts that get more important based on the severity of the alert. Anything of severity 16 or below tends to refer to the database and deals with issues that are tied to syntax errors, violations of foreign keys, etc. While those errors are typically important, they don’t refer to anything with regards to overall health of the SQL Server. Alerts 17 through 25 do. Those are the ones your health checks are probably firing on.

image077 - 
What should you create?
References: https://www.mssqltips.com/sqlservertip/3384/configuring-critical-sql-server-alerts/