This blog post explains the demo scripts and databases I've posted to cover all the Corruption Survival Techniques and DBCC CHECKDB sessions I've presented at conferences this year. There are two zip files you need to download: the example corrupt databases (36-MB zip) and the demo scripts. These are also both available through our past events pages.

The databases zip contains the following databases:

• DemoDataPurity
  • 192-MB SQL Server 2005 database with a single 2570 (data purity) error
• DemoFatalCorruption1
  • 1-MB SQL Server 2005 database with a corrupt system table (that allows CHECKDB to complete)
• DemoFatalCorruption2
  • 1-MB SQL Server 2005 database with a corrupt system table (that terminates CHECKDB)
• DemoNCIndex
  • 192-MB SQL Server 2005 database with a bunch of nonclustered index corruptions
• DemoRestoreOrRepair
  • 1-MB SQL Server 2005 database with a page checksum failure (in fact a zero'd out page)
• DemoCorruptMetadata
  • 1-MB SQL Server *2000* database with corrupt syscolumns table

The scripts zip contains the following directories:

• 1 - Fatal Errors
  • This makes use of the DemoFatalCorruption1 and DemoFatalCorruption2 databases. The FatalErrors.sql script has the steps to follow and see this blog post for a complete walk-through.
• 2 - NC Indexes
  • This makes use of the DemoNCIndex database. The NCIndexCorruption.sql script has the steps to follow and see this blog post for a complete walk-through.
• 3 - Data Purity
  • This makes use of the DemoDataPurity database. The DataPurityCorruption.sql script has the steps to follow. I'll blog more details on this script sometime in the next week (and then update this post).
• 4 - Metadata
  • This makes use of the DemoCorruptMetadata database. The CorruptMetadata.sql script has the steps to follow and see this blog post for a complete walk-through.
• 5 - Restore or Repair
  • This makes use of the DemoRestoreOrRepair database. There's a setup script (01CreateRestoreOrRepair.sql) and two demo scripts - FixUsingPageRestore.sql and FixUsingRepair.sql. I'll blog more details on these scripts sometime in the next week (and then update this post).
• 6 - Suspect Database
  • This demo doesn't come with a corrupt database - you create your own one. The SuspectDatabase.sql script has the steps to follow and see this blog post for a complete walk-through.

Enjoy!

Well, we're here in England now to teach the first of our UK classes and we're staying with our good friend Tony Rogerson (UK SQL Server Community founder and SQLBits co-organizer) and his great family once again - I was beaten at tennis on the Wii by their 5-year old son last night...

It's too late to sign-up for our class this week but we do have some spaces left for the class in Edinburgh September 8^th-9^th. We (mostly Kimberly this time) will be teaching a two-day intensive workshop on Indexing for Performance in SQL Server 2000/2005/2008. Indexing is Kimberly's hot-topic and I'll be sitting in the audience to learn from her. Checkout the full agenda and registration details here.

Hope to see you there!

In conjunction with our Iceland partners Miracle, we're offering three BI classes in Iceland in September, taught by Stacia Misner. The three classes are as follows:

• September 17^th-19^th: Expanding Your SQL Server 2005 Business Intelligence Skills, Part 1
  • Abstract: Deeper than traditional introductory courses for SQL Server 2005 business intelligence technologies, this course teaches students how and when to implement advanced techniques, how to troubleshoot solution performance, and how to secure solutions properly. Part I of this course focuses on Integration Services and Reporting Services. By the end of this three-day course, students will understand how to create flexible, reusable Integration Services packages, improve Integration Services package management by tracking of package activity with auditing and logging of errors and execution details, find and correct Integration Services performance bottlenecks, secure Integration Services packages in production, solve challenging report design problems, create better report models to support  ad hoc reporting, use MDX and DMX queries effectively with Reporting Services, and troubleshoot performance and security issues with Reporting Services
  • For more details and for registration info, see here.
• September 22^nd-23^rd: Expanding Your SQL Server 2005 Business Intelligence Skills, Part 2
  • Abstract: Part II of this course is focused exclusively on Analysis Services. By the end of this two-day course, students will understand how to use Analysis Services management features to create a secure, available environment, find and fix the root cause of Analysis Services performance issues, create more efficient MDX calculations and queries, and leverage data mining technologies.
  • For more details and for registration info, see here.
• September 24^th: Using Excel 2007 with Analysis Services 2005
  • Abstract: Whether you regularly analyze data now or provide technical support for those who do, this class teaches you how take business intelligence to the next level using Excel 2007 and Analysis Services 2005. This course includes an introduction to PivotTable fundamentals, and builds on these concepts to more advanced analytical techniques to explore and visualize data. In addition, this course covers working with OLAP data in a free-form format using CUBE functions. This course will also review the use of data mining to find the hidden information in your business using the Data Mining Add-Ins for Excel 2007. By the end of this one-day course, students will understand how to create and format a PivotTable,  use a PivotTable to explore data by drilling, isolating and eliminating data, or launching actions, use visualization features to summarize PivotTable data for easier identification of trends and exceptions, and use Data Mining Add-Ins for Excel 2007 to simplify investigative and predictive analysis.
  • For more details and for registration info, see here.

All classes will be presented in Reykjavik.

Fresh off a week of teaching classes on the Microsoft campus, we've finalized some user group dates. Here's what we have coming up:

• Monday August 18^th: user group meeting in Redmond
  • .NET Developer's Association Database Meeting at the Microsoft Redmond Campus
  • Topic: Index Internals and Maintenance
• Monday September 1^st to 3^rd: public class in England
  • In conjunction with our UK partners, SQL Know How at Hatfield, England
  • Topic: Best Practices in Performance and Availability in SQL Server 2005/2008 
• Thursday September 4^th: user group meeting in Ireland
  • SQL Server Ireland User Group at the Dublin Microsoft office
  • Topic: Index Internals and Maintenance
• Friday September 5^th: SQL Server launch event in Ireland
  • In conjunction with Microsoft Ireland at the Dublin Microsoft office
  • Topic: SQL Server 2008 Overview for DBAs
• Monday September 8^th to 9^th: public class in Scotland
  • In conjunction with our UK partners, SQL Know How at Edinburgh, Scotland
  • Topic: Indexing for Performance in SQL Server 2000/2005/2008

It's going to be a busy few weeks - hope to see you at one of these events!

September, October, and November are going to be a whirlwind this year - after 3 weeks in the UK and Ireland in September, teaching and the San Francisco Power Workshop in October, we have three back-to-back conference weeks in Barcelona, Las Vegas, and back to Seattle! Hey - who booked that schedule?!?!?! Well, at least it helps us keep our top frequent-flyer status on United :-)

Here's the line-up - see our Upcoming Events page for all the abstracts (including those from Bob Beauchemin and Stacia Misner too).

TechEd EMEA IT Pro, November 3-7, Barcelona, Spain

• We're still working with the TechEd team to finalize the content we'll be delivering but it's looking like the same three sessions from TechEd US, plus a bunch of new ones and Instructor-Led-Labs
• Sessions (at least):
  • Are Your Indexing Strategies Working?
  • Corruption Survival Techniques: From Detection to Recovery
  • Essential Database Maintenance

SQL Server Connections Fall, November 9-14, Las Vegas, USA

• This is the second of the twice-yearly SQL Connections conferences that Kimberly and I Co-Chair
• Workshops:
  • November 9: Pre-pre-con: Database Best Practices for the Involuntary DBA
  • November 10: Pre-con: Relational Data Warehousing: Leveraging Key Features of SQL Server 2005/2008
• Sessions:
  • Index Internals and Usage
  • Essential Database Maintenance
  • DBCC CHECKDB: The Definitive Guide
  • Follow the Rabbit: Interactive Q&A on Database Maintenance

PASS Community Summit 2008, November 17-21, Seattle, USA

• As unbelievable as this may be, I've *never* been to PASS before, even in the few times it was in Seattle - something always conflicted. Kimberly hasn't been since 2005 so it'll be cool for us both to be there this year.
• Workshop:
  • November 18: Database Maintenance: From Planning to Practice to Post-Mortem
• Spotlight Session:
  • Corruption Survival Techniques: From Detection to Recovery

People have been complaining that I've stopped blogging so much - vacation folks, vacation! Today I've got a few class and conference posts to get through and then I'll get back to the technical posts.

We're doing a 2.5 day public class based on the SQL Server 2008 material we developed earlier this year. This will be part of a larger conference being hosted by Dev Connections in San Francisco, USA. Our workshop will run October 6^th through 8^th.

You can register and get more details at http://www.devconnections.com/SFWorkshops/default.asp?s=127.

Here's the abstract:

SQL Server 2008 offers an impressive array of capabilities for professional developers that build upon key innovations introduced in SQL Server 2005. The use of many of these will have manageability and infrastructure implications for a database—and hence the DBA! There are also enhancements to existing high-availability technologies, plus a variety of significant new tools to aid in managing performance, scalability, administration, and troubleshooting. This workshop helps you understand how to exploit the new toolset and how to manage a database that makes use of the new features in SQL Server 2008. The multi-day format of this event allows us to explore each feature in more detail, with more in-depth demonstrations and labs.

Topics covered include:

• Availability Enhancements
  • Database Mirroring
  • Backup Compression
  • Peer-to-Peer Replication
• Security Enhancements
  • Transparent Data Encryption
  • Extensible Key Management
  • All Actions Audited
• Policy-Based Management and Multi-Server Administration
• Troubleshooting and Throttling
  • Resource Governor
  • Extended Events
• New Development Technologies
  • Spatial Indexes
  • Sparse Columns
  • Filtered Indexes and Statistics
  • Change Tracking and Change Data Capture
  • FILESTREAM
• Performance Data Collection
• Scalability Enhancements
  • Data Compression
  • Partition-Level Lock Escalation

This workshop runs Oct 6 (9am - 4pm), Oct 7 (9am - 4pm), Oct 8 (9am - 12pm).

As well as the usual round of conferences later this year, we've also organized some public classes in the UK after lots of requests. In between these two classes we'll be hopping over to Dublin to do a launch seminar for Microsoft on SQL Server 2008 - more details on that as they become available.

The UK classes are organized with our UK partners SQLKnowHow.com. We haven't taught in the UK since a one-day seminar we did with Tony Rogerson (one of the founders of SQLKnowHow) back in March last year so this is pretty exciting (and the Edinburgh class will be at my old alma-mater, The University of Edinburgh). The complete line-up is below - register now to avoid disappointment as the classes are filling up fast.

Best Practices in Performance and Availability for SQL Server 2005/2008

• When: 1^st to 3^rd September, 2008
• Where: Hatfield, Hertfordshire
• Who: Paul and Kimberly
• How much: See here for details, discounts, and early-bird specials

• What:

  This class has three primary goals (for almost all topics/modules): planning, practice/implementation and post-mortem - with the largest emphasis on designing/implementing the RIGHT solution. Questions that you must ask are: How do you choose technologies to fit requirements and effectively use key features of SQL Server 2005/2008? How does your technology/choice affect workload performance?

  Only after an in-depth plan is developed should you move on to actual implementation. So what are the areas that you need to consider?

  • Architecting for Availability
  • Architecting for Performance
  • Maintaining Performance and Availability

  And just to be clear, this is not a high-level class on planning. This is an intense, in-depth class encompassing structures, internals, technologies and solutions. Planning is a critical part of performance, high-availability, database maintenance and disaster recovery - but the most-often disregarded.

  Performance tuning spans many areas within SQL Server from database creation to database design to the code you execute (ad-hoc or procedural). A single magic bullet does not exist (indexing is the closest thing to a magic bullet for some queries). However, to achieve a truly scalable and reliable database it takes a variety of best practices - from database creation (including file structure and placement) to table design and creation (using vertical and horizontal partitioning techniques) to system architecture (including disaster recovery planning and implementation) to ongoing maintenance. Whether you're trying to achieve high performance for a few users or scale to support thousands, there are numerous areas that you can tune to improve performance - proactively. But, how do you make this a reality?

  SQL Server 2005 and 2008 provide a variety of options to help keep your database more available. However, even in the event of a disaster, are you sure you know the best path for recovery - with the least amount of downtime and/or data loss? Putting a well-thought out plan into practice requires a thorough understanding of the technologies, their pitfalls and the effects of many technologies when combined. In terms of architecture, we will start by discussing the most important part of designing an available solution - requirements. Then we'll show how to use requirements to drive a technology decision - not the other way around, which happens so often and results in an inadequate implementation.

  No matter how much effort you spend on the design of your database, if you don't maintain it in production then it will suffer from performance and manageability problems - and possibly data loss and/or downtime. The key to availability and performance is well thought-out and automated database maintenance. The final part of the course will discuss maintenance strategies required to keep your carefully designed system available and performing well, plus a primer on recovering from disasters.

  If you're planning, or already manage, an enterprise system and want better performance and availability - then this is the place to be!

  Module List:

  1. Foundations - SQL Server structures and algorithms
  2. Architecting for Availability
  3. Architecting for Performance
  4. Maintaining Performance and Availability

Indexing for Performance in SQL Server 200/2005/2008

• When: 8^th to 9^th September, 2008
• Where: Edinburgh
• Who: Paul and Kimberly
• How much: See here for details, discounts, and early-bird specials

• What:

  There are many areas of performance tuning in SQL Server: database design, application design, hardware/software configuration, and many more. But none are as important as indexing. Creating the "right indexes" is the most important thing you can do for performance and scalability. Is proper indexing something your application is missing? Do you realize the impact of your clustering key; forcing your base structure of your tables to be either ordered or unordered. If ordered is chosen, by what type of column(s) should the data be ordered? Is the decision solely based on query performance or are there other factors?

  Whether your system is 24x7 or a small system just trying to setup for future growth and improved performance this course is for you! We will cover the often-overlooked impacts of poorly chosen clustered indexes, where/why clustered indexes help the most and how the type of table and the type/frequency of your queries affect your decisions. Additionally, once the internals, statistics and base table structures have been defined, we will talk about indexing strategies for search arguments (including SQL Server 2008 Filtered Indexes), joins, aggregations and appropriate uses for indexed views. Finally, we'll discuss index maintenance as well as how to evaluate your indexing strategy over time to make sure it remains appropriate as your data and workload changes.

  If you want better performance and excellent insight into the wide range of indexing strategies - as well as how things work internally, this is the place to be!

  Course Modules

  1. Index Internals
  2. Statistics
  3. Indexing Strategies, Part I: SARGs and Joins
  4. Indexing Strategies, Part II: Aggregations and Indexed Views
  5. Index Maintenance
  6. Is Your Indexing Strategy Working?