Just when you thought SQL Server couldn’t get better, Microsoft is announcing the features for SQL Server 2014. They haven’t announced the licensing/pricing, but I’ll tell you what I do know so far.
First, open this in another tab and hit play so you’ve got some background music while you read. Done with the commercial? Okay, let’s get to it:
Cache frequently used data on SSDs. You can specify an SSD (or an SSD array) to be used to extend memory. SQL Server 2014 will automatically cache data there with zero risk of data loss. (Only clean pages, not dirty pages, are stored there.) The best use case is for read-heavy OLTP workloads. This works with local SSDs in clusters, too – each node can have its own local SSDs (just like you would with TempDB) and preserve the SAN throughput for the data and log files. SSDs are cheap, and they’re only getting cheaper and faster. Here’s the questions you’ll want to ask before you use this feature:
- Is your total actively queried data set bigger than you can fit in memory? Note that I didn’t say all data: you might have archive or history or audit tables in your databases that are never queried, and there’s no sense in caching those.
- Have you already maxed out the memory on the server? If not, start there first – memory can be used for more than just caching clean pages.
- Do business requirements force you to use shared storage or magnetic local storage? If not, consider moving the data to local SSD entirely.
- Does your server have room for locally attached PCI Express or SAS/SATA solid state drives?
If the answer to all of those questions is yes, an SSD buffer pool extension may be for you. Honestly, Microsoft could stop there and I’d probably still recommend the new version for most of my clients, because that’s a killer performance benefit.
More online maintenance operations. Got big data in a partitioned table? Is nobody giving you any time to do maintenance? Just no time to stop and get away cause you work so hard to make it every day? Well, with SQL 14, you can rebuild a single partition’s index online, and you can switch partitions in/out using DBA-specified lock priorities. For 24/7 workloads, this gives the DBA the ability to do maintenance with lower locking, CPU, and memory overhead. There’s also new Extended Events stuff you can use to monitor who’s getting blocked and killed. Here’s how the syntax works:
ALTER INDEX MyIndex ON MyTable
REBUILD PARTITION = 3
WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5, ABORT_AFTER_WAIT = BLOCKERS)))
The new parameters involved are:
- PARTITION = 3 – you can pick the specific partition you want to rebuild, and you can do it online.
- WAIT_AT_LOW_PRIORITY – just hang out when you need the schema mod lock.
- MAX_DURATION = 5 – wait for up to 5 minutes.
- ABORT_AFTER_WAIT = BLOCKERS – can be a few different variables. If BLOCKERS, then SQL Server will abort (kill) the queries blocking your index rebuild. If SELF, your index rebuild will give up and let user queries keep going. If NONE, everybody just keeps waiting, doin’ the neutron dance. This is the current behavior in SQL Server 2012, and it’ll be the default.
AlwaysOn Availability Groups get more secondaries. If you really need to scale out your reads, SQL 14 gives you up to 8 secondaries (up from 4). Of course, you’ll be paying for Enterprise Edition licensing on these, but if you were already going to replicate data out to various reporting or BI servers, now your life is easier.
AlwaysOn AG readable secondaries will be more reliable. In SQL 2012, if your primary drops offline or the cluster loses quorum, the readable replica databases drop offline. (This is hilarious because it’s right when you really want to be able to query the secondaries.) No way to control it – it’s totally automatic. In SQL 14, the secondaries remain online and readable when the primaries aren’t available. However, keep in mind that typical AlwaysOn AG connections go through the AG listener name, and then fetch the list of readable replicas from the primary. This just means that in order to keep your report queries online, you can’t use the AG listener – you have to connect directly to the replica’s server name. I like using a separate set of DNS records for readable replicas, like readonly.mydomainname.com, and have my report servers point at those.
Use Azure VMs as AlwaysOn AG replicas. Nobody wants to pay for expensive offsite datacenter space with machines that sit idle all the time. Now, in the AlwaysOn Add Replica wizard, there’s an “Add Azure Replica” button that integrates with your Azure subscription logins. The wizard lets you pick the VM image, VM size (cores & memory), cloud replica name, admin password, etc. Lots of gotchas here though:
- Initializing the replica means a full database backup/restore from on-premise up to the Azure VMs, too, so this isn’t a great solution for big databases with limited bandwidth.
- Connectivity from on-premise to the Azure VM requires a VPN appliance from your datacenter to the Azure datacenter, and today that means a hardware appliance, so there’s still some expenditures required. Still way cheaper than buying hardware for a colo, though, and much more flexible.
- If you’re really going to use it for disaster recovery, you need a Windows Domain Controller up in Azure as well. Without that, when your primary site dies, all of your Windows machines won’t be able to log on, so that wouldn’t be very useful. SSMS doesn’t automate the deployment of a DC (nor does it alert you if you didn’t think this through.)
Failover Cluster Support for Clustered Shared Volumes. With regular volumes, only one node can own the volume at any given time. He owns the entire volume, and no other node can see/read/write files on that volume. However, Windows Server clusters have a type of drive volume called Clustered Shared Volumes with much more flexibility. Multiple cluster nodes can be connected to the same volume at the same time, but each node can access different files on the drive independently. Windows and Hyper-V have supported this for a while (see the BOL section on the benefits), and now SQL Server supports it too. The big benefit here is that if one node of the SQL Server cluster loses connectivity to the storage, it can still read and write data over the network to a different node’s SAN connection.
Smart Backup to Azure. SQL Server 2012 CU2 already lets you back up databases to Azure storage. I hear a lot of people ask me, “Brent, how can I make my backups slower and less predictable?” Those folks loved backing up from on-premise databases over their Internet connections, but that wasn’t enough. They wanted even less predictability, so now they get Smart Backups. With this feature, SQL Server figures out whether it should do a full or differential backup, how often it should do a transaction log, and more. Humor aside, this makes sense for people who host their servers in VM providers with very fast Internet connections that don’t pay for bandwidth – specifically, people hosting SQL Server in Windows Azure VMs. Both of those guys are going to be thrilled.
On-premise SQL Server with data/log files in Azure storage. Great news for those of you who really like juggling chainsaws! Now you get the best of everything:
- Expensive on-premise licensing
- Expensive bandwidth costs to the cloud
- Paying for data storage at Microsoft
- Slow backups (because your data has to come down from Azure storage to local on-premise memory then back out to wherever you want it stored, and heaven forbid you be dumb enough to send it back up to Azure storage and pay TWICE for bandwidth in and out)
Here’s the syntax:
CREATE DATABASE foo
ON (NAME = foo_dat, FILENAME = ‘https://internetstorage.windows.net/data/foo.mdf’ )
LOG ON (NAME = foo_log, FILENAME = ‘https://internetstorage.windows.net/data/foolog.ldf’);
I’ll just leave that there.
Hekaton: specialized in-memory OLTP tables. If your application is facing serious concurrency issues with thousands of simultaneous connections trying to lock data, Hekaton offers an intriguing solution. I’m not even going to try to type out an explanation here, but I’ll point out a few challenges with it:
- You’ll probably need to change your data model. For example, identity fields aren’t supported – you’ll need to use a GUID as a primary clustered key.
- You’ll also probably need to change your code. Hekaton works best with stored procedures, and specifically stored procs that it can compile into native code.
- It’s memory-only. If you experience sudden data growth in your Hekaton tables, that means you can cache less of your other tables. If you run out of memory – well, let’s just say you’d better pray you don’t run out of memory, because hello, downtime.
You’re so excited, I can feel you getting hotter. But wait, there’s more.
Other cool improvements:
- Updatable clustered column store indexes
- Query performance improvements due to better cardinality estimator
- Resource Governor for IO
- Sysprep enhancements
- Wizard to deploy database to Azure VM
- Separation of duties enhancements to support DBAs who aren’t allowed to read the data, or auditors who are allowed to read the data but not manage the server
- Windows Server 2012 R2 cooperation improvements – ReFS support, online resize VHDX, storage tiering, SMB improvements
The Pointer Sisters music probably gave away my true feelings here, but really, folks, I’m so excited. There’s very real improvements in here for everybody. If you’re a DBA on a multi-terabyte database, you’re going to love the SSD buffer pool extensions and the granular index rebuilds. If you’re BI-curious, you’re going to be experimenting with the clustered column store indexes. If you’re a software-as-a-service vendor with lots of clients, you’re going to love failover cluster support for CSVs and query performance improvements. And if you’re a developer who works with a SQL Server back end, you’ve got all kinds of new tricks to scale.
I know some DBAs were worried that Microsoft was “all in” with the cloud, and that they’d stop improving the box product. SQL2014 shows that Microsoft is still bringing the awesome.
Now, can somebody just get us a release date and some pricing? I was talking to a PR guy who almost spilled the beans, but he’s so shy.