The market-leading SQL Server product line continues to benefit from frequent enhancements that allow Microsoft's strategic data platform to keep pace with the fast-changing paradigms of modern information technology. SQL Server 2014 incorporates significant improvements in key areas including performance, availability, security, and hybrid-cloud optimization. Progent's certified consulting team can provide efficient online and onsite planning, configuration, management, troubleshooting, and application development services for SQL Server 2014.
Progent can help you migrate your data and applications from earlier versions of SQL Server or from other database management environments. Progent can also help you integrate various versions of SQL Server 2014, from SQL Server Express to SQL Server Enterprise Edition, with Microsoft's critical OS, application, and management platforms including Windows Server and Hyper-V, Exchange, SharePoint, Dynamics, Lync or Skype for Business, and System Center. Progent can help you deploy SQL Server 2014 in your local data center, across private and public clouds, or in hybrid environments that span local and cloud-based infrastructure.
Editions of SQL Server 2014
SQL Server 2014 is available in several editions designed to fit the needs of organizations of any size and to handle virtually any database application that requires performance, security, high availability, and easy management. SQL Server 2014 Express is a free version of SQL Server that supports up to four cores, can power desktop, web and small server applications and that includes the full version of SQL Server 2014 Management Studio. Express supports a rich set of development and management tools including CLR, FileStream and FileTable, Microsoft Visual Studio Integration, Intellisense, Date and Time datatypes, and Native XML support. SQL Server 2014 Standard supports up to 16 cores 128 GB of memory for the Database Engine, and a max relational database size of 524 PB. The Standard edition also adds basic support for OLTP plus basic reporting/analytics, data warehousing, data mining. and basic AlwaysOn High Availability (HA), which includes 2-node Failover Clustering. In addition, the Standard edition supports Buffer Pool Extension (PBE) to solid-state disks, StreamInsight Standard Edition, backup encryption and compression, and Smart Backup. SQL Server 2014 Business Intelligence includes support for Master Data Services, advanced analysis services and data mining, PowerPivot for SharePoint and Excel, and comprehensive reporting services. SQL Server 2014 Enterprise supports the maximum RAM allowed by the operating system, and delivers higher performance with in-memory OLTP and in-memory ColumnStore for data warehousing. The Enterprise edition also includes Resource Governor, data compression, Transparent Database Encryption, Extensible Key Management, advanced auditing, and premium StreamInsight. Availability is improved with AlwaysOn Availability Groups and mirrored backups.
Enhanced In-Memory Technology
SQL Server 2014 is the first version of Microsoft SQL Server that incorporates in-memory technology that operates transparently across all workloads including OLTP, data warehousing, and data analytics. A new In-Memory OLTP engine (formerly code-named Hekaton), the headline feature of SQL Server 2014, can improve OLTP performance by a reported 30x. Because in-memory capability is built into SQL Server 2014, there is no need to rewrite entire applications, purchase expensive add-ons, or learn new development tools or APIs. You simply define a heavily accessed table as memory optimized. A migration advisory built into SQL Server Management Studio makes it simple to determine what to migrate to memory for maximum impact on performance. You can leverage your hardware resources by keeping "cold" data on disk and "hot" data in memory, and you can transparently reference both memory-optimized tables and disk-based tables in a query.
In addition, in-memory ColumnStore has been enhanced in SQL Server 2014 Enterprise by being updatable rather than read-only, so you get the performance associated with a clustered ColumnStore for operations such as Selects, Inserts, Updates, and Deletes. In-memory ColumnStore also allows greater compression in SQL Server 2014 to reduce storage requirements for data warehousing. The new Buffer Pool Extension (BPE) feature of SQL Server 2014 allows you to expand buffer pools seamlessly to non-volatile solid-state disks (SSDs) in order to provide caching between high-speed RAM and slow mechanical disks to achieve faster file I/O without sacrificing durability and without requiring the purchase of additional high-cost RAM.
Unified HA/DR
SQL Server 2012 introduced AlwaysOn Failover Cluster Instances and AlwaysOn Availability Groups to provide enterprise-class HA for mission-critical applications. SQL Server 2014 takes AlwaysOn technology to the next level by offering enhanced HA, improved utilization, and easier deployment and management. SQL Server 2014 supports Windows Server 2012 R2 Clustered Shared Volume and online VHDX, and the maximum number of readable secondary replicas has been increased from 4 to 8. When disconnected from the primary replica or during cluster quorum loss, readable secondary replicas now remain available for read workloads. In addition, Failover cluster instances (FCIs) can now use Cluster Shared Volumes (CSVs) as cluster shared disks.
As with SQL Server 2012, you can deploy an advanced HA solution with commodity disk storage rather than expensive SANs, and you can improve resource utilization by offloading read functions like Business Intelligence (BI) reporting to the secondaries while at the same time using the secondaries for backups. In addition, SQL Server 2014 includes new wizards to simplify the deployment of replicas on-premises and to the Microsoft Azure cloud. (Learn about Progent's Microsoft Azure planning and cloud integration consulting services.) You can also monitor and manage replicas located on site or in the cloud using SQL Server Management Studio (SSMS) or Microsoft System Center. For information about Progent's consulting services for System Center, see System Center planning and support.
The diagram illustrates a typical AlwaysOn HA configuration that features a synchronous replica for disaster recovery (DR) and an asynchronous replica for backups, where both secondaries are actively used to improve utilization. For a description of the AlwaysOn capabilities introduced in SQL Server 2012, see SQL Server 2012 consulting services.
Improved Capacity and Performance via Windows Server 2012 R2 Integration
SQL Server 2014 takes advantage of Windows Server 2012 R2 to achieve enhanced scalability of processing, networking and storage resources. SQL Server 2014 accommodates as many as 640 logical processors and 4TB of RAM in a physical environment and up to 64 virtual processors and 1TB of memory for each virtual machine (VM). SQL Server 2014 also utilizes Windows Server 2012 R2 for network virtualization by abstracting the networking layer, which facilitates the migration of SQL Server 2014 between data centers. Operation with Windows Server 2012 R2 also supports storage virtualization with Storage Spaces, which allows you to create pools of storage and storage tiers that permit "hot data" to utilize premium storage and relegates "cold data" to standard storage. This ensures higher and more consistent performance and improves availability. To learn more about the advantages of Windows Server 2012 R2, see Windows Server 2012 R2 planning and consulting services.
Security and Compliance Enhancements
SQL Server 2014 offers important new security features including backup encryption, expanded role separation, and enhanced encryption key management.
Backup Encryption: SQL Server 2014 introduces the ability to encrypt data while it is being backed up. You specify the encryption algorithm and the Certificate or Asymmetric Key encryptor when you create the backup file. Because the data is compressed prior to being encrypted, this new feature offers excellent compression factors that were unavailable using the older method of SQL Server Transparent Database Encryption (TDE). Encrypted backup supports both local and Microsoft Azure cloud destinations, and encryption options can be configured for the new SQL Server Managed Backup to Microsoft Azure feature.
Expanded Role Separation: SQL Server 2014 introduces four new permissions that allow organizations to distribute privileges among users or roles, eliminating the need to give any one user or role complete control over a critical business process or software program. Role separation makes it easier to meet increasingly strict regulatory compliance improve compliance by delivering new capabilities for creating roles and sub-roles. Enhanced separation of duties makes it possible, for example, for a database administrator can now manage the data without being allowed to see sensitive data or personal information.
Extensible Key Management (EKM) Improvements: As an alternative to storing symmetric data encryption keys on SQL Server, the Extensible Key Management (EKM) provider architecture of SQL Server 2014 can protect data encryption keys using an asymmetric key stored in an external cryptographic provider such as the Microsoft Azure Key Vault. This enhances compliance by allowing you to manage keys and data separately. The Azure Key Vault service also offers the option of using strictly controlled and monitored Hardware Security Modules (HSMs) for additional protection of asymmetric encryption keys.
Progent's certified network security consultants can help you create and validate a security strategy for SQL Server 2014 that complies with regulatory requirements and with your own internal standards. Progent also offers a variety of value-prices security vulnerability assessment packages and penetration testing packages designed to evaluate the security profiles of your network infrastructure and your web-facing software applications.
Hybrid Cloud Support with Microsoft Azure
SQL Server 2014 is more closely integrated with Microsoft Azure to support hybrid on-premises/cloud deployments that can lower infrastructure cost, improve availability, simplify management overhead, enhance security, and provide advanced disaster recovery. On-premises SQL Server 2014 databases can be encrypted and backed up automatically or manually to Azure Storage at the database or instance level, and backups can be restored to an Azure Virtual Machine in the event of an on-premises failure. SQL Server 2014 allows you to use SQL Server Management Studio (SSMS) or System Center 2012 R2 to manage your backup environment.
Backup management is simplified with SSMS because you only have to define retention periods and storage locations. SQL Server Managed Backup schedules, executes, and maintains policy-based backups automatically and supports point-in-time restores for the specified retention time period. Microsoft Azure automatically replicates backups in multiple geographies, and you can specify backup locations based on your disaster recovery strategy. New wizards in SSMS simplify the deployment of AlwaysOn secondaries to Azure VMs with a point-and-click experience.
To improve security, you can encrypt all data in the public cloud using TDE certificates and store the TDE keys in a physically secure on-premises machine that is backed up locally. This way, even if your account credentials were to be stolen from your cloud storage account, your data remains protected.
Azure backup offers an especially attractive DR solution for smaller organizations by providing an affordable, secure and easily scalable enterprise-class infrastructure that includes geographic dispersion without the cost and effort associated with building or managing multiple data centers. .
Progent's certified network security consultants can help you create and validate a security strategy for SQL Server 2014 that complies with regulatory requirements and with your own internal standards. Progent also offers a variety of value-prices security vulnerability assessment packages and penetration testing packages designed to evaluate the security profiles of your network infrastructure and your web-facing software applications.
Additional Enhancements to SQL Server 2014
SQL Server 2014 includes additional enhancements that allow administrators to tune the performance of critical applications. Improvements include:
Resource Governance Expanded to Include I/O: The SQL Server 2012 Resource Governor has been expanded in SQL Server 2014 to enforce resource limits for the physical I/Os issued for user threads within a specified resource pool. In SQL Server 2014, resource pools now let you configure minimum and maximum IOPS (IOs Per Second) for each disk volume, allowing organizations to ensure more predictable performance for critical workloads and to balance I/O distribution in multi-tenant environments.
Delayed Durability: SQL Server 2014 allows you to reduce disk latency by designating transactions as delayed durable, sometimes referred to as lazy commit. Unlike a synchronous durable transaction, a delayed durable transaction is asynchronous in that it returns control to the client before the record of the transaction is logged to disk. SQL Server 2014 allows you to control durability at the database level, COMMIT level, or ATOMIC block level, and an application can support a combination of durable and delayed durable transactions. Delayed durability can help relieve performance bottlenecks for transaction-intensive workloads with significant I/O contention for transaction log writes, but applications must be able to tolerate some data loss.
Enhanced Cardinality Estimation: Cardinality estimation, which predicts the number of rows in a query result, is an important tool for performance tuning. SQL Server 2014 incorporates advanced cardinality estimation logic to enhance the quality of query plans and improve query performance for the new generation of OLTP and data warehousing workloads.
How Progent Can Help You with SQL Server 2014
Progent's IT consulting team offers affordable online and onsite expertise and technical support services to help you plan, deploy, manage and troubleshoot your SQL Server 2014 environment. Progent can help you assess the business benefits and technical requirements for you to upgrade to SQL Server 2014 from earlier versions of SQL Server and can help you understand the pros and cons of various deployment scenarios such as an on-premises data center, a system based on Microsoft Azure or other cloud environment, or a hybrid solution. Progent can help you migrate your databases and applications, optimize your network infrastructure, tune the performance of critical applications, implement efficient management tools including Microsoft System Center, address security and compliance issues, and create a disaster recovery plan that includes high availability and transparent backup. If you currently operate SQL Server 2014, Progent can provide comprehensive SQL Server 2017 migration expertise and SQL Server 2019 migration consulting.
Services available from Progent related to SQL Server 2014 include:
Contact Progent for SQL Server Expertise
To find out more about how Progent can help you with SQL Server 2014, phone
If you need immediate online help from a Microsoft-certified consultant, see Progent's Online Network Support.
Find out more about Progent's Consulting Services for Microsoft .NET Server Products.