SQL Server 2019 is a modern, self-hosted release of Microsoft's flagship database system. The headline enhancement is the integration of SQL Server with Spark, Hadoop Distributed File System (HDFS), and other Linux-driven big data technologies to support Big Data Clusters. The Big Data Cluster enables data-intensive AI and machine learning operations on the high-volume data resident in HDFS and SQL Server 2019 storage pools. You can deploy Big Data Clusters as containers in self-hosted Kubernetes clusters, like AKS on Azure Stack, or in a cloud with a Kubernetes service like Azure Kubernetes Service (AKS).
Microsoft SQL 2019 enables you to combine your relational data and high-volume big data clusters
Microsoft SQL 2019 also includes improvements in the core SQL database engine like feature parity for Linux machines. Other important enhancements impact SQL Server Analysis Services (SSAS) and SQL Server Reporting Services.
Since Microsoft SQL Server 2008 and SQL Server 2008 R2 have arrived at EOS status, moving to a modern release of Microsoft SQL Server makes sense for businesses concerned about cybersecurity, manageability, and productivity. Progent has two decades of experience planning, implementing, tuning, and troubleshooting SQL Server environments and can assist organizations of all sizes to perform an efficient migration to Microsoft SQL Server 2019. Progent's Microsoft Gold-certified Microsoft SQL Server experts can help your business to assess the business case for transitioning to SQL Server 2019 from an older release of SQL Server and Progent offers quick remote or on-premises access to top-tier consultants in system architecture, application development, database administration, and data security. Progent can help your business to deploy Microsoft SQL Server 2019 locally, in the cloud, or in a hybrid on-prem/cloud network model.
You can work with Progent's consultants on an occasional basis for tasks like system architecture guidance or troubleshooting or you can receive end-to-end project management support to ensure your SQL Server 2019 initiative is completed on schedule and on budget. Progent can provide the help of certified network security experts, disaster recovery/business continuity preparedness consultants, and database application developers to ensure your SQL Server 2019 environment delivers top return on investment.
Progent's Microsoft Gold-certified consultants can also help your business to configure Microsoft SQL Server 2019 to operate with other Microsoft products and services by offering support such as Microsoft Windows Server 2019 integration consulting, Hyper-V planning and integration expertise, Microsoft Exchange Server 2019 solution design expertise, SharePoint 2019 development consulting services, hybrid integration with Azure cloud services, Microsoft Skype for Business configuration expertise, Microsoft Dynamics GP reporting consulting services, and System Center Operations Manager experts.
Progent's seasoned software developers can help your organization to design, produce, pilot test, install, and troubleshoot apps that utilize Microsoft SQL Server 2019 and Progent offers expertise in data modeling, (Online Transaction Processing, Data Warehousing and Mining, ETL, tuning, fault tolerance, backup/restore, analytics and reporting, and integration with Amazon Marketplace Web Service. To learn more about Progent's SQL Server programming and debugging services, visit SQL Server software programming 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.
Microsoft SQL Server 2019 Enterprise is the high-end edition of Microsoft SQL Server 2019 and is designed for critical business applications requiring the most advanced levels of scale, reliability, and throughput. Compute power and RAM supported per database are limited only by the OS limit, the number of virtual machines allowed is unlimited, and database size can be as high as 524 petabytes. HA features unique to the Enterprise edition of Microsoft SQL Server 2019 include Always On availability groups for collective failover, online file restore, online indexing, pause and resume online index rebuilds, online schema change, and hot add RAM and compute. Expandability and performance functions exclusive to the Enterprise edition of Microsoft SQL Server 2019 include in-memory database, resource governance, automatic optimization, read ahead plus memory grant feedback. The Enterprise edition of SQL Server 2019 also offers a richer set of data warehousing services, Master Data Services and Reporting Services than other versions.
Microsoft SQL Server 2019 Developer edition is basically the same as the Enterprise version, but is licensed for use only for development and testing. The Developeredition is not permitted for use as a production system. Microsoft SQL Server 2019 Developer allows only one client for the SQL Server Distributed Replay feature.
SQL Server 2019 Standard version is intended for departments and small businesses who need an entry-level, low-maintenance platform for information management and BI. SQL Server 2019 Standard edition includes all the security functions of the Enterprise edition, but processor capacity is restricted to the lesser of four sockets or two dozen cores. For every instance of SQL Server Database Engine, maximum RAM for the buffer pool cache is 128 GB, and maximum memory-optimized data size is 32 GB. In contrast to the Enterprise edition, the Standard version of Microsoft SQL Server 2019 does not include advanced R and Python support and does not include Machine Learning Services, but includes the other programmability features and the management tools provided with the Enterprise edition. Advanced functions such as accelerated database recovery and Big Data Clusters are now provided with the Standard edition.
SQL Server 2019 Web version is an inexpensive option designed for web hosting companies. Compute scale is restricted to the lesser of 4 sockets or 16 cores, max buffer pool memory is 64 MB, and maximum memory per instance is 64 GB. Unlike the Standard version of Microsoft SQL Server 2019, SQL Server 2019 Web edition does not support Always On failover cluster instances, hybrid backup, or transactional replication to Windows Azure. The Web version also does not offer transparent database or backup encryption. You can profile Microsoft SQL Server Web by using Microsoft SQL Server Enterprise or SQL Server Standard editions, and SQL Server Web includes the other management tools provided with the Standard version. Data warehousing, tabular models, Business Intelligence Services, and some Reporting Services are not provided.
SQL Server 2019 Express, available in a few variants, is a free low-end version of SQL Server intended for training or for creating PC, bare-bones-server, and end-user apps that do not need an advanced database. Compute capacity is restricted to the lesser of 1 socket or 4 cores, max buffer pool cache RAM is 1.41 MB, and maximum memory-optimized data size is 352 MB. If apps created with Microsoft SQL Server 2019 Express expand to require more capacity or additional functionality, you can migrate seamlessly to more powerful editions of Microsoft SQL Server. SQL Server 2019 Express with Advanced Services adds Basic R and SQL Graph integration, the distributed replay management utility, and SQL Server Data Tools. 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 edition of Express that operates in user mode and can be deployed without setup and with virtually no prerequisites. The LocalDB version of Express includes all the programmability features of other Express variants but does not support in-memory (Online Transaction Processing.
Parts of Microsoft SQL Server 2019
The SQL Server 2019 Installation Wizard enables you to choose which of the SQL Server components you wish to include in your installation. These elements include:
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, supporting in-database analytics, integrating PolyBase for accessing external data sources like Hadoop, and Machine Learning Services for processing Microsoft SQL Server 2019's relational data using Python and R scripts.
SQL Server Analysis Services (SSAS)
SQL SQL Server Analysis Services is an OLAP and data mining resource for extracting useful information from data scattered across separate databases or in a variety of files or tables.
SQL Server Reporting Services provides a collection of on-premises tools and services that allow organizations to build, distribute, and manage tabular, matrix, graphical, and unstructured reports, both mobile and paginated. You can create device-aware 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 SSRS.
You can access SQL Server Reporting Services (SSRS) 2019 data from Power BI apps for mobile endpoints
Integration Services is a collection of programmable objects and no-code graphical utilities for extracting and transforming data from multiple sources. Extract, transform, and load (ETL) operations can be performed on XML data, text files, and relational data silos. Integration Services can be utilized for duplicating or downloading files, loading data warehouses, cleansing and mining data, and managing SQL Server data and objects.
Master Data Services (MDS)
Master Data Services (MDS) is Microsoft SQL Server's Master Data Management (MDM) platform that enables businesses to create a centralized look at corporate-wide data, synchronizing data sources to eliminate duplication and tracking models to maintain consistency. An Excel extension makes it easy to publish the master data set across an organization.
In-Database Machine Learning Services
Microsoft SQL Server 2019 Machine Learning Services enables you to execute Python and R scripts with relational data without moving data out of SQL Server 2019 or across the network to another server. The open-source Microsoft Python and R packages can be used for predictive analytics and machine learning.
New and Enhanced Features of Microsoft SQL Server 2019
The headline new feature for SQL Server 2019 is Big Data Clusters, which allows you to work with big data from Transact-SQL or Spark for AI applications. Additional new or enhanced features cover key areas such as the SQL Database Engine, feature equivalence between Microsoft SQL Server on Linux and 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 enables you to implement and run scalable clusters in connected SQL Server, Apache Spark, and Hadoop Distributed File System (HDFS) containers managed by Kubernetes. Because these components can run concurrently, The Big Data Clusters feature permits you to process big data from either Microsoft Transact-SQL or Spark and makes it easy to combine and analyze your internal SQL Server relational data with unstructured big data. Implementation scenarios include virtualized data for processing outside data sources without transferring or copying the target data, data lakes created with HDFS pools to hold big data, and scale-out data marts to spread data from a variety of sources across multiple data pool nodes. You can utilize Spark or built-in R, Python, Scala, and Java utilities in SQL Server 2019 for AI and machine learning jobs on the data stored in HDFS storage pools and data pools.
Big Data Clusters enable you to combine and analyze your relational data with unstructured big data
Additional Improvements to the SQL Server 2019 Database Engine
Additional improvements to the SQL Server 2019 Database Engine address intelligent databases, the developer environment, cybersecurity, high-availability (HA), and Linux and Windows feature set parity. Improvements to the Microsoft SQL Server 2019 Database Engine include:
- Intelligent Query Processing increases the performance of your current applications and is turned on automatically on the latest database compatibility level setting. Enhancements 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 enhance performance and expandability with new functions like hybrid buffer pool, memory-optimized TempDB metadata, and the ability to create Database Snapshots of SQL Server databases that contain memory-optimized filegroups.
- Intelligent performance enhancements in Microsoft SQL Server 2019 address typical bottlenecks and offer database configuration options to promote more predictable performance across all applications. Enhancements include optimization for indexes subject to last-page insert contention, Query Store plan forcing and unforcing ability for fast forward and static cursors, more granular control of memory limits for resource governance, reduced extraneous recompilations, improved indirect checkpoint scalability, shared latch concurrent updates for Page Free Space (PFS), and scheduler worker migration for long-running tasks to achieve more balanced CPU utilization.
- Enhancements to the developer environment include table and index partitioning for graph tables, UTF-8 import and export character encoding, a new Java SDK, new spatial reference identifiers (SRIDs) more closely aligned with GPS, and more verbose warnings to simplify debugging truncation problems in unsuccessful ETL processes.
- Cybersecurity enhancements include the Always Encrypted feature with secure enclaves, which permits computations on plaintext data inside the trusted execution environment of a secure enclave on the server side instead of being restricted to on the client side. This eliminates moving data into and out of the database for processes such as key rotation, which increases cryptographic speed. Also, certificate management is integrated into the SQL Server Configuration Manager. This streamlines processes such as viewing and validating SSL/TLS certificates, flagging certificates nearing their expiration, and implementing certificates across machines in Availability Groups. An integrated Data Discovery and Classification function assists you to classify and label columns in user tables. This can make it easier to identify where sensitive information is kept in the enterprise and to meet information security standards and compliance regulations.
Microsoft SQL Server 2019's secure enclaves offer a trusted environment for working with private SQL data
Enhancements to SQL Server 2019 Analysis Services
- For improved availability, SQL Server 2019 increases the highest allowable allowable synchronous replicas in an Availability Group (AG) from three to five. You can configure this group of replicas to have auto failover inside the group. You can have one primary replica along with four synchronous secondary replicas within a group. In addition, secondary to primary replica R/W connection redirection for Always On AGs permits client application 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 deployments that do not include an Availability Group listener as with a multi-subnet deployment in the cloud.
- The SQL Server Database Engine recovery process has been re-engineered in SQL Server 2019 to include Accelerated Database Recovery. This capability significantly reduces the time needed to complete a database recovery following a failover or other disorderly shut down. SQL Server Accelerated Database Recovery also cuts the time needed to execute rollback of canceled long-running transactions.
- SQL Server on Linux approximates reaching feature parity with SQL Server on Windows with the addition of additional SQL Server on Linux features that include support for replication, the Microsoft Distributed Transaction Coordinator (MSDTC), AD authentication, Python and R, PolyBase on Linux for non-Hadoop connectors, Change Data Capture, plus enhancements in TempDB file size and auto growth.
Microsoft SQL Server 2019 includes new and improved features for Analysis Services that improve performance, resource governance, and client support. These improvements include calculation groups in tabular models for grouping common measure expressions, query interleaving for faster query performance, governance setting for Power BI cache refreshes, and online attach for synchronizing read-only replicas.
Improvements to SQL Server 2019 Reporting Services
Enhancements to SQL Server 2019 Reporting Services include:
How Progent Can Help Your Business to Design, Configure, and Manage Your SQL Server 2019 Environment
- Support for Azure SQL Managed Instance (MI) allows you to host a database catalog for SQL Server 2019 Reporting Services (SSRS) in an Azure SQL MI hosted either in a VM or on-premises. You must use database credentials for connecting with SQL MI.
- Support for Power BI Premium datasets via either Microsoft Report Builder or SQL Server Data Tools (SSDT). You can publish those reports to SSRS 2019 using SQL Server 2019 Analysis Services connectivity.
- Azure AD Application Proxy support does away with the necessity of managing your own web application proxy to permit secure connectivity through the web or mobile apps.
- Microsoft SQL Server 2019 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 offer online and on-premises guidance to help your business to plan, implement, administer, tune, and troubleshoot your Microsoft SQL Server 2019 deployment. Progent can help your organization to evaluate the benefits of upgrading to Microsoft SQL Server 2019 and can help you to design an efficient architecture for an on-premises or hybrid onsite/cloud solution.
Progent can help you to port your data and applications, set up test systems to verify SQL Server 2019's ability to support your mission-critical applications, centralize management, design and implement a company-wide security strategy that complies with government and business requirements, integrate mobile devices, and create and test a disaster preparedness/business continuity strategy that incorporates the modern HA technologies integrated into SQL Server 2019.
Expertise available from Progent for SQL Server 2019 include:
Contact Progent for SQL Server 2019 Consulting and Development Expertise
- Project Management: Progent offers project management and co-management services to assist your company to adhere to best practices to ensure that your Microsoft SQL Server 2019 initiative is successfully completed on time and within budget. Project offers the services of top-tier consultants to help with any aspect project management such as requirements identification, readiness evaluation, solution architecture, upgrade planning, building pilot environments, implementation, optimization, documentation, training, management, and as-needed online or onsite advice and debugging.
- Database Migration: Progent's veteran Database Administration consultants can provide expertisefor all releases of Microsoft SQL Server, including end-of-support (EOS) releases like SQL Server 2008 R2. Progent also offers Oracle consulting, MySQL software design and development, and Microsoft Access expertise. Progent has two decades of background providing database solutions and can help you to migrate your databases and apps to Microsoft SQL Server 2019 rapidly and affordably.
- Integration with Other Microsoft Applications: Microsoft's vast line of enterprise applications, operating systems, development environments, management tools, and IT services rely on Microsoft SQL Server as their underlying database engine. Progent's Microsoft Gold-certified experts can assist you to integrate Microsoft SQL Server 2019 seamlessly with other Microsoft apps by offering services that include Windows Server 2019 upgrade services, SharePoint Server 2019 application development, Power BI business reporting expertise, Exchange Server 2019 integration consulting and Microsoft 365 Exchange Online integration solutions, and Active Directory design and debugging.
- Business Intelligence (BI) and Custom Reporting: Progent's business intelligence (BI) and reporting experts can assist your company to use Microsoft's BI stack to create and share BI reports that can be accessed securely via any device. Progent can help you create PowerPivot models and utilize Microsoft SQL Server 2019 Analysis Services to process tabular databases. Progent's Power BI experts can assist you to connect to Power BI datasets using Microsoft Report Builder or SQL Server Data Tools and publish reports to SSRS 2019 by using SQL Server Analysis Services connections. Progent's BI experts can also assist your company to integrate SQL Server 2019 with third-party BI reporting platforms like Crystal Reports or migrate from legacy reporting tools like FRx.
- Network Infrastructure: Progent's Cisco-certified CCIE network architecture experts can help your company to plan, configure, manage, and secure a resilient and responsive network infrastructure for your SQL Server 2019 ecosystem. Progent can assist you plan and integrate an optimal infrastructure for any network model including on-premises or hybrid.
- Cybersecurity and Compliance: Progent's cybersecurity and compliance specialists have earned advanced data security certifications including CISM, ISSAP and GIAC. Progent offers organizations of all sizes a variety of security services including vulnerability assessment, enhancement, response, and RMM services to counter familiar and zero-day malware assaults such as ransomware and to verify compliance with government and industry standards.
- Virtualization Support: Progent can assist your business to design and configure a cost-effective and manageable virtualization solution for Microsoft SQL Server 2019 and can provide the expertise of certified Windows Hyper-V consultants, VMware vSphere and vCloud experts, VMware NSX-certified virtualization experts, and Citrix XenServer consultants to assist you to configure and maintain a virtualized IT system.
- ProSight Remote Monitoring and Management Services: Progent offers a family of affordable, flat-rate services designed to enable small and mid-size businesses to benefit from the same advanced technology deployed by some of the world's top enterprises. The ProSight suite of network management outsourcing services addresses all critical areas of modern networking including email content filtering, security and compliance, cloud-based backup/recovery, infrastructure health monitoring, VM hosting, and IT asset documentation management.
- Disaster Recovery Preparedness: Progent's disaster preparedness consultants can help you to create and validate a DR/BC 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 planning consultants can help your company to create and test a comprehensive plan to keep your core business processes running in the event of a catastrophe. The business continuity plan deals with network issues but also covers the logistics associated with issues like alternative notification technologies and procedures, short-term office space, and regulatory mandates for reporting.
- Remote Guidance and Troubleshooting: Progent has two decades of experience delivering high-level consulting, software development, and debugging services remotely. Access to online expertise helps you to reduce the expense and delay of network disruptions. Progent has successfully delivered remote network services to businesses of all sizes in every state in the U.S. Refer to Progent's customer testimonials.) Progent can also provide on-site expertise in major metro areas across the United States and Canada.
To find out more about ways Progent can help your organization with SQL Server 2019 planning, integration management, troubleshooting, or software programming, call 1-800-993-9400 or visit Contact Progent.
If you need immediate online help from a Microsoft-certified support expert, see Progent's Online Network Support.
Learn more about Progent's Consulting Services for Microsoft .NET Server Platforms.