1 / 40

DAT332 Database Administration For Developers

DAT332 Database Administration For Developers. Stephen Wynkoop SQL Server Worldwide User’s Group www.SSWUG.org swynk@sswug.org. Agenda. Learn about key features that you should be aware of Backup, Restore Performance Tips Indexes, Tuning, Cursors Available Tools Q&A. Overall Goal.

Download Presentation

DAT332 Database Administration For Developers

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.


Presentation Transcript

  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

More Related