SQL Server 2019 is a modern, self-hosted release of Microsoft's flagship database system. The headline feature is the seamless combination of SQL Server with Spark, Hadoop Distributed File System, and other Linux-driven big data technologies to enable Big Data Clusters. The Big Data Cluster enables data-intensive AI and machine learning tasks on the data resident in HDFS and SQL Server storage pools. You can implement Big Data Clusters as containers in on-premises Kubernetes clusters, such as AKS on Azure Stack, or in any cloud with a Kubernetes service like Azure Kubernetes Service (AKS).
SQL 2019 allows you to combine your relational data and unstructured big data clusters
Microsoft SQL 2019 also delivers improvements in the fundamental SQL Server database technology such as feature parity for Linux machines. Additional important innovations impact SQL Server Analysis Services and SQL Server Reporting Services.
Because SQL Server 2008 and SQL Server 2008 R2 have reached end of support status, moving to a current version of Microsoft SQL Server is a priority for organizations concerned about cybersecurity, compatibility, and competitiveness. Progent has twenty years of experience designing, implementing, optimizing, and troubleshooting SQL Server solutions and can help organizations of any size to perform an efficient upgrade to SQL Server 2019. Progent's Microsoft Gold-certified SQL Server consultants can help your business to assess the benefits for upgrading to SQL Server 2019 from any earlier version of SQL Server and Progent offers quick remote or on-premises access to top-tier consultants in system architecture, application development, database administration, and cybersecurity. Progent can help your business to install SQL Server 2019 locally, in the cloud, or in a hybrid on-prem/cloud network architecture.
You can engage Progent's consultants on an occasional basis for tasks like system architecture review or troubleshooting or you can get comprehensive project management and co-management services to make sure your SQL Server 2019 migration is successfully carried out on time and within budget. Progent can provide the help of certified data security experts, disaster recovery planning consultants, and SQL database software designers and programmers to make sure your Microsoft SQL Server 2019 ecosystem provides top return on investment.
Progent's Microsoft Gold-certified IT experts can also help your organization to configure SQL Server 2019 to work in conjunction with additional Microsoft products and services by providing support that includes Windows Server 2019 consulting, Hyper-V configuration expertise, Exchange Server 2019 integration expertise, SharePoint 2019 design and deployment expertise, hybrid configuration with Azure cloud services, Skype for Business configuration consulting, Microsoft Dynamics GP/Great Plains custom reporting expertise, and Microsoft System Center Operations Manager (SCOM) experts.
Progent's veteran software developers can help your business to design, create, validate, install, and troubleshoot apps that utilize SQL Server 2019 databases and Progent can provide expertise in data modeling, OLTP, Data Warehousing and Mining, ETL, optimization, availability, backup/restore, reporting, and integration support for Amazon Marketplace Web Service. To find out more about Progent's SQL Server programming and troubleshooting services, see SQL Server application development experts.
Microsoft SQL Server 2019 Versions
SQL Server 2019 is available in five basic editions distinguished by price, performance, scale and licensing rules. Progent can help you decide which editions make sense for your needs, and Progent can also help you simplify license management.
Microsoft SQL Server 2019 Enterprise is the top-of-the-line version of Microsoft SQL Server 2019 and is intended for mission-critical applications that call for the highest advanced levels of capacity, availability, and performance. Processing power and memory supported per database are bound only by the operating system limit, the amount of VMs supported is unlimited, and relational database size can be as high as 524 PB. HA features unique to the Enterprise edition of SQL Server 2019 include Always On availability groups for collective failover, online page restore, online index create and rebuild, suspend and resume online index rebuilds, online schema change, and hot add memory and compute. Scalability and throughput functions exclusive to the Enterprise version of SQL Server 2019 include in-RAM database, resource governance, auto tuning, read ahead plus row mode memory grant feedback. The Enterprise edition of SQL Server 2019 also supports a more complete collection of data warehousing services, Analysis Services and Advanced Integration Services than other versions.
SQL Server 2019 Developer edition is basically identical to the Enterprise version, but is licensed for use only for application development and testing. The Developeredition is not permitted for use in 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 organizations who require a basic, minimum-overhead solution for data management and business intelligence. Microsoft SQL Server 2019 Standard version includes all the cybersecurity features of SQL Server 2019 Enterprise, but processor capacity is limited to the lesser of four sockets or 24 cores. For each instance of SQL Server Database Engine, maximum RAM for the buffer pool cache is 128 GB, and max 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 functions as well as the administration tools the come with the Enterprise version. Advanced features such as accelerated database recovery and Big Data Clusters are now provided with the Standard version.
Microsoft SQL Server 2019 Web edition is an inexpensive package intended for web hosting companies. CPU power is limited to the lesser of 4 sockets or 16 cores, maximum buffer pool memory is 64 MB, and maximum RAM per instance is 64 GB. In contrast to the Standard version of Microsoft SQL Server 2019, SQL Server 2019 Web version does not support Always On failover cluster instances, basic availability groups, or transactional replication to Windows Azure. The Web version also does not offer transparent database encryption. You can profile Microsoft SQL Server Web by using SQL Server Enterprise or SQL Server Standard versions, and SQL Server Web includes the other management features included with the Standard version. Analysis Services, multidimensional models, Business Intelligence Services, and certain Reporting Services are not provided.
SQL Server 2019 Express, offered in a few variants, is a free entry-level version of Microsoft SQL Server intended for learning or for creating desktop, small-server, and end-user apps that do not need an advanced database platform. Compute capacity is restricted to the smaller of 1 socket or four cores, max buffer pool cache RAM is 1.41 MB, and maximum RAM-optimized data is 352 MB. If applications created with Microsoft SQL Server 2019 Express expand to call for more capacity or additional functionality, you can migrate smoothly to more advanced editions of Microsoft SQL Server. Microsoft SQL Server 2019 Express with Advanced Services includes Basic R and SQL Graph integration, the distributed replay management tool, and SSDT. SQL Server 2019 Express with Advanced Services can also support a SQL Server Reporting Services database. SQL Server Express LocalDB is a ultra-lite version of Express that runs in user mode and can be installed without setup and with virtually no prerequisites. The LocalDB edition of Express shares all the programmability features of other Express releases but does not support in-memory OLTP.
Elements of Microsoft SQL Server 2019
The Microsoft SQL Server 2019 Installation Wizard enables you to choose which of the SQL Server parts you wish to include in your installation. These components include:
The Database Engine is the core of SQL Server's services for computing, storing, and protecting data. The Database Engine is used for replication, full-text search, utilities for managing relational data, supporting in-database analytics, supporting PolyBase for working with external data sources such as Hadoop, and Machine Learning Services for processing SQL Server 2019's relational data with Python and R scripts.
SQL Server Analysis Services
SQL SSAS is an Online Analytical Processing and data mining resource for extracting high-value information from data dispersed across separate databases or contained in various files or tables.
SQL Server Reporting Services
SSRS provides a collection of local utilities and services that allow organizations to create, share, and administer tabular, matrix, graphical, and unstructured reports. You can create device-aware smart phone reports with the Mobile Report Publisher app and use native Power BI apps for mobile endpoints to connect to the data processed in SQL Server Reporting Services.
You can view SSRS 2019 data from Power BI apps for handheld devices
SQL Server Integration Services
Integration Services is a collection of programmable objects and zero-code graphical tools for migrating data from a wide variety of sources. Extract, transform, and load (ETL) processes can be performed on XML data files, flat files, and relational data silos. Integration Services can be utilized for copying or downloading files, adding to data warehouses, cleaning up and mining data, and controlling SQL Server data and objects.
Master Data Services
Master Data Services is Microsoft SQL Server's Master Data Management (MDM) solution that allows businesses to build a single look at corporate-wide data, synchronizing data sources to avoid redundancies and versioning data organization models to preserve consistency. A Microsoft Excel extension makes it easy to share the master data set throughout an organization.
In-Database Machine Learning Services
SQL Server 2019 Machine Learning Services enables you to run Python and R scripts with relational data sources without transferring data out of SQL Server 2019 or over your network to another server. The Microsoft Python and R packages can be used for predictive analytics and machine learning.
New and Improved Features of SQL Server 2019
The most prominent new feature for SQL Server 2019 is Big Data Clusters, which allows you to analyze big data from Transact-SQL or Spark for machine learning applications. Additional new or enhanced features cover important technologies including the SQL Server Database Engine, feature equivalence between Microsoft SQL Server on Linux and Microsoft SQL Server on Windows, 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 use massively scalable clusters in interrelated SQL Server, Apache Spark, and Hadoop Distributed File System containers managed by Kubernetes. Because these elements can run concurrently, The Big Data Clusters feature permits you to analyze big data from either Transact-SQL or Apache Spark and makes it easy to aggregate and analyze your SQL Server relational data with unstructured big data. Deployment scenarios include virtualized data for querying external data sources without transferring or copying the data, data lakes made from HDFS pools to hold big data, and scale-out data marts to spread data from various sources across data pool nodes. You can use Apache Spark or built-in R, Python, Scala, and Java tools in 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 big data
Additional Improvements to the SQL Server 2019 Database Engine
Additional improvements to the SQL Server 2019 Database Engine address smart databases, the developer environment, security, high-availability (HA), and Linux/Windows feature set parity. Enhancements to the SQL Server 2019 Database Engine include:
- Intelligent Query Processing (IQP) improves the performance of your current workloads and is activated automatically on the latest database compatibility level setting. 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 contemporary hardware to enhance throughput and scale with new capabilities like hybrid buffer pool, memory-optimized TempDB metadata, and the ability to create snapshots of SQL Server databases that include memory-optimized filegroups.
- Intelligent throughput improvements in SQL Server 2019 address typical bottlenecks and offer database setup options to ensure more predictable execution speed for all your workloads. Enhancements include optimization within the database engine for indexes prone 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, reduction in incidents of unnecessary recompilations, improved indirect checkpoint scalability, improved concurrent updates for Page Free Space (PFS), and scheduler worker migration for more balanced CPU usage.
- Improvements to the developer environment include partition tables and index partitioning for graph tables, UTF-8 import and export encoding, a new Java SDK, new spatial reference identifiers better aligned with global positioning systems, and more extensive error messages to simplify debugging truncation issues in unsuccessful ETL processes.
- Cybersecurity improvements include the Always Encrypted feature with secure enclaves, which enables operations on plaintext data within a secure enclave on the server side rather than only on on the client. This eliminates the need to move data into and out of the database for operations such as initial data encryption, thereby improving encryption performance and reliability. Also, certificate infrastructure management tools are integrated into the SQL Server Configuration Manager. This simplifies processes like viewing and verifying SSL/TLS certificates, flagging certificates nearing their expiration, and implementing certificates across machines within Availability Groups and Failover Clusters. An integrated Data Discovery and Classification feature assists you to classify and name columns in user tables. This can help you identify where sensitive information is located and to follow data security standards and compliance regulations.
Microsoft SQL Server 2019's secure enclaves provide a safe environment for working with sensitive SQL data
Enhancements to SSAS 2019
- For improved availability, SQL Server 2019 ups the highest allowable allowable synchronous replicas in an Availability Group (AG) from 3 to 5. You can configure this replica group with auto failover within the group. You can have 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 Availability Groups allows client app connections to be directed to the primary replica regardless of what target server is called for the connections string. This capability brings replica connection redirection for deployments that do not include an AG listener such as a multi-subnet configuration in the cloud.
- The SQL Server Database Engine recovery technology has been revamped in SQL Server 2019 to include the Accelerated Database Recovery feature. This feature dramatically shortens the time required to finish the recovery process after a failover or other abrupt termination. SQL Server Accelerated Database Recovery also shrinks the time required to execute rollback of aborted long-duration transactions.
- Microsoft SQL Server on Linux has come close to achieving feature equality with SQL Server on Windows through the addition of additional SQL Server on Linux functions such as support for replication, the Microsoft Distributed Transaction Coordinator, Active Directory authentication with SQL Server on Linux, Machine Learning Services, PolyBase, Change Data Capture, plus enhancements in TempDB file size and auto growth.
Microsoft SQL Server 2019 includes new and improved functions for SQL Server Analysis Services that enhance performance, resource governance, and client support. These improvements include calculation groups in tabular models to reduce redundancy, query interleaving, new memory settings for resource governance, and the ability to attach a tabular model as an online operation.
Enhancements to SQL Server 2019 Reporting Services
Improvements to SSRS 2019 include:
How Progent Can Assist You to Design, Integrate, and Manage Your Microsoft SQL Server 2019 Deployment
- Support for Azure SQL Managed Instance allows you to host a database catalog for SSRS 2019 in an Azure SQL MI hosted either in a virtual machine or in your data center. You are required to use your database credentials for connecting with SQL MI.
- Support for connecting to Power BI datasets using Microsoft Report Builder or SQL Server Data Tools. You can then publish those reports to SSRS 2019 using SQL Server 2019 Analysis Services (SSAS) connectivity.
- Azure AD Application Proxy does away with the necessity of managing your own web application proxy in order to allow secure access through the web or via mobile apps.
- Microsoft SQL Server 2019 works with Transparent Database Encryption for the SQL Server 2019 Reporting Services catalog database for Enterprise and Standard versions.
Progent's Microsoft Gold-certified experts offer remote and onsite expertise to help you to plan, implement, manage, optimize, and debug your Microsoft SQL Server 2019 deployment. Progent's consultants can assist you to assess the advantages of upgrading to SQL Server 2019 and can show you how to design an appropriate architecture for an on-premises or hybrid onsite/cloud solution.
Progent can assist your company to migrate your data and apps, set up test systems to verify SQL Server 2019's ability to support your business-critical applications, centralize management, design and implement a comprehensive security plan that complies with government and business mandates, accommodate mobile devices, and create a disaster recovery/business continuity strategy that takes advantage of the modern high-availability features integrated into Microsoft SQL Server 2019.
Expertise offered by Progent for Microsoft SQL Server 2019 include:
Contact Progent for Microsoft SQL Server 2019 Consulting and Development Expertise
- Project Management: Progent offers project management support services to assist your company to adhere to best practices to make sure your SQL Server 2019 project is successfully completed on time and within budget. Project offers support from IT experts to provide guidance for any aspect project management such as requirements identification, preparedness evaluation, system architecture, upgrade planning, building pilot environments, deployment, optimization, documentation, training, administration, and as-needed remote or onsite advice and troubleshooting.
- Database Migration: Progent's veteran Database Administration experts offer consulting supportfor all versions of SQL Server, even end-of-support (EOS) releases such as SQL Server 2008 R2. Progent can also provide Oracle consulting, MySQL software design and programming, and Microsoft Access expertise. Progent has 20 years of experience delivering database solutions and can assist your organization to port your databases and apps to Microsoft SQL Server 2019 quickly and affordably.
- Integration with Other Microsoft Applications: Microsoft's extensive catalog of enterprise applications, operating systems, development environments, management utilities, and IT services rely on SQL Server as their underlying database technology. Progent's Microsoft Gold-certified consultants can help your business to deploy SQL Server 2019 efficiently with additional Microsoft apps by providing services that include Windows Server 2019 upgrade consulting, SharePoint Server 2019 application design, Power BI business reporting expertise, Exchange Server 2019 migration expertise and Microsoft 365 Exchange Online integration solutions, and Active Directory design and debugging.
- Business Intelligence (BI) and Custom Reporting: Progent's business intelligence and custom reporting consultants can assist you to use Microsoft's business Intelligence (BI) stack to create and distribute BI reports that can be shared safely via any device. Progent can assist your organization create PowerPivot models and utilize SQL Server 2019 Analysis Services to manage tabular databases. Progent's Power BI specialists can help your organization to connect to Power BI datasets using Microsoft Report Builder or SQL Server Data Tools and post reports to SQL Server Reporting Services by using SQL Server Analysis Services connections. Progent's BI experts can also help your company to integrate SQL Server 2019 with third-party BI reporting platforms like Crystal Reports or migrate from legacy reporting tools such as FRx.
- Infrastructure: Progent's Cisco-certified CCIE networking consultants can help your organization to plan, configure, administer, and secure a resilient and responsive network infrastructure for your Microsoft SQL Server 2019 ecosystem. Progent can help you plan and integrate an optimal infrastructure for any network model including on-premises or hybrid.
- Security and Compliance: Progent's security and compliance specialists have been awarded advanced information security certifications such as CISM, ISSAP and CRISC. Progent offers organizations of any sizes a variety of security services including vulnerability evaluation, improvements, mitigation, and remote monitoring and management services to protect against known and zero-day malware assaults like ransomware and to verify compliance with government and industry standards.
- Virtualization Support: Progent can help your company to design and configure a cost-effective and manageable virtual environment for Microsoft SQL Server 2019 and offers access to certified Windows Hyper-V consultants, VMware vSphere and vCloud experts, VMware NSX-certified consulting experts, and Citrix XenServer consultants to assist your organization to configure and maintain a virtualized ecosystem.
- ProSight Remote Monitoring and Management Services: Progent offers a selection of low-cost, flat-rate services designed to enable small and medium-size businesses to utilize the same best-of-breed IT solutions used by some of the industry's top enterprises. The ProSight series of network monitoring and management services covers all critical aspects of modern networking including email protection, behavioral-based threat defense, cloud-based backup/recovery, infrastructure monitoring and management, virtual server cloud hosting, and network asset documentation management.
- Disaster Recovery Planning Preparedness: Progent's disaster preparedness consultants can assist your organization to develop and test a disaster recovery strategy that incorporates Microsoft SQL Server 2019's HA technology to maximize network resilience and minimize restore time for vital applications. Progent's business continuity preparedness experts can assist your business to create and validate a comprehensive strategy to keep your core business processes running in case of a catastrophe. The business continuity strategy deals with IT infrastructure but also covers preparation for problems like emergency notification systems and processes, short-term office space, and regulatory mandates for reporting.
- Remote Consulting and Troubleshooting: Progent has twenty years of experience providing high-level consulting, application development, and troubleshooting services online. Access to online expertise allows you to minimize the costs and delay of network disruptions. Progent has delivered remote IT support to businesses of all sizes in each state in the U.S. See Progent's customer testimonials.) Progent also offers on-site expertise in most metropolitan areas across the U.S. and Canada.
To learn more about how Progent can help your company with SQL Server 2019 planning, integration management, debugging, or application development, call 1-800-993-9400 or visit Contact Progent.
If you require immediate online help from a Microsoft-certified support expert, see Progent's Online IT Support.
Learn more about Progent's Consulting Services for Microsoft .NET Server Platforms.