Top 10 SQL Server 2008 Database Administration Features


Microsoft SQL Server 2008 is a complete database management solution, providing a bundle of new enhancements, features and functionality to database administrators. This article offers the top 10 new database administration features introduced or enhanced in SQL server 2008.

SQL server 2008 can help your organization in application development,
business intelligence, compliance, data warehousing, hosting solutions, OLTP,
server consolidation, virtualization, transaction processing, data mining,
enterprise reporting and many more. This article covers the top 10 database administrators features
introduced or enhanced in SQL server 2008.

Introduction

Microsoft SQL Server 2008 is a complete database management solution. SQL
server 2008 provides a bundle of new enhancements, features and functionalities
to database administrators for administration, database maintenance,
availability check, performance check, security check etc. In this article, I will discuss the top
10 new database administration features introduced or enhanced in SQL server
2008.

Feature -1- Hot Add CPU and Hot Add Memory

From SQL server 2008 onwards hot add CPU functionality is introduced. That means
you can now add CPUs dynamically to a running system with a SQL server 2008 database server instance. You don’t
need to restart the server as you had to
do for SQL server 2005. Adding CPUs can occur
physically by adding new hardware, logically by online hardware partitioning,
or virtually through a virtualization layer. To
add hot add CPUs, your
hardware must support hot add CPU and your OS
must be a 64-bit edition of Windows
Server 2008 Datacenter or the Windows Server 2008 Enterprise edition. After adding CPUs, execute the RECONFIGURE statement, or SQL Server will not recognize the newly added
CPUs.

Like hot  add CPUs, SQL server
2008 also supports hot  add physical memory (RAM) without restarting the
SQL server 2008 database server instance. However, your
OS must be Windows Server 2003 or above. Before physical memory can be added to
the system, SQL Server must be running in Address Windowing Extensions (AWE)
mode and have the startup (-h) switch enabled. Remember, physical memory removal still needs restart of
the server.

Feature -2- Extending Data Compression in SQL Server 2008 with Fixed Length Data Types

With data compression, you can reduce the disk storage space and increase
query performance by reducing I/O and increasing buffer-hit rates. You can do
data compression of decimal and numeric data types in SQL Server 2005 with
service pack 2 installed. This feature has been extended in SQL Server 2008 to
compress all fixed-length data types, such as integer, char, and float. SQL Server 2008
supports row and page level compression for tables (heap table, clustered
table, nonclustered table and partitioned table), views (indexed views) and
indexes. Compressions of tables, views are applied automatically but not for
nonclustered indexes. Compression settings for indexes need to be set
separately using the CREATE TABLE/CREATE INDEX or ALTER TABLE/ALTER INDEX
statement. DBAs can also measure estimated space savings in a table or index by
using either the sp_estimate_data_compression_savings system stored procedure
or the Data Compression Wizard.

Feature -3- Backup Compressions

With compression backup, SQL Server 2008 reduces a
significant amount of backup and restore operation time. By default,
backup compression is turned off on the server instance; you need to explicitly
write backup TSQL command with the COMPRESSION
keyword. The following TSQL statements will take a
compression backup of MyTestDatabase.

BACKUP DATABASE MyTestDatabase
TO DISK = 'C:SQLDataMyTestDatabaseBackup.bak'
WITH COMPRESSION, INIT

Execute the following TSQL statement to restore the compressed database.

USE master 
GO 

RESTORE DATABASE MyTestDatabase 
FROM DISK = N'C:SQLDataMyTestDatabaseBackup.bak' 
WITH MOVE N'MyTestDatabase_Data' TO N'C:SQLDataMyTestDatabase_c.mdf', 
MOVE N'MyTestDatabase_Log' TO N'C:SQLDataMyTestDatabase_c.ldf', 
RECOVERY, 
STATS = 10; 
GO

To enable compression on the server instance level use the following TSQL command.

USE master 
GO 

EXEC sp_configure 'backup compression default', '1' 
RECONFIGURE WITH OVERRIDE 

Feature -4- Resource Governor

To manage SQL server workload and system resource consumption, SQL server 2008 introduces a new feature called
Resource Governor. Using Resource Governor, you can now specify limits on
the amount of CPU and memory utilization that an incoming application requests
can use. SQL server 2008 Enterprise, Developer, and Evaluation
editions have Resource Governor.
Resource Governor can address run-away
query issues on the server,
unpredictable workload execution and set
workload priority. It also has the ability
to monitor resource usage for each workload in a group, the ability to pool resources and set
pool-specific limits on CPU usage and memory allocation, the ability to associate grouped workloads with a
specific pool of resourcesand the ability
to identify and set priorities for workloads etc.

Feature -5- Policy-Based Management

policy-based management feature SQL server 2008 DBA’s can easily manage
multiple instances of SQL server instance from a single location. You can now
easily create policies to control security, database options, object naming
conventions, and other settings for multiple servers using QL server management
studio. Policy based manage management has following advantages.

1. No need to configure each instance of SQL server separately as you can
manage all the servers from a central location. Also centralized policy
configuration is simple (using SQL server management studio) that reduces
effort of maintaining complex environment.

2. Policy-based management is backward compatible,
which means you can manage instances of SQL Server 2008, SQL Server 2005, and SQL
Server 2000 all together.

Feature -6- Performance Data Collector

Using SQL
server 2008 performance data collector,
DBAs can now collect, store and view performance
data of multiple SQL server instances automatically. It collects disk usage,
server activity and query statistics data and stores in a shareable management data warehouse (MDW); this
performance data can be reviewed in SQL server management studio. You can also
generate reports from this data using SQL server reporting service. Performance
data collector provides a simple setup wizard for configuration. Advantages of
Performance data collector as follows.

1. Performance data collector acts as a central data repository for all
performance related data. Performance data
collection is automated by using SQL server agent jobs and SQL server
Integration services packages.

2. Data management is centralized so data collection can be easily
configured and simple reports can be generated
using reporting service.

Feature -7- Data Auditing

SQL Server 2008 introduces a number of attractive auditing features for DBAs to better
management. It has rich and deeply integrated auditing capabilities that offer major improvements over
Microsoft SQL server 2005. New features are as follows.

1. Your audit logs now can be configured from SQL Server Management studio itself.

2. Audit logs are now stored outside of SQL Server database files; no impact of
performance of your database server.

3. Introduced fine-grained auditing whereby
events can be targeted to specific actions on an object by particular
principals.

4. You can now save audit logs in ‘windows application’ and ‘security’ logs
of the database server.

5. New auditing features are now in
compliance with security regulations (like HIPPA)

Feature -8- SQL Server Extended Events

SQL Server
2008 extended events is a general event-handling system that provides an
in-depth troubleshooting tool that helps administrators to address
difficult-to-solve problems more efficiently. Using extended events, DBAs can now investigate and troubleshoot
excessive CPU usage, deadlocks and application time outs as well as many other
issues. Extended events data can be integrated with Windows
events for resolving any complex problem. Extended events data can
be accessed by tracing tools (system monitor, SQL trace. etc) and logging tools (SQL Server log, Windows event
log. etc)

Feature -9- Streamlined installations

SQL Server 2008 installation procedure is easier and
streamlined now. Installation wizard now asks
you to select individual, optional SQL
Server components such as database services, analysis services, and integration
services. Failover cluster configuration is also added to the installation
wizard.

Feature -10- Star Join Query Optimization

Star Join Query Optimization is a new feature
introduced in SQL server 2008 enterprise edition. A star schema is an
implementation of data warehouse that has a fact table at its centre. Any data
warehouse contains a very large number of rows. Star Join Query Optimization has provided significant improvements
in performance for queries (mainly select queries) for retrieving a subset of
rows.

Conclusion

SQL Server 2008 provides the technologies and capabilities to the database
administrators for better management. All the above-mentioned
features are the most important features of SQL server 2008. To know more; see
Microsoft SQL server official site http://www.microsoft.com/sqlserver/2008/en/us/default.aspx

Related Articles

Microsoft Everybody’s Business – SQL Server 2008 R2

»


See All Articles by Columnist

Tapas Pal

Latest Articles