Introduction

Overview

In terms of difficulty, you will find the Optimizing and Maintaining a Database Administration Solution by Using Microsoft SQL Server 2005 (70-444) exam easier than some of the other Microsoft SQL 2005 exams. This is especially true if you at least have experience with administering SQL Server 2000–based solutions. This is partly because the underlying core responsibilities of the database administrator (DBA) have not changed between releases. Experience with administering SQL Server 2005 of course will help immeasurably.

As a DBA, you are responsible for ensuring optimal performance of your SQL Server 2005 instances. This typically involves both a proactive approach and a reactive approach. Although proactive monitoring will allow you to preempt performance problems before they arise, you will still need to respond to specific performance problems as raised by database users.

Another important responsibility is to design and implement an appropriate disaster recovery strategy. Importantly, this means more than just designing and implementing an appropriate database backup strategy. It’s an end-to-end strategy, including the appropriate RAID technology for your storage, an appropriate backup strategy, potentially off-site storage of your database back-ups, and the implementation of correct procedures.

As a DBA, you will know more about SQL Server 2005’s security model, so it is up to you to ensure that security has been implemented correctly, access has been restricted appropriately, and the correct level of auditing/monitoring has been implemented.

Designing data flow is becoming more common as organizations leverage the capabilities of SQL Server 2005 to import and export data from heterogeneous systems. Although SQL Server Integration Services (SSIS) has been designed for the extraction, transformation, and loading of data, don’t forget that it is not the appropriate tool in all cases.

These are the core areas that will be tested in the 70-444 exam. Real-life experience in these areas will definitely help you in passing the exam, so make sure you are familiar with the functionality available with SQL Server Management Studio (SSMS).

You will also notice that we have provided either the full syntax or the partial syntax of the various Transact-SQL statements and commands. Although we do at times explain some of the key clauses in more detail, that is not always the case. We think it will be useful for you to get exposure to the syntax and various options available, since you will more likely be tested on this, rather than the graphical tools available. So, make sure you understand the syntax and look it up SQL Server 2005 Books Online as required.

Last, we recommend you familiarize yourself with the differences in features between the various editions of SQL Server 2005.