Nov 092016
 

I’m very happy to be presenting for the PASS Security Virtual Chapter on Thursday, November 17, 2016 at 10:00am Pacific!

I’ll be presenting my session entitled SQL Server Encryption Basics. This is an introductory-level session on the encryption capabilities of SQL Server, how they work, and when and where you may want or need to deploy them.

High-profile attacks by hackers have made the news more and more the past few years, and your database is a prized target! Fortunately SQL Server offers many possible layers of protection, one of which is encryption. This session will cover SQL Server’s encryption capabilities, how they work, and what they have to offer. Topics discussed will include certificates, encryption algorithms, backup encryption, transparent database encryption, and column-level encryption. Attend this session and learn how SQL Server can help you hide your data in plain sight!

I’m really happy to be delivering this session, and look forward to seeing you there! You can register at this link:

https://attendee.gotowebinar.com/register/1806816841365466115

 

Sep 092016
 

A while back, I wrote about how SQL Prompt now includes execution warnings and how useful I think that feature is. It’s saved my bacon several times now, including just last week!

I’m now happy to announce that I’ve recorded a demo video of this feature for Redgate’s series of #SuperSQLTips for SQL Prompt. Be sure to check out my video, as well as the rest of the series – there’s some super-helpful stuff in there!

 

Aug 262016
 

You would think a lesson as simple as “always tell the truth”, something that parents teach their children from an early age, wouldn’t be such an issue in adulthood. But of course it is for some. I’ve wanted to write this post for a while, and with the recent media reports about US Olympic Swimmer Ryan Lochte lying about being robbed in Rio de Janeiro, the timing seemed incredibly appropriate.

It’s Pretty Simple

When applying or interviewing for employment, don’t lie. If you do, and you aren’t caught right away, chances are pretty good that sooner or later someone will find out. You either won’t get the job to begin with, or if you’ve already been hired, your tenure may end very quickly. I can’t help but think back to George O’Leary, the Notre Dame Football Coach who was caught falsifying information about both his academic and athletic accomplishments. He resigned five days after his hire was announced, once people started taking a harder look at his qualifications.

A Few Tales

In my years of conducting interviews I’ve had plenty of candidates where things seemed strange or just didn’t add up. Two stories of lying on a résumé stick out in my mind as being especially amazing.

The first was a candidate who wanted to stress that they “lived and breathed SQL Server.” One comment to me during the interview was “I dream in SQL”. Yeah that sounds kinda creepy, but by that point it didn’t even matter, because they put the following line on their résumé:

Regular attendee of Chicago SQL Server User Group meetings

Many interviewers would probably see that as a plus, but not me. I’ve been regularly attending meetings of that user group since 2009, have been part of its leadership for the past few years, and have never seen that person before in my life. I know my memory isn’t perfect though, but fortunately our attendance records are pretty extensive. You see, we meet in a secured office building. Anyone attending our meetings has to RSVP ahead of time, so we can get their name on a list that’s given to building security. If your name isn’t on the list, you don’t get into the building.

Being database people…we keep all that data! I was able to look back several years and prove that this person had never attended a meeting in recent history, and more than likely not at all. This wasn’t the only reason they didn’t make it past the first round of interviews, but it sure made our decision easier.

Then there’s “Dr. Mario”. If you were preparing to interview someone, looking over the résumé and saw an education section that looked like this, what would you think?

Faber College
Bachelor of Science, Biology
Pre-Law / Pre-Med

Adams College
Master of Science, Cell Biology

Cornjerker State University
Medical Doctor

Would you think they’re a doctor, or at least have an M.D. degree? That’s certainly what we thought. About halfway through the interview the candidate casually mentioned “oh yeah, I dropped out of that program after one year.” Then why is it on your résumé? Or if you’re going to put it on there, it should probably be denoted that the degree was not earned. As it was written, the logical assumption would be that the degree was completed.

I’ve interviewed plenty of people mid-way through a degree. All have been very careful to note on their résumé that the degree is “pending”, or to explicitly state an “expected graduation date” or something along those lines. “Dr. Mario” (who, much like the actual Dr. Mario, is not a real doctor) had nothing at all.

Don’t Make It Easy

In short, don’t lie. Even if you aren’t lying, if anything at all makes your interviewer think you aren’t being fully truthful, chances are pretty good that you won’t be hearing back from them. There are plenty of reasons for a job interview to not progress to the next stage of an organization’s hiring process. Some of those reasons are fair; others are not. But if you lie either in person or on paper, or in any way appear to be hiding something, you just made your interviewer’s job way easier. If an organization can’t trust you to present yourself and your qualifications accurately at the earliest part of the employment process, how can they be expected to trust you with their data?

But the responsibility doesn’t lie solely on the candidate here. If you’re the hiring manager, make sure you’re doing your due diligence. Contact references, verify qualifications. Check everything. Don’t blindly assume that just because you see something on a résumé, it is true, and that it means what you think it means.

Aug 012016
 

I’m extremely fortunate to have been selected to speak at PASS Summit, “the world’s largest gathering of SQL Server and BI professionals.” PASS has once again put together a fantastic lineup, and I’m extremely proud to have made the cut. As many have already done, I’d like to share the abstracts I submitted along with the feedback I received.

I submitted a total of 5 general sessions (the maximum allowed), with one being accepted. I will cover each of them here, along with the notes I received from the reviewers.

Supercharging Backups and Restores For Fun and Profit (Accepted)

Level: 300
Track: Enterprise Database Administration & Deployment
Topic: Backup / Restore, Disaster Recovery

Abstract:
Super-fast queries are an essential part of any business process, but speed will never be more important than during a disaster when you need to restore from backup. Come and see how both backups and restores can be tuned just like a query. In this demo-intensive session, we will discuss the different phases of the backup and restore processes, how to tell how long each of them is taking, and which are the easiest to significantly speed up. You just might be surprised how simple it is to achieve dramatic results – cutting your backup and restore times by 75% or more is absolutely possible using the methods covered here.

Preqrequisites:
Attendees should have a solid understanding of SQL Server backup and restore operations.

Goals:

  • Learn tips and tricks for speeding up backup and restore processes and methods for tuning them that can have dramatic results.
  • Understand what happens during backups and restores, and which phases of their execution can have the most time shaved off of them.
  • Learn trace flags that expose extra information about the backup process and how to leverage this knowledge for maximum benefit.

Feedback I Received:

  • Abstract: well written, engaging
    Topic: draws attention
    Subjective: personally never been in a position where an emergency restore could be considered fun, but interested to here when it was
  • I would like to attend this session. The title is eye catching, The experience level is good but those DBA with less experience could attend and learn easily based on the information listed.
  • The outline seems well developed. The goals appear to be interesting for attendees. There appears to be a reasonable amount of live demonstrations in relation to the topic being presented.
  • high level, 75% of demo and minimum slides. And important topic. Very interesting session

My Comments:
I’m very happy this topic was accepted; I’ve presented it at several SQL Saturdays and have been wanting to to it at PASS Summit for several years now. Tuning queries is always seen as a common task and I’ve always thought that tuning backups and restores is a logical progression of that. In response to the first reviewer’s question, I think an emergency restore can be fun when you’re prepared for it. When you’ve practiced your disaster scenarios, have all your scripts ready, and know how long the restore will take, there’s not a whole lot left to be stressed about. As for the “high level” of demos, this is a demo-heavy session. You can only talk about backups for so long before it becomes worth it to actually start doing them. Not to mention it’s very helpful to show the audience how dramatic the results can be with some demos.

 

Good Migrations: Moving Maximum Data with Minimum Impact (Not Accepted)

Level: 300
Track:
Enterprise Database Administration & Deployment
Topic:
Database Maintenance

Abstract:
A database at rest tends to stay at rest, until it needs to move. This session will cover various methods available to migrate a SQL Server database from one location to another. Whether moving to a new storage system, a new server, or even to the cloud, there are a multitude of options available, many of which involve little to no user impact. Lack of SQL Server Enterprise Edition isn’t always a problem – many of these methods work for Standard Edition servers as well. We will discuss how to determine the most appropriate migration option based on your environment’s constraints, the pros and cons of each method, and planning and testing your migration. Come see how moving a multi-terabyte database with only a few minutes of downtime is completely possible.

Preqrequisites:
A good understanding of SQL Server files, filegroups, and index rebuild processes would be helpful.

Goals:

  • Be able to determine which migration method is most appropriate for given uptime requirements and organizational/environmental constraints.
  • Learn how to plan and test a database migration to maximize chances of success long before any queries are run.
  • Understand the many different techniques for moving databases, filegroups, and objects between different servers and/or storage, and the advantages and disadvantages of each.

Feedback I Received:

  • Abstract: Clearly written abstract with well aligned goals.
    Topic:Interesting topic that will attract DBA’s on the operations side of the fence.
    Subjective: I’d attend this session, as it sounds like a great topic.
  • Abstract – Outline is well developed. Level seems a bit high. Goals are well developed
    Topic – Title is good but would like to see if this is for which version of SQL 2012/2014/2016?
    Subjective – Would like to see presentation not only with moving data but imports as well aside from SQL Partitioning. Would like to see more demos but didn’t downgrade for that.
  • Abstract: detailed, compelling
    Topic: relevant, useful
    Subjective rating: interesting
  • Demo % seems to be low for 300 level session

My Comments:
Having worked on a system for many years that has grown more quickly than its storage budget, I’ve had to do a lot of creative things to move data around on-the-fly. This session covers a bunch of those tricks, which as you can imagine, end up being a little more interesting than a simple online index rebuild. I don’t include partitioning because that’s an entirely different topic and could easily take up an entire presentation on its own. As for the low amount of demos (25%), a lot of these operations are rather time-consuming and really wouldn’t fit well into a 75-minute session. I’d love to present this topic at the summit someday; I think attendees would get a lot out of it. Also I’ve yet to see something similar to this on the schedule, so it could definitely be something different.

 

Manage & Visualize Your Application Logs with Logstash & Kibana (Not Accepted)

Level: 200
Track:
Enterprise Database Administration & Deployment
Topic:
Management Tools

Abstract:
The logs kept by Windows, SQL Server, and other applications contain a treasure trove of information about the health and activities of a system. However, as an environment grows in size and complexity, the number of logs quickly starts to become unmanageable. Fortunately there is a group of free open-source tools: Elasticsearch, Logstash, and Kibana, known collectively as the “ELK” stack.

This session will demonstrate how to use Logstash to manage all application and error logs in your environment, regardless of format or operating system. You will learn how to configure Logstash to capture logs from SQL Server or any other system, organize and archive them in real-time with Elasticsearch, and create helpful web-based dashboards in Kibana. Don’t miss this opportunity to unlock the hidden power of all your application logs with the ELK stack!

Preqrequisites:
Attendees would benefit from a general understanding of the SQL Server error log and how it behaves.

Goals:

  • Learn about the components of the ELK stack, what they do, and how they interact with each other.
  • Understand how Logstash works and how to configure it to collect log information from any file format or logging method, using SQL Server error log files as an example.
  • See how to build dashboards in Kibana to quickly visualize errors and warnings across your environment.

Feedback I Received:

  • Abstract: Abstract is clear and well written.
    Topic: Topic is interesting and useful. Not sure if there would be enough demand for this topic.
    Subjective: I would like to attend this session. Seems like a good way to leverage other stacks for ease of admin.
  • Abstract: The outline and details of this abstract are well written!
    Topic: This is very interesting topic
    Subjective: I will attend this session
  • Well developed. I would like to attend this session.

My Comments:

This is absolutely a niche topic so I can understand why it wouldn’t get accepted. Sure sounds like the reviewers thought it was intriguing though. I run my ELK stack in Linux and use it to ingest system and application logs from a wide variety of machines. While this session would be more tailored to monitoring your SQL Server logs, it would also address monitoring virtually any log on any platform. This isn’t really database-centric, and certainly isn’t exclusive to SQL Server. While I think it would be very useful, I absolutely understand why this one didn’t make the cut.

 

Automating Your DBA Checklist with Policy-Based Management (Not Accepted)

Level: 200
Track:
Enterprise Database Administration & Deployment
Topic:
Policy Based Management

Abstract:
Manually reviewing database compliance checklists is an excellent way to ensure that processes are followed consistently, but it is also extremely time-consuming. Let’s automate the process! SQL Server’s Policy-Based Management is a powerful and simple-to-configure feature that can ensure that all of your best practices and data policies are consistently enforced throughout your environment.

Come see how easy it is to make sure all your SQL Servers comply with Microsoft’s recommendations or any other constraints your deployment requires. This session is loaded with demos to show you how to write policies, evaluate them across groups of instances, and even set up automated reporting so you can have a list of non-compliant servers delivered to you. Years after its introduction, Policy-Based Management is still one of SQL Server’s best-kept secrets. Attend this session and learn how to work smarter, not harder, by leveraging Policy-Based Management to simplify your day-to-day tasks!

Preqrequisites:
Attendees should have a basic understanding of SQL Server administration, maintenance processes, and why they are necessary.

Goals:

  • Understand the capabilities of Policy-Based Management and how it can be used to uniformly enforce settings and other aspects of SQL Server.
  • Learn how to author policies, evaluate them both manually and automatically across multiple servers, and configure automated reporting of them using the Enterprise Policy Management Framework.
  • Leave with a checklist of best practices to automate on your servers, as well as knowledge of Microsoft’s included scripts that can help get you started.

Feedback I Received:

  • The outline seems to clearly describe the contents of the presentation. The title appears to reflect the content described in the abstract. The topic and goals should be compelling to attendees. The topic and goals appear to deliver an appropriate amount of material for the time allotted.
  • Abstract: clearly stated, interesting
    Topic: good title
    Subjective: interesting subject, and something I use often
  • good content. It would draw people to attend this session.
  • Very interesting topic, From one perspective is a basic of basics but from another we still need teach how to use PBM.

My Comments:
Policy-Based Management is incredibly useful in that it allows you to easily author “sanity checks” to make sure your databases are in compliance with whatever standards the business decides are necessary. However PBM isn’t really sexy and it’s certainly not that new – it’s had very few changes since it was released along with SQL Server 2008. As one reviewer said “it’s a basic of basics”. It is, but so many systems I see still don’t use it, typically because the DBA isn’t aware of it. From what I can tell, no sessions covering PBM were chosen this year. That’s a shame, because it could help a lot of people. But in an industry where new things always get the most attention, and at a conference with a finite number of presentation slots, it’s understandable why you won’t see any sessions on it.

 

SHA, Right! SQL Server Encryption Basics (Not Accepted)

Level: 200
Track:
Enterprise Database Administration & Deployment
Topic:
Security: Access / Encryption / Auditing / Compliance

Abstract:
High-profile attacks by hackers have made the news more and more the past few years, and your database is a prized target! Fortunately SQL Server offers many possible layers of protection, one of which is encryption. This session will cover SQL Server’s various encryption capabilities, how they work, and their advantages and limitations.

You will learn what certificates are and why they matter, which encryption algorithms are available and which should be used, and how Transparent Database Encryption works and when to enable it. More recent features such as backup encryption and SQL Server 2016 Always Encrypted will also be explained. Restoring servers and recovering data can be thought of as difficult, but they are nothing compared to rebuilding your customers’ trust and repairing your reputation. Attend this session and learn how SQL Server can help you protect your data from prying eyes both inside and outside of your organization.

Preqrequisites:
Attendees should have basic knowledge of SQL Server and a desire to learn about encryption.

Goals:

  • Learn about all the different ways SQL Server can protect your data through encryption.
  • Understand the strengths and weaknesses of each encryption technology, and the scenarios where each would be an appropriate solution.
  • Learn tips for designing databases where security through encryption is a prerequisite, not an afterthought.

Feedback I Received:

  • Encryption. Important and lovely topics. Worth to see it!
  • Abstract: detailed
    Topic: relevant, sql server 2016 is covered
    Subjective rating: interesting
  • OK, I’m in the dark — what is SHA?
  • Abstract – Good detail in abstract. Great opener and strong conclusion.
    Topic – Good goals. Attendees will be interested and seems compelling for attendees even if they don’t know in-depth security or encryption.
    Subjective – This is a great abstract. Session Prerequisites and Level match and since its previously presented the topic should be able to fit within the time frame allowed.
  • Abstract: it’s punny! good topic
    Topic: well written and informative of what will be covered and why
    Subjective: definitely interested in this session
  • Abstract: Great abstract supported by clearly defined goals. Abstract goes into an appropriate level of detail on deliverables.
    Topic:Great topic. Encryption is an ongoing concern and likely to be a solid draw.
    Subjective: I would attend this session Sounds like a great introductory conversation.

My Comments:
All the other sessions I submitted had 3 or 4 pieces of feedback (I’m assuming from 3 or 4 people). This one has 6! Encryption is a hot topic as of late, I wonder if that has something to do with the reviewer interest in this session. This is a rather basic presentation, and while it’s done rather well at several SQL Saturdays, I’m not sure it would be as popular at the summit anyway. Not being chosen kind of solidified my thoughts. Having a few sessions with deeper dives on a more narrow scope would probably be more popular, though I doubt any of those sessions would cover the basics in the depth that I do here.

 

Thanks so much to the members of the Program Committee who volunteered their time to review abstracts. I know they do not have an easy time reviewing or selecting sessions for the schedule. (As a member of the Program Committee for several years now, I can speak from experience.) I value all feedback, and look forward to incorporating it into any future submissions.

Jun 222016
 

 

SQL Server 2016 is upon us, with all the much-anticipated hoopla and sexy new features people have been lusting over in the CTPs for months. It’s always great to see the hype around a new release. But in the circle of (an application’s) life, the arrival of new things often means others are going away for good. Let’s pause for a moment and reflect upon the two features that, as of SQL Server 2016, are no longer with us:

32-bit SQL Server. SQL Server 2016 is 64-bit only. If for whatever reason you’re running on a 32-bit architecture, sadly you’re now out of luck – 2014 is the end of the road. On the bright side, there’s probably some new hardware in your future!

Compatibility Level 90. If you’re using compatibility level for backwards compatibility, the oldest available version in SQL Server 2016 is 100, which corresponds to SQL Server 2008. Compatibility level 90, SQL Server 2005, is no longer an option.

Hopefully these changes didn’t catch anyone off guard. To help better prepare for the removal of features in future versions, Microsoft maintains a list of deprecated features in the next version of SQL Server. Here’s a few highlights of what’s most likely going away in SQL Server vNext:

Backup/Restore WITH PASSWORD. This one has been bad news for a long time. Stop using it years ago! If you didn’t, stop using it TODAY :)

Encryption with the RC4 or RC4_128 Algorithms. Better options have been available for a while, hopefully you’re using them for any new development at this point.

Remote Servers. Architect them out. If you really can’t, use linked servers instead.

SET ROWCOUNT. I doubt this one will ever really go away, but it’s nice to dream. The TOP keyword has been available for quite some time now.

HOLDLOCK table hint (without parenthesis). This one’s easy, put HOLDLOCK in parenthesis. Like this: (HOLDLOCK). See? It’s easy! Start doing it!

Database Safety Bear says: “Don’t delay, start planning for deprecated features TODAY!”