Voice of the DBA

Category: Editorial

  • The Service Pack Fiasco

    I remember getting a Service Pack years ago that caused a blue screen of death on Windows 2000 servers when it was installed. In fact, a quick search shows that quite a few operating systems (and SQL Server 2005) have had issues with SP2 over the years. Perhaps we shouldn’t be waiting for the first service pack to upgrade, but upgrading and then skipping SP2 for various products.

    Microsoft has responded fairly well to problems with service packs by removing them quickly from their download sites and re-releasing them. This occurred recently for SQL Server 2014 SP1, where an issue was discovered with the SSIS Catalog. This is disconcerting to me, not because of the bugs, but because these mistakes and problems seem to lead Microsoft in the direction of abandoning Service Packs. The idea floated in the past, and still pushed by many people, is that the Cumulative Updates are good enough for patching SQL Server.

    That seems crazy to me. If we have problems with a service pack, wouldn’t we still have problems with a CU? Or is Microsoft hoping that the smaller group of people that are impacted with CU issues are good beta testers and limit the impact of issues to the community? I’m assuming the testing is similar for all patches, if not the same. However as we all know, testing can’t cover every possible scenario. Microsoft notes this as their latest documentation includes these quotes:

    I really prefer that a service pack is released every year for each product. It can contain all the CUs up to that point, with no additional patches, thought hopefully a bit more testing. It should be a line in the sand that helps administrators manage their systems to keep up with patching and reduce support requirements. I’d actually even suggest Microsoft require vendors that want to certify their products on SQL Server and use any logo, validate their software on an SP within four months of release. In most cases, there isn’t any work for vendors, merely the effort to re-run all their tests.

    We pay a large licensing cost for SQL Server, and the lifecycle support policy under which many of us purchased our software notes that we’ll get ten years of support and the term “service pack” is embedded throughout the SQL Server support pages. We should receive a service pack every year to provide continuing support for our platforms.

    Steve Jones

    The Voice of the DBA Podcast

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

  • 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