sql server 2005 whats new for the dba

Technical Community Resources TechNet Belgium & Luxembourg

www.microsoft.com/belux/technet/

Resources For IT Professionalswww.microsoft.com/belux/technet/community Webcasts, Bloggers Newsgroups, Most Valuable Professionals, Interesting Websites...

http://www.microsoft.com/belux/technet/






http://www.microsoft.com/belux/technet/community

http://www.microsoft.com/belux/technet/community

I.P. Marc Mertens

SQL Server 2005 What’s new for the

database administrator

SQL Server 2005 What’s new for the

database administrator

Marc MertensInstructor/Developer [email protected]://www.azlan.be

Marc MertensInstructor/Developer [email protected]://www.azlan.be

mailto:[email protected]

mailto:[email protected]

Agenda

Sql2005 Architecture Administrative Tools Securing SQL2005 Monitoring Sql2005 Maintaining Indexes Disaster Recovery Data availability

Sql2005 Architecture Relational Database Engine CLR Runtime support Analysis Services Notification Services (Available now) System Integration Services Reporting Services (Available now) Replication Service Broker Native HTTP Support Full-Text search

Agenda


Administrative Tools

Sql Server Management Studio Replacement of Enterprise Manager and

Query Analyzer SQL Computer Manager

Replacement of Service Manager sqlcmd utility

Replacement of ISQL and OSQL SMO

Replacement of DMO

Sql Server Management Studio Based on Visual Studio .NET Manages RDBMS, Cubes, Reporting

Services and CE Databases Project Centered View Different editors for T-SQL, XMLA and MDX Nice things

Multithreaded Scheduling Scripting Filtering Disconnected editing

Sql Computer Manager

MMC snap-in to manage all the SQL Services and connectivity settings

Replaces Server Network Utility, Client Network Utility and SQL Service Manager

Sqlcmd.exe

Enhancements Uses OLE DB Variables DAC (Dedicated Administrator

Connection) Passes error info to calling environment Commands

I.P. Marc Mertens

DEMODEMO

Using SQL Computer ManagerUsing sqlcmdUsing SQL Computer ManagerUsing sqlcmd

Agenda


Security Concepts

Principal (Receiver from a permission) 3 Levels

Windows (Users and groups in SAM or domain) SQL Server (logins and server roles) Database (Database users, Database roles, Application

Roles and Database groups)

Securable (Object to get a permission on) 3 Scopes

Server Database Schema

Permissions Rules that specify the level of access that principal has

to securable

What’s new in security? SP’s are replaced by CREATE LOGIN ,

CREATE USER, DROP LOGIN, … Password policy Hierarchical scopes Separation of owner and schema Server level permissions Limited metadata visibility (you see only

metadata for objects you have permission on)

Execution context Master keys Support for certificates

Creating Principles

LoginsCREATE LOGIN [FlexcomAzlan\jan] FROM

WINDOWS WITH DEFAULT_DATABASE=[AdventureWorks]

UsersCREATE USER [jan] FOR LOGIN

[FlexcomAzlan\jan] Roles

CREATE ROLE Accounting

Password Policy Problem with trusted connections

Firewalls Application can’t choose security context

Sql authentication solves these problems but is less secure in SQL 2000 because there is no password policy.

SQL 2005 allows you to use the Windows Password Policy (Complexity requirements, expiration, history …) for sql logins.

Example:CREATE LOGIN [Joris] WITH PASSWORD=N'a_complex_1_password', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON

Securable scopes

Server Logins, HTTP Endpoints, Certificates,

Databases … Database

Users, Roles, Tables, Views, Functions, Assemblies …

Schema Tables, Views, Functions, Procedures

Examples of using scope

Server scopeGRANT ALTER ON DATABASE :: pubs TO userAliceGRANT ALTER ANY DATABASE TO loginAlice

Database scopeGRANT SELECT ON DATABASE :: pubs TO janGRANT INSERT ON SCHEMA :: test TO jan

Schema scopeGRANT SELECT ON OBJECT :: test.authors TO pietGRANT SELECT ON dbo.righttest TO piet

Schema What?

Namespaces for database objects SQL2000 FQN

<linked server>.<database>.<owner>.<object> SQL2005 FQN

<linked server>.<database>.<schema>.<object>

Why? In SQL2000 owner has all the rights on a

object and users can not be dropped when they own objects, the owner name is thus not a good candidate to classify objects by there name.

By using the schema scope you can manage all similar objects as one unit

Schema scenario A database contains tables for production,

human resources and sales Some users do work on production, human

resources and sales All related tables should have same owner

(authorized user). Solution:

Create schemas for the production, human resources and sales tables

Give users (roles) the necessary rights on the correct schema

Set defaults for users so they can use the object name

If needed you can move objects to other schemas or give them different owners

Schema scenario (cont.)

Create a schemaCREATE SCHEMA Production AUTHORIZATION admin

Set the search path for objects to production.<name> then dbo.<name>ALTER USER alice WITH DEFAULT_SCHEMA=Production

Create objects in a schema (automatically owned by admin)CREATE TABLE Production.Products (nr int, …)

Schema scenario (cont.)

Give a user (role) select rights on all the tables in a schemaGRANT SELECT ON SCHEMA :: Production TO managers

Move objects to another schemaALTER SCHEMA HumanResources TRANSFER

Person.Address Change the owner of a table

ALTER AUTHORIZATION ON OBJECT::Product to admin

Server level permissions Delegation of administrative work SQL2000 (Server roles)

Limited to changing membership of server roles

SQL2005 (Server level permissions) Server roles still supported Possibility to give right on objects in the

server scope or even the server itself Every permission is now grantable Examples:

GRANT CONTROL SERVER TO aliceGRANT CREATE ANY DATABASE TO joris

Execution context

SQL2000 The only way to give users rights to execute a

stored procedure (function), even if they have no rights on the objects used in the stored procedure, is by a unbroken chain (all objects references has the same owner as the SP)

SQL2005 You can specify the execution context of a SP

(function) to the following values CALLER (execute using the identity of the user who

executes the SP (is the SQL2000 case)) SELF (execute using the creators identity) OWNER (execute using the owners identity) User_name (specified user)

Execution Context (cont.)

Examples:

CREATE PROCEDURE GetProducts WITH EXECUTE AS SELF AS SELECT * FROM Production.Products

CREATE PROCEDURE GetTitles WITH EXECUTE AS ‘dbo’ AS SELECT * FROM Books.Titles

Certificates

What Digitally signed document containing public

key of a public/private key pair Generated by Certificate Authorities or

Certificate server (like SQL Server) Used in

Authentication (message signed with private key is guaranteed to come from the owner of the certificate)

Encryption (message encrypted with public key can only be decrypted by private key).

Stored in user database (backup, export, import)

Certificate scenarios

Service Broker Authenticate communications Encrypted messages

Merge replication via HTTP Uses SSL and certificates

Master keys

Managing master keys

Service Master key Encrypts database master keys Created during installation of instance Must be backed up (and restored during

recovery)

BACKUP SERVICE MASTER KEY TO FILE=‘c:\masterkey’ PASSWORD=‘P@ssword’

RESTORE SERVICE MASTER KEY FROM FILE=‘C:\masterke’ PASSWORD=‘P@ssword’

Managing Master Keys (cont) Database master key

Stored in database encrypted by given password and also encrypted by service master key (backup database)

Used to encrypt certificates and (a)symmetric keys if no encryption password is specified for them.

CreatingCREATE MASTER KEY ENCRYPTION BY PASSWORD =

'P@ssword' Opening (needed when creating keys or

certificates)OPEN MASTER KEY DECRYPTION BY PASSWORD =

'P@ssword' Closing

CLOSE MASTER KEY

Creating certificates Create a self signed certificate and

associate it with a user CREATE CERTIFICATE [CustomerClientCertificate] AUTHORIZATION [CustomerClientUser] WITH SUBJECT = ‘My certificate', START_DATE ='02/02/2005‘, EXPIRE_DATE = ’10/10/2005’, ACTIVE FOR BEGIN_DIALOG=ON

Dump a certificate to a file so it can be distributed (rights must be given to associated user), this user can act as a proxy account DUMP CERTIFICATE [CustomerClientCertificate] TO FILE = 'C:\CustomerClient.cer'

Creating certificates (cont.) Create a certificate from a file and

associate it with a user

CREATE CERTIFICATE [CustomerServiceCertificate] AUTHORIZATION [ProxyCustomerServiceUser] FROM FILE = 'C:\CustomerService.cer'

Agenda


Monitoring SQL2005

Sql Profiler Enhancements DDL Triggers Event Notifications Static Metadata Dynamic Metadata

SQL Profiler Enhancements Profiling of Analysis Services Graphical view of deadlock and

showplan Ability to save in XML Aggregate data

DDL triggers

User provided code that automatically executes after a DDL operation is performed

Is able to rollback the operation Retrieve event information using

eventdata() Typical scenarios

Logging of DDL operations Forcing a naming convention Replicating of DDL statements

DDL Triggers Example

create trigger CheckTableNameon databasefor create_tableas declare @data XML set @data=eventdata(); if

convert(varchar(100),@data.query('data(//ObjectName)')) NOT LIKE 'tbl_%'

begin RAISERROR('Invalid table name',10,1) rollback tran end

I.P. Marc Mertens

DEMO DDL triggersDEMO DDL triggers

Using DDL triggers for logging and naming conventionsUsing DDL triggers for logging and naming conventions

Event Notifications Traps SQL Server events

DML events DDL events Trace events

Uses Service Broker Works asynchronously unlike triggers Message type and contract are

predefined To use create:

A queue A service A route

Event Notifications

Scenario: Logging activity on database Security auditing Less impact on the server because it

uses the broker services All events can be centrally collected

Warning! Is unrelated to notification services

I.P. Marc Mertens

DemoDemo

Using Event Notifications to log ddl eventsUsing Event Notifications to log ddl events

Static Metadata

Catalog views in the sys schema to retrieve metadata information about databases and database objects.

Preferred to querying system tables Examples:

sys.assemblies sys.databases sys.schemas

Dynamic Metadata

Dynamic management views that provide information about the current state of a server

Replaces the pseudo tables of SQL2000

Examples: sys.dm_tran_locks sys.dm_os_threads sys.dm_tran_active_transactions

Agenda


Maintaining Indexes

What’s new? ALTER INDEX Online index operations Parallel index operations Locking options Disabling index Included columns Partitioned indexes XML Indexes Database tuning wizard

ALTER INDEX

New statement that will replace DBCC INDEXDEFRAG, DBCC DBREINDEX and CREATE INDEX WITH DROP EXISTING

Used for: Rebuilding indexes Reorganizing indexes Disabling indexes Changing index options

Online index operations

SQL2000 Creating a index caused a exclusive

table lock Rebuilding index caused a shared or

exclusive table lock Bottom line, data is unavailable when

maintaining indexes SQL2005

Allows the creation or altering of a index during which users still have access to the table

Online Index operations

Example:CREATE INDEX i_authors ON authors(au_lname) WITH ONLINE=ON

ALTER INDEX ALL ON Production.Product REBUILD WITH (FILLFACTOR = 80, ONLINE = ON, STATISTICS_NORECOMPUTE = ON)

Parallel index operations

Problems Creating, dropping, altering of a index

can monopolize the processors If previous operation are done in parallel

deadlocks can occur Solution

Control the number of processors that are used via the MAXDOP option in the CREATE INDEX or ALTER INDEX statements

Locking options ALLOW_PAGE_LOCKS

On, access to the index is via table or page locks

Off, page locks are not used ALLOW_ROW_LOCKS

On, access to the index is via table or row locks

Off row locks are not used Scenarios

If locking overhead on memory resources is to great, you can reduce it by disallowing row locks or even page locks

If you get to many blocking locks, you can enable row locks

Disabling indexes

Scenario Closure programs, cleanup programs and

import programs typically do a lot of insert, update and delete operations. Index maintenance can then significantly slow down these programs and indexes can become heavenly fragmented

To avoid this, some or all indexes are dropped before the execution of these programs and recreated afterwards.

Problem: If a clustered index is dropped and recreated, the

non clustered indexes will be dropped and recreated during the drop and create.

Dropping and creating indexes can be cumbersome

Disabling indexes

Solution: Disabling indexes Index is not maintained neither used

anymore for optimization. Disabling a clustered index on a table

makes the table unusable, however you can disable a clustered index on a view while still using the table

Disabled indexes must be rebuild before they can be used again

To disable a index or rebuild it, use ALTER INDEX

Included columns Problem:

Covering of a query by a non clustered index is a important design principle

However adding key columns to a index increases the number of leaf and non-leaf pages

Solution: Included columns can be added to the

leaf pages without increasing the number of non leaf pages

Example:create index IncludedIndex on

Sales.SalesOrderDetail(productid) include (unitprice)

Partitioned indexes

What? Uses a partition function to spread the

index rows over different filegroups Benefits

Balances load over different files Reduces contention Increases changes of parallel IO

operations Possibility to rebuild only one partition

of a index

Partitioned index (cont.)

Scenarios: When dealing with history data, only the

rows belonging to the last year are changing, so if a index is created on the history date, we should rebuild only the last year part on a regular base.

If we partition tables then we must typical partition the indexes in the same way

Partitioning of tables and indexes improves availability (if a filegroup is offline, you still access data stored in the other filegroups)

XML Indexes

What? Allows you to create indexes on columns

of the XML data type Improves the XQUERY operations on

these columns Typical use

Semi structured data

Database tuning wizard

Replaces the index tuning wizard New features:

Time bound tuning Cross database tuning Support for more physical design

structures than just indexes (partitions, indexed views …)

XML Output

I.P. Marc Mertens

DemoDemo

Included columnsPartitioning of indexesDisabling of indexes

Included columnsPartitioning of indexesDisabling of indexes

Agenda


Disaster recovery

Database snapshots Backup operations Restore operations Online restore operations Backup device mirroring Media reliability

Database snapshots What?

Read only, consistent view of the state of a database at a point in time

How? Uses sparse files (NTFS is needed) If a page in the database is modified for

the first time, the original version is written in the corresponding datafile of the snapshot database.

If a modified page is read via the snapshot, the page comes out of the datafiles from the snapshot

If an unmodified page is read it comes from the original database

Database snapshots (cont)

Limitations Does not protects you against failure of the

data, protection is only against user or programming errors

Snapshots are read-only Backup, Restore, Attach and Detach are not

possible (you can export data however) Can take up extra disk space Impact source database

Can not alter location of files Can not be dropped Security constraints can not be changed Slows done modifications (especially if there are

many snapshots)


Scenarios: Protection against dangerous operations Consistent reporting, without locking or

row versioning Maintaining a history of different

versions of the database Export data Restore database to previous snapshot


Creating a snapshotCREATE DATABASE Production_01012003 ON

(NAME=Production_data,FILENAME=‘C:\Snapshots\Production_data_snap.mdf’) AS SNAPSHOT OF Production A file specification must be given for every

data file in the source database

Dropping a snapshotDROP DATABASE Production_01012003

Backup operations

What’s new? No support anymore for NO_LOG,

TRUNCATE_ONLY and named pipes Partial backup allows you to backup all

the data in read/write filegroups + the specified read-only filegroups.

Copy only backups Do not change the next differential backups Do not truncate the log if it is a log backup Use if you don’t want to influence the daily

backup routine

Restore operations What’s new

Point in time recovery is possible for full and differential backups

Partial restore of a partial backup (example recovering of a table)

Page level restore allows you to restore only damaged pages, greatly reducing the recovery time

Piecemeal restore allows you to restore file groups in stages, allowing users to use the part that is already restored Any filegroup for full, bulk logged recovery Only filegroups that where read-only since

backup for simple recovery

Online restore

What? The database becomes online, when

restoring filegroups or data pages, once the primary filegroup is restored

Greatly reduces the perceived downtime for users

How? Automatically for the Enterprise edition

Media reliability

Mirroring Checksum calculation Continue after error

Mirroring

What? Write the backup data to two backup

devices If one of the backup media becomes

unavailable during backup the backup will fail

You can however do the restore using only one backup media

Why? To protect you against media failure

Checksum calculation

What? WITH CHECKSUM option for BACKUP and

RESTORE Backup will calculate a checksum of the

data pages and fails if the checksum is incorrect. The checksums are also written on the backup media.

Restore will calculate the checksums and compares them with the checksum of the backup, failing if there is a difference?

Continue after error

What? WITH CONTINUE_AFTER_ERROR option

of the RESTORE statement, allows you to continue the restore even if errors are detected during the restore

Why? Sometimes wrong data is better than no

data at all

I.P. Marc Mertens

DemoDemo

SnapshotsPage restoreOnline restore

SnapshotsPage restoreOnline restore

Agenda


Data availability

What’s new Database mirroring Clustering

Up to eight nodes are supported Analysis Services, Notification Services and

SQL Server replication can be clustered

Data AvailabilityAttribute Clustering Log

shippingDatabase mirroring

Failure detection Yes No NoAutomatic failover Yes No Yes

Perceived downtime 30 seconds + recovery N/A 3 seconds

Potential data loss Yes – one copy of data

Yes - latest transaction log

Yes – in some configurations

Masking of storage failure

No – shared disk Yes Yes

Special hardwareCertified storage and servers

No No

Distance 100 miles Unlimited Unlimited

ScopeSystem and user databases

User databases

User databases

Database mirroring

What? A database can be mirrored on another

server (mirror server), so that if the first server (principal server) becomes unavailable the mirror can take over

SQL Native Client Library (SNAC) knows about the mirror and will, if the principal server fails, connect to the mirror (if automatically failover is enabled)

Database mirroring (cont)

How? Log records of the principal server are

send to the mirror server and applied to the mirror database

Two modes of mirroring Synchronous

The COMMIT of a transaction waits until the mirror server has received the log records of the transaction and applied them

Asynchronous The commit does not wait on the send of the log

records


Which operation mode? Synchronous

No complete transactions will be lost Transaction performance is determined by

communication speed. Supports failover (manual or automatic)

Asynchronous Could loose complete transactions No overhead on transactions No failover support (although forcing the

use of the mirror is possible)

Database Mirroring (cont) Snapshots on mirror

The mirror database is not available but you can create snapshots on it for reporting.

Issues Need to recreate snapshots as mirror is

updated Snapshots on mirror can reduce

performance of principal in synchronized mode

During failover snapshot is not automatically transferred to the former principal


Server roles Principal server

Contains the principal database Used by the users

Mirror server Server holding the mirror database Unavailable for the users unless a failover or

a force takes place Witness server (optional)

Used for automatically failover Monitors the principal and mirror server

Manual control

Manual failover Works only in synchronous mode Minimal data loss (incomplete

transactions). Mirror becomes principal server and principle becomes mirror.

Force service on mirror You can lose complete transactions Works in synchronous or asynchronous

mode. Mirror becomes principal server and principle becomes mirror upon connection (if enough log is available)

Break mirror

Quorum

Quorum Is a relationship between servers that

are in communications with each other (connected state).

When quorum exists the session and the servers are said to have quorum. If a server instance becomes disconnected from a mirror session the instance loses quorum.

Only applicable for synchronous mode

Quorum (cont.)

High-Protection mode (no witness server, synchronous mode) Quorum requires principal and witness.

If either partner loses quorum both partners lose quorum

Failover is only possible if the quorum exists, you can force the mirror however.

The database becomes unavailable if the quorum does not exists

Quorum (cont).

High-Availability mode (witness server) Quorum involves principal (p), mirror

(m) and witness (w) Full quorum (p,m,w), every server fulfills

its own role unless a manual failover takes places.

Quorum (p,w), the principal database is set to disconnected but is still available and mirroring is in suspended state. If the mirror becomes available again it regains quorum as mirror and begins resynchronizing

Quorum (cont).

High-Availability mode (witness server) Quorum (m,w)

Automatically does a failover, mirror becomes principal and principal will become mirror when started. Database is still available (SNAC clients connect to new principal)

Partner to partner Quorum (p,m) No automatically failover is possible and the

session temporarily operates as High-Security mode. Manual failover is possible

Asynchronous mode

Witness server can still be used, but has no functionality

If principal server becomes unavailable you can manual force service on the mirror

If mirror server becomes unavailable the principal database is still available. Mirror must be recovered and the session must be resumed

Setting up mirroring Create endpoints for principal, mirror

and if needed witnessCREATE ENDPOINT endpoint_mirroring STATE = STARTED AS TCP ( LISTENER_PORT = 5023 ) FOR DATABASE_MIRRORING

(ROLE=PARTNER);CREATE ENDPOINT endpoint_mirroring STATE = STARTED AS TCP ( LISTENER_PORT = 5024 ) FOR DATABASE_MIRRORING

(ROLE=WITNESS);

Setting up mirroring (cont)

Backup the principal database. Restore the principal database backup on

the mirror server with the norecovery option.

Create partnership on the mirrorALTER DATABASE TestMirror SET PARTNER='TCP://boojum:5022'

Create partnership on the principalALTER DATABASE TestMirror SET PARTNER='TCP://boojum:5023'

Change operation mode if neededALTER DATABASE TestMirror SET SAFETY off

Setting up mirroring (cont)

Establish a witness server if neededALTER DATABASE TestMirror SET

WITNESS='TCP://boojum. :5024'

Controlling mirroring

Manual failoverALTER DATABASE TestMirror SET PARTNER FAILOVER

Force service to mirrorALTER DATABASE TestMirror SET PARTNER

FORCE_SERVICE_ALLOW_DATA_LOSS

Break mirrorALTER DATABASE TestMirror SET PARTNER OFF

I.P. Marc Mertens

DemoDemo

MirroringMirroring

I.P. Marc Mertens

Questions?Questions?

sql server 2005 whats new for the dba

Documents