70-465 101 Q&S Designing Database Solutions for Microsoft SQL Server 2012

What should you do?

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.
A. Set up SQL Server Always On with a SQL Azure database as a replica.
B. Set up SQL Server Always On by using a SQL Server on a Windows Azure Virtual Machine.
C. Put the differential backup on tape and send it to the third-party backup storage system.
D. Use the Microsoft SQL Server Backup to Microsoft Windows Azure Tool to direct all backups to a different geographical location.


Answer Clike
What should you recommend?

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?
A. Change data capture
B. Statistics
C. A trigger
D. Master Data Services


Answer Clike
Which server 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 casesensitive 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.)
A. Server1
B. Server2
C. Server3
D. Seiver4


Answer Clike
What should you do?

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.
A. Implement change data capture on the Sales.Orders table.
B. To the Sales.Orders table, add a column named Status that will store the order status. Update the Status column as the order status changes.
C. Create a new table named Sales.OrderStatus that contains three columns named OrderID, StatusDate, and Status. Insert new rows into the table as the order status changes.
D. To the Sales.Orders table, add three columns named FulfilledDate, ShippedDate, and ReceivedDate. Update the value of each column from null to the appropriate date as the order status changes.


Answer Clike
Which technologies should you identify?

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.
Select and Place:



Answer Clike
What should you recommend?

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.
A. Policy-Based Management
B. A Data Definition Language (DDL) trigger
C. SQL Server Agent jobs
D. A maintenance plan


Answer Clike
What should you include in the recommendation?

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?
A. The bulk-logged recovery model
B. The full recovery model
C. A new partitioned table
D. A new log file
E. A new file group


Answer Clike
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.
A. Resource Governor
B. Max Degree of Parallelism
C. Windows System Resource Manager (WSRM)
D. Processor affinity


Answer Clike
DRAG DROP

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.
Select and Place:



Answer Clike
How many servers should you identify?

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.
Select and Place:



Answer Clike
What should you include in the design?

You have four databases that are accessed by using an Online Transaction Processing (OLTP) application. The databases are stored on a server named SQL1 that has SQL Server 2014 installed.
You plan to deploy an additional server that has SQL Server 2014 installed.
You need to design a high-availability solution for the databases that meets the following requirements:
If SQL1 fails, the databases must be available.
Users must be able to run reports against a secondary copy of the databases.
What should you include in the design?
More than one answer choice may achieve the goal. Select the BEST answer.
A. AlwaysOn availability groups
B. Database mirroring
C. Log shipping
D. Failover Clustering


Answer Clike
Which system objects should you use in your query to best achieve this task?

You administer a SQL Server 2014 instance.
Users report that the SQL Server has seemed slow today. A large database was being restored for much of the day, which could be causing issues.
You want to write a query of the system views that will report the following:
. Number of users that have a connection to the server
. Whether a user’s connection is active
. Whether any connections are blocked
. What queries are being executed
Whether the database restore is still executing and, if it is, what percentage of the restore is complete.
Which system objects should you use in your query to best achieve this task?
A. sys.dm_exec_requests, sys.dm_exec_sessions, sys.objects
B. sys.dm_exec_sessions, sys.dm_exec_query_stats, sys.dm_exec_query_text,sys.objects
C. sys.sysprocesses, sys.dm_exec_query_text, sys.objects
D. sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_exec_query_text


Answer Clike
What should you include in the recommendation?

You are designing a monitoring application for a new SQL Server 2014 instance.
You need to recommend a solution to generate a report that displays the 10 most frequent wait types that occur for the instance.
What should you include in the recommendation?
More than one answer choice may achieve the goal. Select the BEST answer.
A. The SQL Server error log
B. The sys.dm_os_wait_stats dynamic management view
C. The DBCC SQLPERF(WAITSTATS) command
D. SQL Server Profiler


Answer Clike
Which method should you recommend?

You have a database hosted on SQL Server 2012 R2. The database contains 5 million rows.
You need to recommend a repeatable method to migrate the database to SQL Azure.
Which method should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.
A. Create a SQL Server Integration Services (SSIS) package, and then run the package.
B. Back up the database, and then restore the database.
C. Extract a data-tier application, and then import the application.
D. Generate scripts to create all of the all database objects and all of the data, and then execute the scripts by using SQL Azure.


Answer Clike
What should you recommend?

You are designing a database named DB1.
Changes will be deployed to DB1 every Wednesday night.
You need to recommend a strategy to deploy the changes to DB1. The strategy must meet the following requirements:
. The strategy must not disrupt backup operations.
. DB1 must be online while the changes are deployed.
. You must be able to undo quickly any changes made to objects.
What should you recommend?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Perform a copy-only database backup before the changes are deployed. If the deployment fails, restore the database to another server and recover the original objects from the restored database.
B. Create a database snapshot. If the deployment fails, recover the objects from the database snapshot.
C. Create a database snapshot. If the deployment fails, revert the database to the database snapshot.
D. Perform a full database backup before the changes are deployed. If the deployment fails, restore the database to another server and recover the original objects from the restored database.


Answer Clike
What should you include in the design?

You are building a stored procedure for a Windows Azure SQL Database. The procedure will add multiple rows to a table.
You need to design the stored procedure to meet the following requirements:
If any of the new rows violates a table constraint, then no further additions must be attempted and all changes made by the stored procedure must be discarded.
If any errors occur, a row must be added to an audit table, and the original error must be returned to the caller of the stored procedure.
What should you include in the design?
A. An explicit transaction that has XACT_ABORT disabled
B. An implicit transaction that has error handling enabled
C. An explicit transaction that has error handling enabled
D. An implicit transaction that has XACT.ABORT enabled


Answer Clike
Which it is started.

DRAG DROP
You are the senior database administrator at Contoso, Ltd. You manage a SQL Server 2014 Instance, with multiple databases used for reporting.
You have recently hired a junior database administrator. You want this person to be able to view the database structures on the server, but you do not want him or her to be able to make changes or see the data in the tables.
The new hire’s login credentials are as follows:
Login name: JFree
Password: Jx672$qse
You want the new hire to be required to change his password on his next login.
The code that is produced should execute no matter the initial database context in which it is started.
You need to write the code required to give the new hire only the desired access, using the smallest number of steps. Develop the solution by selecting and arranging the required code blocks in the correct order. You may not need all of the code blocks.
Select and Place:



Answer Clike
What should you recommend?

You are creating a database that will store usernames and credit card numbers for an application.
You need to recommend a solution to store and reuse the credit card numbers in the database.
What should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.
A. Data encryption
B. Transparent Data Encryption (TDE)
C. Encrypting File System (EPS)
D. Data hashing


Answer Clike
What should you recommend?

You have a query that is used by a reporting dashboard.
Users report that the query sometimes takes a long time to run.
You need to recommend a solution to identify what is causing the issue.
What should you recommend?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Set the blocked process threshold, and then run SQL Server Profiler.
B. Set the blocked process threshold, and then create an alert.
C. Enable trace flag 1204, and then create an alert.
D. Create a job that queries the sys.dm_os_waiting_tasks dynamic management view.


Answer Clike
What should you do?

You are troubleshooting an application that runs a query. The application frequently causes deadlocks.
You need to identify the isolation level used by the query when a deadlock occurs.
What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Query the sys.dm_exec_requests dynamic management view.
B. Create a trace in SQL Server Profiler that contains the Deadlock graph event.
C. Query the sys.dm_exec_sessions dynamic management view.
D. Enable trace flag 1222, and then view the SQL Server error log.


Answer Clike
What should you recommend?

You plan to create a database.
The database will be used by a Microsoft .NET application for a special event that will last for two days.
During the event, data must be highly available.
After the event, the database will be deleted.
You need to recommend a solution to implement the database while minimizing costs. The solution must not affect any existing applications.
What should you recommend?
More than one answer choice may achieve the goal. Select the BEST answer.
A. SQL Server 2014 Enterprise
B. SQL Server 2014 Standard
C. SQL Azure
D. SQL Server 2014 Express with Advanced Services


Answer Clike
Which type of index storage should you recommend?

DRAG DROP
You have a server named SQL1 that has SQL Server 2014 installed. SQL1 hosts a database named Database! Database1 contains a table named Table1. Table1 is partitioned across five filegroups based on the TransactionType field.
The schema of Table1 is configured as shown in the following table.

Table1 contains the indexes shown in the following table.

You need to recommend an index strategy to maximize performance for the queries that consume the indexes available to Table1.
Which type of index storage should you recommend?
To answer, drag the appropriate index storage type to the correct index in the answer area.
Select and Place:



Answer Clike
What should you include in your design?

DRAG DROP
You are planning to deploy a database to Windows Azure SQL Database.
You need to design a stored procedure to update rows. The stored procedure must meet the following requirements:
. If the update fails, an error must be raised to the application and the update must be discarded.
. The stored procedure must be designed to maximize concurrency.
What should you include in your design?
To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Select and Place:



Answer Clike
Which isolation levels should you recommend?

DRAG DROP
You plan to deploy SQL Server 2012.
You are designing two stored procedures named USP_1 and USP_2 that have the following requirements:
. Prevent data read by USP_1 from being modified by other active processes.
. Prevent USP_2 from performing dirty reads.
You need to recommend the isolation level for each stored procedure. The solution must maximize concurrency.
Which isolation levels should you recommend?
To answer, drag the appropriate isolation level to the correct stored procedure in the answer area.
Select and Place:



Answer Clike
DRAG DROP

DRAG DROP
You use SQL Server 2014.
You need to create a single object that inserts a provided value into Table1, and then returns a count of the records in Table1.
Develop the solution by selecting and arranging the required code blocks in the correct order. You may not need all of the code blocks.
Select and Place:



Answer Clike
Which upgrade steps should you recommend?

DRAG DROP
You have two servers named SQL1 and SQL2 that have SQL Server 2012 installed. SQL1 contains a database that is mirrored asynchronously to SQL2. The database contents are updated once a month.
You need to upgrade the database to SQL Server 2014. The solution must minimize downtime.
Which upgrade steps should you recommend?
To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Select and Place:



Answer Clike
What should you use?

You have two SQL Server 2014 instances named SQLDev and SQLProd.
You plan to create a new database on SQLProd that will use SQL Server Authentication.
You need to ensure that when the new database is copied from SQLProd to SQLDev, users can connect to the database on SQLDev even if they do not have a login on the SQLDev instance.
What should you use?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Triggers
B. Contained database
C. SQL Server Analysis Services (SSAS) scripts
D. Extended Events
E. SQL Server Integration Services (SSIS) scripts


Answer Clike
Which options should you recommend?

DRAG DROP
You plan to deploy a database to SQL Azure.
You must create two tables named Table 1 and Table 2 that will have the following specifications:
. Table1 will contain a date column named Column1 that will contain a null value approximately 80 percent of the time.
. Table2 will contain a column named Column2 which is the product of two other columns in Table2.
Both Table1 and Table2 will contain over one million rows.
You need to recommend which options must be defined for the columns. The solution must minimize the time it takes to retrieve data from the tables.
Which options should you recommend?
To answer, drag the appropriate options to the correct column in the answer area.
Select and Place:



Answer Clike
What should you include in the recommendation?

You have a server named Server1 that has 16 processors.
You plan to deploy multiple instances of SQL Server 2014 to Server1.
You need to recommend a method to allocate processors to each instance.
What should you include in the recommendation?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Processor affinity
B. Windows System Resource Manager (WSRM)
C. Max Degree of Parallelism
D. Resource Governor


Answer Clike
Which order should the four actions be performed?

DRAG DROP
You need to recommend the actions that are required to partition a table.
In which order should the four actions be performed?
To answer, move the actions from the list of actions to the answer area and arrange them in the correct order.
Select and Place:



Answer Clike
Which technologies should you recommend?

DRAG DROP
You are designing an authentication strategy for a new server that has SQL Server 2014 installed. The strategy must meet the following business requirements:
The account used to generate reports must be allowed to make a connection during certain hours only.
Failed authentication requests must be logged.
You need to recommend a technology that meets each business requirement. The solution must minimize the amount of events that are logged.
Which technologies should you recommend?
To answer, drag the appropriate solution to the correct business requirement in the answer area.
Select and Place:



Answer Clike
What should you recommend?

You are creating a database that will store usernames and passwords for an application.
You need to recommend a solution to store the passwords in the database.
What should you recommend?
More than one answer choice may achieve the goal. Select the BEST answer.
A. One-way encryption
B. Encrypting File System (EFS)
C. Transparent Data Encryption (TDE)
D. Reversible encryption


Answer Clike
What should you include in the recommendation?

DRAG DROP
You need to recommend a backup process for data warehouse database.
The solution must meet the following requirements:
. Ensure that if a hardware failure occurs, you can bring the database online without losing more than 24 hours of transactions.
. Minimize the amount of administrative effort required to restore any lost data.
. Minimize the space used by transaction logs.
What should you include in the recommendation?
To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Select and Place:



Answer Clike
Which features should you identify?

DRAG DROP
You plan to deploy SQL Server 2014.
You identify the following security requirements for the deployment:
. Users must be prevented from intercepting and reading the T-SQL statements sent from the clients to the database engine.
. All database files and log files must be encrypted if the files are moved to another disk on another server.
You need to identify which feature meets each security requirement. The solution must minimize processor overhead.
Which features should you identify?
To answer, drag the appropriate feature to the correct requirement in the answer area.
Select and Place:



Answer Clike
What should you include in the recommendation?

You have two databases named DB1 and DB2 that are located on the same server.
You plan to create a stored procedure named SProc1 in DB1.
SProc1 will query a table named Table2 in DB2.
You need to recommend a solution to ensure that SProc1 can access Table2 without granting users direct access to Table2.
What should you include in the recommendation?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Contained databases
B. Application roles
C. Cross-database ownership chaining
D. Digital certificates


Answer Clike
Which features should you identify?

DRAG DROP
You plan to deploy SQL Server 2014.
Your company identifies the following monitoring requirements:
. Tempdb must be monitored for insufficient free space.
. Deadlocks must be analyzed by using Deadlock graphs.
You need to identify which feature meets each monitoring requirement.
Which features should you identify?
To answer, drag the appropriate feature to the correct monitoring requirement in the answer area.
Select and Place:



Answer Clike
Which features should you identify?

DRAG DROP
You plan to deploy three highly available SQL Server environments that will use SQL Server 2014.
You identify the following specifications for each environment as shown following table.

You need to recommend which high-availability feature is required for each environment.
Which features should you identify?
To answer, drag the appropriate feature to the correct requirement in the answer area.
Select and Place:



Answer Clike
Which features should you identify?

DRAG DROP
You plan to deploy a database by using SQL Server 2014.
Your company identifies the following requirements for the database:
. The name of all stored procedures must start with "usp_"always.
. All distribution statistics must be updated daily.
You need to identify which feature must be used to meet each database requirement.
Which features should you identify?
To answer, drag the appropriate feature to the correct database requirement in the answer area.
Select and Place:



Answer Clike
What should you do?

You are troubleshooting an application that runs a query. The application frequently causes deadlocks.
You need to identify which transaction causes the deadlock.
What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Query the sys.dm_exec_sessions dynamic management view.
B. Query the sys.dm_exec_requests dynamic management view.
C. Create a trace in SQL Server Profiler that contains the Deadlock graph event
D. Create an extended events session to capture deadlock information.


Answer Clike
Which features should you identify?

DRAG DROP
You plan to deploy SQL Server 2014.
Your company identifies the following monitoring requirements for the database:
. An e-mail message must be sent if the SQL Server Authentication mode changes.
. An e-mail message must be sent if CPU utilization exceeds 90 percent.
You need to identify which feature meets each monitoring requirement.
Which features should you identify?
To answer, drag the appropriate feature to the correct monitoring requirement in the answer area.
Select and Place:



Answer Clike
What should you do?

You are the new database administrator for a SQL Server 2014 instance.
You conduct an assessment on the instance and determine that the auto create statistics setting on the database named DB1 has been turned off. You see no evidence that any maintenance has been occurring.
You want to set up monitoring to see if query performance is being affected.
You need to set up a monitoring process that will capture any cases where statistics could have been useful if they existed.
What should you do?
A. Create a SQL Server Agent job to execute DBCC SHOWSTATISTICS on each of the primary key columns in the database.
B. Use the missing_column_statistics extended event.
C. Query the sys.statistics system view to see all cases where the statistics were last needed.
D. Write a query using the sys.dm_db_missing_index_group_stats DMV Joining to sys.indexes, filtering on is_hypothetical.


Answer Clike
What should you recommend?

Your network contains an Active Directory domain that has two groups named Group1 and Group2.
The domain contains two SQL Server instances named SQLDev and SQLProd. Each SQL Server instance has access to various storage media.
The SQL Server instances have a database that contains a table named Table1.
Table1 contains a column named Column1. The value for Column1 can be either Value1 or Value2.
You need to recommend a solution to ensure that users in Group1 can retrieve only rows from Column1 that contain the value of Value1.
What should you recommend?
A. A dynamic management view
B. Filegroups
C. Snapshot isolation
D. User-defined views


Answer Clike
What should you recommend?

You have two SQL Server instances named SQLDev and SQLProd that have access to various storage media.
You plan to synchronize SQLDev and SQLProd.
You need to recommend a solution that meets the following requirements:
. The database schemas must be synchronized from SQLDev to SQLProd.
. The database on SQLDev must be deployed to SQLProd by using a package.
. The package must support being deployed to Windows Azure SQL Database.
What should you recommend?
More than one answer choice may achieve the goal. Select the BEST answer.
A. A database snapshot
B. SQL Server Integration Services (SSIS)
C. Change data capture
D. A data-tier application


Answer Clike
Which method should you use?

You manage a SQL Server 2014 instance that contains a database named DB1.
Users report that some queries to DB1 take longer than expected. Although most queries run in less than one second, some queries take up to 20 seconds to run.
You need to view all of the performance statistics for each database file.
Which method should you use?
A. Query the sys.dm_os_tasks dynamic management view.
B. Query the sys.dm_os_performance_counters dynamic management view.
C. Query the sys.dm_io_virtual_file_stats dynamic management function.
D. Examine the Data File I/O pane in Activity Monitor.


Answer Clike
Which SQL elements should you include in the recommendation?

You have an SQL Server 2014 instance named SQL1.
SQL1 creates error events in the Windows Application event log.
You need to recommend a solution that will run an application when SQL1 logs a specific error in the Application log.
Which SQL elements should you include in the recommendation? (Each correct answer presents part of the solution. Choose all that apply.)
A. A policy
B. A maintenance plan
C. An alert
D. A job
E. A trigger


Answer Clike
What should you do?

You have a database named DB1.
You plan to create a stored procedure that will insert rows into three different tables. Each insert must use the same identifying value for each table, but the value must increase from one invocation of the stored procedure to the next.
Occasionally, the identifying value must be reset to its initial value.
You need to design a mechanism to hold the identifying values for the stored procedure to use.
What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Create a sequence object that holds the next value in the sequence. Retrieve the next value by using the stored procedure. Reset the value by using an ALTER SEQUENCE statement as needed.
B. Create a sequence object that holds the next value in the sequence. Retrieve the next value by using the stored procedure. Increment the sequence object to the next value by using an ALTER SEQUENCE statement. Reset the value as needed by using a different ALTER SEQUENCE statement.
C. Create a fourth table that holds the next value in the sequence. At the end each transaction, update the value by using the stored procedure. Reset the value as needed by using an UPDATE statement.
D. Create an identity column in each of the three tables. Use the same seed and the same increment for each table. Insert new rows into the tables by using the stored procedure. Use the DBCC CHECKIDENT command to reset the columns as needed.


Answer Clike
Which columns should you designate as SPARSE?

HOTSPOT
You use SQL Server 2014. You create a table within a database by using the following DDL:

The following table illustrates a representative sample of data:

The system is expected to handle 50 million orders a month over the next five years.
You have been instructed by your Team Lead to follow best practices for storage and performance in the utilization of SPARSE columns.
Which columns should you designate as SPARSE? To answer, mark each column as SPARSE or NOT SPARSE in the answer area.
Hot Area:



Answer Clike
Which isolation levels should you recommend?

DRAG DROP
You plan to deploy a database to SQL Azure.
You are designing two stored procedures named USP_1 and USP_2 that have the following requirements:
. Prevent data read by USP_1 from being modified by other active processes.
. Allow USP_2 to perform dirty reads.
You need to recommend the isolation level for the stored procedures.
The solution must maximize concurrency.
Which isolation levels should you recommend?
To answer, drag the appropriate isolation level to the correct stored procedure in the answer area.
Select and Place:



Answer Clike
Which code should you use?

You use SQL Server 2014 to maintain the data used by applications at your company.
You want to execute two statements.
You need to guarantee that either both statements succeed, or both statements fail together as a batch.
Which code should you use?

A. Option A
B. Option B
C. Option C
D. Option D
E. Option E


Answer Clike
What should you recommend?

You deploy a SQL Server instance named SQLProd that uses SQL Server 2014.
You need to recommend a solution to monitor the transactions that are running currently against SQLProd. The solution must minimize the amount of custom code required.
What should you recommend?
A. Statistics
B. A dynamic management view
C. A trigger
D. User-defined views


Answer Clike
Which data types should you recommend for each column?

You have a SQL Azure database named Database1.
You need to design the schema for a table named table1. Table1 will have less than one million rows. Table1 will contain the following information for each row:

The solution must minimize the amount of space used to store each row.
Which data types should you recommend for each column?
To answer, drag the appropriate data type to the correct column in the answer area.
Select and Place:



Answer Clike
What should you recommend?

You create a stored procedure that retrieves all of the rows from a table named Table1.
You need to recommend a solution to ensure that all of the statements in the stored procedure can be executed if another transaction is modifying rows in Table1 simultaneously.
What should you recommend?
A. Snapshot isolation
B. A database snapshot
C. Filegroups
D. Indexes


Answer Clike
What should you recommend?

You have a SQL Server instance on a server named Server1.
You need to recommend a solution to perform the following tasks every week:
. Rebuild the indexes by using a new fill factor.
. Run a custom T-SQL command.
. Back up the databases.
What should you recommend?
More than one answer choice may achieve the goal. Select the BEST answer.
A. A trigger
B. An alert
C. A maintenance plan
D. Windows PowerShell
E. A system policy


Answer Clike
When the status changed.

You are designing a Windows Azure SQL Database for an order fulfillment system. You create a table named Sales.Orders with 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 that you can retrieve the following information:
. The current status of an order
. The previous status of an order.
. The date when the status changed.
The solution must minimize storage.
More than one answer choice may achieve the goal. Select the BEST answer.
A. To the Sales.Orders table, add three columns named Status, PreviousStatus and ChangeDate. Update rows as the order status changes.
B. Create a new table named Sales.OrderStatus that contains three columns named OrderID, StatusDate, and Status. Insert new rows into the table as the order status changes.
C. Implement change data capture on the Sales.Orders table.
D. To the Sales.Orders table, add three columns named FulfilledDate, ShippedDate, and ReceivedDate. Update the value of each column from null to the appropriate date as the order status changes.


Answer Clike
What should you recommend replacing Table1?

You need to recommend a solution to minimize the amount of time it takes to execute USP_1.
With what should you recommend replacing Table1?
A. A view
B. A temporary table
C. A table variable
D. A function


Answer Clike
Which change should you recommend?

You need to recommend a change to USP_3 to ensure that the procedure continues to execute even if one of the UPDATE statements fails.
Which change should you recommend?
A. Set the XACT_ABORT option to off.
B. Set the XACT_ABORT option to on.
C. Set the IMPLICIT_TRANSACTIONS option to off.
D. Set the IMPLICIT_TRANSACTIONS option to on.


Answer Clike
What should you recommend?

You need to recommend a solution for the error handling of USP_4. The solution must handle errors for nested stored procedures in the code for
USP_4.
What should you recommend?
A. Use the @@ERROR variable in the nested stored procedures.
B. Use the @@ERROR variable in USP_4.
C. Use the RAISERROR command in the nested stored procedures.
D. Use the RAISERROR command in USP_4.


Answer Clike
What should you include in the recommendation?

You need to recommend a solution to meet the security requirements of the junior database administrators.
What should you include in the recommendation?
A. A server role
B. A database role
C. A credential
D. A shared login


Answer Clike
What should you include in the recommendation?

You need to recommend a solution to ensure that USP_4 adheres to the security requirements.
What should you include in the recommendation?
A. Enable SQL Server Audit.
B. Enable trace flags.
C. Configure data manipulation language (DML) triggers.
D. Enable C2 audit tracing.


Answer Clike
What should you include in the recommendation?

You need to recommend a solution to minimize the amount of time it takes to execute USP_5.
What should you include in the recommendation?
A. Enable cross-database chaining.
B. Use a server role to group all logins.
C. Use the EXECUTE AS clause in USP_5.
D. Copy USP.5 to each database.


Answer Clike
What should you recommend?

You need to recommend a solution for the planned changes to the customer classifications.
What should you recommend? (Each correct answer presents part of the solution. Choose all that apply.)
A. Add a row to the Customers table each time a classification changes.
B. Add columns for each classification to the Customers table.
C. Add a table to track any changes made to the classification of each customer.
D. Add a column to the Classifications table to track the status of each classification.
E. Implement change data capture.


Answer Clike
Which change should you recommend?

You need to recommend a change to USP_3 to ensure that the procedure completes only if all of the UPDATE statements complete.
Which change should you recommend?
A. Set the XACT_ABORT option to off
B. Set the XACT_ABORT option to on.
C. Set the IMPLICIT_TRANSACTIONS option to off.
D. Set the IMPLICIT_TRANSACTIONS option to on.


Answer Clike
What should you include in the recommendation?

You need to recommend a disaster recovery strategy for the Inventory database.
What should you include in the recommendation?
A. Log shipping
B. SQL Server Failover Clustering
C. AlwaysOn availability groups
D. Peer-to-peer replication


Answer Clike
What should you recommend?

You need to recommend a solution for the error handling of USP_3. The solution must minimize the amount of custom code required.
What should you recommend?
A. Use the @@ERROR variable in the nested stored procedures.
B. Use a TRY CATCH block in the called stored procedures.
C. Use the @@ERROR variable in the called stored procedures.
D. Use the RAISERROR command in the nested stored procedures.


Answer Clike
What should you recommend replacing Table1?

You need to recommend a solution to minimize the amount of time it takes to execute USP_1.
With what should you recommend replacing Table1?
A. An indexed view
B. A function
C. A table variable
D. A temporary table


Answer Clike
What should you include in the recommendation?

You need to recommend a disaster recovery solution for the Dev database.
What should you include in the recommendation?
A. The simple recovery model and full backups
B. The bulk-logged recovery model and full backups
C. The full recovery model, full backups, and differential backups
D. The full recovery model, full backups, and transaction log backups


Answer Clike
What should you recommend?

You need to recommend a solution to minimize the amount of time it takes to execute USP_2.
What should you recommend?
A. A database snapshot
B. A table variable
C. A temporary table
D. Snapshot isolation


Answer Clike
What should you create in Azure to support the creation of the backups for DB1?

What should you create in Azure to support the creation of the backups for DB1?
A. an Azure Content Delivery Network (CDN) endpoint
B. a Service Bus namespace
C. a storage account
D. a cloud service


Answer Clike
Which two actions should you perform?

You need to configure security on the Product table for customer support representatives.
Which two actions should you perform? Each correct answer presents part of the solution. Choose two.
A. Create a view called CustProduct that includes columns ProductID. ProductName, Product Description, QuantityOnHand, ProductPrice, ProductCost, and ProductSupplierID.
B. GRANT ALL on CustProduct TO OurDomainCustomerSupport.
C. Create a user-defined data type called CustProduct that includes columns ProductID, ProductName, Product Description, and ProductPrice.
D. Create a view called CustProduct that includes columns ProductID, ProductName, Product Description, QuantityOnHand, and ProductPrice.
E. GRANT SELECT on CustProduct TO OurDomainCustomerSupport.
F. GRANT SELECT on CustProduct TO public.


Answer Clike
What should you include in the recommendation?

You need to recommend a solution to back up DB1.
What should you include in the recommendation?
A. Azure Table Storage
B. Azure Queue storage
C. Azure Blob storage
D. Azure Document DB


Answer Clike
Which code fragment should you use in each location in the command to complete one of the commands you will need to include in the job?

DRAG DROP
You need to create a job to automate some database maintenance tasks.
Which code fragment should you use in each location in the command to complete one of the commands you will need to include in the job? To answer, drag the appropriate lines of code to the correct locations in the command. Each line of code may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
Select and Place:



Answer Clike
DRAG DROP

DRAG DROP
You need to create the CustomerAccess table to support the reporting and performance requirements.
Develop the solution by selecting and arranging the required code blocks in the correct order. You may not need all of the code blocks.
Select and Place:



Answer Clike
Which two database options should you change to meet the requirements?

You need to change the ProdDB database.
Which two database options should you change to meet the requirements? Each correct answer presents part of the solution. Choose two.
A. CONTAINS FILESTREAM
B. Change recovery model to FULL
C. CONTAINMENT = PARTIAL
D. Change recovery model to BULK_LOGGED
E. COLLATE IN.MEMORY
F. CONTAINS MEMORY OPTIMIZED DATA


Answer Clike
Which three actions should you perform?

You need to implement changes to the system to reduce contention and improve performance of the SalesOrderDetail table.
Which three actions should you perform? Each correct answer presents part of the solution. Choose three.
A. Use (SNAPSHOT] hints in the report queries
B. ALTER DATABASE [ProdDB] SET READ_COMMITTED_SNAPSHOT ON
C. ALTER DATABASE [ProdDB] SET READ_COMMITTED_SNAPSHOT OFF
D. SET TRANSACTION ISOLATION LEVEL SNAPSHOT
E. Use (TABLOCK) hints in the report queries
F. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
G. ALTER DATABASE [ProdDB] SET ALLOW_SNAPSHOT_ISOLATION ON
H. Use (SNAPSHOT] hints in the update statements


Answer Clike
DRAG DROP

DRAG DROP
The business requires a satellite office to have a local copy of the data to report against.
You want to implement a solution to support the requirements. You need to establish a new Availability Group between the two servers.
Develop the solution by selecting and arranging the required code blocks in the correct order. You may not need all of the code blocks.
Select and Place:



Answer Clike
Which two actions should you perform?

You need to implement a backup strategy to support the requirements.
Which two actions should you perform? Each correct answer presents part of the solution. Choose two.
A. Create a credential called MyCredential on SQL Server by using a Windows domain account and password.
B. Schedule a full backup by using the command BACKUP DATABASE ProdDB TO DISK…
C. Create a share on your Windows Azure site by using your Windows Azure storage account information, and grant permission to the SQL Server service login.
D. Schedule a full backup by using the command BACKUP DATABASE ProdDB TO URL … WTTH CREDENTIAL=N’MyCredential’
E. Create a share on the hot standby site and grant permission to the SQL Server service login.
F. Create a credential called MyCredential on SQL Server, using MyStorageAccount for the storage account name and StorageAccountKey for the access key.
G. Schedule a full backup by using the command BACKUP DATABASE ProdDB TO SHARE … WITH CREDENTIAL=N’ MyCredential’


Answer Clike
Which code should you use?

You need to write code that will allow the sales force to retrieve data for their reports with the least amount of effort.
Which code should you use?

A. Option A
B. Option B
C. Option C
D. Option D


Answer Clike
Which command should you use only during the monthly maintenance window?

You are designing your maintenance plan.
Which command should you use only during the monthly maintenance window?
A. DBCC INDEXDEFRAG (ProdDB, SalesOrderDetail, SODIndex)
B. ALTER INDEX SODIndex ON SalesOrderDetail REORGANIZE
C. ALTER INDEX SODIndex ON SalesOrderDetail REBUILD
D. ALTER INDEX SODIndex ON SalesOrderDetail REBUILD WITH (ONLINE * ON)


Answer Clike
What should you recommend for the updates to Sales.TransactionHistory?

What should you recommend for the updates to Sales.TransactionHistory?
A. a REPEATABLE READ isolation level
B. implicit transactions
C. query hints
D. a SNAPSHOT isolation level


Answer Clike
How should you recommend completing the statement?

DRAG DROP
You need to recommend which statement should be used to update SalesOrder.
How should you recommend completing the statement? To answer, drag the appropriate elements to the correct locations. Each element may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
Select and Place:



Answer Clike
Which function should you assign to each server?

DRAG DROP
You need to distribute functionality across the three servers.
Which function should you assign to each server? To answer, drag the appropriate functions to the correct servers. Each function may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
Select and Place:



Answer Clike
What should you include in the recommendation?

You need to recommend a solution that addresses the file storage requirements.
What should you include in the recommendation?
A. FileStream
B. FileTable
C. The varbinary data type
D. The image data type


Answer Clike
What should you include in the recommendation?

You need to recommend a solution that addresses the installation issues.
What should you include in the recommendation?
A. Windows logins
B. Server roles
C. Contained users
D. Database roles


Answer Clike
What should you recommend?

You need to recommend a solution that addresses the security requirement.
What should you recommend?
A. Revoke user permissions on the tables. Create stored procedures that manipulate data. Grant the users the EXECUTE permission on the stored procedures.
B. Grant the users the SELECT permission on the tables. Create views that retrieve data from the tables. Grant the users the SELECT permission on the views.
C. Deny the users SELECT permission on the tables. Create views that retrieve data from the tables. Grant the users the SELECT permission on the views.
D. Deny the users the SELECT permission on the tables. Create stored procedures that manipulate data. Grant the users the EXECUTE permission on the stored procedures.


Answer Clike
What should you recommend changing?

You need to recommend changes to the ERP application to resolve the search issue. The solution must minimize the impact on other queries generated from the ERP application.
What should you recommend changing?
A. The collation of the Products table
B. The index on the ProductName column
C. The collation of the ProductName column
D. The data type of the ProductName column


Answer Clike
What should you recommend?

You need to recommend a solution that addresses the concurrency requirement.
What should you recommend?
A. Break each stored procedure into two separate procedures, one that changes Sales.Table1 and one that changes Sales.Table2.
B. Make calls to Sales.Proc1 and Sales.Proc2 synchronously.
C. Call the stored procedures in a Distributed Transaction Coordinator (DTC) transaction.
D. Modify the stored procedures to update tables in the same order for all of the stored procedures.


Answer Clike
What should you include in the recommendation?

You need to recommend a solution that addresses the backup issue. The solution must minimize the amount of development effort.
What should you include in the recommendation?
A. Indexed views
B. Filegroups
C. Table partitioning
D. Indexes


Answer Clike
Which isolation level should you recommend?

You need to recommend an isolation level for usp_UpdateOrderDetails.
Which isolation level should you recommend?
A. Read committed
B. Repeatable read
C. Read uncommitted
D. Serializable


Answer Clike
What should you recommend?

You need to recommend a solution that resolves the missing data issue. The solution must minimize the amount of development effort.
What should you recommend?
A. Denormalize the Products table.
B. Denormalize the OrderDetails table.
C. Normalize the OrderDetails table.
D. Normalize the Products table.


Answer Clike
What should you include in the recommendation?

You need to recommend a solution that addresses the index fragmentation and index width issue.
What should you include in the recommendation? (Each correct answer presents part of the solution. Choose all that apply.)
A. Change the data type of the lastModified column to smalldatetime.
B. Remove the lastModified column from the clustered index.
C. Change the data type of the modifiedBy column to tinyint.
D. Change the data type of the id column to bigint.
E. Remove the modifiedBy column from the clustered index.
F. Remove the id column from the clustered index.


Answer Clike
What should you include in the recommendation?

You need to recommend a solution that reduces the time it takes to import the supplier data.
What should you include in the recommendation?
A. Enable instant file initialization.
B. Reorganize the indexes.
C. Disable Resource Governor.
D. Enable Auto Update Statistics.


Answer Clike
What should you include in the recommendation?

You need to recommend a solution that meets the data recovery requirement.
What should you include in the recommendation?
A. A differential backup
B. A transaction log backup
C. Snapshot isolation
D. A database snapshot


Answer Clike
What should you recommend?

You need to recommend a solution to synchronize Database2 to App1_Db1.
What should you recommend?
A. Change data capture
B. Snapshot replication
C. Master Data Services
D. Transactional replication


Answer Clike
What should you include in the recommendation?

You need to recommend a database reporting solution that meets the business requirements.
What should you include in the recommendation?
A. Data collection
B. Performance Monitor
C. A maintenance plan
D. A dynamic management view


Answer Clike
What should you include in the recommendation?

You need to recommend a disk monitoring solution that meets the business requirements.
What should you include in the recommendation?
A. An audit
B. A dynamic management view
C. A maintenance plan
D. An SQL Server Agent alert


Answer Clike
What should you include in the recommendation?

You need to recommend a solution to improve the performance of usp.UpdateInventory. The solution must minimize the amount of development effort.
What should you include in the recommendation?
A. A table variable
B. A common table expression
C. A subquery
D. A cursor


Answer Clike
What should you include in the recommendation?

You need to recommend a solution for the deployment of SQL Server 2014. The solution must meet the business requirements.
What should you include in the recommendation?
A. Create a new instance of SQL Server 2014 on the server that hosts the SQL Server 2008 instance.
B. Upgrade the existing SQL Server 2008 instance to SQL Server 2014.
C. Deploy two servers that have SQL Server 2014 installed and implement Failover Clustering.
D. Deploy two servers that have SQL Server 2014 installed and implement database mirroring.


Answer Clike
What should you recommend?

You need to recommend a solution to allow application users to perform tables. The solution must meet the business requirements.
What should you recommend?
A. Create a Policy-Based Management Policy.
B. Create a user-defined database role and add users to the role.
C. Create stored procedures that use EXECUTE AS clauses.
D. Create functions that use EXECUTE AS clauses.


Answer Clike
What should you include in the recommendation?

You need to recommend a feature to support your backup solution.
What should you include in the recommendation?
A. Transparent Data Encryption (TDE)
B. Column-level encryption
C. An NTFS file permission
D. A Secure Sockets Layer (SSL)


Answer Clike
What should you include in the recommendation?

You need to recommend a solution for Application1 that meets the security requirements.
What should you include in the recommendation?
A. Signed stored procedures
B. Certificate Authentication
C. Encrypted columns
D. Secure Socket Layer (SSL)


Answer Clike
What should you recommend?

During performance testing, you discover that database INSERT operations against the Inventory table are slow.
You need to recommend a solution to reduce the amount of time it takes to complete the INSERT operations.
What should you recommend?
A. Partition the nonclustered index.
B. Partition the Inventory table.snapshot replication
C. Create a column store index.Master Data Services
D. Drop the clustered index.change data capture


Answer Clike