"No one is harder on a talented person than the person themselves" - Linda Wilkinson ; "Trust your guts and don't follow the herd" ; "Validate direction not destination" ;

August 27, 2011

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 - 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!!!!

No comments: