Welcome! I’m Thomas…

The Top 5 SQL Server Features You Aren’t Using And Why

The Top 5 SQL Server Features You Aren’t Using And Why

The Top 5 SQL Server Features You Aren't Using And WhyI’m throwing my money away.

I know I am, and yet I still do it. It’s not a lot of money though.

See, I pay for a services bundle (phone, internet, cable television) from the one-and-only provider for my town and I’m certain that I am not using every possible feature. For example, I have hundreds of channels at my disposal and tonight I found myself watching a movie streamed through my Xbox from YouTube.

Seems like a waste, right?

It *is* a waste. Just like it is a waste to have these five features of SQL Server 2012 at your fingertips and to not be using them.

Here is my list of the top SQL Server features that most of us are simply not using.

1. Resource Governor

Released in 2008 this feature allows for you to effectively throttle workloads by setting a minimum and maximum for resource consumption in order to avoid contention. Rarely used by many, this feature has been given a facelift in SQL 2014 to include the ability to throttle a workload by I/O. In the 6+ years this feature has been publicly available I would estimate that it is used by less than 6% of all customers.

2. Database Snapshots

This is a wonderful feature, and especially handy for change deployments. Snapshots make it easy for you to rollback a change by allowing you to quickly recover data or objects. Despite the many benefits snapshots offer they remain about as used as Bing. On second thought, probably not even that much.

3. Policy Based Management

I’ve been a big fan of this for years, ever since it was known as “declarative management framework”. I even helped to do the technical review for the Pro SQL Server 2008 Policy-Based Management book. PBM offers you the ability to quickly deploy a policy to check all instances registered within a Central Management Server (something else you probably aren’t using either).

4. SQL Audit

I still find people asking questions in forums regarding how best to trace activity against an instance of SQL Server for audit purposes. The answer? Use SQL Audit! Alas, many people have no idea that this feature exists, or how to use it efficiently.

5. Native Row and/or Page Compression

The gap between customer adoption (very low) and performance benefit (very high) for this feature represents the widest gap on this list. Compression is easy to implement and you will see performance gains right away.

Now, take a good look at the list above. Do you see anything in common between all those features?

Here’s a hint for you, check out this chart:

Did you guess it yet?

That’s right, every one of those features is Enterprise edition only.

And therein lies the problem. When I am presenting at events like a SQL Saturday I will ask the attendees if they are using the above features. The answer is almost always “no, because it is Enterprise only”. [Of course every feature has a cost, benefit, and risk associated with it’s use, but the business can’t decide the business case for those features if their data professionals don’t know (or care to know) about the features or their benefits.]

I know that one of the few people reading this post is saying “YES, STANDARD EDITION ALL THE FEATURES”. And to those people I would say this: I find your lack of foresight disturbing.

Microsoft Licensing: The Only Thing More Confusing Than Airline Ticket Pricing

SQL Server licensing is a very complex beast so let’s consider a simple example. Let’s say I want to run SQL Server Enterprise edition on top of Windows Server 2012 on a server that has eight logical cores, hosted in my own datacenter, which means I have to pay someone to take care of things like racking the server, buying storage, and paying for Software Assurance in order to have Microsoft support. I don’t have the full costs available (because you don’t ever get them in advance) so let’s just say it’s a lot, close to $60k just for licensing and software and not including hardware, storage, and staff. There is also electricity, cooling, fire prevention, flood prevention, physical security, security staff, and time to production (getting a physical server up and running can take months in a corporate process.)

Considering some folks are still running SQL2000 instances (do you know what year it is?), how many years from now might someone still be running this Enterprise edition of SQL 2012?

Years, that’s how long.

And every year that goes by, Microsoft may get a whopping total of $0 for that server. Not likely, but possible.

Now, go here and do some digging around: 

An Enterprise edition of SQL Server on eight logical cores in an Azure VM will cost roughly $4,300 USD per month (not including bandwidth for egress, but we didn’t talk network costs for the first server either). That’s about $50,000 USD per year. And that price includes the Windows O/S,  the hardware, and you won’t need to pay anyone to watch the server in your datacenter at night.

Which do you prefer? Don’t answer yet.

You think it might be less money for the Azure VM, and it is. But it is also more. At some point those costs will exceed the amount you paid for the first server in our example.

Why Microsoft Wants You in the Cloud

This is what Microsoft (or any company for that matter) truly wants: a predictable revenue stream. Microsoft has a chance to be the 21st century equivalent of The Electric Company. They want us to be plugged in, all the time. They want us to pay a little bit, every month, forever.

They don’t want us to pay for software that we can use for years and will generate little additional income (like all those SQL 2000 boxes still running).

The feature limits imposed upon users of SQL Server Standard edition aren’t there to force customers to pay through the nose now for Enterprise edition. That would be a horrible business model if true. Customers would look to competing products and technologies.

Therefore, a smart company would offer a viable alternative that would allow them to retain customers. Limiting the features inside of SQL Server Standard edition will motivate business owners to evaluate Windows Azure as a viable alternative.

Check out what’s new in SQL Server 2014 and then check out what features and editions are available for SQL Server 2014. Notice the trend? I sure do.

If you think the five features listed above are worth using (and most people do believe they are) but haven’t been able to use them because you’ve been running SQL Server Standard edition, now would be a good time to look at Windows Azure.

[I’ll even help get you started, go here and try it free for one month, then thank me later.]

3 Pingbacks/Trackbacks

  • datachick

    Microsoft. Easy Reader…coooool.

    • ThomasLaRock

      Easy Reader? Wow, you are truly showing your…experience.

  • Pingback: The Top 5 SQL Server Features You Aren’t Using And Why - SQL Server - SQL Server - Toad World()

  • Chris Yates

    I was excited to see PBM on there. I’ve found that to be a very useful tool in a good size environment. I like the ability for rapid policy checks. PBM along with our CMS has become a mainstay.

    • ThomasLaRock


      I’ve always been enamored with the idea of PBM. With 180+ servers and 3,000+ databases in my care, PBM along with CMS made my life a bit easier at times.

  • Pingback: (SFTW) SQL Server Links 07/03/14 • John Sansom()

  • eghetto

    SQL Server Service Broker is pretty cool, isn’t it? Who is using that?

    • ThomasLaRock

      Yes, Service Broker *is* very cool. And I don’t know of many that are using it, but I haven’t been asking about it enough.

  • Gianluca Sartori

    IMHO PBM is seriously broken. The idea is good, but the implementation is not. Try evaluating polices from a CMS on different versions of SQL Server and watch it explode.

    • ThomasLaRock

      I would not say that PBM is “seriously broken”. I would agree that it does have limitations. However, once you are aware of those limitations you begin to understand where PBM makes sense and where you need different tools like OpsMgr.

  • DoctorOwl

    You wrote: “And every year that goes by, Microsoft may get a whopping total of $0 for that server. Not likely, but possible.”

    I don’t know much about the licensing aspect but I was told that since SQL 2008 all the license fees are per annum whether you get Software Assurance or not, and so they are already on a predictable revenue stream whether you go to Azure or not. And that’s why companies are really reluctant to upgrade. Is that right?

    • ThomasLaRock

      I wasn’t aware of such a licensing model. It could be true. Maybe someone else can comment on that aspect.

      • SQLCAN

        I am not a license expert; however under EA its’ not that you are paying for license over multiple years. You initially pay for License+SA and then for each following year up to product life cycle you are paying SA for each production instance, which provides you support from Microsoft Premier support services.

        • ThomasLaRock

          Yes, but you don’t have to pay for that support, do you? For example, if I was still running SQL 2000, and that is past it’s end of life…would I be paying for support?

          • Correct. You would not pay for that fees, however as organization you would have option to get Extended Customer Support Agreement for each instance you want support for from Microsoft. However, each year out-side support cost increases.

          • ThomasLaRock

            Thanks for the info!

          • Guess goes without saying, but after 10 years Microsoft does not provide support for any products by default. That’s why we generally recommend people should look at moving to next version when main stream support as ended. This way to minimize the risk of having applications run on unsupported platform.

    • Paul Bell

      Those with Enterprise Agreements can pay for licenses over several years.

      • ThomasLaRock

        Yes, and you can do that for Azure as well. You pay for expected usage. If you go 50% over your estimate, you are asked to pay quarterly.

  • realsqlguy

    Compression – we’re using that extensively. Solidly in my “top ten” new features that have been added recently.

    Snapshots – we also use these heavily, for a variety of reasons. One is as a fallback option during deployments. We also use them for reporting, as a static source for ETL, a daily “backup” of sorts for QA environments, and probably more that I don’t know we’re doing.

    • ThomasLaRock

      Thanks, do you use PBM at all?

      • realsqlguy

        No, haven’t even looked at it. It’s on my list of things to explore “someday”, because managing individual servers is becoming problematic.

        • ThomasLaRock

          Ah, yes, someday….

  • Ayman El-Ghazali

    I’m planning on attending the Resource Governor session at SQL Saturday in Richmond (March 22, 2014). Yeah, yeah, I’m promoting the event!

    We use DB Snapshots for doing our QA testing at work. We also use them on our Mirrored databases to check/verify data since it is the only way you can read from the mirror. It serves as a nice way to test the data without having to do a full fail over.

    I blogged about using DB Snapshots for QA purposes. Hopefully it will be beneficial to some of your readers:

    • ThomasLaRock


  • Dave Bennett

    “The feature limits imposed upon users of SQL Server Standard edition
    aren’t there to force customers to pay through the nose now for
    Enterprise edition. That would be a horrible business model if true.
    Customers would look to competing products and technologies.”

    I have to disagree with both those statements. The feature limits are there specifically to force customers to pay more for Enterprise edition. Microsoft says so all the time. The way they continually boast about these and other enterprise only features, so they can convince you that you should pay nearly 4 times the amount of Standard is pretty clear. It seems to me that only reason Standard exists from their point of view is to keep more customers from looking at less expensive competitors.

    Which brings out the second point, there are lots of things keeping customers with Microsoft (or away from them for that matter), the price difference between Enterprise and Standard is only one item in all the factors people consider. If people are developing on the .Net platform, there is tremendous incentive to use SQL Server, no matter the cost. I could go on.

    I have always felt that Microsoft is their own worst enemy here. If Enterprise was 50% more than Standard, I think the vast majority of customers would go with it, but when I can stand up almost 4 Standard instances for the same price as 1 Enterprise instance, the business case for those Enterprise only features has to be extremely compelling, not just a “nice to have” kind of thing.

    Also, I’m not sure that the cloud issue really is germane to the Enterprise only discussion. Several of the Enterprise only features don’t exist in Azure currently either. Sure Microsoft wants steady revenue, but they have other ways of getting that, as mentioned in other comments.

    • ThomasLaRock


      Thanks for the comment.

      My point is that the features are there to force us to use Azure as a lower cost option to buying Enterprise licenses for on-premises instances.

      Also, I think you are mixing platforms here. Windows Azure SQL Database would not have all Enterprise features, but an Azure VM running SQL Server is the same as any other SQL install.


      • Dave Bennett

        Maybe I’m confused. Assuming a five year lifespan on an on premise system, vs. the same thing with Azure (using your numbers) Enterprise looks about the same on Azure vs. on Premise, so how is this better deal for the business?

        This is still a huge price jump up from Standard and I am trading in some infrastructure uncertainty for network and vendor uncertainty. One of the big business risks of SaaS is that the vendor can (and probably will) make price changes down the road that you have no control over. Microsoft has been discounting their Azure prices of late, but there is no guarantee that they will continue to do so down the road. Especially if I am trying to price this on a five year time frame as is typical with on Premise purchases.

        Or maybe I’m all wrong on my numbers. Either way, I still think that Microsoft is doing themselves more harm than good with their crazy price jump between Standard and Enterprise.


        • ThomasLaRock


          It’s a benefit if you don’t have to pay as much for infrastructure/hardware costs. We are focusing only on licensing fees, but there is a lot of additional overhead associated with hosting your own data center as opposed to someone else hosting it for you.

          I agree that the difference in price between the editions is steep. My point is that the real driver for Microsoft these days is to get people into Azure.

  • Pingback: The Top 5 Sql Server Features You Aren’t Using And Why | Senior DBA()