Review: SQL Server 2014 pushes the pedal to the metal
Microsoft's latest SQL Server release boosts OLTP performance with in-memory tables, expands backup and HA options with ties to Windows Azure
Backups aren't the only role for Azure to play with SQL Server. You can also define a Windows Azure VM as a secondary replica to an Availability Group. This can be a great option for shops that need a reliable HA solution but don't have a second data center to fall back on. SQL Server even provides you with a wizard to aid in setup. This feature comes with some stiff prereqs, such as a site-to-site VPN between your on-premise subnet and Azure, so make sure your infrastructure team can make it happen before you bet the farm on it.
One more Azure feature worth knowing about is SQL Server Data Files in Windows Azure. The names are getting longer, aren't they? I'm far less excited about this feature, which allows you to create an on-premise database and store its files on Azure blob storage, because it's so open to abuse (by shops that will be sorely tempted to run huge databases with heavy transaction workloads from the cloud), but I can see some benefits in certain situations.
For example, when you create a database on a local server, you normally store the database files on either local storage or a SAN. Data Files in Windows Azure allows you to instead put the files in a URL-accessible location on Azure storage. This could be beneficial if you wind up doing a migration from one server to another because you simply have to attach the database files to the new server -- there's no data movement involved. That means you don't have to worry about restoring a database after a failure, or copy files across the network, or wait for four hours for your tapes to be brought back on-site. The blob storage is always there.
For some shops, Data Files in Windows Azure could be a critical piece in their disaster recovery strategy. It is a true hybrid scenario where you manage every aspect of your SQL Server on-premise, but the actual data resides in the cloud. It also provides you with high availability that you don't have to manage. Unfortunately, it also puts your database at the mercy of the ebbs and flows of the Internet. Troubleshooting database performance is more complicated because you don't know if there's an issue with the local server, the indexes, the queries, or the Internet. We've had the ability to attach database files across local networks for years now, and it's been discouraged for performance reasons. I have a hard time believing that adding the open Internet to the equation is suddenly a good idea.
Feeding the need for speed
These days, even the smallest of online startups is instantly thrown into a global marketplace and could find itself inundated with server requests. When it comes to performance, Microsoft has risen to the challenge with some nice features for this release.
We'll start with what I consider to be the flagship feature of SQL Server 2014, Hekaton. It's Microsoft's flavor of memory-optimized tables: All you have to do is define a table as being memory-optimized, and the engine will take care of the rest. And unlike other in-memory database solutions, Hekaton allows you to bring individual tables into memory instead of the entire database. This follows a more realistic business scenario too, because you typically have only a handful of tables that need this kind of boost, and forcing the entire database into memory for the sake of a few tables isn't a good use of resources.
Hekaton achieves its turbo boost through a combination of optimized algorithms, optimistic concurrency, eliminating physical locks and latches, and oh yeah, storing the table in memory. If you have stored procedures that hit only the memory-optimized table, you can convert them into Hekaton procedures to get even more speed. This compiles them into native C code, which optimizes their efficiency.