Microsoft's SQL Server family is well into its second decade as a leading data platform and has benefited from frequent new releases that have allowed SQL Server to keep up with the fast-paced changes in modern computing. SQL Server 2014 delivers important enhancements that cover performance, high availability, security and compliance, disaster recovery, and cloud readiness. Progent's Microsoft-certified consulting team offers affordable remote and on-premises consulting, application development, and troubleshooting services to help organizations of any size to derive maximum business value from their SQL Server 2014 deployment.
Versions of SQL Server 2014
Microsoft offers SQL Server 2014 in a variety of additions that can meet the IT requirements and budgets of virtually any organization. All editions work best with the latest release of Windows Servers, and all support Windows Server Core for applications that require few resources and that can benefit from the added security of a minimal attack surface. Major editions of SQL Server 2014 include:
Progent can help you select the editions of SQL Server 2014 that make the most sense for your workloads and can help you integrate SQL Server 2014 with the latest version of Windows Server so you can access SQL Server's advanced feature set.
- SQL Server 2014 Express is a free edition of SQL Server 2014 designed for desktops and small web and server applications. SQL Server 2014 Express supports up to four cores, 1 GB of memory for each instance of the SQL Server Database Engine, and a maximum relational database size of 10 GB. SQL Server 2014 Express includes the complete version of SQL Server 2014 Management Studio (SSMS) and offers a rich set of development and management tools including CLR, FileStream, FileTable, Microsoft Visual Studio Integration, Intellisense, Date and Time datatypes, and Native XML support. The Express edition of SQL Server 2014 also supports the new Delayed Durability feature.
- 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 adds support for basic Online Transaction Processing (OLTP); basic reporting and analytics; limited data warehousing and data mining capability; StreamInsight Standard Edition, and basic AlwaysOn Failover Clustering. The Standard edition also supports Buffer Pool Extension, backup compression, backup encryption, and Smart Backup.
- SQL Server 2014 Business Intelligence adds Master Data Services, advanced analysis services, PowerPivot for SharePoint and Excel, comprehensive data mining, and advanced reporting services.
- SQL Server 2014 Enterprise allows the host OS maximum supported RAM for the Database Engine and adds enhanced performance with in-memory OLTP and in-memory ColumnStore, Resource Governor, and data compression; enhanced security with Transparent Database Encryption (TDE), Extensible Key Management; advanced data warehousing; fine-grained auditing; plus premium StreamInsight and advanced High Availability (HA) including AlwaysOn Availability Groups and mirrored backups.
In-Memory Technology Enhances OLTP and Data Warehousing Performance
Microsoft's enhanced in-memory technology, code-named Hekaton, is the most prominent new feature of SQL Server 2014, offering a reported 3x to 30x performance improvement in OLTP and data warehousing applications. By extending in-memory technology to support Online Transaction Processing, SQL Server 2014 Enterprise edition becomes the first release of SQL Server to support Microsoft's in-memory technology for all classes of applications including OLTP, data warehousing, and analytics. Because in-memory technology is integrated within SQL Server 2014, you can achieve dramatic performance increases transparently without the need to redesign critical applications or purchase add-on software. SQL Server Management Studio (SSMS) includes a migration advisory to determine heavily accessed tables that are the best candidates for being defined as memory optimized. You can keep "cold data" on disk and "hot data" in memory to make the most efficient use of your hardware resources, and a SQL Server 2014 query can transparently reference memory-optimized tables along with disk-resident tables.
SQL Server 2014 Enterprise enhances in-memory ColumnStore by making it writable. This speeds up clustered ColumnStore operations including Selects, Inserts, Updates, and Deletes for improved data warehousing performance. SQL Server 2014 Enterprise also supports greater compression with in-memory ColumnStore, which significantly reduces data warehousing storage requirements. In addition, the new Buffer Pool Extension capability of SQL Server 2014 Enterprise permits you to expand buffer pools transparently to non-volatile SSDs so they can operate as a cache between RAM and moving-head disks for fast and durable file I/O.
Azure Cloud Support for Hybrid Environments
SQL Server 2014's close integration with Microsoft Azure enables hybrid deployment scenarios that offer the versatility of local control as well as the benefits of cloud computing including lower CAPEX, reduced management overhead, higher availability, enhanced security and compliance, and enterprise-class disaster recovery. You can back up and encrypt your local SQL Server 2014 databases manually or automatically to Azure Storage either at the database or instance level, and you can restore backups to an Azure VM in case your local data center experiences a failure. Unified management of your hybrid backup solution for SQL Server 2014 is supported by SQL Server Management Studio or System Center 2012 R2.
Cloud Backup can simplify management, cut infrastructure costs, and improve disaster recovery
SQL Server Management Studio (SSMS) simplifies the management of backups by requiring only that you specify retention periods and storage locations. SQL Server Managed Backup then automatically schedules, runs, and manages backups based on policy and allows point-in-time restores. Azure follows best practices by automatically replicating backups in different geographical locations. You have the option of specifying backup geographies in accordance with your disaster recovery plan. SSMS also includes new wizards that offer easy point-and-click controls for deploying AlwaysOn secondaries to Microsoft Azure virtual machines.
For enhanced security and compliance, you have the option of encrypting all cloud data via Transparent Database Encryption (TDE) certificates and storing the TDE keys in a local server that is physically secured and locally backed up. This protects your cloud data even in the event that your account credentials are stolen from your cloud storage account.
Utilizing Microsoft Azure backup can be a particularly compelling option for smaller companies because it simplifies management, dramatically reduces investment in infrastructure, is easy to expand, and offers high levels of security and recoverability. Azure Storage is a convenient way to achieve geographic dispersion, which is fundamental to any viable disaster recovery plan, without incurring the expense and hassle of managing several data centers.
Progent's disaster recovery planning consultants can help you incorporate Azure Backup into an efficient recovery plan that will allow you to restore access to critical SQL Server 2014 services and databases quickly in the event of a disaster. (Learn about Progent's Microsoft Azure planning and cloud integration consulting services.)
AlwaysOn Availability Groups and AlwaysOn Failover Cluster Instances were first supported by SQL Server 2012 to deliver enhanced high availability (HA) for critical SQL Server applications. SQL Server 2014 Enterprise further enhances AlwaysOn technology by improving HA, supporting better resource utilization, and streamlining deployment and administration. SQL Server 2014 Enterprise supports Windows Server 2012 R2 Clustered Shared Volume and online VHDX and doubles the maximum number of readable secondary replicas from 4 to 8. For improved utilization, readable secondary replicas can be accessed for read workloads such as BI reporting if they become disconnected from the primary replica or during the loss of cluster quorum. Also, Failover cluster instances (FCIs) are now able to use Cluster Shared Volumes as cluster shared disks.
SQL Server 2014 introduces wizards that make it easy to deploy replicas locally and to the Microsoft Cloud. SQL Server Management Studio (SSMS) and Microsoft System Center allow you to track and manage onsite and cloud-based replicas. To learn about Progent's consulting support for Microsoft System Center, refer to Microsoft System Center consulting services.
Improved Scale and Performance with Windows Server 2012 R2
SQL Server 2014 leverages Windows Server 2012 R2 technology for enhanced scale, performance, and efficiency. When used with Windows Server 2012 R2, SQL Server 2014 supports up to 640 logical processors and 4 TB of physical RAM plus up to 64 virtual processors and 1 TB of RAM for every virtual machine. Windows Server 2012 R2's ability to abstract the network layer makes it easy to migrate SQL Server 2014 between data centers. The Storage Spaces feature of Windows Server 2012 R2 for storage virtualization enables you to create pools and tiers of storage so you can utilize fast, expensive storage for frequently accessed data ("so-called "hot data") and slower, conventional storage for "cold data." This storage tiering allows higher and more predictable performance and can also improve uptime. For information about Progent's support for Windows Server 2012 R2, refer to Windows Server 2012 R2 consulting support.
New Security Features
SQL Server 2014 includes security enhancements that can help organizations comply with increasingly strict government regulations and industry requirements. Important security enhancements include backup encryption, enhanced encryption key management, and expanded role separation.
Backup Encryption: SQL Server 2014 can encrypt data as it is being backed up, eliminating the previous need for third-party tools to accomplish this. Supported encryption algorithms include AES 128, AES 192, AES 256, and Triple DES. The Asymmetric keys used to encrypt backups must be from a Microsoft Cryptographic API (MSCAPI) provider in Extensible Key Management. Because SQL Server 2014 data is compressed before it is encrypted, this enhancement provides significant storage savings compared to the TDE encryption method offered in previous releases of SQL Server. Backup encryption works locally and with Microsoft Azure Backup.
Azure Key Vault: The Azure Key vault is a key management service that uses FIPS-validated Hardware Security Modules (HSMs) to protect Azure-hosted applications and services. The Azure Key vault service offers more secure encryption of SQL Server databases in Azure virtual machines. The HSMs that process keys are located in the same Azure datacenters as the hosted applications, reducing latency and offering better reliability than keys located on-premises. The Extensible Key Management provider architecture of SQL Server 2014 allows data encryption keys for local applications to be stored externally (for example, in the Azure Key Vault). This allows you to manage keys and data separately to improve compliance.
More Granular Role Separation: New roles introduced in SQL Server 2014 provide four additional permissions that allow more leeway in assigning privileges among users or roles. This can help organizations avoid giving any single user or role excessive control over a vital process or program. Role separation makes it easier to comply with current and impending security regulations and standards by providing more options for assigning roles and sub-roles. As an example, role separation in SQL Server 2014 makes it possible to allow a DBA to manage a database without being permitted to view sensitive information.
Progent's certified data security consultants can help you plan and validate a security strategy for SQL Server 2014 that meets required regulatory standards and protects sensitive business data and personal information. Progent offers a portfolio of affordable security assessment packages and security penetration testing packages that can help you assess, improve, and validate the security profiles of your network infrastructure and your SQL Server applications.
Additional New Features of SQL Server 2014
SQL Server 2014 delivers other improvements that expand the ability of DBAs to tune application performance. Enhancements include:
Expanded Resource Governance: Introduced with SQL Server 2008 and previously limited to CPU and memory settings, Resource Governance has been extended in SQL Server 2014 to include disk I/O. You can now specify minimum and maximum I/Os per second for each disk volume within a resource pool. By allowing organizations to enforce limits on their use of CPU, memory and I/O, Resource Governance in SQL Server 2014 makes it easier for administrators to achieve consistent performance in multi-tenant environments, support a wide range of workloads, and prevent runaway queries from hogging the system.
Delayed Durability: By default, SQL Server 2014 transactions are logged to disk before they are reported as successful. These transactions are durable and synchronous. SQL Server 2014 offers a performance-tuning option known as "delayed durability" where the writes to disk are deferred, taking place asynchronously in the background while control is returned to the client before the disk log is updated. For applications with a large percentage of small transactions and a moderate tolerance for data loss, asynchronous delayed durable transactions can prevent log-update bottlenecks and improve performance.
Redesigned Cardinality Estimator: Cardinality estimation attempts to predict the number of rows in a query result and is a critical aspect of performance tuning. The cardinality estimation logic of SQL Server 2014 has been redesigned, for the first time in almost a decade, to improve query execution plan quality and associated workload performance for contemporary OLTP and data warehousing applications.
Progent's Consulting Services for SQL Server 2014
Progent can provide cost-effective remote and on-premises consulting services to help organizations of any size to design, integrate, administer, and troubleshoot a SQL Server 2014 deployment that maximizes business value. Progent can help you evaluate the costs and benefits of migrating from earlier versions of SQL Server or from another data platform to SQL Server 2014 and can assist you to deploy SQL Server 2014 within a local data center, in the cloud, or as a hybrid ecosystem that includes Microsoft Azure. Progent can also help you integrate SQL Server 2014 optimally with other Microsoft platforms by providing services that include Windows Server 2012 R2 consulting, Hyper-V virtualization expertise, Active Directory planning and support, Exchange Server consulting, SharePoint application development and integration, Microsoft Dynamics help, Skype for Business support, Microsoft Lync Server 2013 integration, and Microsoft System Center management support services. Progent's consulting services are available on an as-needed basis to supplement the skills of your in-house staff or Progent can provide comprehensive outsourced or co-sourced project management services.
Additional consulting services available from Progent to support your SQL Server 2014 solution include:
- Migrating Databases and Applications: Progent's RDBMS consultants can provide database and application migration services for outdated releases SQL Server and also offer Oracle consulting services, MySQL development and support, plus Microsoft Access consulting and training. Progent's experience with database development and support can help ensure that your migration to SQL Server 2014 is completed on schedule and within budget. If you currently run SQL Server 2014 and are interested in upgrading your database ecosystem, Progent can help you plan and carry out an efficient migration by providing SQL Server 2017 upgrade consulting services or SQL Server 2019 migration expertise.
- DBA Services: Progent's Database Administration consultants offer outsourced or co-source DBA support for SQL Server 2014 environments. Services available from Progent's experienced DBA consultants include capacity planning, performance tuning, data security strategies, compliance validation, configuration and account management, backup solutions, patch and upgrade automation, system monitoring, management reporting, and database consolidation.
- Business Intelligence and Reporting: Progent's business analytics consultants can help you use a variety of SQL Server 2014 Reporting Services (SSRS) tools like Report Designer and Report Builder to create custom business intelligence (BI) reports. Progent can help you set up and run Power View to visualize PowerPivot models and SSAS tabular-mode databases. Progent also offers expert support for familiar platforms such as Crystal Reports, Report Writer, and FRx. (Find out about Progent's Power BI consulting and training services for using SQL Server SSRS and SSAS data to create visualizations for Power BI analytical reports and dashboards.)
- Application Design and Programming: Progent's application developers can help you plan, develop, test, manage, optimize, and repair custom SQL Server 2014 applications. Progent can provide assistance at every stage of an application's life cycle by delivering services that include requirements analysis, features specification, logical and physical database modeling and data flow, ETL development, T-SQL programming, database partitioning, tuning, documentation and training.
- Network Architecture Planning and Support: Progent's Cisco-certified CCIE network infrastructure consultants can help you plan, implement, expand, manage and troubleshoot a cost-effective, secure, high-performance, manageable, and fault-tolerant network infrastructure that accommodates your SQL Server 2014 environment's local and cloud-based resources and that supports remote and mobile workers and the modern Bring-Your-Own-Device (BYOD) style of computing.
Contact Progent to Find Out More
To learn more about Progent's consulting services for SQL Server 2014, call 1-800-993-9400 or visit Contact Progent.
If you currently need online support from a Microsoft-certified consultant, visit Progent's Remote Network Support Services.