SQL Server 2019 is an advanced, on-premises release of Microsoft's primary database engine. The headline enhancement is the integration of SQL Server 2019 with the Spark analytics engine, HDFS, and other Linux-based big data technologies to support Big Data Clusters. The Big Data Cluster enables massively scaled AI tasks on the high-volume data held in HDFS and SQL Server 2019 storage pools. You can deploy Big Data Clusters as containers in self-hosted Kubernetes clusters, such as AKS on Azure Stack, or in any cloud with a Kubernetes service such as Microsoft Azure AKS.
Microsoft SQL 2019 enables you to analyze your relational data and unstructured big data clusters
SQL 2019 also includes improvements in the fundamental SQL Server database technology like feature parity on Linux machines. Additional significant innovations impact SQL Server Analysis Services (SSAS) and SQL Server Reporting Services (SSRS).
Because Microsoft SQL Server 2008 and SQL Server 2008 R2 have reached EOS status, migrating to a current release of Microsoft SQL Server makes sense for organizations worried about security, compatibility, and productivity. Progent has twenty years of background designing, deploying, tuning, and troubleshooting SQL Server environments and can assist organizations of any size to carry out an efficient migration to SQL Server 2019. Progent's Microsoft Gold-certified SQL Server consultants can help you to assess the benefits for transitioning to Microsoft SQL Server 2019 from an older version of Microsoft SQL Server and Progent can provide rapid online or on-premises access to top-tier consultants in system design, software development, DBA services, and cybersecurity. Progent can help you to install 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 tasks like system architecture guidance or troubleshooting or you can receive comprehensive project management and co-management services to make sure your SQL Server 2019 migration is successfully carried out on time and within budget. Progent can provide the skills of certified network security consultants, disaster recovery/business continuity planning consultants, and database application designers and programmers to make sure your Microsoft SQL Server 2019 ecosystem delivers maximum business value.
Progent's Microsoft Gold-certified consulting experts can also help your organization to set up SQL Server 2019 to work with other Microsoft products and services by providing expertise such as Microsoft Windows Server 2019 integration consulting, Windows Hyper-V planning and integration expertise, Microsoft Exchange Server 2019 solution design consulting, SharePoint 2019 design and deployment expertise, hybrid integration with Microsoft Azure cloud services, Microsoft Skype for Business integration consulting services, Microsoft Dynamics GP/Great Plains reporting consulting, and Microsoft System Center Operations Manager (SCOM) consultants.
Progent's veteran application developers can assist your business to plan, create, validate, install, and troubleshoot applications that use SQL Server 2019 databases and Progent offers guidance in data modeling, (Online Transaction Processing, Data Warehousing and Mining, ETL, optimization, availability, backup/restore, reporting, and integration with Amazon MWS. To learn more about Progent's SQL Server programming and troubleshooting expertise, refer to SQL Server software development services.
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 top-of-the-line version of Microsoft SQL Server 2019 and is designed for critical business applications requiring the most advanced levels of capacity, availability, and performance. Processing power and memory permitted per database are limited only by the operating system maximum, the amount of VMs allowed is unlimited, and database size can be as high as 524 petabytes. High availability capabilities unique to the Enterprise version of Microsoft SQL Server 2019 include Always On availability groups, online file restore, online index create, suspend and resume online index rebuilds, fast recovery, and live add RAM and CPU. Scalability and throughput functions for the Enterprise edition of SQL Server 2019 include in-memory database, resource governance, automatic optimization, read ahead plus row mode memory grant feedback. The Enterprise edition of SQL Server 2019 also offers a more complete set of data warehousing services, Master Data Services and Reporting Services than other editions.
Microsoft SQL Server 2019 Developer version is basically the same as the Enterprise version, but is licensed for use only for application development and validation. The Developeredition is not permitted for use in a production server. SQL Server 2019 Developer allows only a single client for the SQL Server Distributed Replay feature.
Microsoft SQL Server 2019 Standard version is intended for departments and small organizations who need a basic, minimum-overhead platform for data management and business intelligence. SQL Server 2019 Standard edition includes all the cybersecurity functions of SQL Server 2019 Enterprise, but compute scale is limited to the lesser of four sockets or two dozen cores. For every instance of SQL Server Database Engine, the most RAM for the buffer pool cache is 128 GB, and max RAM-optimized data size is 32 GB. Unlike the Enterprise edition, the Standard version of SQL Server 2019 does not come with advanced R and Python integration and does not include Machine Learning Services, but includes the remaining programmability functions and the management utilities available with the Enterprise edition. High-end functions such as transparent data encryption and Big Data Clusters are included with the Standard edition.
SQL Server 2019 Web edition is an inexpensive package intended for web hosting organizations. Compute capacity is limited to the lesser of four sockets or 16 cores, maximum buffer pool memory is 64 MB, and maximum RAM is 64 GB. In contrast to the Standard version of SQL Server 2019, Microsoft SQL Server 2019 Web edition does not support Always On failover cluster instances, basic availability groups, or accelerated database recovery. The Web edition also does not support transparent database or backup encryption. You can profile Microsoft SQL Server Web with SQL Server Enterprise or SQL Server Standard versions, and SQL Server Web comes with the other management utilities included with the Standard version. Data warehousing, tabular models, Business Intelligence Services, and some Reporting Services are not provided.
Microsoft SQL Server 2019 Express, available in a few variants, is a free low-end version of Microsoft SQL Server designed for training or for developing desktop, small-server, and client apps that do not require an industrial-strength DBMS. Compute power is limited to the smaller of one socket or four cores, max buffer pool cache RAM is 1.41 MB, and top memory-optimized data size is 352 MB. If applications developed with SQL Server 2019 Express grow to require more capacity or additional features, you can migrate seamlessly to more advanced editions of SQL Server. Microsoft SQL Server 2019 Express with Advanced Services adds Basic R and SQL Graph services, the distributed replay management utility, and SQL Server Data Tools (SSDT). Microsoft SQL Server 2019 Express with Advanced Services can also support a SQL Server Reporting Services (SSRS) database. SQL Server Express LocalDB is a ultra-lite edition of Express that runs in user mode and can be installed without setup and with virtually no prerequisites. The LocalDB edition of Express shares all the programmability functions of other Express releases but does not support in-memory (Online Transaction Processing.
Parts of Microsoft SQL Server 2019
The Microsoft SQL Server 2019 Installation Wizard enables you to select which of the SQL Server components you wish to include in your installation. These components include:
SQL Server Database Engine
The Database Engine is the core of SQL Server's services for processing, holding, and securing data. The Database Engine is used for replication, search, tools for managing relational data, integrating in-database analytics, supporting PolyBase for working with outside data repositories such as Hadoop, and Machine Learning Services for processing SQL Server's relational data using Python and R scripts.
SQL SSAS is an OLAP and data mining toolset for extracting useful insights from data dispersed over multiple databases or in a variety of files or tables.
SQL Server Reporting Services
SQL Server Reporting Services (SSRS) offers a set of local tools and services that enable organizations to create, distribute, and manage tabular, matrix, graphical, and unstructured reports, both mobile and paginated. You can create responsive-design smart phone reports with Microsoft's Mobile Report Publisher app and use native Power BI apps for handheld endpoints to access the data processed in SSRS.
You can view SSRS 2019 data via Power BI apps for mobile endpoints
Integration Services (SSIS) is a collection of programmable objects and zero-code graphical utilities for blending data from a wide variety of sources. ETL processes can be applied to XML data files, text, and relational data sources. Integration Services can be utilized for copying or downloading files, populating data warehouses, cleaning up and mining data, and controlling SQL Server data and objects.
Master Data Services (MDS)
Master Data Services (MDS) is SQL Server's Master Data Management (MDM) solution that allows you to build a single view of enterprise data, synchronizing data silos to avoid duplication and versioning models to preserve consistency. A Microsoft Excel add-in makes it easy to publish the master data set across an enterprise.
In-Database Machine Learning Services
Microsoft SQL Server 2019 Machine Learning Services enables you to execute Python and R scripts using relational data without the need to move data out of SQL Server or over your network to another server. The Microsoft Python and R packages can be used for predictive analytics and machine learning.
New and Improved Features of SQL Server 2019
The most prominent new feature for SQL Server 2019 is Big Data Clusters, which allows you to process big data from Transact-SQL or Spark for machine learning solutions. Additional new or improved features cover important areas including the SQL Server Database Engine, feature equivalence between SQL Server on Linux and Microsoft SQL Server on Windows, SQL Server Analysis Services (SSAS), and SQL Server Reporting Services (SSRS).
Big Data Clusters
trusted execution environment Server 2019 introduces the Big Data Cluster, a cluster of Linux containers orchestrated by Kubernetes. The Big Data Cluster allows you to implement and use scalable clusters in connected SQL Server, Spark, and HDFS containers managed by Kubernetes. Since these components can run concurrently, The Big Data Clusters technology allows you to process big data from Transact-SQL or Apache Spark and makes it simpler to combine and analyze your internal SQL Server relational data with. Implementation scenarios include virtualized data for querying external data sources without transferring or replicating the data, data lakes created with HDFS storage pools to hold big data, and scale-out data marts to spread data from various sources across multiple data pool nodes. You can use Apache Spark or built-in R, Python, Scala, and Java tools in SQL Server 2019 for machine learning jobs on the data deposited in HDFS storage pools and data pools.
Big Data Clusters allow you to analyze your relational data with unstructured big data
Additional Enhancements to the SQL Server 2019 Database Engine
Additional enhancements to the SQL Server 2019 Database Engine address intelligent databases, the developer environment, cybersecurity, high-availability, and Linux/Windows feature parity. Improvements to the Microsoft SQL Server 2019 Database Engine include:
- Intelligent Query Processing improves the performance of your existing workloads and is turned on automatically on the latest database compatibility level designation. Enhancements include automatic memory grant correction, batch mode on rowstore, automatic transformation of scalar UDFs into relational expressions, and table variable deferred compilation.
- Enhanced In-Memory Database features leverage modern hardware to improve performance and expandability with new functions such as direct PMEM device access, memory-optimized TempDB metadata, and support for creating snapshots of databases that contain memory-optimized filegroups.
- Intelligent throughput enhancements in SQL Server 2019 address typical resource bottlenecks and provide database setup options to promote more consistent responsiveness 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 Transact-SQL and API cursors, more granular control of memory limits for resource governance, reduced unneeded recompilations, improved scalability for indirect checkpoint, improved concurrent updates for Page Free Space, and scheduler worker migration for long-running tasks to achieve more balanced processor usage.
- Improvements to the developer environment include partition tables and index partitioning support for graph tables, UTF-8 import and export encoding, a new Java language software development kit (SDK), new spatial reference identifiers more closely aligned with global positioning systems, and more specific error messages to simplify debugging data truncation problems in unsuccessful ETL processes.
- Security improvements include the Always Encrypted feature with secure enclaves functionality, which permits operations on plaintext data inside a secure server-side enclave instead of being restricted to on the client side. This eliminates moving data into and out of the database for processes such as initial data encryption, which increases cryptographic speed. Also, certificate infrastructure management is integrated into the SQL Server Configuration Manager. This streamlines tasks like viewing and validating certificates, flagging certificates approaching expiration, and implementing certificates throughout machines within Availability Groups. A new integrated Data Discovery and Classification feature assists you to classify and name columns in user tables. This can make it easier to find where sensitive data resides and to follow data privacy standards and compliance mandates.
Microsoft SQL Server 2019's secure enclaves provide a trusted environment for processing private SQL data
Enhancements to SQL Server 2019 Analysis Services
- For enhanced availability, Microsoft SQL Server 2019 expands the max number of synchronous replicas within an Availability Group from 3 to 5. You can set up this replica group to have auto failover inside the group. You can have one primary replica plus four synchronous secondary replicas within a group. Also, secondary to primary replica R/W connection redirection for Always On AGs permits client app connections to be directed to the primary replica no matter what target server is called for the connections string. This capability brings replica connection redirection for implementations that have no an AG listener such as a multi-subnet configuration in the cloud.
- The SQL Server Database Engine database recovery process has been re-architected in SQL Server 2019 to include the Accelerated Database Recovery feature. This capability substantially shortens the time it takes to complete a database recovery following a failover or other abrupt shut down. SQL Server Accelerated Database Recovery also cuts the time required to execute rollback of canceled long-duration transactions.
- Microsoft SQL Server on Linux has come close to offering feature parity with SQL Server on Windows with the addition of new SQL Server on Linux functionality such as support for replication, the Microsoft Distributed Transaction Coordinator, AD authentication, Python and R Machine Learning Services, PolyBase, Change Data Capture, plus improved scale in TempDB file size and auto growth.
SQL Server 2019 incorporates new and improved functions for SQL Server Analysis Services that enhance throughput, resource governance, and client support. These enhancements include calculation groups in tabular models for grouping common measure expressions, query interleaving, governance setting for Power BI cache refreshes, and the ability to attach a tabular model as an online operation.
Enhancements to SQL Server 2019 Reporting Services (SSRS)
Improvements to SQL Server 2019 Reporting Services include:
How Progent Can Assist You to Design, Configure, and Manage Your Microsoft SQL Server 2019 Deployment
- Support for Azure SQL Managed Instance enables you to host a database catalog for SSRS 2019 in an Azure SQL Managed Instance hosted either in a virtual machine or in your on-prem data center. You have to use your database credentials for connecting with SQL MI.
- Support for Power BI Premium datasets via either Microsoft Report Builder or SSDT. You can then publish your reports to SQL Server 2019 Reporting Services (SSRS) by utilizing SQL Server 2019 Analysis Services connectivity.
- Azure AD Application Proxy does away with the need to manage your own web application proxy to allow secure connectivity via the web or via mobile apps.
- SQL Server 2019 supports Transparent Database Encryption for the SSRS 2019 catalog database for Enterprise and Standard editions.
Progent's Microsoft Gold-certified consultants can provide remote and on-premises expertise to help your business to plan, configure, manage, tune, and debug your SQL Server 2019 environment. Progent's consultants can help your organization to evaluate the benefits of upgrading to Microsoft SQL Server 2019 and can show you how to design an appropriate architecture for an onsite or hybrid onsite/cloud solution.
Progent can assist you to port your data and applications, set up test systems to verify SQL Server 2019's capability to run your mission-critical applications, centralize management, create and implement a company-wide security strategy that complies with government and industry requirements, accommodate mobile devices, and define a disaster recovery/business continuity plan that incorporates the modern HA technologies incorporated in SQL Server 2019.
IT consulting services available from Progent for SQL Server 2019 include:
Contact Progent for Microsoft SQL Server 2019 Expertise
- Project Management: Progent offers project management services to assist your company to follow best practices to make sure your Microsoft SQL Server 2019 project is successfully completed on schedule and within budget. Project offers support from top-tier consultants to provide guidance for any phase of project management including requirements identification, readiness assessment, system architecture, upgrade planning, configuring test systems, implementation, tuning, documentation, education, management, and on-demand remote or onsite consulting and troubleshooting.
- Database Migration: Progent's veteran DBA experts offer consulting supportfor all versions of Microsoft SQL Server, including end-of-support (EOS) versions like SQL Server 2008. Progent can also provide Oracle expertise, MySQL software design and programming, and Microsoft Access consulting. Progent has 20 years of experience providing database solutions and can assist you to migrate your databases and apps to SQL Server 2019 quickly and economically.
- Integration with Other Microsoft Products: Microsoft's extensive catalog of enterprise applications, operating systems, development environments, management tools, and IT services depend on Microsoft SQL Server as their underlying database engine. Progent's Microsoft Gold-certified experts can assist your business to deploy SQL Server 2019 seamlessly with additional Microsoft apps by offering consulting services that include Windows Server 2019 migration services, SharePoint Server 2019 application design, Power BI business reporting expertise, Microsoft Exchange Server 2019 integration consulting and Microsoft 365 Exchange Online hybrid integration consulting, and Active Directory planning and troubleshooting.
- Business Intelligence and Custom Reporting: Progent's business intelligence and custom reporting experts can assist your company to use Microsoft's business Intelligence (BI) stack to build and publish BI reports that can be accessed securely from any device. Progent can help your organization build PowerPivot models and utilize SQL SSAS 2019 to extract insights from tabular databases. Progent's Power BI experts can help you to communicate with Power BI datasets via Microsoft Report Builder or SQL Server Data Tools and publish reports to SSRS 2019 by using SQL Server Analysis Services (SSAS) connections. Progent's experts can also help your company to connect SQL Server 2019 with third-party BI reporting products like Crystal Reports or upgrade from out-of-date reporting products like FRx.
- Network Infrastructure: Progent's Cisco-certified CCIE networking experts can help you to design, configure, manage, and secure a resilient and high-performance network infrastructure for your SQL Server 2019 ecosystem. Progent can help you design and deploy an optimal infrastructure for any network model including onsite or hybrid.
- Security and Compliance: Progent's security and compliance consultants have been awarded prestigious data security certifications such as CISM, ISSAP and CRISC. Progent offers organizations of all sizes a range of cybersecurity services covering vulnerability assessment, enhancement, response, and remote monitoring services to defend against familiar and zero-day cyber assaults such as ransomware and to verify compliance with government and industry mandates.
- Virtualization Expertise: Progent can help your business to plan and configure an affordable and manageable virtual environment for SQL Server 2019 and offers the expertise of certified Windows Hyper-V experts, VMware vSphere and vCloud experts, VMware NSX-certified virtualization consultants, and Citrix XenServer consultants to help your organization to deploy and maintain a virtualized IT system.
- ProSight Remote Monitoring and Management Services: Progent offers a selection of affordable, flat-rate services designed to allow small and mid-size organizations to utilize the same cutting-edge technology deployed by many of the world's leading corporations. The ProSight suite of IT management outsourcing services addresses all critical facets of modern networking including email protection, behavioral-based threat defense, cloud-based backup/recovery, infrastructure monitoring and management, VM cloud hosting, and IT resources documentation management.
- Disaster Recovery Planning: Progent's disaster recovery planning consultants can help your organization to create and validate a disaster recovery plan that utilizes SQL Server 2019's high-availability technology to maximize network resilience and shorten restore time for critical applications. Progent's business continuity planning experts can assist you to create and validate a detailed plan to keep your core business processes operating in case of a catastrophe. The business continuity strategy deals with IT infrastructure but also covers preparation for issues like emergency notification systems and procedures, short-term office space and transportation, and regulatory mandates for reporting.
- Remote Guidance and Troubleshooting: Progent has twenty years of background delivering high-level consulting, application development, and troubleshooting services online. Using online expertise allows you to reduce the expense and delay of network disruptions. Progent has delivered online network support to organizations of all sizes in each state in the U.S. Refer to Progent's customer testimonials.) Progent can also provide on-site support in most metro areas across the United States and Canada.
To learn more about ways Progent can assist your organization with Microsoft SQL Server 2019 planning, configuration management, troubleshooting, or application development, call 1-800-993-9400 or see Contact Progent.
If you require immediate remote assistance from a Microsoft-certified support expert, see Progent's Online IT Support.
Learn more about Progent's Consulting Services for Microsoft .NET Server Platforms.