Tech-Ed Talks - Managing SQL Server for Reluctant DBA

SQL Learning for this week is learning from Session - Managing SQL Server for Reluctant DBA.

Good Session. Notes listed below 

• SQL manages data in 8KB Chunks called pages 

How Data Change occurs

• Transaction log records query in ldf - log data file
• Database Engine identifies pages affected by change
• Load affected pages in server memory (RAM)
• Changes are made and written to disk 

Recovery Mode - Clearing uncommitted transactions, get them committed

SQL Server Backups - Full & Diff backups would truncate transaction logs. Transaction log backup would help max last 15 mins transactions.

Recovery Model - Full (Transaction log is used), Bulk (Bulk load of records might not get recorded, Quicker Recovery - Risk of losing data), Simple

 Indexes

• Indexes - Make it faster to read data.  
• Clustered Index - Governs physical order how data is stored
• Non-Clustered Index – Pointer. Covering Index - Include column in SQL Server
• Index Pros - Faster Retrieval
• Index Cons - Space required for storing index, slow down CRUD operations as indexes need to be updated for every operation

Fragmentation - Occurs when pages become full and data need to be inserted in middle.  Provide proper fill factor based on INSERT/UPDATE/DELETE Operations

Reorganizing and Rebuilding Indexes - Reorg has lower performance impact, this can be online operation. Rebuild would require you to wait until index creation is complete to use it.

DB Best Practices 

• Statistics Auto Create / Update (Auto Create Statistics – Set it TRUE)
• Growth in terms of MB, Not %%, Provide enough file size initially itself

Security Model

• Login (Windows only – Domain users, AD/ SQL Server)
• Login has to be mapped to DB user, predefined database roles or user created
• Every Instance created is a sql server. Every instance can be configured for different security

HA Options 

• Log Shipping – Ships transaction log to another server and re-runs transactions there. Manual failover process
• Database Mirroring –Automated, Keeps complete copy on secondary server. Software solution. Mirroring Database level
• Clustering – Hardware redundancy. Clustering instance level 

Good Refresher!!!!


More Reads

Common Tasks of SQL Server DBA
Index Fragmentation–“If it isn’t broken, don’t fix it”
Buying a Vehicle vs. Buying a Database Platform
Common SQL Server Myths by Paul Randal now in PDF form
Multiple Instances or a Single Instance of SQL Server - Part 1: Counting Instances
Fundamentals: Improving Insert and Update Performance by Dropping Unused Indexes

Happy Learning!!!!

Tech-Ed Talks - SQL Server Upgrade Best Practices & High Availability

SQL Server Upgrade Best Practices & High Availability has always been my Area of Learning (Improvement) since, I had not worked on Clustering and Mirroring Scenarios.

I found below Tech-Ed talks extremely useful from learning perspective

Microsoft SQL Server Upgrade, 2000 - 2005 - 2008: Notes and Best Practices from the Field

Summary of Learning's (Notes)

Upgrade path for SQL 7.0 is SQL 7.0->SQL 2000->SQL 2005 and then 2008

Phases for Migration

• Planning
• Pre Upgrade Tasks
• Upgrade
• Decide to commit upgrade
• Post Upgrade

Slipstream

• Single installation along with service packs

In Place Upgrade Pros

• Faster
• Application connects to same instance after upgrade

In Place Upgrade Cons

• Complex Rollback strategy
• Upgrade all DBs connected to instances

Side by Side Upgrade

• New Installation
• Detach - Copy and Attach Database

Things to consider for Side by Side Upgrade

• Data Sync
• Decide to move for 64 bit / High end Hardware
• Data validation for copied objects

Tools

• Upgrade Advisor
• Assessment and Planning Kit

Clustering Scenario

• Install pre-requisites on passive node (B)
• Manual Failover for Passive Node to become Active (B)
• Install pre-requisites on passive Node (A)
• Start Upgrade with passive node for clustering scenario (A)
• Then Upgrade Active Node (B)
• SQL Server Automatic Failover takes care

This is very good information to get started with migration involving clustering

Database Mirroring

• Software solution which consist of principal and mirror server. All active transactions mirrored to mirror server

Upgrade Involving Database Mirroring, Replication and Failover Clustering is also covered. Migration of Analysis Services is also covered

Bunch of more Best Practices are provided in the tech talk.

SQL Server High Availability: Overview, Considerations, and Solution Guidance

Summary of Notes

• Classifying Applications based on HA requirements
• Types of Failures (Local / Site Level)
• Regional & Geographic Disaster Recovery
• Chossing approach based on SLA, Cost
• Planned Downtime (Sps, Hot fixes, Security Fixes, Rebuilding Indexes)
• Unplanned Downtime (Hardware, Software and power failures)

HA Notes

• Database backup and restore
• Log Shipping
• Database Mirroring (Fastest Failover). Synchronous/ Asynchronous Data movement
• Instance need to start in cluster scenario but in mirroring it is already started and running
• Clustering is at instance level, Mirroring is at DB level
• Mirroring / Log shipping Applying Data Page level
• Replication - Logical, Secondary can have read access
• Replication is at Table level, Flexible to replicate subset of information

For Detailed information please check the session. This is very good session to begin with HA topic.

More Reads


SQL Server 2005 High-Availability Options (Distilled)
Automating Database Migration to Microsoft SQL Server
MVC,MVP and MVVM: A Comparison of Architectural Patterns
Microsoft Silverlight,WCF RIA Services and Your Business Objects

Happy Learning!!!