Top 10 SQL Server 2008 Database Administration Features
September 10, 2010
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.
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.
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