Implementing Database Snapshot & Database Mirroring in SQL Server 2005

1
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

2
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

3
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
4
9
10
dbSnap Read-Only Database Snapshot
USE dbSnap SELECT (pages 4, 6, 9, 10, 14)
4
Snapshot (Copy on Write)
Northwind
Northwind_SS
Command
Create Northwind_SS
Update Northwind
Read Northwind_SS
Result
D
D
Space Used
0
12.5
5
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.
6
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

7
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 full-text catalogs are not
  propagated from the source database

8
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)

9
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
10

• Demo

11
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

12
Database MirroringNew 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

Client
Client
Principal
13
Database MirroringOverview 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.

14
Database MirroringSQL 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)

15
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

16
Database MirroringSynchronous 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

17
Database MirroringAsynchronous 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

18
Failover SolutionsClustering and Mirroring
Compared

• Both Provide
• Automatic Detection and Failover
• Manual Failover
• Transparent Client Redirect
• Zero Work Loss

• Database Mirroring
• Database scope
• Standard servers
• Fastest failover
• Limited reporting on standby
• Duplicate copy of database

• Failover Clustering
• System scope
• Certified hardware
• Fast failover
• No reporting on standby
• Single copy of database

19

• Demo

20
Resources Questions

• Microsoft Resources
• - msdn.microsoft.com/sqlserver/
• - www.microsoft.com/sql/community
• Contact me
• - tghazali_at_sqlmvp.com
• Download Presentation
• - www.sqlmvp.com