SQL Server 2005 vs SQL Server 2008

For anyone looking for any additional reasons to upgrade, this list may help:

Reasons To Upgrade (Worded For Management)

1. Upgrading to 2008 will give an additional 3 years of support. So you’re looking at 7-8 years of support as opposed to 4-5 years on 2005. Source: founder of SQL Server Central. http://www.sqlservercentral.com/articles/Administration/3094/
2. SQL 2005 SSIS – Buggy and has major shortcomings. Any enhancements for SSIS 2005 are halted.
   http://ayende.com/Blog/archive/2007/07/15/SSIS-15-Faults.aspx
• Cannot create a global class in a project (i.e. – you have to re-write code everywhere)
• CLR Framework is only a subset (Does not support External Assemblies or COM Interop)
• DataTypes for transformations do not automatically map (have to manually be mapped using the mouse)
• It is very hard to debug a package
• Built-in logging displays tons of useless information and very little useful information
• Difficult to find out specific information on the record-level about why an error occurred
• Limited data types available in their flat-file connectors
• Data flow tasks cannot include logical conditional flows like the process flows.
• Script editors use VB.Net only (…my opinion)
• Overall, just very sensitive and annoying.
3. SSAS – Being a complete re-write, the 2005 Analysis services is less mature. Performance enhancements have been made in 2008. Personal experience has shown many errors when aggregating cubes and slower than expected on-the-fly aggregations than in SQL 2000 AS.
4. Auditing – SQL 2008 implements auditing out of the box and functions asynchronously not hindering performance. Using SQL 2005 we will have to manually write auditing functionality or go third party. Depending on future auditing requirements, performance potentially can be impacted.
5. Resource governor – SQL 2008 provides the ability to limit the resources of queries. This often happens with reporting procedures. Limiting the resources of non application centric resources will help end-user experience.
6. Performance Data Collection – collect historical snapshots of system performance in a separate database
7. Reporting Services – 2005 reporting services is very resource intensive and is not practical unless on a separate installation of SQL Server to perform report pagination/rendering. 2008 Reporting services is a rewrite of the reporting engine. http://www.microsoft.com/sqlserver/2008/en/us/wp-sql-2008-performance-scale.aspx
8. Full text search – This is now integrated into the SQL Server engine and performance has been enhanced. http://www.microsoft.com/sqlserver/2008/en/us/wp-sql-2008-performance-scale.aspx
9. Change Data Capture – For requirements to save or monitor historical information on changed data. Using SQL 2008 we can implement a non-invasive detection of changed records.
10. CPU’s can be added on the fly
11. Ability to compress data and use less disk space

Reasons For Developers

Change Data Capture (CDC) – very solid and great to use for determining changed records.

Additional enhancements made to minimal logging for inserts

Intellisense – Built in finally

Resource Governor – Very cool. Throttle the resources of users based on Memory or Processor

Declare and set a variables inline

New Grouping Sets allows analysis-like data in OLTP (Grouping Sets)

Table Valued Parameters – Pass a table into a stored procedure as a parameter

Merge Statement – No more upserts. Now you can perform update and insert operations in on statement. Great for updating historical tables

New DMV shows all object dependencies

Data compression – Compress tables at the row level or the page level. Saves a tremendous amount of space.