1 / 37

Jimmy Wong

Explore the significant improvements in SQL Server Integration Services (SSIS) and Master Data Services (MDS) in SQL Server 2016. Learn about performance enhancements, scalability, security, administration, and more. Manage hierarchies, create business rules, and improve modeling and management capabilities.

randalj
Download Presentation

Jimmy Wong

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Please note that the session topic has changed.Please visit My Ignite for current session description.

  2. What´s new in SQL Server 2016 Integration & Master Data Services Jimmy Wong DAT 325

  3. Overview This session covers the major improvements in SQL Server Integration Services (SSIS) & Master Data Services (MDS) in the SQL Server 2016 release. There will be slides, and demos. There will be happy faces (if we did it right).

  4. Focus Areas Manageability Performance Connectivity Customer Feedback

  5. SQL Server 2016Master Data Services (MDS)

  6. Master Data Services in SQL 2016 Security and Administration Performance and Scale Modeling and Management

  7. Do more. Faster. Massive improvements to performance and scalability • Capability • Heavy performance optimizations for the MDS backend system. Scale entities to 100 million members (and beyond). • Benefits • 15x performance increase for Excel • Faster entity based staging • Reduce the overhead by optimizing security check 15x

  8. Do more. Achieve more. Transaction log retention Configurable settings for retaining the MDS transaction history table to enable automatic truncation. Improved hierarchy management Simplified the various hierarchy types, and made it easier to find and manage unused members. Manage hierarchies from Excel. Improved business rule experience Create and manage business rules from Excel, and use custom SQL logic as part of your business rules. Addition of NOT operator, and conditional ELSE logic. Historical subscription views See attribute change history in an easy to consume format. Custom and compound indexes Add a custom index on commonly used attributes to improve overall performance. Also allows combination of multiple attributes to create a compound index. Modeling and Management

  9. Entity Sync Share common entities between models • Capability • Allows entities to be shared across models on the same MDS instance. You can configure one time, on-demand, or continuous sync of members. • Benefits • Reuse of common entities

  10. Many-to-Many Relationships Create M2M relationships in an derived hierarchy • Capability • Goes beyond the One-to-Many relationship mapping normally implied by derived hierarchies, allowing you to model Many-to-Many relationships. • Benefits • Allows modeling of M2M relationships • Replaces Collections

  11. Simple Approval Flows Require approval before changes are made to an entity • Capability • This feature allows the model administrator to specify that changes to a give entity require approval. Changes will be batched (Change Set), and only committed once they are approved. • Benefits • Create (single level) approval flows • Review changes before an entity is modified

  12. Do more. With confidence. Granular security permissions Allows permissions to be set around read, write, create, and delete. Multiple administrator roles Support for Super User and Model Admin roles allows for multiple system administrators, and model level admins. Security and Administration

  13. User Experience Improvements • Capability • Redesign of the MDS web portal. • Benefits • Modern look and feel • Cross Platform • Usability improvement for administration tasks • Hierarchy managements improvements

  14. Updated Excel Add-in • Capability • General usability and performance improvements. More functionality moving from the Web Portal to the Excel add-in. • Benefits • Manage and edit business rules from Excel • Change display names for Domain Based Attributes • Filter attribute values in Domain Based Attributes based on another attribute (using hierarchies)

  15. MDS SQL 2016 at a glance

  16. SQL Server 2016Integration Services (SSIS)

  17. Three Focus Areas Connectivity Manageability Usability & Productivity BIG DATA

  18. Do more. Achieve more. Better deployment Incremental project deployment Always On support for the SSIS Catalog Better debugging Column names included in error outputs Customized logging levels in the SSIS Catalog Better package management Package templates Automatic MaxBufferSize for Data Flow Task Manageability

  19. Always On support for SSISDB High Availability for the SSIS Catalog • Capability • DBA can set up Always On availability groups for the SSIS Catalog • Benefits • Consistent with SQL Server High Availability setup flow • Works for any user database which contains encrypted data based on database master key • Support SSISDB upgrade even when it has been added to the HA group • Easy to configure

  20. Error Column Name Support No need to manually figure out the column name Capability Developer can see the error column name the data viewer Developer can also see the IntToString lineage ID mapping in the log Developer can also programmatically get the column name using the lineage ID • Benefits • No more manual work to figure it out • Speed up and simplify debugging

  21. Custom Logging Level & Log Reader Role Customize your SSIS log with exactly what you need • Capability • Define custom logging levels for package executions within the SSIS Catalog • Additional ssis_logreaderrole to see the SSIS logs (no need to be DBA / Admin) • Benefits • Capture the exact information you need • Flexibility of roles and reporting

  22. Package Template Give you code reusability • Capability • Save part of the package as template and reuse it in the design of other packages • Benefits • Reuse control flow logic across packages • Simplify package maintenance

  23. Automatic MaxBufferSize in Data Flow Task Suitable max buffer size is calculated for you • Capability • Set AutoAdjustBufferSizeto TRUE to automatically calculate the buffer size for your data flow task: • BufferSize = DefaultBufferMaxRows (design time) * RowSize (runtime) • Benefits • Max Buffer Size can go up to 2GB-1 • Easier to size your buffers • Improved performance when working with ColumnStore in SQL Server

  24. Do more. With more sources. Expanded Connectivity More on-premises connectivity HDFS Connectors Hadoop HIVE/PIG Tasks Excel 2013 / 2016 support OData V4 Support Teradata / Oracle updates Execute SQL Task to run analytics with R in SQL Server 2016 More cloud connectivity Azure Storage Connectors HDInsight HIVE/PIG Task BIG DATA

  25. Azure Storage Connectors Enables hybrid ETL • Capability • Move files or read and write data Azure Blob storage • Benefits • Extend existing ETL pipeline with cloud storage, or cloud based SSIS execution through Azure VMs • Data preparation for cloud compute services such as HDInsight or Azure Machine Learning • Data archival to cloud storage • Easy enumeration of files in blob storage

  26. HDFS Connectors Enables data movement between Hadoop BIG DATA • Capability • Move data to and from HDFS as source or destination • Benefits • Extend existing ETL pipeline with on-premises Hadoop and other big data processing • Data preparation for big data compute services such as Hadoop or Spark

  27. HDInsight Tasks Orchestrate and manage HDI clusters • Capability • Trigger HDInsight jobs and manage your HDInsight cluster life cycle directly from SSIS • Benefits • Integrate big data processing into your existing ETL flows • Filter and process raw cloud-born data using HDI before moving it to your data warehouse • Dynamically create your HDI cluster on demand, and remove it once processing is complete • Combine with Azure Storage connectors to extend your ETL environments to the cloud

  28. Hadoop Tasks Orchestrate and manage on-premises Hadoop BIG DATA • Capability • Submit Hive and Pig jobs to your Hadoop cluster directly from SSIS • Benefits • Integrate big data processing into your traditional ETL flows • Combine with HDFS connectors to extend your ETL environments with big data processing power • Leverage the power of Hadoop as part of your Data Warehouse loads

  29. OData V4 Support Connect more with applications out of the box • Capability • Use the same OData source data flow task to connect to OData V4 feed • Benefits • Same OData source UI, no change • Automatic detection of OData version

  30. Do more. Faster. Faster Design Experience Multi-version support in SSDT Many designer improvements Faster Install Experience IS designer is now part of SSDT install Faster Management Studio Experience Performance improvement on SSIS Catalog views Usability & Productivity

  31. Multi-Version Support in SSDT Handle different SSIS package versions with a single designer • Capability • Author, execute, deploy and debug multiple versions of SSIS package from a single version of SSDT Designer. • Benefits • No need to maintain multiple version of Visual Studio, SSDT designer to manage different versions of SSIS packages • Enjoy the benefits from the latest visual studio shell and SSDT designer even when they are working on older version of SSIS package • Support SSIS 2012, SSIS 2014 and SSIS 2016

  32. SSIS SQL 2016 at a glance

  33. Demo - SSIS 2016

  34. Complete your session evaluation on My Ignite for your chance to win one of many daily prizes.

  35. Continue your Ignite learning path Visit Microsoft Virtual Academy for free online training visit https://www.microsoftvirtualacademy.com Visit Channel 9 to access a wide range of Microsoft training and event recordings https://channel9.msdn.com/ Head to the TechNet Eval Centre to download trials of the latest Microsoft products http://Microsoft.com/en-us/evalcenter/

More Related