SQL Server 2005 vs SQL Server 2008
Overview:
Microsoft SQL Server is a relational database management system (RDBMS) produced by Microsoft. Its primary query languages are MS-SQL and T-SQL.
Below is the Release History for SQL server:
Version | Year | Release Name | Codename |
1.0 (OS/2) | 1989 | SQL Server 1.0 | - |
4.21 (WinNT) | 1993 | SQL Server 4.21 | - |
6.0 | 1995 | SQL Server 6.0 | SQL95 |
6.5 | 1996 | SQL Server 6.5 | Hydra |
7.0 | 1998 | SQL Server 7.0 | Sphinx |
- | 1999 | SQL Server 7.0 | Plato |
8.0 | 2000 | SQL Server 2000 | Shiloh |
8.0 | 2003 | SQL Server 2000 | Liberty |
9.0 | 2005 | SQL Server 2005 | Yukon |
10.0 | 2008 | SQL Server 2008 | Katmai |
SQL Server 2005
SQL Server 2005, released in October 2005, is the successor to SQL Server 2000. It included native support for managing XML data, in addition to relational data. For this purpose, it defined an xml data type that could be used as a data type in database columns.
SQL Server 2008
The current version of SQL Server, SQL Server 2008, (code-named "Katmai",) was released on August 6, 2008 and aims to make data management self-tuning, self organizing, and self maintaining with the development of SQL Server Always On technologies, to provide near-zero downtime. SQL Server 2008 will also include support for structured and semi-structured data, including digital media formats for pictures, audio, video and other multimedia data. In current versions, such multimedia data can be stored as BLOBs (binary large objects). SQL Server 2008 can be a data storage backend for different varieties of data: XML, email, time/calendar, file, document, spatial, etc as well as perform search, query, analysis, sharing, and synchronization across all data types. Other new data types include specialized date and time types and a Spatial data type for location-dependent data. Better support for unstructured and semi-structured data is provided using the new FILESTREAM data type, which can be used to reference any file stored on the file system. Structured data and metadata about the file is stored in SQL Server database, whereas the unstructured component is stored in the file system. Such files can be accessed both via Win32 file handling APIs as well as via SQL Server using T-SQL;
- SQL Server 2008 delivers a rich set of integrated services that enable you to do more with your data such as query, search, synchronize, report, and analyze.
- SQL Server 2008 provides the highest levels of security, reliability, and scalability for your business-critical applications.
- SQL Server 2008 also natively supports hierarchical data, and includes T-SQL constructs to directly deal with them, without using recursive queries.
What's new in SQL Server 2008 for SSIS
SSIS (SQL Server Integration Services) is a built in application for developing and executing ETL (extraction, transformation, and load) packages. SSIS replaced SQL 2000 DTS. Integration Services includes the necessary wizards, tools, and tasks for creating both simple import export packages, as well very complex data cleansing operations. SQL Server 2008 SSIS includes a number of improvements and enhancements such as better parallel execution. In SSIS 2005, the pipeline didn't scale past two processors. SSIS 2008 will scale past two processors on multiprocessor machines. Also, the newly redesigned pipeline improves performance on large packages that contain long sub-trees. In addition, the SSIS engine is reported to be more stable with fewer incidents of deadlocks.
Improved Scripting
SQL Server 2008 (Katmai) introduces VSTA as our new scripting engine, which replaces the old VSA engine used in SQL Server 2005 (Yukon). There are a number of advantages to the new engine, but I think the feature users are most excited about is that it allows you to use C# as a scripting language (as well as VB.NET). It also makes it easier to reference all .Net assemblies, which enables a number of common scenarios (such as easier web service, email service and custom components integration).
Merge
SQL 2008 includes the TSQL command MERGE. Using this statement allows a single statement to UPDATE, INSERT, or DELETE a row depending on its condition. The example below demonstrates the MEGRE being used to combine a new list of Inventory Item descriptions into the existing Inventory Master. In addition to new Descriptions, there are some new parts included in the NewInventory table. Without the Merge statement, two commands would need to run. The first would look for a match then upgrade the Description. The second statement would look for non matches and then INSERT. With MERGE, one statement can perform both tasks as shown below.
MERGE
InventoryMaster AS im
USING
(SELECT InventoryID, Descr FROM NewInventory)
AS src
ON
im. InventoryID = src. InventoryID
WHEN
MATCHED
THEN
UPDATE
SET im.Descr = src.Descr
WHEN
NOT
MATCHED
THEN
INSERT
(InventoryID, Descr)
VALUES (src. InventoryID, src.Descr);
Data Flow Task Improvements
It essentially boils down to smarter thread allocation and processing of our execution trees. Long chains of synchronous transforms are optimized, and most packages will see a performance increase on multi-processor machines.
This graph shows much better CPU usage distribution. In this case, the package received an 80% performance increase.
Enhanced ADO.Net Support
Katmai now has an ADO.Net source (replacing the DataReader source) and destination adapters. They function a lot like the OLEDB ones, supporting a custom UI, drop down list of tables/views, and query builder. Its properties are also expression-able. This means we now support ODBC as a destination, as well.
Import/Export Wizard
The SQL Server Import/Export Wizard (which generates SSIS packages) has been improved as well.
- ADO.Net support (which gives you ODBC)
- Data type conversion – mappings are read from customizable (xml) files, and data convert transforms will be inserted into the data flow if required
- Scales better – large numbers of tables will be split across multiple data flows (up to five sources per data flow)
Cached Lookup
The main feature is that you can now create a lookup cache in a separate data flow using the new Lookup Cache Transform and Cache Connection Manager, and persist it to disk. This cache can be created from any data source supported by SSIS, which removes the need to stage your lookup table in a database.
This persisted cache is especially useful in scenarios where reference data is updated periodically – say, once a week. You can run a package to generate your cache when the update comes in, and re-use it in the rest of your packages until it needs to be updated again.
Another scenario is managing large lookup tables, where a small number of keys are used a large percentage of the time. For example, 5% of your customer IDs show up in 80% of your rows. Instead of caching the entire lookup table, you can use a cached lookup for the top 5%, and cascade another lookup in partial / no-cache mode for the infrequent rows.
As you can see from the screen shot, Lookup also has a new "No Match" output – rows with no matches aren't directly sent to the error output anymore. We also maintain a "Miss Cache" internally, so missing keys aren't constantly re-queried.
SQL Server 2008 Top New Features
Policy-Based Management
Policy-Based Management enables the efficient management of multiple SQL Server instances from a single location. Easily create policies that control security, database options, object naming conventions, and other settings at a highly granular level. Policies can evaluate servers for compliance with a set of predefined conditions and prevent undesirable changes being made to servers.
- Management is centralized, thereby reducing the need to configure each server separately
- Administration is simplified, reducing the effort required to maintain standardization and compliance, even in complex environments
- Configuration is straightforward and can be done entirely within SQL Server Management Studio
- Out-of-the-box predefined policies make it easy to get started
- Backwards compatibility supports managing instances of SQL Server 2008, SQL Server 2005, and SQL Server 2000
Performance Data Collection
The Data Collector provides a convenient way to collect, store, and view performance data automatically. It collects disk usage, server activity, and query statistics data, which it loads in a management data warehouse and performance data can be reviewed in SQL Server Management Studio.
- Setup wizard makes configuration simple
- Data collection is automated by using set of SQL Server Agent jobs and SQL Server Integration Services packages
Data Compression
Data compression reduces the amount of storage space needed to store tables and indexes, which enables more efficient storage of data. Data Compression does not require changes be made to applications in order to be enabled.
- Save disk storage
- Enable compression option for individual tables or indexes
- Configuration is easy using the Data Compression wizard
- Applications do not need to be reconfigured as SQL Server handles compression and decompression of data
- Compression can improve disk I/O and memory utilization
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
- Prioritize applications, users, and computers competing for the same resources
- Prevent runaway queries that hold resources for extended periods of time
- Limitations are not enforced when there is no competition for resources
Transparent Data Encryption
Transparent Data Encryption enables 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 de-encrypted by an authorized agent. SQL Server performs the encryption and de-encryption directly, so the process is entirely transparent to connecting applications. Applications can continue to read and write data to and from the database as they normally would. Backup copies of encrypted database files are also automatically encrypted.
- Implements strong encryption keys and certificates to secure data
- Applications do not need to be modified to support Transparent Data Encryption
- Enables compliance with data privacy regulations
- Does not increase the size of the database
Data Auditing
Data Auditing provides a simple way to track and log events relating to your databases and servers. You can audit logons, password changes, data access and modification, and many other events. Tracking these events helps maintain security and can also provide valuable troubleshooting information. The results of audits can be saved to file or to the Windows Security or Application logs for later analysis or archiving.
· Enables compliance with security regulations
· Simple configuration using SQL Server Management Studio
· Minimal impact on performance because audit data is stored outside of SQL Server database files
Backup Compression
Backup compression enables the backup of a database to be compressed without having to compress the database itself. All backup types, including log backups, are supported and data is automatically uncompressed upon restore.
· Save storage space
· Compressed backups can be stored on tape or on disk
· Simple configuration using SQL Server Management Studio
· Default state of all backups on a server to be compressed can be configured
Table-Valued Parameters
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. 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.
· Eliminates the need to use less efficient methods of processing data sets, such as passing XML data into stored procedures
· Reduces complexity and simplifies the process of working with data sets for developers
Virtual Earth Integration
Use the new spatial data types in SQL Server 2008 with Microsoft Virtual Earth to deliver rich graphical representations of the physical locations stored in a database. Use Virtual Earth support to create applications that display data about locations in desktop maps or web pages. For example, SQL Server 2008 makes it easy to show the locations of all company sites that are less than 50 kilometers from Denver.
· Spatial data types enable geographical data to be stored natively in SQL Server2008 databases
· Built-in library of industry-standard spatial functions streamlines development
· Provide meaningful representations of geographical data
DATE / TIME Data Types
SQL Server 2008 introduces several new date and time based data types. DATETIME2 references the Coordinated Universal Time (UTC) instead of the system time for greater accuracy and can store date and time data to a precision of 100 nanoseconds. The new DATE and TIME data types enable you to store date and time data separately. The new DATETIMEOFFSET data type introduces time zone support by storing date, time and offset such as 'plus 5 hours'.
· Greater flexibility for storing date and time data.
· Greater accuracy and precision for date and time data.
· DATETIME and SMALLDATETIME data types still supported.
Partitioned Table Parallelism
Parallelism refers to using multiple processors in parallel to process a query, which improves query response time. On a multiprocessor system, SQL Server 2008 uses parallel processing when you run queries that reference partitioned tables.
When SQL Server 2008 processes such a query, rather than allocating just one processor for each partition referenced by the query, it can allocate all available processors, regardless of the number of partitions referenced.
· Queries against partitioned tables run faster
· More efficient use of processor resources than in previous versions of SQL Server
· Operations involving partitioned indexes are optimized in the same way
Reporting Services
Processing and performance have been improved in SSRS (SQL Server Reporting Server). Large reports will no longer consume all available memory. In addition, there is greater consistency between layout and render. Also, the TABLIX, a cross between a table and a matrix is included in SQL SSRS 2008. Application Embedding allows URLs in reports to point to a calling application.
Microsoft Office 2007
SQL Server 2008 can tightly integrate with Microsoft Office 2007. For example, in SQL Server Reporting Server reports can now export directly to Word. In addition, both Word and Excel can be used as templates for SSRS reports by using the Report Authoring tool. Excel SSAS performance has been improved and there is a data mining add-in.
For anyone looking for any additional reasons to upgrade, this list may help:
Reasons To Upgrade (Worded For Management)
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/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. 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.
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 equirements, performance potentially can be impacted. 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.
Performance Data Collection – collect historical snapshots of system performance in a separate database 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 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 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. CPU's can be added on the fly 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.
0 comments:
Post a Comment