Microsoft SQL Server 2019 is a modern, self-hosted version of Microsoft's primary database system. The headline enhancement is the seamless combination of SQL Server with the Spark analytics engine, Hadoop Distributed File System (HDFS), and additional Linux-based big data components to support Big Data Clusters. The Big Data Cluster enables data-intensive AI tasks on the high-volume data held in HDFS and Microsoft 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 like Microsoft Azure AKS.
SQL 2019 allows you to analyze your structured data and high-volume big data clusters
Microsoft SQL 2019 also delivers enhancements in the core SQL database engine like feature parity for Linux systems. Other significant enhancements improve SQL Server Analysis Services (SSAS) and SSRS.
Since Microsoft SQL Server 2008 and SQL Server 2008 R2 have reached end of support status, moving to a modern version of SQL Server makes sense for businesses concerned about compliance, manageability, and competitiveness. Progent has twenty years of experience designing, implementing, optimizing, and debugging SQL Server solutions and can assist companies of all sizes to carry out an efficient upgrade to Microsoft SQL Server 2019. Progent's Microsoft Gold-certified SQL Server consultants can assist your business to evaluate the benefits for transitioning to SQL Server 2019 from any earlier release of Microsoft SQL Server and Progent offers rapid online or onsite access to experts in system architecture, software development, DBA services, and cybersecurity. Progent can assist you to install Microsoft SQL Server 2019 on premises, in a private or public cloud, or in a hybrid on-prem/cloud network model.
You can engage Progent's consultants on an as-needed basis for jobs like design review or debugging or you can get end-to-end project management and co-management services to ensure your Microsoft SQL Server 2019 initiative is completed on schedule and on budget. Progent offers the help of certified data security consultants, disaster recovery preparedness specialists, and SQL database software designers and programmers to make sure your Microsoft SQL Server 2019 ecosystem delivers top business value.
Progent's Microsoft Gold-certified consulting experts can also assist your business to configure Microsoft SQL Server 2019 to operate with other Microsoft apps and technologies by offering expertise that includes Windows Server 2019 integration consulting, Windows Hyper-V virtualization planning expertise, Exchange Server 2019 integration expertise, SharePoint 2019 development consulting, hybrid integration with Microsoft Azure cloud services, Microsoft Skype for Business integration consulting services, Dynamics GP custom reporting consulting services, and Microsoft System Center Operations Manager (SCOM) consulting.
Progent's seasoned software developers can help you to design, produce, validate, install, and troubleshoot apps that utilize SQL Server 2019 and Progent offers guidance in data modeling, OLTP, Data Warehousing and Mining, ETL, optimization, availability, backup/restore, OLAP, and integration with Amazon MWS. To learn more about Progent's SQL Server development and troubleshooting expertise, see SQL Server application programming experts.
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 edition of SQL Server 2019 and is designed for mission-critical applications requiring the most advanced levels of scale, reliability, and throughput. Compute power and RAM permitted per database are bound only by the OS limit, the amount of VMs allowed is unlimited, and database size can be up to 524 PB. HA features offered only on the Enterprise version of SQL Server 2019 include Always On availability groups, online page restore, online index create, resumable online index rebuilds, online schema change, and hot add RAM and compute. Expandability and throughput features for the Enterprise edition of SQL Server 2019 include in-RAM database, partitioned table parallelism, auto tuning, read ahead plus row mode memory grant feedback. The Enterprise version of Microsoft SQL Server 2019 also offers a richer collection of data warehousing services, Master Data Services and Reporting Services than other editions.
SQL Server 2019 Developer version is essentially the same as the Enterprise version, but is licensed for use only for application development and validation. The Developeredition is not permitted for use as a production system. SQL Server 2019 Developer handles only a single client for SQL Server Distributed Replay simulation.
SQL Server 2019 Standard version is intended for departments and small businesses who need an entry-level, low-overhead solution for information management and BI. Microsoft SQL Server 2019 Standard edition includes all the cybersecurity features of SQL Server 2019 Enterprise, but CPU scale is limited to the lesser 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 come with advanced R and Python integration and does not incorporate Machine Learning Services, but includes the remaining programmability features and the management tools provided with the Enterprise edition. High-end functions such as transparent data encryption and Big Data Clusters are provided with the Standard version.
Microsoft SQL Server 2019 Web version is an inexpensive option intended for web hosting organizations. Compute power is restricted to the smaller of 4 sockets or 16 cores, max buffer pool RAM is 64 MB, and maximum memory per instance 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 clusters, hybrid backup, or accelerated database recovery. The Web version also does not support transparent database or backup encryption. You can profile SQL Server Web by using SQL Server Enterprise or SQL Server Standard versions, and SQL Server Web comes with the other management tools included with the Standard edition. Analysis Services, tabular models, Business Intelligence Services, and some Reporting Services are not included.
SQL Server 2019 Express, offered in a few variants, is a free low-end version of Microsoft SQL Server designed for learning or for developing desktop, bare-bones-server, and end-user applications that do not require an advanced database. Compute scale is limited to the lesser of 1 socket or four cores, maximum buffer pool cache memory is 1.41 MB, and top RAM-optimized data is 352 MB. If applications developed with Microsoft SQL Server 2019 Express expand to require more capacity or additional functionality, you can migrate seamlessly to more powerful versions of SQL Server. Microsoft SQL Server 2019 Express with Advanced Services adds Basic R and SQL Graph services, the distributed replay management tool, and SQL Server Data Tools. SQL Server 2019 Express with Advanced Services can also host a SSRS database. SQL Server Express LocalDB is a ultra-lite edition of Express that operates in user mode and can be deployed without setup and with almost no prerequisites. The LocalDB edition of Express includes the programmability features of other Express releases but does not support in-memory (Online Transaction Processing.
Components of Microsoft SQL Server 2019
The SQL Server 2019 Installation Wizard allows you to choose which of the SQL Server components you want to include in your installation. These components include:
SQL Server Database Engine
The Database Engine is the heart of SQL Server's services for processing, holding, and protecting data. The Database Engine is used for replication, full-text search, tools for managing relational data, supporting in-database analytics, supporting PolyBase for accessing outside data repositories like Hadoop, and Machine Learning Services for analyzing Microsoft SQL Server 2019's relational data with Python and R scripts.
SQL Server Analysis Services (SSAS)
SQL Analysis Services is an OLAP and data mining resource for extracting high-value insights from data scattered over multiple databases or contained in a variety of files or tables.
SQL Server Reporting Services (SSRS)
SQL Server Reporting Services (SSRS) offers a collection of local tools and services that allow you to create, 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 utilize integrated Power BI apps for handheld endpoints to view the data you have in SQL Server Reporting Services.
You can view SSRS 2019 data via Power BI apps for mobile endpoints
SQL Server Integration Services (SSIS) is a set of programmable objects and zero-code graphical utilities for blending data from multiple sources. Extract, transform, and load (ETL) processes can be performed on XML data files, flat files, and relational data silos. SSIS can be utilized for duplicating or downloading files, loading data warehouses, cleaning up and mining data, and managing SQL Server data and objects.
Master Data Services
Master Data Services (MDS) is Microsoft SQL Server's Master Data Management (MDM) solution that enables businesses to build a centralized visualization of enterprise data, synchronizing data silos to eliminate redundancies and versioning models to preserve consistency. A Microsoft Excel extension allows you to share the master data set across an organization.
In-Database Machine Learning Services
Microsoft SQL Server 2019 Machine Learning Services allows you to run Python and R scripts with relational data without transferring data out of SQL Server or over your network to another server. The Microsoft Python and R packages can be used for predictive analytics.
New and Improved Features of Microsoft SQL Server 2019
The most prominent new feature for Microsoft SQL Server 2019 is Big Data Clusters, which enables you to process big data from Transact-SQL or Spark for AI solutions. Other new or improved features impact important technologies such as the SQL Server Database Engine, feature parity between SQL Server on Linux and SQL Server on Windows, SQL Server Analysis Services (SSAS), and SQL Server Reporting Services.
Big Data Clusters
trusted execution environment Server 2019 debuts the Big Data Cluster, a cluster of Linux containers orchestrated by Kubernetes. The Big Data Cluster enables you to configure and use scalable clusters in connected SQL Server, Spark, and Hadoop Distributed File System (HDFS) containers orchestrated by Kubernetes. Because these elements can run in parallel, The Big Data Clusters technology allows you to analyze big data from either Microsoft Transact-SQL or Spark and makes it simpler to combine and analyze your internal SQL Server relational data with. Deployment models include virtualized data for querying 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 data pool nodes. You can utilize Spark or built-in R, Python, Scala, and Java tools in Microsoft SQL Server 2019 for AI tasks on the data stored in HDFS storage pools and data pools.
Big Data Clusters allow you to analyze your relational data with unstructured big data
Other Improvements to the SQL Server 2019 Database Engine
Additional improvements to the Microsoft SQL Server 2019 Database Engine cover intelligent databases, the developer experience, security, high-availability (HA), and Linux and Windows feature set parity. Improvements to the SQL Server 2019 Database Engine include:
- Intelligent Query Processing increases the responsiveness of your current workloads and is turned on 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 technologies leverage hardware advances to improve performance and scale with new capabilities like direct PMEM device access, reduced Metadata contention in TempDB, and support for creating Database Snapshots of databases that contain memory-optimized filegroups.
- Intelligent performance enhancements in SQL Server 2019 address common bottlenecks and provide database setup options to promote more consistent responsiveness across all applications. Improvements include optimization for indexes prone to last-page insert contention, Query Store plan forcing ability for fast forward and static cursors, more granular control for configuring maximum memory grants for resource governance, reduction in unneeded recompilations, improved scalability for indirect checkpoint, improved concurrent updates for PFS, and scheduler worker migration for long-running tasks to achieve evenly distributed CPU utilization.
- Enhancements to the developer experience include partition tables and index partitioning for graph tables, support for UTF-8 import and export variable-width character encoding, an advanced Java SDK, new spatial reference identifiers (SRIDs) more closely aligned with GPS, and more verbose error messages to streamline troubleshooting data truncation issues in unsuccessful extract, transform, and load (ETL) processes.
- Cybersecurity improvements include the Always Encrypted feature with secure enclaves, which enables operations on plaintext data inside the trusted execution environment of a secure server-side enclave instead of only on on the client side. This avoids the need to move data into and out of the database for operations such as key rotation, thereby improving encryption performance and reliability. Also, certificate infrastructure management tools are integrated into the SQL Server Configuration Manager. This simplifies tasks such as checking and validating certificates, flagging certificates close to expiration, and deploying certificates throughout machines in Availability Groups and Failover Clusters. An integrated Data Discovery and Classification feature assists you to categorize and name columns in user tables. This can make it easier to identify where sensitive information resides and to follow data security standards and compliance regulations.
Microsoft SQL Server 2019's secure enclaves offer a trusted environment for processing sensitive SQL data
Improvements to SQL Server 2019 Analysis Services (SSAS)
- For enhanced availability, SQL Server 2019 expands the highest allowable number of synchronous replicas in an Availability Group from three to five. You can set up this group of replicas with auto failover inside the group. You can deploy one primary replica along with four synchronous secondary replicas within a group. In addition, secondary to primary replica read/write connection redirection for Always On AGs permits client app connections to be directed to the primary replica regardless of what target server is called for the connections string. This feature brings replica connection redirection for implementations that have no an Availability Group listener such as a multi-subnet deployment in the cloud.
- Microsoft's SQL Server Database Engine recovery technology has been re-engineered in SQL Server 2019 to include Accelerated Database Recovery. This capability dramatically shortens the time needed to complete the recovery process following a failover or other non-clean shut down. Accelerated Database Recovery also cuts the time needed to complete rollback of aborted long-duration transactions.
- SQL Server on Linux has come close to reaching feature equality with SQL Server on Windows with the addition of new SQL Server on Linux features that include support for replication, the Microsoft Distributed Transaction Coordinator (MSDTC), Active Directory authentication, Python and R, PolyBase, Change Data Capture (CDC), plus improved scale in TempDB.
SQL Server 2019 includes new and improved features for SQL Server Analysis Services that boost throughput, resource governance, and client support. These improvements include calculation groups in tabular models for grouping common measure expressions, query interleaving for faster query performance, many-to-many relationships in tabular models, and online attach for synchronizing read-only replicas.
Enhancements to SQL Server 2019 Reporting Services
Improvements to SSRS 2019 include:
How Progent Can Help Your Business to Design, Integrate, and Manage Your Microsoft SQL Server 2019 Deployment
- 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 Managed Instance hosted either in a virtual machine or in your data center. You need to use your database credentials for connecting with SQL MI.
- Support for connecting to Power BI Premium datasets via Microsoft Report Builder or SSDT. You can publish those reports to SQL Server 2019 Reporting Services (SSRS) by utilizing SSAS 2019 connectivity.
- Azure Active Directory Application Proxy support eliminates the necessity of managing your own web application proxy to allow secure access through the web or mobile apps.
- SQL Server 2019 supports Transparent Database Encryption for the SQL Server 2019 Reporting Services catalog database for both Enterprise and Standard versions of SQL Server.
Progent's Microsoft Gold-certified consultants can provide online and on-premises guidance to assist you to plan, implement, manage, optimize, and troubleshoot your Microsoft SQL Server 2019 environment. Progent's consultants can assist you to evaluate the advantages of upgrading to Microsoft SQL Server 2019 and can show you how to design an efficient infrastructure for an on-premises or hybrid solution.
Progent's SQL Server experts can help you to migrate your data and apps, configure pilot environments to verify SQL Server 2019's ability to run your business-critical applications, simplify management, create and implement an enterprise-wide cybersecurity plan that complies with regulatory and business requirements, accommodate mobile devices, and create and test a disaster recovery/business continuity strategy that takes advantage of the advanced HA technologies incorporated in Microsoft SQL Server 2019.
IT consulting services offered by Progent for SQL Server 2019 include:
Contact Progent for SQL Server 2019 Consulting
- Project Management and Co-management: Progent offers project management support services to help your company to follow best practices to ensure that your Microsoft SQL Server 2019 initiative 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 analysis, preparedness evaluation, system architecture, migration planning, configuring test systems, deployment, optimization, documentation, education, management, and on-demand online or onsite consulting and troubleshooting.
- Database Migration: Progent's veteran Database Administration experts offer consulting supportfor all versions of Microsoft SQL Server, including end-of-support (EOS) versions such as SQL Server 2008 R2. Progent also offers Oracle expertise, MySQL software design and development, and Microsoft Access expertise. Progent has two decades of experience providing database solutions and can assist your organization to move your databases and applications to Microsoft SQL Server 2019 quickly and efficiently.
- Integration with Other Microsoft Applications: Microsoft's vast line of enterprise applications, operating systems, development platforms, management tools, and IT services rely on SQL Server as their underlying database engine. Progent's Microsoft Gold-certified consultants can help your business to integrate SQL Server 2019 seamlessly with additional Microsoft apps by providing consulting services such as Windows Server 2019 upgrade consulting, SharePoint Server 2019 application development, Power BI reporting expertise, Exchange Server 2019 migration expertise and Office 365 Exchange Online integration consulting, and Active Directory planning and debugging.
- Business Intelligence (BI) and Custom Reporting: Progent's business intelligence (BI) and reporting consultants can help you to utilize Microsoft's business Intelligence (BI) stack to create and publish BI dashboards and reports that can be shared safely via any device. Progent can assist you build PowerPivot models and use SQL Server 2019 Analysis Services to manage tabular databases. Progent's Power BI specialists can assist your organization to connect to Power BI datasets using Microsoft Report Builder or SQL Server Data Tools and publish reports to SQL Server Reporting Services (SSRS) by using SQL Server Analysis Services connections. Progent's consultants can also assist you to integrate SQL Server 2019 with third-party BI reporting products like Crystal Reports or migrate from legacy reporting products like FRx.
- Infrastructure: Progent's Cisco-certified CCIE networking consultants can assist your organization to design, implement, manage, and protect a resilient and responsive network infrastructure for your Microsoft SQL Server 2019 environment. Progent can help you design and deploy an efficient solution for any network model including onsite or hybrid.
- Security and Compliance: Progent's cybersecurity and compliance consultants have been awarded advanced data security certifications including CISA, CISSP and GIAC. Progent offers organizations of all sizes a variety of cybersecurity services including vulnerability evaluation, enhancement, mitigation, and remote monitoring services to protect against known and new cyber attacks like ransomware and to validate compliance with government and industry requirements.
- Virtualization Support: Progent can assist your company to plan and implement an affordable and manageable virtualization solution for Microsoft SQL Server 2019 and can provide access to certified Hyper-V consultants, VMware vSphere and vCloud experts, VMware NSX-certified consulting experts, and Citrix XenServer consultants to help your business to deploy and maintain a virtualized IT system.
- ProSight Remote Monitoring and Management Services: Progent provides a family of low-cost, fixed-price services designed to enable small and medium-size organizations to utilize the same cutting-edge technology used by some of the world's top enterprises. The ProSight line of IT management outsourcing services addresses all critical aspects of information technology including email protection, security and compliance, automated backup and disaster recovery, network infrastructure management, VM hosting, and network resources documentation management.
- Disaster Disaster Recovery Preparedness: Progent's disaster preparedness specialists can help you to develop and test a DR/BC plan that incorporates SQL Server 2019's HA technology to maximize network resilience and minimize restore time for critical applications and services. Progent's business continuity planning consultants can help your organization to develop and test a detailed strategy to keep your core business processes running in the event of a catastrophe. The business continuity plan deals with IT infrastructure but also covers preparation for problems like alternative alert technologies and processes, temporary office space, and regulatory requirements for reporting.
- Online Consulting and Troubleshooting: Progent has two decades of background delivering high-level consulting, software programming, and troubleshooting services remotely. Access to remote expertise allows you to reduce the expense and duration of network downtime. Progent has delivered remote network support to organizations in every state in the U.S. Refer to Progent's customer testimonials.) Progent can also provide on-premises expertise in most metro areas across the United States and Canada.
To find out more about how Progent can assist your company with Microsoft SQL Server 2019 solution design, integration administration, troubleshooting, or application development, call 1-800-993-9400 or see Contact Progent.
If you require immediate remote help from a Microsoft-certified expert, visit Progent's Online Network Support.
Learn more about Progent's Consulting Services for Microsoft .NET Server Platforms.