Transcript Html Overview

Implementing Database Snapshot &
Database Mirroring in SQL Server
2005
Presented by Tarek Ghazali
IT Technical Specialist
Microsoft SQL Server MVP
Microsoft SQL Server MCTS
Web Development MCP
Database Snapshot
Recovering from Human Error
• Snapshot of an entire database at a point in time
– Great to recover from User Error
– Created instantly
– Read only
• Works with
– Single server
– Database Mirroring
– Failover Cluster
• Base database continues to change
– Database Snapshot does not restrict the base database
• Multiple Snapshots are allowed
Database Snapshot
How it really works
CREATE DATABASE dbSnap AS SNAPSHOT OF mydb
USE mydb
UPDATE (pages 4, 9, 10)
mydb – Database
Page
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
dbSnap – Read-Only Database Snapshot
USE dbSnap
SELECT (pages 4, 6, 9, 10, 14)
Snapshot (Copy on Write)
Command
Northwind
Northwind_SS
Create Northwind_SS
Update Northwind
Read Northwind_SS
Result:
DD
Value
Value
R
D
X
B
D
H
J
Space Used 12.5%
0%
L
Y
M
Database Snapshot
How it really works (Cont)
A sparse file is an essentially empty file that contains no user data and
has not yet been allocated disk space for user data. As more and more
pages are updated in the source database, the size of the file grows.
Constraints on the Source Database
• DB snapshot and source database must be on
the same instance
• If any db snapshots exist on a source database,
the source database cannot be dropped or
restored
– first delete all the db snapshots
• However, backing up the source database
works normally; it is unaffected by DB snapshots
Considerations of DB Snapshots
• None of the specifications of the files of the DB Snapshot can be
changed
• A DB Snapshot inherits the security constraints of its source
database
– DB snapshots are read-only, inherited permissions cannot be changed
– If you drop a user from the source DB, it is still in the snapshot
• To prevent future access you can drop\disable the login
•
•
•
•
Snapshots cannot be backed up (or restored)
Snapshots cannot be attached (or detached)
Snapshots cannot be created on FAT32 or RAW partitions
Full-text indexing is not supported on Database Snapshots, and fulltext catalogs are not propagated from the source database
Database Snapshot
How much Space Used?
• Size Based on Update Patterns
– View the sparse files (sys.master_files)
– View the Maximum Size Possible
– View the Actual Size
• Select the size column from sys.master_files. The size column in
sys.master_files reflects the maximum space, in SQL pages, that
the snapshot can ever use; this value is equivalent to the Windows
Size field, except that it is represented in terms of the number of
SQL pages in the file; the size in bytes is:
( number_of_pages * 8192)
Database Snapshot
How much Space Used? (Cont)
Use Windows Explorer to
right click on the NTFS
sparse file
'C:\AWDW.SNP' and
select Properties
Look at the “Size” and you
will see it is similar to the
size of the source database
file
However, look at the “Size
on Disk” and you will see
how much space is REALLY
used
Demo
High Availability Solutions
Overview
• SQL Server provides several options for
creating high availability for a server or
database.
– Failover clustering
– Database mirroring
– Log shipping
– Replication
Database Mirroring
•
•
•
•
•
New for SQL Server 2005
Instant Stand-By
Fault Tolerant Virtual Database
Very fast Failover (3 sec)– No Data-Loss
Automatic, Transparent Client-Redirect
Automatic Re-Sync after Failover
Witness
Client
Client
Principal
Mirror
Database Mirroring
Overview of Database Mirror
• Mirroring allows the user to create an
exact copy of a database on a different
server.
• The mirrored database must reside on
different instance of SQL Server Database
engine.
Database Mirroring
SQL Server 2005 RTM ,SP1,SP2
• Microsoft fully supports database mirroring with SQL
Server 2005 SP1,SP2 onwards.
• For the RTM release (prior to SP1), Microsoft support
services will not support databases or applications that
use database mirroring. The database mirroring feature
should not be used in production environments. Prior to
SP1, database mirroring is disabled by default, but can
be enabled for evaluation purposes by using trace flag
1400. The following T-SQL statement can be used to
achieve this:
DBCC TRACEON(1400)
Transparent Client Redirect
• No changes to application code
• Client automatically redirected if
session is dropped
• MDAC is aware of Principal and Mirror servers
– Upon initial connect to Principal, MDAC caches
Mirror name
– When client attempts to reconnect
• If Principal is available, connects
• If not, MDAC automatically redirects connection to Mirror
Database Mirroring
Synchronous Mode
•
This is also called as high safety mode.
–
•
•
In this mode, every transaction applied to the
principal will also be committed on the mirror server.
The transaction on the principal will be
released only when it is also committed on the
mirror.
High safety mode with/without automatic
failover
Database Mirroring
Asynchronous Mode
• This is also known as the high performance mode.
– Here performance is achieved at the cost of availability.
• In this mode, the principal server sends log information
to the mirror server, without waiting for an
acknowledgement from the mirror server.
• This mode allows the principal server to run with
minimum transactional latency and does not allow the
user to use automatic failover
Failover Solutions
Clustering and Mirroring Compared
• Both Provide
–
–
–
–
•
Automatic Detection and Failover
Manual Failover
Transparent Client Redirect
Zero Work Loss
Failover Clustering
–
–
–
–
–
System scope
Certified hardware
Fast failover
No reporting on standby
Single copy of database
•
Database Mirroring
–
–
–
–
–
Database scope
Standard servers
Fastest failover
Limited reporting on standby
Duplicate copy of database
Demo
Resources & Questions
• Microsoft Resources:
- msdn.microsoft.com/sqlserver/
- www.microsoft.com/sql/community
• Contact me:
- [email protected]
• Download Presentation :
- www.sqlmvp.com