BCHAPTER 7MCTS SQL SERVER 2008 EXAM 432MAINTAINING YOUR DATABASEEXAM OBJECTIVES IN THIS CHAPTER

20. B

Chapter 7

MCTS SQL Server 2008

Exam 432

Maintaining

Your Database

Exam objectives in this chapter:

Understanding Data Collation

■

Maintaining Data Files

Backing Up and Restoring Data

Performing Ongoing Maintenance

Performance Data Collection

Exam objectives review:

˛ Summary of Exam Objectives

˛ Exam Objectives Fast Track

˛ Exam Objectives Frequently Asked Questions

˛ Self Test

˛ Self Test Quick Answer Key

Introduction

Ongoing maintenance, preventative monitoring, and fine-tuning are key to the health

of your SQL Servers and the ongoing operation of your organization. Plan mainte-

nance tasks thoroughly and perform them regularly. In this chapter, you will learn

about the most critical database maintenance tasks and the factors that influence them:

storing and maintaining international data; optimizing data files; and daily operations

like backups, restores, and gathering performance data for preventative monitoring.

A key task of any database administrator is to implement a robust disaster

recovery strategy. This strategy ensures business continuity. A clearly defined disaster

recovery strategy formalizes your requirements, procedures, and expectations related

to data protection. Backup is a critical part of any disaster recovery strategy.

Choosing the correct backup parameters like type, frequency, and media allows you

to meet the data recovery objectives set by your organization. Backup strategies are

implemented so that at some point you can perform adequate data recovery.

SQL Server allows you to perform recovery at several levels, from individual corrupt

data pages to the entire database or server settings.

SQL Server 2008 is designed to deliver the best possible performance by

allowing administrators to fine-tune the system to meet their organization’s specific

needs. The Data Collector feature is a new feature that allows you to systematically

collect and analyze performance related and other data. The centralized performance

data repository allows you to better understand the performance of your SQL Server

as a whole, as well as examine its interactions with environmental dependencies.

In today’s global economy, many organizations need to store data in different

languages in addition to sorting and querying it based on culture-specific rules.

SQL Server 2008 fulfills this need. SQL Server 2008 allows you to store data from

multiple languages in a single database column. It also allows you to define specific

languages for individual server instances, databases, and database columns. Collation is

the set of rules for storing, sorting, and comparing text data. Collation affects most

database operations. Collation can affect query results, the ability to import and

export data, as well as backup and restore operations. To be able to effectively

maintain SQL Server 2008 databases in a multilingual environment, you must

understand data collation.

When you perform a fresh install of SQL Server, you are asked to select a data

collation, as shown in Figure 7.1. The collation you choose becomes the collation

of the Model database as well as the default collation for all new databases. What