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
SQL Server 2014 is a significant release with two overarching themes: cloud and speed -- or, to be specific, Azure integrations and in-memory OLTP (online transaction processing). Truth be told, I'm more excited about the speed features than the cloud stuff, but I also understand there is a growing portion of the customer base that is heading to cloud-based operations, and these shops will find the cloud features useful.
For all SQL Server shops, I don't think we've seen a release this significant since SQL Server 2008 RTM. Looking back, you might remember that SQL Server 2008 brought Change Data Capture, data compression, and most important, PowerShell. Then SQL Server 2008 R2 brought us PowerPivot plug-ins for self-service BI, along with StreamInsight and Master Data Services. SQL Server 2012 gave us Availability Groups, columnstore indexes, and several T-SQL enhancements. You might classify SQL Server 2008 as a data warehouse release, SQL Server 2008 R2 as a BI release, and SQL Server 2012 as an HA release.
[ Also on InfoWorld: 10 excellent new features in Windows Server 2012 R2 | 10 great new features in Windows Server 2012 R2 Hyper-V | Stay abreast of key Microsoft technologies in our Technology: Microsoft newsletter. ]
SQL Server 2014 brings enhancements to a number of these areas, including Availability Groups and columnstore indexes, and others, such as security (encrypted backups). The bulk of this review will focus on the cloud and performance features.
Your database on Azure
SQL Server 2014 has two ways to work with Azure storage. First, you can back up a database to Azure blob storage. While this feature (officially called SQL Server Backup to URL) was introduced in 2012, it gets a boost in SQL Server 2014 with SQL Server Managed Backup to Windows Azure -- or simply Managed Backup, since it only works with Azure at this point. Managed Backup is well named because it literally manages your backups for you. SQL Server looks at your retention period and your transaction workload, figures out when the best time would be to take the backups, and just handles it for you. You can set it up at the instance level or the database level, so you have decent control over which databases get backed up to the cloud.
While Managed Backup can help simplify your backup strategy, especially for smaller shops without a full-time DBA, there are a couple caveats here. First, you have to be careful about the size of the databases you're pushing across the Internet. Your success at this will depend largely on your connection and on any number of other factors once you get into the wild. You may see intermittent performance problems.
Second, you need to keep an eye on your usage. If your databases get too big or if someone drops a large database on the server and it gets backed up to the cloud, you could blow up your service agreement and wind up paying a lot more for storage than you intended. That said, for smaller databases, Managed Backup is a beautiful replacement for shipping tapes offsite.