1-New Features in SQL Server 2008

Exam objectives in this chapter:

■ New Feature Overview

■ Reporting Services

Introduction

>A Word About the Test

New Feature Overview

>Installation

The SQL Server installation tool is used to create a new installation, or to make changes to the existing installation.

>Compressed Backups

When using T-SQL to create the backup, you’d use:

BACKUP DATABASE [robby] TO DISK = N'C:\Backup\robby.bak' WITH

NOFORMAT, NOINIT, NAME = N'robby-Full Database Backup', SKIP,

NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10

The Copy Only Backup is especially useful for taking “one off ” backups for development or testing – the advantage is it doesn’t affect transaction log backups or differential backups. Keep in mind it also cannot serve as a base for differential or transaction log backups when restoring either.

The T-SQL procedure to do a Copy Only Backup would look like:

BACKUP DATABASE [robby] TO DISK = N'C:\Backup\robby.bak' WITH COPY_ONLY,

NOFORMAT, NOINIT, NAME = N'robby-Full Database Backup', SKIP, NOREWIND,

NOUNLOAD, STATS = 10

GO

>Enhanced Configuration and Management of Audits

Auditing is available using the new Change Data Capture (CDC) feature. CDC can be used to capture insertions, updates, and deletes in an SQL table in a database and place the changes in another table.

The following SQL code demonstrates how to configure CDC for auditing of a table in a database:

--Activate CDC

EXEC sys.sp_cdc_enable_db_change_data_capture

--Enable CDC on table

EXEC sys.sp_cdc_enable_table_change_data_capture @source_schema = 'dbo',

@source_name = 'myTable', @role_name = 'cdc'

>New Table Value Parameter

Passing tables as parameters has been a long time coming. The new table type can be passed to a stored procedure. This will solve quite a few problems!

Here’s an example:

To declare a Table User Defined Type in the database:

create type MyTableType as table

(

Name varchar(150),

City varchar(20),

AddressID int

)

And here's the stored procedure that consumes it:

create procedure InsertFriends

(

@MyTable MyTableType readonly

)

as

insert

into Friends (Name, city, AddressID)

select Name, city, AddressID

from @MyTable;

--To fill create and fill the temp table:

declare @MyBestFriends_temp MyTableType

insert into @MyBestFriends_temp values ('Debbie', 'Havertown', 2)

insert into @MyBestFriends_temp values ('Chris', 'Philadelphia', 1)

insert into @MyBestFriends_temp values ('Tom', 'Garden City', 11)

insert into @MyBestFriends_temp values ('Greg', 'Lansdowne', 6)

insert into @MyBestFriends_temp values ('Steve', 'Wilmington', 6)

--And finally, to execute:

execute InsertFriends @MyBestFriends_temp

>FileStream Data Types

the database engine will store all of the data associated with the column in a disk file as opposed to the actual database. You might have used a similar home-grown scheme in earlier versions of SQL, but this integrates everything nicely into SQL Server.

must first enable it. This is accomplished via the sp_FILESTREAM_configure system stored procedure, or via the GUI in Management Studio under advanced settings.

Once FileStream is enabled, a file group must be added to the database in order for it to be able to use FileStream data types.

FileStream has the following limitations:

■ Database mirroring cannot be configured in databases with FileStream data.

■ Database snapshots are not supported for FileStream data.

■ Native encryption is not possible by SQL Server for FileStream data.

>Sparse Column Support

Sparse columns allow for the optimized storage of null columns. Sparse columns can be a good thing, but be sure to enable them only on columns that contain sparse data, or your storage requirements may go up instead of down.

To enable a column as a sparse column, use the create statement in SQL or change the properties in the column to Sparse.

The SQL to accomplish this is as follows:

CREATE TABLE dbo.Table_1

(

OID int NULL,

MyValue1 varchar(50) SPARSE NULL

) ON [PRIMARY]

GO

>Encryption Enhancements

Transparent data encryption (TDE) is available in SQL Server 2008.TDE allows you to easily encrypt the contents of your database and is designed to provide protection to the entire database. With TDE, you can encrypt the contents of your database with no changes to your application.

To enable TDE, you need to first create a master key and a certificate. Once the master key and certificate are set up, use the following to enable TDE:

ALTER DATABASE myDatabase SET ENCRYPTION ON

Once TDE is enabled, it’s designed to be transparent to the application.

>>Key Management and Encryption

Encryption requires keys to secure the database. These keys must be protected and backed up 

>High Availability 

Mirroring has been improved, Hot Add CPU has been added, and Hot Add Memory is available. 

SQL Server 2008 can also take advantage of the new failover clustering enhancements available in Windows 2008.

failover clustering is not available in the Web edition or workgroup edition.

>Performance

>>Performance Data Management

Performance data management is a new tool available in SQL Server 2008.

Performance data management allows you to collect performance-related data from your SQL Servers over time. Performance data management consists of a warehouse database (for storing the results) and the data collector, and collection is usually scheduled to run at specific times.

>>Resource Governor (similar to Query Governor)

Resource Governor is a nice new feature to help manage workload, designed to limit the resources available to a process. The way the Resource Governor works is the DBA creates a workload group and a resource pool. Workload groups are containers to hold user sessions. Workload groups are mapped to resource pools.

User sessions are mapped to workload groups based on classifier functions. The classifier functions can be by IP address, username, application name, and so on.

>>Freeze Plan

Plan freezing is meant to offer greater predictability when it comes to executing a particular query in SQL Server 2008.

basically a cached plan that is kept around and used for a particular query.

First, you need to create the data warehouse

The actual how to is found later in this book; it’s worthwhile knowing how to use this new feature.

Figure A Data Collection Report is a sample of one of the reports available in the data collection set.

the data collection can be used only on SQL Server 2008 servers. It will not work if you connect to a SQL 2005 or SQL 2000 database server.

>SQL Server 2008 Declarative Management Framework

Declarative Management Framework (DMF). This is basically a new policy-based management system for SQL Server 2008.

The DMF is very similar to Windows policy; 

you can enforce a particular naming convention for stored procedures and a different naming convention for tables.

There are three main components to the DMF: policies, conditions, and facets.

To create or apply a policy, you would right click on the policy node and proceed from there. 

>Development Improvements

>>LINQ Support

basically a mapping of the database object to programming objects. This allows for a more object-oriented approach to dealing with database objects 

>>MERGE Statement

The MERGE statement performs insert, update, or delete operations on a target table based on the results of a join to a source table.

For example, you can synchronize two tables by inserting, updating, or deleting all of the rows in one table based on differences in the other table.

Here is an example of MERGE code:

MERGE MyDatabase.Inventory AS target

USING (SELECT ProductID, SUM(OrderQty) FROM mySales.Orders AS o

JOIN mySales.mySalesOrderHeader AS soh

ON o.mySalesOrderID = soh.mySalesOrderID

AND soh.OrderDate = @OrderDate

GROUP BY ProductID) AS source (ProductID, OrderQty)

ON (target.ProductID = source.ProductID)

WHEN MATCHED AND target.Quantity - source.OrderQty <= 0

THEN DELETE

WHEN MATCHED

THEN UPDATE SET target.Quantity = target.Quantity - source.OrderQty,

target.ModifiedDate = GETDATE()

OUTPUT $action, Inserted.ProductID, Inserted.Quantity, Inserted.ModifiedDate,

Deleted.ProductID,

Deleted.Quantity, Deleted.ModifiedDate;

>Spatial Data Type

The spatial data type is a new data type

spatial data type is used to store location-based data

do geocoding.

>Analysis Services Improvements

In Analysis Services, a better cube designer, dimension and attribute designer, and enhanced data mining structures.

>ETL/SSIS Enhancements

Many of the new features carry over to SSIS, like Change Data Capture and the MERGE statement. Another good new feature is being able to script in C#

Reporting Services

SQL Server Reporting Services. it now supports rich text format.

>No Longer Requires IIS

>Better Graphing

Reporting Services supports graphing, 

>Export to Word Support

Reporting Services now has the ability to export to Word. 

>Deprecated Features

■ BACKUP {DATABASE | LOG} WITH PASSWORD

■ BACKUP {DATABASE | LOG} WITH MEDIAPASSWORD

■ RESTORE {DATABASE | LOG} … WITH DBO_ONLY

■ RESTORE {DATABASE | LOG} WITH PASSWORD

■ RESTORE {DATABASE | LOG} WITH MEDIAPASSWORD

■ 80 compatibility level and upgrade from version 80.

■ DATABASEPROPERTY

■ WITH APPEND clause on triggers

■ Default setting of disallow results from triggers option = 0

■ sp_dboption

■ FASTFIRSTROW hint

■ sp_addremotelogin

■ sp_addserver

■ sp_dropremotelogin

■ sp_helpremotelogin

■ sp_remoteoption

■ @@remserver

■ SET REMOTE_PROC_TRANSACTIONS

■ sp_dropalias

■ SET DISABLE_DEF_CNST_CHK

■ SET ROWCOUNT for INSERT, UPDATE, and DELETE statements

■ Use of *= and =*

■ COMPUTE / COMPUTE BY

■ sys.database_principal_aliases

■ sqlmaint Utility

■ The RAISERROR (Format: RAISERROR integer string) syntax is deprecated.

these will still work in SQL Server 2008, they are not a recommended best practice.

>Discontinued Features

■ sp_addalias

■ Registered Servers API

■ DUMP statement

■ LOAD statement

■ BACKUP LOG WITH NO_LOG

■ BACKUP LOG WITH TRUNCATE_ONLY

■ BACKUP TRANSACTION

■ 60, 65, and 70 compatibility levels

■ DBCC CONCURRENCYVIOLATION

■ sp_addgroup

■ sp_changegroup

■ sp_dropgroup

■ sp_helpgroup

■ Northwind and pubs

■ Surface Area Configuration Tool

■ sp_makewebtask

■ sp_dropwebtask

■ sp_runwebtask

■ sp_enumcodepages

Ref>
ISBN 13: 978-1-59749-420-5