SQL Server 2008 Interesting features

SQL Server 2008

Reporting Services

Memory management in SQL Server 2008 Reporting Service is improved. So running large reports will not consume all available memory. In addition, report rendering has more consistency than before.

Encryption

Transparent Data Encryption: the SQL engine encrypts the entire database e.g. all data, log files, Indexes and Tables. Changes to programming applications are not required.

Backup Encryption: Enables encryption of backups to prevent data disclosure or tampering. It can place limitations on the users who are able to restore backups.

External Key Management: Used for involvement with credit card processing or payment card industry compliance. It supports third party Hardware Security Modules 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 2008 allows for monitoring of data changes or access. It is configured in TSQL Statements - 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 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.

Performance Data

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 colleting methods including TSQL queries, SQL Trace, and Performance 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
* Data collection sets-based reports

Entity Data Services

SQL Server 2008 and ADO.NET now allow for high level business objects to be created, such as Customers or Parts. These entities can be used rather than the standard method of returning individual rows and tables. If you’re using E-R (entity relationship) modeling, your objects in SQL will now match your modeling. There are several new ADO.NET frameworks that can access these entities such as the Line-of-Business (LOB) framework and the Entity Query Language (eSQL).

Data Synchronizing Features

The combination of SQL 2008, Visual Studio, and ADO.NET bring together new methods of creating synchronizing or frequently disconnected applications, making it easier to create client applications that synchronize with a central database. SQL 2005 started by providing support for change tracking by using triggers. SQL 2008 synchronizing is better integrated and optimized.

Large UDT

Previously, in SQL 2005, User Defined Types (UDT) could not be larger than 8,000 bytes. In SQL 2008 there is no longer any size restriction, allowing storage of very large UDTs.

Dates and Times

There are new Date and Time data types in SQL 2008.

* Date. This is a data type with a date only, no time.
* Time. A Time data type without a date component. Precision can be up to 100 nanoseconds.
* Date Time Offset. This data type will store a Universal Coordinated Time (UTC) time-zone aware value.

File Stream

The new data type VarBinary(Max) FileStream allows for a way to manipulate binary data using TSQL Select, Insert, Update, and Delete statements. In the past, to store binary data a BLOB, accessed by a Dot.Net application was typically used. Now, SQL functions such as triggers, Full Text Search, and backup restore can be applied to binary data.

Spatial Data

The new Spatial Data type allows Latitude, Longitude, and GPS-based data entries to be natively stored inside SQL Server. The data type conforms to several industry standards such as Open Geospatial Consortium (OGC) Simple Features for SQL and ISO 19125 Simple Feature Access.

Table Value Parameters

In previous versions of SQL Server, there wasn’t a native way to pass a table to a stored procedure. The usual workaround was to pass a large varchar or XML type and parse through it. Now, in SQL Server 2008, Table Parameters are available.

Migration Options

SQL Server 2008 provides migration tools to move databases created in previous versions of SQL Server into the new platform. This will allow us to easily upgrade our current databases. It also supports a side-by-side installation with older versions, allowing us to keep legacy databases running without the need to upgrade. However, this will not be an issue for us as our server architecture will include two new standalone SQL Server 2008 machines.