SQL Server 2019 is an advanced, on-premises version of Microsoft's flagship database platform. The headline feature of SQL Server 2019 is the introduction of the Big Data Cluster, a pattern of Linux containers orchestrated by Kubernetes. The Big Data Cluster feature enables massively scaled AI and machine learning tasks on the data stored in HDFS and SQL Server storage pools. SQL Server 2019 also includes additional improvements in the core SQL Server database engine such as feature parity on Linux hosts. Other important enhancements impact SQL Server Analysis Services (SSAS) and SQL Server Reporting Services (SSRS).
Big Data Clusters let you combine/analyze your relational data with high-volume big data
With SQL Server 2008 and SQL Server 2008 R2 having reached end of support (EOS) status, migrating to a modern version of SQL Server makes sense for organizations concerned about compliance, compatibility, cybersecurity, and competitiveness. Progent has twenty years of experience planning, deploying, tuning, managing, and troubleshooting SQL Server solutions and can help companies of any size to prepare and execute an efficient migration to SQL Server 2019. Progent's Microsoft-certified SQL Server experts can help you evaluate the business case for upgrading to SQL Server 2019 and supply fast online or onsite access to experts in system design, application programming, database administration, and cybersecurity. Progent can help you deploy SQL Server 2019 on premises, in a private or public cloud, or in a hybrid network architecture.
You can engage Progent's consultants on an as-needed basis for tasks like design review or troubleshooting or you can receive comprehensive project management and co-management services to ensure your SQL Server 2019 initiative is completed on time and on budget. Progent offers the help of certified network security consultants, disaster recovery and business continuity planners, and database application developers to make sure your SQL Server 2019 deployment consistently delivers maximum business value.
Progent's Microsoft-certified consultants can also assist you to integrate SQL Server 2019 with other Microsoft products and services by providing expertise that includes Windows Server 2022 integration services, Windows Server 2019 consulting, Hyper-V virtualization planning, Exchange Server 2019 integration consulting, SharePoint 2019 consulting, hybrid integration with Azure cloud services, Skype for Business integration, Dynamics GP custom reporting help, and System Center Operations Manager (SCOM) consulting.
Progent's seasoned software developers can assist you to plan, create, pilot test, install, and troubleshoot applications that utilize SQL Server 2019 databases and Progent can provide expertise in data modeling, OLTP, Data Warehousing, Data Mining, ETL, tuning, availability, backup/recovery, analytics and reporting, and integration with Amazon Marketplace Web Service. To find out more about Progent's SQL Server development and debugging services, visit SQL Server application development services.
SQL Server 2019 Editions
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 edition of SQL Server 2019 and is intended for critical business applications requiring the most advanced levels of scale, availability, and performance. Compute power and memory supported per database match the operating system maximum, virtualization is unlimited, and relational database size can be up to 524 PB. High availability features unique to the Enterprise edition include Always On availability groups, online page and file restore, online index create and rebuild, resumable online index rebuilds, online schema change, fast recovery, mirrored backups, and hot add memory and CPU. Scalability and performance features for the Enterprise edition include in-memory database, resource governance, partitioned table parallelism, read ahead, automatic tuning, batch mode adaptive joins, plus row mode and memory grant feedback. The Enterprise edition of SQL Server 2019 also supports a richer set of Master Data Services, Data Warehousing, Analysis Services, Reporting Services, and Advanced Integration Services than other editions.
SQL Server 2019 Developer edition is essentially identical to the Enterprise edition, but its license restricts its use to development and testing. The Developer edition is not licensed for use as a production server. SQL Server 2019 Developer supports only a single client for SQL Server Distributed Replay.
SQL Server 2019 Standard edition is designed for departments and small organizations who require a basic, low-overhead platform for data management and BI. SQL Server 2019 Standard edition supports all the security features of SQL Server 2019 Enterprise, but compute capacity is limited to the smaller of 4 sockets or 24 cores. For each instance of SQL Server Database Engine, maximum memory for buffer pool is 128 GB and maximum memory-optimized data size is 32 GB. Unlike the Enterprise edition, the Standard edition of SQL Server 2019 does not support advanced R and Python integration and does not include Machine Learning Services, but supports the other programmability features as well as the management tools available with the Enterprise edition. Advanced features like transparent data encryption, accelerated database recovery, and Big Data Clusters are now included with the Standard edition.
SQL Server 2019 Web edition is an inexpensive option intended for web hosting companies. Compute capacity is limited to the lesser of 4 sockets or 16 cores, max buffer pool memory is 64 MB, and memory-optimized data size is 16 GB. Unlike the Standard edition of SQL Server 2019, SQL Server 2019 Web edition does not support Always On failover cluster instances, basic availability groups, hybrid backup or transactional replication to Windows Azure, or accelerated database recovery. The Web edition also does not support transparent database or backup encryption. You can profile SQL Server Web by using SQL Server Enterprise or SQL Server Standard editions, and SQL Server Web includes the other management tools included with the Standard edition. Data warehousing, Analysis Services, tabular and multidimensional models, BI Services, and some Reporting Services are not included.
SQL Server 2019 Express, available in several variants, is a free entry-level version of SQL Server designed for training or for creating desktop, small-server, and client applications that do not require an industrial-strength database. Compute capacity is limited to the lesser of 1 socket or 4 cores, max buffer pool memory is 1.41 MB, and memory-optimized data size is 352 MB. If applications created with SQL Server 2019 Express grow to require more capacity or additional features, you can upgrade seamlessly to more powerful editions of SQL Server. SQL Server 2019 Express with Advanced Services adds Basic R and SQL Graph integration, the distributed replay management tool, and SQL Server Data Tools (SSDT). SQL Server 2019 Express with Advanced Services can also host a SQL Server Reporting Services (SSRS) database. SQL Server Express LocalDB is a trimmed down version of Express that runs in user mode and can be installed without configuration and with virtually no prerequisites. SQL Server Express LocalDB shares all the programmability features of other Express variants but does not support in-memory OLTP.
Components of SQL Server 2019
The SQL Server 2019 Installation Wizard allows you to select which of the SQL Server components you want to include in your deployment. These components include:
SQL Server Database Engine
The Database Engine is the heart of SQL Server's services for processing, storing, and protecting data. The Database Engine is responsible for replication, full-text search, utilities for managing relational and XML data, integrating in-database analytics, integrating PolyBase for accessing external data sources like Hadoop, and Machine Learning Services for processing SQL Server's relational data with Python and R scripts.
SQL Server Analysis Services (SSAS) is an OLAP and data mining resource for extracting useful information from data scattered across multiple databases or in various files or tables.
SQL Server Reporting Services (SSRS) offers a collection of on-premises utilities and services that allow you to build, share, and manage tabular, matrix, graphical, and free-form reports, both mobile and paginated. You can create responsive-design mobile reports with the Mobile Report Publisher app and use native Power BI apps for mobile devices to access the data you have in SQL Server Reporting Services.
You can access SSRS data from Power BI apps for mobile devices
Integration Services is a collection of programmable objects and zero-code graphical tools for migrating data. ETL operations can be performed on XML data files, flat files, and relational data sources. These services can be used for copying or downloading files, populating data warehouses, cleansing and mining data, and managing SQL Server objects and data.
Master Data Services
Master Data Services (MDS) is SQL Server's Master Data Management (MDM) platform that allows you to build a single, centralized view of enterprise data, synchronizing data sources to eliminate redundancies and versioning models to maintain consistency. An Excel add-in makes it easy to share the master data set throughout an organization.
In-Database Machine Learning Services
Machine Learning Services allows you to execute Python and R scripts with relational data sources without moving data outside SQL Server or across the network. The Microsoft Python and R packages can be used for predictive analytics and machine learning.
Standalone Machine Learning Server
The Standalone Machine Learning Server allows you to build distributed, scalable machine learning solutions on multiple platforms and use multiple enterprise data sources, including Linux and Hadoop. Because the Standalone Machine Learning Server is independent of SQL Server, you configure, protect, and access the R and Python environment via the OS and tools available on your standalone server. To use SQL Server data, you can create data source objects and connections.
New and Enhanced Features of SQL Server 2019
The headline new feature for SQL Server 2019 is Big Data Clusters, which allows you to work with very large sets of data and includes machine learning and AI support. Other new or enhanced features impact important areas including the SQL Server Database Engine, SQL Server on Linux feature parity with SQL Server on Windows, SQL Server Analysis Services, and SQL Server 2019 Reporting Services.
Introducing Big Data Clusters
SQL Server 2019 introduces Big Data Clusters, a feature that enables you to configure and run scalable clusters in interrelated SQL Server, Spark, and HDFS containers managed by Kubernetes. Because these components can run in parallel, Big Data Clusters technology allows you to process big data from Transact-SQL or Spark and makes it easy to combine and analyze your internal SQL Server relational data with big data. Deployment scenarios include data virtualization, data lakes, and scale-out data marts.
Big Data Clusters let you combine and analyze your relational data with big data
With SQL Server PolyBase, you can query data from virtualized external SQL Server, Oracle, Teradata, MongoDB, and ODBC data sources without having to move or copy the data. You can create a scalable HDFS storage pool to store big data that can be ingested from multiple external sources. Once the data lake is stored in HDFS in the Big Data Cluster, you can analyze and query the data and combine it with your relational data. You can increase data analysis performance by creating a data mart with scale-out compute and storage resources.
You can also distribute data from a wide range of sources across data pool nodes as a cache for ongoing analysis. 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 and data pools.
Additional Enhancements to the SQL Server 2019 Database Engine
Other enhancements to the SQL Server 2019 Database Engine cover intelligent databases, the developer experience, security, high-availability, and feature parity between Linux and Windows hosts. Improvements to the SQL Server 2019 Database Engine include:
- Intelligent Query Processing increases the performance of your existing workloads and is activated by default on the latest database compatibility level setting. Improvements include row mode memory grant feedback, batch mode execution without requiring columnstore indexes, scalar UDF Inlining, table variable deferred compilation, and approximate query processing.
- Enhanced In-Memory Database technologies improve performance and scale with new features such as hybrid buffer pool, memory-optimized TempDB metadata, and support for creating Database Snapshots of databases that include memory-optimized filegroups for In-Memory OLTP.
- Intelligent performance improvements in SQL Server 2019 address common resource bottlenecks and offer database configuration options to ensure more predictable performance across all your workloads. Improvements include optimization for indexes subject to last-page insert contention, Query Store plan forcing support for fast forward and static cursors, more granular control of memory limits for resource governance, reduced incidents of unnecessary recompilations, improved scalability for indirect checkpoint, concurrent Page Free Space (PFS) updates, and Scheduler worker migration for more balanced CPU utilization.
- Improvements to the developer experience include support for table and index partitioning for graph tables, support for UTF-8 import and export character encoding, a new Java language software development kit (SDK), new spatial reference identifiers more closely aligned with global positioning systems (GPS), and more informative and actionable error messages for failed ETL processes.
- Security improvements include the Always Encrypted with secure enclaves feature, which allows computations on plaintext data within a secure server-side enclave. This avoids moving data in and out of the database, thereby improving cryptographic performance and reliability. In addition, certificate management is integrated into the SQL Server Configuration Manager, making it easier to view and validate certificates, manage certificates approaching expiration, and deploy certificates across machines in Availability Groups and Failover Clusters. Also, a new integrated Data Discovery and Classification feature helps you classify, label, and protect sensitive data in your databases and meet regulatory and compliance standards for information security.
Secure enclaves provide a trusted environment for processing sensitive SQL data
Enhancements to SQL Server 2019 Analysis Services (SSAS)
- For improved availability, SQL Server 2019 increases the maximum number of synchronous replicas in an Availability Group from 3 to 5. In addition, secondary to primary replica read/write connection redirection for Always On Availability Groups allows client application connections to be directed to the primary replica no matter what target server is specified in the connections string. This feature adds replica connection redirection for deployments that do not include an Availability Group listener. In addition, you can configure high availability for SQL Server 2019 Machine Learning Services on a Windows Server Failover Cluster.
- The SQL Server Database Engine recovery process has been redesigned in SQL Server 2019 to support Accelerated Database Recovery. This significantly reduces the time required to complete the recovery process after a failover or other abrupt shut down. Accelerated Database Recovery also cuts the time it takes to complete rollback of canceled long-running transactions.
- SQL Server on Linux has come close to reaching feature parity with SQL Server on Windows through the addition of new SQL Server on Linux features that include support for replication, the Microsoft Distributed Transaction Coordinator (MSDTC), OpenLDAP for third-party AD providers, Machine Learning Services, PolyBase, Change Data Capture (CDC), plus improvements in TempDB.
SQL Server 2019 includes new and improved features for Analysis Services that enhance performance, resource governance, and client support. These features include calculation groups in tabular models, query interleaving, many-to-many relationships in tabular models, new property settings for resource governance, online attach, and governance setting for Power BI cache refreshes.
Enhancements to SQL Server 2019 Reporting Services (SSRS)
Enhancements to SSRS 2019 include:
How Progent Can Help You Plan, Configure, and Manage Your SQL Server 2019 Deployment
- Support for Azure SQL Managed Instances allows you to host a database catalog for SSRS in an Azure SQL Managed Instance hosted in a virtual machine or in your data center. You are required to use database credentials for the connection to SQL Managed Instance.
- Support for Power BI Premium datasets allows you to connect to Power BI datasets via Microsoft Report Builder or SQL Server Data Tools (SSDT). You can publish those reports to SSRS 2019 using SQL Server Analysis Services connectivity.
- The Azure Active Directory Application Proxy eliminates the need to manage your own web application proxy for secure access via the web or mobile apps.
- Transparent Database Encryption is supported for the SSRS catalog database for the Enterprise and Standard editions of SQL Server 2019.
Progent's Microsoft-certified consultants can provide online and onsite help to assist you to plan, configure, manage, tune, and debug your SQL Server 2019 deployment. Progent can help you assess the benefits of migrating to SQL Server 2019 and can help you design an appropriate architecture for an on-premises or hybrid network model.
Progent can assist you to migrate your data and applications, set up pilot systems to verify SQL Server 2019's ability to run your business-critical applications, streamline management, design and implement a company-wide cybersecurity strategy that meets government and industry requirements, integrate mobile devices, and create a disaster recovery or business continuity plan that takes advantage of the advanced high-availability technology integrated into SQL Server 2019.
Consulting services available from Progent for SQL Server 2019 include:
Contact Progent for SQL Server 2019 Consulting and Development Expertise
- Project Management: Progent offers project management services to help you adhere to leading practices to make sure your SQL Server 2019 initiative is carried out on schedule and within budget. Project can provide fast access to experts to help with any phase of project management including requirements analysis, preparedness assessment, system design, migration planning, configuring test environments, deployment, optimization, documentation, training, administration, and as-needed online or onsite consulting and debugging.
- Database Migration: Progent's seasoned DBA consultants offer consulting expertise for all releases of SQL Server, including end-of-support (EOS) versions such as SQL Server 2008 R2. Progent can also provide Oracle expertise, MySQL software design and programming, and Microsoft Access consulting. Progent has 20 years of experience delivering database solutions and can help you move your databases and applications to SQL Server 2019 quickly and affordably.
- Integration with Other Microsoft Products: Microsoft's extensive catalog of enterprise applications, operating systems, development platforms, management tools, and technologies rely on SQL Server as their core database engine. Progent's Microsoft-certified consultants can help you integrate SQL Server 2019 efficiently with other Microsoft products by offering services that include Windows Server 2022 configuration support, Windows Server 2019 migration expertise, SharePoint Server 2019 application design, Power BI reporting expertise, Exchange Server 2019 consulting and Microsoft 365 Exchange Online integration, and Active Directory and Azure AD planning and troubleshooting.
- Reporting and Business Intelligence: Progent's Business Intelligence (BI) reporting consultants can help you use Microsoft's BI stack to build and share BI reports that can be accessed securely from any authorized endpoint. Progent can help you create PowerPivot models and use SQL Server 2019 Analysis Services to manage tabular databases. Progent's Power BI experts can help you connect to Power BI datasets using Microsoft Report Builder or SQL Server Data Tools and publish reports to SQL Server Reporting Services by using SQL Server Analysis Services connections. Progent can also help you integrate third-party BI reporting platforms like Crystal Reports with SQL Server 2019 or migrate from legacy reporting tools like FRx.
- Network Infrastructure: Progent's Cisco-certified CCIE network architecture consultants can help you plan, configure, manage, and protect a high-availability and high-performance network infrastructure for your SQL Server 2019 environment. Progent can help you design and deploy an optimal solution for any network model including on-premises, local or public cloud, or hybrid.
- Security and Compliance: Progent's cybersecurity and compliance experts have earned prestigious security certifications including CISA, CISM, CISSP, ISSAP, CRISC, and GIAC. Progent offers organizations of any size a variety of cybersecurity assessment, enhancement, response, and remote monitoring services to protect against known and zero-day cyber attacks such as ransomware and to validate compliance with regulatory and industry requirements.
- Virtualization Expertise: Progent can assist you to plan and configure a cost-effective and manageable virtualization solution for SQL Server 2019 and offers the expertise of certified Windows Hyper-V consultants, VMware vSphere and vCloud experts, VMware NSX-certified virtualization consultants, and Citrix XenServer consultants to assist your organization to deploy and manage a virtualized ecosystem.
- ProSight Remote Monitoring and Management Services: Progent's ProSight family of remote monitoring and management (RMM) services offer an affordable way for small and mid-size businesses (SMBs) to benefit from modern tools and technologies for network management. ProSight services offer comprehensive remote monitoring at a low monthly cost and include management of key areas including network and device security, backup/restore, VM hosting, and documentation management. To find out more about how Progent's ProSight ProSight services can help you monitor and manage your SQL Server deployment, visit ProSight remote network management services.
- Disaster Recovery/Business Continuity Planning: Progent's disaster recovery planning consultants can help you create, test, and document a disaster recovery plan that incorporates SQL Server 2019's high-availability technology to maximize uptime and minimize recovery time for business-critical applications and services. Progent's business continuity planning consultants can help you develop and validate a comprehensive plan to keep your core business processes operating in case of a catastrophic event. The business continuity plan deals with IT infrastructure but also addresses the logistics associated with issues like emergency notification systems and procedures, temporary office space and transportation, and regulatory mandates for reporting. For enterprises, Progent offers the expertise of a VMware VCDX-certified VMware Site Recovery Manager expert and VMware NSX network virtualization consultant to help you plan, implement, and manage an advanced disaster-recovery/application-mobility solution for private cloud and hybrid-cloud environments.
- Online Consulting and Troubleshooting: Progent has twenty years of background providing advanced consulting, application development, and debugging services remotely. Access to online expertise allows you to minimize the costs and duration of network downtime. Progent has successfully delivered remote IT support to organizations of all sizes in every state in the U.S. (See Progent's customer testimonials.) Progent can also provide on-premises support in most metropolitan areas throughout the United States and Canada.
To find out more about how Progent can help you with SQL Server 2019 planning, integration, management, troubleshooting, or software development, call 1-800-993-9400 or visit Contact Progent.
If you need immediate online help from a Microsoft-certified consultant, see Progent's Online Network Support.
Learn more about Progent's Consulting Services for Microsoft .NET Server Platforms.