1 / 20

Implementing Database Snapshot & Database Mirroring in SQL Server 2005

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.

Ava
Download Presentation

Implementing Database Snapshot & Database Mirroring in SQL Server 2005

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 Database SnapshotHow it really works CREATE DATABASE dbSnap AS SNAPSHOT OF mydb USE mydb UPDATE (pages 4, 9, 10) mydb – Database Page 1 2 3 4 4 5 6 7 8 9 10 11 12 13 14 15 16 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 12.5% 0%

  5. Database SnapshotHow 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 SnapshotHow 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 SnapshotHow 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. Witness Mirror 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 MirroringSQLServer2005 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@sqlmvp.com • Download Presentation : - www.sqlmvp.com

More Related