Understanding and Handling Database Corruption

 

About the Presentation

This presentation was formerly titled “DBA 911 – Database Corruption.”

Database CorruptionDatabase corruption is simply a fact of a DBA’s life – if you haven’t seen it yet, you will. Success or failure depends on having a solid plan to deal with different types of corruption. In this session, we will discuss usage of related DBCC commands such as DBINFO, CHECKDB and PAGE. You’ll learn how to monitor for corruption with SQL Server Agent Alerts and Jobs, and when it’s time to restore instead of repair. After attending this session, you will be able to check for issues such as page corruption or data purity problems, and take the appropriate measures to deal with them.

Session Level: Beginner

View Webinar & Download Presentation

Download contains:

  • Webinar Recording – 60 Minutes
  • PowerPoint Presentation
  • Scripts

Understanding and Handling Database Corruption

Presentations (Upcoming & Past)

 

Want to Learn More About SQL Server Disaster Recovery?

If you'd like to learn more about how UpSearch can support your SQL Server disaster recovery initiative, visit SQL Server Disaster Recovery or contact us today.

About the Author

Data Platform Consultant

David Maxwell

UpSearch Alum David Maxwell is a database strategist, community evangelist and public speaker. He is passionate about helping community and business leaders get the most out of SQL Server.

Since 2000, David has served to protect, unlock and optimize data's value within such diverse environments as healthcare providers, insurance companies, manufacturers and financial institutions.  David has the unique ability to zero in on a complex challenge quickly, and provide a long-term solution that fits both the business needs and budget.

Since 2012, David has maintained a SQL Server focused blog at https://dmmaxwell.wordpress.com/. He is a frequent presenter at local and regional events for the Professional Association for SQL Server (PASS) and and works with the Columbus, OH based PASS chapter.

In his free time, David is an avid musician who plays several instruments, as well as a lover of puzzle-based games.

Learn more about David Maxwell at https://upsearch.com/david-maxwell/.

About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.

The Usual SQL Server SUSPECT: When Good Databases Go Bad

 

About the Presentation

The Usual SQL Server SUSPECT: When Good Databases Go BadYou just finished patching your SQL Server. After rebooting it, though, some of your databases aren’t coming online. What do you do? In this session we’ll cover some of the various states that a SQL Server database can be in. We’ll look at useful states like OFFLINE and STANDBY, as well as damaged states like SQL Server SUSPECT or RECOVERY_PENDING. You’ll learn how databases get into those states, and the proper techniques to bring your data safely back online.

Session Level: Beginner

Download the Presentation

The Usual SQL Server SUSPECT: When Good Databases Go Bad

Presentations (Upcoming & Past)

 

Want to Learn More About SQL Server Disaster Recovery?

If you'd like to learn more about how UpSearch can support your SQL Server disaster recovery initiative, visit SQL Server Disaster Recovery or contact us today.

About the Author

Data Platform Consultant

David Maxwell

UpSearch Alum David Maxwell is a database strategist, community evangelist and public speaker. He is passionate about helping community and business leaders get the most out of SQL Server.

Since 2000, David has served to protect, unlock and optimize data's value within such diverse environments as healthcare providers, insurance companies, manufacturers and financial institutions.  David has the unique ability to zero in on a complex challenge quickly, and provide a long-term solution that fits both the business needs and budget.

Since 2012, David has maintained a SQL Server focused blog at https://dmmaxwell.wordpress.com/. He is a frequent presenter at local and regional events for the Professional Association for SQL Server (PASS) and and works with the Columbus, OH based PASS chapter.

In his free time, David is an avid musician who plays several instruments, as well as a lover of puzzle-based games.

Learn more about David Maxwell at https://upsearch.com/david-maxwell/.

About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.

Managing SQL Server Availability Groups

 

About the Presentation

Managing SQL Server Availability GroupsHave you built SQL Server Availability Groups only to be suddenly surprised by all the gotchas and hurdles that pop up? Are you struggling with keeping your Availability Group running smoothly without a lot of management on your part? This session will cover what happens after you have created an Availability Group and how to manage it. We will review tips, tricks, and patterns for handling all the considerations that running this popular high availability solution. After this session, you will be prepared for managing your Availability Groups and how you can streamline that management to make your availability strategy more robust.

Session Level: Intermediate

Download the Presentation

Managing SQL Server Availability Groups

Presentations (Upcoming & Past)

 

Want to Learn More About SQL Server Disaster Recovery?

If you'd like to learn more about how UpSearch can support your SQL Server disaster recovery initiative, visit SQL Server Disaster Recovery or contact us today.

About the Author

SQL Server Consultant

Michael Fal

Michael S. Fal is an UpSearch Alum and Microsoft Data Platform MVP. He is a specialist in data management technologies. As a community advocate, public speaker, and blogger, Mike is a practicing thought leader for data and automation. He is passionate about DevOps and data, building platforms to optimize, protect, and use data efficiently.

Mike has been working with databases for more than 15 years. He continues to blog at http://mikefal.net since 2011, writing about a wide range of topics that center around data management. He is an active member, and former board member, of the Denver SQL Server User Group as well as a volunteer for the Professional Association for SQL Server (PASS).

Learn more about Michael Fal at https://upsearch.com/michael-fal/.

About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.

Transactional SQL Server Replication Toolbox Scripts

Transactional SQL Server Replication Toolbox Scripts

Originally published on KendalVanDyke.com.

During the last few years I’ve worked extensively with transactional replication and have written a handful of scripts that have found a permanent home in my “useful scripts” toolbox. I’ve provided these scripts as downloads whenever I’ve presented about replication…but not everyone who has worked with replication has been to one of my presentations (or had access to the downloads afterwards) so I’m posting them in this Transactional SQL Server Replication Toolbox Scripts series.

The first script in my toolbox shows all of the articles and columns in each article for all transactional publications in a published database. It’s pretty straightforward – just execute the script in the published database on the publisher. Note that because it uses the FOR XML PATH directive it must be run on SQL 2005 or higher.

/********************************************************************************************* 
Transactional SQL Server Replication Toolbox Scripts: Show Articles and Columns for All Publications 

Description: 
   Shows articles and columns for each article for all transactional publications 

   (C) 2013, Kendal Van Dyke (mailto:kendal.vandyke@gmail.com) 

Version History: 
   v1.00 (2013-01-29) 

License: 
   This query is free to download and use for personal, educational, and internal 
   corporate purposes, provided that this header is preserved. Redistribution or sale 
   of this query, in whole or in part, is prohibited without the author's express 
   written consent. 

Note: 
   Execute this query in the published database on the PUBLISHER 

   Because this query uses FOR XML PATH('') it requires SQL 2005 or higher 
   
*********************************************************************************************/ 

SELECT 
   syspublications.name AS "Publication", 
   sysarticles.name AS "Article", 
   STUFF( 
       ( 
           SELECT ', ' + syscolumns.name AS [text()] 
           FROM sysarticlecolumns WITH (NOLOCK) 
               INNER JOIN syscolumns WITH (NOLOCK) ON sysarticlecolumns.colid = syscolumns.colorder 
           WHERE sysarticlecolumns.artid = sysarticles.artid 
               AND sysarticles.objid = syscolumns.id 
           ORDER BY syscolumns.colorder 
           FOR XML PATH('') 
       ), 1, 2, '' 
   ) AS "Columns" FROM syspublications WITH (NOLOCK) 
   INNER JOIN sysarticles WITH (NOLOCK) ON syspublications.pubid = sysarticles.pubid 
ORDER BY syspublications.name, sysarticles.name

 

About the Author

Microsoft SQL Server MVP & Principal Consultant

Kendal Van Dyke

UpSearch Alum Kendal Van Dyke is a database strategist, community advocate, public speaker and blogger. He is passionate about helping leaders use Microsoft's SQL Server to solve complex problems that protect, unlock and optimize data's value.

Since 1999, Kendal has specialized in SQL Server database management solutions and provided IT strategy consulting. Kendal excels at disaster recovery, high availability planning/implementation and debugging/troubleshooting mission critical SQL Server environments.

Kendal Van Dyke served the SQL Server community as Founder and President of MagicPass, the Orlando, FL based chapter of the Professional Association for SQL Server (PASS). In 2012, Kendal served as a member of the PASS Board of Directors.

Kendal remains active in the SQL Server community as a speaker and blogger. He teaches SQL Server enthusiast and technology leaders how to protect, unlock and optimize data’s value. Since 2008, Kendal has operated a SQL Server focused blog at http://www.kendalvandyke.com/.

Microsoft acknowledged Kendal for his support and outstanding contributions to the SQL Server community by awarding him Microsoft MVP (2011-15). Learn more about Kendal Van Dyke https://upsearch.com/kendal-van-dyke/.

About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.

Implementing a SANLess Cluster in Under an Hour

 

About the Presentation

Implementing a SANLess Cluster in Under an Hour

Kendal Van Dyke demonstrates how to Implement a SANless Cluster in Under an Hour using SIOS DataKeeper™ Cluster Edition.  No shared storage, SAN, or SAN Admin required and operations were not disrupted.

Session Level: Intermediate

 

“We recommended SIOS DataKeeper software because it provided the only solution that could enable our client to move from a production environment to a fully supported Windows Server Failover Cluster – and to do so without disruption to business operations that rely on SQL Server.”

Kendal Van Dyke

Learn how UpSearch used SIOS DataKeeper™ to provide a simple, easy way to add High Availability Protection for SQL Server with an AlwaysOn Failover Cluster.

Download the Presentation

Implementing a SANLess Cluster in Under an Hour

Presentations (Upcoming & Past)

 

Want to Learn More About SQL Server Disaster Recovery?

If you'd like to learn more about how UpSearch can support your SQL Server disaster recovery initiative, visit SQL Server Disaster Recovery or contact us today.

About the Author

Microsoft SQL Server MVP & Principal Consultant

Kendal Van Dyke

UpSearch Alum Kendal Van Dyke is a database strategist, community advocate, public speaker and blogger. He is passionate about helping leaders use Microsoft's SQL Server to solve complex problems that protect, unlock and optimize data's value.

Since 1999, Kendal has specialized in SQL Server database management solutions and provided IT strategy consulting. Kendal excels at disaster recovery, high availability planning/implementation and debugging/troubleshooting mission critical SQL Server environments.

Kendal Van Dyke served the SQL Server community as Founder and President of MagicPass, the Orlando, FL based chapter of the Professional Association for SQL Server (PASS). In 2012, Kendal served as a member of the PASS Board of Directors.

Kendal remains active in the SQL Server community as a speaker and blogger. He teaches SQL Server enthusiast and technology leaders how to protect, unlock and optimize data’s value. Since 2008, Kendal has operated a SQL Server focused blog at http://www.kendalvandyke.com/.

Microsoft acknowledged Kendal for his support and outstanding contributions to the SQL Server community by awarding him Microsoft MVP (2011-15). Learn more about Kendal Van Dyke https://upsearch.com/kendal-van-dyke/.

About SIOS


Implementing a SANLess Cluster in Under an Hour
SIOS delivers innovative software solutions that provide application availability and disaster protection for Windows and Linux environments.

About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.

Factor Encrypted Data Requirements into your Disaster Recovery Plan

Backups are probably the first, most important thing a DBA can do for his company’s data. Without backups, any number of problems can cause data loss, and significant data loss can be devastating to a company. Of course, without testing those backups, they’re just bits on a disk, and may or not be useful. Testing those backups regularly allows the DBA the practice of restoring so the steps are ingrained when they’re needed for a real recovery, plus it ensures that the backups themselves are good.

Sensitive data in the databases, of course, needs to be encrypted, so that only authorized users have access to that data. We’ve seen too many cases of the wrong people getting access to personal information that leads to fraud and identity theft, so encrypting that data is also critical to a company’s success.

Testing restores of database backups, with encrypted data in the database, becomes a bit more complicated, and it’s important to remember a few things before attempting to restore from backups with encrypted data in SQL Server.

First, and this is really important, back up the service master key and database master key for each database which uses SQL Server encryption. It’s not hard – here are the steps:

use master
GO
BACKUP SERVICE MASTER KEY FROM FILE = 'C:\MyDirectory\ServerSMK.key' ENCRYPTION BY PASSWORD = 'UD58ss6r'
GO
use MyDatabase
GO
BACKUP MASTER KEY FROM FILE = 'C:\MyDirectory\MyDatabaseMK.key'
ENCRYPTION BY PASSWORD = 'UD58ss6r'
GO

Copy these files to somewhere safe, where you know you’ll be able to find them should you find you need to recover the data in these databases.
Now, should you find that you need to recover the database, you can restore the database from backup, then issue the following commands to restore full access to the encrypted data:

use master
GO
RESTORE SERVICE MASTER KEY FROM FILE = 'C:\MyDirectory\ServerSMK.key' DECRYPTION BY PASSWORD = 'UD58ss6r'
GO
use MyDatabase
GO
RESTORE MASTER KEY FROM FILE = 'C:\MyDirectory\MyDatabaseMK.key'
DECRYPTION BY PASSWORD = 'UD58ss6r'
ENCRYPTION BY PASSWORD = 'ccH4QvQCp8Ry6nYSsVxZ5oU'
FORCE
GO

Disaster recovery without testing doesn’t amount to much, so it’s a good exercise for many reasons.

Want to Learn More About SQL Server Disaster Recovery?

If you'd like to learn more about how UpSearch can support your SQL Server disaster recovery initiative, visit SQL Server Disaster Recovery or contact us today.

About the Author

Microsoft SQL Server MVP and Practice Leader

Allen White

Allen White is an UpSearch Alum and Microsoft SQL Server MVP.  For over 30 years, Allen has specialized in developing applications that manage the movement of data and maximize data's usefulness. Allen excels at communicating highly technical information using language that results in increased client engagement and understanding, regardless of technical competency.

Allen has been working with relational database systems for over 20 years. He has architected database solutions in application areas like retail point-of-sale (POS), POS audit, loss prevention, logistics, school district information management, purchasing and asset inventory and runtime analytics. Allen thrives on providing comprehensive solutions to information management problems across a great variety of application environments.

About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.