快速上手營

錢曉明資策會 資深講師

議程

1. SQL Server 2005 資料庫管理 1.1 SQL Server 2005 管理工具介紹與使用1.2 資料庫的監控1.3 資料庫與索引的維護1.4 資料庫的安全性1.5 災難回復與資料的高可用度

2. SQL Server 2005 資料庫開發 2.1 TSQL 的新增功能2.2 SQL Server 2005 的 XML 支援功能2.3 Service Broker 功能介紹2.4 Web Service 功能介紹2.5 SQL Server 2005 .NET CLR 的支援能力

3. SQL Server 2005 商業智慧 3.1 SQL Server Integration Service (SSIS)3.2 SQL Server Analysis Service3.3 SQL Server Data Mining3.4 SQL Server Reporting Service

相關教育訓練及演講

課程名稱 時數SQL 2005 快速上手營（管理 / 開發 /BI)

14 小時

SQL 2005 實戰系列（管理） 24 小時

SQL 2005 實戰系列（開發） 24 小時

SQL 2005 應用程式相容性測試 6 小時

SQL 2005 認證班 （ MCTS ） 56 小時

SQL 2005 認證班 （ MCITP 管理） 63 小時

SQL 2005 認證班（ MCITP 開發） 70 小時

詳情請參考詳情請參考微軟網站微軟網站或或資策會網站資策會網站

What is SQL Server 2005?

SQL Server 2005SQL Server 2005

Relational Database Engine

.NET CLR

Integration Services

Reporting Services

Notification ServicesAnalysis Services

Native HTTP Support

Service Broker

Replication

Full-Text Search

高可靠、高可用、擴充性 整合的管理 效能 & 安全性的提昇

Visual Studio & .NET 的整合Native XML, CLR, Web Services

使用者自訂 Data Type

即時的 OLAP, Data Mining, KPI, UDM 多國語言的 supportReporting Services, Report Builder更快的 DTS (SSIS) 600% faster

高擴充、高穩定、安全的企業級資料管理平台高擴充、高穩定、安全的企業級資料管理平台

SQL 2005 Top 30 – 管理

Database Mirroring

Online Indexing Operations

New Integrated Toolset

Snapshot Isolation

Data Partitioning

Mirrored Backups

Online Restore

Fast Recovery

Dedicated Administrator Connection

Replication Enhancements

智慧智慧開發開發管理管理

B IB I開發開發管理管理

SQL 2005 Top 30 – 開發

.NET Framework Hosting

XML Technologies

ADO.NET 2.0

Transact-SQL Enhancements

SQL Service Broker

Notification Services

Web Services

XQuery Support

Full-Text Search Enhancements

Security Enhancements

智慧智慧開發開發管理管理

B IB I開發開發管理管理

SQL 2005 Top 30 – 商業智慧

Analysis Services

SSIS (Data Transformation Services)

Data Mining

Reporting Services

Clustering Support

Key Performance Indicators

Scalability and Performance

One-Click Cube

Architecture Enhancements

Integration with the Microsoft Office System

智慧智慧開發開發管理管理

B IB I開發開發管理管理

議程

1. SQL Server 2005 資料庫管理 1.1 SQL Server 2005 管理工具介紹與使用1.2 資料庫的監控1.3 資料庫與索引的維護1.4 資料庫的安全性1.5 災難回復與資料的高可用度

1.1 SQL Server 2005 管理工具介紹與使用

議程

開發與管理資料庫工具的整合組態工具SQLCMD

開發與管理資料庫工具的整合

SQL Server Management Studio

SQL Server Business Intelligence Development Studio

整合 Enterprise Manager, Query Analyzer, 與 Analysis Manager 的功能可管理 Databases Engine, Analysis Services, Reporting Services, SQL Server Mobile 與 Integration Services

可用來開發 Transact-SQL, XMLA, MDX, 與 XQuery scripts

SQL Server Management Studio

SQL Server Business Intelligence Development Studio

Demonstration: 開發與管理資料庫工具的整合

組態工具

Reporting Services Configuration

SQL Configuration Manager

SQL Server Surface Area Configuration

SQLCMD

Command-line tool for executing Transact-SQL statements and scripts

Uses OLE DB to run Transact-SQL batches Replaces osql

Enhancements over osql Variables Query server information Passes error information to calling environment Dedicated Administrator Connection Commands

Dedicated Administration Connection

管理者專屬的連線不需重新啟動 Server 就可以將沒反應的 session 清除使用時機 : 當 SQL Server 沒有反應時或是過於繁忙時的管理者登入

sqlcmd –A

SHUTDOWN WITH NOWAIT

1.2 資料庫的監控

Overview

Using SQL Profiler and Performance Monitor Integration

Using DDL Triggers

Lesson: Using SQL Profiler

SQL Profiler Enhancements in SQL Server 2005

How to Save a Trace as XML

SQL Profiler Enhancements in SQL Server 2005

Profiling Analysis ServicesProfiling Analysis Services

Tracing Showplan and deadlock eventsTracing Showplan and deadlock events

Saving results as XMLSaving results as XML

Aggregating dataAggregating data

Lesson: Using DDL Triggers

What Are DDL Triggers?

How to Create DDL Triggers

How to Manage DDL Triggers

Demonstration: Creating a DDL Trigger

Process:

What Are DDL Triggers?

UPDATE STATISTICS someTableUPDATE STATISTICS someTableDDL statement executedDDL statement executed11

Triggers to trap DDL statement execution

Database or server scope

DDL action performedDDL action performed22

Trigger firesTrigger fires33 EventDataEventData

CREATE TRIGGER UpdStatsON DATABASEFOR UPDATE_STATISTICSAS...

CREATE TRIGGER UpdStatsON DATABASEFOR UPDATE_STATISTICSAS...

CREATE TRIGGER UpdStatsON DATABASEFOR UPDATE_STATISTICSASDECLARE @data XML. . .SET @data = eventdata(). . .

CREATE TRIGGER UpdStatsON DATABASEFOR UPDATE_STATISTICSASDECLARE @data XML. . .SET @data = eventdata(). . .

CREATE TRIGGER UpdStatsON DATABASEFOR UPDATE_STATISTICSASDECLARE @data XMLDECLARE @database NVARCHAR (100)SET @data = eventdata()SET @database =

CONVERT(NVARCHAR(100), @data.query('data(//DatabaseName)'))

. . .

CREATE TRIGGER UpdStatsON DATABASEFOR UPDATE_STATISTICSASDECLARE @data XMLDECLARE @database NVARCHAR (100)SET @data = eventdata()SET @database =

CONVERT(NVARCHAR(100), @data.query('data(//DatabaseName)'))

. . .

How to Create DDL Triggers

Define the trigger name, scope, and eventDefine the trigger name, scope, and event11

Retrieve event information using eventdata()Retrieve event information using eventdata()22

Extract event data using query()Extract event data using query()33

1.3 資料庫與索引的維護

Overview

Managing Indexes

Using the Database Tuning Advisor

Lesson: Managing Indexes

New Index Features in SQL Server 2005

How to Create an Index

How to Alter an Index

How to Drop an Index

How to Manage Fragmentation

ALTER INDEX … REBUILD …SELECT * FROM MyTable

CREATE INDEX …ON MyTable(…) WITH (ONLINE = ON)

CREATE INDEX …WITH (MAXDOP=3)

ALLOW_ROW_LOCKS = ONALLOW_PAGE_LOCKS = ON XMLIndex XML Column

SELECT column::query(…)FROM …

New Index Features in SQL Server 2005

ALTER INDEX statement

Online index operations

Parallel index operations

Locking options

Indexes with included columns

Partitioned indexes

XML indexes

How to Manage Fragmentation

Indexes become fragmented over time

Reorganize – defragments leaf nodes and compacts internal nodes

Rebuild – defragments, compacts, re-sorts, and restructures

ALTER INDEX IX_Employee_ManagerIDON HumanResources.EmployeeREORGANIZE WITH (LOB_COMPACTION = ON)

ALTER INDEX IX_Employee_ManagerIDON HumanResources.EmployeeREORGANIZE WITH (LOB_COMPACTION = ON)

ALTER INDEX IX_Employee_ManagerIDON HumanResources.EmployeeREBUILD WITH (FILLFACTOR = 80)

ALTER INDEX IX_Employee_ManagerIDON HumanResources.EmployeeREBUILD WITH (FILLFACTOR = 80)

索引重整 （ Index Rebuild ）

SQL 2005 新增 DMF sys.dm_db_index_physical_stats SELECT avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats (DB_ID(),

OBJECT_ID(N'NewEmployee'),Null,Null,Null)

取代 DBCC Showcontig (NewEmployee)

新增大量資料會造成資料碎裂 Insert into NewEmployee

SELECT * FROM HumanResources.Employee

索引重整可消除碎裂，提昇效能 Alter Index EmployeeId

on NewEmployee

Rebuild

What Is the Database Tuning Advisor?

WorkloadWorkload ResultsResults

.sql script

Databases

DTA

Table

.trc file

How to Use the Database Tuning Advisor

Create a new sessionCreate a new session11

Set tuning optionsSet tuning options33

Perform the analysisPerform the analysis44

Examine the resultsExamine the results55

Implement the recommendationsImplement the recommendations66

Specify the workloadSpecify the workload22

1.4 資料庫的安全性

錢曉明

資策會 資深講師台灣微軟 資深講師

Overview

SQL Server 2005 Security Overview

Managing SQL Server 2005 Security

Managing Permissions

Managing Certificates

Lesson: SQL Server 2005 Security Overview

Security Enhancements in SQL Server 2005

What Are Principals?

What Are Securables?

What Are Permissions?

Multimedia: SQL Server 2005 Security Architecture

Security Enhancements in SQL Server 2005

Password policy for SQL Server loginsPassword policy for SQL Server logins

Hierarchical security scopesHierarchical security scopes

Separation of user and schemaSeparation of user and schema

Limited metadata visibilityLimited metadata visibility

Declarative execution contextDeclarative execution context

What Are Principals?

Principals

Server RoleSQL Server Login

Windows GroupDomain User AccountLocal User Account

UserDatabase RoleApplication RoleGroup

SQL ServerSQL Server

DatabaseDatabase

WindowsWindows

Permissions

Securables

What Are Securables?

Principals

Server RoleSQL Server Login

Windows GroupDomain User AccountLocal User Account

UserDatabase RoleApplication RoleGroup

SQL ServerSQL Server

DatabaseDatabase

WindowsWindows

FilesRegistry Keys

Server

Schema

Database

Permissions

Securables

What Are Permissions?

Principals

Server RoleSQL Server Login

Windows GroupDomain User AccountLocal User Account

UserDatabase RoleApplication RoleGroup

SQL ServerSQL Server

DatabaseDatabase

WindowsWindows

FilesRegistry Keys

CREATEALTERDROPCONTROLCONNECTSELECTEXECUTEUPDATEDELETEINSERTTAKE OWNERSHIPVIEW DEFINITIONBACKUP

Permissions

Securables

GRANT/REVOKE/DENYGRANT/REVOKE/DENY

ACL

Server

Schema

Database

What Are Schemas?

dbo

Products(Server1.AdventureWorks.dbo.Products)

SalesData

Orders(Server1.AdventureWorks.SalesData.Orders)

Namespaces for database objectsNamespaces for database objects

How to Manage Schemas

Altering schemasAltering schemas

Alter schema Productiontransfer dbo.OrdersAlter schema Productiontransfer dbo.Orders

How to Set a User’s Default Schema

Products

Orders

SELECT * FROM Products

SELECT * FROM Orders

SELECT * FROM Products

SELECT * FROM Orders

Bill(no default schema)

Ted(Default schema = sales)

ALTER USER TedWITH DEFAULT_SCHEMA = salesALTER USER TedWITH DEFAULT_SCHEMA = sales

Assigning a default schemaAssigning a default schema

Name ResolutionName Resolution

sales

dbo

CREATE PROCEDURE GetOrdersWITH EXECUTE AS CALLERASSELECT * FROM sales.orders

CREATE PROCEDURE GetOrdersWITH EXECUTE AS CALLERASSELECT * FROM sales.orders

How to Control Execution Context

Execution context principal

Execution context principal

Stored ProcedureStored Procedure

SELECT *FROM sales.ordersSELECT *FROM sales.orders

CREATE PROCEDURE GetOrdersWITH EXECUTE AS SELFASSELECT * FROM sales.orders

CREATE PROCEDURE GetOrdersWITH EXECUTE AS SELFASSELECT * FROM sales.orders

CREATE PROCEDURE GetOrdersWITH EXECUTE AS 'Rose'ASSELECT * FROM sales.orders

CREATE PROCEDURE GetOrdersWITH EXECUTE AS 'Rose'ASSELECT * FROM sales.orders

Sales.ordersSales.ordersCallerCaller

Lesson: Managing Certificates

What Are Certificates?

How Does SQL Server Use Certificates?

How to Manage Certificates

Demonstration: Managing Certificates

What Are Certificates?

Digitally signed document containing a public/private key pair

Obtained from: Certificate authority Certificate server

Used for: Authentication – a message signed by the private key is

guaranteed to be sent by the owner of the certificate Encryption – a message encrypted with a public key can

only be decrypted with the matching private key, and vice versa

How Does SQL Server Use Certificates?

Certificate server – new Transact-SQL statements

Service Broker

Authenticated communications

Encrypted messages

Web synchronization for merge replication

Replication over HTTP

Uses SSL and digital certificates

Encryption Hierarchy

How to Manage Certificates

Creating certificatesCreating certificates

CREATE MASTER KEYCREATE MASTER KEY

CREATE CERTIFICATE AWCert ENCRYPTION_PASSWORD = 'P@ssw0rd', START_DATE='2006/06/27'

CREATE CERTIFICATE AWCert ENCRYPTION_PASSWORD = 'P@ssw0rd', START_DATE='2006/06/27'

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'DB Master!'CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'DB Master!'

Creating certificatesCreating certificates

CREATE SYMMETRIC KEY [AWSym Key] WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE AWCert

CREATE SYMMETRIC KEY [AWSym Key] WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE AWCert

1.5 災難回復與資料的高可用度

Overview

Disaster Recovery in SQL Server 2005

Using Database Snapshots

Backup and Restore Operations

Introduction to Database Mirroring

Disaster Recovery in SQL Server 2005

Database snapshotsDatabase snapshots

12:00 Snapshot

Online restore operationsOnline restore operations

Backup media mirroringBackup media mirroring

Improved verificationImproved verification

Checksum12345678

Checksum12345678

Checksum12345678

Database snapshotsDatabase snapshots

Online restore operationsOnline restore operations

Backup media mirroringBackup media mirroring

Improved verificationImproved verification

Lesson: Using Database Snapshots

What Are Database Snapshots?

How to Manage Database Snapshots

Demonstration: Creating a Database Snapshot

How to Use a Database Snapshot to Recover Data

What Are Database Snapshots?

12:00 Snapshot

Page

Page

Read-only, consistent view of a database

Specified point-in-time

Modifying data

Copy-on-write of affected pages

Reading data

Accesses snapshot if data has changed

Redirected to original database otherwise

How to Manage Database Snapshots

Creating a snapshotCreating a snapshot

Deleting a snapshotDeleting a snapshot

RestrictionsRestrictions

DROP DATABASE AdventureWorks_dbsnapshot_1800DROP DATABASE AdventureWorks_dbsnapshot_1800CREATE DATABASE AdventureWorks_dbsnapshot_1800 ON (NAME = AdventureWorksData, FILENAME = 'C:\SnapshotData\AdventureWorks_Data.mdf'), (NAME = WorkOrders, FILENAME = 'C:\SnapshotData\WorkOrders_Data.mdf'), (NAME = TransactionHistory, FILENAME = 'C:\SnapshotData\TransactionHistory_Data.mdf') AS SNAPSHOT OF AdventureWorks

CREATE DATABASE AdventureWorks_dbsnapshot_1800 ON (NAME = AdventureWorksData, FILENAME = 'C:\SnapshotData\AdventureWorks_Data.mdf'), (NAME = WorkOrders, FILENAME = 'C:\SnapshotData\WorkOrders_Data.mdf'), (NAME = TransactionHistory, FILENAME = 'C:\SnapshotData\TransactionHistory_Data.mdf') AS SNAPSHOT OF AdventureWorks

Demonstration: Creating a Database Snapshot

In this demonstration, you will see how to:

Create a database snapshot

Access a database snapshot

Examine database snapshot file growth

How to Use a Database Snapshot to Recover Data

Scenario Example Code / Steps

Undeletingrows

Undoingan update

Recoveringa droppedobject

11 Script the object in the database snapshot

INSERT INTO Production.WorkOrderRoutingSELECT * FROM AdventureWorks_dbsnapshot_1800.Prod.WorkOrderRouting

INSERT INTO Production.WorkOrderRoutingSELECT * FROM AdventureWorks_dbsnapshot_1800.Prod.WorkOrderRouting

UPDATE HR.DepartmentSET Name = ( SELECT Name FROM AdventureWorks_dbsnapshot_1800.HR.Department WHERE DepartmentID = 1)WHERE DepartmentID = 1

UPDATE HR.DepartmentSET Name = ( SELECT Name FROM AdventureWorks_dbsnapshot_1800.HR.Department WHERE DepartmentID = 1)WHERE DepartmentID = 1

Caution: Not a substitute for a comprehensive backup and restore strategy

Execute the script in the source database22

Repopulate the object (if appropriate)33

Lesson: Backup and Restore Operations

Changes to Backing Up Databases

Changes to Restoring Databases

How to Perform an Online Restore

Changes to BackUp/Restore Databases

Transact-SQL BACKUP statement – no support for BACKUP LOG WITH NO_LOG and BACKUP LOG WITH NO_TRUNCATE

Data and differential backups include enough log to restore to a consistent state

Partial backups enable piecemeal restore of a simple recovery model database

Partial restore operations/Piecemeal restore operations

BACKUP DATABASE AdventureWorks READ_WRITE_FILEGROUPS TO Adv1

RESTORE DATABASE AWTemp Filegroup='Primary', FROM Adv1 WITH PARTIAL

Changes to Restoring Databases

Point-in-time recovery

Page-level restore operations

RESTORE DATABASE AdventureWorks FROM AWBackup WITH RECOVERY, STOPAT = 'Mar 1, 2004 2:00 PM'

RESTORE DATABASE AdventureWorksPAGE = 'AdventureWorks_data_1:832' ',

FROM AWBackup

How to Perform an Online Restore

Only in Enterprise Editions

Only for full or bulk-logged recovery models

By default, restoring a file or a page is automatically an online restore

Filegroup offline when any file within it is being restored

Database offline during restore of any files in the primary filegroup

資料的高可用度

Introduction to Database Mirroring

Configuring Database Mirroring

Introduction to Data Availability

Attribute Clustering Log shipping Database mirroring

Failure detection Yes No No

Automatic failover Yes No Yes

Perceived downtime 30 seconds + recovery N/A 3 seconds

Potential data loss Yes – one copy of data Yes – latest

transaction logYes – in some configurations

Masking of storage failure No – shared disk Yes Yes

Special hardware

Certified storage and servers No No

Distance 100 miles Unlimited Unlimited

Scope System and user databases User databases User databases

What Is Database Mirroring?

Principal Database Mirror DatabaseClient

UpdateUpdate Update

Principal Database Mirror DatabaseClient

Server Roles in Database Mirroring

Server holding the principal database Users connect to this server

Server holding the principal database Users connect to this server

Principal server

Server holding the mirror database Users connect to this server only after failover

Server holding the mirror database Users connect to this server only after failover

Mirror server

Monitors connectivity between partners and initiates automatic failoverMonitors connectivity between partners and initiates automatic failover

Witness server

Mirror Sessions

Mirror server requests transaction log records—synchronizationMirror server requests transaction log records—synchronizationOn initiation

Principal server transmits log records as transactions occurPrincipal server transmits log records as transactions occur

During the session

Communications or server failure Database administrator

Communications or server failure Database administrator

Termination

Database Mirroring Configurations

ScenariosScenariosSynchronous with witness serverSynchronous with witness server

Asynchronous with witness serverAsynchronous with witness server

Synchronous without witness serverSynchronous without witness server

Principal failureAutomatic failover – mirror becomes principalOriginal principal recovered, becomes new mirror

Mirror failurePrincipal continuesSession in suspended state

Principal failureAutomatic failover – mirror becomes principalOriginal principal recovered, becomes new mirror

Mirror failurePrincipal continuesSession in suspended state

Principal failureDatabase unavailable – force service to mirrorOriginal principal recovered, becomes new mirror

Mirror failurePrincipal continuesSession in suspended state

Principal failureDatabase unavailable – force service to mirrorOriginal principal recovered, becomes new mirror

Mirror failurePrincipal continuesSession in suspended state

Principal failureDatabase unavailable – force service to mirrorOriginal principal recovered – resume session

Mirror failureDatabase unavailableOriginal mirror recovered – automatically available

Principal failureDatabase unavailable – force service to mirrorOriginal principal recovered – resume session

Mirror failureDatabase unavailableOriginal mirror recovered – automatically available

How to Configure a Mirror Server

Mirror server prerequisites:Mirror server prerequisites:

Accessible from principal serverAccessible from principal server

Trusted by principal serverTrusted by principal server

Sufficient resources to substitute for principal serverSufficient resources to substitute for principal server

Mirror database:Mirror database:

Must match principal databaseMust match principal database

Must implement full recovery modelMust implement full recovery model

Must be in “restoring” stateMust be in “restoring” state