Database Engine Feature Enhancement: SQL Server 2014

There are many new features available in SQL Server 2014 from the point view of admin and developers. I have listed few features below which are important for the admin prospective. The brief description is given below.

1. SQL Server Data Files in Windows Azure.
2. Host a SQL Server Database in a Windows Azure Virtual Machine.
3. Backup and Restore Enhancements.
4. Always On Enhancement
5. Columnstore Indexes
6. Database Compatibility Level

SQL Server Data Files in Windows Azure:

SQL Server Data Files in Windows Azure enables native support for SQL Server database files stored as Windows Azure Blobs. It allows you to create a database in SQL Server running in on-premises or in a virtual machine in Windows Azure with a dedicated storage location for your data in Windows Azure Blob Storage. This enhancement especially simplifies to move databases between machines by using detach and attach operations. In addition, it provides an alternative storage location for your database backup files by allowing you to restore from or to Windows Azure Storage. Therefore, it enables several hybrid solutions by providing several benefits for data virtualization, data movement, security and availability, and any easy low costs and maintenance for high-availability and elastic scaling.

Limitations

• Only .mdf, .ldf, and .ndf files can be stored in Windows Azure Storage by using the SQL Server Data Files in Windows Azure feature.
• When using the SQL Server Data Files in Windows Azure feature, geo-replication for your storage account is not supported. If a storage account is geo-replicated and a geo-failover happened, database corruption could occur.
• Each Blob can be up to maximum 1 TB in size.
• When using SQL Server Data Files in Windows Azure feature, SQL Server performs all URL or file path comparisons using the Collation set in themaster

Host a SQL Server Database in a Windows Azure Virtual Machine

Use the Deploy a SQL Server Database to a Windows Azure VM wizard to deploy a database from an instance of the Database Engine to SQL Server in a Windows Azure Virtual Machine (VM). The wizard utilizes a full database backup operation, so it always copies the complete database schema and the data from a SQL Server user database. The wizard also does all of the Azure VM configuration for you, so no pre-configuration of the VM is required.

You cannot use the wizard for differential backups because the wizard will not overwrite an existing database that has the same database name. To replace an existing database on the VM, you must first drop the existing database or change the database name. If there is a naming conflict between the database names for an in-flight deploy operation and an existing database on the VM, the wizard will suggest an appended database name for the in-flight database to enable you to complete the operation.

Limitations and Restrictions

The database size limitation for this operation is 1 TB.

This deployment feature is for use only with user databases; deploying system databases is not supported.

The SQL Server version in the VM must be the same or later than the source SQL Server version. SQL Server database versions that can be deployed to a Windows Azure VM using this wizard:

• SQL Server 2008
• SQL Server 2008 R2
• SQL Server 2012
• SQL Server 2014

SQL Server database versions running in a Windows Azure VM database can be deployed to:

• SQL Server 2012
• SQL Server 2014

Backup & Restore Enhancements:

SQL Server 2014 contains the following enhancements for SQL Server Backup and Restore:

1. SQL Server Backup to URL

SQL Server Backup to URL was introduced in SQL Server 2012 SP1 CU2 supported only by Transact-SQL, PowerShell and SMO. In SQL Server 2014 you can use SQL Server Management Studio to backup to or restore from Windows Azure Blob storage service.

Limitations

• The maximum backup size supported is 1 TB.
• You can issue backup or restore statements by using TSQL, SMO, or PowerShell cmdlets.
• Creating a logical device name is not supported. So adding URL as a backup device using sp_dumpdevice or through SQL Server Management Studio is not supported.
• Appending to existing backup blobs is not supported. Backups to an existing Blob can only be overwritten by using the WITH FORMAT option.
• Backup to multiple blobs in a single backup operation is not supported.
• SQL Server has a maximum limit of 259 characters for a backup device name. The BACKUP TO URL consumes 36 characters for the required elements used to specify the URL – ‘https://.blob.core.windows.net//.bak’, leaving 223 characters for account, container, and blob names put together.

SQL Server Managed Backup to Windows Azure

 Built on SQL Server Backup to URL, SQL Server Managed Backup to Windows Azure is a service that SQL Server provides to manage and schedule database and log backups. In this release only backup to Windows Azure storage is supported. SQL Server Managed Backup to Windows Azure can be configured both at the database and at instance level allowing for both granular control at the database level and automating at the instance level. SQL Server Managed Backup to Windows Azure can be configured on SQL Server instances running on-premises and SQL Server instances running on Windows Azure virtual machines. It is recommended for SQL Server instances running on Windows Azure virtual machines

Encryption for Backups

You can now choose to encrypt the backup file during a backup operation. It supports several encryption algorithms including AES 128, AES 192, AES 256, and Triple DES. You must use either a certificate or an asymmetric key to perform encryption during backup.

AlwaysOn Enhancements

SQL Server 2014 contains the following enhancements for AlwaysOn Failover Cluster Instances and AlwaysOn Availability Groups:

1. The maximum number of secondary replicas is increased from 4 to 8.
2. When disconnected from the primary replica or during cluster quorum loss, readable secondary replicas now remain available for read workloads.
3. Failover cluster instances (FCIs) can now use Cluster Shared Volumes (CSVs) as cluster shared disks.
4. A new system function,fn_hadr_is_primary_replica, and a new DMV, sys.dm_io_cluster_valid_path_names, is available.

Columnstore Indexes

These new features are available for columnstore indexes:

Clustered columnstore indexes :

Use a clustered columnstore index to improve data compression and query performance for data warehousing workloads that primarily perform bulk loads and read-only queries. Since the clustered columnstore index is updateable, the workload can perform many insert, update, and delete operations.

SHOWPLAN

SHOWPLAN displays information about columnstore indexes. The EstimatedExecutionMode and ActualExecutionMode properties have two possible values: Batch orRow. The Storage property has two possible values: RowStore and ColumnStore.

 Archival data compression

ALTER INDEX … REBUILD has a new COLUMNSTORE_ARCHIVE data compression option that further compresses the specified partitions of a columnstore index. Use this for archival, or for other situations that require a smaller data storage size and can afford more time for storage and retrieval.

Database Compatibility Level

The 90 compatibility level (i.e. SQL server 2005) is not valid in SQL Server 2014.

Note: Your valuable feedback would be appreciated. 

Thank You