SQL Server 2014, the latest complete information platform from Microsoft, embodies the new era of Microsoft’s Cloud OS, which provides organizations and customers with one consistent platform for infrastructure, apps and data that span customer data centers, hosting service-provider data centers, and the Microsoft public cloud. The benefits that customers experience with a consistent platform include common development, management, data, identity, and virtualization, no matter where an application is being run. SQL Server 2014 also offers organizations the opportunity to efficiently protect, unlock, and scale their data across the desktop, mobile devices, data centers, and a private, public, or hybrid cloud. Furthermore, SQL Server 2014 builds on the success of SQL Server 2012 by making a strong impact on organizations worldwide with significant new capabilities that are tightly aligned to the trends transforming the IT industry, including Microsoft’s Cloud OS. SQL SERVER provides mission-critical performance for the most demanding database applications while delivering the highest forms of security, scalability, high availability, and support. SQL Server 2014’ s mission is to deliver faster insights into big data, small data— all data— and, most importantly, deliver business intelligence in a consumable manner through familiar tools such as Microsoft Excel. Finally, SQL Server 2014 enables new unique hybrid-cloud solutions based on the Cloud OS. These solutions can positively affect an organization’s bottom line and allow an organization to create innovative solutions for its database applications. below diagram shows the mission statement for SQL Server 2014, based on three pillars.
Diagram of SQL Server 2014, a cloud-ready information platform.
This chapter examines the new features, capabilities, and EDITIONS OF SQL SERVER 2014 from a database administrator’s perspective. It also describes hardware and software requirements and installation strategies.
The organizations of today are looking for a trusted, cost-effective, and scalable database platform that offers mission-critical confidence, breakthrough insights, and flexible cloud-based offerings. These organizations regularly face changing business conditions in the global economy and numerous challenges to remaining competitive, such as gaining valuable business insights, using the right information at the right time, and working successfully within budget constraints. In addition, organizations must be fluid because new and important trends regularly change the way software is developed and deployed. Some of these trends focus on in-memory databases that can improve performance and efficiency, a capability that has most likely been influenced by the dramatic drop in the cost of memory. Another important trend sweeping over IT is the adoption of cloud computing by customers who require an infrastructure that provides a generational leap in agility, elasticity, and scalability across a set of shared resources with more automation of cloud services and self-service provisioning. Last but not least is the data-explosion trend, where discussions about storage capacity naturally involve the word zettabytes. Customers want to gain new insights about their businesses faster, so they are analyzing larger data sets, both internal and external. These new insights are helping customers obtain a competitive edge, but they are also leading to massive volumes of data. Microsoft has made major investments in SQL Server 2014 as a whole. The new features and capabilities that should interest database administrators are introduced in this chapter, including availability, backup and restore, scalability, performance, security, manageability, programmability, and the platform for the hybrid cloud. Other chapters in this book offer a deeper explanation of the major technology investments.
A tremendous number of high-availability enhancements were added to SQL Server 2012, which increased both the confidence that organizations have in their databases and the maximum uptime for those databases. Microsoft built on this work with new high-availability enhancements in SQL Server 2014.
In SQL Server 2012, AlwaysOn Availability Groups was the most highly anticipated feature related to the Database Engine for DBAs. This high-availability and disaster-recovery capability provided protection by allowing one or more databases to fail over as a single unit. Shared storage was not needed, and replicas could be leveraged to offload backups and reporting workloads from the primary. Enhancements in SQL Server 2014 have given databases even better data redundancy, protection, and availability. First, the maximum number of secondary replicas has increased from four to eight. This change allows organizations to further offload read-only operations such as reporting and backups to additional secondary replicas. The additional secondary replicas can also be placed in more data centers for higher levels of protection and disaster recovery. Moreover, with SQL Server 2014, the secondary replicas have been enhanced and can be used for read-only operations even in the case of network failures or loss of quorum between replicas. Second, whether a manual or an automatic failover is performed, the operation no longer needs to fail over databases one at a time. Numerous databases can now fail over simultaneously, which increases availability. Finally, SQL Server 2014 introduces the Add Microsoft Azure Replica wizard for organizations looking to create a secondary replica that is stored in Windows Azure, Microsoft’s public cloud. (Below Figure.) Placing a secondary replica in Windows Azure is a great way for an organization to achieve additional disaster-recovery protection in the unlikely event that all data centers hosting its secondary replicas become unavailable.
A global organization using AlwaysOn Availability Groups, including Windows Azure, to achieve high availability and disaster recovery.
In Above Figure, company X is an organization with a global presence. It achieves both high availability and disaster recovery for mission-critical databases by using AlwaysOn Availability Groups. Secondary replicas are placed in data centers around the world, including in Windows Azure, and are being used to offload reporting and backups.
AlwaysOn Failover Cluster Instances (FCIs) is a feature that provides superior instance-level protection by using Windows Server Failover Clustering and shared storage. Traditionally, each SQL Server failover cluster instance required at least one logical unit number (LUN) because the LUN was the unit of failover. This requirement imposed a significant limitation because when a database administrator ran out of drive letters or mount points, the administrator also lost the opportunity to host any more failover cluster instances. This anomaly has been addressed in SQL Server 2014 with the use of the Cluster Shared Volumes (CSVs) feature, as shown in Figure below, which requires fewer LUNs. As a note, this feature is included in Windows 2012 and later releases.
Using the Cluster Shared Volume feature associated with Windows Server 2012 R2 for storing SQL Server 2014 virtual machines.
CSVs reduce the number of LUNs (disks) required for SQL Server clustered instances because many failover cluster instances can use a single CSV LUN and can fail over without causing the other FCIs on the same LUN to also fail over. Finally, new dynamic management views introduced in SQL Server 2014 help administrators troubleshoot and return information pertaining to FCIs.
Regardless of how many database replicas an organization has within its enterprise, there is still a need to protect data with backups. Hence, Microsoft continued its investments in backup and recovery to protect data with SQL Server 2014. The new enhancements include the following: SQL Server Managed Backups to Windows Azure Backups in SQL Server 2014 natively support the Windows Azure Blob storage service for simplifying backups to the cloud. Hybridcloud backups reduce capital expenditures (CAPEX) and operational expenditures (OPEX) and improve disaster recovery for an organization’s backups because the backups stored in the Windows Azure cloud are automatically replicated to multiple data centers around the world. The process to exploit this new enhancement is fairly straightforward. First, create a Windows Azure storage account and a blob container, and then generate a SQL Server credential that will be used to store security information and access the Windows Azure storage account. Finally, create a backup that will use the Windows Azure Blob storage service.
SQL Server backups have been updated to use URLs as one of the destination options when backups are performed with SQL Server Management Studio. Backups are stored in Windows Azure because the Windows Azure Blob storage service is used. Previously, only Transact-SQL, PowerShell, and SQL Server Management Objects (SMO) were supported when using SQL Server 2012 SP1 CU2 and later. A database backup to a URL destination is depicted in Figure 1-4.
For years, DBAs have been asking for the ability to natively encrypt data while creating a backup. This task can now be performed in SQL Server 2014 by specifying an encryption algorithm and an encryptor— a certificate or an asymmetric key— to secure the encryption key. The industry standard encryption algorithms that are supported include AES 128, AES 192, AES 256, and Triple DES. Encrypted backups are supported in Windows Azure storage or on-premises.
Backing up a database to an Azure storage container by using a URL prefix.
The SQL Server product group made sizable investments to improve scalability and performance associated with the SQL Server Database Engine. Some of the main enhancements allow organizations to improve their SQL Server workloads, especially when using Windows Server 2012 or later releases:
In-Memory OLTP (project code name Hekaton) is considered the most important feature release and investment in SQL Server 2014. This new feature is fully integrated into the Database Engine component. Databases were originally designed to reside on disk because of the high costs associated with procuring memory. This situation has since changed, due mainly to the significant drop in price for memory. It is now possible for most OLTP databases to fit into memory, which reduces I/O expense, and in turn increases transaction speed performance. To date, organizations testing In-Memory OLTP have reported promising numbers: transaction speeds improved up to 30 times that of their past performance. As with any new capability, mileage will vary, and the best performance gains have been achieved when the business logic resides in the database and not in the applications.
At the heart of the Microsoft Cloud OS vision, Windows Server 2012 R2 offers a tremendous amount of computing resources to provide scale for large, mission-critical databases not only in a physical environment but also in a virtual environment. Windows Server 2012 R2 supports up to 2,048 logical processers for a Hyper-V host, which can handle the largest database applications. In a virtual environment you can use up to 64 virtual CPUs, up to 1 terabyte (TB) of memory, and up to 64 TB of virtual disk capability for each Hyper-V virtual guest. In addition, you now have up to 64 nodes in a SQL Server cluster and up to 8,000 virtual machines within a Hyper-V cluster. Enterprise scale on this magnitude has never been achieved before.
Windows Server 2012 R2 introduced many new capabilities in the area of virtual networking that bolster the SQL Server experience. Network virtualization provides a needed layer of abstraction, which allows SQL Server workloads to be moved from one data center to another. NIC teaming, which was introduced with Windows Server 2012, still exists and can be used to provide fault tolerance by enabling multiple network interfaces to work together as a team. Finally, SMB Multichannel and Network Quality of Service (QoS) can be used in conjunction with SQL Server to improve database application availability over physical and virtual networks by ensuring that multiple paths are available to application shares and that sufficient available bandwidth is reserved for the application. Scale storage Windows Server 2012 introduced Storage Spaces, a feature that was enhanced in Windows Server 2012 R2. Storage spaces allow database administrators to take advantage of sophisticated virtualization enhancements to the storage stack that can distribute or tier SQL Server workloads across storage pools. For example, high-capacity spinning disks can be used to store less frequently used data, while high-speed solid-state disks can be used to store more frequently used data. Windows Server 2012 R2 recognizes the tiers and optimizes them by placing hot data in the fastest tier and less-utilized data in lower tiers, improving performance without increasing costs.
In previous versions of SQL Server, it was possible to pool CPU and memory to manage SQL Server workloads and system-resource consumption. In SQL Server 2014, I/ O has been added to Resource Governor, which lets I/ O be pooled and tiered following an organization’s criteria. This ensures greater scale and performance predictability for your SQL Server workloads, especially when running applications in private clouds and environments managed by hosting organizations.
Buffer pool extension enables integration of a nonvolatile random access memory extension with the Database Engine buffer pool to significantly improve I/O throughput. Solid-state drives (SSDs) would be considered nonvolatile random access memory, which would be added to the SQL Server system to improve query performance. Benefits that can be achieved when you use buffer pool extension include increased random I/ O throughput, reduced I/ O latency, increased transaction throughput, improved read performance with a larger buffer pool, and a caching architecture that can take advantage of present and future low-cost memory drives.
SQL Server 2014 supports new functionality in Sysprep, which allows you to fully support deployments of clustered SQL Server instances. This capability reduces deployment time for SQL Server failover clusters and is great for building private or public clouds.
Columnstore indexes are used to accelerate query performance for data warehousing that primarily performs bulk loads. In the previous version of SQL Server, tables that had columnstore indexes could not be updated. In the past, you had to drop the index, perform the update, and then rebuild the index, or use partition switching or two tables— one with a columnstore index and another for updating— and then use UNION ALL queries to return data. As you can imagine, this could be challenging from an administrative perspective. With SQL Server 2014, in-memory columnstore has been modified to support updateable operations such as inserts, updates, and deletes.
Approximately a decade has passed since Microsoft initiated its trustworthy computing initiative. Compared with other major database players in the industry, SQL Server has had the best track record since then, with the least number of vulnerabilities and exposures. Based on results from an April 2013 study conducted by the National Institute of Standards and Technology (NIST), shown in Below Figure, SQL Server led the way five years in a row as the least-vulnerable database among the top data platforms tracked. Moreover, it is currently the most-utilized database in the world, with 42 percent of market share. (Source: ITIC 2013: “SQL Server Delivers Industry-Leading Security.”)
Common vulnerabilities and exposures reported to NIST from January 2006to January 2013.
With SQL Server 2014, the product continues to expand its solid foundation to deliver enhanced security and compliance within the database platform. By splitting the database administrator role from the system administrator role and allowing organizations to further customize the rights of each DBA or system administrator, greater compliance and security can be achieved. Here is a snapshot of some of the enhanced enterprise-ready security capabilities and controls that enable organizations to meet strict compliance policies and regulations: Redefined engineering security process CC certification at high assurance level Enhanced separation of duty Transparent data encryption (TDE) Encryption key management Support for Windows Server Core A new set of explicit server-level and database-level permissions for securables have been introduced in SQL Server 2014 to further enhance access and security:
A server-level permission that grants a login the ability to connect to all databases that currently exist and to any new databases that might be created in the future.
Another new server-level permission, IMPERSONATE ANY LOGIN allows a middle-tier process to impersonate the account of clients connecting to it as it connects to databases.
When granted, this new server-level permission allows a login to view data in all databases that the user can connect to.
Unlike the other permissions listed here, ALTER ANY DATABASE EVENT SESSION is a database-level permission. It is typically used to give a role the ability to read metadata associated with a database for monitoring purposes. A perfect example would be Microsoft System Center Operations Manager agents used to proactively monitor a SQL Server database.
SQL Server 2014 empowers a diverse set of hybrid-cloud opportunities that can reduce both capital and operational expenditures for an organization. These include backing up to the cloud, extending high availability to the cloud, hybrid application development, and improved on-premises disaster recovery. Let’s review the different types of hybrid-cloud solutions that organizations can take advantage of:
A new wizard in SQL Server 2014 allows a DBA to use SQL Server Management Studio to seamlessly deploy and transition a database from an on-premises deployment to a Windows Azure Virtual Machine. Windows Azure Virtual Machines are an Infrastructure-as-a-Service (IaaS) offering included in Windows Azure. The implementation is based on a few simple steps:
Specify the source connection settings such as the SQL Server instance, database name, and temporary location for backup files.
Provide Windows Azure sign-in credentials that include a management certificate.
Enter information for the Windows Azure Virtual Machine or, if you plan on deploying your database to an existing Windows Azure Virtual Machine, provide the DNS name for the cloud service and the credentials for the virtual machine and SQL Server instance.
Deploy a database to Windows Azure SQL Database
Another new feature in SQL Server 2014 allows a DBA to easily deploy an on-premises database to Windows Azure SQL Database. Windows Azure SQL Database is a relational-database service in the Windows Azure Platform-as-a-Service (PaaS) environment. The wizard used in this type of deployment can also be used to move databases from Windows Azure SQL Database to an on-premises SQL Server instance or to move databases from one instance of Windows Azure SQL Database to another. The deployment process is very straightforward. Only credentials associated with the Windows Azure SQL Database account are required; the wizard takes care of the rest.
As mentioned earlier, SQL Server 2014 and Windows Azure are tightly integrated, providing organizations with the ability to implement hybrid scenarios such as backing up on-premises databases to the cloud. To achieve this goal, all that is required is a URL and a storage key. Then, a straightforward policy can be created and used to back up a single database or all databases within a SQL Server instance directly to Windows Azure storage. This process can be automatic or manual. Windows Azure storage provides additional benefits through out-of-the-box geo-replication. The use of geo-replication protects databases because the backups are stored in multiple Windows Azure data centers around the world, hence offering superior disaster recovery. Finally, the backups stored in Windows Azure can be restored on Windows Azure Virtual Machines (should a terrible disaster transpire, taking out an organization’s on-premises data center).
Azure Many organizations need to account for offsite disaster recovery, but they do not have a secondary data center or their secondary data center is within close proximity to the primary data center. Therefore, a single disaster could potentially take out both the primary and the secondary data centers, causing a major outage. In these cases, hybrid cloud provides organizations better disaster-recovery scenarios, and these scenarios also lower RTO (recovery time objective) and increase RPO (recovery point objective). As mentioned earlier, the Add Azure Replica wizard in SQL Server 2014 can be used to create secondary AlwaysOn asynchronous replicas in Windows Azure Virtual Machines. Therefore, in the event of a disaster, a replica can be failed over to the Windows Azure public cloud because all transactions committed on-premises will be sent asynchronously to the Windows Azure replica.
An organization can take advantage of hybrid scenarios to extend and scale on-premises applications. For example, a physical retailer could use its on-premises SQL Server and supporting infrastructure to continue to serve physical retail transactions and utilize cloud-based Windows Azure infrastructure services to support online sales. Another scenario is a pizza organization that needs to scale to support the irregular peak demands of its business, which occur only on Super Bowl Sunday. The pizza maker could transition the web tier of its online pizza ordering system to Windows Azure while maintaining the back-end database on-premises. Windows Azure would provide the automatic scale-out functionality to support the irregular demand without the need to overprovision on-premises infrastructure, which would be costly and seldom used throughout the year.
Windows Azure Blob storage allows for a flexible and reliable backup option that does not require the overhead of traditional hardware management, including the hassle and additional costs associated with storing backups offsite. In addition, by leveraging a direct backup to Windows Azure Blob storage, you can bypass the 16-disk limit, which was a concern in the past. As you can see, SQL Server 2014 delivers many new capabilities for building hybrid-cloud solutions that use Microsoft’s Cloud OS vision. This vision entails a consistent experience with a common set of tools across the entire application life cycle, no matter where you are running your data platform.
Get Updates on Tech posts, Interview & Certification questions and training schedules