SQL Server 2019 is an advanced, on-premises release of Microsoft's primary RDBM platform. The headline feature is the integration of Microsoft SQL Server 2019 with Apache Spark, Hadoop Distributed File System (HDFS), and additional Linux-powered big data components to support Big Data Clusters. The Big Data Cluster supports data-intensive AI and machine learning tasks on the high-volume data stored in HDFS and Microsoft SQL Server storage pools. You can implement Big Data Clusters as containers in self-hosted Kubernetes clusters, such as AKS on Azure Stack, or in a cloud with a managed Kubernetes service like Microsoft Azure AKS.
Microsoft SQL 2019 allows you to combine your relational data and unstructured big data clusters
Microsoft SQL 2019 also delivers enhancements in the core SQL Server database technology such as feature parity on Linux machines. Other important innovations improve SSAS and SQL Server Reporting Services (SSRS).
Since Microsoft SQL Server 2008 and SQL Server 2008 R2 have reached end of support (EOS) status, moving to a current version of Microsoft SQL Server is a priority for organizations concerned about security, manageability, and competitiveness. Progent has two decades of experience planning, deploying, tuning, and debugging SQL Server environments and can help companies of any size to perform a cost-effective migration to SQL Server 2019. Progent's Microsoft Gold-certified SQL Server consultants can assist your organization to assess the benefits for transitioning to Microsoft SQL Server 2019 from any earlier release of SQL Server and Progent offers fast online or on-premises access to top-tier consultants in system architecture, software programming, DBA services, and data security. Progent can assist your business to configure Microsoft SQL Server 2019 on premises, in a private or public cloud, or in a hybrid on-prem/cloud network model.
You can work with Progent's consultants on an as-needed basis for jobs like system architecture review or debugging or you can get comprehensive project management and co-management services to make sure your Microsoft SQL Server 2019 initiative is completed on schedule and on budget. Progent can provide the skills of certified information security experts, disaster recovery preparedness specialists, and database application designers and programmers to ensure your SQL Server 2019 deployment provides maximum business value.
Progent's Microsoft Gold-certified consultants can also assist your organization to configure SQL Server 2019 to work in conjunction with additional Microsoft products and services by providing support that includes Microsoft Windows Server 2019 integration consulting, Windows Hyper-V configuration consulting, Exchange Server 2019 integration consulting, SharePoint 2019 development expertise, hybrid integration with Azure cloud services, Microsoft Skype for Business configuration expertise, Microsoft Dynamics GP/Great Plains custom reporting consulting, and Microsoft System Center Operations Manager (SCOM) consulting.
Progent's veteran software developers can help you to plan, produce, pilot test, deploy, and troubleshoot applications that utilize SQL Server 2019 and Progent can provide guidance in data modeling, (Online Transaction Processing, Data Warehousing, Data Mining, ETL, optimization, fault tolerance, backup/restore, reporting, and integration support for Amazon MWS. To learn more about Progent's SQL Server programming and troubleshooting services, visit SQL Server application programming experts.
SQL Server 2019 Versions
SQL Server 2019 is available in five basic editions distinguished by price, performance, scale and licensing rules. Progent can help you decide which editions make sense for your needs, and Progent can also help you simplify license management.
SQL Server 2019 Enterprise is the high-end version of SQL Server 2019 and is designed for critical business applications requiring the highest advanced levels of scale, reliability, and throughput. Processing power and RAM permitted per database are limited only by the operating system limit, the number of VMs allowed is unlimited, and database size can be as high as 524 PB. HA features offered only on the Enterprise edition of Microsoft SQL Server 2019 include Always On availability groups, online file restore, online indexing, resumable online index rebuilds, fast recovery, and live add memory and processor. Expandability and throughput features for the Enterprise edition of Microsoft SQL Server 2019 include in-RAM database, resource governance, auto tuning, read ahead plus memory grant feedback. The Enterprise edition of Microsoft SQL Server 2019 also offers a richer set of data warehousing services, Analysis Services and Reporting Services than other editions.
Microsoft SQL Server 2019 Developer edition is basically identical to the Enterprise edition, but is restricted for use exclusively for application development and validation. The Developeredition is not permitted for use in a production server. Microsoft SQL Server 2019 Developer handles only one client for SQL Server Distributed Replay simulation.
SQL Server 2019 Standard version is designed for departments and small businesses who require an entry-level, low-overhead solution for information management and business intelligence. SQL Server 2019 Standard version supports all the security features of the Enterprise edition, but CPU capacity is restricted to the lesser of four sockets or 24 cores. For each instance of SQL Server Database Engine, the most RAM for the buffer pool cache is 128 GB, and maximum memory-optimized data size is 32 GB. In contrast to the Enterprise version, the Standard version of Microsoft SQL Server 2019 does not include advanced R and Python support and does not include Machine Learning Services, but supports the other development features as well as the management tools the come with the Enterprise version. Advanced features like transparent data encryption and Big Data Clusters are now included with the Standard version.
Microsoft SQL Server 2019 Web version is an inexpensive package intended for web hosting companies. CPU power is restricted to the lesser of four sockets or 16 cores, max buffer pool RAM is 64 MB, and maximum RAM per instance is 64 GB. In contrast to the Standard edition of Microsoft SQL Server 2019, Microsoft SQL Server 2019 Web edition does not support Always On failover cluster instances, hybrid backup, or accelerated database recovery. The Web edition also does not support transparent database encryption. You can profile SQL Server Web by using Microsoft SQL Server Enterprise or SQL Server Standard versions, and SQL Server Web includes the other management utilities included with the Standard version. Analysis Services, tabular and multidimensional models, BI Services, and certain Reporting Services are not provided.
Microsoft SQL Server 2019 Express, available in a few variants, is a free entry-level edition of SQL Server designed for learning or for creating PC, small-server, and client apps that do not need an advanced database. Compute scale is limited to the smaller of 1 socket or 4 cores, maximum buffer pool cache memory is 1.41 MB, and top RAM-optimized data is 352 MB. If applications created with SQL Server 2019 Express expand to call for more resources or extra functionality, you can migrate seamlessly to more advanced versions of SQL Server. Microsoft SQL Server 2019 Express with Advanced Services includes Basic R and SQL Graph integration, the distributed replay management tool, and SSDT. Microsoft SQL Server 2019 Express with Advanced Services can also support a SQL Server Reporting Services database. SQL Server Express LocalDB is a ultra-lite version of Express that runs in user mode and can be deployed without configuration and with virtually no prerequisites. The LocalDB version of Express includes the programmability features of other Express releases but does not allow in-memory OLTP.
Elements of SQL Server 2019
The SQL Server 2019 Installation Wizard allows you to choose which of the SQL Server components you want to include in your deployment. These components include:
The Database Engine is the core of SQL Server's services for processing, holding, and securing data. The Database Engine is responsible for replication, full-text search, utilities for managing relational data, supporting in-database analytics, integrating PolyBase for working with external data sources such as Hadoop, and Machine Learning Services for analyzing SQL Server's relational data using Python and R scripts.
SQL Server Analysis Services (SSAS)
SQL SQL Server Analysis Services (SSAS) is an OLAP and data mining toolset for deriving useful insights from data dispersed across separate databases or in various files or tables.
SQL Server Reporting Services
SQL Server Reporting Services (SSRS) provides a library of local tools and services that allow organizations to build, share, and manage tabular, matrix, graphical, and unstructured reports. You can build responsive-design mobile reports with Microsoft's Mobile Report Publisher app and use integrated Power BI apps for handheld endpoints to view the data you have in SQL Server Reporting Services.
You can access Reporting Services 2019 data via Power BI apps for mobile endpoints
Integration Services is a set of programmable objects and code-less graphical utilities for migrating data from a broad range of sources. ETL processes can be applied to XML data files, flat files, and relational data sources. These services can be used for duplicating or downloading files, loading data warehouses, cleansing and mining data, and controlling Microsoft SQL Server data and objects.
Master Data Services
Master Data Services (MDS) is SQL Server's Master Data Management (MDM) solution that enables you to create a single visualization of corporate-wide data, synchronizing data sources to avoid duplication and tracking models to preserve consistency. A Microsoft Excel add-in makes it easy to share the master data set throughout an organization.
In-Database Machine Learning Services
SQL Server 2019 Machine Learning Services enables you to execute Python and R scripts using relational data without moving data out of SQL Server or across the network to another server. The Microsoft Python and R packages can be used to train and deploy machine learning models.
New and Improved Features of SQL Server 2019
The most prominent new feature for Microsoft SQL Server 2019 is Big Data Clusters, which enables you to work with big data from Transact-SQL or Spark for machine learning solutions. Other new or enhanced features impact key technologies such as the SQL Server Database Engine, feature equivalence between SQL Server on Linux and SQL Server on Windows, SSAS, and SQL Server Reporting Services.
Big Data Clusters
trusted execution environment Server 2019 debuts the Big Data Cluster, a cluster of Linux containers orchestrated by Kubernetes. The Big Data Cluster enables you to configure and run massively scalable clusters in connected SQL Server, Apache Spark, and Hadoop Distributed File System (HDFS) containers managed by Kubernetes. Since these components can run concurrently, The Big Data Clusters technology permits you to analyze big data from either Transact-SQL or Spark and makes it easy to combine and process your internal SQL Server relational data with unstructured big data. Deployment scenarios include data virtualization for processing outside data silos without transferring or copying the data, data lakes created with HDFS pools to house big data, and scale-out data marts to spread data from a variety of sources across data pool nodes. You can use Spark or integrated R, Python, Scala, and Java tools in SQL Server 2019 for AI and machine learning tasks on the data stored in HDFS storage pools.
Big Data Clusters allow you to combine and analyze your relational data with unstructured big data
Additional Improvements to the SQL Server 2019 Database Engine
Other enhancements to the Microsoft SQL Server 2019 Database Engine address smart databases, the developer experience, cybersecurity, high-availability, and Linux and Windows feature set parity. Enhancements to the Microsoft SQL Server 2019 Database Engine include:
- Intelligent Query Processing (IQP) improves the performance of your current applications and is turned on by default on the current database compatibility level setting. Improvements include automatic memory grant correction, batch mode on rowstore, automatic transformation of scalar UDFs into relational expressions, and approximate query processing.
- Enhanced In-Memory Database features leverage modern hardware to improve throughput and scale with new capabilities like hybrid buffer pool, reduced Metadata contention in TempDB, and the ability to create snapshots of databases that include memory-optimized filegroups for In-Memory OLTP.
- Intelligent performance enhancements in Microsoft SQL Server 2019 impact typical bottlenecks and offer database configuration options to ensure more consistent performance across all applications. Enhancements include optimization within the database engine for indexes subject to high-concurrency inserts, Query Store plan forcing support for fast forward and static cursors, more granular control for configuring maximum memory limits for resource governance, reduction in extraneous recompilations, improved indirect checkpoint scalability, shared latch update concurrency for Page Free Space, and scheduler worker migration for more balanced processor utilization.
- Improvements to the developer environment include table and index partitioning for graph tables, UTF-8 import and export encoding, Microsoft Extensibility SDK for Java, new spatial reference identifiers (SRIDs) more closely aligned with GPS, and more specific warnings to streamline troubleshooting truncation issues in unsuccessful ETL processes.
- Cybersecurity enhancements include the Always Encrypted feature with secure enclaves functionality, which permits computations on plaintext data inside a secure server-side enclave instead of exclusively on on the client. This avoids the need to move data into and out of the database for processes like initial data encryption, which increases cryptographic speed. Also, certificate infrastructure management is built into the SQL Server Configuration Manager. This streamlines processes like viewing and validating certificates, managing certificates approaching expiration, and implementing certificates across machines in Availability Groups and Failover Clusters. A new integrated Data Discovery and Classification function helps you to categorize and label columns in user tables. This can help you find where sensitive data resides and to follow data privacy standards and compliance requirements.
Microsoft SQL Server 2019's secure enclaves offer a safe environment for working with private SQL data
Enhancements to SQL Server 2019 Analysis Services (SSAS)
- For improved availability, Microsoft SQL Server 2019 increases the max allowable synchronous replicas in an Availability Group (AG) from 3 to 5. You can set up this group of replicas with automatic failover within the group. You can deploy one primary replica plus four synchronous secondary replicas within a group. Also, secondary to primary replica read/write connection redirection for Always On Availability Groups permits client app connections to be directed to the primary replica regardless of what target server is called for the connections string. This capability adds replica connection redirection for implementations that are without an AG listener as with a multi-subnet deployment in the cloud.
- The SQL Server Database Engine recovery process has been redesigned in SQL Server 2019 with the Accelerated Database Recovery feature. This capability substantially reduces the time needed to finish a database recovery after a failover or other non-clean termination. Accelerated Database Recovery also slashes the time required to execute rollback of canceled long-duration transactions.
- Microsoft SQL Server on Linux approximates reaching feature equality with SQL Server on Windows through the addition of new SQL Server on Linux functionality such as support for replication, the MSDTC and RPC endpoint mapper, AD authentication, Machine Learning Services, PolyBase on Linux for non-Hadoop connectors, CDC support, plus improved scale in TempDB file size and auto growth.
SQL Server 2019 includes new and improved functions for SQL Server Analysis Services that boost performance, resource management, and client support. These improvements include calculation groups in tabular models for grouping common measure expressions, query interleaving, many-to-many relationships in tabular models, and the ability to attach a tabular model as an online operation.
Improvements to SQL Server 2019 Reporting Services
Improvements to SQL Server 2019 Reporting Services (SSRS) include:
How Progent Can Assist Your Business to Plan, Configure, and Manage Your Microsoft SQL Server 2019 Environment
- Support for Azure SQL Managed Instance (MI) allows you to host a database catalog for SQL Server 2019 Reporting Services in an Azure SQL Managed Instance hosted either in a VM or on-premises. You need to use database credentials for connecting with SQL Managed Instance.
- Support for connecting to Power BI datasets using either Microsoft Report Builder or SQL Server Data Tools (SSDT). You can publish those reports to SSRS 2019 by utilizing SQL Server 2019 Analysis Services connectivity.
- Azure Active Directory Application Proxy does away with the need to manage your own web application proxy to permit secure connectivity through the web or via mobile apps.
- Microsoft SQL Server 2019 now supports Transparent Database Encryption for the SQL Server 2019 Reporting Services (SSRS) catalog database for Enterprise and Standard versions.
Progent's Microsoft Gold-certified consultants can provide remote and on-premises expertise to help your business to design, implement, manage, optimize, and debug your Microsoft SQL Server 2019 deployment. Progent can assist you to assess the advantages of migrating to Microsoft SQL Server 2019 and can show you how to design an efficient architecture for an on-premises or hybrid solution.
Progent's SQL Server experts can assist you to migrate your data and apps, configure pilot environments to verify SQL Server 2019's capability to support your mission-critical applications, simplify management, design and implement a comprehensive cybersecurity plan that meets government and business mandates, accommodate mobile devices, and create a disaster preparedness/business continuity strategy that takes advantage of the modern HA features integrated into SQL Server 2019.
IT consulting services available from Progent for Microsoft SQL Server 2019 include:
Contact Progent for SQL Server 2019 Expertise
- Project Management and Co-management: Progent offers project management and co-management consulting services to help you to adhere to best practices to make sure your SQL Server 2019 project is carried out on time and on budget. Project offers support from IT experts to provide guidance for any phase of project management including requirements identification, readiness evaluation, solution architecture, upgrade planning, configuring test environments, deployment, tuning, documentation, training, administration, and as-needed remote or on-premises advice and debugging.
- Database Migration: Progent's seasoned Database Administration experts offer consulting supportfor all versions of SQL Server, including end-of-support (EOS) versions such as SQL Server 2008. Progent can also provide Oracle consulting, MySQL solution design and development, and Microsoft Access consulting. Progent has 20 years of background providing database consulting expertise and can assist you to move your databases and apps to Microsoft SQL Server 2019 rapidly and affordably.
- Integration with Other Microsoft Applications: Microsoft's extensive line of enterprise applications, operating systems, development environments, management utilities, and cloud services rely on Microsoft SQL Server as their underlying database technology. Progent's Microsoft Gold-certified experts can help you to deploy Microsoft SQL Server 2019 efficiently with additional Microsoft products by offering consulting services such as Windows Server 2019 upgrade expertise, SharePoint Server 2019 application design, Power BI business reporting training and support, Exchange Server 2019 migration expertise and Microsoft Office 365 Exchange Online hybrid integration consulting, and Active Directory design and troubleshooting.
- Business Intelligence and Reporting: Progent's business intelligence (BI) and custom reporting experts can help you to utilize Microsoft's business Intelligence (BI) stack to build and publish BI dashboards and reports that can be accessed securely via any device. Progent can help you create PowerPivot models and use Microsoft SQL Server 2019 Analysis Services to manage tabular databases. Progent's Power BI experts can help you to connect to Power BI datasets using Microsoft Report Builder or SQL Server Data Tools and post reports to SQL Server Reporting Services by using SSAS 2019 connections. Progent's experts can also assist your company to integrate Microsoft SQL Server 2019 with third-party BI reporting platforms such as or migrate from out-of-date reporting platforms like FRx.
- Network Infrastructure: Progent's Cisco-certified CCIE network architecture experts can help you to plan, implement, administer, and protect a high-availability and high-performance network infrastructure for your Microsoft SQL Server 2019 environment. Progent can help your company design and implement an efficient solution for any network model including onsite or hybrid.
- Security and Compliance: Progent's security and compliance specialists have been awarded advanced network security certifications such as CISA, ISSAP and CRISC. Progent can provide businesses of any sizes a range of security services including vulnerability audits, improvements, response, and RMM services to protect against known and new cyber assaults like ransomware and to verify compliance with government and industry mandates.
- Virtualization Expertise: Progent can help you to design and configure an affordable and manageable virtual environment for Microsoft SQL Server 2019 and can provide access to certified Hyper-V consultants, VMware vSphere and vCloud experts, VMware NSX-certified consultants, and Citrix XenServer consulting experts to help your business to deploy and maintain a virtualized IT system.
- ProSight Remote Monitoring and Management Services: Progent offers a selection of budget-friendly, fixed-price managed services intended to allow small and medium-size businesses to utilize the same cutting-edge technology deployed by some of the industry's top enterprises. Progent's ProSight portfolio of network monitoring and management services addresses all critical aspects of information technology such as email protection, behavioral-based threat defense, automated backup/recovery, network infrastructure monitoring and management, virtual server cloud hosting, and network resources documentation management.
- Disaster Disaster Recovery Preparedness: Progent's disaster recovery planning consultants can help you to develop and validate a disaster recovery plan that utilizes Microsoft SQL Server 2019's high-availability technology to maximize network uptime and shorten restore time for mission-critical applications. Progent's business continuity preparedness experts can assist your company to develop and test a detailed strategy to keep your essential business processes running in case of a catastrophe. The business continuity strategy deals with IT infrastructure but also covers the logistics associated with problems like emergency alert systems and processes, temporary office space and transportation, and regulatory mandates for reporting.
- Online Consulting and Debugging: Progent has two decades of background providing high-level consulting, software development, and troubleshooting services online. Using remote expertise allows you to reduce the costs and duration of network downtime. Progent has delivered remote IT services to businesses in every state in the U.S. Refer to Progent's customer testimonials.) Progent also offers on-premises support in most metropolitan areas throughout the U.S. and Canada.
To find out more about how Progent can assist your company with SQL Server 2019 planning, configuration administration, debugging, or application programming, call 1-800-993-9400 or see Contact Progent.
If you need immediate online help from a Microsoft-certified expert, see Progent's Remote Network Support.
Learn more about Progent's Consulting Services for Microsoft .NET Server Platforms.