Microsoft SQL Server Architecture Tom Hamilton Americas Channel
- Slides: 18
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 ¡Database Files and File Groups ¡Transaction Logs ¡Backup and Recovery ¡Microsoft Clusters ¡Protocols ¡Disaster Recovery
SQL Server Databases ¡System databases – – – Master Model MSDB Resource Tempdb ¡User databases
SQL Server Files ¡Binaries ¡Datafiles (. mdf, . ndf) ¡Transaction log files (. ldf) ¡Backup files and snapshot files ¡File groups
SQL Server 2005 New Feature: Data Partitioning Using File Groups Resources: http: //msdn. microsoft. com/library/default. asp? url=/library/en-us/dnsql 90/html/sql 2 k 5 partition. asp http: //www. microsoft. com/sql/bi/Project. REAL/default. mspx http: //msdn. microsoft. com/en-us/library/ms 190787
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.
SQL Server Recovery Model Architectures ¡ Three Models: – Simple: Truncate occurs on checkpoint (default in SQL 2000) ¡ 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
SQL Server Recovery Model Architectures Recovery Model Description Work Loss Exposure Recover to point in time? Simple No log backups Changes since the most recent backup are unprotected Can recover only to the end of a backup Full Requires log backups Normally none Can recover to a specific point in time Bulk logged Requires log backups If the log is damaged or bulk-logged operations occurred since the most recent log backup, changes since the last backup must be redone Point in time is not supported
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-onwrite 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.
Microsoft Cluster Server ¡Implementation Considerations ¡Hardware ¡Software ¡Network
Protocols ¡FCP ¡i. SCSI ¡SCSI/NFS? ¡SMB
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/pro dtechnol/sql/2005/dbmirror. mspx
Microsoft SQL Server Mirror Vs. Snap. Mirror
Microsoft SQL Server Mirror Vs. Snap. Mirror Data transfers Licenses Server-server vs. controller-controller Reversible sync Failover Database only vs. everything Go for win-win
New in SQL Server 2012 ¡Availability Groups – http: //msdn. microsoft. com/en-us/library/cc 645581. aspx ¡SMB – http: //msdn. microsoft. com/en-us/library/hh 759341. asp
SQL Server Replication ¡Transactional Replication ¡Merge Replication ¡Snapshot replication
- Microsoft sql server 2005 analysis services
- Microsoft sql server machine learning services
- Microsoft sql server introduction
- Sql security best practices
- Polybase query service for external data
- Microsoft sql server management studio express 2005
- Sql server parallel data warehouse
- Microsoft sql server migration assistant for oracle
- Microsoft kerberos configuration manager for sql server
- Microsoft sql server raspberry pi
- Sql server 2008 auditing best practices
- Microsoft sql server compact edition
- Sql server always on architecture diagram
- Troubleshooting sql server memory issues
- Ms sql server architecture
- Sql server internals and architecture
- Iis internet information services
- Vmmap heap
- Tomtom go 910 update