Q1. 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 SQL Azure.
What should you recommend?
More than one answer choice may achieve the goal. Select the BEST answer.
A. A database snapshot
B. A data-tier application
C. Change data capture
D. SQL Server Integration Services (SSIS)
Answer: B
Q2. 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.
Answer:
Q3. 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 subquery
B. a cursor
C. a common table expression
D. a table variable
Answer: C
Q4. 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 unavailable to users while the changes are deployed.
-You must be able to undo quickly the entire operation.
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: C
Q5. 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 2012 installed.
You plan to deploy an additional server that has SQL Server 2012 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. Database mirroring
B. Log shipping
C. Failover Clustering
D. AlwaysOn availability groups
Answer: D
Q6. 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 resuse 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 (EFS)
D. Data hashing
Answer: A
Q7. Your company has offices in Seattle and Montreal.
The network contains two servers named Server1 and Server2 that have SQL Server 2012 installed. The servers are located in separate building within your campus. The latency of the WAN link between the buildings is less than 10 ms.
You plan to implement an AlwaysOn availability group on both servers.
You need to recommend a failover type for the availability group.
What should you recommend?
A. Asynchronous automatic failover
B. Synchronous manual failover
C. Asynchronous manual failover
D. Synchronous automatic failover
Answer: D
Q8. 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. Implement change data capture.
B. Add a table to track any changes made to the classification of each customer.
C. Add columns for each classification to the Customers table.
D. Add a row to the Customers table each time a classification changes.
E. Add a column to the Classifications table to track the status of each classification.
Answer: AC
Topic 4, Litware, Inc Case B
Overview
You are a database administrator for a company named Litware, Inc. Litware is a book publishing house.
Litware has a main office and a branch office.
You are designing the database infrastructure to support a new web-based application that is being developed. The web application will be accessed at www.litwareinc.com. Both internal employees and external partners will use the application.
You have an existing desktop application that uses a SQL Server 2005 database named App1_DB. App1_Db will remain in production.
Requirements
Planned Changes
You plan to deploy a SQL Server 2012 instance that will contain two databases named Databasel and Database2.
Database1 will contain two tables named Orders and OrderDetails. Databasel will also contain a stored procedure named usp_UpdateOrderDetails. The stored procedure is used to update order information. The stored procedure queries the Orders table twice each time the procedure executes. Each query against the Orders table must return the same data. All statements in the stored procedure run in a single transaction.
Database1 will contain several queries that access data in the Database2 tables.
Database2 will contain a table named Inventory. Inventory will contain over 100 GB of data.
The Inventory table will have two indexes: a clustered index on the primary key and a nonclustered index. The column that is used as the primary key will use the identity property.
Database2 will contain a table named Inventory. Inventory will contain over 100 GB of data.
The Inventory table will have two indexes: a clustered index on the primary key and a nonclustered index. The column that is used as the primary key will use the identity property.
Database2 will contain a stored procedure named usp_UpdateInventory. Usp_UpdateInventory will manipulate several small data sets of up to 100 rows each. Each data set will be used multiple times.
Data from Database2 will be accessed periodically by an external application named Application!. The data from Database2 will be sent to a database named App1_Db1 as soon as changes occur to the data in Database2.
Litware plans to use offsite storage for all SQL Server 2012 backups.
Business Requirements
You have the following requirements:
-Costs for new equipment must be minimized.
-External access to the databases must be encrypted.
-Development effort must be minimized whenever possible.
-System administrators must be notified when disk space falls below a certain threshold.
-The databases must be available if the SQL Server service or the storage solution fails.
-Database administrators must receive a detailed report that contains allocation errors and data corruption.
-Application developers must be denied direct access to the database tables. Applications must be denied direct access to the tables. -You must encrypt the backup files to meet regulatory compliance requirements. The encryption strategy must minimize changes to the databases and to the applications.
Q9. DRAG DROP
You plan to deploy SQL Server 2012.
Your company identifies the following monitoring requirements for the database:
-An e-mail message must be sent when a user logs in.
-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.
Answer:
Q10. DRAG DROP
You have a server named SQL1 that has SQL Server 2012 installed. SQL1 hosts a database named Database1. Database1 contains a table named Table1. Table1 is partitioned across five filegroups based on the Date 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.
Answer: