10 New SQL Server 2008 Features
April 24, 2009
Microsoft released the feature rich version of SQL Server 2008 to manufacturing on August 8, 2008. Have you got it installed yet? Possibly, you are still in the mist of migrating to SQL Server 2005. Or maybe you have yet to find any features that would require you to obtain SQL Server 2008. In this article, Ill cover 10 new features that were introduced with SQL Server 2008. These features are not presented here in any particular order. Possibly one or more of these features will provide you and your organization with some compelling reasons to obtain and installing SQL Server 2008.
Geospatial Data Types and Functions
Finally, some Geographic Information System (GIS) capabilities have been incorporated into SQL Server. With SQL Server 2008 two geospatial data types where introduced and a number of geographic functions. The two new data types are geometry and geography. The geometry data type is a planer data type that represents the Euclidean coordinate system, or more commonly called the flat earth model. The geography data type represents ellipsoidal data and represents the round earth model. With these new data types, you can identify a specific point on the earth, or other geographic artifacts like roads, lakes, cities, countries, etc.
With the support for the geometry and geography data types Microsoft provides a number of different types of Open Geospatial Consortium (OGC) methods. These methods allow you to define points, linestrings, polygon, distance, intersection, etc. These GIS enhancements should now allow you to easily incorporate mapping functionality into your applications.
When you are running many different kinds of processes on your SQL Server machine, you need a way to control the resource intensive processes so they dont consume all the resources of your machine. The Resource Governor is a way to control those processes that are resource hogs. With the Resource Governor, you can limit CPU and Memory resources for those sessions that can be identified as using excessive resources. The Resource Governor uses classifier functions to identify which workload a particular session should use. The classification of a session in turn associates them with Resource Pools. By limiting the amount of CPU and Memory associated with a Resource Pool, you can control the amount of resources classified sessions can consume. By using the Resource Governor available with SQL Server 2008, a DBA can better control resources usage to provide a balanced throughput of all sessions. The Resource Governor is only available with the Enterprise and Developer editions of SQL Server 2008.
With Policy Management, you can consistently manage your SQL Server 2008 instances through a set of rules, known as policies. You can write policies that will control things like naming standards, server configuration, import/export requirements, etc. By using a standard set of policies across all your SQL Server instances, you can consistently manage your servers and minimize your administrative costs to enforce those policies.
A policy consists of a condition that is defined against a facet that is checked against the appropriate target SQL Server components associated with the facet. A facet is a single SQL Server component that contains properties. Here is a short list of some of the facets available: Credential, Data File, Database, Index, Login, Name, Schema, Server, Table, User and View. The condition is a logical expression that validates that the properties of the facet meet your policy requirements. By using the condition defined in a policy you can control what is appropriate and not appropriate for a given facet.
Policy Management is available in all editions of SQL Server 2008.
To help conserve on the amount of disk space consumed by a table and it indexes, Table Compression was introduced in SQL Server 2008. This feature is only available in the Enterprise and Developer editions of SQL Server 2008.
With Table Compression, there are two different types of compression that can be used: Row and Page. Row compression compresses the fixed fields to save additional space in a row. Page compression goes a step further than row compression. It first does row compression and then performs prefix compression and dictionary compression. Prefix Compression is the process of taking reoccurring column prefix values and storing them in a Compression Information (CI) structure stored in the header of a page, and then replaces the actual values with an index to the value stored in the CI structure. Dictionary Compression is the process of taking reoccurring values in a page and replacing them with the index of the value stored in the CI structure.
Since Table compression is done within the database engine, it is completely transparent to the application. From my limited testing of compressing, it appears that 40-60% space savings can be gained by using Table compression. Your compression savings might vary based on the data types you use and data stored in your database tables.
With table compression there is also the possibility to improve the elapsed time of some of your processes. The performance boost occurs because fewer I/Os need to be performed to read the data. Keep in mind that the additional overhead to compress and decompress also is a cost associated with Table compression. Therefore, to improve the elapsed time of your TSQL statement the cost savings associated with performing less I/Os needs to be greater than the additional CPU cost incurred by performing Table Compression.
New in SQL Server 2008 Enterprise and Developer edition is database Backup Compression. With Backup Compression, the database backups are compressed as the backup file is being written. By using compressed backups, you can save valuable disks space if a disk device is the target for your backups. Compressing backup will help minimize the amount of space needed for your backup. This could help if you dont have a lot of free disk space.
Because less I/O is needed to write compressed database backups to a file, you might see your backup processes run fasters. Keep in mind compressing backups is a CPU intensive process. So if you are running compressed backup statements while other CPU intensive processes are running both the backup and the processes will be affected if you max out the CPU capacity of your machine.
Data Collections/Management Data Warehouse
There is a new statistic gathering and reporting mechanism called Data Collection. By using Data Collection a DBA can easily collect, store and manage statistics about their SQL Server instances. These statistics are stored in a database known as the Management Data Warehouse. The Data Collection mechanism does not only collect data but it also purges old data. When you define data collections, you identify how long you want to keep the data. Using this retention period SQL Server has a built in processes to automatically purge your Data Collection data from the Management Data Warehouse.
Out of the box, Microsoft has provided three different system Data Collections. One collection collects statistics about queries that are run. Another one collects disk space information. The last Data Collection collects information about the System performance. You can also define your own custom data collections.
Also provided are some canned reports that report on the data collect by the system Data Collections. These reports allow you to view trend information from the data collected by these system Data Collections. The reports are feature rich with hyperlinks to allow you to drill down from a high-level report to more specific detailed report. Using these canned reports, you can get a good picture of what is going on within your server.
Much of the data we work with is unstructured data, like images, Word documents, etc. This type of data is typically called binary large objects (BLOBs). Now with SQL Server 2008 you are able use the database engine to manage and store BLOBs as an NTFS file using FILESTREAM. With FILESTREAM, you are able to store BLOBs that exceed 2 gigabytes of space. By default, SQL Server 2008 has disabled FILESTREAM storage. You need to turn it on to take advantage of this kind of storage. Storing your images, Word documents or other BLOBs as a FILESTREAM object allows these objects to be under the control of the database engine. This allows the database engine to backup and restore these objects as part of the database backup/restore process. Also having these objects controlled by the database allows the database engine to also control the security related to these objects. Therefore, users dont have access to FILESTREAM objects unless they are granted access. FILESTREAM is supported by all editions of SQL Server 2008.
New Date and Time Data Types
Finally, Microsoft has realized applications do not always need a time value stored with a date, or a date stored with a time value. With SQL Server 2008 two new data types, DATE and TIME, became available. Now you can use these new data types to store just the component of a point in time that you need, either a DATE or a TIME. Also provided is a new date/time data type known as DATETIME2. DATETIME2 increases the amount of precision you can have on the time portion of your date/time value. With DATETIME2 data types, you can store up to 7 digits of precision with accuracy down to 100 nanoseconds. DATETIME2 is also a variable length field, meaning you can define the number of digits of precision you want to store for the time portion. Allowing you to identify the precision allows you to conserve disk space for your DATETIME2 columns. Lastly, a new DATETIMEOFFSET data type was introduced. This new date type allows you to store date and time values that are time zone aware. This data type also allows you to define the number of digits of time precision you require for your DATETIMEOFFSET values.
Transparent Data Encryption
Transparent Data Encryption is just want it sounds like. Transparent Data Encryption is the process of encrypting your databases at rest transparently from the application. The encryption is done as blocks are written to disks, and then are decompressed when they are read back from disk. So within the buffer pool the database engine works the same, since the data is unencrypted in the buffer pool. There is some increased overhead to perform Transparent Data Encryption. As a number of the other new features mentioned here, this feature is also only available in the Enterprise and Developer editions of SQL Server 2008. When you use Transparent Data Encryption your databases data is encrypted at rest. So if you detach a database it is encrypted and cant be moved to another server unless the encryption keys are also moved to that other server. The database backups are also encrypted, so you can only restore them to other servers if you also backup and restore the encryption keys.
Change Data Capture
SQL Server 2008 is now able to track changes to your database overtime using a new feature called Changed Data Capture. This new change tracking feature is only available in the Enterprise Edition and Developer edition. Once a table in a database is enabled for change data capture all changes to that table are tracked by storing changes in a change table. The change table will contain one record for every INSERT that can be used to identify column values for the inserted records. Each time a DELETE is performed the change table will contain one record for each DELETE that will show the values in each column prior to the DELETE. When an UPDATE is preformed, against a change data capture enabled table, two records will be created in the change table, one with the updated column values and one with the original column values. By using change data capture, you can track changes that have occurred over time to your table. This kind of functionality is useful for applications, like a data warehouse load process that need to identify changes so they can correctly apply updates to track historical changes over time.
What New Feature Do You Need?
Hopefully this list of features has given you some ideas on what you can leverage to improve your SQL Server environment. As you move forward with your SQL Server 2008 planning you can use these new features or enhancements to help justify your migration to this new version of SQL Server. I think Microsoft has done a good job of providing a feature rich version of SQL Server. Hopefully your organization can take advantage of some of these new features in the not too distant future.