Learn About Microsoft’s Exam 70-450

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

Publication Types for Transactional Replication
MSDN
http://msdn.microsoft.com/en-us/library/ms152570.aspx

How Merge Replication Detects and Resolves Conflicts
Microsoft TechNet
http://technet.microsoft.com/en-us/library/ms151749.aspx

Objective: Designing a Backup and Recovery Solution.
SubObjective: Design a backup strategy.

Single answer, multiple choice

You manage a server running SQL Server 2008 Standard edition. The Production database is located on the server. The database is configured to use the full recovery model. A full backup is performed on Friday night. Differential backups are performed Monday through Thursday at 3 a.m. An hourly transaction log is performed between 8 a.m. and midnight Monday through Friday.

You received new vendor pricing information on Wednesday. You create a batch file that will modify the vendor pricing information. You plan to create a job that will run the batch file at 1 a.m. Thursday.

You need to ensure that if the batch file fails, you can restore the database to the state it was in before executing the job. The restore must be performed as quickly as possible. You must still be able to use normal recovery procedures to recover from other failures.

You need to make the necessary configurations.

What should you do?

A.    Add a job step that performs a copy-only backup of the database before the step that runs the batch file.
B.    Add a job step that performs a differential backup of the database before the step that runs the batch file.
C.    Add a job step before the step that runs the batch file to configure the database to use the simple recovery model. Add another job step that performs a full backup.
D.    Add a job step before the step that runs the batch file to configure the database to use the bulk-logged recovery model. Add another job step that performs a log backup.

Answer:
A

Tutorial:
You should add a job step that performs a copy-only backup of the database before the step that runs the batch file. A copy-only backup is a full database backup that does not impact the backup sequence used for normal recovery. Only the copy-only backup would need to be restored if the batch file failed.

You should not add a job step that performs a differential backup of the database before the step that runs the batch file. A differential backup backs up the changes that have occurred since the last full backup. When restoring a differential backup, you need to restore the full backup, the latest differential backup and each transaction log backup taken since the last differential backup.

You should not add a job step before the step that runs the batch file to configure the database to use the simple recovery model and add another job step that performs a full backup. Simple recovery mode allows only full and differential backups. If you change the database to use the simple recovery model, the hourly transaction log backups cannot be performed. Also, taking a full backup would reset the backup base.

You should not add a job step before the step that runs the batch file to configure the database to use the bulk-logged recovery model and add another job step that performs a log backup. If the batch file failed, you would need to recover from the last full backup, Tuesday's differential backup and all transaction logs since Tuesday's backup. When a database is configured to use the bulk-logged recovery model, minimally logged operations such as SELECT INTO and index rebuild are not logged.

References:
Further Info Description
Copy-Only Backups
MSDN
http://msdn.microsoft.com/en-us/library/ms191495.aspx

Objective: Designing a Monitoring Strategy.
SubObjective: Design a solution to monitor performance and concurrency.

Single answer, multiple choice

You are a database administrator for a server running SQL Server 2008.

Users report frequent timeouts during peak periods. You execute Select @@LOCK_TIMEOUT and it returns a value of 1200.

You need to identify whether lock contention is causing the timeouts.

What should you do?

A.    Execute sp_who.
B.    Create a SQL Server Profiler trace that monitors the Deadlock Graph event.
C.    Execute sys.dm_tran_locks.
D.    View the Locks by Process tab in Activity Monitor.

Answer:
B

Tutorial:
You should create a SQL Server Profiler trace that monitors the Deadlock Graph event. The Deadlock Graph event stores XML when a deadlock must be resolved by SQL Server. A deadlock occurs when processes block each other by requesting locks held by the other process. For example, Transaction A holds a shared lock on the Employees table, but requests an exclusive lock on the Salary table. Transaction B holds an exclusive lock on the Salary table, but it cannot resolve until it can also obtain an exclusive lock on the Employees table. In this example, neither transaction can commit and the lock contention cannot be resolved. After 1,200 milliseconds, SQL Server will select a kill victim and roll back its transaction to release the locks it holds.

You should not execute sp_who. The sp_who system stored procedure allows you to view information about current locks, but does not allow you to gather information for later analysis. By the time the timeout occurs, information about the locks held would no longer be available through sp_who.

You should not execute sys.dm_tran_locks. The sys.dm_tran_locks dynamic management view (DMV) also allows you to view information about current locks, but does not allow you to gather information for later analysis. By the time the timeout occurs, information about the locks held would no longer be available through sys.dm_tran_locks.

You should not view the Locks by Process tab in Activity Monitor. Activity Monitor allows you to view current locks and kill processes. It does not allow you to gather information for later analysis. By the time the timeout occurs, information about the locks held would no longer be available through Activity Monitor.

References:
Analyzing Deadlocks with SQL Server Profiler
MSDN
http://msdn.microsoft.com/en-us/library/ms188246.aspx

sys.dm_tran_locks (Transact-SQL)
MSDN
http://msdn.microsoft.com/en-us/library/ms190345.aspx

sp_who (Transact-SQL)
MSDN
http://msdn.microsoft.com/en-us/library/ms174313.aspx

Activity Monitor
MSDN
http://msdn.microsoft.com/en-us/library/cc879320.aspx

Objective: Designing a Strategy to Maintain and Manage Databases.
SubObjective: Design policies by using Policy-Based Management.

Single answer, multiple choice

You administer an instance of SQL Server 2008.

Users in several departments have permission to create and modify databases on the server. Company policy requires all databases on the server to use the same collation as the master database.

You need to ensure that no databases are created that violate company policy.

What should you do?

A.    Create a DML INSTEAD OF trigger.
B.    Configure the server to use the same collation as the master database.
C.    Import the Database Collation policy.
D.    Create an alert that runs a job to roll back a CREATE DATABASE or ALTER DATABASE that uses a different collation.

Answer:
C

Tutorial:
You should import the Database Collation policy. You can import or create policies to ensure that Data Definition Language (DDL) statements, such as CREATE DATABASE and ALTER DATABASE, do not violate company policy. The imported policies are enforced by Policy-Based Management. The Database Collation policy is a default policy included with SQL Server that verifies that all databases have the same collation as master and model. When a policy is configured as an On change: prevent policy and an action occurs that violates the condition, it uses DDL triggers to roll back changes that should be prevented.

You should not create a DML INSTEAD OF trigger. A Data Modification Language (DML) trigger executes when a DML statement such as INSERT, UPDATE or DELETE executes. Creating a database or modifying the collation of a database requires a DDL statement.

Configuring the server to use the same collation as the master database will not meet the requirements. The master database is configured to use the same collation as the server when it is created. Its collation can only be changed by rebuilding master. However, the collation of the server instance (and therefore the master database) does not prevent users from creating a database with a different collation.

You should not create an alert that runs a job to roll back a CREATE DATABASE or ALTER DATABASE that uses a different collation. An alert fires in response to an error condition or a SQL Server event that is logged to the event log. An alert does not fire in response to a DDL statement unless an error occurs. You can, however, create an alert that fires in response to an error raised by policy management. For example, you might import the Database Collation policy and create an alert that notifies an administrator if a user attempts to create a database with the wrong collation.

References:
COLLATE (Transact-SQL)
MSDN
http://msdn.microsoft.com/en-us/library/ms184391.aspx

Set the Collation of User-defined Databases to Match Those of the master and model Databases
MSDN
http://msdn.microsoft.com/en-us/library/bb402915.aspx

Monitoring and Enforcing Best Practices by Using Policy-Based Management
MSDN
http://msdn.microsoft.com/en-us/library/cc645723.aspx

Administering Servers by Using Policy-Based Management
MSDN
http://msdn.microsoft.com/en-us/library/bb510667.aspx

Like what you see? Share it.Share on Google+Share on LinkedInShare on FacebookShare on RedditTweet about this on TwitterEmail this to someone
cmadmin

ABOUT THE AUTHOR

Posted in Certification|

Comment: