SQL 2014: Investigating Microsoft's latest database release
A comprehensive collection of articles, videos and more, hand-picked by our editors
Microsoft bills SQL Server 2014 as a hybrid cloud platform because of its increased Windows Azure integration....
With SQL Server 2014, you can host your database log and data files in Windows Azure Storage. SQL Server 2014 also provides new tools for managing database backups to Windows Azure Storage and deploying databases to Windows Azure virtual machines (VMs). Plus, you can now use Windows Azure VMs as secondary replicas for your AlwaysOn Availability Groups configuration.
Storing SQL Server data files in Windows Azure
SQL Server 2014 now includes the SQL Server Data Files in Windows Azure feature for hosting your .mdf, .ldf and .ndf files within a Windows Azure Storage container. You can store log and data files for both on-premises databases and databases hosted on a Windows Azure VM.
The feature saves the files to a container associated with your Windows Azure Storage account. Each file is stored as a page binary large object (BLOB) with a 1 TB maximum size. A storage account can contain an unlimited number of containers, and each container can contain an unlimited number of page BLOBs. However, the account as a whole cannot exceed 100 TB.
The Windows Azure data files feature also has a number of other limitations. For example, you cannot store FileStream data or In-Memory OLTP data in Windows Azure Storage. In addition, multiple SQL Server instances cannot access the same database files at the same time.
If you can work around the various limitations, you might benefit from the flexible scalability, simplified application development and geographically dispersed locations that cloud-based storage and the Azure integration can provide. The Windows Azure data files feature can also help to simplify migration processes and enhance high availability and disaster recovery strategies.
Managing SQL Server backups to Windows Azure
You've been able to save database backups to Windows Azure Storage since the release of SQL Server 2012 SP1 CU2, whether working with on-premises databases or those hosted in a Windows Azure VM. However, SQL Server 2014 takes cloud backups one step further with the introduction of the SQL Server Managed Backup to Windows Azure feature, which manages and automates Azure-based backups.
With the managed backup feature, you need to specify only the retention period and storage location for the backup file. SQL Server does the rest. The retention period determines how long backup files are retained in storage. You can choose between one to 30 days. The location is an URL that points to a specific container and page BLOB in Windows Azure Storage.
You can configure managed backups at the instance or database level. If configured at the instance level, the managed backup settings apply to all databases on that instance, both existing and those added later. If configured at the database level, the settings apply only to the specific database and override any settings at the instance level. Once you've configured the managed backup settings, SQL Server performs and maintains all backup operations. You don't specify the backup type, frequency or any other details.
Deploying on-premises databases to Windows Azure
SQL Server 2014 now includes the Deploy Database to a Windows Azure VM wizard, which lets you easily deploy a database to an instance of SQL Server on a Windows Azure VM. The wizard provides a straightforward interface to help simplify the steps necessary to carry out the deployment and improve Azure integration.
Before you launch the wizard, however, you need at your disposal the information necessary to complete the wizard, such as details about the Microsoft account associated with your Windows Azure subscription as well as the management certificate uploaded to that subscription. In addition, if you're deploying to an existing Windows Azure VM, you must also provide the proper administrative credentials and the DNS name of the cloud service hosting the VM.
Although the deployment wizard is new to SQL Server 2014, you can use it to deploy database versions from SQL Server 2008 on, as long as they're hosted on an on-premises instance of SQL Server 2014. For Azure-based SQL Server instances, you can use the wizard only for SQL Server 2012 and 2014 database versions.
Regardless of where your databases reside, you cannot deploy a FileStream-enabled database into a new VM. The FileStream feature must already be enabled on the Azure-based SQL Server instance before deploying the database to that instance. You can, however, deploy a database that uses In-Memory OLTP without taking extra steps.
Implementing an Availability Groups secondary on Windows Azure
Perhaps one of the most publicized cloud-related features in SQL Server 2014 is the ability to configure a SQL Server instance on a Windows Azure VM as an Availability Groups secondary replica. The replica serves as a stand-in should the primary replica fail and supports ongoing read operations whether or not the primary fails. Best of all, setting up an Azure-based secondary is now integrated into the Availability Groups configuration process.
In addition to configuring the secondary replica, you must also establish a virtual private network (VPN) between your primary instance and the Windows Azure VM, if your primary resides on-premises. In such cases, the Azure-based secondary supports only asynchronous replication and manual failovers. However, if your primary is an Azure-based instance, you can also set up synchronous replication and automatic failover.
Microsoft's hybrid-cloud database platform
Clearly, Microsoft is pushing the cloud big time in SQL Server 2014. But not just any cloud, only Windows Azure with SQL Server 2014's Azure integration. Like any business, Microsoft wants to make money. So weigh carefully the real costs of committing to Windows Azure before making any decisions. Using the cloud might seem a good strategy in the short term, but you should also take into account the long-range view.
Microsoft provides two sites for helping you assess your costs: Windows Azure Storage Pricing Details and Windows Azure VM Pricing Details. This information alone, however, should not replace a comprehensive cost analysis that considers all potential expenses. If after that analysis you determine that Windows Azure fits your budget and database requirements, the Azure integration in SQL Server 2014 might be just the type of service that suits your needs.
About the author:
Robert Sheldon is a technical consultant and the author of numerous books, articles and training material related to Microsoft Windows, various relational database management systems and business intelligence design and implementation.