- March 8, 2015
- Posted by: Warner Chaves
- Category: Database Administration, SQL Server Performance Tuning
One of the most common complaints I’ve been hearing from clients and colleagues are how most of the “big” features of SQL Server are being funneled straight into the Enterprise edition and the Standard edition gets no love at all. Now, I’m not going to disagree with that, I agree that it would be really nice if Microsoft could throw a couple of cool features like Availability Groups (even if it was some kind of limited version) into the Standard package.
However, reality is that if you want to invest on SQL Server Standard you have to learn to live without the Enterprise features but it doesn’t necessarily mean you’re getting a non-production, non-enterprise database product. By all means, SQL Server Standard is a very robust solution that fits very well for small and medium workloads.
I’m going to tell you another thing I’ve noticed with my clients: even on Standard edition, many of them are still not taking advantage of all the features of the product! That’s where the idea for this blog post came to be. I wanted to create a reference guide of many features that are included in the Standard edition that people might miss. Or make you think of well known features in a different way so you can squeeze absolutely every single drop of benefit from that SQL Server Standard you have deployed.
Note: on each point I’m going to mention the minimum version where this feature is available starting with SQL 2005. If at this point you’re still on SQL 2000 then go check out my post on Best Practices for Upgrading to SQL 2014 and stop procrastinating!
Without, further ado, let’s get on with my list of Top Features of SQL Server Standard that you’re NOT using!
1. Multi-instancing (SQL 2005+)
So you look at all the cool stuff you can with Resource Governor on Enterprise Edition and you wish you could have as much control. OK, so it won’t be 100% the same, but a lot of people forget that you can add many instances of SQL Server (up to 50) to a single machine and control their CPU, memory and storage assignments. All with one license.
This means you can do for example:
a) Instance named Legacy with files on slow magnetic drives, 4GB of RAM assigned, 2 CPU cores assigned.
b) Instance named Reporting with a mix of SSD and magnetic, 8GB of RAM assigned, 4 CPU cores assigned.
c) Instance named OLTP on SSDs, 16GB of RAM assigned, 10 CPU cores assigned.
That would be on a 16 core, 32GB server.
And remember that Standard edition limits are per instance! For example, even though the individual limit on SQL 2014 is 16 cores, nothing is stopping you from deploying a 48 core server with 3 instances. Or the RAM limit is 128GB on SQL 2014, but you could deploy a 256GB server and have 2 instances running to use up all the available memory.
2. Backup Compression (SQL 2008 R2+)
I’m surprised by how many times we still find servers out in the field in production without backup compression enabled. This usually happens when a non-DBA is not familiar with the setting, doesn’t activate it at the instance level and then creates the default maintenance plan task with the setting set to inherit the instance level setting. I probably don’t need to convince anyone reading here of the massive savings on disk space and backup time when compression is activated so really, if you’re using native backups, there’s no excuse not to activate backup compression.
3. Encrypted Backups (SQL 2014+)
For a long time encrypted backups have been the domain of 3rd party backup tools but not anymore. Starting with SQL Server 2014, you are able to take encrypted compressed backups directly from your normal SQL Server without any 3rd party tools on all Enterprise, BI and Standard edition. A big note about encrypted backups: make sure you understand how to DECRYPT them and have practiced it, you don’t want to lock yourself out of your own backups! That will probably be a future blog post just in case.
4. Delayed Durability (SQL 2014+)
I have already blogged a bit about this new feature here. It’s one of the coolest features that comes with the 2014 release and I’m very pleased they decided to make it available on Standard as well. Check out the blog post and see if the use cases can apply to your system and you might be on the road of getting some good improvements on T-Log performance.
5. Clustering (SQL 2005+)
HA is not only for the Enterprise edition as we will see with my next 2 other features as well. The only big limitations is that on Standard edition you are limited to a 2-node cluster, however for many small and medium size business that could be a very acceptable limitation. Clustering itself has matured so much over the years and shared storage is very common on most environments, making this one of the best one-stop solutions for local high availability.
6. Log Shipping (SQL 2005+)
This one could also be called “poor man’s Availability Group read-only replicas”. Log shipping has the privilege along with Availability Groups of being the only HA feature (no, I don’t count Replication as HA) that allows multiple read-only copies of a database. You’re on Standard Edition and you want to scale out to 4 servers for reporting on 4 different geographies? Log shipping can do that. You want a controlled, automated and delayed copy of your database? Log shipping is the ONLY one that will do that.
7. Database Mirroring (SQL 2005+)
Database mirroring has been getting a lot of bad press lately because Microsoft decided to deprecate it starting with SQL 2014. However, let’s keep in mind that it is still in the box in 2014 which means they will have to support it all through the lifecyle of 2014 (2024?) and more if they still include it as deprecated on the next release. And to be honest, it still does things that the other features can’t match. Availability Groups are still Enterprise only, Log Shipping might be “too delayed” for some folks and clustering might not be possible if for example there is no shared storage available. Since mirroring can do security through certificates it can also skip through the requirement of requiring a domain across both Principal and Secondary.
Definitely a technology that in my opinion is very much on the table when picking an HA solution. Just remember that Standard is limited to “synchronous” mode so you will NOT want to deploy this on a high latency WAN link.
8. Partitioned Views (SQL 2005+)
For some reason a lot of people think that Partitioned views are an Enterprise only feature. For all of you unfamiliar, here is the link to the official documentation. Basically, with partitioned views you are able to simulate having a partitioned table on Standard edition. Instead of having multiple partitions as part of the table, you have multiple tables as part of the partitioned view. If you specify the proper WHERE clause, the optimizer is even smart enough to do elimination and hit only the necessary tables you need.
Now, you will need to edit the View definition to add or remove “partitions” but many clients move around this limitation by simply doing these maintenance operations outside of business hours and it still let’s them leverage this advanced functionality without having to invest into a full upgrade to partitioned tables on Enterprise edition.
Even the sister feature of this one: distributed partitioned views (a view over multiple servers) is ALSO available on Standard edition, with the only caveat that it is NOT UPDATE-ABLE. However, this might be acceptable for many use cases.
9. Buffer Pool Extension (SQL 2014)
This is another new 2014 feature that we have covered here and here. While a very cool feature in itself, BPE also fits very well with the Standard edition because it’s the one that has the 128GB RAM limit (on SQL 2014). So, if your workload can’t be split into multiple instances and you can’t afford the Enterprise upgrade, BPE will at least allow you expandability with the deployment of SSD storage to increase the buffer pool size.
10. Table Change Tracking (SQL 2008+)
Another feature we covered on a practical introduction, here are part 1 and part 2. Completely supported inside Standard edition, it can be a great tool to use when creating sync functionality on clients that can’t be replication subscribers for example, or where you only want to track changes to one table and a full blown replication setup is not worth the management overhead.
Do NOT confuse with Change Data Capture (CDC), that is an Enterprise feature and will capture ALL intermediate changes in a row. However, for many applications, just knowing what row and column has changed without knowing the entire history of the changes is more than enough.
11. Replication (SQL 2005+, except P2P)
I’m not a big fan of SQL Server replication. It doesn’t have native DDL support, it has many moving pieces and even up to this day has some strange behaviors and bugs that will make it a pain to manage on an ongoing basis. However, I acknowledge it’s usefulness when you need to quickly deploy data publications and easily distribute specific database objects to many servers. It’s all in Standard edition except with Peer to Peer but in any case, I’ve still yet to see a P2P replication deployment on our many clients that is actually worth the pain of managing that solution.
12. Policy based management (SQL 2008+)
A very useful feature for managing not only local policies but also for registering servers to comply to one global policy. This again is included on Standard edition and the default install of SQL Server already copies many useful policies on the installation path that you can deploy to the server. You can of course create your own or get ideas from many of the articles you can find online on this topic.
13. Plan guides (SQL 2005+)
A life saver feature when dealing with badly behaved code that you CANNOT change. So you need a specific hint or you need to lock a specific plan in place? Create a plan guide so SQL Server will capture the query on-the-fly and do the change without you having to go in and change any code. Some 3rd party applications might submit code and you don’t have access to the source so you wouldn’t even be able to change it even if you wanted to! In those situations where you’re backed into a corner, plan guides are an invaluable life line.
14. Indexed Views (SQL 2005+)
And last but not least. Another feature that for some reason a lot of people think is Enterprise only. Indexed views are very useful for materializing views to be consumed as reports for the users of your application. For example, after the data load cycle that happens over night, create the indexed views and then the data will be pre-computed ready to simply be accessed by your users.
Standard edition does have the limitation that you need to specify the view explicitly whereas Enterprise can actually use it as long as the query request matches the view shape. However, this is not a major road block for many clients.
I’m sure I’m missing some features that are Standard and you probably love to use in your environment, please share them with us in the comments! I hope this provides a good starting point and some food for thought for all you folks working on Standard edition. And that instead of focusing on what we CANT DO, focus on what we CAN DO and push the product to the limit of it’s capabilities.