Why Upgrade to Microsoft SQL Server 2008?

With the new release of SQL Server 2008, there are many benefits in upgrading the existing applications to the latest release. SQL Server 2008 provides a Data Platform for enterprise applications. Let’s learn more about the new features available in SQL Server 2008.

1) Performance Enhancements – Many performance enhancements have been made throughout the technology stack, including enhancements within Analysis Services, Reporting Services and Integration Services.

1. Fixed Query Plan Guides– SQL Server 2008 allows existing query execution plans to be imported. One can revert to a known good execution plan if query performance degenerates.
2. Star Join Query Optimization–Star join query optimization happens automatically, applications do not need to be configured to use it. When SQL Server processes queries using star join query optimization, bitmap filters eliminate rows that do not qualify for inclusion in the result set very early on, so that the rest of the query is processed more efficiently. Hence, the query processing for data warehouses is significantly quicker.
3. Improved performance for lookup transformations – SQL Server 2008 Integration Services uses persistent lookups so that data loaded into the lookup cache is available to other packages, or to multiple pipelines within the same package, without the need to reload the cache.
4. Partition Aligned Indexed Views ‐ Indexed views improve performance with very large partitioned tables, such as fact tables in data warehouses. Indexed Views let SQL Server persist the results of a view, instead of having to dynamically combine the results from the individual queries in the view definition. Indexed Views can now be created to follow the partitioning scheme of the table that they reference.
5. Table‐valued parameters ‐ Table‐valued parameters make the development of stored procedures that manipulate data more straightforward and can improve performance by reducing the number of times a procedure needs to call a database.

2) Enhanced Business Intelligence Capabilities – Additional reporting capabilities integrated with Microsoft Office applications and a new report designer application allows the creation of enterprise reports without the need for Developer Studio.

1. Change Data Capture (CDC) – It can be used to track changes to the data in the tables. CDC uses a SQL Server Agent job to capture insert, update and delete activity. CDC can be used in conjunction with Integration Services to incrementally populate data warehouses, thus enables you to produce more frequent reports that contain up‐to‐date information.
2. Cell Write back in SQL Server Analysis Services – It enables users to perform speculative analysis on data i.e. users can modify specific data values and then issue queries to see the effect of the changes.
3. Data Profiling – SQL Server 2008 Integration Services includes the Data Profiling task, which enables the quality of data to be inspected before adding it to your databases.
4. IIS Agnostic Report Deployments – No need to install IIS to use Reporting Services.
5. Multiple Sources – The reporting engine in SQL Server 2008 Reporting Services enables the pulling together of data from multiple heterogeneous sources from across an Enterprise.
6. MERGE operator – streamlines the process of populating a data warehouse from a source database and reduces the need to use other less performance‐efficient methods such as Lookup transformations, to achieve the same functionality. The MERGE statement distinguishes between the new and updated rows from the source database so that the appropriate action (insert or update) can be performed against the data warehouse in one single call.
7. Best Practice Design Alerts – SQL Server 2008 Analysis Services uses Analysis Management Objects (AMO) warnings to alert when the choices made in design deviate from best practice. Design problems are underlined in blue, similar to the way spelling mistakes are underlined in red in Microsoft Office Word.
8. Improved Rendering for Microsoft Office Word and Excel – Users can view and edit reports without having to install special software.

3) Developer Productivity – Tools like Entity Framework and LINQ, new date/time, FILESTREAM and spatial data types provide powerful and easy to use application development enhancements.

1. Language Integrated Query (LINQ) – It is a .NET Framework version 3.5 feature that provides developers with a common syntax to query any data source from client applications. Using LINQ to SQL or LINQ to Entities, developers can select, insert, update, and delete data that is stored in SQL Server 2008 databases using any .NET programming language such as C# or VB.NET.
2. Filtered Indexes – A filtered index is essentially an index that supports a WHERE condition and includes only matching rows.
3. Table‐Valued Parameters – Eliminates the need to use less efficient methods of processing data sets, such as passing XML data into stored procedures. Table‐Valued Parameters (TVPs) allows stored procedures to accept and return lists of parameters. Developers can write applications that pass sets of data into stored procedures rather than just one value at a time.
4. BLOB FILESTREAM – It enables binary large object (BLOB) data to be stored in the Microsoft Windows NTFS file system instead of in a database file.
5. Sparse Columns – They provide an efficient way to store NULL data in tables by not requiring NULL values to take up space.
6. New GEOGRAPHY and GEOMETRY data types – They allow spatial data to be stored directly in a SQL Server 2008 database to deliver rich graphical representations of the physical locations stored in a database.

4) Predictable/Reliable System Response – New features such as Query Governor and Data Compression along with general scalability enhancements provide scalable solutions that are more reliable for very large enterprise systems.

1. Data Compression – Reduce the amount of storage space needed to store tables and indexes, which enables more efficient storage of data.
2. Resource Governor – The Resource Governor enables administrators to control and allocate CPU and memory resources to high priority applications. This enables predictable performance to be maintained and helps avoid performance from being negatively affected by resource‐intense applications or processes.

5) Improved System Management Capabilities – Centralized Management

1. Policy Based Management – Easily create policies that control security, database options, object naming conventions, and other settings at a highly granular level.
2. Performance Data Collection – Data collection is automated by using a set of SQL Server Agent jobs and SQL Server Integration Services packages.

6) Enhanced Security and Auditing

1. Transparent Data Encryption – Enable data to be stored securely by encrypting the database files. If the disks that contain database files become compromised, data in those files is protected because that data can only be decrypted by an authorized agent.
2. Data Auditing – SQL Server 2008 provides a simple way to track and log events relating to your databases and servers.
3. External Key Management / Extensible Key Management – Storing the keys separately from the data enables a more extensible and robust security architecture.

7) Simplified Administration

1. Conflict detection in Peer‐to‐Peer replication.
2. Extended Events ‐ The extended events infrastructure provides an in‐depth troubleshooting tool that enables administrators to address difficult‐to‐solve problems more efficiently.
3. Enhanced database mirroring.
4. Dynamically add memory and processors to servers without incurring downtime.

Related Posts

1. An Introduction to Microsoft SQL Server 2008 – Part 1
2. Microsoft SQL SERVER – Security
3. SQL Server 2008 Top New Features
4. Ad hoc Reporting using SQL Server Reporting Services 2005
5. Step by Step : SQL Server 2005 Analysis Services (SSAS) Project

Tags: Business Intelligence, Performance Enhancements, Simplified Administration, SQL Server 2008, SQL Server 2008 Features