SQL Server 2008 New Features

Views:
 
Category: Entertainment
     
 

Presentation Description

No description available.

Comments

Presentation Transcript

SQL Server 2008 New Features :

SQL Server 2008 New Features Jeff R. Johnson| Lead SQL Architect– [email protected]| 973-401-0660

AGENDA:

AGENDA Security and Data Auditing 4 Availability and Reliability 8 Performance 10 Management 13 New Data Types 14 Development Enhancements 15 Service broker 20 Data Storage 21

AGENDA:

AGENDA Data Warehousing/ETL 27 Reporting 31 Deprecation 32 Q & A 33

Security and Data Auditing:

Security and Data Auditing Transparent Data Encryption Encryption of data on the disk while it remains transparent to the application requesting the data. Steps Create a Master Key Create or obtain a certificate protected by the master key Create a database encryption key and protect it by the certificate Set the database to use encryption

Security and Data Auditing:

Security and Data Auditing Transparent Data Encryption Use master go Create Master Key Encryption by Password = ‘mssqlR0cks’ Go Create Certificate TDECert With Subject = ‘TDE Certificate’ Go Create Database TDESample Go Use TDESample Go Create Database Encryption Key With Algorithm = AES_256 Encryption by Server Certificate TDECert Go Alter database TDESample Set Encryption On Go

Security and Data Auditing:

Security and Data Auditing Extensible Key Management Features Support for Hardware Security Modules (HSM) HSM devices store encryption keys in hardware or software modules More secure because encryption keys do not reside with the data Higher performance for hardware-based encryption/decryption Additional authorization check Secure encryption key disposal Easier retention and retrieval Turn on EKM Sp_configure ‘show advanced’,1 Go Reconfigure Go Sp_configure ‘EKM provider enabled’,1 Go Reconfigure go

Security and Data Auditing:

Security and Data Auditing Auditing Server Audits Audit events that happen at the server level (Login, Logout, Groups, etc….) CREATE SERVER AUDIT [LoginAudit] TO APPLICATION_LOG WITH ( QUEUE_DELAY = 1000 ,ON_FAILURE = CONTINUE ) GO CREATE SERVER AUDIT SPECIFICATION [TestServerAudit] FOR SERVER AUDIT [LoginAudit] ADD (SUCCESSFUL_LOGIN_GROUP) WITH (STATE = ON) GO Database Audits Audit events that happen at the database level (Schema, Ownership, Data, etc.) CREATE DATABASE AUDIT SPECIFICATION [SchemaAudit] FOR SERVER AUDIT [SchemaAudit] ADD (SCHEMA_OBJECT_CHANGE_GROUP) WITH (STATE = On) GO

Availability and reliability:

Availability and reliability HOT SWAP CPUs Adds the ability to add CPUs on the fly while the machine is up and running Automatically recognizes additional CPU and starts using it

Availability and reliability:

Availability and reliability Database Mirroring Compressed log steam to minimize network bandwidth Automatic page repair for both principal and mirror New DMVs (Dynamic Management Views) Additional mirroring performance counters

Performance:

Performance Backup Compression Setup Server Wide Exec sp_configure ‘backup compression default’,’1’ Reconfigure with override Database Backup Syntax BACKUP DATABASE AdventureWorks To Disk = ‘c:\SQLData\AdventureWorks_Compressed.bak With COMPRESSION , INIT

Performance:

Performance Performance Data Collection New tool in Management Studio Collects data about SQL Server 2008 in collection sets Stores data collected in a database Allows you to define what are in those sets Counters can be from DMV’s, SQL Traces, Application Data Extensible Comes with 3 collection sets Disk Usage Query Statistics Server Activity

Performance:

Performance Resource Governor Limit the amount of CPU by percent Limit the amount of Memory by percent Applied by a workload group Workload group is defined by a custom classifier function CREATE RESOURCE POOL [TestResourcePool] WITH(min_cpu_percent=0, max_cpu_percent=5, min_memory_percent=0, max_memory_percent=5) GO CREATE WORKLOAD GROUP [TestWorkloadGroup] WITH (importance=Medium) USING [TestResourcePool] GO Create Function TestResourcePoolMember() Returns Sysname with schemabinding as Begin Declare @name as SysName = 'default' if (suser_name() <> 'sa') Set @name = 'TestWorkloadGroup' Return @name End Go Alter Resource Governor with (Classifier_Function = dbo.TestResourcePoolMember)

Management:

Management Policy-Based Management Policy management is done inside SQL Server Manager Studio Create policies Export Policies to XML files Evaluate group of Servers for an exported policy Install policies on each server or group of servers Easier to administer and support multiple servers Policy consists of 4 items Target – database, table, index, etc Facet – predefined set of properties that can be managed Condition – expression on a facet that evaluates to true or false Policy – a condition to be checked and/or enforced

New Data Types:

New Data Types Data Types Date Time2(7) - Millisecond up to 7 digits DateTimeOffset(7) – DateTime2 with Offset for TimeZone Geometry Geography HierarchyID Time(7) – Time only up to 7 digit milliseconds Date – Date Only

Development Enhancements:

Development Enhancements Queries LINQ was introduced with Visual Studio 2008 SQL 2008 ships with a LINQ to SQL Provider which allows LINQ commands directly against SQL Server ADO.NET Entity framework allows developers to create database queries using entities. Development Enhancements

Development Enhancements:

Development Enhancements SQL Server Change Tracking Change Data Capture Provides historical information for a user table by capturing both the fact that DML changes were made and the actual data what was changed.. Changes are captured by using an asynchronous process that reads the transaction log and has a low impact on the system. Enabling Exec sys.sp_cdc_enable_db_change_data_capture Go Exec sys.sp_cdc_enable_table_change_data_capture @ source_schema = ‘ dbo ’, @ source_name = ‘ TestTable ’, @ Role_Name = null Results – New tables cdc.captured_columns – Information about the captured columns being tracked. cdc.changed_tables – Tables being tracked. cdc.ddl_history – Changes to the tracked table. cdc.index_columns – Tracked index columns. cdc.lsn_time_mapping – Tracks when a transaction starts and end cdc.dbo _[ TableName ]_ct. – The actual changed data Change Tracking Captures the fact that rows where changed but doesn’t capture what was changed.

Development Enhancements:

Development Enhancements Table Value Parameters Ability to declare table types and pass as variables Sample Create type KeyList as TABLE (RowKey int) GO Declare spGetRows (@KeyList KeyList) Begin Select * From Employees = K.RowKey End Go Declare @Rows KeyList Insert into @Rows Values(1) Insert into @Rows Values(2) Exec spGetRows (@Rows) GO

Development Enhancements:

Development Enhancements Large UDTs UDT – User Defined Datatype Introduced in SQL 2005 had 8k limit SQL 2008 has varbinary(max) limit ~ 2GB

Development Enhancements:

Development Enhancements XML Enhancements Datetime, date, and time validation including timezone information in schema collections Better support for union and list types Support for LAX validation (anytype and anyattribute validation) Modify method now allows DML Adds let clause in XQuery

Service Broker:

Service Broker Conversation Priority Now assign a priority to Service Broker conversations CREATE BROKER PRIORITY ConversationPriorityName FOR CONVERSATION [ SET ( [ CONTRACT_NAME = {ContractName | ANY } ] [ [ , ] LOCAL_SERVICE_NAME = {LocalServiceName | ANY } ] [ [ , ] REMOTE_SERVICE_NAME = {'RemoteServiceName' | ANY } ] [ [ , ] PRIORITY_LEVEL = {PriorityValue | DEFAULT } ] ) ]

Data Storage:

Data Storage Data Compression Row Compression Minimizes the metadata (Column Info, Length, Offsets, etc) associated with each record Numeric data types and fixed length strings are stored in variable-length storage format like varchar. Sample Create Table MyTable ( Id int identity Primary Key, Name char(100), Email char(100)) With (Data_Compression = Row) Alter table MyTable Rebuilt With (Data_Compression = Row, MaxDop = 2)

Data Storage:

Data Storage Data Compression Page Compression Uses Row Compression Prefix Compression – For every column in a page prefixes are identified. Prefixes are saved in compression information headers which reside after page header. Reference number refers to prefix. Dictionary Compression – Searches for duplicate values in a page and stores them in the compression information header. Prefix is only one column and dictionary is complete page. Sample Create Table MyTable ( Id int identity Primary Key, Name char(100), Email char(100)) With ( Data_Compression = Page) Alter table MyTable Rebuilt With ( Data_Compression = Page, MaxDop = 2)

Data Storage:

Data Storage File Stream Ability to store objects in the file system but still be managed by SQL Server Store and retrieve BLOBs together with relational data in a single data store. Included in backup and restore operations Inserting, updating, deleting BLOBs and relational data occur in same database transaction 2GB limit of varchar (max) doesn’t apply SQL Server buffer pool (memory) is not used BLOB access can be performed from .Net code NTFS file system can save and retrieve data faster than SQL Server Steps Configure SQL Server to use Filestream and appropriate level (T-SQL, local system, remote) Create filegroup to database that contains Filestream Add file to database filegroup that contains the Filesteam Add column with Filestream specified in definition Sample Exec sp_filestream_configure @ Enable_level =3, @ share_name = ‘ FilesteamShare ’ Go Alter database MyDB Add Filegroup MyFileStreamFG Contains Filestream Go Alter database MyDB add File (name = ‘ MyDB_filestream ’, Filename=‘c:\db\fs’) to filegroup MyFileStreamFG GO Create table TestTable (ID int identity not null, LargeObject VARBINARY(MAX) FILESTEAM NULL) GO

Data Storage:

Data Storage Integrated Full Text Search Full Text Search is now integrated into SQL Server 2008 Improvement include: Performance – Indexing and Query Stop Lists stored in the database Thesaurus Improvements DMVs Dm_fts_index_keywords Dm_fts_index_keywords_by_document Dm_fts_parser

Data Storage:

Data Storage Sparse Columns Sparse columns reduce the amount of storage for null values at the sacrifice of more overhead to retrieve non-null values. Best practice is to use when column contains 20 – 40 percent null values. Sample Create Table SparseSample ( KeyValue int idenity not null, Name varchar(100) not null, Address1 varchar(100) not null, Address2 varchar(100) sparse not null, City varchar(50) not null, State char(2) not null, PostalCode varchar(10) not null)

Data Storage:

Data Storage New Index Types Filtered Index Optimized Non-clustered index Uses filter to index a portion of a table Create NonClustered Index “MyIndex” On TableName.ColumnName(Col1, Col2) Where Col2 is not null Spatial Index Used to index geography and geometry value types Index is created on a column XML Index Primary XML Index – Creates clustered index on XML Column Secondary XML Index – Creates index on Path, Value or Property

Data Warehousing/ETL:

Data Warehousing/ETL Partition Table Parallelism Removes the 1 CPU limit on queries against partitioned tables New syntax to switch partitioned tables Sample Alter table PartitionTable Switch Partition 2 to NonPartitionTable

Data Warehousing/ETL:

Data Warehousing/ETL Analysis Services Star Join Support –optimized for better star join query response Better scalability- Can share read-only DBs between several servers New tools for cube design Improved cube backup – Can support large databases Excel data-mining add-ins Grouping by sets

Data Warehousing/ETL:

Data Warehousing/ETL Integration Services ( ssis ) Persistent Lookups Improved Thread Scheduling

Data Warehousing/ETL:

Data Warehousing/ETL Merge Statement Merge statement works as an insert, update, delete statement all at once Works on two sources of data, the source and the target Merge Main.dbo.TestTable as Target Using (Select Col1, col2 from stage.dbo.TestTable) as Source On Target.TableKey = Source.TableKey When Matched [and condition] Then Update Set Col1 = Source.Col1, Col2 = Source.Col2 When Matched [and condition] Then Delete When Not Matched Then Insert (Col1, Col2) values (Col1, Col2) Output $action, Inserted.TableKey, Deleted.TableKey

Reporting :

Reporting Reporting Services Enhancements IIS no longer required More charting choices Export report to rich text format Export report to word format Built in forms authentication

Deprication:

Deprication Deprication SQL Server 6.0, 6.5 and 7.0 compatibilty modes removed NOLOG and TRUNCATEONLY on backup command Remote Server support Security Sp_addalias sp_dropalias Sp_addgroup Sp_changegroup Sp_dropgroup Sp_helpgroup SetUser

Q & A:

Q & A Question and Answers Question and Answers

authorStream Live Help