1 / 41

Please don’t do this to your SQL Server Databases

Please don’t do this to your SQL Server Databases. Michael Epprecht IT Pro Evangelist Microsoft Corporation michael.epprecht@microsoft.com. Please note: The Demos will be recorded later and the link will be published on the Swiss IT Pro Blog. http://blogs.technet.com/chitpro-en.

portia
Download Presentation

Please don’t do this to your SQL Server Databases

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.

E N D

Presentation Transcript

  1. Please don’t do this to your SQL Server Databases Michael Epprecht IT Pro Evangelist Microsoft Corporation michael.epprecht@microsoft.com

  2. Please note: The Demos will be recorded later and the link will be published on the Swiss IT Pro Blog. http://blogs.technet.com/chitpro-en

  3. If you don’t know about it….does it exist?

  4. Corruption • Corruption does happen, mostly caused by IO subsystem problems • People don’t realize they have corruption until too late • Either they don’t know how to check for corruption or they miss the warning signs • People don’t know what to do when they do have corruption, leading to: • More data loss and downtime than necessary • Monetary and even job losses • Overall lowered perception of SQL Server • Makes it harder to convince management that SQL Server is Enterprise capable • If using SQL Server 2008 and Database Mirroring, Primary can get bad page from Partner if it is OK on the Partner.

  5. Corruption – I/O Errors • Three types • 823 (a hard I/O error) • 824 (a soft I/O error) • 825 (a read-retry error) • Nice error messages in 2005+ Msg 824, Level 24, State 2, Line 1 SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x7232c940; actual: 0x720e4940). It occurred during a read of page (1:143) in database ID 8 at offset 0x0000000011e000 in file 'c:\sqlDB\CORRUPT.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online. • Logged in msdb.dbo.suspect_pages • Input into single-page restore operations

  6. Page Protection • SQL Server allows pages to be ‘protected’ on disk from corruptions • Allows fast detection of corruptions • Set using • ALTER DATABASE SET PAGE_VERIFY <option> • Three options: • NONE  • TORN_PAGE_DETECTION  • CHECKSUM 

  7. Page Protection – Torn Page • SQL Server 6.0 onwards • Possible for a page to partially written in the event of a power failure – i.e. a torn page • Torn-page detection protects SQL Server against this • Takes two-bits from each sector, stores them in the page header and writes an alternating bit pattern in each sector • On subsequent read, if the pattern is disrupted, the page is torn • Does not detect corruptions within a disk sector

  8. Page Protection - Checksums • New in SQL Server 2005 • Per-page checksum • Written as the very last thing SQL Server does on a physical write • Checked as the very first thing SQL Server does on a physical read • Provides the ‘smoking gun’ that the error is not due to SQL Server • On by default for new databases in SQL Server 2005+ • Checksum failures result in an 824 error • CPU overhead of approx 2% • Error-detecting, not error correcting • Superset of torn-page detection

  9. Page Protection – Checksums (2) • Checked when: • Page is read normally • Page is read during CHECKDB • Page is read during BACKUP WITH CHECKSUM • Page is contained within a checksum’d backup • Be Aware: • Not available for TEMPDB until SQL Server 2008 • Switching it on doesn’t do anything until pages are written…

  10. DBCC CHECKDB • The only way to read all allocated pages in the database • Use to force page checksums to be checked • Choose between full checks and WITH PHYSICAL_ONLY • Many algorithms to minimize runtime and run ONLINE since SQL Server 2000 • Run it at least weekly on each database

  11. DBCC CHECKDB • By default, CHECKDB will: • Only return the first 200 errors • Return lots of info that’s distracting in a corruption situation • Use the following command with only these options: • DBCC CHECKDB (yourdb) WITH ALL_ERRORMSGS, NO_INFOMSGS • If it’s taking longer than usual, that should mean that it found some corruption • Check the error log for message 5268 from SQL Server 2005 SP2 onwards to see if it’s rescanning some data • Most importantly, wait for it to complete

  12. Things That People Often Try *First*…and fail • Restart SQL Server • Just wastes time and delays getting back online • Immediately jump to a last resort and cause data loss without working through options • Running repair • Rebuilding the transaction log • Detach a suspect database • It will fail to attach again – now the situation is even worse! • This is the 2nd worst state to be in

  13. If it never happened before….will it happen in future?

  14. Disaster Recovery • Have a documented plan • Test the plan • Practice the plan with everyone in the team • Do you now how to restore your system databases too? Have you tried it? • Have Microsoft Support number handy

  15. Backup and Restore • "Backup Strategy" means nothing...do you have a "Restore Strategy" too? • Test the backups...RESTORE DATABASE....WITH VERIFYONLY to test backup on another server • Keep at least 2 full backups....you never know if you can't restore the last one • Backup Transaction logs very often (10 minutes - 3 hours) • Use Backup Compression where possible • Don't backup to Data or Log drive, get the backup ASAP off the machine • Backup system DB's too • The reason you have backups is so you can restore them…

  16. Recovering from Backups • Best way to avoid data loss • Not necessarily the best way to avoid downtime • Depends what kind of backups are available • Although backup compression in SQL Server 2008 helps… • Plethora of options available • Full database backup is a good starting point • Series of transaction log backups as well is much better • Remember: • Backups have to exist to be useful • Backups have to be valid to avoid data loss

  17. If it is not in the newspapers…did you do something wrong?

  18. Security • Always use Minimum Permissions to do your daily work • Don’t use SA account for anything • Apply the latest hotfix and service pack • If using SQL authentication, use password expiry and password complexity rules • Don't give anyone but the DBA team sysadmin, not even developers • Increase the number of error logs kept • Audit failed access to objects

  19. Security (2) • Disconnect from a SQL Server when you are not using it...mistakes happen if you select the wrong server • Turn on failed login auditing at a minimal • Don't allow applications to use SA or sysadmin role • Use the lowest possible permissions for your application • Use Windows Authentication where possible • Use SQL Server 2008’s Policy Based Management to ensure all rules are adhered to.

  20. SQL Injection • Method to pass rogue SQL statements into SQL Server • Allows a hacker to potentially access the rest of the network, probe the SQL Server or see data • Runs under the authentication of whatever account the application uses • Not just a SQL Server issue!

  21. The Security Stack • Physical Security • Limiting access to the machine itself, backups, and copies of data • Encryption of data files and backups – Transparent Data Encryption • Authentication • Logins – Windows Authentication, SQL Server Authentication • Strong passwords, password expiration policies • Endpoints – restrict connections by protocol, login, etc. • Authorization • Separation of duties • Permissions, users, roles, access through SPs or views only • No direct access to tables • No permissions directly to users; grant to roles and put users in roles • Separation of data • Instances, databases, schemas, views – or perhaps encrypt it with certificates or keys • Principle of least privilege – from service accounts to users • Auditing – tracking who did what when – Built into SQL Server 2008

  22. If nobody notices…is there something wrong?

  23. Monitoring • What • SQL Server Logs • SQL Server Agent Logs • Operating System Logs • SQL Server Agent Jobs • SQL Server Alerts (Severity of 11 and higher should be configured to send alerts. Very Critical are severity 19 (fatal) and higher) • Disk and Database Free Space • Performance

  24. Monitoring (2) • How • System Center Operations Manager • System Center Essentials • Write your own scripts • E-mail Alerts for SQL Server Agent Jobs • In Windows 2008 Server, you can create alerts on OS events that can be sent to you via e-mail. • Management Data Warehouse in SQL Server 2008

  25. If it works…is it good enough?

  26. Performance Testing • Benchmark Disk Performance (SQLIOSim) • Test Server Performance before you go live with a server • Create a small standard benchmark • Use it to compare different servers • Run your benchmark when you think there are performance issues after go-live and compare to pre-go live • Run test as part of HotFix, Cumulative Update or Service Pack validation • Run test after major OS updates, Firmware, BIOS and driver updates.

  27. If you can automate it...do you select all options?

  28. Maintenance Plans • Very useful, but don’t Misuse • Autoshrink is BAD. • Send notifications from within the Plan. Don’t rely on the Job (What if you run the Plan manually)

  29. If you can modify a setting...should you?

  30. Database Settings • Auto Create Statistics: On • Auto Update Statistics: On • Auto Shrink: Off • Page Verify: Checksum • Autogrowth: Use mainly for catching mistakes. File growth should be managed manually. Use fixed amount, not percentage growth. • Production databases should be set to FULL RECOVERY.

  31. If you don’t look after your database...will it still love you?

  32. Indexing • Indexes need to be rebuilt or reorganized regularly to minimize fragmentation and reduce wasted space. • Rebuild an index if it is heavily fragmented (>30%). In Enterprise Edition, can perform online. If Standard Edition, consider it an off-line job. This automatically updates statistics, do you don’t need to do this again. • Reorganize an index if it is not heavily fragmented (>5% and <= 30%). This is an online operation and doesn’t use a lot of resources. You must update statistics afterwards, as this is not automatically done for you. • Ideally, you should only rebuild or reorganize indexes that need rebuilding, especially for very large databases. Use sys.dm_db_index_physical_stats to identify what tables/indexes need to be rebuilt/reorganized. • If databases are small, or you don’t know how to identify and correct individual indexes that are fragmented, then consider running a weekly job to rebuild or reorganize all indexes in all of your user databases.

  33. Did you catch all that...if not remember me...

  34. Summary • Catch Corruption before it catches you • Your backups and plans are worthless, unless you have tested them, recently. The reason you have backups is so you can restore them. • Secure your platform and data before someone plublishes it on the Internet for you • Automate, but don’t forget about your servers, let them tell you when they are sick • Test your server before you let it loose • Look after your databases, they are all you have

  35. Please note: The Demos will be recorded later and the link will be published on the Swiss IT Pro Blog. http://blogs.technet.com/chitpro-en

  36. Swiss IT Pro Blog: http://blogs.technet.com/chitpro-en Schweizer IT Pro Blog: http://blogs.technet.com/chitpro-de Schweizer TechNet: http://technet.microsoft.com/de-ch/

  37. Your MSDN resourcescheck out these websites, blogs & more! PresentationsTechDays: www.techdays.chMSDN Events: http://www.microsoft.com/switzerland/msdn/de/presentationfinder.mspxMSDN Webcasts: http://www.microsoft.com/switzerland/msdn/de/finder/default.mspx MSDN EventsMSDN Events: http://www.microsoft.com/switzerland/msdn/de/events/default.mspxSave the date: Tech•Ed 2009 Europe, 9-13 November 2009, Berlin MSDN Flash (our by weekly newsletter)Subscribe: http://www.microsoft.com/switzerland/msdn/de/flash.mspx MSDN Team BlogRSS: http://blogs.msdn.com/swiss_dpe_team/Default.aspx Developer User Groups & CommunitiesMobile Devices: http://www.pocketpc.ch/Microsoft Solutions User Group Switzerland: www.msugs.ch.NET Managed User Group of Switzerland: www.dotmugs.chFoxPro User Group Switzerland: www.fugs.ch

  38. Your TechNet resourcescheck out these websites, blogs & more! PresentationsTechDays: www.techdays.ch TechNet EventsTechNet Events: http://technet.microsoft.com/de-ch/bb291010.aspx Save the date: Tech•Ed 2009 Europe, 9-13 November 2009, Berlin TechNet Flash (our by weekly newsletter)Subscribe: http://technet.microsoft.com/de-ch/bb898852.aspx Schweizer IT Professional und TechNet BlogRSS: http://blogs.technet.com/chitpro-de/ IT Professional User Groups & CommunitiesSwissITPro User Group: www.swissitpro.chNT Anwendergruppe Schweiz: www.nt-ag.chPASS (Professional Association for SQL Server): www.sqlpass.ch

  39. Save the date for tech·days nextyear! 7. – 8. April 2010Congress Center Basel

  40. Premium Sponsoring Partners Classic Sponsoring Partners Media Partner

More Related