With the release of Microsoft SQL Server 2008, SQL Server has become more than just a relational database hidden behind your corporate applications. Microsoft’s ambitious goal for SQL Server 2008 is for it to be an enterprise data—not a database—platform, and Microsoft is positioning SQL Server 2008 to become the provider for all your enterprise information needs. With its built-in integration, analysis, and reporting services, SQL Server 2008 is more than ready to meet this expectation. Even slogan for new sql server version sounds as "Harness the power of data explosion".

In comparison with sql server 2005 new version of sql server - ms sql server 2008 (code-named Katmai) did not change dramatically. In terms of functionality it was more improved than extended. Main sql server 2008 features are Hot Add CPU support, Resource Governor, data compression, transparent data encryption (TDE), Policy-Based Management, and change data capture (CDC). But all this tasty features are available in Enterprise editioin only. If your edition is not enterprise you will get only new data types, support for T-SQL debugging and IntelliSense auto-completion, and business intelligence (BI) enhancements. Not to much to migrate you current SQL Server 2005 Standard Edition to new 2008 one just right away.

With the release of Microsoft SQL Server 2008, SQL Server has become more than just a relational database hidden behind your corporate applications. Microsoft’s ambitious goal for SQL Server 2008 is for it to be an enterprise data—not a database—platform, and Microsoft is positioning SQL Server 2008 to become the provider for all your enterprise information needs. With its built-in integration, analysis, and reporting services, SQL Server 2008 is more than ready to meet this expectation. Even slogan for new sql server version sounds as "Harness the power of data explosion".

 The next goes overview of new features in detail.

Hot Add CPU support. Hot Add CPU support lets SQL Server 2008 recognize and use any new processors added to the system without needing to reboot the server or stop and start the SQL Server service. Support for Hot Add CPU augments SQL Server’s existing Hot Add RAM support, well-equipping SQL Server 2008 to be installed in virtual environments and take part in dynamic workload management.

Resource Governor. Possibly the most important new feature in SQL Server 2008 is the Resource Governor, that lets you control the amount of system resources that SQL Server will allocate to a given workload. For example, the Resource Governor lets you limit the system resources consumed by poorly designed end-user queries that could otherwise adversely impact the system’s overall performance. The Resource Governor can also provide more predictable execution for queries and jobs running on the system.tail

Data compression. The new data compression capability is completely transparent to client applications and requires no application changes to use compression. SQL Server 2008’s data compression can reduce the size of database data stored on disk and can also significantly decrease backup and restore times by reducing the required I/O.

Transparent data encryption (TDE). TDE extends SQL Server 2005’s encryption capabilities. SQL Server 2005 encrypts data at the cell level using encryption functions. However, managing the encryption keys is a manual process, and applications require code changes to access encrypted data. SQL Server 2008’s TDE lets you encrypt an entire database and have the encryption be completely transparent to end-user applications.

Policy-Based Management. Policy-Based Management lets a database administrator enforce corporate standards such as database-configuration settings and object-naming conventions across multiple servers.

Change data capture (CDC). For organizations needing to populate data warehouses or other external databases from SQL Server, the new CDC feature alone might be worth the upgrade. CDC does away with the need to write custom code to capture changes. Instead, changes to tables are automatically captured from the transaction log and propagated to a set of target capture tables. CDC can also enhance auditing by capturing all the data changes to target tables.

IntelliSense, debugging, and code-outlining support. In sql server 2000 query analyzer and in SQL Server 2005 you dreamed about IntelliSense auto-completion and the ability to comfortably debug T-SQL code. SQL Server 2008’s new IntelliSense support—in SQL Server Management Studio’s (SSMS’s) Query Editor—provides T-SQL syntax checking and automatic prompting for database object names.

 Yes. Sql server management studio 2008 looks very similar to 2005. Auto-complete feature does not start hinting right after you open query editor. It requires some time to gather metadata information. Every syntax error is marked red color.

New data types. Unlike the DATETIME data type in earlier SQL Server versions, which required you to combine date and time data in one column, SQL Server 2008’s new DATE and TIME data types let you store data and time values discretely and there are other new date and time data types that enable more accurate storage of data and time data. A new FILESTREAM data type allows high-speed access to large objects (LOBs). SQL Server 2008 also provides new spatial data types that you can use to build mapping applications.

T-SQL enhancements. As you’d expect from a new SQL Server release, SQL Server 2008 includes a collection of T-SQL enhancements. The DECLARE statement now lets you assign values to a variable. New compound-assignment operators (+=, -=, /=, %=, &=, |=, and ^=) let you more efficiently perform mathematical, XOR, XAND, and string-concatenation operations. A new row-constructor feature lets you use a single INSERT statement to insert multiple rows. A new MERGE statement lets you perform INSERT, UPDATE, and DELETE functions in one statement.

Another new T-SQL feature, table-valued parameters, lets you pass a table as a parameter to a stored procedure or function (In sql server 2005 cursor could be used as only output parameter). Table-valued parameters are useful in situations where you need to pass large numbers of parameters to a stored procedure or function. In addition, a new GROUPING SETS feature gives you more control over the output and aggregations used in SQL Server queries.

BI enhancements. Although the business intelligence (BI) enhancements in SQL Server 2008 aren’t quite as notable as those in SQL Server 2005, the SQL Server 2008 BI subsystems—SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), and SQL Server Reporting Services (SSRS)—have experienced some important enhancements. SSIS features a new Data Profiling task that lets you analyze data passed through an SSIS package. The Data Profiling task can also output data for later viewing with the Data Profile Viewer. A new Cache Transform lets you cache lookup operations for improved package performance. In addition, the SSIS Script Task is now a Visual Studio Tools for Applications (VSTA) implementation with full support for Microsoft .NET Framework references.

SSAS has fixed Cube and Dimension Wizards that make the design process faster and more intuitive. Microsoft has enhanced the Dimension Designer by enabling it to define attributes’ relationships and adding a new visual designer. There are also improvements to the backup and restore process for SSAS databases.

One of the most important enhancements to SSRS is that it no longer requires you to install Microsoft IIS as a prerequisite. In addition, the Report Designer has had a major facelift. There’s also a new report format called Tablix that combines the attributes of table and matrix-style reports.

Another feature that can help end users access information in SQL Server 2008 is the ability to author reports in Microsoft Excel and Word. Microsoft has licensed SoftArtisans’ OfficeWriter, which integrates Excel and Word with SQL Server and SSAS databases.

Installing and Upgrading to SQL Server 2008
I installed SQL Server 2008 on a new Windows Server 2003 SP2 system and upgraded another of my existing SQL Server 2005 systems to SQL Server 2008. The setup program prompted me to upgrade my systems with .NET Framework 3.5 SP1, then with Windows Installer 4.5, after which I needed to reboot the system. After the reboot, I needed to manually restart the setup process, which first installed the setup prerequisites and checked the system setup requirements, then displayed the Installation Center.

The Installation Center provides a new setup experience. The Planning page provides links to the SQL Server 2008 documentation, and the Installation tab provides links to the new installation and upgrade programs. For a new installation, you launch the New SQL Server stand-alone installation or add features to an existing installation option. To upgrade a SQL Server 2000 or SQL Server 2005 instance to SQL Server 2008, you select the Upgrade from SQL Server 2000 or SQL Server 2005 option. The setup program is also cluster aware and has links that you use to either set up a new SQL Server cluster installation or add a node to an existing cluster. Both the installation and upgrade procedures went smoothly for me. The installation process took only a few minutes. The upgrade process was longer, taking about half an hour.

If you’re familiar with previous SQL Server versions, it should take you no time at all to get used to SQL Server 2008. The management and development experiences are essentially the same. Your primary management tool is SSMS, and you develop SSAS cubes, SSIS packages, and SSRS reports using Business Intelligence Development Studio (BIDS).

A few more changes to note: The old Surface Area Configuration tool is gone. Most of its functionality was duplicated in SQL Server Configuration Manager, which still exists in SQL Server 2008. In addition, the little-used Microsoft SQL Server Notification Services has been depreciated. The sample AdventureWorks database and AdventureWorksDW data warehouse are also no longer available through the installation process. If you want to have those databases, you’ll need to download them at www.codeplex.com.

SQL Server Books Online (BOL) remains the DBA’s best friend and is an excellent source for SQL Server 2008 technical information and tutorials. A useful new feature in BOL is its integration with the Microsoft online SQL Server forums. BOL links you to the Microsoft SQL Server forums when you ask a question and lets you search the Microsoft online forum from within BOL for an answer to your question.

Value for the Enterprise
Although it’s likely that many businesses aren’t ready for another major SQL Server release just yet, I strongly encourage you to take a look at SQL Server 2008 anyway. SQL Server 2008 delivers exceptional value with an array of new enterprise-oriented features. For organizations that are already SQL Server Enterprise Edition customers or are considering moving to Enterprise Edition, SQL Server 2008’s new enterprise-oriented features make it a must-have upgrade. The upgrade case is less compelling for SQL Server 2005 Standard Edition customers, although all organizations would benefit from the new data types, IntelliSense, and T-SQL debugging.

If you’re running SQL Server 2000, now is the time to make the jump to SQL Server 2008. SQL Server 2008 has all the SQL Server 2005 features plus the new features, all of which make the upgrade worthwhile. Furthermore, the integrated upgrade option makes it easy to move from SQL Server 2000 to SQL Server 2008, and the next SQL Server release might not have that capability.

Summary
Microsoft SQL Server 2008
PROS: Offers a bevy of new enterprise-oriented features, including the Resource Governor, data compression, transparent data encryption, Policy-Based Management, and change data capture; provides new data types, including DATE, TIME, spatial, and FILESTREAM; included new T-SQL IntelliSense and debugging capabilities in Query Editor.
CONS: Is a major release that many companies aren’t ready for, only three years after SQL Server.
RATING: 5 stars
PRICE: Enterprise Edition: $24,999 per processor or $13,969 per server plus 25 CALs; Standard Edition: $5,999 per processor or $1,849 per server plus five CALs. See www.microsoft.com/sqlserver/2008/en/us/pricing.aspx for additional pricing information.
RECOMMENDATION: If you’re using SQL Server 2000, you should upgrade now to take advantage of the combined features of the SQL Server 2008 and 2005 releases and the seamless upgrade path. If you’re an existing SQL Server 2005 Enterprise Edition customer, you should definitely make the leap to SQL Server 2008 to take advantage of the new enterprise-oriented features such as the Resource Governor, data compression, and encryption. SQL Server 2005 Standard Edition customers should upgrade only if they find a specific feature worthwhile.