What’s new in SQL 2008 Part 1

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

Don Schlichting
Don Schlichting
Don Schlichting is a Microsoft MSCE, MCDBA and an Oracle Certified Associate. Working at Professional Control Corporation as the IT Director,.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles