1 / 18

Microsoft SQL Server Architecture

Microsoft SQL Server Architecture. Tom Hamilton – America ’ s Channel Database CSE. Common SQL Server Versions. SQL Server 2000 SQL Server 2005 Product Overview SQL Server 2008 Product Overview SQL Server 2012 Product Overview. SQL Server Components. Databases

theodore-pallas
Download Presentation

Microsoft SQL Server Architecture

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. MicrosoftSQL Server Architecture Tom Hamilton – America’s Channel Database CSE

  2. Common SQL Server Versions SQL Server 2000 SQL Server 2005 • Product Overview SQL Server 2008 • Product Overview SQL Server 2012 • Product Overview

  3. SQL Server Components Databases Database Files and File Groups Transaction Logs Backup and Recovery Microsoft Clusters Protocols Disaster Recovery

  4. SQL Server Databases System databases • Master • Model • MSDB • Resource • Tempdb User databases

  5. SQL Server Files Binaries Datafiles (.mdf, .ndf) Transaction log files (.ldf) Backup files and snapshot files File groups

  6. SQL Server 2005 New Feature: Data Partitioning Using File Groups Resources: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5partition.asp http://www.microsoft.com/sql/bi/ProjectREAL/default.mspx http://msdn.microsoft.com/en-us/library/ms190787

  7. SQL Server Transaction Log When the end of the logical log reaches the end of the physical log file, the new log records wrap around to the start of the physical log file.

  8. SQL Server Recovery Model Architectures Three Models: • Simple: Truncate occurs on checkpoint (default in SQL2000) • No “roll-forward” capability • Data loss acceptable • Bulk Logged: All operations are logged except bulk operations (BCP, BULK, etc) (DSS environments) • Roll forward capability • Some data loss acceptable • Full: All ops are logged (Default for SQL Server 2005) • Full roll forward/back • Least amount of data loss possible

  9. SQL Server Recovery Model Architectures

  10. SQL Server 2005 New Feature: DB Snapshots – Copy on write Key Points from SQL Books Online (BOL) Intended for reporting to a point in time at mirror site or locally Performance is reduced due to increased I/O on the source database resulting from a copy-on-write operation to the snapshot every time a page is updated. Snapshots of the model, master, and temp databases are prohibited. Specifications of the database snapshot files cannot be changed. Files cannot be dropped from a snapshot. Can’t backup or restore snapshots. Can’t attach or detach snapshots. Can’t clone a snapshot.

  11. Microsoft Cluster Server Implementation Considerations • Hardware • Software • Network

  12. Protocols FCP iSCSI SCSI/NFS? SMB

  13. SQL Server 2005 New Feature: DB Mirroring • Database Failover • Very fast failover – less than 3 seconds • Automatic or manual failover • Works with dissimilar hardware and storage • Sync and async modes supported • More info: http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

  14. Microsoft SQL Server Mirror Vs. SnapMirror

  15. Microsoft SQL Server Mirror Vs. SnapMirror Data transfers Licenses Server-server vs. controller-controller Reversible sync Failover Database only vs. everything Go for win-win

  16. New in SQL Server 2012 Availability Groups • http://msdn.microsoft.com/en-us/library/cc645581.aspx SMB • http://msdn.microsoft.com/en-us/library/hh759341.asp

  17. SQL Server Replication Transactional Replication Merge Replication Snapshot replication

More Related