DQS – Using the Changes History Panel

The DQS client will automatically sort the list of Domain Values, which can make adding new values to a big list tricky. Once you enter the new value at the bottom of the list, it gets automatically sorted, and you need to scroll up the list to find it again. However, there is a better way!

At the far end of the toolbar, there is a drop down button which exposes the lesser used commands. One of these is the ShowHide domain values changes history panel button, which turns out to be incredible useful.

Domain Values Changes History panel button

When the panel is enabled, it is displayed at the bottom of the Domain Values page. When a value is added or modified, the change will be displayed in the log window, with a hyperlink to the domain value. Clicking on this link will automatically scroll/focus on the list to the target value.


Microsoft SQL Server 2012 Integration Services

I’m happy to see that my second SSIS book for the SQL Server 2012 release has been announced! SQL Server 2012 Integration Services  is published by Microsoft Press (and O’Reilly), and gives you a real insiders view of the product. My co-authors are all members of the SSIS product team, and we’ve really dug deep into the product for some of the chapters you’ll see in this book. It is scheduled to come out this summer (July 2012). I think this book is a good compliment to my other book – SSIS Design Patterns – which takes a more hands-on, solution based approach.

From the O’Reilly site:

Conquer Microsoft® SQL Server® 2012 Integration Services—from the inside out!

You’re beyond the basics, so dive right in and really put SQL Server Integration Services to work! This supremely organized reference packs hundreds of timesaving solutions, troubleshooting tips, and workarounds. It’s all muscle and no fluff. Discover how the experts perform data integration tasks—and challenge yourself to new levels of mastery.

  • Gain in-depth understanding of Integration Service capabilities introduced in SQL Server 2012
  • Implement Integration Services best practices and design patterns
  • Master the ETL tool for data extraction, transformation, and loading
  • Manage performance issues using tuning principles and techniques
  • Diagnose problems and apply advanced troubleshooting features

Training Scholarships for SQL Saturday #111 in Atlanta

Julie Smith announced that Idera is offering full training scholarships for each of the three Pre-Con training events at SQL Saturday #111 (Atlanta). One of those sessions – A Day of SSIS – is one I’ll be doing along with Andy Leonard. This is a great opportunity for someone who might not have gotten to go otherwise. Check out Julie’s post for details on how you can be selected. 


If you can’t make it to Atlanta, you can also see me for SSIS Performance Design Pattern training at SQL Saturday #105 in Dublin, and SQL Bits X in London.

Getting Started with DQS and MDS

If you’re looking to get started with Data Quality Services (DQS) and Master Data Services (MDS), there are some fantastic resources available on Technet. The site includes videos and slides for full day training sessions on both products.

Data Quality Services for SQL Server 2012

  • Data Quality Basics and Introducing DQS: Video | Slides
  • Knowledge Management and Data Cleansing in DQS: Video | Slides
  • Data Matching in DQS: Video | Slides
  • DQS Integration with SSIS: Data Cleansing using SSIS: Video | Slides
  • DQS Integration with MDS: Data Matching using MDS: Video | Slides

Master Data Services for SQL Server 2012

  • Master Data Services Overview: Video | Slides
  • Managing Data Warehousing Dimensions with MDS, Part 1: Video | Slides
  • Managing Data Warehousing Dimensions with MDS, Part 2: Video
  • Data Loading via Entity Based Staging (EBS): Video | Slides
  • MDS Hierarchies and Collections: Video | Slides
  • Business Rules and Workflow in MDS: Video | Slides
  • MDS Model Migration and Upgrade: Video | Slides
  • Security Features and Guidelines in MDS: Video | Slides
  • Eliminate Duplicate Data with the MDS Add-In for Excel: Video | Slides

AlwaysOn Multi-Subnet Failover and SSIS

Updated 2012-05-11: Updated the .NET Framework update link to point to 4.0.3, as it has some additional updates for AlwaysOn.

If you plan on using Multi-Subnet Failover Clustering, note that the SQL Native Client 11.0 OLEDB provider does not support the new connection string key words (MultiSubnetFailover=True). Instead you should use the ADO.NET or ODBC Source and Destination components.

Details can be found in the SQL Server 2012 Release Notes.

MultiSubnetFailover=True Not Supported by NET Framework 3.5 or OLEDB

If your Availability Group or Failover Cluster Instance has a listener name (known as the network name or Client Access Point in the WSFC Cluster Manager) depending on multiple IP addresses from different subnets, and you are using either ADO.NET with .NET Framework 3.5SP1 or SQL Native Client 11.0 OLEDB, potentially, 50% of your client-connection requests to the availability group listener will hit a connection timeout.

Workarounds: We recommend that you do one of the following tasks.

  • If do not have the permission to manipulate cluster resources, change your connection timeout to 30 seconds (this value results in a 20-second TCP timeout period plus a 10-second buffer).
  • Pros: If a cross-subnet failover occurs, client recovery time is short.
  • Cons: Half of the client connections will take more than 20 seconds.
  • If you have the permission to manipulate cluster resources, the more recommended approach is to set the network name of your availability group listener to RegisterAllProvidersIP=0. For more information, see “Sample PowerShell Script to Disable RegisterAllProvidersIP and Reduce TTL”, later in this section.
  • Pros: You do not need to increase your client-connection timeout value.
  • Cons: If a cross-subnet failover occurs, the client recovery time could be 15 minutes or longer, depending on your HostRecordTTL setting and the setting of your cross-site DNS/AD replication schedule.

Driver support summary for AlwaysOn:


Driver Multi-Subnet Failover ApplicationIntent Read-Only Routing Multi-Subnet Failover: Faster Single Subnet Endpoint Failover Multi-Subnet Failover: Named Instance Resolution For SQL Clustered Instances
SQL Native Client 11.0 ODBC Yes Yes Yes Yes Yes
SQL Native Client 11.0 OLEDB No Yes Yes No No
ADO.NET with .NET Framework 4.0 Update 4.0.3 Yes Yes Yes Yes Yes
ADO.NET with .NET Framework 3.5 No Yes Yes Yes Yes
Microsoft JDBC driver 4.0 for SQL Server Yes Yes Yes Yes Yes


As mentioned on the SSIS Team Blog, the SQL Native Client OLEDB provider is being deprecated, but still provides the best interoperability with SSIS components in the SQL Server 2012 release.

Data Quality Services Performance Best Practices Guide – now available!

The Data Quality Services (DQS) Performance Best Practices Guide (or DQSPDPG for short) is now available on the Microsoft Download Center. It covers hardware and setup considerations, how matching policies will impact your performance, and some best practices when using the DQS Cleansing transform in SSIS. I was also happy to see a section in there about the impact of using advanced DQS functionality, such as Composite Domains, Term-Based Relations, and Reference Data Services. A must read for all DQS users…