Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Aug 3, 2007

What's new in SQL 2008 Part 1

By Don Schlichting

Introduction

SQL Server 2008 will be released approximately February of 2008, along with a new version of Visual Studio and Windows. A CTP (Community Technology Preview) of SQL 2008 is currently available for download from the Microsoft URL http://www.microsoft.com/sql/prodinfo/futureversion/default.mspx . This article will highlight some of the new features and benefits found in SQL Server 2008.

SQL 2000 Support Ends

Mainstream Support for SQL 2000 is coming to an end. Feature and benefits included in Mainstream Support include the ability to submit requests for product feature changes, Security Updates, Non Security Hotfixes, Complimentary support, and Paid Support. This Mainstream Support will expire on 4/8/2008 for SQL Server 2000 64-bit Edition, SQL Server 2000 Developer, SQL Server 2000 Enterprise, SQL Server 2000 Standard, SQL Server 2000 CE, and SQL Server 2000 Workgroup Edition. Extended Support, consisting of Security Updates and Paid Support will continue until 2013. A full description of support phases can be found at these Microsoft URLs: “Microsoft Support Lifecycle” and “Microsoft Support Lifecycle Policy FAQ”. Many resellers will discontinue selling SQL 2000 in December of 2007. Also, no version of SQL 2000 will be supported on Vista, including SQL 2000 Express.

Key Areas

One of the first items noticed when reviewing Microsoft documentation on SQL Server 2008 is the new lingo classifying features and benefits into groups, or Key Areas. There are four Key Areas, Enterprise Data Platform, Dynamic Development, Beyond Relational Database, and Pervasive Insight. This article will follow these groupings in an effort to make cross referencing with Microsoft articles easier.

Enterprise Data Platform

The Enterprise Data Platform grouping, sometimes referred to as the Mission Critical Platform, contains the core SQL Server Engine features and benefits including Data Encryption methods, Resource Management, System Analysis, and Server Management Features.

Encryption

There are several encryption options; the first is called Transparent Data Encryption. In SQL Server 2008, the entire database can be encrypted by the SQL Engine. This method encrypts all database data and log files for a database. By using this all-encompassing method, all Indexes and Tables are encrypted. Changes to programming applications are not required.

The next encryption feature is Backup Encryption. SQL Server 2008 has a method of encrypting backups to prevent data disclosure or tampering. In addition, Restoring of backups can be limited to specific users.

Lastly, there are new options for External Key Management. If you are involved with credit card processing or PCI (payment card industry) compliance, SQL 2008 will support Hardware Security Modules (HSM). Hardware Security Modules are third party hardware solutions used to store Keys in a location separate from the data they protect.

Auditing

In addition to the standard auditing of logon / logoffs and permission changes, SQL Server 2008 allows for monitoring of data changes or access. Auditing is configured by TSQL statements as in this example: AUDIT UPDATE(Salary) ON Employee TO MyAuditFolder WHERE Salary>200000

Data Compression

Usually, data compression is associated with general hard disk savings, and with smaller physical files, backup times are reduced. While this holds true for SQL Server Data Compression, the main goal is Fact Table size reduction. The stated advantages for Data Compression include the following:

  • Improves query performance by reducing I/O and increasing buffer-hit rates
  • Provides compression ratios of 2X to 7X for real DW fact data
  • Is orthogonal to other features
  • Is available for both data and indexes

According to Microsoft, while using compression will slightly increase CPU usage, overall system performance will be improved because of less IO.

Resource Governor

New in SQL Server 2008 is the Resource Governor. The Governor is used to restrict users or groups of users from consuming high levels of resources. Items that can be monitored include CPU bandwidth, timeout waits, execution times, blocking times, and idle times. If a Resource Governor threshold level is reached, the system can trigger an event or stop the process. There are a series of steps involved in utilizing the Resource Governor that will be reviewed in an upcoming article.

Hot Plug CPU

In SQL Server 2008, additional CPUs can be added on the fly if the underlying hardware supports it.

Performance Data

The amount of Performance Counters in SQL Server 2008 has been expanded compared to earlier versions. IO and memory usage counters are just a couple of the items that can be collected to monitor performance. The data collected by the counters is stored in a centralized data warehouse. Microsoft states that running the default set of performance related monitors will consume less than 5% of CPU and memory resources.

There is also now a Performance Dashboard tool that can read saved performance data. In addition, historical and baseline comparisons can be made and used to create action triggers. For example, if memory use exceeds a threshold for more than five minutes, a more detailed data collection can be automatically triggered.

New in SQL Server 2008 is the Performance Studio. The Studio is a collection of performance tools. Together they can be used for monitoring, troubleshooting, tuning and reporting. The Data Collector component of the studio is configurable and low overhead. It supports several collecting methods including TSQL queries, SQL Trace, and Perfmon Counters. Data can also be collected programmatically. Once data is collected, there are drill-down and aggregate reporting options. Microsoft lists these six client side features of the Performance Studio:

  • SQL Server dashboard
  • Performance monitoring
  • Current and historical data analysis
  • Suggestions for potential performance tuning
  • Data collection sets-based reports
  • MDW-based reports

Installation

Setup and Installation of SQL Server 2008 has also been enhanced. Configuration data and engine bits have been separated so it will be possible to create a disk image of a basic unconfigured system making distribution to several servers easier. Also, the installation will be able to retrieve the latest updates from the Microsoft web site. Another feature is the ability to install SQL, service pack, and patches as a single step. On the other end of this feature, is the ability to uninstall service packs.

Conclusion

There are many significant features and benefits coming in SQL Server 2008. In Part 2 of this series, we’ll examine Development, Integration, Business Intelligence, and new Data Types.

» See All Articles by Columnist Don Schlichting



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date