DAT332 Database Administration For Developers

1. DAT332Database Administration For Developers Stephen Wynkoop SQL Server Worldwide User’s Group www.SSWUG.org swynk@sswug.org

2. Agenda • Learn about key features that you should be aware of • Backup, Restore • Performance Tips • Indexes, Tuning, Cursors • Available Tools • Q&A

3. Overall Goal • Get in • Get what you need • Get out • NEVER: SELECT * FROM mytable • Be fast • Be recoverable • Data-wise • Backup/recover-wise

4. 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

5. 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 measuresare in place “Backups are not important Restoration/recovery is”

6. Recovery Models • Database, Properties, Options….

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

8. 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

9. 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

10. Other Recovery Options • Lumigent Log Explorer • BMC SQL Back-Track Allow you to potentially recover a dropped table, a specific transaction, etc.

11. 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

12. 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

13. 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

14. 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

15. Creating A Task…

16. Maintenance Plans • Sets of instructions carried out regularly • Database backup • Integrity check, etc.

17. 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…

18. Most Important Tabs… • Backup and Tran. Log Backup • Warning:disk files canget reallylarge…

19. Shrinking The Database • Right click on the db • All tasks • Shrink database… • Can be a big performanceboost • Can be a bigbudget saver!

20. 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

21. 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

22. 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

23. 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

24. Using The Tuning Tools • Captures the SQL statements by connection • Runsremotely • Tune tospecificdatabase

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

26. 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

27. 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

28. Example Plan

29. 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

30. 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

31. 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

32. Scripting Objects

33. 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

34. 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

35. 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

36. 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

37. Online Resources • Sswug.org • SqlServerCentral.com • MSSQLserver.com • Sql-Server-Performance.com • SQLSecurity.com • SQLTeam.com • SQLJunkies.com

38. 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

39. 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

40. Questions… (be sure to grab a performance tips poster from The SQL Server booth in the exhibit hall) ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? Stephen Wynkoop swynk@sswug.org