Microsoft SQL Server 2019 is a modern, on-premises version of Microsoft's primary RDBM system. The marquee feature is the integration of Microsoft SQL Server with Apache Spark, Hadoop Distributed File System (HDFS), and other Linux-based big data components to enable Big Data Clusters. The Big Data Cluster enables massively scaled AI tasks on the data held in HDFS and SQL Server 2019 storage pools. You can deploy Big Data Clusters as containers in locally hosted Kubernetes clusters, such as AKS on Azure Stack, or in any cloud with a managed Kubernetes service like Azure Kubernetes Service.
Microsoft SQL 2019 allows you to analyze your relational data and high-volume big data clusters
Microsoft SQL 2019 also delivers enhancements in the core SQL Server database engine like feature parity on Linux systems. Other important enhancements improve SSAS and SQL Server Reporting Services (SSRS).
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 compliance, compatibility, and competitiveness. Progent has twenty years of experience planning, implementing, tuning, and troubleshooting SQL Server environments and can assist companies of any size to carry out an efficient migration to Microsoft SQL Server 2019. Progent's Microsoft Gold-certified SQL Server consultants can help you to assess the advantages for upgrading to SQL Server 2019 from an older release of Microsoft SQL Server and Progent can provide rapid remote or onsite access to top-tier consultants in solution architecture, application programming, database administration, and data security. Progent can help you to configure Microsoft SQL Server 2019 on premises, in the cloud, or in a hybrid on-prem/cloud network architecture.
You can engage Progent's experts on an occasional basis for jobs like design guidance or debugging or you can get comprehensive project management services to ensure your SQL Server 2019 migration is completed on time and within budget. Progent can provide the help of certified information security experts, disaster recovery planning consultants, and database application designers and programmers to make sure your SQL Server 2019 deployment provides top business value.
Progent's Microsoft Gold-certified consulting experts can also assist your organization to set up Microsoft SQL Server 2019 to work with additional Microsoft apps and technologies by providing expertise that includes Microsoft Windows Server 2019 consulting services, Windows Hyper-V configuration consulting, Microsoft Exchange Server 2019 solution design consulting, SharePoint 2019 design and deployment consulting, hybrid integration with Azure cloud services, Microsoft Skype for Business configuration consulting services, Dynamics GP/Great Plains custom reporting consulting services, and Microsoft System Center Operations Manager (SCOM) consulting.
Progent's seasoned software developers can help you to plan, produce, pilot test, deploy, and debug applications that utilize Microsoft SQL Server 2019 databases and Progent offers guidance in data modeling, OLTP, Data Warehousing and Mining, ETL, tuning, fault tolerance, backup/recovery, OLAP, and integration support for Amazon MWS. To find out more about Progent's SQL Server development and troubleshooting services, see SQL Server software programming services.
Microsoft 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 top-of-the-line version of Microsoft SQL Server 2019 and is intended for mission-critical business applications requiring the highest advanced levels of scale, resilience, and performance. Compute power and RAM permitted per database match the operating system limit, the number of VMs allowed is unlimited, and database size can be as high as 524 PB. HA capabilities unique to the Enterprise version of SQL Server 2019 include Always On availability groups for collective failover, online page restore, online indexing, pause and resume online index rebuilds, fast recovery, and live add memory and compute. Scalability and throughput functions exclusive to the Enterprise edition of Microsoft SQL Server 2019 include in-RAM database, partitioned table parallelism, automatic optimization, read ahead plus memory grant feedback. The Enterprise version of Microsoft SQL Server 2019 also supports a more complete set of data warehousing services, Analysis Services and Advanced Integration Services than other versions.
Microsoft SQL Server 2019 Developer version is essentially identical to the Enterprise edition, but is licensed for use only for development and validation. The Developerversion is not permitted for use as a production system. SQL Server 2019 Developer handles only one client for SQL Server Distributed Replay simulation.
SQL Server 2019 Standard version is intended for departments and small organizations who require an entry-level, low-maintenance solution for information management and BI. SQL Server 2019 Standard version supports all the cybersecurity features of SQL Server 2019 Enterprise, but CPU capacity is limited to the smaller of four sockets or two dozen 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. Unlike the Enterprise version, the Standard version of SQL Server 2019 does not include advanced R and Python support and does not include Machine Learning Services, but includes the remaining development features as well as the management tools the come with the Enterprise version. High-end features like accelerated database recovery and Big Data Clusters are included with the Standard version.
Microsoft SQL Server 2019 Web edition is an inexpensive option intended for web hosting organizations. CPU scale is limited to the smaller of 4 sockets or 16 cores, max buffer pool RAM is 64 MB, and maximum RAM per instance is 64 GB. In contrast to the Standard version of SQL Server 2019, Microsoft SQL Server 2019 Web version does not support Always On failover clusters, 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 by using Microsoft SQL Server Enterprise or SQL Server Standard editions, and SQL Server Web comes with the other management utilities provided with the Standard version. Analysis Services, multidimensional models, BI Services, and some Reporting Services are not included.
SQL Server 2019 Express, available in a few releases, is a free entry-level edition of SQL Server designed for learning or for creating PC, bare-bones-server, and end-user apps that do not require an advanced DBMS. Compute scale is limited to the smaller of one socket or four cores, max buffer pool cache RAM is 1.41 MB, and maximum RAM-optimized data size is 352 MB. If apps created with SQL Server 2019 Express grow to call for more capacity or additional functionality, you can upgrade seamlessly to more advanced editions of Microsoft SQL Server. SQL Server 2019 Express with Advanced Services includes Basic R and SQL Graph services, the distributed replay management tool, and SQL Server Data Tools (SSDT). Microsoft SQL Server 2019 Express with Advanced Services can also support a SSRS database. SQL Server Express LocalDB is a ultra-lite version of Express that operates in user mode and can be installed without configuration and with virtually no prerequisites. The LocalDB version of Express includes the programmability features of other Express variants but does not allow in-memory (Online Transaction Processing.
Elements of Microsoft SQL Server 2019
The Microsoft SQL Server 2019 Installation Wizard enables you to select which of the SQL Server elements you wish to activate in your installation. These parts include:
SQL Server Database Engine
The Database Engine is the heart of SQL Server's services for computing, holding, and securing data. The Database Engine is used for replication, search, utilities for managing relational data, supporting in-database analytics, integrating PolyBase for working with outside data repositories such as Hadoop, and Machine Learning Services for analyzing SQL Server 2019'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 resource for extracting useful insights from data dispersed across separate databases or in a variety of files or tables.
SQL Server Reporting Services
SQL Server Reporting Services (SSRS) provides a collection of local tools and services that allow companies to build, distribute, and administer tabular, matrix, graphical, and unstructured reports. You can create device-aware smart phone reports with Microsoft's Mobile Report Publisher app and utilize integrated Power BI apps for handheld endpoints to view the data processed in SQL Server Reporting Services.
You can view SQL Server Reporting Services 2019 data from Power BI apps for mobile endpoints
SQL Server Integration Services (SSIS)
SQL Server Integration Services is a set of programmable objects and zero-code graphical tools for extracting and transforming data from a wide variety of sources. ETL operations can be applied to XML data files, flat files, and relational data silos. SQL Server Integration Services can be utilized for copying or downloading files, adding to data warehouses, cleansing and mining data, and controlling Microsoft 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 look at enterprise data, synchronizing data silos to avoid redundancies and versioning models to preserve consistency. A Microsoft Excel add-in allows you to publish the master data set throughout an organization.
In-Database Machine Learning Services
Microsoft SQL Server 2019 Machine Learning Services enables you to run Python and R scripts using relational data sources without transferring data out of SQL Server 2019 or over your network to another server. The open-source Microsoft Python and R packages can be used to train and deploy machine learning models.
New and Enhanced Features of SQL Server 2019
The headline new feature for SQL Server 2019 is Big Data Clusters, which enables you to process big data from Transact-SQL or Spark for machine learning solutions. Additional new or improved features cover key technologies such as the SQL Database Engine, feature parity between Microsoft SQL Server on Linux and SQL Server on Windows, SSAS, and 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 run massively scalable clusters in connected SQL Server, Apache Spark, and Hadoop Distributed File System (HDFS) containers orchestrated by Kubernetes. Because these components can run in parallel, The Big Data Clusters technology allows you to process big data from Microsoft Transact-SQL or Spark and makes it simpler to combine and analyze your internal SQL Server relational data with. Implementation models include virtualized data 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 multiple data pool nodes. You can use Spark or integrated R, Python, Scala, and Java tools in SQL Server 2019 for AI jobs on the data held in HDFS storage pools and data pools.
Big Data Clusters allow you to combine and analyze your relational data with big data
Additional Enhancements to the Microsoft SQL Server 2019 Database Engine
Additional enhancements to the SQL Server 2019 Database Engine cover smart databases, the developer environment, cybersecurity, high-availability, and Linux and Windows feature set parity. Enhancements to the Microsoft SQL Server 2019 Database Engine include:
- Intelligent Query Processing increases the performance of your current applications and is activated by default on the latest database compatibility level designation. Improvements include row mode memory grant feedback, batch mode execution without requiring columnstore indexes, automatic transformation of scalar UDFs into relational expressions, and table variable deferred compilation.
- Enhanced In-Memory Database features leverage hardware advances to enhance performance and expandability with new capabilities such as hybrid buffer pool, memory-optimized TempDB metadata, and support for creating Database Snapshots of databases that include memory-optimized filegroups.
- Intelligent performance improvements in SQL Server 2019 address common bottlenecks and provide database configuration alternatives to ensure more consistent execution speed across all applications. Improvements include optimization within the database engine for indexes subject to high-concurrency inserts, Query Store plan forcing support for fast forward and static cursors, finer control for configuring maximum memory grants for resource governance, reduced incidents of unnecessary recompilations, improved scalability for indirect checkpoint, improved update concurrency for Page Free Space, and scheduler worker migration for long-running tasks to achieve more balanced CPU usage.
- Enhancements to the developer environment include partition tables and indexes support for graph tables, support for UTF-8 import and export character encoding, a new Java SDK, new spatial reference identifiers better aligned with GPS, and more verbose warnings to streamline debugging data truncation issues in unsuccessful ETL processes.
- Cybersecurity improvements include the Always Encrypted feature with secure enclaves functionality, which enables computations on plaintext data within a secure server-side enclave rather than only on on the client side. This avoids the need to move data in and out of the database for operations such as key rotation, thereby improving encryption speed. In addition, certificate infrastructure management tools are integrated into the SQL Server Configuration Manager. This streamlines processes like checking and validating certificates, managing certificates approaching their expiration, and deploying certificates throughout machines within Availability Groups and Failover Clusters. An integrated Data Discovery and Classification function helps you to classify and name columns in user tables. This can make it easier to identify where sensitive data is located and to meet information security standards and compliance requirements.
Microsoft SQL Server 2019's secure enclaves offer a safe environment for processing sensitive SQL data
Improvements to SSAS 2019
- For improved HA, Microsoft SQL Server 2019 expands the highest allowable allowable synchronous replicas in an Availability Group (AG) from 3 to 5. You can set up this replica group with automatic failover inside the group. You can deploy a primary replica plus four synchronous secondary replicas. Also, secondary to primary replica R/W connection redirection for Always On AGs allows 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 deployments that have no an AG listener such as a multi-subnet configuration in the cloud.
- The SQL Server Database Engine recovery process has been re-engineered in Microsoft SQL Server 2019 with Accelerated Database Recovery. This capability dramatically shortens the time it takes to finish a database recovery following a failover or other disorderly shut down. SQL Server Accelerated Database Recovery also cuts the time required to complete rollback of aborted long-duration transactions.
- SQL Server on Linux approximates achieving feature parity with SQL Server on Windows with the addition of new SQL Server on Linux functionality that include replications services, the MSDTC, Active Directory authentication with SQL Server on Linux, Python and R Machine Learning Services, PolyBase, Change Data Capture, plus enhancements in TempDB.
Microsoft SQL Server 2019 includes new and improved functions for SQL Server Analysis Services that improve performance, resource management, 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 online attach.
Enhancements to SQL Server 2019 Reporting Services
Enhancements to SQL Server 2019 Reporting Services (SSRS) include:
How Progent Can Help Your Business to Design, Configure, and Manage Your SQL Server 2019 Environment
- 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 VM or on-premises. You need to use database credentials for the connection to SQL Managed Instance.
- Support for Power BI datasets via Microsoft Report Builder or SSDT. You can then publish those reports to SQL Server 2019 Reporting Services by utilizing SQL Server 2019 Analysis Services (SSAS) connectivity.
- Azure Active Directory Application Proxy eliminates the necessity of managing your own web application proxy to permit safe access through the web or via mobile apps.
- SQL Server 2019 now supports Transparent Database Encryption for the SQL Server 2019 Reporting Services (SSRS) catalog database for both Enterprise and Standard editions.
Progent's Microsoft Gold-certified consultants offer online and on-premises guidance to assist your business to design, configure, administer, optimize, and troubleshoot your Microsoft 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 create an efficient architecture for an onsite or hybrid solution.
Progent's SQL Server experts can help you to port your data and apps, set up pilot environments to verify SQL Server 2019's ability to run your mission-critical applications, centralize management, create and implement a company-wide cybersecurity strategy that meets regulatory and industry mandates, accommodate mobile devices, and create a disaster recovery/business continuity plan that takes advantage of the advanced HA features integrated into SQL Server 2019.
Expertise available from Progent for Microsoft SQL Server 2019 include:
Contact Progent for SQL Server 2019 Consulting and Development Expertise
- Project Management: Progent can provide project management services to help you to adhere to best practices to ensure that your Microsoft SQL Server 2019 project is carried out on time and on budget. Project offers support from top-tier consultants to provide guidance for any aspect project management such as requirements identification, readiness assessment, solution architecture, migration planning, configuring pilot systems, deployment, tuning, documentation, education, administration, and as-needed online or onsite advice and debugging.
- Database Migration: Progent's veteran DBA consultants offer consulting supportfor all versions of SQL Server, even end-of-support (EOS) releases like SQL Server 2008 R2. Progent also offers Oracle expertise, MySQL software design and development, and Microsoft Access consulting. Progent has 20 years of background providing database solutions and can assist your organization to move your databases and apps to Microsoft SQL Server 2019 rapidly and affordably.
- Integration with Other Microsoft Products: Microsoft's extensive catalog of enterprise apps, operating systems, development environments, management tools, and cloud services depend on Microsoft SQL Server as their underlying database technology. Progent's Microsoft Gold-certified consultants can help you to integrate Microsoft SQL Server 2019 seamlessly with additional Microsoft apps by providing services such as Windows Server 2019 migration 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 planning and troubleshooting.
- Business Intelligence and Reporting: Progent's business intelligence (BI) and custom reporting consultants can assist your company to utilize Microsoft's BI stack to create and distribute BI reports that can be shared safely from any device. Progent can assist your organization build PowerPivot models and use Microsoft SQL Server 2019 Analysis Services to process tabular databases. Progent's Power BI specialists can assist your organization to communicate with Power BI datasets via Microsoft Report Builder or SQL Server Data Tools and post reports to SQL Server Reporting Services by using SSAS 2019 connections. Progent's consultants can also help you to integrate Microsoft SQL Server 2019 with third-party BI reporting platforms like Crystal Reports or upgrade from out-of-date reporting products like FRx.
- Network Infrastructure: Progent's Cisco-certified CCIE networking consultants can assist your business to plan, implement, manage, and protect a high-availability and responsive network architecture for your SQL Server 2019 environment. Progent can assist your business plan and implement an efficient infrastructure for any network model including on-premises or hybrid.
- Cybersecurity and Compliance: Progent's security and compliance experts have been awarded advanced network security certifications such as CISM, CISSP and CRISC. Progent can provide businesses of all sizes a range of cybersecurity services covering vulnerability evaluation, improvements, mitigation, and remote monitoring services to counter known and new cyber assaults like ransomware and to verify compliance with government and industry mandates.
- Virtualization Expertise: Progent can help your business to design and configure an affordable and manageable virtual environment for SQL Server 2019 and can provide access to certified Hyper-V consultants, VMware vSphere and vCloud experts, VMware NSX-certified virtualization consultants, and Citrix XenServer consultants to help you to deploy and manage a virtualized IT system.
- ProSight Remote Monitoring and Management Services: Progent provides a family of low-cost, flat-rate services designed to allow small and mid-size organizations to benefit from the same cutting-edge technology used by many of the industry's top corporations. The ProSight line of IT management outsourcing services covers all critical areas of modern networking such as email content filtering, threat defense based on machine leaning, cloud-based backup and disaster recovery, infrastructure management, virtual server cloud hosting, and IT asset documentation management.
- Disaster Disaster Recovery Planning: Progent's disaster preparedness specialists can assist your company to create and test a disaster recovery strategy that incorporates Microsoft SQL Server 2019's high-availability features to maximize network resilience and shorten restore time for mission-critical applications. Progent's business continuity planning experts can assist your company to develop and test a detailed strategy to keep your essential business processes operating in case of a catastrophe. The business continuity strategy deals with IT infrastructure but also covers the logistics associated with issues such as emergency alert technologies and processes, short-term office space, and regulatory requirements for reporting.
- Online Consulting and Troubleshooting: Progent has twenty years of experience providing high-level consulting, software programming, and troubleshooting services remotely. Using online expertise helps you to reduce the costs and delay of network disruptions. Progent has delivered online IT support to businesses of all sizes in each state in the U.S. See Progent's customer testimonials.) Progent can also provide on-site expertise in most metro areas throughout the United States and Canada.
To learn more about ways Progent can assist your company with Microsoft SQL Server 2019 planning, management, troubleshooting, or application development, call 1-800-993-9400 or visit Contact Progent.
If you require immediate remote help from a Microsoft-certified consultant, visit Progent's Remote IT Technical Support.
Learn more about Progent's Consulting Services for Microsoft .NET Server Platforms.