Site owners

MSSQL 2008

1.1.1Key Areas

One of the first items noticed when reviewing Microsoft documentation on SQL
Server 2008 is the new lingo classifying features and benefits into groups, or
Key Areas. There are four Key Areas, Enterprise Data Platform, Dynamic
Development, Beyond Relational Database, and Pervasive Insight. This article
will follow these groupings in an effort to make cross referencing with
Microsoft articles easier.

1.1.3Encryption

1.1.4Related Articles

There are several encryption options; the first is called Transparent Data
Encryption. In SQL Server 2008, the entire database can be encrypted by the SQL
Engine. This method encrypts all database data and log files for a database. By
using this all-encompassing method, all Indexes and Tables are encrypted.
Changes to programming applications are not required.

The next encryption feature is Backup Encryption. SQL Server 2008 has a
method of encrypting backups to prevent data disclosure or tampering. In
addition, Restoring of backups can be limited to specific users.

Lastly, there are new options for External Key Management. If you are
involved with credit card processing or PCI (payment card industry) compliance,
SQL 2008 will support Hardware Security Modules (HSM). Hardware Security
Modules are third party hardware solutions used to store Keys in a location
separate from the data they protect.

1.1.5Auditing

In addition to the standard auditing of logon / logoffs and permission
changes, SQL Server 2008 allows for monitoring of data changes or access.
Auditing is configured by TSQL statements as in this example: AUDIT
UPDATE(Salary) ON Employee TO MyAuditFolder WHERE Salary>200000

1.1.6Data Compression

Usually, data compression is associated with general hard disk savings, and
with smaller physical files, backup times are reduced. While this holds true
for SQL Server Data Compression, the main goal is Fact Table size reduction. The
stated advantages for Data Compression include the following:

According to Microsoft, while using compression will slightly increase CPU
usage, overall system performance will be improved because of less IO.

1.1.7Resource Governor

New in SQL Server 2008 is the Resource Governor. The Governor is used to
restrict users or groups of users from consuming high levels of resources.
Items that can be monitored include CPU bandwidth, timeout waits, execution
times, blocking times, and idle times. If a Resource Governor threshold level
is reached, the system can trigger an event or stop the process. There are a
series of steps involved in utilizing the Resource Governor that will be
reviewed in an upcoming article.

1.1.8Hot Plug CPU

In SQL Server 2008, additional CPUs can be added on the fly if the
underlying hardware supports it.

1.1.9Performance Data

The amount of Performance Counters in SQL Server 2008 has been expanded
compared to earlier versions. IO and memory usage counters are just a couple of
the items that can be collected to monitor performance. The data collected by
the counters is stored in a centralized data warehouse. Microsoft states that
running the default set of performance related monitors will consume less than
5% of CPU and memory resources.

There is also now a Performance Dashboard tool that can read saved
performance data. In addition, historical and baseline comparisons can be made
and used to create action triggers. For example, if memory use exceeds a
threshold for more than five minutes, a more detailed data collection can be
automatically triggered.

New in SQL Server 2008 is the Performance Studio. The Studio is a collection
of performance tools. Together they can be used for monitoring,
troubleshooting, tuning and reporting. The Data Collector component of the
studio is configurable and low overhead. It supports several collecting methods
including TSQL queries, SQL Trace, and Perfmon Counters. Data can also be
collected programmatically. Once data is collected, there are drill-down and
aggregate reporting options. Microsoft lists these six client side features of
the Performance Studio:

SQL Server
dashboard

Performance
monitoring

Current
and historical data analysis

Suggestions
for potential performance tuning

Data
collection sets-based reports

MDW-based
reports

1.1.10Installation

Setup and Installation of SQL Server 2008 has also been enhanced.
Configuration data and engine bits have been separated so it will be possible
to create a disk image of a basic unconfigured system making distribution to
several servers easier. Also, the installation will be able to retrieve the
latest updates from the Microsoft web site. Another feature is the ability to
install SQL, service pack, and patches as a single step. On the other end of
this feature, is the ability to uninstall service packs.

1.1.11Conclusion

There are many significant features and benefits coming in SQL Server 2008.
In Part 2 of this series, we’ll examine Development, Integration, Business
Intelligence, and new Data Types.

1.1.12Introduction

This article will highlight some of the new features and benefits found in
SQL Server 2008. Some of the new features include Development changes, new
Business Intelligence features, Integration additions, and new Data Types.
Listed below are some of the items covered that were covered in Part
1 of this series.

1.1.13Dynamic Development

SQL 2008 leverages the new Dot Net Framework 3.0 with LINQ (Language
Integrated Query). In addition, there is more efficient support for Business
Data Entities along with data synchronization options. Also, there are new ADO
and Visual Studio development options. Collectively, these are labeled Dynamic
Development and are reviewed below.

1.1.14Entity Data Services

SQL Server 2008 and ADO.NET now allow for high level business objects to be
created, such as Customers or Parts. These entities can be used rather than the
standard method of returning individual rows and tables. If you’re using E-R
(entity relationship) modeling, your objects in SQL will now match your
modeling. There are several new ADO.NET frameworks that can access these
entities such as the Line-of-Business (LOB) framework and the Entity Query
Language (eSQL).

1.1.15LINQ

1.1.16Related Articles

LINQ provides a standard development syntax for accessing data, regardless
of where the data resides. For example, the same syntax can access either SQL
Server or XML data. LINQ is used rather than TSQL inside the application
language, such as C# or VB.

1.1.17Data Synchronizing Features

The combination of SQL 2008, Visual Studio, and ADO.NET bring together new
methods of creating synchronizing or frequently disconnected applications,
making it easier to create client applications that synchronize with a central
database. SQL 2005 started by providing support for change tracking by using
triggers. SQL 2008 synchronizing is better integrated and optimized.

1.1.18Beyond Relational Databases

These next groups of features are collectively grouped as “Beyond
Relational”. They include new location, geometry, data and time data types. In
addition, there are new Full Text and File Stream options built into SQL Server
2008.

1.1.19Large UDT

Previously, in SQL 2005, User Defined Types (UDT) could not be larger than
8,000 bytes. In SQL 2008 there is no longer any size restriction, allowing
storage of very large UDTs.

1.1.20Dates and Times

There are new Date and Time data types in SQL 2008.

Date. This
is a data type with a date only, no time.

Time. A
Time data type without a date component. Precision can be up to 100 nanoseconds.

Date Time
Offset. This data type will store a Universal Coordinated Time (UTC)
time-zone aware value.

1.1.21File Stream

The new data type VarBinary(Max) FileStream allows for a way to manipulate
binary data using TSQL Select, Insert, Update, and Delete statements. In the
past, to store binary data a BLOB, accessed by a Dot.Net application was
typically used. Now, SQL functions such as triggers, Full Text Search, and
backup restore can be applied to binary data.

1.1.22Spatial Data

The new Spatial Data type allows Latitude, Longitude, and GPS-based data
entries to be natively stored inside SQL Server. The data type conforms to
several industry standards such as Open Geospatial Consortium (OGC) Simple
Features for SQL and ISO 19125 Simple Feature Access.

1.1.23Table Value Parameters

In previous versions of SQL Server, there wasn’t a native way to pass a
table to a stored procedure. The usual workaround was to pass a large varchar
or XML type and parse through it. Now, in SQL Server 2008, Table Parameters are
available. The following provides a simple example of passing a table into a
Stored Procedure.

1.1.24Full Text Search

There are Full Text Search changes in SQL Server 2008 including native
indexes, thesaurus files stored as metadata, and the ability to perform a
Backup.

1.1.25Reporting Server

Memory management in SQL Server 2008 Reporting Service is improved. So
running large reports will not consume all available memory. In addition,
report rendering has more consistency than before.

1.1.26SQL 2000 Support Ends

As explained in Part
1 of this series, Mainstream Support for SQL 2000 is coming to an
end in April 2008. This includes the CE version.

1.1.27Conclusion

SQL Server 2008 has many practical and useful improvements. The new Date and
Time data types will help simplify some applications. Listed below is a summary
of the features and improvements reviewed so far:

Transparent
Data Encryption allows for an entire database, all tables and data, to be
encrypted on the fly without application programming.

Backups
can be encrypted to prevent data disclosure or tampering.

Data
changes and access can now be audited.

Fact
Tables can be compressed for performance benefits.

The
Resource Governor can prevent runaway resource usage.

SQL 2008
supports Hot Plug CPU.

Performance
Counters have been greatly expanded.

Installation
has been simplified.

In Part 3 of this series, we’ll cover the following SQL Server 2008 topics:

Data
Integration Features such as the MERGE statement, Parallelism, SSIS
multiple processor improvements, and look up performance improvements.

Microsoft
Office 2007 Integration such as Exporting Reporting Service reports as
Word docs, SSRS format and font improvements, and the Office Tool Bar.

1.1.28SQL Server Integration Services

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.

The Lookup component has been improved. Lookups are a very common SSIS
operation that fetches a related piece of information. Such as a lookup
obtaining the Customer Name from the CustomerID and brining that value into the
dataset being worked on. Because Lookups are very common in SSIS and can be
performed on large million row datasets, performance could be poor.
Improvements have been made in SQL 2008 to increase performance. In addition,
Lookups can be done on a variety of data sources including ADO.NET, XML, OLEDB,
and other SSIS packages.

1.1.29Merge

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);

1.1.30Analysis Service

1.1.31Related Articles

A number of improvements and enhancements have been made to SSAS (SQL Server
Analysis Server). The BI Stack has been improved for increased performance.
Commodity hardware can be by utilized by scale out management tools. Also,
Block Computation can provide significant performance improvements in cube
analysis.

1.1.32Reporting 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.

1.1.33Microsoft 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.

1.1.34Conclusion

SQL Server 2008 contains many new features and enhancements, a large numbers
of which were not covered in this series. Additional information can be found
at the main SQL 2008 Microsoft page: http://www.microsoft.com/sql/2008/default.mspx.
Listed below is a concise bulleted list of the SQL Server 2008 features
reviewed in this series.

·Transparent Data Encryption. The ability to
encrypt an entire database.

·SQL 200 Support Ends. Mainstream Support for SQL
2000 is coming to an end. Feature and benefits included in Mainstream Support
include the ability to submit requests for product feature changes, Security
Updates, Non Security Hotfixes, Complimentary support, and Paid Support. This
Mainstream Support will expire on 4/8/2008 for SQL Server 2000 64-bit Edition,
SQL Server 2000 Developer, SQL Server 2000 Enterprise, SQL Server 2000
Standard, SQL Server 2000 CE, and SQL Server 2000 Workgroup Edition. Extended
Support, consisting of Security Updates and Paid Support will continue until
2013. A full description of support phases can be found at these Microsoft
URLs: “Microsoft
Support Lifecycle” and “Microsoft Support
Lifecycle Policy FAQ”. Many resellers will discontinue selling SQL
2000 in December of 2007. Also, no version of SQL 2000 will be supported on
Vista, including SQL 2000 Express.