Transcript Using the Disaster Recovery Plan

SQL Server在企业级
环境下的应用
赵歌喃
SQL Server资深讲师
Overview


Integrating SQL Server with Your Enterprise
Application Architecture
Designing a Disaster Recovery Plan
Integrating SQL Server with Your
Enterprise Application Architecture








Introduction to Application Architecture
Query Processing and Performance
Batches and Transactions
Building a Highly Available Application
Debugging Availability Issues
Managing Connections
Using a Loosely Coupled Design
Clustering for Application Availability
 Introduction to Application
Architecture






The N-Tier Application Model
Presentation Services
Business Logic Services
Data Services
Cursors
Cursor Best Practices
The N-Tier Application Model
Presentation Services
Business Logic Services
Data Services
Presentation Services

Windows Client Applications
Installed locally
 Use group policy to ensure logic is always available


Web and Intranet Applications
Accessed using a browser
 Web site availability is an issue

Business Logic Services

Usually Implemented as Components


Can be deployed locally or remotely
Can be Implemented as Stored Procedures

Business logic availability becomes tied to database
availability
Data Services


Database Provides Data Storage, Retrieval, and
Modification Services
High Availability is Crucial to Most Enterprise
Applications
Cursors








Cursors Overview
Default Result Sets
Server-side Cursors
Server API Cursors
Fast Forward Only Cursors
Transact-SQL Server Cursors
API Client-side Cursors
Cursors and Locking
Cursor Best Practices





Use Default Result Sets in Place of Cursors
Avoid the Usage of Server Cursors to Return a
Few Rows at a Time
Always use Optimistic Locking for Server Side
Cursors
Avoid Cursor Implicit Conversion
Miscellaneous
Query Processing and
Performance









Compiling and Executing SQL
Execution Plan Caching
Stored Procedures
Initial Processing of Stored Procedures
Cached Stored Procedure Plans and
Performance
Stored Procedure Recompilation
Prepare-Execute
sp_executesql
Auto-Parameterization
Compiling and Executing SQL
Execution Plan Caching


Ad-Hoc Statements Are the First To Be Discarded from
Cache
A Query Can Reuse a Plan in Cache that it Matches Exactly


The Lazywriter Ages Plans and Removes Them from Cache




Hash key based on SQL text and ANSI settings
Stored plans are managed much like data buffers
Ad-Hoc Query Cost Is Zero, then Incremented when
Reused
Stored Procedure, Prepared Statement, or Auto
Parameterized Statements Use Creation Cost
Procedure Cache is Not Maintained Separately
Stored Procedures

Advantages of Stored Procedures
Isolated business logic
 Modular coding
 Performance benefits to caching plans
 Security buffer


Disadvantages of Procedures
Dynamic SQL
 Bad plans from cached procedures


DONE_IN_PROC Messages
Initial Processing of Stored
Procedures
Creation
Parsing
Resolution
Resolution*
Execution
sysobjects
Name, type, etc.
syscomments
Text of object
syscolumns
Parameter list
sysdepends
Object dependencies
Optimization
(first time
or recompile)
Compilation
Compiled plan placed in
unified cache
Cached Stored Procedure Plans
and Performance



Caching Plans Can Be a Performance Benefit
for Stored Procedures Performing Repetitive
Tasks
Caching Plans Can Harm Performance when
Inefficient Execution Results from a Cached
Plan
Modular Code Is Key:
When to recompile
 When not to recompile

Stored Procedure Recompilation




Manual Recompilation
Automatic Recompilation
Inline Recompilation
Temporary Tables and When Procedures are
Recompiled
Prepare-Execute

Statements Can Be Prepared Once and
Executed Many Times


A query plan is cached and a handle returned
Prepare-Execute Improved in SQL Server 2000
One round trip to prepare and Execute
 One round trip for each Execute
 One round trip to Unprepare, Prepare the next and
Execute the next


Clients Must Unprepare to Avoid Harming
Performance

ADO 2.6 calls sp_unprepare implicitly
sp_executesql




sp_executesql Plans Are Cached for Reuse by
Multiple Users
sp_executesql Is More Powerful When Using
Parameter Markers
In ADO, Call sp_executesql As Ad-Hoc SQL to
Minimize Round Trips
Use Execute('string') When Assigning Identifiers
to Variables
Auto-Parameterization



Auto-Parameterization is Attempted with SQL
Language TDS Messages
Only “Safe” Auto-Parameterized Plans Can Be
Shared
SQL Server is Conservative About AutoParameterization


Inefficient Plans Can Result from Different
Parameters
Detecting Auto-Parameterization Through
Statistics Time and Profiler
Batches and Transactions


Batches and Transactions
Transactions Best Practices
Batches and Transactions





Autocommit
Explicit and Implicit Transaction
Transaction Nesting
Statement,Transaction, and Batch Abort
Aborting and Set Options
Transactions Best Practices





Keep Transactions Small
Transaction Isolation Level
Don’t Span a Transaction to Multiple Batches
No User Interaction within transactions
Transactions and Locking
Building a Highly Available
Application




Business Tier Error Handling
SQL Server Error Handling
Special Message Handling Situations
Testing
Business Tier Error Handling


Use Error Handlers to Trap Errors
Log the Errors
SQL Server Error Handling






SQL Server Errors and Messages
User-Defined Errors
Use @@ERROR and @@TRANCOUNT for
Decisions Within an Application
Error Severity and @@error
Compile and Runtime Errors
Transaction Error Checking
Special Message Handling
Situations

Deadlock Handling
Cancel the batch and rollback trans
 Resubmit the batch again


Query Timeout Handling




Rollbacks occurring in a trigger
Lock Timeout Handling
Constraint Violations Handling
A Lost Connection is a Cancel Operation
Testing




Test Throughout the Development Cycle
Load Testing
Component Testing
System Testing
Debugging Availability Issues


Debugging
Tracing
Debugging






Reproduce the Problem
Log the Application During Failure
Compare Performance Metrics to Baseline
Use Built In Diagnostic Tools
Determine the Source of the Failure
Create and Test a Fix
Tracing



Use Message Queuing to Log Traces Centrally
Minimize the Impact of Tracing on the
Application’s Performance
Trace Before Major Activities
Lesson: Managing Connections


Connection Pooling
COM+ Object Pooling
Connection Pooling

Component Opens a Database Connection

When Closed, the Connection Is Placed in
the Pool

Pooled Connections Can Be Re-used by
Other Components
COM+ Object Pooling









Object Pooling vs. Connection Pooling
Speed
Database Connection Requests
Incremental Cost
Manual Enlisting in a Transaction
Turning Off Automatic Enlistment
Disabling Automatic Connection Pooling
Implementing IObjectControl
Transaction Specific Pools
Using a Loosely Coupled
Design



Microsoft Message Queuing Services
COM+ Queued Components
Asynchronous Scenarios
Microsoft Message Queuing
Services


Data Is Passed Asynchronously
Multiple Servers Can Read from the Same
Queue

Providing load balancing and failover
COM+ Queued Components

Create a Queued Component
Mark COM+ application and interface as queued
 COM+ application must listen for messages


Call Methods on the Queued Component

Use the queue moniker
Asynchronous Scenarios

Data Update, Delete, or Insert Operations
Continue order taking when database is unavailable
 Not useful for data retrieval


No Errors or Acknowledgements will be
Returned

Use a response queue or COM+ event for error
notification
Clustering for Application
Availability




Using a Web Farm for Presentation Services
Maintaining Application Server Availability
Component Load Balancing
Best Practices
Using a Web Farm for
Presentation Services
Network Load Balancing
Maintaining Application Server
Availability

Use Stateless Components

This allows dynamic load balancing
Component Load Balancing
Provided by Application Center
Only Activations are Load Balanced
Based on Response Time and Round-robin
Routing Server (cluster)
CoCreateInstance
Best Practices - 1







Avoid Using Poorly Coded Extended Stored Procedures
Disallow Loading COM Objects in Process with SQL
Server
Always Create a Clustered Index on a Table
Always Use Index Tuning Wizard Extensively.
If Error Within a Transaction, Always Rollback the
Transaction Explicitly
Always Stress Test at the Maximum Projected User Load
Before Deployment
Avoid Using a Lot of Ad-Hoc SQL Statements
Best Practices - 2








When a Client Gets Lock Timeout Error or Deadlock,
Include Retry Logic in the Code
Set Transaction Isolation Level as Low as Possible
Keep Statistics Updated
Remove Internal Page Fragmentation
Reduce Index Fragmention
Minimize Use of Cursors
Keep Transactions Short and Have No User Interaction
Within a Transaction
Let Applications Process the Result Sets as Quickly as
Possible and to Completion
Best Practices - 3



Ensure AutoClose Database Option Is NOT
SET
Avoid or Reduce the Need to AutoGrow the
Database and Log Files
Use the Default sp_configure Settings
Designing a Disaster Recovery
Plan
 Disaster
Recovery Plan Basics
 Writing the Disaster Recovery Plan
 Testing and Using the Disaster Recovery Plan
Disaster Recovery Plan Basics
 What
Is a Disaster Recovery Plan?
 System Design Considerations for Disaster
Recovery
What Is a Disaster Recovery Plan?
 Disaster
Recovery Scenario
 Importance of Disaster Recovery Planning
System Design Considerations for
Disaster Recovery
 Review





Design Considerations
SQL Server 2000 failover clustering
Log shipping
Replication
Remote disk mirroring
Snapshots
 Reduce
the Chance of Total System Failure
Writing the Disaster Recovery
Plan
 Resource
Information in the Disaster Recovery
Plan
 Procedural Information in the Disaster Recovery
Plan
 Backup Site Information in the Disaster Recovery
Plan
 Scenario Information in the Disaster Recovery Plan
Resource Information—Tools
 SQL
Server Error Log
 Windows Event Log
 DBCC Commands
Resource Information—Hardware
and Software
 Operating
System
 SQL Server
 Other Software Documentation
 Software Locations
 Transact-SQL Scripts
 Server Hardware and Configuration
 Access to Other Servers
Resource Information—Technical
Staff
 All
Necessary Contacts
 Contact Information
 Backup Contacts
Procedural Information in the
Disaster Recovery Plan
 Timelines
and Data Loss Expectations
 Steps for Bringing Your System Back Online
 Steps for Analyzing the Results of the Recovery
Efforts
Backup Site Information in the
Disaster Recovery Plan
 Hot
Backup Site
 Warm Backup Site
 Cold Backup Site
Scenario Information —
Environment
 Natural
Disasters
 Power Outage
 Hardware Failure
Scenario Information—
SQL Server
 Hardware
or Software Corruption
 User-related Corruption
 Inability to Start SQL Server
Scenario Information—Related
Systems
 Failure
of Application, Related Server, or
Network
 Failure of Publisher, Distributor, or Subscriber
 Failure of a Cluster
 Failure in a Distributed Environment
Testing and Using the Disaster
Recovery Plan
 Testing
the Disaster Recovery Plan
 Using the Disaster Recovery Plan
 Following Up After Using the Disaster Recovery
Plan
Testing the Disaster Recovery
Plan
 Why
Test?
 Develop Test Cases for Every Scenario
 Execute the Test Plan
 Validate Test Results
 Consider Possible Redesign Based on Testing
Using the Disaster Recovery Plan
 Follow
the Plan
 Monitor Closely
 Expect the Unexpected
 Restart Recovery, If Needed
Following Up After Using the
Disaster Recovery Plan
 Perform
a Post Mortem after a Disaster Occurs
 Think About How to Prevent a Recurrence
 Evaluate What Went Right and What Went
Wrong with Your Plan
 Use Post Mortem Results to Improve High
Availability and Your Disaster Recovery Plan
Review
 Integrating
SQL Server with Your Enterprise
Application Architecture
 Designing a Disaster Recovery Plan