Transcript SQL Server Administration

SQL Server
Administration
Click
to edit
Master title style
A List of “Must Do” Items
Click to edit Master subtitle style
2
Agenda
 Objectives
 Server Inventory
 Database Backups
 Table Health
 House Cleaning
 Unsuccessful Login Attempts
3
Agenda (cont’d)
 Stalled Jobs
 Disk Utilization (% Full)
 Being Alerted by SQL Server
 Questions and Answers
4
Objectives
 Keep SQL Running Well
 Prevent Unnecessary Outages
 Ensure the ability to Restore from Backup
Server Inventory
5
 Hardware
– CPU
– Memory
– Disk
• File Placement
 Server Properties
– Operating System
– SQL Server
6
Hardware Inventory Example
SQL Server Hardware Characteristics
Number of CPUs
CPU MHz
Hyperthreading On or Off?
Multiple Core? (Specify Dual, Quad, etc)
Physical RAM Amount
32 Bit or 64 Bit?
Number of Disk Controllers in Server
Type of Disk Controllers in Server
How Many Network Cards Are in Server?
Describe Here
8
3.33
Off
2 - Dual Cores
8 GB
32 Bit
2
Dell 5i
2
What is the Speed of the Network Cards in Server?
Gigabit
Are the Network Cards Hard-Coded for Speed/Duplex?
Are All the Hardware Drivers Up-to-Date?
Is this Physical Server Dedicated to SQL Server?
Duplex
No
Yes
7
Disk and File Inventory Example
Logical Disk
Physical Disk ID
Total Amount of Available Drive Space on each Drive
Total Number of Physical Drives in Each Array
RAID Level of Array Used for SQL Server Databases
Stripe Size
Allocation Unit Size
Location of Operating System
Location of SQL Server Executables
Location of tempdb Database
Location of System Databases
Location of User Databases
Location of Log Files
Speed of Disk Drives
Size of Each Physical Drive in Array
Total size of Logical Disk
C
0
22 GB
2
1
4K
X
X
E
0
110 GB
2
1
64K
F
1
84GB
3
5
64K
64K
X
X
X
10K
146GB
30GB
X
10K
146GB
116GB
15K
73GB
146GB
8
Server Properties
 select @@version
– Microsoft SQL Server 2005 - 9.00.3233.00
(Intel X86)
Mar 6 2008 22:09:47
Copyright (c) 1988-2005 Microsoft
Corporation Standard Edition on Windows
NT 5.1 (Build 2600: Service Pack 2)
9
Operating System Version Guide
Operating System
Version
Windows 2000
NT 5.0.2195 Extended Support until July 13, 2010
Windows Me
4.90.3000
Windows XP
Windows XP 64-bit Edition 2003
Windows Server 2003
Windows XP Professional x64
Edition
Comments
Unsupported
Current for SP2 and SP3 (RTM and SP1
NT 5.1.2600 unsupported).
NT 5.2.3790 Unsupported
Current for SP1, R2, SP2 (RTM
NT 5.2.3790 unsupported).
Windows Vista
NT 5.2.3790 Current
Current. Version Changed to NT
NT 6.0.6000 6.0.6001 with SP1 (February 4th 08)
Windows Home Server
NT 5.2.4500 Current
Windows Server 2008
NT 6.0.6001 Current
Other Methods
10
 select serverproperty('Edition')
– Standard Edition
 select serverproperty('MachineName')
– XPCURNUTT
 select serverproperty('ProductVersion')
– 9.00.3233.00
 select serverproperty('ProductLevel')
– SP2
11
Server Inventory (cont’d)
 File Folder Structure Dependencies
 DSNs
 Boot.ini
– /PAE
– /3GB
– /USERVA
 Software and DLLs
 Mapped Drives
12
Server Inventory (cont’d)
 SQL Server Configuration Settings
– select comment, value from
sys.syscurconfigs order by comment
 Database Settings
– Sp_dboption ‘dbname’
SQL Server & DB Settings
Change Examples
13
SQL Server Configuration Settings
Show Advanced Options
Max Degree of Parallelism
Max Server Memory
Min Server Memory
AWE
Database Name
TMW_Test
TMW_Test
TMW_Test
TMW
Describe Here
On
4
7
7
On
Database Settings
AutoShrink
Max Degree of Parallelism
Recovery Model
Torn Page Detection
Describe Here
On
4
Simple
On
14
Database Backups
 Type of Backups
– Full
– Differential
– Transaction Log
 Databases to Backup
– System Databases
– User Databases
Full Backups
15
 Nightly
 Not to the same disk as the SQL Data or
Logs
– Performance
– Safety
 3 days readily available (onsite)
 Weekly copy offsite for 12 weeks
 Monthly copy offsite for months 4-12
16
Full Backups
 Verify backup when finished
 Monthly restoration test
 Methods
– 3rd Party Utilities (Backup Exec, Litespeed)
– Scheduled SQL Maintenance Plan
• By default “daisy chained” if doing multiple DBs in
one job
– TSQL Script + Scheduled Job
• tmw_dba_backup
Full Backups
17
 System Databases
• No need to do model unless you have altered it.
• No need to do tempdb.
– Master
– Msdb
 User Databases
• Development DBs should not be on Production
Servers
• Test DBs should not require backing up
– Production DBs
18
Full Recovery Mode
 This means all transactions are being
recorded in an audit trail (transaction log)
 You can restore to any point and time
between the last successful backup and
the last successful transaction log backup
– “crawl forward”
 Production DBs, Model
19
Simple Recovery Mode
 Also known as “Truncate on Checkpoint”
 This means once a transaction is
complete, any record of what took place is
discarded
 Keeps Transaction Log Size small
 Can only restore from the last full backup
 Master, MSDB, Tempdb, Dev & Test DBs
20
Differential or Tran Log Backups
 Only possible when database is in Full
Recovery mode
 Must have already completed a Full
Backup
 Chain together during restore of DB to get
as close to failure/problem as possible
21
Differential Backup
 Differential Backup
– All changes since last Full Backup
22
Transaction Log Backup
 All changes since one of the following
– The last Full Backup OR
– The last Differential Backup OR
– The last Transaction Log Backup
 The act of completing a transaction log
backup “clears out” the transaction log
and prevents it from growing excessively
large
Restore from Backup Example
23
12am
6am
7am
8am
9am
10am
11am
12pm
1pm
2pm
3pm
4pm
5pm
6pm
Full Differential Transaction
Backup Backup Log Backup
30GB
40MB
200MB
200MB
200MB
200MB
200MB
1GB
200MB
200MB
200MB
8MB
Stops Table Accidentally
Dropped at 3:06pm
Take a Final MANUAL Transaction
Log Backup -- “The Tail of the Log”
Full
Backup
Diff
Backup
Tran
Logs
Specify Restore to 3:04pm! 
24
Table Health - Fragmentation
 Intelligent Defrag Run Nightly
– Reorganize Indexes with between 10% and
29% Logical Fragmentation
– Rebuild Indexes with between 30% and
100% Logical Fragmentation
– Use tmw_dba_ALTER_INDEX
 Maintenance Plans
– Defrag ALL indexes regardless of state
– Databases usually “daisy changed” – failure in
1 leads to failure in all subsequent
Table Health - Statistics
25



Tells SQL about the distribution of data in a table
Maintenance Plan uses SQL Default sample rate
Use TSQL to alter overall sample rate. I prefer
25%
– Exec tmw_dba_update_statistics


Run Daily if Maintenance Window permits
SQL 2005 is much better about this with Large
tables (than SQL 2000)
Table Health - Corruption
26

Checks for “Torn Pages”
– Power Outages
– Disk Problems


SQL Server Maintenance Plan is sufficient
Also have TSQL available
– Exec tmw_dba_dbcc_checkdb


Run for every database EVERY NIGHT
Consider any errors or warnings that are reported
as urgent and needing immediate attention!
House Cleaning
27
 Job History
– MSDB Tables
 Database Mail History
– Only required if sending attachments via
Database Mail
 Old Backups
– Database Backup Files (.bak)
– Transaction Log Backup Files (.trn)
 Log Files (.txt)
28
Unsuccessful Login Attempts
 Detects a person or process attempting to
repeatedly login by guessing a password
 Can set thresholds of qty per Min or Hour
or failed logins before an alert is sent to
the admin
 Doesn’t come w/SQL, custom script
– tmw_dba_check_for_failed_logins
 Should run every minute
Stalled Jobs
29
 Detects any job that has been running for
longer than 4 hours (adjustable)
 A stalled job doesn’t “FAIL”, so an alert
isn’t fired
 Doesn’t come w/SQL, custom script
– Exec tmw_dba_long_running_jobs
 Should run every hour
30
Disk Utilization (% Full)
 Can check ANY machine on the network,
even Non SQL Servers
 Set bottom limits for MB for each disk /
each machine
 Doesn’t come w/SQL, custom script
– Exec tmw_dba_disk_space_check
 Should run every 15 minutes
31
Being Alerted by SQL Server
 SQL 2000 (could be difficult)
– MAPI
– Send_cdosysmail
 SQL 2005 (much easier!)
– Enable Database Mail
– Create an Operator
– Create a Database Mail Profile
 Create a Notification on any Job with
Failure
32
SQL Server Consulting Services
 Performance – Basic Assessment
 Performance – Full Assessment
 Maintenance Plan Review
 Hardware Selection
 Architectural Design
 Environment Upgrade
 Predictive Performance (Trace Replay)
33
Question and Answer Time
Contact Info
Mindy Curnutt
Senior Database Architect
TMW Systems, Inc.
216.831.6606 x 2819
214.923.7419
[email protected]
Ron Kost
Sr. System Analyst
TMW Systems, Inc.
216.831.6606 x 2224
[email protected]
For more information:
Visit the TMW booth in the Vendor Hall