Microsoft Gold-Certified Consulting

SQL Server 2016 introduces major enhancements in performance, security, availability plus integrated analytics and reporting compared to previous versions of Microsoft's market-leading data platform. SQL Server 2016's management, development tools and scalability features seamlessly span on-premises, private cloud and public cloud deployments and allow operations and services to be accessed anywhere on any device. Headline improvements in SQL Server 2016 enhance key areas such as Big Data integration, security/compliance, and Mobile Business Intelligence (BI) by introducing new functionalities including Stretch Database, PolyBase for handling SQL and NoSQL data, Query Store, Always Encrypted, R Services and Mobile Reports.

Progent's Microsoft-certified SQL Server consultants have worked extensively with all current and legacy versions of SQL Server and can help you evaluate the business case for moving to SQL Server 2016 from earlier versions or from other data platforms, migrate your applications and data, optimize your network infrastructure to support a cloud or on-premises or hybrid deployment strategy, provide software development and DBA services, and deliver online or onsite training customized for individuals or groups within your organization. Progent can provide the services of a SQL Server consultant who has earned advanced certifications in SQL Server technology including Microsoft Certified Solution Master: Data Platform and Microsoft Certified Trainer. Progent also offers wireless integration expertise and can help you support a productive and secure BYOD ecosystem for SQL Server 2016's mobile business intelligence features by providing services that include iPhone and iPad integration and Android phone and tablet consulting. If you currently have a SQL Server 2016 environment, Progent can provide the guidance of a SQL Server 2019 migration consultant to help you upgrade smoothly to a modern version of SQL Server.

Progent can help businesses of any size to plan, deploy, manage or troubleshoot any edition of SQL Server 2016. Progent's Microsoft-certified consulting team can also help you integrate SQL Server 2016 with other core Microsoft platforms including Windows Server and Hyper-V, Exchange, SharePoint, Dynamics CRM and GP, Lync or Skype for Business, and System Center Operations Manager (SCOM) and Configuration Manager (SCCM).

Progent's certified SQL Server developers can help with all stages of software development life cycle and offer expertise in:

• Data Modeling
• OLTP
• Data Warehousing
• Data Mining
• ETL
• Performance Tuning
• Disaster Recovery
• Business Analytics
• Integration with Amazon Marketplace Web Service
• Troubleshooting

Editions of SQL Server 2016
Microsoft offers five editions of SQL Server 2016 that vary significantly in scale, functionality and price. Progent can help you determine which edition of SQL Server and which components are most appropriate for your applications. Progent can also help you understand licensing options and assist you to migrate from one edition to another.

SQL Server 2016 Express is a no-cost database suitable for desktop and lightweight server apps. Each version of SQL Server Express is limited to one socket or four cores plus 1 GB of memory for each instance of SQL Server Database engine and allows a maximum relational database size of 10 GB. SQL Server 2016 Express with Tools adds SQL Server Management Studio (SSMS), the Distributed Replay Admin Tool, and the LocalDB feature. SQL Server 2016 Express with Advanced Services adds SQL Server Data Tools (SSDT) and Basic R integration.

SQL Server 2016 Standard offers fundamental database management and BI support for departmental or small business applications and is designed to support local or cloud-based environments. The Standard edition can support 4 sockets or 24 cores, 128 GB of RAM per instance of SQL Server Database engine, and relational database size of up to 524 PB. Maximum memory utilized per instance of Analysis Services is 16 GB for Tabular and 64 GB for MOLAP. A few of the many features supported in SQL Server 2016 Standard edition that are unavailable in Express editions include Buffer Pool Extension, Delayed Durability, row-level security, dynamic data masking, support for basic availability groups, backup encryption, Oracle publishing, peer-to-peer transactional replication, SQL Profiler, SQL Server Agent, SCOM Management Pack, Database Tuning Advisor (DTA), SQL query edit and design tools, PolyBase compute node, Always On failover cluster instances, plus analysis services, BI semantic model, and basic data mining support.

SQL Server 2016 Enterprise, the top-of-the-line edition of SQL Server 2016, is designed for mission-critical applications and supports massive scale, industry-leading performance, unlimited virtualization, and world-class availability. Compute and memory capacity are limited only by the host operating system. Many of the features uniquely available in the Enterprise edition address availability. These include connection director, online page and file restore, online indexing and online schema change, fast recovery, mirrored backups, and hot add memory and add CPU. Unique scalability and performance features include real-time operational analytics, In-Memory Column Store, table and index partitioning, data compression, Resource Governor, Partition Table Parallelism, Multiple Filestream containers, NUMA-Aware and Large Page Memory and Buffer Array Allocation, IO Resource Governance, and In Memory OLTP. Other features available only in the Enterprise edition of SQL Server 2016 include comprehensive data warehousing and data mining support, advanced integration services adapters and transforms, plus Master Data and Data Quality Services.

SQL Server 2016 Developer edition, available for free, has all the features and functionality of SQL Server 2016 Enterprise and is designed to enable developers to create full-scale applications powered by SQL Server 2016. The Developer edition is not licensed for use as a production server.

SQL Server 2016 Web edition is an economical data solution for hosting web sites and offers less scale than the Standard edition of SQL Server 2016 and fewer high-availability features. The Web edition can support 4 sockets or 16 cores, 64 GB of RAM per instance of SQL Server Database engine, and relational database size of up to 524 PB. Data mining, data warehousing, and business intelligence features are not supported.

New and Enhanced Features of SQL Server 2016
Microsoft SQL Server 2016 includes a massive array of new and enhanced features including a more powerful database engine, improved management for SQL Server 2016 Analysis Services (SSAS), easier deployment and debugging with SQL Server Integration Services (SSIS), a new web portal for SQL Server Reporting Services (SSRS), improved scale and performance for Master Data Services (MDS), and the introduction of SQL Server R Services.

SQL Server 2016 Database Engine
Enhancements to the SQL Server 2016 Database Engine deliver improvements in performance, scale, manageability, availability, security and other areas. Some of these enhancements require the Enterprise edition of SQL Server. Significant enhancements include:

• Performance and scaling improvements remove limitations on data size.
• Built-in Java Script Object Notation (JSON) support enables importing and exporting JSON and working with JSON strings.
• PolyBase, optimized for data warehousing, allows you to use T-SQL to access and query non-relational data in Hadoop or Azure Storage Blobs or files, query semi-structured data, and combine the results with relational data sets stored in SQL Server.
• Stretch Database allows you to migrate online OLTP data and/or closed data transparently to Microsoft Azure on demand without application changes. This is ideal for transactional databases with large amounts of historical data stored in a small number of tables. The Stretch Database Advisor feature of SQL Server 2016 Upgrade Advisor can help identify databases and tables appropriate for Stretch Database.
• Security enhancements include Row-level Security, Always Encrypted for defending against unscrupulous administrators and backup thieves, Dynamic Data Masking for masking sensitive data from non-privileged users by controlling how the data appears in the output of database queries, the adoption of AES encryption as the default encryption for Endpoints, and support for creating a credential at the database level rather than only at the server level.
  
  Security innovations like Dynamic Data Masking (DDM) help protect sensitive data

• T-SQL enhancements include R extensions for advanced analytics, support for the new Always Encrypted feature, support for creating database-level credentials, expanded support for compression, plus the addition of new string functions.
• Support for Always On Basic Availability Groups in the Standard edition of SQL Server 2016
• Symmetric multi-processing (SMP) solutions for data warehousing applications are available for on-premises deployments and for the cloud via SQL Server Fast Track Data Warehouse for Azure.
• The Microsoft Analytics Platform System supports scale-out, massively parallel processing (MPP) solutions for hybrid data warehouse applications and offers 100-times faster query performance across relational and non-relational data plus 15-times better data compression than conventional data warehouses with In-memory Columnstore.
• Real-time operational analytics is enabled by the introduction of columnstore indexes. You can run analytics workloads against the columnstore indexes and at the same time run transactional workloads against the rowstore, keeping transactional changes always up to date for analytics.
• AlwaysOn Availability Groups now support readable secondary replicas, allowing analytics queries to run on secondaries without impacting operational workloads on the primary replica.
• Replication is supported for memory-optimized tables and to Azure SQL Database.
• Faster and more granular back up and restore between on-premises databases to Microsoft Azure
• Distributed Availability Groups allow secondary replicas of an availability group to exist in different geographical regions than the primary. This enhances disaster recovery and enables serving read-only workloads for remote regions.
• Load Balancing of readable secondaries improves scalability by allowing you to define groups of readable secondaries to load balance.

• Tabular 1200 Models support includes faster metadata operations for tabular 1200 models in SSDT than for level 1103 models in SQL Server 2014, project templates for tabular 1200 models in SSDT for building relational and Business Intelligence projects, display folders for tabular 1200 models defined in SSDT and rendered in applications like Excel or Power BI for easier field list navigation, built-in support for bi-directional cross filters in tabular models, and the ability to store translated metadata in a tabular 1200 model.
• Scripting improvements include PowerShell enhancements for tabular models at compatibility level 1200, support for scripted database and administrative operations by SSMS with TMSL output in JSON, and Tabular Model Scripting Language (TMSL) support in Analysis Services Execute DDL Task and SSAS PowerShell.
• Data Analysis Expressions (DAX) enhancements include improved DAX formula editing, support for named variables in expressions, more than 50 new functions to support faster calculations and enhanced visualizations in Power BI, and the ability to save incomplete DAX measures directly in a tabular 1200 model project for resumption later. (Find out about Progent's Power BI business analytics consulting services.)
• Developer enhancements include Analysis Services Management Objects (AMO) updates and a JSON editor for BIM files with Visual Studio 2015 or later.
• DirectQuery is enhanced for tabular 1200 models to generate simpler queries with better performance, provide more control over defining sample datasets for model design and testing, Row Level Security (RLS) and Calculated columns support for tabular 1200 models in DirectQuery mode, and the elimination of redundant join for VertiPaq and DirectQuery to optimize performance. Tabular 110x models in DirectQuery mode support data from include Oracle, Teradata and Microsoft Analytics Platform (previously called Parallel Data Warehouse).

• Manageability improvements include support for Always On in the SSIS Catalog to provide high availability for the SSISDB database and its contents, the ability to deploy packages incrementally rather than the whole project, and support for Always Encrypted in the SSIS Catalog.
• Connectivity improvements include support for the ATOM and JSON data formats for OData V3 protocol, explicit support for Excel 2013 data sources in the SQL Server Import and Export Wizard, support for the Hadoop file system (HDFS), new Connectors for SAP BW, Oracle and Teradata, plus expanded cloud connectivity with the Azure Feature Pack.
• Usability improvements include higher performance for SSIS Catalog views, support for TMSL commands in the Analysis Services Execute DDL Task, automatic downloading of the Balanced Data Distributor transformation and Data Feed Publishing Components, integrated R language support, and detailed XML validation output in the XML Task to help you find and resolve XML errors.

• The Reporting Services web portal included in SSRS 2016 replaces Report Manager and provides a central place for accessing KPIs, mobile and paginated reports, plus Excel and Power BI Desktop files. The portal is built on HTML5, supports CSS3, works with current versions of all major browsers, and replaces the ActiveX print experience on the ReportViewer toolbar with a standard PDF-based print experience. The portal can be custom branded and entire reports can be exported to PowerPoint slides.
• Mobile Reports, introduced by SSRS 2016 for on-premises deployments, allow you to create dynamically formatted reports that adjust content layout automatically to accommodate different screen sizes and aspect ratios and respond to screen rotation on mobile devices such as smartphones and tablets.
• Mobile Report Publisher provides a set of tools that allow businesses to create mobile reports from data in Microsoft Excel or from enterprise and cloud data sources. Mobile Report Publisher can pull from a variety of data sources, integrate with Active Directory for user authentication, deliver real-time updates to mobile devices, and personalize data queries for individual users. You can publish reports to a SQL Server Reporting Services web portal and view them either on a standard browser or in the Power BI iOS mobile app on an Apple iPhone or iPad.
• SharePoint 2016 and SharePoint 2016 integration is supported and requires a report server to be installed in SharePoint mode plus a version of the Reporting Services add-in to be installed on the SharePoint servers.
• Report improvements include the new HTML 5 Rendering Engine; paginated reports that can include charts, gauges, maps and other data visualizations; Tree Map and Sunburst Charts; the ability to embed reports in other web pages and apps; the ability to pin report items to a Power BI Dashboard and specify update scheduling; plus PowerPoint Rendering and Export using the new PowerPoint (PPTX) SSRS 2016 rendering extension from within Report Builder, Report Designer and the SSRS 2016 web portal.
• Subscription improvements include a new user interface that makes it easier for administrators to enable and disable and re-enable subscriptions, change subscription owners, and configure a single file share account used for one-to-many subscriptions.
• Report Builders has a new streamlined user interface that allows you to customize the parameters pane and that supports high-resolution scaling and devices.

• The MDS Add-in for Excel allows you to load filtered lists of data from MDS into Excel, build data management applications directly within Excel, and publish the data back to MDS for centralized storage.
• Performance and scale enhancements include support for larger models, more efficient data loading, the ability to scale entities to more than 100 million members, and support for row-level compression per entity to reduce disk I/O.
• Manageability and monitoring improvements include configurable retention settings for transaction logs, reuse of entities across models, greater control over the names displayed for a given object including code and name attributes, plus easier hierarchy management.
• Security roles in MDS have more granular permissions around read, write, delete, and create. MDS also eliminates the difficulty of designating multiple system admins and allows an explicit model administrator permission property.
• Additional MDS enhancements include scheduled Transaction Log maintenance, enhanced debugging support, a redesigned and expanded Business Rules management experience for validating data, Derived Hierarchy improvements including the display of many-to-many relationships, custom attribute indexes to improve query performance, plus new change management features including revision history and the ability to perform Merge Conflicts.

How Progent Can Help You with SQL Server 2016
Progent's Microsoft-certified consultants offer remote and on-premises integration, development and troubleshooting to help you design, implement and manage your SQL Server 2016 solution. Progent can help you evaluate the business benefits and technical hurdles associated with migrating to SQL Server 2016 and can help you develop a deployment model for a local, cloud-based or hybrid environment. Progent can help you move your databases and apps, set up pilot systems to validate SQL Server 2016's ability to support your critical workloads, automate and distribute management tasks, develop and test an enterprise-wide security strategy that complies with industry standards and government requirements, integrate mobile devices for productive BYOD computing, and create a disaster recovery plan that takes advantage of SQL Server 2016's latest high-availability technology.

Consulting services offered by Progent for SQL Server 2016 include:

• Project Management Support: Progent can provide project management outsourcing or co-sourcing to help you follow best practices in your SQL Server 2016 migration. Project management services include readiness evaluation, needs assessment, system design, deployment planning, testing, configuration, tuning, documentation, plus individual and group training.
• Database and Application Migration: Progent's Database Administration consultants can provide expertise with all versions of SQL Server and also offer Oracle consulting, MySQL development and troubleshooting, and Microsoft Access consulting services. Progent's extensive experience in relational database technology can help your migration to SQL Server 2016 stay on schedule and within budget.
• SQL Server 2016 Integration with Other Microsoft Platforms: SQL Server is the data engine behind Microsoft's core servers and applications and Progent offers expert integration services including Windows Server integration, SharePoint design and development, Microsoft 365 Excel and Power BI support, Exchange Server 2019 migration, Exchange Server 2016 integration, Active Directory configuration expertise, and Microsoft Dynamics 365 ERP and MRP consulting.
• Reporting and Business Analytics: Progent's BI reporting consultants can help you use SQL Server Reporting Services (SSRS) 2016's enhanced features to create, distribute and manage BI reports that are available to authorized users with any device at any location. Progent can help you set up visualizations of PowerPivot models and use SQL Server Analysis Services (SSAS) 2016 for tabular mode databases. Progent can also help you with leading reporting platforms such as Crystal Reports, FRx, and Report Writer.
• Infrastructure Planning and Troubleshooting: Progent's Cisco-certified CCIE networking experts can help you set up and manage a robust network infrastructure to accommodate your SQL Server 2016 environment for any deployment strategy including on-premises, cloud-based, or hybrid models. (Learn about Progent's Microsoft Azure planning and cloud integration consulting services.)
• Security Consulting: Progent's security and compliance consultants have earned top-level security certifications including CISA, CISM, CISSP, ISSAP, CRISC, and GIAC. Progent offers business of all sizes a selection of security assessment and enhancements services to help guard sensitive information and verify compliance with government and industry requirements.
• Virtualization Consulting: SQL Server is probably the world's most virtualized critical application. Progent's certified Windows Hyper-V 2016 hybrid cloud integration experts, Windows Hyper-V consultants, VMware vSphere and vCenter experts, VMware NSX virtual network consultants and Citrix XenServer specialists can help you determine a virtualization strategy for SQL Server 2016 applications that takes into account performance as well as cost-savings, manageability, fault-tolerance, recoverability and security.
• Streamlined Management: Progent's System Center 2016 Operations Manager consultants can help you utilize Microsoft's SCOM Management Pack for SQL Server 2016 to track the health and performance of your SQL Server 2016 deployment from a single portal that monitors both on-premises and cloud resources and warns you of issues that could affect security or productivity.
• Disaster Recovery/Business Continuity Planning: Progent's disaster recovery planning consultants can help you devise, document and test a disaster recovery strategy that incorporates SQL Server 2016's high-availability features to minimize downtime for critical applications in case of a catastrophic event. Progent's business continuity preparedness experts can help you plan recovery procedures that go beyond IT issues such as temporary office space and mandated incident reporting. For large organizations, Progent can provide the services of a VCDX certified VMware Site Recovery Manager (SRM) consultant and VMware NSX network virtualization expert to help you design, deploy and manage a compliant, enterprise-class disaster recovery solution for cloud and hybrid-cloud environments.

If you need immediate online help from a Microsoft-certified consultant, see Progent's Online Network Support.

Find out more about Progent's Consulting Services for Microsoft .NET Server Products.