Microsoft SQL Server 2008 Learning Guide

In August 2008, Microsoft officially released SQL Server 2008. The latest database management system features a plethora of improvements and new features from full-text search integration and new data types to enhancements to business intelligence (BI) tools.

But is SQL Server 2008 right for you? We've done all the legwork for you, compiling a wealth of information on SQL Server 2008 since before it was released. This Microsoft SQL Server 2008 guide features tips, book excerpts and more covering the top features, improvements and changes included with the 2008 edition, as well as information to help plan your upgrade.

Getting started with SQL Server 2008

SQL Server 2008 includes a lot of new features but not all of them may be right for your business. In this section, discover the pros and cons of Microsoft's latest data platform and find tips for making a successful upgrade.

Should you upgrade to SQL Server 2008?
SQL Server 2008 offers several benefits for businesses – but is it right for your organization? This article helps you figure out if the performance and availability advantages are worth the move.

SQL Server 2008 will include several new data types and SQL Server Integration Services features which will come in handy when creating your data warehouse schema and loading data.

Old vs. new: A SQL Server comparison
Compare SQL Server 2005 to SQL Server 2008 and learn the advantages and disadvantages of upgrading to either.

Migrating to SQL Server 2008
You've decided to upgrade your SQL Server – but what now? Check out some basics steps to ensure a successful migration, as well as the features you'll want to take advantage of right away.

Scaling up vs. scaling out
With SQL Server 2008, there are two distinct paths you can take to scaling: up and out. While both are designed to provide increased overall performance for a SQL Server-based application, each provides unique advantages and utilizes very different techniques.

Key features and enhancements

This section reviews some of SQL Server 2008's most important improvements regarding database administration, development, and management issues, with details on how to get the most out of them.

Podcast: Major SQL Server 2008 features outlined by Microsoft exec
Before the release of SQL Server 2008, we spoke with product manager Francois Ajenstat about some of the key enhancements designed to appeal to DBAs and IT administrators.

 Database Administration
Replication features
Discover replication improvements in snapshot delivery, peer-to-peer topologies, merge replication and more. 

New GROUP BY option
GROUPING SETS in the GROUP BY clause trumps the ROLLUP and CUBE options by providing a more efficient and specific data aggregator in SQL Server 2008 – and greater data control.

Hierarchyid data type
With step-by-step examples, learn how the new hierarchyid data type simplifies queries when you want to store parent-child relationships in a table and represent a hierarchial SQL tree.

Resource Governor
Learn how Resource Governor can improve SQL Service performance by establishing resource limits, prioritizing workloads and throttling processes in this chapter excerpt from Microsoft SQL Server 2008 Management and Administration.

Backup compression
Backup compression in SQL Server 2008 speeds up backups and reduces the size of data files when compared to previous backup methods. But increased CPU could be the tradeoff.

 Database Development
Table-valued parameters for SPs
With table-valued parameters in SQL Server 2008, ordinary stored procedures have extraordinary abilities. Pass variables as parameters in your stored procedure and save time by making significantly fewer calls to SQL Server and fewer writes against the database.

Datetime data types
See how to use DATETIME2, DATETIMEOFFSET and DATE and TIME data types — flexible features that separate date and time.

Physical data storage
Learn the fundamentals of data storage in SQL Server 2005 and 2008, including tables, views and data types, in this excerpt from A Developer's Guide to Data Modeling for SQL Server, Covering SQL Server 2005 and 2008.

Different data types
Here you'll about data types available in SQL Server 2008 and how to use them, including datetime, string and user-defined data types.

A guide to basic new features in SQL Server Management Studio 2008
SQL Server Management Studio 2008 new features include IntelliSense support, region outlining and multi-server query. Learn how SSMS 2008 makes database administration easier.

BI in SQL Server 2008

Integration Services
A short history of SSIS
Discover how SQL Server Integration Services evolved from Data Transformation Services into a fully functional data warehousing tool in this chapter excerpt from SQL Server 2008 Integration Services Unleashed.

SSIS primer
Many features and technologies are built-in to SQL Server Integration Services. This excerpt breaks down what is included with SSIS and how they can be used to improve performance and accessibility.

New features in SSIS
Integration Services in SQL Server 2008 provides three new features for better performance and ease of use for administrators and developers. See how data integration has been kicked up a notch with improved scripting capabilities, data profiling and data flow.

Data profiling tools
New data-profiling tools such as the Data Profiling task and the Data Profiler Viewer help manage a business intelligence strategy up front.

 Reporting Services
What's new in SSRS?
New features in SQL Server 2008 Reporting Services mean improved charting and increased memory. See how Microsoft has improved SSRS 2008's performance and made it easier to use.

Change data capture improves BI reporting accuracy
The change data capture feature allows you to update your data warehouse or data mart in near real time based on changes in the source transactional system. Learn how to capture these changes for the most accurate data collection and reporting.

 Analysis Services
SSAS overview
The latest version of Analysis Services improves on the fundamentals of the 2005 version, including faster processing, improved backups and new monitoring functionality. Download this Microsoft White Paper to learn more.

Webcast: Advanced manageability
See how to effectively use new SSAS features, like Dynamic Management Views, in this Microsoft TechNet webcast.

Security improvements

New security and compliance features
Learn about new security and compliance features in SQL Server 2008 including schema permissions, data encryption at file level and policy-based management.

Features leave some work for you
Although the security features are improved, you still must take security measures to protect your SQL Server databases. Here's how Microsoft can't help.

Implementing security audit
If you're a DBA tasked with producing detailed audits tracking database use, take a look at these capabilities where the process is much easier than in earlier versions.

Viewing audited data in SQL Server 2008's Log File Viewer (Click to enlarge)

Understanding transparent data encryption
Transparent data encryption can go a long way toward meeting compliance and security standards. Learn what TDE can do for you, with details on the considerations that should be made before it's implemented.

T-SQL in SQL Server 2008

• Part 1: Basic objects of T-SQL in SQL Server 2008
  Discover the basic objects that drive T-SQL in this excerpt from Microsoft SQL Server 2008: A Beginner's Guide. See how literal values, delimiters, comments, identifiers and reserved keywords are utilized in the SQL language in author Dusan Petkovic's book.
• Part 2: Using T-SQL data types
  Learn the numerous data types used in SQL Server 2008's Transact-SQL language in this excerpt. Find out which data types hold numeric data and which hold character data.
• Part 3: Function types in T-SQL
  Functions in Transact-SQL can be either aggregate functions or scalar functions. This excerpt describes both and details the many numeric functions that can be accessed.
• Part 4: Additional T-SQL operations
  Explore how scalar operators, global variables and NULL values can be used in Transact-SQL, with examples to gauge your understanding of SQL Server 2008 objects and operators.

DBA tips and how-tos

Windows PowerShell
Creating Windows PowerShell scripts helps manage SQL Server 2008 objects, databases and other instances. This tip covers two PowerShell snap-ins that aid the navigation of the SQL Server component hierarchy.

Working with sparse columns
Sparse columns can provide significant storage benefits to those working with SQL Server 2008. Learn the proper way to implement sparse columns, as well as column sets and filtered indexes.

SQL Server 2008 R2

The next major release of SQL Server is on its way. See what to expect in the near future and how the latest version differs from its predecessor.

Figuring out SQL Server 2008 R2
Not sure what to make of R2? Here you’ll find answers to some of the most common questions involving the latest database server release from Microsoft.

Key changes and new features
A lot's different in SQL Server 2008 R2. Check out our list of the 10 most significant changes.

Licensing differences from SQL Server 2008 to R2
Microsoft has put the kibosh on unlimited virtualization rights with SQL Server 2008 R2 Enterprise Edition. Learn what else has changed in terms of SQL Server licensing.

For more information on new R2 features and functionality visit the SQL Server 2008 R2 topic page.