You have a server that has SQL Server 2014 installed. The server contains 100 user
databases.
You need to recommend a backup solution for the user databases.
The solution must meet the following requirements:
• Perform a transaction log backup every hour.
• Perform a full backup of each database every week.
• Perform a differential backup of each database every day.
• Ensure that new user databases are added automatically to the backup solution.
What should you recommend?More than one answer choice may achieve the goal. Select
the BEST answer.

DRAG DROP
You are designing a database for a university.
The database will contain two tables named Classes and Enrollment that have the following
specifications:
• Classes will store brochures in the XPS format. The brochures must be structured in
folders and must be accessible by using UNC paths.
• Enrollment will store information about students and their classes. Performance must
be enhanced for queries of the current enrollments.
You need to identify which SQL Server technology meets the specifications of each table.
Which technologies should you identify?
To answer, drag the appropriate technology to the correct table in the answer area.

DRAG DROP
You plan to install two SQL Server 2014 environments named Environment1 and
Environment.
Your company identifies the following availability requirements for each environment:
• Environment1 must have mirroring with automatic failover implemented.
• Environment2 must have AlwaysOn with automatic failover implemented.
You need to identify the minimum number of SQL Server 2014 servers that must be
deployed to each environment to ensure that all data remains available if a physical server fails.
How many servers should you identify?
To answer, drag the appropriate number to the correct environment in the answer area.

You have a SQL Server 2014 database named DB1.
You plan to import a large number of records from a SQL Azure database to DB1.
You need to recommend a solution to minimize the amount of space used in the transaction
log during the import operation.
What should you include in the recommendation?

You have a server named Server1 that has 2 processors.
You plan to deploy multiple instances of SQL Server 2014 to Server1. Each instance will
have multiple databases.
You need to recommend a method to allocate processor time to each database.
What should you include in the recommendation?
More than one answer choice may achieve the goal. Select the BEST answer.

You are designing a SQL Server database for an order fulfillment system.
You create a table named Sales.Orders by using the following script:

Each order is tracked by using one of the following statuses:
• Fulfilled
• Shipped
• Ordered
• Received
You need to design the database to ensure that you can retrieve the status of an order on a
given date. The solution must ensure that new statuses can be added in the future.
What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.

DRAG DROP

You use SQL Server 2014 Enterprise Edition. Your database contains a partitioned table
named AuditData. AuditData is partitioned by year. Partition 1 contains data from the year
2010 and prior.
Management has decided to archive all AUDITDATA records from 2010 and prior.
Management wants the records to be removed from the database entirely and provided to
the backup team as a zipped text file. The data must no longer reside in the database. There
is very little tolerance for performance degradation in your environment.
You need to remove all 2010 and prior data from the AuditData table by using the least
amount of system resources possible.
Develop the solution by selecting and arranging the required SQL actions in the correct order.
You may not need all of the actions.

You deploy a database by using SQL Server 2014. The database contains a table named Table1.
You need to recommend a solution to track all of the deletions executed on Table1. The
solution must minimize the amount of custom code required.
What should you recommend?

You have a SQL Server 2014 environment That includes four servers.
The servers are configured as shown in the following table.

You plan to configure Policy-Based Management to enforce the following rules:
• On Server1, enable SQL Server password policies and enable the default trace.
• On Server3, ensure that the names of user-defined stored procedures begin with the
prefix “usp_” and ensure that all databases use a case-sensitive collation.
You need to recommend which server you must configure as a Central Management Server.
Which server should you recommend? (Each correct answer presents a complete solution.
Choose all that apply.)

You are the administrator for a SQL Server 2014 instance that stores the data for an online
transaction processing sales system.
The company takes full backups every week; differential backups on the days with no full
backups; and hourly transaction backups. These backups are stored on a backup server in
the company’s data center.
Every week, the company places the full backup on a tape and sends it to a third-party
backup storage system.
The company is worried that a disaster might occur that could destroy their computer center
and cause them to lose orders.
You need to determine the best method for providing the smallest amount of data loss and
downtime without leasing or purchasing additional physical locations.
What should you do? More than one answer choice may achieve the goal. Select the BEST
answer.