Learn About Microsoft's Exam 70-450

  By Certification Magazine —

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.