Learn About Microsoft's Exam 70-450

  By Certification Magazine —

These practice test questions from MeasureUp are based on Microsoft’s exam 70-450: PRO: Designing, Optimizing and Maintaining a Database Administrative Solution Using Microsoft SQL Server 2008.  

The audience for this exam includes individuals who are responsible for administering SQL Server databases. Experience using the management tools for SQL Server to automate database administration, optimize database and server performance, and monitor and troubleshoot database servers will help you prepare for this exam.

By passing exam 70-450, you earn credit toward the Microsoft Certified IT Professional (MCITP): Database Administrator 2008 certification.
 

Objective: Designing a Database Server Security Solution.
SubObjective: Design instance-level security configurations.

Single answer, multiple choice

You manage two instances of SQL Server 2008 running on different computers. The computers are named serv1.busicorp.com and serv2.busicorp.com.

You execute the following to create a symmetric key on serv1:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'My p*55wORd';
GO
CREATE CERTIFICATE cert_keyProtection WITH SUBJECT = 'My Key Protection';
GO
CREATE SYMMETRIC KEY key_DataShare WITH
KEY_SOURCE = 'These are my key generation bits'
ALGORITHM = AES_256,
IDENTITY_VALUE = 'These are my key Identity generation bits'
ENCRYPTION BY CERTIFICATE [cert_keyProtection];
GO

You need to create an identical symmetric key on serv2.

What should you do?

A.    Copy the symmetric key from serv1 to serv2.
B.    Back up the master key from serv1, restore it to serv2, and then run the same commands on serv2.
C.    Run the same commands on serv2, back up the key from serv1, and restore the key to serv2.
D.    Run the same commands on serv2, SELECT the value for encryptbykey(key_guid('key_DataShare'), 'MyData' ), and paste the result into the key on serv2.

Answer:
D

Tutorial:
You should run the same commands on serv2, SELECT the value for encryptbykey(key_guid('key_DataShare'), 'MyData' ), and paste the result into the key on serv2. You must create a symmetric key based on the server's master key on each instance. You can then copy the unique key value from serv1 to serv2.

You should not copy the symmetric key from serv1 to serv2. You must manually create the symmetric key on serv2 and then reconfigure it as an identical key.

You should not back up the master key from serv1, restore it to serv2, and then run the same commands on serv2. The database master key is based on the service master key and the database master key from one instance will not work on another instance.

You should not run the same commands on serv2, back up the key from serv1, and restore the key to serv2. The symmetric key on serv2 must be based on the local database master key. If you just copy the symmetric key from serv1, it will not work.

References:
How to: Create Identical Symmetric Keys on Two Servers
Microsoft TechNet
http://technet.microsoft.com/en-us/library/ms366281.aspx

SQL Server and Database Encryption Keys (Database Engine)
Microsoft TechNet
http://technet.microsoft.com/en-us/library/bb964742.aspx

Understanding Transparent Data Encryption (TDE)
Microsoft TechNet
http://technet.microsoft.com/en-us/library/bb934049.aspx

Objective: Designing a Database Solution for High Availability.
SubObjective: Design a high-availability solution that is based on replication.

Single answer, multiple choice

You support several instances of SQL Server 2008. You have one instance running in your home office and an instance installed on each field service person's mobile computer. All instances host a full copy of the FieldService database.

Field engineers post service reports and update outstanding service reports while at the customer site. Field engineers have intermittent access to the SQL Server instance located in the home office. You need to design and implement a replication solution that best meets the following requirements:

 * Updates can be made on any copy of the FieldService database.
 * Updates can be made on a predefined schedule or as needed.
 * Updates are propagated to all copies of the FieldService database.
 * Conflicts can be detected and resolved.

Field engineers make frequent updates to the FieldService database each day. You need to minimize the administrative effort needed to implement and maintain the solution.

What should you do?

A.    Configure the home office instance as a merge publisher/distributor and each mobile instance as a merge subscriber.  
B.    Configure each instance as a peer-to-peer transactional replication publisher/subscriber.  
C.    Configure the home office as a standard transactional replication publisher/distributor and each mobile instance as a standard transactional subscriber.  
D.    Configure the home office as a transactional replication publisher/distributor and each mobile instance as an updating subscriber.  

Answer:
A

Tutorial:
You should configure the home office instance as a merge publisher/distributor and each mobile instance as a merge subscriber. Merge replication allows for updates to be made to any copy of the database and those updates propagated to the publisher and all subscribers. You can configure scheduled updates, but also support ad hoc updates whenever the mobile computers can connect to the instance at the home office. Merge replication supports detection and correction of conflicts.

You should not configure each instance as a peer-to-peer transactional replication publisher/subscriber. This configuration is designed for environments where you have high availability, which you do not have in this scenario. Also, this configuration allows changes of a specific row to be made at only one location at a time. The requirement to recognize conflicts means that changes might be made to the same row at different locations.

You should not configure the home office as a standard transactional replication publisher/distributor and each mobile instance as a standard transactional subscriber. In this configuration, subscriber copies of the database are read-only and do not support updates.

You should not configure the home office as a transactional replication publisher/distributor and each mobile instance as an updating subscriber. This configuration is designed for scenarios where subscriber databases are updated only occasionally, which does not meet the scenario requirements.

References:
Selecting the Appropriate Type of Replication
Microsoft TechNet
http://technet.microsoft.com/en-us/library/ms152565.aspx

Types of Replication Overview
Microsoft TechNet
http://technet.microsoft.com/en-us/library/ms152531.aspx