Free Online SQL Server Training for the Week of November 3, 2013

I’m trying to re-establish this running guide to free online training for the following week. If you’re a training provider and I’ve missed you, please drop me a line at brian {dot} kelley {at} sqlpass {dot} org.

All times are Eastern (New York). To convert to your local time, use the converter at timeanddate.com.

Tuesday, Nov 5:

Thursday, Nov 7:

Training Providers I Regularly Review:

Advertisements

Is There Interest in SQL Server Security Pre-Cons?

I’m very passionate about security, especially database security. As the numbers with regards to data breaches continue to climb, this is become even more important to me. I’ve been affected personally by data breaches, as have many others. For instance, I’ve had to change out my credit cards due to payment processor breaches, I was affected by the South Carolina Department of Revenue breach, and I’m sure there are others, maybe ones that weren’t reported, that I was affected by as well.

I enjoy speaking on security, but a 45-75 minute session barely scratches the surface when it comes to any security topic. I’d love to spend more time covering more information, diving down into more details, etc., for those wanting to learn. The best way to do that is probably through a pre-con at a SQL Saturday.

I’ve given half day and full day sessions before, primarily to IT auditors, and I’m thinking the same sort of time length is appropriate to convey enough information to make it worth paying for. Here’s a sample agenda from one of my half day courses to give you an idea of what I’m thinking about (this is from a SQL 2000-2008R2 class):

  • Hour 1
    • General Security Principles We Follow
    • Hardening OS and SQL Server – Surface Area
    • Access SQL Server and its Databases
  • Hour 2
    • SQL Server’s Permission Model
    • Changes from SQL Server 2000 to 2005
  • Hour 3
    • Auditing using Triggers
    • Server Side Traces
    • Audit Object
  • Hour 4
    • Built-In Encryption Options

If you think you’d be interested in such a pre-con, especially if you’re a SQL Saturday organizer, comment on this post or, better, send me an email at brian {dot} kelley {at} sqlpass {dot} org (note the spelling on the last name as kelly will cause it to bounce).

SQL Injection Webcast on November 5, 2013

On November 5th, in conjunction with MSSQLTips, I’ll be giving a webinar on SQL Injection. It will be at 2 PM Eastern (New York).

SQL Injection: What it is, how it happens and how to stop it?   [registration required]

The agenda for this webinar is:

  • Who Is the Enemy?
  • What Is SQL Injection?
  • Is SQL Injection Still an Issue?
  • SQL Injection in Action
  • Prevention Methods

Review: SQL Server Transaction Log Management

SQLServerTransactionLogManagementBook Details:

SQL Server Transaction Log Management
Davis, Tony and Shaw, Gail
Simple Talk Publishing, October 2012.

Free PDF download

Do I Recommend This Book?

Yes, I recommend this book for any DBA working with Microsoft SQL Server. Gail and Tony do an excellent job of covering how Microsoft SQL Server uses the transaction log for a database. There are plenty of code examples to reveal the behavior they describe. In addition, they provide plenty of references to other sources which reinforce or expand upon what they cover in the book.

What I Liked:

There’s a lot about this book I liked, so let me pick out the highlights.

Easy Reading Style:

Some technical books are hard to read. There’s a lot of jargon and a lot of assumptions are made as to the technical proficiency of the reader. Others have called this the Curse of Knowledge. Gail and Tony don’t have this issue. They make the book readable to a junior DBA level.

Extensive Code Examples:

Gail and Tony provide code examples for just about every behavior they describe. None of them were very long, but all were effective. Sometimes code samples are too long to reasonably type in. However, most of us learn best from learning, so typing in code is helpful. That means the code samples have to be reasonably small. They were in this book as I typed in nearly every example and then tinkered with them to see the behavior described. There was one example where there looks there was a printing/editing error, Listing 7.1), but the rest worked as long as I didn’t mistype. If you don’t feel like typing the code in, there is a provided download (Listing 7.1 is correct in the download).

Coverage of Bulk-Logged Recovery Mode:

This book had an extensive amount of coverage on the bulk-logged recovery mode. Gail and Tony did a great job explaining why this mode exists as well as the pros and cons of using it. If you don’t get this book for any other reason, get it to review what you know about bulk-logged recovery mode.

What I Didn’t Like:

There are only a couple of things I didn’t like.

Images Were Designed for an e-Book:

Some of the images used colors/shading that don’t show up well in a printed black & white book. Also, there were some references to shading in green and yellow. As a result, these particular images were hard to read, especially in chapter 2.

Oversight on Differential Backups:

Differential backups establish or re-establish a log chain with respect to future transaction log backups. However, most of the writing focused on full backups. The first time I noted this was in chapter 1, in the section titled Transaction Log Backup and Restore. Later chapters sometimes mentioned differential backups, but it was hit or miss.

 

Note: I was provided a free copy of this book for review.

Architecture Reasons for Putting BLOBs in the Database

I was participating in a Twitter Chat looking at what suggestions and recommendations for developer on how to make the best use of SQL Server. One of the points that came up was about BLOBs (Binary Large OBjects) within the database. The general recommendation is to keep BLOBs out of the DB. I brought up SharePoint as a counter-argument to the recommendation. Then folks starting joking on SharePoint. SharePoint does a lot of things that makes DBAs and System Administrators/Active Directory Administrators tear their hair out. However, I’m sure SharePoint includes BLOBs in the DB for a good reason or two.

Let me first state that I don’t like BLOBs in the database. I like metadata in the database with the files stored somewhere else. The app reads the metadata and then goes and accesses the file(s) based on that metadata. Why do I like this?

  • It keeps the database small. This is good for performance and for my backup/restore headaches.
  • An RDBMS is not a file system. It is not optimized for such.
  • Things like implementing antivirus becomes much, much harder.

So why would a system like SharePoint implement BLOBs in the database? I can see several reasons.

Backups: It’s Hard to Synchronize Between the Database and the File System:

If you have two separate systems, synchronization usually poses a challenge. When you have systems that are very different, both in type AND size, synchronization is extremely challenging. Consider trying to take a backup of the file system for all the files stored in a document management system like SharePoint. During the time you’re doing the backup, more files could show up. Now, consider the database. If it only contains metadata, it’s likely to backup much, much faster. So its backup gets done faster, meaning it will miss those incoming files. The files are on the file system but there is no metadata in the database. Now we have inconsistency between the two systems.

How do we generally solve this situation? We solve it by taking a backup of the file system first, then the database. This should ensure that you minimize the likelihood of a file existing and the metadata not being there. However, consider if you’ve got different systems backing up the DB versus the file system. How do you synchronize those systems so that the file system fully completes before the database backup kicks off? It may not be so easy to coordinate those backups.

Restore: Making Sure You Have the Right Set of Backups

Now you have to keep the pair matched up. Worst case scenario, if you don’t have a matched set, is to pick an older database backup compared to the file system backup. You’ll have files you don’t have metadata to, but at least everything within the app works.

There are additional procedures if a coordination is required. That’s additional work and potential confusion. If you find you don’t have a matched pair, it’s even more work. Recovery situations can be (and usually are) stressful. Disaster recovery situations are even more so. These additional procedures will likely increase the time to recover. It also means your documentation for recovery have to be detailed and complete. They should be already, but even more so with this type of architecture.

Backups: What about during the day?

A lot of organizations don’t run file system backups all day long. They usually pick a point during the night and that’s when the backups kick off. If that isn’t sufficient for recovery purposes, then you’re looking at modifying file system backup processes to take into account the new application. Databases, on the other hand, tend to be backed up more frequently. If all the files are in the database, I just have to leverage what I’m likely already doing for other databases. Yes, the databases and, therefore, the backups will be larger (and slower), but likely I have more infrastructure around handling database backups more frequently than once a day. I can leverage that existing infrastructure without having to build new infrastructure and design new processes.

Application Use: What about document versioning?

Products like SharePoint provide document versioning. We see this in source control as well as history. Windows Server has versioning using Shadow Copy, but the number of copies you have is determined by the number of snapshots you have. You can probably think about how difficult things just became to ensure you can pull old versions of documents back up in the application.

So what if I use BLOBs? Things become signficantly easier. I don’t have to worry about operating system configurations, taking snapshots (Shadow Copy meaning), and I don’t have to worry about losing a version when a snapshot is deleted to make room for a newer one.

Application Use: Moving to a Different System

As soon as I involve Shadow Copy and snapshots for versioning, I complicate the ability to move the files to a new system. When would this come up?

  • When I detect my file system has a fault that’s going to get bring the system down.
  • When I need bigger hardware.
  • When I want to move to a different environment or a different location.

If it’s all in the database, I can restore the database backup and I’ve got it all. That makes things a whole lot simpler and easier.

I know there are counter-arguments for every reason I’ve presented. My point in presenting these architecture reasons is because they do weigh in to whether for a given system we put BLOBs in the database or not. A consideration of the pros and cons for a given system is important to make the right choice. There isn’t a black and white answer, as with some other design choices.