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, I’ll 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.
Resource Governor
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 don’t 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.
Policy-Based Management
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.
Table Compression
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.
Backup 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 don’t 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.
FILESTREAM
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 don’t 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 can’t 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.
