Voice of the DBA

Category: Editorial

  • How Virtualized?

    I went to a talk recently where I saw this statistic: “50% of all workloads were virtualized in 2009. That number is 72% today.”

    That’s a really big number, at least in my mind. That implies the vast majority of all servers, file, print, database, email, etc. are virtualized. Inside of companies that have their own data centers and machines, they must be heavily virtualized. I’m sure that all those instances in the “cloud” also count, but still, 72%? That’s big.

    However I’m sure that’s skewed towards those machines that don’t require a lot of resources, like file and print servers, DNS hosts, etc. This week, I thought I’d see what the percentage is inside of your organization.

    What percentage of your SQL Servers are virtualized?

    Give us numbers of physical v virtual if you can. I’d combine all instances, from development to test to production, not worrying about size or workload. If you have a single guest on a host, using almost all the resources, that’s a virtual server.

    My suspicion is that the percentage of SQL Servers is much lower than that of other workloads, but I’m curious. With the low overhead of modern hypervisors, and the free (or low) cost, it makes sense to virtualize servers. If for no other reason than to remove any weird hardware dependencies for DR purposes. However I’m sure that there are large workloads that require more resources than the current hypervisors can expose, at least for some database instances, and those need to remain on physical machines, but my guess is more often than not, it’s the human concerns or lack of confidence that prevents virtualization.

    Let us know this week how your organization is doing in the trend towards virtual servers.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.4MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • vNext 2016

    We have a name for the next version of SQL Server: SQL Server 2016. I suspect the internal build may be v14, which I guess makes sense if you’re superstitious about the number 13, or you think your customers are. There are plenty of v13s out there for various other software packages, so I’m not sure if this is an issue, but we’ll see when we get a public CTP for the next version of SQL Server.

    vNext has been the way to refer to the next version of Microsoft products before a decision is announced about the official name. This convention seems to have taken over from code names, which makes sense, but it certainly is less fun than hearing Denali, Yukon, or some of the names that have been used for SQL Server. 

    Perhaps this is a sign of maturity within Microsoft? It certainly could be as releasing a new version of SQL Server every two years doesn’t happen without some discipline and rigorous engineering effort. Despite the issues with SQL Server 2014 SP1, I do think Microsoft has done a nice job taking a formal approach to software engineering with SQL Server.

    I am a bit more interested in SQL Server 2016 than I was in SQL Server 2014. Perhaps that’s just the fact that some significant changes might take longer than two years to implement and a number of them are coming in SQL Server 2016. I certainly looked forward to SQL Server 2008 and SQL Server 2012 more than the “R2” and 2014 releases. It has seemed that every other release has more significant enhancements and includes more reasons for me to consider upgrading. I think SQL Server 2016 is one of those releases.

    The datasheet shows a number of features that I Think make this a compelling release. I worry about security and the Always Encrypted idea seems very interesting. We’ll see how it looks once we get details. I like the improvements to AlwaysOn as well as the changes to include JSON and R support into the database engine. Row Level Security is in Azure now, but I think that’s important in the boxed product, and, of course, the Query Store is something I’ve been looking forward to for years. If you don’t know about that, read about it now. I was hoping to link to Conor Cunningham’s SQL Bits session, but I’m guessing recording wasn’t allowed.

    There’s more coming, including the “Stretch to Azure” feature, which I’m not sure about. I’d feel better about “copy to Azure”, but perhaps I can keep local filegroup backups of this data, so that might make sense. I’m sure many of you won’t like some features, but this does seem like a compelling release. Depending on how pricing and licensing might change.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.5MB) podcast or subscribe to the feed at iTunes and LibSyn. feed

  • Idempotent

    I ran into the word idempotent in the Stairway to Integration Services. I had heard the word, but I hadn’t really considered how important it can be for a DBA or developer until that time. It’s a term used in computer science, as well as other sciences, but I think it’s one that many of us don’t consider when we’re writing code, especially code used to deploy software to other systems.

    Most of us have written scripts like this:

    if exists (select object_id from sys.objects where name = 'uspGetSales')
  drop procedure uspGetSales;
create procedure uspGetSales
as
...

    At the end of running this code, we have the system in a state. If we run this over and over, we’ll regularly return to the same state, which is often exactly what we want to occur. That works great when deploying code, but what about this:

    insert into states select 'CO', 'Colorado';

    If I run that multiple times, what will happen? Either I’ll get errors if one of these fields is a PK, or I’ll get multiple inserts. If I’m running this as part of a software deployment, do I want either of those conditions? Do I want my end user to experience either one?

    No, I don’t. Certainly if I’m manually making changes to systems I can probably avoid issues, but that’s not what I want to do. Maybe you do, but I don’t. I’d like to be able to restart my deployment if something fails, without causing other issues. I’d like idempotent code, like this:

    if not exists (select abbrev from states where abbrev = 'CO')
  insert into states select 'CO', 'Colorado';

    That way if I happen to run this code twice, or ten times, I arrive in the same place.

    I realize that building scripts and deployment processes that are idempotent is a pain. It’s work, but it’s also scriptable and repeatable work that is easy to automate over time with a few patterns. I also realize that a little extra work to prevent issues is often an investment that’s worth making for both my customers, and my reputation.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.1MB) podcast or subscribe to the feed at iTunes and LibSyn. feed

  • Can Auditing Fail?

    I saw someone’s list of requirements for a SQL Server auditing system recently that included many of the standard items most of us would want. DDL changes should be captured, along with configuration alterations. The performance impact should be low, and all versions of SQL Server must be supported. However, there was one requirement that surprised me. This person noted that the auditing must be asynchronous and the application should not be dependent on the auditing. If auditing failed, the application shouldn’t have any of its transactions limited or rolled back.

    I’m sure there are use cases where this is important, and where the auditing might not be critical, but the auditing data is available for informational purposes to troubleshoot any issues that relate to the database. However in many places where auditors review information, or the data is part of a legal record, the auditing cannot fail. If it does, then the application needs to stop working. If an audit is truly an audit of activity, then all activity must be audited.

    The C2 audit mode in SQL Server has been deprecated. Probably for multiple reasons, but It did seem that the idea that a failure in auditing could stop the database wasn’t a setting many people were willing to implement. To me, this means that auditing isn’t as important as having the system continue to process data. If that’s the case, then is auditing that important?

    I know auditing data can be overwhelming. I know that the management of audit data, including archival, is complex. I also know that most of the data isn’t very useful and will never be examined. However when we need audit data, we really, really, need accurate audit data. I really wish that Microsoft would integrate auditing better into SQL Server to ensure the data can be easily managed, compressed, and archived in an automated fashion.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.5MB) podcast or subscribe to the feed at iTunes and LibSyn.