Top 10 Features ISVs Look to in SQL Server 2008

  • Article

The SQL Server CAT team has put together a list of top features that independent software vendor (ISVs) can use with SQL Server 2008. SQL Server 2008 has hundreds of new and improved features, many of which are specifically designed for your large scale applications.

This article presents details of the top 10 features that we believe are most applicable to such applications based on our work with strategic ISV partners. Along with the description of each feature, the main pain-points the feature helps resolve and some of the important limitations that need to be considered are also presented.

Features that do not require any application change:

  1. Data Compression. You can selectively compress any table, table partition, or index, resulting in a smaller on-disk footprint, smaller memory working-set size, and reduced I/O.
  2. Backup Compression. The backup file is compressed as it is written out, thereby requiring less storage, less disk I/O, and less time, and utilizing less network bandwidth for backups that are written out to a remote server.
  3. Transparent Data Encryption (TDE). TDE performs real-time I/O encryption and decryption of the data and log files.
  4. Data Collector and Management Data Warehouse. To provide actionable performance insights, SQL Server 2008 delivers a fully extensible performance data collection and warehouse tool also known as the Data Collector.
  5. Lock Escalation.  Locks can be specified to not escalate, or to escalate to the partition level for partitioned tables. Both these enhancements help improve the scalability and performance without having negative side-effects on other objects in the instance.
  6. Plan Freezing. You can directly create a plan guide (freeze) for any query plan that exists in the SQL Server plan cache to enable greater query performance stability and predictability.
  7. Optimize for Ad hoc Workloads Option. This option is used to improve the efficiency of the plan cache.
  8. The Resource Governor limits can easily be reconfigured in real time with minimal impact on the workloads that are executing.

Features that require some application code change:

  1. Table-valued parameter (TVP) is a new TVP data type, a client application can pass a potentially unlimited sized array of data directly from the client to the server in a single-batch operation.
  2. The new Filestream data type allows large unstructured data to be stored as files on the file system.

For more information including how to access each of the features and which SQL Server editions have which feature, see Top 10 SQL Server 2008 Features for ISV Applications.