11. Close SQL Server Management Studio.
Summary of Exam Objectives
In this chapter you have learned about ongoing maintenance tasks you must perform
to keep your databases functioning well.
Data collation defines how text data is stored, sorted, and compared. Specifically,
data collation defines the code page to use with non-Unicode (char, varchar, text)
text data types. It also defines whether sort and comparison operators are case sensi-
tive, accent sensitive, kana sensitive, and/or width sensitive. Collation names provide
clues to the functionality of data collation, for example, French_CS_AI data colla-
tion uses French character codepage and is case sensitive and accent insensitive.
Binary collations are always case and accent sensitive. You can assign data collation at
server, database, and column level. Use the COLLATE collation_name clause with
the SELECT statement to select a specific collation for sort, comparison, or join.
You must maintain your database by regularly checking for data validity,
managing disk space, and optimizing database performance. Data validation and
repair is performed using the Database Console Commands (DBCC). Although
many commands are available, you should learn about DBCC CHECKDB and
CBCC SHRINKFILE in detail. You have also learned about page and row level
compression, which can be used to maximize the utilization of disk space by your
tables. Mark columns as sparse to reduce the space used by storing null values.
Sparse columns can be efficiently used with column sets and filtered indexes.
Maintenance tasks, including database validation, index maintenance, and
backup are key to the health of your SQL Server. To automate these tasks, use SQL
Server Agent to create multi step jobs and run them on a schedule. For proactive
monitoring, use SQL Server Agent alerts to notify operators when an error occurs.
The Maintenance Plans feature allows you to visually consolidate many individual
administrative tasks into a coherent strategy based on proven best practices. Finally,
the new Policy-Based Management feature of SQL Server 2008 allows administra-
tors in large organizations to monitor and configure any number of SQL Servers
and databases for configuration settings and compliance. Using this feature, you can
also enforce compliance rules and standards on the naming and configuration of
SQL Servers and SQL Server objects.
Creating and executing a disaster recovery strategy is a key part of any database
administrator’s job. The disaster recovery strategy consists of a backup strategy that
encompasses the type and frequency of backup and a restore strategy that specifies
how data is to be restored in the event of a disaster. SQL Server offers many backup
and restore types to meet your organization’s needs. You can back up an entire
database as a whole or only the changes since the last backup. You can also back up
the log file and then replay it into a database restored from a full backup. Restoring
databases is a highly flexible operation in SQL Server 2008, allowing you to restore
a full backup, merge differential backup changes into the full backup, and replay
logs to a point-in-time to roll the database forward. Backup compression is a new
feature of SQL Server 2008 Enterprise Edition that delivers faster, smaller backups.
Importantly, you can restore parts of a database while the unaffected parts remain
accessible to users through an online restore.
The Data Collector feature of SQL Server 2008 uses scheduled SQL Agent jobs,
data structures, and tools designed to collect and analyze data from various sources.
The Data Collector can be used to collect and consolidate performance data, custom
collection items, and sets from compatible data providers. Data collected by the
Data Collector is stored in the management data warehouse. You can create
SQL Server Reporting Services reports to view and analyze the data. Some reports
are available by default.
Exam Objectives Fast Track
Understanding Data Collation
Data Collation defines the code page for multilingual data and rules
Ü
for sort, and comparison of this data (case, accent, kanatype and width
sensitivity).
The server-level default collation applies to all new databases unless a
different database-level collation is specified. The database-level collation
applies to all new table columns unless a different column-level collation is
specified.
If a database is restored to a server with a different collation, the database-
level and column-level collations will remain as they were originally
You can use the COLLATE collation_name option in a SELECT statement
to explicitly specify a collation to use in the ORDER BY, WHERE or
JOIN clauses.
Maintaining Data Files
To reduce the disk space used by your database, consider implementing data
compression. Data compression can be set at the row level and page level.
Additionally, you can implement sparse columns to decrease the space it
takes to store null values.
You can view index usage statistics and new index suggestions by querying
dm_db_missing_index_details, dm_db_index_operational_stats and dm_
db_index_physical_stats Dynamic Management Views (DMVs).
You can rebuild and reorganize indexes by using the ALTER INDEX
statement with the REBUILD or REORGANIZE clause. You can also
specify a FILLFACTOR to determine how much free space should be
allocated for future inserts on index pages.
The Database Console Commands (DBCC) are a useful set of commands
that allow you to view and manipulate your databases. For example,
you can verify the integrity of all tables in a database using DBCC
CHECKDB, or shrink a database or log file using DBCC SHRINKFILE.
Backing Up and Restoring Data
The Database Recovery Model defines how transaction logs are used for
a database. Simple recovery model overwrites the transaction log as
needed. The Full recovery model forces all transactions to be written to
the log without exception. Bulk- Logged recovery model records most
transactions, except the Bulk-Logged operations like TRUNCATE
TABLE. The Full recovery model should be used in production, and is
the only recovery model that guarantees the ability to restore transaction
logs into a database.
SQL Server allows you to backup an entire database (known as full
backup), individual files and filegroups, changes to database extents since
the last backup (known as a differential backup) or create a copy-only
backup that does not interfere with normal backup schedule.
Transaction log backup can be taken, and then replayed into the database
when restored. Backing up and restoring a salvaged transaction log after a
restore operation is known as Tail-Log backup and restore.
You can roll the database forward to a specified point in time by replaying
transaction logs into a database during restore.
An online restore is a way of restoring individual files, filegroups and even
pages into an existing database, while the rest of the database remains
accessible by users.
Bạn đang xem 11. - THE REAL MTCS SQL SERVER 2008 EXAM 70 432 PREP KIT P63 PPSX
