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