New class: IEBISec on Securing Your BI Platform

We’ve added a second brand-new class to our Spring line-up in Chicago: Immersion Event on Securing Your BI Platform.

It’s a 2-day class, taught by MVP and industry expert Stacia Varga, presented on May 3-4, 2018.

By the end of this class, you’ll understand the relationship across the security settings not only in the BI tools, but also the back-end databases and the Windows operating system. As the class examines potential security issues, you’ll learn how to build a security action plan for Integration Services, Analysis Services, and Reporting Services. Is Azure in your environment? The class also covers important aspects of cloud security for a BI solution. Come learn how to preserve and protect your data effectively from the inside out.

Target audience:

  • DBAs who need to understand and mitigate the security risks of the BI components in the data platform
  • BI architects and developers who need to confidently design and build secure BI solutions

The modules are as follows:

  • Introducing Security Principles
  • Reviewing Integration Services Security
  • Reviewing Analysis Services Security
  • Reviewing Reporting Services Security
  • Azure BI Security
  • Building a Security Action Plan

You can read a more detailed curriculum here and all the class registration and logistical details are here.

We hope to see you there!

New class: IEBIStrat on Developing a BI and Analytics Strategy

We’ve added a brand-new class to our Spring line-up in Chicago: Immersion Event on Developing a BI and Analytics Strategy.

It’s a 3-day class, taught by MVP and industry expert Stacia Varga, presented on April 30 – May 2, 2018.

By the end of the class, you’ll have some new ideas and inspiration to get started with your own BI and analytics roadmap and understand which technologies and skills are needed to build a foundation for your organization’s next-generation BI, including:

  • How the pieces fit together to create a modern analytics architecture
  • How to select the tools that best suit your organization’s analytical requirements
  • How to prepare yourself and/or your staff to implement these tools

Target audience:

  • DBAs who need to support a technical infrastructure for BI, analytics, and data science
  • Managers who need a better understanding of how these technologies fit together to manage data as a strategic asset
  • IT professionals with a data background who want to learn how the BI space is evolving and want to prepare for the future

The modules are as follows:

  • Understanding Traditional vs. Modern BI
  • Establishing a Maturity Baseline
  • Ingesting Data
  • Cataloging Data
  • Preparing Data for Analysis
  • Storing Data for Analysis
  • Analyzing Data
  • Publishing Data
  • Consuming Data
  • Preparing Your Roadmap

You can read a more detailed curriculum here and all the class registration and logistical details are here.

We hope to see you there!

SQLskills SQL101: How can corruptions disappear?

As Kimberly blogged about earlier this year, SQLskills has an ongoing initiative to blog about basic topics, which we’re calling SQL101. We’re all blogging about things that we often see done incorrectly, technologies used the wrong way, or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101 blog posts, check out

Every so often I get an email question from a confused DBA: why is it that sometimes corruptions seem to disappear?

The situation is commonly as follows:

  • There is a regular SQL Agent job that runs DBCC CHECKDB
  • One morning the DBA finds that the job failed, reporting corruptions in one of the databases
  • The DBA runs DBCC CHECKDB on that database again, but this time there are no reported corruptions

This can lead the DBA to mistrust DBCC CHECKDB. Remember the SQL Server 2000 days where sometimes DBCC CHECKDB occasionally reported corruptions when there weren’t any? Those days are long gone now: if DBCC CHECKDB reports corruption, then at that time that it ran there was definitely corruption.

Think about what DBCC CHECKDB is doing: it reads and processes all the allocated pages in the database – all the pages that are part of tables and indexes at the time that DBCC CHECKDB runs. It doesn’t check all the pages in the data files; only those that are currently being used. The pages that are not currently allocated to an object cannot be checked as there’s no “page history” maintained. There’s really no way for DBCC CHECKDB to tell if they have ever been used before or not and since they’re not currently allocated there’s no valid page structure on them and no past to verify.

And if your database is still being accessed then the set of allocated pages can change after DBCC CHECKDB runs. A simple example of this occurring is:

  • Nonclustered index X of table Y has some corrupt pages in, which the DBCC CHECKDB (being run by a SQL Agent job) reports
  • Another SQL Agent job runs and performs index maintenance where it rebuilds index X (the rebuild operation always builds a new index and then drops the old index)
  • The DBA runs DBCC CHECKDB manually and there are no corruptions reported in the new index structure

Nonclustered index corruption is the best kind of corruption to have. The rebuild operation rewrote the index to a new set of pages and deallocated the pages that had corruption. When DBCC CHECKDB is run manually, those new pages are not corrupt and the old pages are not checked, as they are no longer in use.

These kind of ‘disappearing’ corruptions are a problem because it’s almost impossible to investigate them further. However, they could indicate a problem with your I/O subsystem. If you find that they’re occurring repeatedly, consider briefly preventing the process that causes the corrupt pages to be deallocated so you can investigate the corruption.

Another cause of disappearing corruptions can be transient I/O subsystem problems, where page reads sometimes fail outright and then succeed after that. Take a look at these blog posts on read-retry and Agent alerts for more information.

And yet one more cause could be that the database is mirrored or is part of an availability group and the page was fixed by automatic page repair before the second DBCC CHECKDB occurred. You can look in the msdb.dbo.suspect_pages table (more details here) for an entry for the broken page(s) with event_type of 4.

Bottom line: From SQL Server 2005 onward, if DBCC CHECKDB reports corruption, then at the time that it ran there definitely was corruption. Make sure you don’t just ignore the problem as next time the corruption occurs, you may not be so ‘lucky’ that it just seemed to disappear.