Transcript DAT332 Database Administration For Developers Stephen

DAT332
Database Administration
For Developers
Stephen Wynkoop
SQL Server Worldwide User’s Group
www.SSWUG.org
[email protected]
Agenda
Learn about key features that you
should be aware of
Backup, Restore
Performance Tips
Indexes, Tuning, Cursors
Available Tools
Q&A
Overall Goal
Get in
Get what you need
Get out
NEVER: SELECT * FROM mytable
Be fast
Be recoverable
Data-wise
Backup/recover-wise
Multiple Instance Support
Why is this interesting?
There is typically a default instance
(but not required)
Instances can “talk” to one-another
Instances can be administered
separately
Good for splitting environments,
collation issues, testing, learning
Recovery Models
Yes, it’s your job to work with the DBA
Different Models
Full
Simple
Bulk-logged
Point in time, or “marker” recovery (drop
anchor…)
DO NOT wait until disaster strikes
to find out the wrong measures
are in place
“Backups are not important Restoration/recovery is”
Recovery Models
Database, Properties, Options….
Recovery Model: Full
Full transaction log restore
Restore the baseline db
Restore the transaction logs in order
Restored up to last tran. log backup
Watch the transaction log physical
devices
Beware total reliance on tran. log
Best production environment option
Recovery Model: Simple
Backup/restore at the database level
Good for development environments
Disaster recovery, but not transaction
recovery since the last backup
“All or nothing” restore
Recovery Model:
Bulk_logged
Better for bulk operations
Select into, etc.
Good for a migrating database
If the data exists elsewhere
Can be rebuilt from other source
Good for DTS move operations – control
log size build-up
Restore to last transaction log backup
Re-bulk-operation to get current again
Switch to better model for production
Other Recovery Options
Lumigent Log Explorer
BMC SQL Back-Track
Allow you to potentially recover a dropped
table, a specific transaction, etc.
SQL Server Backup
Backup types/models – know them!
Full (Complete)
Differential
Transaction log
File or file group
File differential
Different impacts on the system
Change based on production versus
test environment
Copy/Move Database Wizard
Uses sp_detach_db/sp_attach_db
Beware: objects, scripting,
dependencies
Test, test, test
Check for tasks that copy, system-level
operations
Check for jobs, logins, etc.
Good for moving to production
Moving A Database To
Production
DTS: If you use it, be cautious and
check your work
Jobs
Views
Don’t forget “system-level objects” –
anything outside the db. Won’t transfer
If you don’t tell it to replace data – it
will append, duplicating your data
Creating SQL Agent Tasks
Use these to automate database
processes
Examples:
Grouping of data
Creating stat summary tables
Resetting counters
SQL Server Agent: select Jobs, New
Document your agent jobs
Creating A Task…
Maintenance Plans
Sets of instructions carried out
regularly
Database backup
Integrity check, etc.
Database Maintenance Plan
Creates a job for the backup
Creates a job for the transaction log
backups
Creates a job for other database maint.
Tasks
Re-org
Shrink
Etc.
Management >> SQL Server Agent >>
Jobs…
Most Important Tabs…
Backup and
Tran. Log
Backup
Warning:
disk files can
get really
large…
Shrinking The Database
Right click on the db
All tasks
Shrink
database…
Can be a big
performance
boost
Can be a big
budget saver!
Traditional ASP Tuning
Put your connection STRINGS in the
global.asa
Application variables (not session)
NOT other variables unless you absolutely
need to
NOT connections themselves – very bad
Remove non-essential info from the
global.asa
Beware SESSION versus Application
Global.asa(x) – Hacker’ s
Dream
Access = access to SQL server
Consider how your application accesses
the db –
Read-only UIDs
Move admin functions – don’t use global for their
security info?
Never multi-database use for an ID
Live behind a physical firewall…
Encrypt passwords/info
Stored Procedures
If you’re using a SELECT over and
over – consider stored procedures
Compiled on the server
Based on data modeling
Optimized
Can be encrypted
Central admin
Fights injection
All DB interaction should go through
an SP if possible
Indexing
EXTREMELY IMPORTANT
If you’re not indexing, you’re not close
to full performance
“Covered” queries
Clustered versus not…
Examine your SELECT statements
Order in the SELECT
Using The Tuning Tools
Captures the SQL statements
by connection
Runs
remotely
Tune to
specific
database
Tuning…
Let profiler work with your workload
Save the workload
Play it back through the
performance wizard
Watch the cursors (resources)
Watch the open connections (memory)
Watch the DB growth
Auto-grow is nice, but…
…Out of disk space!
Index Tuning Wizard
Use the Profiler tool
Capture activity to a file
Re-run that
activity through
the profiler index
tuning wizard
Implement,
review or
document the
suggestions
More Performance Ideas
Select count(*) – Table Scan
Execution plans
How to get them
How to read them
Query Analyzer >> ctrl-K or “results in
grid” drop down – Show Execution Plan
Example Plan
Additional Performance
Tips
SET NOCOUNT ON in stored
procedures
Stops “10 rows affected…”
Query Analyzer – What operation is
faster?
Set up statements to compare
Run with showplan
Shows relative execution times
More Performance Tips
Check for indexes on…
JOINs
Views
Keep statistics updated (esp. after
mass updates) sp_updatestats
Also “auto update statistics” at database
level
Script And Schema Control
Save your scripts offline
Don’t rely on the DBA for your
backups
Version control applied to scripts
Script out the entire db after major
changes
Scripting Objects
Security
Don’t have all developers login with
the same UID
Don’t use the DBA UIDs for developers
Do keep UIDs confidential
Don’t let an application use a
developer/user/dba login
SQL Injection
Can provide alarming access
Select * from myTable where lname=‘”
request.form(“lastname”) & “’”
Form input ’ or ‘a’=‘a
Result: select * from mytable where lname=‘’
or ‘a’=‘a’
How to protect against it
Stored procedures
Watching quotes
Control statement generation
Parameter queries
Best Description:
Don’t let someone else write your code…
- Steve Kass
Other Points To Consider
Web site: session timeout
Defaults to 20 minutes
Web site: HTTP keep-alives
Forces IIS to keep around information on
the connection (memory)
Cursors versus more simple SELECTs
Storing summary information versus
detail for later calculation
Move reporting support to a different
server
Use Available Tools
Query Analyzer
Enterprise Manager
SQL Agent
Scripting Engine
Wizards!
Profiler – use it as first line of
investigation
Import/Export/Move Tools – but with
caution
Online Resources
Sswug.org
SqlServerCentral.com
MSSQLserver.com
Sql-Server-Performance.com
SQLSecurity.com
SQLTeam.com
SQLJunkies.com
Third-Party Tools
Imceda – compressed/faster backups
Idera – monitoring, management tools
BMC – recovery tools
Sonasoft – automated disaster
recovery
Sql Power Tools – low-impact
monitoring
Red-Gate – schema comparison,
synchronization
Please fill out a session evaluation on CommNet
Q1: Overall satisfaction with the session
Q2: Usefulness of the information
Q3: Presenter’s knowledge of the subject
Q4: Presenter’s presentation skills
Q5: Effectiveness of the presentation
Questions…
(be sure to grab a performance tips poster from
The SQL Server booth in the exhibit hall)
? ? ? ? ???? ?
?
?
?
? ? ?? ?
?? ?
?? ?
Stephen Wynkoop
[email protected]