Real World Migration Story from BizTalk 2006/2006R2 to BizTalk 2010

Writer:

Srikiran Tadikonda, Microsoft

Banupriya Thirumaran, Microsoft

Reviewed By:

Puneet Gupta, Microsoft

Shridhar Nadgaundi, Microsoft

Scott Zimmerman, Microsoft

Mandi Ohlinger, Microsoft

Contributors:

Gopinath P, Microsoft

Prethish Kumar Puthenparampil, Microsoft

Rachna Srivastava, Microsoft

Rajesh Ramamirtham, Microsoft

Sumant Kumar Pandey, Microsoft

Sneha Aswani, Microsoft

Published: January 2013

Applies to: BizTalk Server

Summary: The BizTalk Server infrastructure used by Microsoft IT (MSIT) included BizTalk Server 2006 R2 and BizTalk Server 2006 environments. Until recently, the platforms that hosted these applications were not Disaster Recovery enabled. Since BizTalk Server 2006 doesn’t support the Applicability Statement 2 (AS2) protocol, AS2 transactions are sent to BizTalk Server 2006 R2. Then, the transactions are routed back to BizTalk Server 2006 for further processing. As a result, there are many cross-server dependencies. With the transactions passed through multiple BizTalk servers, the architecture of these applications was complex. The integration team wanted to remove these cross-server dependencies and simplify the application architecture.

To simplify the architecture, the BizTalk Server 2006 R2/2006 environments are migrated to BizTalk Server 2010. The objectives of the migration are:

• Enable Disaster Recovery for business critical applications.
• Reduce the number of servers required to deploy the applications and reduce the support costs for the servers.
• Minimize the number of pipelines deployed by leveraging the Pipeline Consolidation Framework of BizTalk Server 2010.
• Enable MSIT to support a broader range of internal applications.
• Stay current with the technologies: Windows Server 2008 R2, SQL Server 2008 R2, Visual Studio 2010, and the .NET Framework 4.

The migration to BizTalk Server 2010 is a great success, including the following results:

• Reduced the physical servers for 32 applications from 51 servers to 22 servers
• Uptime is greater than 99.9% and about 400,000 messages are processed per week
• Utilize the benefits of Hyper-V
• Zero major issues post-production

This white paper discusses the steps taken to migrate to BizTalk Server 2010.

Copyright

This document is provided “as-is”. Information and views expressed in this document, including URL and other Internet Web site references, may change without notice. You bear the risk of using it.

Some examples depicted herein are provided for illustration only and are fictitious.  No real association or connection is intended or should be inferred.

This document does not provide you with any legal rights to any intellectual property in any Microsoft product. You may copy and use this document for your internal, reference purposes.

© 2013 Microsoft. All rights reserved.

Contents

Introduction    5

Introduction to BizTalk Server 2010    5

New Features of BizTalk Server 2010    5

Easier Integration of Enterprise Applications    5

Platform Support for Latest Microsoft Technologies    6

Simplify Solution Manageability for IT Pros    6

Enhanced Enterprise Interoperability    7

Deprecated Features of BizTalk Server 2010    7

Common Reasons for Migrating to BizTalk Server 2010    7

Microsoft IT (MSIT) BizTalk 2010 Migration Story    8

Problem Statement    8

Objectives    8

Migration Process    8

Development Strategy    8

Architecture – Before and After Migration    9

New Features used in Migration    10

Testing Strategy    11

Benefits of Migration    13

Challenges faced in Migration    14

Connect Bug: AS2 encryption and signing issue details    14

Host instance and hosts going down intermittently    14

BizTalk 2010 is stopping when input file is more than 5 MB    15

Best Practices    15

Appendix    16

Volume/ Throughput after migration    16

Weekly Volumes on BizTalk Servers    16

Patches applied to fix issues in BizTalk 2010    17

Conclusion    18

Acknowledgements    18

Introduction

This white paper describes the following:

• Motivations for moving to BizTalk Server 2010
• The process that Microsoft IT went through to migrate 32 mission-critical applications from BizTalk 2006/2006 R2 to BizTalk 2010
• Suggestions for smooth administration of BizTalk Server 2010.

The new BizTalk installation went into production in phases from February 2011 to November 2011. Since then, uptime is greater than 99.9% while processing about 400,000 messages per week. This migration project allowed Microsoft IT to reduce the physical servers for the 32 applications from 51 to 22 servers, which includes eight 4-proc servers running Hyper-V to host BizTalk Server 2010. There have been zero major issues during post-production support. In many respects, the project has been a great success.

Introduction to BizTalk Server 2010

In most organizations today, there is a backlog of requirements to integrate different IT systems. Yet connecting software means more than just exchanging bytes. As organizations continue to move toward a service-oriented world, the real goal—creating effective business processes that unite disparate systems into a coherent whole—is now within reach. Microsoft BizTalk Server 2010 supports this goal.

Microsoft BizTalk Server is an integration server and connectivity solution. It helps organizations meet the challenge of integrating diverse systems. Including over 25 multiplatform adapters and a robust messaging infrastructure, BizTalk Server provides connectivity between core systems inside and outside of your organization. In addition to integration functionality, BizTalk also provides strong durable messaging, a rules engine, EDI connectivity, Enterprise Service Bus, Business Activity Monitoring (BAM), RFID capabilities, industry-standard SWIFT and HL7 support, and IBM Host/Mainframe connectivity.

New Features of BizTalk Server 2010

BizTalk Server 2010 introduces enhancements and new features in four main areas.

Easier Integration of Enterprise Applications

BizTalk 2010 accelerates time to solution with better and more accessible tools:

• BizTalk Server 2010 includes easy to use tools that integrate Line-of-Business applications with Windows Server AppFabric Workflows. This includes Workflow activities that provide access to the BizTalk Transformation Engine and Mapper, and the BizTalk Line-of-Business adapters.
• The BizTalk 2010 Mapper has been updated with a new user interface that improves development of large transformations and provides new search and predictive matching functionality. The new mapper also improves productivity by adding cut, copy, paste, move, and undo functions and stronger support for documenting maps and readability.
• BizTalk Services are now accessible via SharePoint 2010 Business Connectivity Services.
• BizTalk RFID includes new event handlers and event delivery capabilities. The new event handlers enable removal of duplicate events and filters based on Electronic Product Codes (EPC) and visibility requirements. The new delivery capabilities send events directly to .NET applications, BizTalk Server, and EPC Information Services (EPCIS).
• Easier access to IBM applications and data sources. BizTalk 2010 Adapter for Host Systems offers efficient development tools and native runtime protocol engines to connect BizTalk 2010 to existing line-of-business programs and data on IBM mainframe systems.
• Excellent support for industry-standard web services. BizTalk 2010 SOAP adapter enables sending and receiving messages by using SOAP over HTTP, thus enabling it to interact with the universe of web services. BizTalk 2010 also includes seven adapters and easy-to-use “wizards” that enable communicate to and from BizTalk Server and Web services-based applications via the Windows Communication Foundation (WCF). 

for Latest Microsoft Technologies

BizTalk Server 2010 supports the Microsoft platform technologies, including Windows Server 2008 R2, SQL Server 2008 R2, Visual Studio 2010, and the .NET Framework 4.

• BizTalk Server 2010 support for Windows Server 2008 R2 offers modular, minimal installation, improved network performance and control. It also includes improved high availability features, enhanced administration, and management with Server Manager, Windows PowerShell command-line, and enhanced virtualization with Hyper-V. By utilizing Windows Server 2008 R2 clustering, BizTalk Server can be deployed in multisite cluster scenarios, where cluster nodes can reside on separate IP subnets and avoid complicated VLANs.
• BizTalk Server 2010 takes advantage of the virtualization improvements included as part of Windows Server 2008 R2 Hyper-V. These improvements lead to reduced costs through lower hardware, energy, and management overhead; plus the creation of a more dynamic IT infrastructure.
• BizTalk Server 2010 support for SQL Server 2008 and SQL Server 2008 R2 offers better manageability and scalability, an optimized virtual SQL Server implementation and improved performance, especially in a 64-bit.
• Support for Visual Studio 2010 and NET Framework (v4) introduces a number of improvements to the underlying Visual Studio-based BizTalk project system. Improvements include debugging support for artifacts and BizTalk maps (XSLT), enhanced BizTalk artifact property pages integrated into Project Designer tabs, the Visual Studio Project Update Wizard, and support for release and debug builds.

Simplify Solution Manageability for IT Pros

With BizTalk Server 2010 it’s now much easier to get things done faster.

• A new settings dashboard consolidates all settings into a single place, providing granular settings at the group, host, or host instance level. The Settings Dashboard supports automated settings deployment with scriptable export/import operations.
  
• The new System Center Operation Manager Pack includes a new model with separate application and deployment views, new alerts and diagnostics, and improved multimachine monitoring and cluster awareness.
• Enhancements to the setup and upgrade include a new Smart Setup which scans and installs BizTalk upgrades automatically, upgrading from BizTalk Server 2006 R2 and BizTalk Server 2009.

Enhanced Enterprise Interoperability

BizTalk Server 2010 has better options for connecting with business partners and applications.

• Enhanced Trading Partner Management provides easy on-boarding and lifecycle management of trading partners. A new partner management model reflects typical B2B relationships and is easier to manage large-scale TPM deployments. An improved user interface includes agreement templates and business profiles for rapid configuration and a new TPM operator role which provides access to users other than administrators.
• New and enhanced adapters provide higher levels of security, better performance, and support for the latest releases of line-of-business applications.

Deprecated Features of BizTalk Server 2010

The following features and tools are available in previous BizTalk versions and are replaced with new features in BizTalk Server 2010.

• BizTalk Web Services Publishing Wizard: This wizard is replaced with the new BizTalk WCF Publishing Wizard, which provides broader and more flexible support to publish web services from BizTalk.
• BizTalk Adapter Pack 2010 version (BAP 2010): The SAP adapter, Oracle adapter, SQL Server adapter and Siebel adapter in BAP 2010, which comes with BizTalk 2010 replaces the corresponding adapters in BAP 1.0, which comes with BizTalk 2006/2006 R2.
• Add Web Reference: The Consume WCF Service wizard is used to consume web services from BizTalk.
• EDI Pipeline and Context Properties: The new Trading Partner Management feature in BizTalk Server 2010 provides the same capabilities as the EDI Pipeline and Context Properties and replaces these features of older BizTalk Server Versions.
• Web Services Enhancements Adapter: This adapter is replaced with the WCF Adapter using a custom transport.

Common Reasons for Migrating to BizTalk Server 2010

• Stay Current: Being able to use newer communication protocols makes it easy to stay current and take advantage of new software features and technologies, such as improved encryption.
• Support: BizTalk Server 2006/R2, Visual Studio 2005, and SQL Server 2005 are no longer covered by Microsoft’s Mainstream Support Policy. To keep active with support for mission-critical IT assets, it is essential to upgrade to the latest versions.
• Virtualization: BizTalk 2010 takes advantage of the latest virtualization improvements of Windows Server 2008 R2 Hyper-V. The improvements can lead to reduced costs through lower hardware, energy, and management overhead and in the creation of a more dynamic IT infrastructure.
• Clustering: By using Windows Server 2008 R2 clustering, BizTalk Server can now be deployed in multisite cluster scenarios, where cluster nodes can reside on separate IP subnets and avoid complicated VLANs.

Microsoft IT (MSIT) BizTalk 2010 Migration Story

Problem Statement

The Microsoft Board of Directors mandates that Microsoft’s internal business critical applications, like Volume licensing and Order Processing, must have Business Continuity/Disaster Recovery (BC/DR). Until recently, the platforms hosting these applications (that is BizTalk 2006/2006 R2) were not DR enabled. Since BizTalk Server 2006 doesn’t support the AS2 (Applicability Statement 2) protocol, AS2 transactions went to BizTalk Server 2006 R2. Then, the transactions were routed back to 2006 for further processing. This led to many cross-server dependencies. With the transactions being passed through multiple BizTalk servers, the architecture of these applications was complex. The integration team wanted to remove these cross-server dependencies and simplify the application architecture.

Objectives

The objectives of this migration were:

• To enable Disaster Recovery for business critical applications.
• To reduce the number of servers required to deploy the applications and to reduce the support costs for them.
• To minimize the number of pipelines deployed (by leveraging the Pipeline Consolidation Framework of BizTalk 2010).
• To enable MSIT to support a broader range of internal applications.
• To stay current with the technologies: Windows Server 2008 R2, SQL Server 2008 R2, Visual Studio 2010, and the .NET Framework 4.

Migration Process

Development Strategy

The migration process started with estimating the number of maps for the applications, frequency of transactions per LOB (Line of Business), size of the messages and so on; and then setting these values as the production benchmark. Based on the estimation, and the criticality of the applications and the business needs, the integration team divided the entire migration process into four phases.

In each phase, a set of applications were migrated from BizTalk 2006/2006R2 to BizTalk 2010. Once the applications were determined for each phase, the old BizTalk solutions were rebuilt using Visual Studio 2010. BizTalk solutions that contained legacy adapters like SAP and SQL are replaced with WCF-Custom adapters in BizTalk 2010. Due to the growing number of pipeline and pipeline components, an internal framework called Pipeline Consolidation Framework (PCF) was developed. The framework offers flexibility for pipeline configuration, minimizes the number of pipelines and pipeline components, centralizes the storage in the .NET global assembly cache (GAC) and makes the pipelines reusable with more shared functionality. Refer to this (http://blogs.msdn.com/b/srikiran/archive/2012/12/22/pipeline-consolidation-framework-pcf-biztalk-2010.aspx) blog for more information on PCF. Changes to the existing solutions are done in configuring adapters, pipelines, and party settings. In other artifacts like maps, schemas and orchestrations no changes were required.

The final solutions were built and deployed in System Integration Test (SIT), User Acceptance Test (UAT), and Production environments. This entire migration process took about 15 months for 32 applications. This migration process started with less critical applications like HR and Supply Chain applications, then moved to highly critical applications like Finance, and Order to Cash in later phases.

Architecture – Before and After Migration

Previously, there were two versions of BizTalk (BizTalk 2006, 2006R2) in which all applications were deployed (see the following diagram). This architecture was previously supported by 22 Production servers. This architecture led to many problems. See Figure1. For example, when one application was updated, it used to affect the other applications on the same server. Any patches applied to BizTalk 2006/2006R2 affected all the applications. To overcome these major problems, the integration team came up with the new, appropriate architecture. In the new architecture, three BizTalk 2010 LOB Servers are maintained. The applications which are high-critical are deployed on first server, medium-critical on second server and low-critical on third server. When changes are made to low-critical applications, it does not impact the high-, or medium-critical applications on the other BizTalk servers and vice versa. The maintenance of the applications is greatly simplified. The alerts on the host instances are set according to the criticality of the applications, which helps the business owners handle any issues effectively. See Figure2 for each LOB server configuration.

            Figure 1. Old and New Architecture

Figure 2. BizTalk 2010 LOB Server Configuration

New Features used in Migration

Several BizTalk 2010 new features are used to help development productivity and solve some of the challenges faced with BizTalk 2006 and BizTalk 2006 R2:

• Using the BizTalk 2006/2006R2 Trading Partner Management (TPM), the application could not use multiple AS2 transaction setups with AS2 (different AS2 Id with single MDN receive endpoint) for the same business partner. In BizTalk 2010, transaction setup is not only at the Party level but also at the Agreement level. So now, there is fine-grained control over Transaction Setup within the Agreement Level.
• The BizTalk Mapper improvements reduced the time from 16 hours to develop complex maps to 4 hours. The key mapper improvements were:
  
  • Organizing maps in multiple pages
    
  • Ability to search in Schemas
    
  • Predictive Matching
    
  • Cut/Copy/Paste       
    
• BAM User Experience Improvements – There were no indexed properties in earlier versions of BAM tracking. BTS2010 has indexed based sorting. The indexed-based sorting resolves a performance issue when returning the most recent tracking records.
  
• SQL and SAP Adapters are migrated to WCF adapters to give a uniform experience when configuring the settings, and more importantly, improved performance.

   

Testing Strategy

Three months of test data was captured from the archives from the production systems. This data was later cleaned up and categorized for testing. The business partners for testing were selected based on criteria, including production volume, encoding, location, and so on. The testing scenarios cover new changes, regression of the as-is functionality, data comparison, and negative testing. 

Test is organized into four main phases.

Isolated Environment Testing:

In isolated environment testing, the BizTalk 2010 solution is tested with five or six partners per transaction. Individual BizTalk artifacts were tested including adapters, maps, schemas, receive locations and send ports. And the output for each transaction in BizTalk 2010 is compared with the old environment.

Volume testing was also done and ensured that the outputs between old and new translators were same.  

System Integration Testing:

For system integration testing, the BizTalk 2010 system is connected with internal tools and downstream systems. Partner testing is simulated here. For inbound transaction, HTTP posts to the gateway system are simulated. For outbound transactions, transactions are sent to local AS2 sites to verify the correctness of the output. 

User Acceptance and Rollback Testing:

For user acceptance and roll-back testing, there is collaboration with the partners at the start of the release so their environments are available during UAT testing.

Test certificates are used and shared with partners during this testing. The UAT test scenarios include positive and negative scenarios agreed with the partners. Scenarios include missing mandatory fields in purchase orders sent by partners, or incorrect EDI qualifiers or delimiters in TPM Agreement settings. Another criteria for successful UAT testing and signoff is that there is a minimum of five files tested with each partner.

Rollback testing happened in UAT. Scenarios were created depending on where the rollback happens. For example, different rollback procedures in gateway applications compared to BizTalk application-level procedures are used. The system was rolled back to the old environment and tested with one file for each transaction to verify that files were flowing through the old environment as expected.

Once the rollback tests were successful, again the automated deployment to switch to the new environment was initiated. This is followed by smoke testing the new applications in BizTalk 2010. This level of rollback testing obviously takes time, but it’s the best way to ensure that the project team is ready to make the switch, without any disruption to the business.

Performance Testing:

For Performance Testing, benchmark statistics are collected from BizTalk 2006 and BizTalk 2006 R2 production systems, covering a period of more than six months. The metrics included number of messages per transaction, size of the messages, peak volume, and frequency of the messages.

Using LoadGen (a simulation tool to measure the impact of clients on server), configuration sets are created for different adapters such as File, HTTP, WCF, and so on. The appropriate performance counters for a BizTalk clustered environment and SQL were selected and started before running LoadGen scenarios. For this project, the PAL (Performance Analysis of Logs) tool is used to analyze the performance counter logs and thresholds. It also documents the system, server, and network issues and presents it in HTML format.

The extreme scalability tests include increasing the message count and the size of the messages to manifold. In some cases, an increase up to 60 times more than the actual volume and 40 times more than the actual size of messages in production are used. See the following Figure 3; which shows the different scenarios. The team was satisfied that the measured CPU rates demonstrated the ability to handle peak load far beyond the requirements. A breakpoint scenario is also tested to ensure the stability and availability of the server even under high CPU utilization. It was seen that the files continued to be processed, even in the breakpoint scenario where there was more than 95% CPU utilization across the servers.

Figure 3. The CPU Utilization of BizTalk 2010 LOB1 during extreme load testing.

Preproduction Validation Testing:

The production deployment scripts, assemblies, and artifacts were validated and smoke tested before the Go-Live event. As the environments were new, there were network and firewall issues when connecting to external customer URLs and internal business systems such as SAP. So, the transactions are smoke tested with local end points and customer UAT URLs to ensure that the firewall issues or connectivity issues were overcome. After installing the BizTalk solutions and before the new receive ports and orchestrations were enabled, Build Validation Tests (BVT) are performed in the production environment. The BVTs verify that all the necessary artifacts are present.

Post Production Support:

Using System Center Operations Manager (SCOM) 2010, we monitored the following events:

• Receive Location and Send port availability
• BizTalk to BizTalk server services
• Suspending Q monitoring
• DB availability
• Any disconnection between Database and processing nodes

Monitoring was automated. Alerts are sent for failed messages and SLAs were created based on the criticality of the applications. The 24/7 support team from Operations and Engineering ensures that any critical issue is resolved based on the SLA.

The post production support for applications is extended for a month from the date of Go-Live. Any suspended messages in BizTalk or any issues raised by customers are attended immediately with the 24/7 support model. There have been no major issues during the post-production support period.

Benefits of Migration

The migration of several applications from BizTalk 2006/2006R2 to BizTalk 2010 achieved many significant benefits for Microsoft.

• The Integration Team successfully deployed 32 unique applications that have 150+ project artifacts (maps) and 300+ partners to production with zero severity-1 bugs or issues.
• It resulted in the retirement of 51 servers, 70% of them being physical servers. This is a large cost savings.
• There was no adverse impact on any business processes, and customers are using new applications without any issues.
• It resulted in a reduction of the pipelines from 108 to 18, which significantly increased the performance of the applications.
• After migration, the applications are able to take advantage of state-of-the-art virtualization technologies with Hyper-V, which leads to reduced costs through lower hardware, less management overhead and more flexible administration.
• The migration has the applications on a newer platform, including Windows Server 2008 R2, SQL Server 2008 R2, and .NET Framework 4.0 which helped to leverage the dynamic features of all these new technologies.
• BizTalk 2010 takes advantage of N-node Active-Active clustering of Windows Server 2008 which improves the availability and utilization of the resources for the applications. BizTalk 2006 R2 uses 2-Node Active-Passive clustering in Windows Server 2003.

Challenges faced in Migration

There were some challenges faced during the migration of applications to BizTalk 2010. The most important of them are listed here.

Connect Bug: AS2 encryption and signing issue details

Issue:

A signed and encrypted message from partner fails to be processed in BizTalk 2010 Beta, and was getting processed in BizTalk 2006 R2. A partner is able to send signed and encrypted message but the AS2 Decoder fails to decrypt the file and returns the following error

:“An error occurred when decrypting an AS2 message.
The sequence number of the suspended message is 1. “
Additional error generated in the event log whenever this error occurs is:
“The AS2 Decoder encountered an exception during processing. Details of the
message and exception are as follows: AS2-From :”<<Some Value>>” AS2-To:”<<
Some Value>>” MessageID:”<< Some Value>>” Message Type: “unknown”
Exception: “An error occurred when decrypting an AS2 message””

    The more detailed exception is available here (http://connect.microsoft.com/BizTalk/feedback/details/564504/error-while-decrypting-as2-files-as2-over-file-scenario)

Fix:

This issue was caused by code issue in BizTalk 2010 beta version and fixed in the BizTalk 2010 RTM version.

Host instance and hosts going down intermittently

Issue:

When performing transactions on BizTalk 2010, some of the following errors occurred:

• Error code: 0xC0002A21, an error occurred while attempting to access the SSO database.
• A transport-level error has occurred when receiving results from the server. (Provider: TCP Provider, error: 0 – The semaphore timeout period has expired.) SQL Error code: 0x00000079

Fix:

To fix this problem, follow these steps:

• On the BizTalk servers, update MaxUserPort and TCPTimeWaitDelay registry keys per KB article 319502 (http://support.microsoft.com/?id=319502) and then reboot the servers.
• On the SQL backend servers of BizTalk, set the SynAttackProtect registry key to 0 per KB article 899599 (http://support.microsoft.com/kb/899599), Winsocklistenbacklog to 190 as per KB article 328476 (http://support.microsoft.com/?id=328476) , and reboot the servers.

BizTalk 2010 is stopping when input file is more than 5 MB

Issue:

When you install BizTalk 2010 in Windows 7, Windows 7 Service Pack 1 (SP1), Windows Server 2008 R2 or Windows Server 2008 R2 Service Pack 1 (SP1) and use AS2 decoder to decrypt an encrypted message larger than 5 MB, it fails with an “out of memory error while decrypting files larger than 5MB” exception.

Fix:

To resolve this issue, apply the hotfix described in Microsoft Knowledge Base (KB) article 2480994 (http://support.microsoft.com/kb/2480994)

Best Practices

The following are some of the best practices used to help the migration process go smoothly:

• Ensure consistent monitoring and tracking of messages is present across all applications.
• Avoid overloading individual hosts. Instead, use the appropriate number of BizTalk 2010 Servers and decide how many applications to deploy on each of them in the design phase.
• Validate your message volume per host and modify default settings as appropriate.
• Ensure that the BizTalk SQL Agents Jobs run successfully in SQL Server to release all the resources.
• Ensure that you have a backup and replication strategy in place.
• When possible, avoid large schemas with mostly optional fields.
• Ensure that appropriate permissions exist for SSO, SQL, and BizTalk.
• Modify default retries and timeouts as appropriate.

The optimizations performed by the Integration team to improve the performance of applications in the migration process include:

• Utilized the SQL Server Backup Compression feature in BizTalk Server which eventually reduced the size of BizTalk backups and the time consumed for backing up the Database.
  
• In BizTalk Server 2010 Management Pack for System Center 2010, set the discovery interval to lower levels which fixed the performance issues related to resource utilization in previous BizTalk versions.
  
• To achieve higher performance, the Max receive interval is tuned at the host Level in BizTalk 2010. Performance was further improved by tuning the Max polling interval and Orchestration polling interval. Refer to this (http://blogs.msdn.com/b/prethishkumar/archive/2012/02/04/high-performing-biztalk-server-2009-biztalk-server-2010-server-setup.aspx) blog for more information on improving performance.
  
• Upgraded the hardware (for example, increasing the RAM and disk space) which reduced disk I/O Latency.
  

Appendix

Volume/ Throughput after migration

The following table and graph show the typical weekly number of messages that flow through the new BizTalk Servers.

Weekly Volumes on BizTalk Servers

Patches applied to fix issues in BizTalk 2010

Certain patches that are applied to BizTalk 2010 to fix issues based on customer feedback received by the Microsoft BizTalk team. In the following table, some of the patches that helped the integration team in migrating the applications from BizTalk 2006/ 2006R2 to BizTalk 2010 are listed.

Conclusion

BizTalk 2010 is implemented by many organizations worldwide today for their mission-critical integration. BizTalk 2010 is for high volume, high availability, and high scalability which are required for business process solutions. Migrating to BizTalk 2010 from BizTalk 2006, BizTalk 2006 R2 gives many benefits to the organization and to the customers.

Acknowledgements

Many thanks for the technical information and input provided by Gopinath P, Prethish Kumar Puthenparampil, Rachna Srivastava, Rajesh Ramamirtham, Sumant Kumar Pandey and Sneha Aswani. We would like to acknowledge the leadership and support provided by Puneet Gupta and Shridhar Nadgaundi. A special thanks to Scott Zimmerman and Mandi Ohlinger for the technical review and valuable insights on this white paper.

For more information:

http://www.microsoft.com/sqlserver/: SQL Server Web site

http://technet.microsoft.com/en-us/sqlserver/: SQL Server TechCenter

http://msdn.microsoft.com/en-us/sqlserver/: SQL Server DevCenter

Did this paper help you? Please give us your feedback. Tell us on a scale of 1 (poor) to 5 (excellent), how would you rate this paper and why have you given it this rating? For example:

• Are you rating it high due to having good examples, excellent screen shots, clear writing, or another reason?
  
• Are you rating it low due to poor examples, fuzzy screen shots, or unclear writing?
  

This feedback will help us improve the quality of white papers we release.

Send feedback.

Active Directory LDAP Compliance

Microsoft Corporation

Published: October 2003

Abstract

Directories are public or private stores containing essential identifying information typically used in daily enterprise activities. Many application providers capitalize on directories offering integration into existing directories to extend their application’s functionality. Network operating systems also house vital network information, such as users and computers, within directories.

Lightweight Directory Access Protocol (LDAP) is a directory standard founded on the legacy X.500 directory. LDAP’s initial implementations provided gateway services between X.500 directory servers and clients. While LDAP was initially created to meet this requirement, it became clear that a parting from the cumbersome X.500 directory standard was needed to simplify deployments. In 1994, LDAP was transformed into a directory specification with its own database and structuring conventions.

This paper discusses the origins of LDAP within Microsoft products and, specifically, the implementation of, and conformance to, the LDAPv3 Proposed Standard within Microsoft Windows 2000 Server and Microsoft Windows Server 2003. Included for reference are matrixes detailing supported RFCs.

The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.

This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, AS TO THE INFORMATION IN THIS DOCUMENT.

Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.

© 2003 Microsoft Corporation. All rights reserved.

Microsoft, Active Directory, Visual Basic, Windows, and Windows Server are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.

The names of actual companies and products mentioned herein may be the trademarks of their respective owners.

Contents

Introduction    2

Directory Foundation: X.500    2

X.500: The Need for a Lightweight Alternative    2

What Is LDAP?    3

LDAP: First Generation    3

Enhancements with Version 2    3

The Current State of LDAP    3

What Does It Mean to Be LDAP Compliant?    5

Achieving Compliance: IETF Applicability Statement    5

Achieving Compliance: Third-Party Test Suites    5

The Open Group LDAP Certifications    5

Setting an LDAP Compliance Baseline    6

Active Directory’s LDAP Compliance    8

Windows 2000 Server    8

Windows Server 2003    8

Compliance Misconceptions    10

inetOrgPerson    10

Native LDAP Calls    10

Directory Interoperability    11

LDAP API    11

Active Directory Services Interface    11

Development Environments    12

Active Directory Application Mode    12

Directory Services Markup Language    12

Microsoft Identity Integration Server 2003, Enterprise Edition    12

Additional Resources    14

Lightweight Directory Access Protocol Version 3    14

Open Group and the Directory Interoperability Forum    14

Developing with Active Directory Services Interface    14

Miscellaneous    14

Introduction

Directories—public or private resource lists containing names, locations, and other identifying information—are essential tools often taken for granted in our daily activities. Typically these directories provide information about people, places, or organizations as part of an overall solution. For example, a telephone is virtually useless without a directory to correspond names with telephone numbers. Historically, most directories were only available in printed form.

As the computer revolution forged ahead, printed directories gave way to an electronic counterpart. Many application providers capitalized on the directory concept offering proprietary versions that extended their application’s functionality. Network operating systems also provided directories, typically housing user and device information. Unfortunately, these first generation directories were often developed with little or no concern for interoperability. Isolated and specific in function, they performed admirably. However, it was obvious directories needed to interact within a larger network ecosystem. This idea grew into the definition of the X.500 standard.

Directory Foundation: X.500

In 1988, the International Organization for Standardization (ISO) and the International Telecommunications Union (ITU) introduced the X.500 standard. X.500 defines the protocols and the information model for an application and network platform agnostic directory service. As a distributed directory based on hierarchically named information objects, X.500 specifications characterized a directory that users and applications could browse or search.

The X.500 paradigm includes one or more Directory System Agents (DSAs)—directory servers—with each holding a portion of the Directory Information Base (DIB). The DIB contains named information objects assembled in a tree structure—defined by a Directory Information Tree (DIT)—with each entry having an associated set of attributes. Every attribute has a pre-defined type and one or more associated values. Object classes, containing mandatory and optional attributes, are defined within a directory schema. End users communicate with an X.500 DSA using the Directory Access Protocol (DAP) while the Directory System Protocol (DSP) controls interaction between two or more DSAs.

X.500: The Need for a Lightweight Alternative

Understanding the need for a streamlined directory standard, several implementers proposed a lightweight alternative for connecting to X.500 directories. Ultimately, the first iteration of LDAP gained traction as a simple alternative to the X.500 Directory User Agent (DUA). The new LDAP definition:

• Simplified protocol encoding
  
• Used text encoding for names and attributes
  
• Mapped directly onto the TCP/IP stack
  
• Supplied a simple Application Programming Interface (API)
  

What Is LDAP?

Organized development of LDAP occurred on several fronts. However, the most notable work, and the first freely available implementation, was completed by the University of Michigan in 1993. The University focused efforts on developing a simpler TCP/IP version of X.500’s DAP. DAP was considered cumbersome as it pushed much of its workload to the client.

Although LDAP is well rooted as a simplified component of the X.500 directory, it has become the de facto directory protocol on the Internet today.

LDAP: First Generation

LDAP’s initial implementations provided gateway services between X.500 directory servers and clients. The clients communicated with an LDAP gateway through LDAP-enabled software. In turn, the gateway handled transactions—on behalf of the client—with the X.500 DSA. This model promoted directory interoperability allowing application providers to easily develop client software capable of communicating with an LDAP gateway service, regardless of the backend platform. While LDAP was initially created to meet this requirement, it became clear that a parting from X.500 was needed to simplify deployments. In 1994, LDAP was transformed into a directory specification with its own database and structuring conventions.

Once transformed, the LDAP specifications reflected a true client-server model with clients making requests directly to servers for information or operations. One or more directory servers may either perform the operation or refer the client to another directory server that may be able to provide the requested information, or perform the requested operation. The LDAP client will see the same view of the directory no matter which server is contacted. If necessary, the LDAP server can authenticate the client to the operating system in use. Once received, the LDAP server will convert a request into an appropriate format for the accessed directory. For X.500 directories, the LDAP server would convert the LDAP request into a DAP request.

Enhancements with Version 2

As interest in LDAP increased, several new developments extended its core functionality while streamlining its footprint. In 1995, Request for Comment (RFC) 1777 was introduced for LDAP Version 2. RFC 1777 eliminated many of the impracticable components of X.500 that were central to the original LDAP specifications. Furthermore, network connectivity was changed from the X.500 Open Standards Intercommunication (OSI) model to the TCP/IP model.

LDAPv2 is officially defined by the following RFCs:

• RFC
  1777 – Lightweight Directory Access Protocol (v2)
  
• RFC
  1778 – The String Representation of Standard Attribute Syntaxes
  
• RFC
  1779 – A String Representation of Distinguished Names
  

The Current State of LDAP

Developed by the Internet Engineering Task Force (IETF) in 1997, the current LDAPv3 implementation is a renovation of LDAPv2, which primarily tackles deployment limitations identified within the previous version. LDAPv3 also enriches compatibility with X.500 along with enhanced integration with non-X.500 directories. LDAPv3 encompasses LDAPv2 within a new set of RFCs.

LDAPv3 is a Proposed Standard currently defined by RFC 3377, which includes, the RFCs below in addition to support for the LDAPv2 RFCs listed above:

• RFC 2251 – Lightweight Directory Access Protocol (v3)
  
• RFC 2252 – LDAP (v3): Attribute Syntax Definitions
  
• RFC 2253 – LDAP (v3): UTF-8 String Representation of Distinguished Names
  
• RFC 2254 – String Representation of LDAP Search Filters
  
• RFC 2255 – The LDAP URL Format
  
• RFC 2256 – A Summary of X.500(96) User Schema for use with LDAP (v3)
  
• RFC 2829 – Authentication Methods for LDAP
  
• RFC 2830 – LDAP (v3): Extensions for Transport Layer Security
  

What Does It Mean to Be LDAP Compliant?

Claiming LDAP compliance seems to be a common occurrence among today’s directory-capable vendors. In truth, applications may be either directory-aware—capable of reading an LDAP directory—or directory-enabled—capable of reading and performing other defined LDAP operations on a directory. Both implementations should be considered LDAP-compliant if proposed standards are followed in achieving an application’s desired level of LDAP functionality. Compliance with standards, however, does not ensure interoperability. Standards may lack sufficient clarity, even after formalization, leading to varying guideline interpretations.

The compliance task for directory server vendors weighs on their ability to conform to defined standards while ensuring interoperability with those standards. In addition, the process of standard formalization is an ongoing effort compounding the difficulty of achieving full compliance. For example, LDAPv2 has been formalized with a defined set of RFCs, however, LDAPv3, a Proposed Standard, is theoretically still a work in progress as it moves toward an Internet Standard. In summary, a vendor’s compliance statement should be viewed as their ability to implement a known set of RFCs, accurately interpret those RFCs to ensure interoperability, and provide a framework capable of incorporating new RFCs.

Achieving Compliance: IETF Applicability Statement

The IETF has established an LDAPv3 Working Group—ldapbis—with the charter of steering the previously mentioned LDAPv3 RFCs through the Internet Standards process. In September 2002, the working group submitted a revised LDAP specification for consideration as a draft standard. Currently, no IETF LDAPv3 conformance or compliance directive exists; however, one of the most pervasive documents to be delivered by the working group is an LDAP applicability statement.

Once complete, an LDAP applicability statement will guide independent vendors toward IETF-based LDAP compliance. In all probability, third-party test suites, based on the applicability statement, will be developed, allowing vendors and end users to accurately determine LDAP compliance. Until then, vendor declarations combined with existing third-party testing suites are the most suitable alternatives.

Achieving Compliance: Third-Party Test Suites

Several LDAP compliance testing and certification solutions exist in the marketplace today. One of the most prominent test suites is offered by the Open Group, a technology-neutral consortium. The Open Group also sponsors the Directory Interoperability Forum (DIF), which compromises architects, strategists, and product managers from customers and vendors of directories and directory-enabled applications. According to the Open Group, the DIF is chartered to “provide testing and certification for directory servers and applications that use Open Directory Standards.” Microsoft Corporation is an active member of the DIF.

The Open Group LDAP Certifications

Recently released in 2003 are two directory Open Group/DIF test certifications: LDAP Ready and LDAP Certified. With an LDAP Certified directory, the vendor guarantees its LDAP directory server conforms to the LDAPv3 specifications published by the IETF. On the other hand, an LDAP Ready application warrants that it will interoperate with an LDAP Certified directory.

To become LDAP Certified, directory vendors typically submit a warranty of LDAPv3 conformance using the Open Group’s VSLDAP Test Suite as a compliance measuring tool. The warranty is intended to ensure conformity to industry standard specifications, conformity through a product’s lifecycle, and the quick remedy of any nonconformity. At the moment, no vendor has received either of these new certifications.

Setting an LDAP Compliance Baseline

According to the DIF, a baseline of LDAP compliance can be measured by verifying support of the following RFC components. By no means exhaustive, an LDAP compliance baseline can be supplemented by support of additional LDAP RFCs, features, or extensions.

Active Directory’s LDAP Compliance

Windows 2000 Server

With the release of Windows 2000 Server, Microsoft made its first introduction of Active Directory®directory service. With a foundation stemming from proven Microsoft technologies, Active Directory’s origins lie within the original 1996 release of Microsoft Exchange Server 4.0. Active Directory, however, was a significant paradigm shift from the familiar Windows NT 4.0 identity store. By envisioning a directory capable of extending beyond a typical NOS store, Microsoft’s first implementation of Active Directory became a viable repository for many forms of enterprise data.

To allow Active Directory to gain popularity as an enterprise repository, Microsoft had to make an early commitment to LDAP. The Windows 2000 implementation of Active Directory is an LDAP-compliant directory supporting the core LDAP RFCs available at the time of its release. Currently, Windows 2000 Active Directory reaches LDAP compliance through support of the following RFCs.

Lightweight Directory Access Protocol (v3)

Windows Server 2003

Building on the foundation established in Windows 2000 Server, the Active Directory service in Windows Server 2003 extends beyond the baseline of LDAP compliance into one of the most comprehensive directory servers offering a wide range of LDAP support. Accordingly, the Windows Server 2003 Active Directory service introduces a number of new LDAP capabilities targeted for IT professionals and application developers. Some of the latest LDAP features include:

• Dynamic Entries – Active Directory can store dynamic entries allowing the directory to assign Time-To-Live (TTL) values to determine automatic entry deletion.
  
• Transport Layer Security (TLS) – Connections to Active Directory over LDAP can now be protected using the TLS security protocol.
  
• Digest Authentication Mechanism – Connections to Active Directory over LDAP can now be authenticated using the DIGEST-MD5 Simple Authentication and Security Layer (SASL) authentication mechanism. The Windows Digest Security Support Provider (SSP) provides an interface for using Digest Authentication as an SASL mechanism.
  
• Virtual List Views (VLV) – When an LDAP query has a large result set, it is inefficient for a client application to pull down the entire set from the server. VLV allows a client application to “window” through a large result set without having to transfer the entire set from the server.
  
• Concurrent LDAP Binds – Although not specifically defined by the IETF, Concurrent LDAP Bind provides the ability to perform multiple LDAP binds on one connection for the purpose of authenticating users.
  

Although the LDAP compliance guidelines proposed by the Directory Interoperability Forum signify conformance at a base level, organizations demand directory servers capable of providing robust network and application services. This is one of the driving forces behind Microsoft’s support of virtually all IETF- recognized LDAP components. Although LDAP compliance should always be considered a work in progress until full IETF standardization, Microsoft’s current LDAP compliance in Windows Server 2003 includes support of the following RFCs.

Lightweight Directory Access Protocol (v3)

Compliance Misconceptions

Since the release of Windows 2000 Server Active Directory, many assertions have been made about its LDAPv3 compliance. As noted previously, Active Directory and Microsoft Exchange Server have supported a baseline of LDAP compliance since inception. Furthermore, Active Directory extends this baseline by supporting a majority of the LDAP Informational and Proposed RFCs. Although Microsoft’s commitment to building one of the most comprehensive and interoperable directories in the marketplace is obvious, there are still common compliance misconceptions worth discussing.

inetOrgPerson

One of the most widely publicized Windows 2000 Server LDAP non-compliance claims pivots on the support of RFC 2798 and inetOrgPerson. RFC 2798 was submitted as an Informational RFC in April 2000 defining the inetOrgPerson LDAP object class. The object class definition is considered an extension to LDAPv3 and, as such, is not included in the RFC 3377 LDAPv3 specifications.

Nevertheless, the timing of the Windows 2000 Server release in February 2000 and the introduction of RFC 2798 in April of that same year barred the inclusion of the inetOrgPerson Object Class within the Windows 2000 Server Active Directory schema. However, any Windows 2000 Active Directory implementation can easily accommodate inetOrgPerson through Active Directory’s extensible schema.

In response to customer requests for an inetOrgPerson object class for Windows 2000, Microsoft released the Windows 2000 inetOrgPerson Kit. The kit, which is freely available, derives inetOrgPerson from the user class within Active Directory’s schema. In addition to defining the inetOrgPerson, the kit delivers basic administrative control over the class object. See the “Additional Resources” section for information on obtaining the Microsoft Windows 2000 inetOrgPerson Kit.

As noted previously, Windows Server 2003’s Active Directory schema includes the full definition and provides complete manageability of RFC 2798 and the inetOrgPerson LDAP Object Class.

Native LDAP Calls

Another common misconception is that Active Directory does not support native LDAP calls, thereby forcing developers to use Active Directory Services Interface (ADSI) for access to the directory. In fact, Active Directory fully supports native LDAP calls through support of the LDAP API (RFC 1823) — it is actually the primary access method for the majority of Window’s directory operations. Microsoft merely provides ADSI as an abstraction layer to simplify directory development and promote directory interoperability.

It is important to note that Active Directory does have some functionality that is not exposed by the LDAP protocol since the functionality extends beyond the LDAP model. For instance, LDAP applications bind to specific directory servers via the server’s DNS name whereas Active Directory applications have the option of employing a distributed locator service to find a nearby replica of a given directory partition. This option does not force ISVs to rewrite their applications since they can bind to a specific LDAP server as usual. However, if an ISV wants to use the Active Directory distributed locator service to reduce the customer cost of managing an application, they can call the DsGetDcName API and then use LDAP. Conversely, the ISV may use ADSI, which abstracts away both DsGetDcName and LDAP. The choice is entirely up to the ISV.

Active Directory fully supports writing to the Server Principal Name via the Windows Server 2003 and Windows 2000 Server LDAP APIs.

Directory Interoperability

It is not uncommon to find a variety of directories—from resource to application-specific—deployed within the enterprise today. However, multiple directories can create complex challenges for the users, administrators, and developers of any organization. From multiple user logon requirements and management complexities to developers needing to support multiple versions of their applications, directory complexities may continue without a common management and development framework. Microsoft has introduced several interoperability tools to help customers reduce the complexities of using and managing heterogeneous directory servers.

LDAP API

An LDAP API is available through the Microsoft Developer Network (MSDN) Platform SDK. It provides native LDAP functionality for client applications to search for and retrieve information from an LDAP-compliant directory server. Additional functions, such as modifying directory entries or access control to allow clients to authenticate themselves, are also available from within the LDAP API. Actually, the API supports all LDAP compliance specific functions identified in the previous sections to assist in development efforts.

Active Directory Services Interface

ADSI is a set of Component Object Model (COM) programming interfaces that make it easy for customers and independent software vendors (ISVs) to build applications that register with, access, and manage multiple directory services with a single set of well-defined interfaces. ADSI is an open API.

One of the most familiar open programming APIs is Open Data Base Connectivity (ODBC). ODBC provides open interfaces for relational databases, thus allowing developers to write applications and tools that work with any database that supports ODBC. Because of the thriving ODBC development community, every major relational database now supports ODBC. ADSI is equivalent to ODBC for directory services.

ADSI provides developers with access to multiple directory service providers through a set of interfaces. Applications written to ADSI work with any directory service that offers an ADSI provider, thus addressing some of the complexities outlined previously. By abstracting the capabilities of directory services from different network providers, ADSI presents a single set of directory service interfaces for managing network resources.

For example, ADSI defines a naming convention that can uniquely identify an ADSI object in an LDAP-compliant or similar directory. These names are called AdsPath strings. In the following code example, AdsPath contains the LDAP provider’s moniker and the provider’s specific path to identify an organizational unit in the Sample Domain:

LDAP://OU=Sales, DC=Sample, DC=COM

As an interoperability tool, ADSI is:

• Open – Any directory vendor can implement an ADSI provider; Microsoft has included an LDAP ADSI provider.
  
• Directory Service Independent – Administrative applications are not bound to a vendor’s directory service as applications can be developed without understanding of vendor-specific directory APIs.
  
• Multiple Development Language Capable – COM applications can be written in languages such as Microsoft® Visual Basic®, Microsoft Visual Basic Scripting Edition (VBScript), and C/C++.
  
• Simple Programming Model – ADSI consists of a small, easy-to-learn set of interfaces.
  
• Scriptable – Any automation-compatible language can be used to develop directory service applications. Administrators and developers can use the tools they already know.
  
• Functionally Rich – ISVs and sophisticated end users can develop serious applications using the same ADSI models used for simple scripted administrative applications.
  
• Extensible – Directory providers, ISVs, and end users can extend ADSI with new objects and functions to add value or meet unique needs.
  
• OLE DB Aware – ADSI provides an OLE DB interface so that programmers familiar with database programming through OLE DB can use it effectively.
  

Development Environments

ADSI client applications can be written in many languages. For most administrative tasks, ADSI defines interfaces and objects accessible from automation-compliant languages like Microsoft^® Visual Basic^®, Microsoft Visual Basic Scripting Edition (VBScript), Active Server Pages (ASP), and Java to the more performance and efficiency-conscious languages such as C and C++. A good foundation in COM programming is useful to the ADSI programmer.

Active Directory Application Mode

Active Directory Application Mode (ADAM) is a new capability of Microsoft Active Directory that addresses certain deployment scenarios related to directory-enabled applications. ADAM runs as a non-operating system service and, as such, does not require deployment on a domain controller. Running as a non-operating system service means that multiple instances of ADAM can run concurrently on a single server, with each instance being independently configurable.

While the vision of a single enterprise directory is still unfulfilled, ADAM represents a significant breakthrough in directory services technology that separates typical Network Operating System (NOS) functionality from a native LDAP directory.

Directory Services Markup Language

The Directory Services Markup Language (DSML) provides a means of representing directory structural information and directory operations as an XML document. The intent of DSML is to allow XML-based enterprise applications to leverage profile and resource information from a directory in their native environment. DSML allows XML and directories to work together and provides a common ground for all XML-based applications to make better use of directories.

DSML Services for Windows extends the power of the Active Directory service. Because DSML Services for Windows uses open standards such as HTTP, XML, and SOAP, a greater level of interoperability is possible. For example, in addition to the already standard Lightweight Directory Access Protocol (LDAP), many devices and other platforms have other alternatives to communicate with Active Directory. This provides a number of key benefits for IT administrators and independent software vendors (ISVs), who can now have even more open-standard choices to access Active Directory.

DSML Services for Windows supports DSML version 2 (DSMLv2). DSMLv2 is a standard approved by the Organization for the Advancement of Structural Information Standards (OASIS) and backed by many directory services vendors. Supporting the DSMLv2 specification allows better interoperability with other directory services vendors who are also supporting this standard. For information about the DSMLv2 specification and schema, see the OASIS Web site.

Microsoft Identity Integration Server 2003, Enterprise Edition

For organizations that must support heterogeneous directories, Microsoft Identity Integration Server (MIIS) 2003, Enterprise Edition, enables the integration and management of identity information across multiple repositories, systems, and platforms. Although the administration of these multiple repositories often leads to time-consuming and redundant efforts, it also causes frustration for users who may need to remember multiple IDs and passwords for varying applications or systems. The larger the organization, the greater the potential for multiple repositories and the effort required to keep them current.

MIIS augments Active Directory by providing broad interoperability capabilities, including integration with a wide range of identity repositories; provisioning and synchronizing identity information across multiple stores; and brokering changes to identity information by automatically detecting updates and sharing the changes across systems.

Additional Resources

See the following resources for further information:

Lightweight Directory Access Protocol Version 3

The IETF LDAPv3 Working Group:

• http://www.ietf.org/html.charters/ldapbis-charter.html
  

The LDAPv3 Working Group archived newsgroup:

• http://www.openldap.org/lists/ietf-ldapbis/
  

RFC 3377, the current definition of LDAPv3:

• ftp://ftp.rfc-editor.org/in-notes/rfc3377.txt
  

Open Group and the Directory Interoperability Forum

The Open Group’s VSLDAP compliance testing suite overview:

• http://www.opengroup.org/directory/mats/ldap2000/dsvsldap.pdf
  

The Directory Interoperability Forum (DIF):

• http://www.opengroup.org/directory/
  

Developing with Active Directory Services Interface

Active Directory Services Interface developer’s reference:

• http://msdn.microsoft.com/library/default.asp?url=/downloads/list/directorysrv.asp
  

Miscellaneous

The Microsoft Active Directory Web site:

• http://www.microsoft.com/ad
  

Active Directory Application Mode (ADAM):

• http://www.microsoft.com/windowsserver2003/adam/default.mspx
  

Directory Services Markup Language (DSML):

• http://www.microsoft.com/windows2000/server/evaluation/news/bulletins/dsml.asp
  

Microsoft Identity Integration Server 2003, Enterprise Edition:

• http://www.microsoft.com/windowsserver2003/technologies/directory/miis/default.mspx
  

The Microsoft Windows 2000 inetOrgPerson Kit:

• http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnactdir/html/inetopkit.asp
  

LDAP reference for developers on MSDN:

• http://msdn.microsoft.com/library/default.asp?url=/library/en-us/netdir/ldap/ldap_reference.asp
  

LDAP API reference for developers on MSDN:

• http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wceldap/htm/cmconusingldapapi.asp
  

For the latest information about Windows Server 2003, see the Windows Server 2003 Web site at http://www.microsoft.com/windowsserver2003.

Identity and Access

Windows Server 2012

Table of contents

Identity and access enhancements in Windows Server 2012………… 5

Protecting digital assets with previous versions of Windows Server ……………………………………………. 5

Protecting digital assets with Windows Server 2012 …………………………………………………………………………. 6

Dynamic Access Control ………………………………………………………………………. 7

Classification …………………………………………………………………………………………………………………………………………………… 8

Control access ………………………………………………………………………………………………………………………………………………… 8

The structure of central access policies ……………………………………………………………………………………………………………… 9

Central access policies and file servers ……………………………………………………………………………………………………………. 10

Policy staging ……………………………………………………………………………………………………………………………………………………….. 11

Access-denied remediation ………………………………………………………………………………………………………………………………. 11

Security auditing ………………………………………………………………………………………………………………………………………….. 13

Protection ………………………………………………………………………………………………………………………………………………………. 14

Active Directory Domain Services ……………………………………………………..16

Simplified deployment ………………………………………………………………………………………………………………………………. 16

Deployment with cloning …………………………………………………………………………………………………………………………. 17

Safer virtualization of domain controllers ……………………………………………………………………………………………. 17

Windows PowerShell script generation ……………………………………………………………………………………………….. 18

Active Directory for client activation ……………………………………………………………………………………………………… 19

Group-managed service accounts …………………………………………………………………………………………………………. 19

DirectAccess and remote access ……………………………………………………….20

Integrated remote access …………………………………………………………………………………………………………………………. 21

Cross-premises connectivity ……………………………………………………………………………………………………………………. 23

Improved management experience ……………………………………………………………………………………………………… 24

Easier deployment ………………………………………………………………………………………………………………………………………. 24

Improved deployment scenarios ……………………………………………………………………………………………………………. 25

Windows Server 2012: Identity and Access

2

Windows Server 2012: Identity and Access

3

Scalability improvements ………………………………………………………………………………………………………………………….. 25

Summary ……………………………………………………………………………………………….26

List of charts, tables, and figures ………………………………………………………..27

Windows Server 2012: Identity and Access

4

Copyright information

© 2012 Microsoft Corporation. All rights reserved. This document is provided “as-is.” Information and views expressed in this document, including URL and other Internet website references, may change without notice. You bear the risk of using it. This document does not provide you with any legal rights to any intellectual property in any Microsoft product. You may copy and use this document for your internal, reference purposes. You may modify this document for your internal, reference purposes.

Windows Server 2012: Identity and Access

5

Identity and access enhancements in Windows Server 2012

Today’s organizations need the flexibility to respond rapidly to new opportunities. They also need to give workers access to data and information—across varied networks, devices, and applications—while still keeping costs down. Innovations that meet these needs—such as virtualization, multitenancy, and cloud-based applications—help organizations maximize existing infrastructure investments, while exploring new services, improving management, and increasing availability.

While some factors—such as hybrid cloud implementations, a mobile workforce, and increased work with third-party business partners—add flexibility and reduce costs, they also lead to a more porous network perimeter. When organizations move more and more resources into the cloud, and grant network access to mobile workers and business partners outside the firewall, managing security, identity, and access control becomes a greater challenge. Adding to this challenge are increasingly stringent regulatory requirements, such as Health Insurance Portability and Accountability Act (HIPAA) Privacy Rule and Sarbanes-Oxley Act of 2002 (SOX), both of which increase the cost of compliance.

New and enhanced capabilities in Windows Server 2012 help organizations meet these challenges by making it easier and less costly to ensure secure access to valuable digital assets and comply with regulations. These identity and access improvements include:

 Dynamic Access Control. A new feature that enables automated information governance on file servers for compliance with business and regulatory requirements.

 Active Directory Domain Services. Storing directory data and managing communication between users and domains is improved by making it easier to deploy and virtualize domain services both locally and remotely, as well as simplifying management tasks.

 DirectAccess. Always-available connectivity to the corporate network now includes simplified deployment, a streamlined management experience, and improved scalability and performance.

This paper provides an introduction to these Windows Server 2012 identity and access technologies for IT professionals.

Protecting digital assets with previous versions of Windows Server

A major obstacle faced by IT professionals today is the sheer volume of repetitive work that needs to be done, particularly when large deployments of physical or virtual desktops are involved. Manual work is not only time-consuming, but it makes systems less secure by introducing many opportunities for errors and misconfiguration. In previous versions of Windows Server, Microsoft helped reduce this type of work.

For example, Windows Server 2008 R2 included the following improvements:

Windows Server 2012: Identity and Access

6

 File Classification Infrastructure. This new infrastructure for classifying files by tagging them enabled IT professionals to more easily manage unstructured data in files based on their organizational value.

 Active Directory Domain Services. As part of the Information Protection Solution, Active Directory Domain Services was improved to make domain controllers easier to deploy, both on-premises and in the cloud.

 System Management and Security. The Windows PowerShell 2.0 command-line interface enabled IT professionals to automate many common tasks involved in deploying and managing desktops.

Protecting digital assets with Windows Server 2012

With Windows Server 2012, Microsoft builds on these previous improvements by making it even easier to configure, manage, and monitor users, resources, and devices to improve security and automate auditing. In addition, Windows Server 2012 includes the following new and enhanced identity, access and data protection features:

 Dynamic Access Control gives you the ability to automatically control and audit access to files in file shares across your organization based on the characteristics of both the files and the users requesting access to them. It uses claims to achieve this high degree of access control specificity. Claims, contained in security tokens, consist of assertions about a user or device, such as name or type, department, or security clearance, for example. You can employ user claims, device claims, and file classification tags to centrally control and audit access to files, as well as use Rights Management Services (RMS) to protect information in files across your organization.

 Active Directory Domain Services is important in new hybrid cloud infrastructures because it supports the increased need for security, compliance, and access control. Furthermore, unlike many competing cloud services, which require users to have a separate set of credentials hosted with the provider, Active Directory Domain Services provides continuity between your on-premises and cloud resources so that users only need a single set of credentials no matter where the resources are located. A new deployment wizard and support for cloning virtual domain controllers makes Active Directory Domain Services easier to virtualize and simpler to deploy, both locally and remotely. The introduction of Active Directory Domain Services and Windows PowerShell 3.0 integration, and the ability to capture and record command-line syntax as you perform tasks in the Service Manager interface, improves automation of manual tasks. Other new features include desktop activation support using Active Directory Domain Services and Group Managed Service Accounts.

 DirectAccess allows nearly any user who has an Internet connection to more securely access corporate resources, such as email servers, shared folders, or internal websites, with the experience of being easily connected to the corporate network. Windows Server 2012 offers a new, unified management experience, allowing administrators to configure DirectAccess and legacy virtual private network (VPN) connections from one location. Other enhancements simplify deployment, and improve performance and scalability.

The remaining sections in this paper describe these new features in more detail.

Windows Server 2012: Identity and Access

7

Dynamic Access Control

Dynamic Access Control in Windows Server 2012 gives IT professionals new ways to control access to file data and monitor regulatory compliance. It provides next-generation authorization and auditing controls, along with classification capabilities that let you apply information governance to the unstructured data on file servers.

Until now, file security was handled at the file and folder level. IT professionals had little control over the way security was handled by users day to day. However, by using Dynamic Access Control, you can restrict access to sensitive files regardless of user actions by establishing and enforcing file security policy at the domain level, which is then enforced across all Windows Server 2012 file servers. For instance, if a development engineer accidentally posts confidential files to a publicly shared folder, those files can still be protected from access by unauthorized users.

In addition, security auditing is now more powerful than ever, and audit tools make it easier to prove compliance with regulatory standards, such as the requirement that access to Health and Biomedical Information (HBI) is appropriately guarded and regularly monitored.

Windows Server 2012 provides the following new and enhanced ways to control access to your files while providing authorized users the resources they need:

 Classify. Automatic and manual file classification using an improved file classification infrastructure. There are several methods to manually or automatically apply classification tags to files on file servers across the organization.

 Control. Central access control for information governance. You can control access to classified files by applying central access policies (CAPs). CAPs is a new feature of Active Directory that enables you to define and enforce very specific requirements for granting access to classified files. For example, you can define which users can have access to files that contain health information within the organization by using claims that might include employment status (such as full-time or contractor) or access method (such as managed computer or guest). You can even require two-factor authentication, such as that provided by smartcards. Central access control functionality includes the ability to provide automated assisted access-denied remediation when users have problems gaining access to files and shares.

 Audit. File access auditing for forensic analysis and compliance. You can audit access to files by using central audit policies, for example, to identify who gained (or tried to gain) access to highly sensitive information.

 Protect. Classification-based encryption. You can apply protection by using automatic RMS encryption for sensitive documents. For example, you can configure Dynamic Access Control to automatically apply RMS protection to all documents that contain HIPAA information. This feature requires a previously provisioned RMS environment.

Dynamic Access Control can provide these classification, control, audit, and protection capabilities because it is built on top of the following technologies:

 A new Windows authorization and audit engine that can process conditional expressions and central policies

 Kerberos support for user claims and device claims within Active Directory Domain Services

Windows Server 2012: Identity and Access

8

 Improvements to the File Classification Infrastructure

 RMS extensibility support so that partners can provide solutions that encrypt third-party files

You can use the Dynamic Access Control application programming interface (API) to extend these technologies and create custom classification tools, audit software, and more.

Classification

The first step in establishing file access policies that are more secure is to identify the files, and then classify them by applying tags to group files based on the information they contain. In Windows Server 2012, files are tagged in one of four ways:

 By location. When a file is stored on a file server, it inherits the tags from its parent folder. Folder tags are specified by the folder owner.

 Manually. Users and administrators can manually apply tags through the Windows 8 operating system File Explorer interface, or use data entry applications to apply them.

 Automatically. Automatic classification processes in Windows Server 2012 can automatically tag files, depending on the content of the file. This method is useful for applying tags to large numbers of files.

 By application API. Applications can use APIs to tag files that they manage. For example, tags can be specified by line-of-business (LOB) applications that store information on file servers, or by data management applications.

Control access

Controlling access to files is enforced by central access policies—sets of authorization policies that you centrally manage in Active Directory Domain Services, and deploy to file servers using Group Policy. You can use CAPs to comply with both organizational and regulatory requirements. CAPs help you to create more complete access policies by pairing information about files in the form of file tags with user and device claims.

In earlier versions of Windows Server, claims were used only by Active Directory Federation Services to authorize users in one domain to use applications in different, federated domains based on attributes submitted to Active Directory Federation Services. In Dynamic Access Control, the functionality of claims is essentially the same. In both cases, a claim consists of one or more statements (for example, name, identity, key, group, privilege, or capability) made about a user or device. These statements are contained in a security token that is issued and signed by a trusted partner or entity (such as Active Directory Domain Services) and used for authorizing that user or device to access a resource. You can create claim properties, either manually by using the Active Directory management tools or by using an identity management tool. In the case of Dynamic Access Control, the token is issued by Active Directory Domain Services and the resource to be accessed is a file.

In Dynamic Access Control, claims can be combined into logical policies that enable fine-grained control over arbitrarily-defined subsets of files. The following are two examples of situations where you might want to apply such policies:

 To obtain access to high-business-impact (HBI) information, a user might be required to be a full-time employee. In this scenario, you would have to do the following:

o Identify and tag the files that contain HBI information.

Windows Server 2012: Identity and Access

9

o Identify the full-time employees in your organization.

o Create a central access policy that applies to all files that contain HBI on all file servers across the organization.

 To enforce an organization-wide requirement to restrict access to personally identifiable information (PII) in files so that only the file owner and members of the human resources (HR) department are allowed to view it, you might implement a policy that applies to all PII files independent of their location. In this scenario, you would have to do the following:

o Identify and classify (tag) the files that contain PII.

o Identify the group of HR members that are allowed to view PII information.

o Create a CAP that applies to all files that contain PII on all file servers across the organization.

The motivation to deploy and enforce an authorization policy can arise for different reasons and from multiple levels of the organization. The following are some examples:

 Organization-wide authorization policy. Most commonly initiated from the information security office, this type of authorization policy arises from compliance or another high-level requirement that is relevant across the organization. For example, HBI files should be accessed by full-time employees only.

 Departmental authorization policy. Various departments in an organization may have special data-handling requirements that they want to enforce. For example, the finance department might want to limit access to finance servers for the finance employees.

 Specific data-management policy. This type of policy usually arises from compliance and organizational requirements for protecting information that is being managed, such as to prevent modification or deletion of files that are under retention or files that are under electronic discovery (eDiscovery).

 Need-to-know policy. This type of policy is typically used in conjunction with the policy types mentioned earlier. The following are two examples:

o Vendors should be able to access and edit only those files that relate to a project that they are working on.

o In financial institutions, information barriers are important so that analysts do not access brokerage information and brokers do not access analysis information.

The structure of central access policies

CAPs stored in Active Directory Domain Services act as security umbrellas that an organization applies across its file servers. These policies supplement—but do not replace—the local access policy, or discretionary access control list (DACL), applied to files and folders. For example, if a local DACL allows access to a specific user, but a CAP that is applied to the file denies access to the same user, the user cannot access the file. The reverse also applies: if a CAP allows access to a user but the local DACL denies it, the user cannot access the file. File access is only possible when permitted by both local DACLs and CAPs.

A CAP can contain many rules—each of which are evaluated and deployed as part of an overall CAP. Each rule contained in the CAP has the following logical parts:

 Applicability. This is a condition that defines which files the rule applies to. For example, the condition can define all files tagged as containing personal information.

Windows Server 2012: Identity and Access

10

 Access conditions. This is a list of one or more access control entries (ACEs) that define who can access the data, such as allow read and write access if the user has a high clearance level and their device is a managed device.

Figure 1 shows the components of a CAP rule, and how they can be combined to create very explicit data access policies.

Figure 1: CAP components

Central access policies and file servers

Figure 2 shows the interrelationships between Active Directory Domain Services, where CAPs, user claims, and property definitions are defined and stored; the file server, where these policies are applied; and the user, who is trying to gain access to a file on the file server.

Figure 2: CAP structure

Figure 3 shows how you can combine different rules (blue boxes) into a CAP (green boxes) that can then be applied to shares on file servers across the organization.

Windows Server 2012: Identity and Access

11

Figure 3: Combining multiple policies into policy lists and applying them to resources

Policy staging

When you want to change a policy, Microsoft Windows 2012 lets you test a proposed policy that runs parallel to the current policy so that you can identify the consequences of the new policy without enforcing it. This feature, which is known as policy staging, lets you measure the effects of a new policy in the production environment.

When policy staging is enabled, Windows Server 2012 continues to use the current policy to authorize user access to files. However, if the access allowed by the proposed policy differs from that of the current policy, the system logs an event with the details. You can use the logged events to determine whether the policy has to be changed or is ready to be deployed.

Access-denied remediation

Of course, denying access is only part of an effective central access control strategy, and sometimes access must be granted after at first being denied. Today, when access is denied, the user does not receive additional information on how to get access. This causes a lot of pain for both users and help desk or IT administrators. To mitigate this problem, assisted access-denied remediation in Windows Server 2012 enables you to provide the user with additional information and the opportunity to send an access request email message to the appropriate owner. Access-denied remediation reduces the need for manual intervention by providing the following three different processes for granting users access to resources:

 Self-remediation. If users can determine what the issue is and correct the problem so that they can get the requested access, the impact on the organization is low, and minimal special exceptions are needed in the organization policy. Windows Server 2012 helps you to author a general access-denied message to help users self-remediate when access is denied. This message can include URLs to direct the users to self-remediation websites provided by the organization.

Windows Server 2012: Identity and Access

12

 Remediation by the file owner. Windows Server 2012 enables you to create a distribution list of file or folder owners so that users can directly connect with them to request access. This resembles the Microsoft SharePoint model, where the share owner receives a user’s request for access to a file. Remediation can range from adding user rights to the appropriate file or folder to editing share permissions. For example, if a local DACL on a file allows access to a specific user but a CAP restricts access to the same user, the user will be unable to gain access to the file. In Windows Server 2012 when a user requests access to a file or a folder, an email message with the request details is sent to the file owner. When additional help is required, the file owner can in turn forward this information to the appropriate IT administrator.

 Remediation by help desk and file server administrators. When the user cannot self-remediate an access problem and the file owner cannot help, the issue can be corrected manually by the help desk or file server administrator. This is the most costly and time-consuming remediation. Windows Server 2012 provides a UI to view the effective permissions for users on a file or a folder so that it is easier to troubleshoot access issues.

Figure 4 shows the series of events involved in access-denied remediation.

Figure 4: Access-denied remediation

Access denied remediation provides a user access to a file when it has been initially denied:

Windows Server 2012: Identity and Access

13

1. The user attempts to read a file.

2. The server returns an “access denied” error message because the user has not been assigned the appropriate claims.

3. On a computer running Windows 8, Windows retrieves the access information from the File Server Resource Manager on the file server, and presents a message with the access remediation options, which may include a link for requesting access.

4. When the user has satisfied the access requirements (for example, by signing a non-disclosure agreement, or providing other authentication) the user’s claims are updated.

5. The user can access the file.

Security auditing

Security auditing of file access is one of the most powerful tools to help maintain the security of an organization. A key goal of security auditing is regulatory compliance. For example, industry standards such as SOX, HIPAA, and Payment Card Industry (PCI) require organizations to follow a strict set of rules related to data security and privacy. Security audits help establish the presence or absence of such policies and thereby prove compliance or noncompliance with these standards. Additionally, security audits help detect anomalous behavior, identify and reduce gaps in security policy, and deter irresponsible behavior by creating a trail of user activity that can be used for forensic analysis. Audit policy requirements typically arise from three levels:

 Information security. File access audit trails are frequently used for forensic analysis and intrusion detection. The ability to monitor specified events regarding access to high-value information lets organizations significantly improve their response time and investigation accuracy.

 Organizational policy. For example, organizations regulated by PCI standards can have a central policy to monitor access to all files that are marked as containing credit card information and PII, or organizations may want to monitor all unauthorized attempts to view information about their projects.

 Departmental policy. For example, the finance department may require that the ability to modify certain finance documents (for example, a quarterly earnings report) be restricted to the finance department, and thus want to monitor all other attempts to change these documents. Additionally, the compliance department may want to monitor all changes to central policies and policy constructs such as user, computer, and resource attributes.

One of the biggest considerations for security audits is the cost of collecting, storing, and analyzing audit events. If the audit policies are too broad, the volume of audit events that are collected increases, making it more time-consuming and expensive to identify the most important audit events. However, if the audit policies are too narrow, you risk missing important events.

With Windows Server 2012, you can author audit policies by combining claims and resource properties (file tags). This leads to audit policies that are richer, more selective, and easier to manage by reducing the number of potential audit events to those most relevant to your auditing requirements. It enables scenarios that until now were either impossible, or too difficult to implement. The following are examples of such audit policies:

Windows Server 2012: Identity and Access

14

 Audit everyone who does not have a high security clearance and yet tries to access an HBI document. In this example, “high security clearance” is a claim, and “HBI” is a file tag. The audit policy triggers an event when a user who does not have a high security clearance tries to gain access to an HBI document.

 Audit all vendors when they try to access documents related to projects that they are not working on. In this example, the user’s claims include an employment status of “vendor” as well as a list of projects the user is authorized to work on. In addition, each file in the organization to which a vendor could potentially have access was tagged to identify its associated project. The audit policy triggers an event if a vendor tries to access a project file, and none of the projects in the vendor’s claim list matches the file’s project tag.

To view and query audit events you can use familiar tools such as Event Viewer on the local server or Microsoft System Center Operations Manager Audit Collection Service across multiple servers. The Dynamic Access Control API also provides support for integrating DAC audit events into third- party audit software consoles. These tools help you to answer such questions as, “Who is accessing my HBI data?” or “Was there an unauthorized attempt to access sensitive data?”

Figure 5 shows the file-access auditing workflow and the interrelationships between Active Directory, where claim types and resource properties are created; Group Policy, where the audit policies are defined and stored; the file server, where policies and resource properties are applied as file tags; and the user, who is trying to access information on the file server.

Figure 5: Central auditing workflow

Protection

Protecting sensitive information involves reducing risk for the organization. Various regulations, such as HIPAA or Payment Card Industry Data Security Standard (PCI-DSS), require encryption of information, and there are many business reasons to also encrypt sensitive information. However, encryption is expensive and can adversely affect productivity. Therefore, organizations usually have different approaches and priorities for it.

Windows Server 2012: Identity and Access

15

To support this scenario, Windows Server 2012 lets you automatically encrypt sensitive Microsoft Office files based on their classification. This is done through automatic file management of tasks that are running on the server, and that start RMS protection for sensitive Microsoft Office documents a few seconds after the file is identified as being a sensitive file on the file server (continuous file management tasks).

RMS encryption provides another layer of protection for files. If a person with access to a sensitive file inadvertently sends that file out through email, the file is still protected by the RMS encryption. Nearly any user who wants to gain access to the file must first authenticate to an RMS server to receive the decryption key. This process is illustrated in Figure 6.

Figure 6: Classification-based RMS protection

Dynamic Access Control allows sensitive information to be automatically protected using Active Directory RMS:

1. A rule is created to automatically apply RMS protection to nearly any file that contains the word “confidential.”

2. A user creates a file with the word “confidential” in the text and saves it.

3. The RMS Dynamic Access Control classification engine, following rules set in the CAP, discovers the document with the word “confidential,” and initiates RMS protection accordingly.

4. The RMS template and encryption are applied to the document on the file server, and it is classified and encrypted.

Note that support for third-party file formats is available through third-party vendors. Also be aware that if a file is RMS-protected, data management features such as search- or content-based classification are no longer available for that file.

Windows Server 2012: Identity and Access

16

Active Directory Domain Services

Active Directory has been at the center of IT infrastructure for more than 10 years, and its features, adoption, and business value have grown with each new release. Today, most Active Directory infrastructure remains on-premises, but the trend toward cloud computing is creating the need to deploy Active Directory in the cloud as well.

New hybrid infrastructures are emerging, and Active Directory Domain Services must support the needs of new and unique deployment models that include services hosted entirely in the cloud, services that consist of both cloud and on-premises components, and services that remain exclusively on-premises. These new hybrid models further increase the importance of security and compliance, and compound the already complex and time-consuming exercise of ensuring that access to information and services is appropriately audited, and accurately expresses the intent of the organization.

Active Directory Domain Services in Windows Server 2012 addresses these emerging needs with features that help you more quickly and easily deploy domain controllers both on-premises and in the cloud, audit and authorize access to files, and perform administrative tasks at scale—either locally or remotely—through consistent graphical and scripted management interfaces. Active Directory Domain Services in Windows Server 2012 improvements include the following:

 Simpler on-premises deployment, which replaces DCpromo with a new, streamlined domain controller configuration wizard that is integrated with Server Manager and built on Windows PowerShell 3.0.

 More rapid deployment of virtual domain controllers through cloning.

 Better support for public and private cloud implementations through safer virtualization of domain controllers.

 A consistent graphical and scripted management interface that enables you to perform tasks in the Active Directory Administrative Center and automatically generate the syntax required to fully automate the task in Windows PowerShell 3.0.

 Functionality that uses Active Directory to simplify client activations.

 Group Managed Services Accounts for groups of servers such as server clusters that share their identity and service principal name.

Using these features, you can effectively and efficiently deploy and manage Active Directory Domain Services over multiple servers, locally and around the globe.

Simplified deployment

The new Active Directory Domain Services configuration wizard in Windows Server 2012 integrates all the required steps to deploy new domain controllers into a single graphical interface. It requires only one organization-level credential, and can prepare the forest or domain by remotely targeting the appropriate operations master role holders. It conducts extensive prerequisite validation tests that minimize the opportunity for errors that might have otherwise blocked or slowed the installation. The wizard is built on

Windows Server 2012: Identity and Access

17

Windows PowerShell 3.0, and is integrated with Server Manager. It can configure multiple servers and remotely deploy domain controllers, resulting in a deployment experience that is simpler, more consistent, and less time-consuming.

The Active Directory Domain Services configuration wizard includes the following features:

 Adprep integration into the Active Directory Domain Services deployment process. This reduces the time that is required to deploy Active Directory Domain Services, and reduces the chances for errors that might block domain controller promotion.

 Remote execution against multiple servers. This greatly reduces the probability of administrative errors and the overall time required for deployment, especially when you deploy multiple domain controllers across global countries/regions and domains.

 Prerequisite validation. This identifies potential errors before the deployment begins. You can correct error conditions before they occur without the concerns that result from a partially complete upgrade.

 Configuration pages grouped in a sequence that mirror the requirements of the most common promotion options, with related options grouped in fewer wizard pages. This provides better context for making installation choices, and reduces the number of steps and time that is required to complete domain controller installation.

 Options that were specified in the wizard are exported into a Windows PowerShell script. This simplifies the process of automating later Active Directory Domain Services installations through automatically generated Windows PowerShell scripts.

Deployment with cloning

With earlier versions of Windows Server, administrators found that deploying virtualized replica domain controllers was as labor-intensive as deploying physical domain controllers. In theory, this should not be the case because virtualization brings the possibility of cloning domain controllers, instead of performing all deployment steps separately for each one. Domain controllers within the same domain/forest are nearly identical, except for name, IP address, and so on. Therefore virtualization should be fairly easy. With earlier versions of Windows Server, however, deployment still involved many (redundant) steps.

With Windows Server 2012, you can deploy replica virtual domain controllers by “cloning” existing virtual domain controllers. This significantly reduces the number of steps and time involved by eliminating repetitive deployment tasks and also lets you fully deploy additional domain controllers that are authorized and configured for cloning by the Active Directory domain administrator.

Safer virtualization of domain controllers

Active Directory Domain Services has been successfully virtualized for several years, but features present in most hypervisors can invalidate strong assumptions made by the Active Directory replication algorithms—primarily, the assumption that the logical clocks used by domain controllers to determine relative levels of convergence only go forward in time. Windows Server 2012 includes improvements that enable virtual domain controllers to detect when snapshots are applied to a virtual machine or a virtual machine is copied, causing the domain controller clock to go backward in time.

This new functionality is made possible by a virtual domain controller that uses a unique ID exposed by the hypervisor, called the virtual machine GenerationID. The virtual machine GenerationID changes when

Windows Server 2012: Identity and Access

18

the virtual machine experiences an event that affects its position in time. The virtual machine GenerationID is exposed to the virtual machine’s address space within its Basic Input Output System (BIOS), and is made available to its operating system and applications through a Windows Server 2012 driver.

During startup, and before completing any transactions, a Windows Server 2012 virtual domain controller compares the current value of the virtual machine GenerationID against the value that it stored in the directory. A mismatch is interpreted as a “rollback” event, and the domain controller uses safeguards in Active Directory Domain Services that are new to Windows Server 2012. The safeguards enable the virtual domain controller to converge with other domain controllers and also prevent it from creating duplicate security principals.

For Windows Server 2012 virtual domain controllers to gain this extra level of protection, the virtual domain controller must be hosted on a virtual machine GenerationID–aware hypervisor such as Windows Server 2012 Hyper-V.

Windows PowerShell script generation

The Windows PowerShell cmdlets for Active Directory are a set of tools that help you to manipulate and query Active Directory Domain Services by using Windows PowerShell commands, and to create scripts that automate common administrative tasks. The Active Directory Administrative Center uses these cmdlets to query and modify Active Directory Domain Services according to the actions that are performed within the Active Directory Administrative Center UI.

In Windows Server 2012, the Windows PowerShell History viewer in the Active Directory Administrative Center, as shown in the following figure, lets an administrator view the Windows PowerShell commands as they execute in real time. For example, when you create a new fine-grained password policy, the Active Directory Administrative Center displays the equivalent Windows PowerShell commands in the Windows PowerShell History viewer task pane. You can then use those commands to automate the process by creating a Windows PowerShell script.

Figure 7: Windows Server 2012 Windows PowerShell History viewer

By combining scripts with scheduled tasks, you can automate everyday administrative duties that were previously completed manually. Because the cmdlets and required syntax are created for you, very little experience with Windows PowerShell is required. Because the Windows PowerShell commands are the same as the ones executed by the Active Directory Administrative Center, they should replicate their original function exactly.

Windows Server 2012: Identity and Access

19

Active Directory for client activation

Client licensing is an additional labor-intensive task that can be eased with the improved Active Directory functionality in Windows Server 2012. With earlier versions of Windows Server, volume licensing for Windows and Office required Key Management Service (KMS) servers. These entail several drawbacks:

 They require remote procedure call (RPC) network traffic, which some organizations want to disable.

 Additional training is necessary.

 The turnkey solution only covers approximately 90 percent of deployments.

 There is no graphical administration console, so the process is more complex than it needs to be.

 KMS does not support any kind of authentication, because the Microsoft Software License terms prohibit the customer from connecting the KMS server to any external network.

 Access to the service means that anyone can be activated.

This situation is improved in Windows Server 2012 because it helps leverage your existing Active Directory infrastructure to help you activate clients. No additional computers are required, and no RPC is needed. The activation uses Lightweight Directory Access Protocol (LDAP) exclusively and includes support for read-only domain controllers (RODCs).

In this activation process, the only data written back to the directory is what’s required for the installation and service. Activating the initial customer-specific volume license key (CSVLK) requires the following:

 One-time contact with Microsoft Activation Services over the Internet (identical to retail activation).

 A key entered using volume activation server role or the command line.

 Repetition of the activation process for additional forests (by default, up to six times).

Another benefit of Active Directory integration is that the activation object is maintained in the configuration partition. This represents proof-of-purchase, and means that the activated computers can be a member of any domain in the forest. And, perhaps most important, with Active Directory activation integration, all computers that are running Windows 8 will automatically activate. This represents a significant workflow improvement over earlier versions of Windows Server, and is achieved by using additional resources that are provided in Active Directory.

Group-managed service accounts

Managed service accounts (MSAs) were a new type of account introduced in Windows Server 2008 R2 and Windows 7 to enhance the service isolation and manageability of network applications such as Microsoft SQL Server and Exchange Server. They eliminate the need for an administrator to manually administer the service principal name (SPN) and credentials for domain-level service accounts.

Until now, however, this feature has not been available for server groups, such as clusters, that share their identity and service principal name. This creates a problem for IT administrators.

When a client connects to a service hosted on a server farm using network load balancing (NLB) or some other method where all the servers appear to be the same service to the client, authentication protocols supporting mutual authentication, such as Kerberos, cannot be used unless all the instances of the

Windows Server 2012: Identity and Access

20

services use the same principal (which means that they use the same passwords/keys to prove their identity). Service administrators find managing this to be difficult.

When a client connects to a shared service it cannot know in advance which instance it will connect to, so the authentication must succeed regardless of the host. This requires that each instance of the server use the same security principal. Today, services have four principals to choose from, each with their own issue: computer, virtual, managed service, or user.

Computer, MSA, or virtual accounts cannot be shared across multiple systems. This only leaves the option of using a user account for services on server farms. Because user accounts do not have password management, each organization then has to create a solution to update keys for the service in Active Directory, and distribute the keys to all instances of the services. This is expensive and problematic.

By creating a group MSA, services or service administrators do not have to manage password synchronization between service instances. The group MSA supports credential reset, hosts that are kept offline for some time, and seamless management of member host group management for all instances of a service.

 You can deploy single-identity server farms/clusters on Windows Server 2012 to which domain clients can authenticate without knowing which instance of a server farm/cluster they are connecting.

 You can configure services by using Service Control Manager to use a shared domain identity that automatically manages passwords.

 As soon as the group MSA is created, a domain administrator can delegate management of the group MSA to a service administrator.

 You can deploy single identity server farms/clusters on Windows Server 2012 servers running Windows 8 for identities in mixed-mode domains.

DirectAccess and remote access

Windows Server 2012 provides an integrated remote access solution that is easier to deploy and manage when compared to earlier versions that relied on multiple tools and consoles. Employees can gain access to corporate network resources while they work remotely, and IT administrators can manage corporate computers in Active Directory that are located outside the internal network. Windows Server 2012 accomplishes this by integrating two existing remote access technologies: DirectAccess for automatic, transparent connectivity, and traditional virtual private networks (VPNs) for compatibility.

DirectAccess was introduced in Windows 7 and Windows Server 2008 R2 to help remote users to more securely access shared resources, websites, and applications on an internal network without connecting to a VPN. DirectAccess establishes bidirectional connectivity with an organization’s corporate network every time a DirectAccess-enabled computer is connected to the Internet. Users never have to think about connecting to the corporate network, and IT administrators can manage remote computers outside the office, even when the computers are not connected to the VPN. Windows Server 2012 continues to offer

Windows Server 2012: Identity and Access

21

this transparent connection to the corporate network, with improvements around deployment, management, performance, and scalability. Remote access improvements in Windows Server 2012 include:

 Integrated remote access: DirectAccess and VPN can be configured together in the Remote Access Management console by using a single wizard. The new role allows easier migration of Windows 7 Routing and Remote Access service (RRAS) and DirectAccess deployments.

 Cross-premises connectivity. Windows Server 2012 provides a highly cloud-optimized operating system. VPN site-to-site functionality in remote access provides cross-premises connectivity between enterprises and hosting service providers, including Azure.

 Improved management experience: By using the new Remote Access Management console, you can configure, manage, and monitor multiple DirectAccess and VPN remote access servers in a single location. The console provides a dashboard that allows you to view information about server and client activity.

 Simplified deployment: In simple deployments, you can configure DirectAccess without being required to set up a certificate infrastructure. DirectAccess clients can now authenticate themselves by using only Active Directory credentials; no computer certificate is required.

 New deployment scenarios. Remote access in Windows Server 2012 includes integrated deployment for several scenarios that required manual configuration in Windows Server 2008 R2. These include force tunneling (which sends all traffic through the DirectAccess connection), Network Access Protection (NAP) compliance, support for locating the nearest remote access server from DirectAccess clients in different geographical locations, and deploying DirectAccess for only remote management.

 Improved scalability: Remote access in Windows Server 2012 offers several scalability improvements than support more users while providing better performance and lower costs. These include support for network load balancing, better performance in virtualized environments, and underlying platform improvements.

Integrated remote access

With DirectAccess, users who have an Internet connection can more securely access corporate resources, such as email servers, shared folders, or internal websites, with the experience of being easily connected to the corporate network.

DirectAccess transparently connects client computers to the internal network whenever the computer connects to the Internet, even before the user logs on, as shown in the following figure. This transparent, automatic connectivity means that access is provided without additional steps or configuration required by the user.

Windows Server 2012: Identity and Access

22

Figure 8: DirectAccess connection architecture

DirectAccess also lets you easily monitor connections and remotely manage DirectAccess client computers on the Internet.

At the same time, RRAS provides traditional client VPN connectivity for unmanaged client computers, such as computers running client operating systems earlier than Windows 7. In addition, RRAS site-to-site VPN provides connectivity between VPN servers, as shown in the following figure.

Figure 9: VPN connection architecture

The remote access server role in Windows Server 2012 integrates DirectAccess and RRAS VPN. You can configure DirectAccess and VPNs together in the Remote Access Management console by using a single wizard. You also can configure other RRAS features by using the legacy RRAS management console. The new role allows easier migration of Windows 7 RRAS and DirectAccess deployments, and it provides new features and improvements.

Windows Server 2012: Identity and Access

23

Windows Server 2012

Cross-premises connectivity

Windows Server 2012 provides an operating system that is highly optimized for the cloud. VPN site-to-site functionality in remote access provides cross-premises connectivity between enterprises and hosting service providers. Cross-premises connectivity enables organizations to connect to private subnetworks in a hosted cloud network. It also enables connectivity between geographically separate enterprise locations.

With cross-premises connectivity, you can use existing networking equipment to connect to hosting providers by using the industry standard Internet Key Exchange version 2 (IKEv2) and IP security (IPsec) protocol.

Figure 10 demonstrates how these two organizations implement cross-premises deployments by using Windows Server 2012.

Figure 10: Example of a cross-premises deployment

Windows Server 2012: Identity and Access

24

The following steps describe the procedures that Contoso and Woodgrove use for the cross-premises deployment shown in Figure 10:

1. Contoso.com and Woodgrove.com offload some of their enterprise infrastructure in a hosted cloud.

2. The hosting provider provides private clouds for each organization.

3. In the hosted cloud, virtual machines running Windows Server 2012 are configured as remote access servers running site-to-site VPN.

4. In each hosted private cloud, a cluster of two or more remote access servers is deployed to provide continuous availability and failover.

5. Contoso.com has two branch office locations. In each location, a Windows Server 2012 remote access server is deployed to provide a cross-premises connectivity solution to the hosted cloud and between the branch offices.

6. The Contoso.com branch office computers running the unified remote access server role in Windows Server 2012 are also configured as DirectAccess servers in a multisite deployment. DirectAccess clients can access any resource in the Contoso.com public cloud or Contoso.com branch offices from nearly any location on the Internet.

7. Woodgrove.com can use existing routers to connect to the hosted cloud because cross-premises functionality in Windows Server 2012 complies with IKEv2 and IPsec standards.

Improved management experience

By using the new Remote Access Management console, you can configure, manage, and monitor multiple DirectAccess and VPN remote access servers in a single location. The console provides a dashboard that allows you to view information about server and client activity. You can also generate reports for additional, more detailed information. Operations status provides comprehensive monitoring information about specific server components. Event logs and tracing help diagnose specific issues. By using client monitoring, you can see detailed views of connected users and computers, and you can even monitor which resources the clients are accessing. Accounting data can be logged to a local database or a Remote Authentication Dial-In User Service (RADIUS) server.

In addition to the Remote Access Management console, you can use Windows PowerShell command-line interface tools and automated scripts for remote access setup, configuration, management, monitoring, and troubleshooting.

On client computers, users can access the Network Connectivity Assistant application, integrated with Windows Network Connection Manager, to see a concise view of the DirectAccess connection status and links to corporate help resources, diagnostics tools, and troubleshooting information. Users can also enter one-time password (OTP) credentials if OTP authentication for DirectAccess is configured.

Easier deployment

The enhanced installation and configuration design in Windows Server 2012 allows you to set up a working deployment without changing your internal networking infrastructure. In simple deployments, you can configure DirectAccess without setting up a certificate infrastructure. DirectAccess clients can now authenticate themselves by using only Active Directory credentials; no computer certificate is required. In

Windows Server 2012: Identity and Access

25

addition, you can choose to use a self-signed certificate created automatically by DirectAccess for IP-HTTPS and for authentication of the network location server.

To further simplify deployment, DirectAccess in Windows Server 2012 supports access to internal servers that are running IPv4 only. An IPv6 infrastructure is not required for DirectAccess deployment.

Improved deployment scenarios

The remote access server role in Windows Server 2012 includes additional enhancements, including integrated deployment for several scenarios.

 With Windows Server 2012, you can now configure a DirectAccess server with two network adapters at the network edge or behind an edge device, or with a single network adapter running behind a firewall or network address translation device. The ability to use a single adapter removes the requirement to have dedicated public IPv4 addresses for DirectAccess deployment. With this configuration, clients connect to the DirectAccess server by using IP-HTTPS.

 In Windows Server 2012, you can configure remote access servers in a multisite deployment that allows users in dispersed geographical locations to connect to the multisite entry point closest to them. You can distribute and balance traffic across the multisite deployment by using an external global load balancer. To support fault tolerance, redundancy, and scalability, DirectAccess servers can now be deployed in a cluster configuration that uses Windows load balancer or an external hardware load balancer.

 DirectAccess in Windows Server 2012 adds support for two-factor authentication that uses an one-time password (OTP). For two-factor smart card authentication; Windows Server 2012 supports the use of Trusted Platform Module (TPM)-based virtual smart card capabilities that are available in Windows 8. The TPM of client computers can act as a virtual smart card for two-factor authentication, which reduces the overhead and costs incurred in smart card deployment.

 Windows Server 2012 also introduces the ability of computers to join an Active Directory domain and receive domain settings remotely via the Internet. By using this capability, you will find that deployment of new computers in remote offices and provisioning of client settings to DirectAccess clients is easier. You can configure client computers running Windows 8, Windows 7, and Windows Server 2008 R2 as DirectAccess clients. Clients running Windows 8 have access to all DirectAccess features, and they have an improved experience when connecting from behind a proxy server that requires authentication. Clients not running Windows 8 have the following limitations:

o They must download and install the DirectAccess Connectivity Assistant tool.

o They require a computer certificate for authentication.

o In a multisite deployment, they must be configured to always connect through the same entry point.

Scalability improvements

Remote access offers several scalability improvements, including support for more users with better performance and lower costs:

Windows Server 2012: Identity and Access

26

 You can cluster multiple remote access servers for load balancing, continuous availability, and failover. Cluster traffic can be load-balanced by using NLB or a third-party load balancer. Servers can be added to or removed from the cluster with few interruptions to the connections in progress.

 The remote access server role takes advantage of Single Root I/O Virtualization (SR-IOV) for improved I/O performance when running on a virtual machine. In addition, remote access improves the overall scalability of the server host with support for IPsec hardware offload capabilities, available on many server interface cards that perform packet encryption and decryption in hardware.

 Optimization improvements in IP-HTTPS use the encryption that IPsec provides. This optimization, combined with the removal of the Secure Sockets Layer (SSL) encryption requirement, increases scalability and performance.

With the new DirectAccess and remote access enhancements, you can easily provide more secure remote access connections for your users, as well as log reports for monitoring and troubleshooting those connections. The new features in Windows Server 2012 support deployments in dispersed geographical locations, improved scalability with continuous availability, and improved performance in virtualized environments.

Summary

Identity and access control are two areas that require critical attention from IT professionals, particularly when you move to virtualized and private or public cloud environments. Windows Server 2012 makes these tasks easier by offering simple but powerful new and enhanced features to provide intelligent, auditable security; easier deployment and management of Active Directory Domain Services; and secure, always-on connectivity to the corporate resources.

For more information, see the Windows Server 2012 website at: http://www.microsoft.com/windowsserver2012/.

Windows Server 2012: Identity and Access

27

List of charts, tables, and figures

Figure 1: CAP components ………………………………………………………………………………………………………………10

Figure 2: CAP structure ……………………………………………………………………………………………………………………10

Figure 3: Combining multiple policies into policy lists and applying them to resources ……………………..11

Figure 4: Access-denied remediation ……………………………………………………………………………………………….12

Figure 5: Central auditing workflow …………………………………………………………………………………………………14

Figure 6: Classification-based RMS protection ………………………………………………………………………………….15

Figure 7: Windows Server 2012 Windows PowerShell History viewer ………………………………………………18

Figure 8: DirectAccess connection architecture ………………………………………………………………………………..22

Figure 9: VPN connection architecture …………………………………………………………………………………………….22

Figure 10: Example of a cross-premises deployment ………………………………………………………………………..23

Contents

Introduction. xv

PART 1 DATABASE ADMINISTRATION

Chapter 1 SQL Server 2012 Editions and Engine Enhancements 3

SQL Server 2012 Enhancements for Database Administrators. 4

Availability Enhancements. 4

Scalability and Performance Enhancements. 6

Manageability Enhancements. 7

Security Enhancements. 10

Programmability Enhancements. 11

SQL Server 2012 Editions. 12

Enterprise Edition. 12

Standard Edition. 13

Business Intelligence Edition. 14

Specialized Editions. 15

SQL Server 2012 Licensing Overview. 15

Hardware and Software Requirements. 16

Installation, Upgrade, and Migration Strategies. 17

The In-Place Upgrade. 17

Side-by-Side Migration. 19

What do you think of this book? We want to hear from you!

Microsoft is interested in hearing your feedback so we can continually improve our books and learning

resources for you. To participate in a brief online survey, please visit:

microsoft.com/learning/booksurvey

viii Contents

Chapter 2 High-Availability and Disaster-Recovery

Enhancements 21

SQL Server AlwaysOn: A Flexible and Integrated Solution. 21

AlwaysOn Availability Groups . 23

Understanding Concepts and Terminology . 24

Configuring Availability Groups . 29

Monitoring Availability Groups with the Dashboard. 31

Active Secondaries. 32

Read-Only Access to Secondary Replicas . 33

Backups on Secondary . 33

AlwaysOn Failover Cluster Instances. 34

Support for Deploying SQL Server 2012 on Windows Server Core . 36

SQL Server 2012 Prerequisites for Server Core. 37

SQL Server Features Supported on Server Core. 38

SQL Server on Server Core Installation Alternatives . 38

Additional High-Availability and Disaster-Recovery Enhancements. 39

Support for Server Message Block . 39

Database Recovery Advisor. 39

Online Operations. 40

Rolling Upgrade and Patch Management. 40

Chapter 3 Performance and Scalability 41

Columnstore Index Overview. 41

Columnstore Index Fundamentals and Architecture. 42

How Is Data Stored When Using a Columnstore Index?. 42

How Do Columnstore Indexes Significantly Improve the

Speed of Queries? . 44

Columnstore Index Storage Organization. 45

Columnstore Index Support and SQL Server 2012. 46

Columnstore Index Restrictions. 46

Columnstore Index Design Considerations and Loading Data. 47

When to Build a Columnstore Index. 47

When Not to Build a Columnstore Index. 48

Loading New Data. 48

Beyond-Relational Example . 76

FILESTREAM Enhancements. 76

FileTable . 77

FileTable Prerequisites. 78

Creating a FileTable. 80

Managing FileTable. 81

Full-Text Search. 81

Statistical Semantic Search. 82

Configuring Semantic Search. 83

Semantic Search Examples. 85

Spatial Enhancements . 86

Spatial Data Scenarios . 86

Spatial Data Features Supported in SQL Server . 86

Spatial Type Improvements. 87

Additional Spatial Improvements. 89

Extended Events. 90

PART 2 BUSINESS INTELLIGENCE DEVELOPMENT

Chapter 6 Integration Services 93

Developer Experience . 93

Add New Project Dialog Box. 93

General Interface Changes. 95

Getting Started Window. 96

SSIS Toolbox. 97

Shared Connection Managers. 98

Scripting Engine. 99

Expression Indicators. 100

Undo and Redo . 100

Package Sort By Name. 100

Status Indicators. 101

Control Flow. 101

Expression Task. 101

Execute Package Task. 102

Data Flow . 103

Sources and Destinations. 103

Transformations. 106

Column References. 108

Collapsible Grouping. 109

Data Viewer. 110

Change Data Capture Support. 111

CDC Control Flow. 112

CDC Data Flow. 113

Flexible Package Design. 114

Variables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .115

Expressions. 115

Deployment Models. 116

Supported Deployment Models. 116

Project Deployment Model Features. 118

Project Deployment Workflow . 119

Parameters. 122

Project Parameters. 123

Package Parameters. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .124

Parameter Usage. 124

Post-Deployment Parameter Values. 125

Integration Services Catalog. 128

Catalog Creation . 128

Catalog Properties. 129

Environment Objects. 132

Administration. 135

Validation. 135

Package Execution. 135

Logging and Troubleshooting Tools. 137

Security . 139

Package File Format. 139

Miscellaneous Changes. 197

SharePoint Integration . 197

Metadata. 197

Bulk Updates and Export . 197

Transactions . 198

Windows PowerShell. 198

Chapter 9 Analysis Services and PowerPivot 199

Analysis Services. 199

Server Modes. 199

Analysis Services Projects. 201

Tabular Modeling. 203

Multidimensional Model Storage. 215

Server Management . 215

Programmability. 217

PowerPivot for Excel. 218

Installation and Upgrade . 218

Usability. 218

Model Enhancements. 221

DAX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .222

PowerPivot for SharePoint. 224

Installation and Configuration. 224

Management. 224

Chapter 10 Reporting Services 229

New Renderers . 229

Excel 2010 Renderer . 229

Word 2010 Renderer. 230

SharePoint Shared Service Architecture. 230

Feature Support by SharePoint Edition. 230

Shared Service Architecture Benefits. 231

Service Application Configuration . 231

Power View. 232

Data Sources. 233

Power View Design Environment . 234

Data Visualization . 237

Sort Order. 241

Multiple Views . 241

Highlighted Values. 242

Filters. 243

Display Modes . 245

PowerPoint Export. 246

Data Alerts. 246

Data Alert Designer. 246

Alerting Service . 248

Data Alert Manager. 249

Alerting Configuration . 250

Index 251

What do you think of this book? We want to hear from you!

Microsoft is interested in hearing your feedback so we can continually improve our books and learning

resources for you. To participate in a brief online survey, please visit:

microsoft.com/learning/booksurvey

3

CHAP TE R 1

SQL Server 2012 Editions and

Engine Enhancements

SQL Server 2012 is Microsoft’s latest cloud-ready information platform. Organizations can use SQL

Server 2012 to efficiently protect, unlock, and scale the power of their data across the desktop,

mobile device, datacenter, and either a private or public cloud. Building on the success of the SQL

Server 2008 R2 release, SQL Server 2012 has made a strong impact on organizations worldwide with

its significant capabilities. It provides organizations with mission-critical performance and availability,

as well as the potential to unlock breakthrough insights with pervasive data discovery across the

organization. Finally, SQL Server 2012 delivers a variety of hybrid solutions you can choose from. For

example, an organization can develop and deploy applications and database solutions on traditional

nonvirtualized environments, on appliances, and in on-premises private clouds or off-premises public

clouds. Moreover, these solutions can easily integrate with one another, offering a fully integrated

hybrid solution. Figure 1-1 illustrates the Cloud Ready Information Platform ecosystem.

Hybrid IT

Traditional

Nonvirtualized

Private

Cloud

On-premises cloud

Public

Cloud

Off-premises cloud

Nonvirtualized

applications

Pooled (Virtualized)

Elastic

Self-service

Usage-based

Pooled (Virtualized)

Elastic

Self-service

Usage-based

Managed Services

FIGURE 1-1 SQL Server 2012, cloud-ready information platform

To prepare readers for SQL Server 2012, this chapter examines the new SQL Server 2012 features,

capabilities, and editions from a database administrator’s perspective. It also discusses SQL Server

2012 hardware and software requirements and installation strategies.

4 PART 1 Database Administration

SQL Server 2012 Enhancements for Database Administrators

Now more than ever, organizations require a trusted, cost-effective, and scalable database platform

that offers mission-critical confidence, breakthrough insights, and flexible cloud-based offerings.

These organizations face ever-changing business conditions in the global economy and challenges

such as IT budget constraints, the need to stay competitive by obtaining business insights, and

the ability to use the right information at the right time. In addition, organizations must always be

adjusting because new and important trends are regularly changing the way software is developed

and deployed. Some of these new trends include data explosion (enormous increases in data usage),

consumerization IT, big data (large data sets), and private and public cloud deployments.

Microsoft has made major investments in the SQL Server 2012 product as a whole; however, the

new features and breakthrough capabilities that should interest database administrators (DBAs) are

divided in the chapter into the following categories: Availability, Manageability, Programmability,

Scalability and Performance, and Security. The upcoming sections introduce some of the new features

and capabilities; however, other chapters in this book conduct a deeper explanation of the major

technology investments.

Availability Enhancements

A tremendous amount of high-availability enhancements were added to SQL Server 2012, which is

sure to increase both the confidence organizations have in their databases and the maximum uptime

for those databases. SQL Server 2012 continues to deliver database mirroring, log shipping, and replication.

However, it now also offers a new brand of technologies for achieving both high availability

and disaster recovery known as AlwaysOn. Let’s quickly review the new high-availability enhancement

AlwaysOn:

¦ ¦ AlwaysOn Availability Groups For DBAs, AlwaysOn Availability Groups is probably the

most highly anticipated feature related to the Database Engine for DBAs. This new capability

protects databases and allows for multiple databases to fail over as a single unit. Better data

redundancy and protection is achieved because the solution supports up to four secondary

replicas. Of these four secondary replicas, up to two secondaries can be configured as synchronous

secondaries to ensure the copies are up to date. The secondary replicas can reside

within a datacenter for achieving high availability within a site or across datacenters for disaster

recovery. In addition, AlwaysOn Availability Groups provide a higher return on investment

because hardware utilization is increased as the secondaries are active, readable, and can be

leveraged to offload backups, reporting, and ad hoc queries from the primary replica. The

solution is tightly integrated into SQL Server Management Studio, is straightforward to deploy,

and supports either shared storage or local storage.

Figure 1-2 illustrates an organization with a global presence achieving both high availability

and disaster recovery for mission-critical databases using AlwaysOn Availability Groups. In

addition,

the secondary replicas are being used to offload reporting and backups.

CHAPTER 1 SQL Server 2012 Editions and Engine Enhancements 5

70%

50%

25%

15%

70%

50%

25%

15%

Primary

Datacenter

Replica2

Replica3

Reports

Backups

Reports

Backups

Secondary

Datacenter

Replica4

Synchronous Data Movement

Asynchronous Data Movement

A

A

Secondary Replica

Primary Replica

A

A

A

A

Replica1

FIGURE 1-2 AlwaysOn Availability Groups for an organization with a global presence

¦ ¦ AlwaysOn Failover Cluster Instances (FCI) AlwaysOn Failover Cluster Instances provides

superior instance-level protection using Windows Server Failover Clustering and shared

storage.

However, with SQL Server 2012 there are a tremendous number of enhancements to

improve availability and reliability. First, FCI now provides support for multi-subnet failover

clusters. These subnets, where the FCI nodes reside, can be located in the same datacenter

or in geographically dispersed sites. Second, local storage can be leveraged for the TempDB

database.

Third, faster startup and recovery times are achieved after a failover transpires.

Finally, improved cluster health-detection policies can be leveraged, offering a stronger and

more flexible failover.

¦ ¦ Support for Windows Server Core Installing SQL Server 2012 on Windows Server Core

is now supported. Windows Server Core is a scaled-down edition of the Windows operating

system and requires approximately 50 to 60 percent fewer reboots when patching servers.

This translates to greater SQL Server uptime and increased security. Server Core deployment

options using Windows Server 2008 R2 SP1 and higher are required. Chapter 2, “High-Availability

and Disaster-Recovery Options,” discusses deploying SQL Server 2012 on Server

Core, including the features supported.

¦ ¦ Recovery Advisor A new visual timeline has been introduced in SQL Server Management

Studio to simplify the database restore process. As illustrated in Figure 1-3, the scroll bar

beneath

the timeline can be used to specify backups to restore a database to a point in time.

FIGURE 1-3 Recovery Advisor visual timeline

Note For detailed information about the AlwaysOn technologies and other high-availability

enhancements, be sure to read Chapter 2.

Scalability and Performance Enhancements

The SQL Server product group has made sizable investments in improving scalability and

performance

associated with the SQL Server Database Engine. Some of the main enhancements that

allow organizations to improve their SQL Server workloads include the following:

¦ ¦ Columnstore Indexes More and more organizations have a requirement to deliver

breakthrough

and predictable performance on large data sets to stay competitive. SQL

Server 2012 introduces a new in-memory, columnstore index built directly in the relational

engine. Together with advanced query-processing enhancements, these technologies provide

blazing-fast performance and improve queries associated with data warehouse workloads

by 10 to 100 times. In some cases, customers have experienced a 400 percent improvement

in performance. For more information on this new capability for data warehouse workloads,

review Chapter 3, “Blazing-Fast Query Performance with Columnstore Indexes.”

¦ ¦ Partition Support Increased To dramatically boost scalability and performance associated

with large tables and data warehouses, SQL Server 2012 now supports up to 15,000 partitions

per table by default. This is a significant increase from the previous version of SQL Server,

which was limited to 1000 partitions by default. This new expanded support also helps enable

large sliding-window scenarios for data warehouse maintenance.

¦ ¦ Online Index Create, Rebuild, and Drop Many organizations running mission-critical

workloads use online indexing to ensure their business environment does not experience

downtime during routine index maintenance. With SQL Server 2012, indexes containing

varchar(max), nvarchar(max), and varbinary(max) columns can now be created, rebuilt, and

dropped as an online operation. This is vital for organizations that require maximum uptime

and concurrent user activity during index operations.

¦ ¦ Achieve Maximum Scalability with Windows Server 2008 R2 Windows Server 2008 R2

is built to achieve unprecedented workload size, dynamic scalability, and across-the-board

availability and reliability. As a result, SQL Server 2012 can achieve maximum scalability when

running on Windows Server 2008 R2 because it supports up to 256 logical processors and

2 terabytes of memory in a single operating system instance.

Manageability Enhancements

SQL Server deployments are growing more numerous and more common in organizations. This fact

demands that all database administrators be prepared by having the appropriate tools to successfully

manage their SQL Server infrastructure. Recall that the previous releases of SQL Server included

many new features tailored toward manageability. For example, database administrators could easily

leverage Policy Based Management, Resource Governor, Data Collector, Data-tier applications, and

Utility Control Point. Note that the product group responsible for manageability never stopped

investing in manageability. With SQL Server 2012, they unveiled additional investments in SQL Server

tools and monitoring features. The following list articulates the manageability enhancements in SQL

Server 2012:

¦ ¦ SQL Server Management Studio With SQL Server 2012, IntelliSense and Transact-SQL

debugging

have been enhanced to bolster the development experience in SQL Server Management

Studio.

¦ ¦ IntelliSense Enhancements A completion list will now suggest string matches based on

partial words, whereas in the past it typically made recommendations based on the first character.

¦ ¦ A new Insert Snippet menu This new feature is illustrated in Figure 1-4. It offers developers

a categorized list of snippets to choose from to streamline code. The snippet picket tooltip can

be launched by pressing CTRL+K, pressing CTRL+X, or selecting it from the Edit menu.

¦ ¦ Transact-SQL Debugger This feature introduces the potential to debug Transact-SQL

scripts on instances of SQL Server 2005 Service Pack 2 (SP2) or later and enhances breakpoint functionality.

FIGURE 1-4 Leveraging the Transact-SQL code snippet template as a starting point when writing new

Transact-SQL statements in the SQL Server Database Engine Query Editor

¦ ¦ Resource Governor Enhancements Many organizations currently leverage Resource

Governor to gain predictable performance and improve their management of SQL Server

workloads and resources by implementing limits on resource consumption based on incoming

requests. In the past few years, customers have also been requesting additional improvements

to the Resource Governor feature. Customers wanted to increase the maximum number of

resource pools and support large-scale, multitenant database solutions with a higher level of

isolation between workloads. They also wanted predictable chargeback and vertical isolation

of machine resources.

The SQL Server product group responsible for the Resource Governor feature introduced

new capabilities to address the requests of its customers and the SQL Server community. To

begin, support for larger scale multitenancy can now be achieved on a single instance of SQL

Server because the number of resource pools Resource Governor supports increased from 20

to 64. In addition, a maximum cap for CPU usage has been introduced to enable predictable

chargeback

and isolation on the CPU. Finally, resource pools can be affinitized to an individual

schedule or a group of schedules for vertical isolation of machine resources.

A new Dynamic Management View (DMV) called sys.dm_resource_governor_resource_pool_

affinity improves database administrators’ success in tracking resource pool affinity.

Let’s review an example of some of the new Resource Governor features in action. In the

following

example, resource pool Pool25 is altered to be affinitized to six schedulers (8, 12,

13, 14, 15, and 16), and it’s guaranteed a minimum 5 percent of the CPU capacity of those

schedulers.

It can receive no more than 80 percent of the capacity of those schedulers. When

there is contention for CPU bandwidth,

the maximum average CPU bandwidth that will be

allocated is 40 percent.

ALTER RESOURCE POOL Pool25

WITH(

MIN_CPU_PERCENT = 5,

MAX_CPU_PERCENT = 40,

CAP_CPU_PERCENT = 80,

AFFINITY SCHEDULER = (8, 12 TO 16),

MIN_MEMORY_PERCENT = 5,

MAX_MEMORY_PERCENT = 15,

);

¦ ¦ Contained Databases Authentication associated with database portability was a challenge

in the previous versions of SQL Server. This was the result of users in a database being associated

with logins on the source instance of SQL Server. If the database ever moved to another

instance of SQL Server, the risk was that the login might not exist. With the introduction of

contained databases in SQL Server 2012, users are authenticated directly into a user database

without the dependency of logins in the Database Engine. This feature facilitates better

portability of user databases among servers because contained databases have no external

dependencies.

¦ ¦ Tight Integration with SQL Azure A new Deploy Database To SQL Azure wizard, pictured

in Figure 1-5, is integrated in the SQL Server Database Engine to help organizations deploy

an on-premise database to SQL Azure. Furthermore, new scenarios can be enabled with SQL

Azure Data Sync, which is a cloud service that provides bidirectional data synchronization

between databases across the datacenter and cloud.

FIGURE 1-5 Deploying a database to SQL Azure with the Deploy Database Wizard

¦ ¦ Startup Options Relocated Within SQL Server Configuration Manager, a new Startup

Parameters

tab was introduced for better manageability of the parameters required for

startup. A DBA can now easily specify startup parameters compared to previous versions of

SQL Server, which at times was a tedious task. The Startup Parameters tab can be invoked

by right-clicking a SQL Server instance name in SQL Server Configuration Manager and then

selecting Properties.

¦ ¦ Data-Tier Application (DAC) Enhancements SQL Server 2008 R2 introduced the concept

of data-tier applications. A data-tier application is a single unit of deployment containing

all of the database’s schema, dependent objects, and deployment requirements used by

an application.

SQL Server 2012 introduces a few enhancements to DAC. With the new SQL

Server, DAC upgrades are performed in an in-place fashion compared to the previous side-byside

upgrade process we’ve all grown accustomed to over the years. Moreover, DACs can be

deployed, imported and exported more easily across premises and public cloud environments,

such as SQL Azure. Finally, data-tier applications now support many more objects compared to

the previous SQL Server release.

Security Enhancements

It has been approximately 10 years since Microsoft initiated its trustworthy computing initiative. Since

then, SQL Server has had the best track record with the least amount of vulnerabilities and exposures

among the major database players in the industry. The graph shown in Figure 1-6 is from the National

Institute of Standards and Technology (Source: ITIC 2011: SQL Server Delivers Industry-Leading Security).

It shows common vulnerabilities and exposures reported from January 2002 to June 2010.

Oracle

0

50

100

150

200

250

300

350

DB2 MySQL SQL Server

FIGURE 1-6 Common vulnerabilities and exposures reported to NIST from January 2002 to January 2010

With SQL Server 2012, the product continues to expand on this solid foundation to deliver

enhanced

security and compliance within the database platform. For detailed information

of all the security enhancements associated with the Database Engine, review Chapter 4,

”

Security Enhancements.”

For now, here is a snapshot of some of the new enterprise-ready security

capabilities and controls that enable organizations to meet strict compliance policies and regulations:

¦ ¦ User-defined server roles for easier separation of duties

¦ ¦ Audit enhancements to improve compliance and resiliency

¦ ¦ Simplified security management, with a default schema for groups

¦ ¦ Contained Database Authentication, which provides database authentication that uses

self-

contained access information without the need for server logins

¦ ¦ SharePoint and Active Directory security models for higher data security in end-user reports

Programmability Enhancements

There has also been a tremendous investment in SQL Server 2012 regarding programmability.

Specifically,

there is support for “beyond relational” elements such as XML, Spatial, Documents, Digital

Media, Scientific Records, factoids, and other unstructured data types. Why such investments?

Organizations have demanded they be given a way to reduce the costs associated with managing

both structured and nonstructured data. They wanted to simplify the development of applications

over all data, and they wanted the management and search capabilities for all data improved. Take a

minute to review some of the SQL Server 2012 investments that positively impact programmability.

For more information associated with programmability and beyond relational elements, please review

Chapter 5, “Programmability and Beyond-Relational Enhancements.”

¦ ¦ FileTable Applications typically store data within a relational database engine; however, a

myriad of applications also maintain the data in unstructured formats, such as documents,

media files, and XML. Unstructured data usually resides on a file server and not directly in

a relational database such as SQL Server. As you can imagine, it becomes challenging for

organizations

to not only manage their structured and unstructured data across these disparate

systems, but to also keep them in sync. FileTable, a new capability in SQL Server 2012,

addresses these challenges. It builds on FILESTREAM technology that was first introduced

with SQL Server 2008. FileTable offers organizations Windows file namespace support and

application

compatibility with the file data stored in SQL Server. As an added bonus, when

applications

are allowed to integrate storage and data management within SQL Server, fulltext

and semantic search is achievable over unstructured and structured data.

¦ ¦ Statistical Semantic Search By introducing new semantic search functionality, SQL Server

2012 allows organizations to achieve deeper insight into unstructured data stored within the

Database Engine. Three new Transact-SQL rowset functions were introduced to query not only

the words in a document, but also the meaning of the document.

¦ ¦ Full-Text Search Enhancements Full-text search in SQL Server 2012 offers better query

performance and scale. It also introduces property-scoped searching functionality, which

allows

organizations the ability to search properties such as Author and Title without the need

Specialized Editions

Above and beyond the three main editions discussed earlier, SQL Server 2012 continues to deliver

specialized editions for organizations that have a unique set of requirements. Some examples include

the following:

¦ ¦ Developer The Developer edition includes all of the features and functionality found in the

Enterprise edition; however, it is meant strictly for the purpose of development, testing, and

demonstration. Note that you can transition a SQL Server Developer installation directly into

production by upgrading it to SQL Server 2012 Enterprise without reinstallation.

¦ ¦ Web Available at a much more affordable price than the Enterprise and Standard editions,

SQL Server 2012 Web is focused on service providers hosting Internet-facing web services

environments. Unlike the Express edition, this edition doesn’t have database size restrictions,

it supports four processors, and supports up to 64 GB of memory. SQL Server 2012 Web does

not offer the same premium features found in Enterprise and Standard editions, but it still

remains the ideal platform for hosting websites and web applications.

¦ ¦ Express This free edition is the best entry-level alternative for independent software

vendors, nonprofessional developers, and hobbyists building client applications. Individuals

learning about databases or learning how to build client applications will find that this edition

meets all their needs. This edition, in a nutshell, is limited to one processor and 1 GB of

memory, and it can have a maximum database size of 10 GB. Also, Express is integrated with

Microsoft Visual Studio.

Note Review “Features Supported by the Editions of SQL Server 2012” at

http://msdn.microsoft.com/en-us/library/cc645993(v=sql.110).aspx and

http://www.microsoft.com/sqlserver/en/us/future-editions/sql2012-editions.aspx for a

complete

comparison of the key capabilities of the different editions of SQL Server 2012.

SQL Server 2012 Licensing Overview

The licensing models affiliated with SQL Server 2012 have been both simplified to better align to

customer solutions and optimized for virtualization and cloud deployments. Organizations should

process knowledge of the information that follows. With SQL Server 2012, the licensing for computing

power is core-based and the Business Intelligence and Standard editions are available under

the Server + Client Access License (CAL) model. In addition, organizations can save on cloud-based

computing costs by licensing individual database virtual machines. Because each customer environment

is unique, we will not have the opportunity to provide an overview of how the license changes

affect your environment. For more information on the licensing changes and how they impact your

organization,

please contact your Microsoft representative or partner.

Software Component

Requirements

Windows PowerShell

Windows PowerShell 2.0

SQL Server support tools and

software

SQL Server 2012 – SQL Server Native Client

SQL Server 2012 – SQL Server Setup Support Files

Minimum: Windows Installer 4.5

Internet Explorer

Minimum: Windows Internet Explorer 7 or later version

Virtualization

Windows Server 2008 SP2 running Hyper-V role

or

Windows Server 2008 R2 SP1 running Hyper-V role

Note The server hardware has supported both 32-bit and 64-bit processors for several

years; however, Windows Server 2008 R2 is 64-bit only. Take this into serious consideration

when planning SQL Server 2012 deployments.

Installation, Upgrade, and Migration Strategies

Like its predecessors, SQL Server 2012 is available in both 32-bit and 64-bit editions. Both can be

installed

with either the SQL Server Installation Wizard through a command prompt or with Sysprep

for automated deployments with minimal administrator intervention. As mentioned earlier in the

chapter, SQL Server 2012 can now be installed on the Server Core, which is an installation option of

Windows Server 2008 R2 SP1 or later. Finally, database administrators also have the option to upgrade

an existing installation of SQL Server or conduct a side-by-side migration when installing SQL Server

2012. The following sections elaborate on the different strategies.

The In-Place Upgrade

An in-place upgrade is the upgrade of an existing SQL Server installation to SQL Server 2012.

When an in-place upgrade is conducted, the SQL Server 2012 setup program replaces the previous

SQL Server binaries with the new SQL Server 2012 binaries on the existing machine. SQL Server data is

automatically converted from the previous version to SQL Server 2012. This means data does not have

to be copied or migrated. In the example in Figure 1-8, a database administrator is conducting an

in-place upgrade on a SQL Server 2008 instance running on Server 1. When the upgrade is complete,

Server 1 still exists, but the SQL Server 2008 instance and all of its data is upgraded to SQL Server 2012.

Note SQL Server 2005 with SP4, SQL Server 2008 with SP2, and SQL Server 2008 R2 with

SP1 are all supported for an in-place upgrade to SQL Server 2012. Unfortunately, earlier

versions such as SQL Server 2000, SQL Server 7.0, and SQL Server 6.5 cannot be upgraded

to SQL Server 2012.

Side-by-Side Migration Pros and Cons

The greatest benefit of a side-by-side migration over an in-place upgrade is the opportunity to build

out a new database infrastructure on SQL Server 2012 and avoid potential migration issues that can

occur with an in-place upgrade. The side-by-side migration also provides more granular control

over the upgrade process because you can migrate databases and components independent of one

another. In addition, the legacy instance remains online during the migration process. All of these

advantages result in a more powerful server. Moreover, when two instances are running in parallel,

additional testing and verification can be conducted. Performing a rollback is also easy if a problem

arises during the migration.

However, there are disadvantages to the side-by-side strategy. Additional hardware might need to

be purchased. Applications might also need to be directed to the new SQL Server 2012 instance, and

it might not be a best practice for very large databases because of the duplicate amount of storage

required during the migration process.

SQL Server 2012 High-Level, Side-by-Side Strategy

The high-level, side-by-side migration strategy for upgrading to SQL Server 2012 consists of the following

steps:

1. Ensure the instance of SQL Server you plan to migrate meets the hardware and software

requirements for SQL Server 2012.

2. Review the deprecated and discontinued features in SQL Server 2012 by referring to

“Deprecated

Database Engine Features in SQL Server 2012″ at http://technet.microsoft.com

/en-us/library/ms143729(v=sql.110).aspx.

3. Although a legacy instance will not be upgraded to SQL Server 2012, it is still beneficial to

run the SQL Server 2012 Upgrade Advisor to ensure the data being migrated to the new SQL

Server 2012 is supported and there is no possibility of a break occurring after migration.

4. Procure the hardware, and install your operating system of choice. Windows Server 2012 is

recommended.

5. Install the SQL Server 2012 prerequisites and desired components.

6. Migrate objects from the legacy SQL Server to the new SQL Server 2012 database platform.

7. Point applications to the new SQL Server 2012 database platform.

8. Decommission legacy servers after the migration is complete.

21

CHAP TE R 2

High-Availability and Disaster-

Recovery Enhancements

Microsoft SQL Server 2012 delivers significant enhancements to well-known, critical capabilities

such as high availability (HA) and disaster recovery. These enhancements promise to assist

organizations

in achieving their highest level of confidence to date in their server environments.

Server

Core support,

breakthrough features such as AlwaysOn Availability Groups and active

secondaries,

and key improvements

to features such as failover clustering are improvements that

provide organizations a range of accommodating options to achieve maximum application availability

and data protection for SQL Server instances and databases within a datacenter and across

datacenters.

This chapter’s goal is to bring readers up to date with the high-availability and disaster-recovery

capabilities that are fully integrated into SQL Server 2012 as a result of Microsoft’s heavy investment

in AlwaysOn.

SQL Server AlwaysOn: A Flexible and Integrated Solution

Every organization’s success and service reputation is built on ensuring that its data is always

accessible

and protected. In the IT world, this means delivering a product that achieves the highest

level of availability and disaster recovery while minimizing data loss and downtime. With the

previous

versions of SQL Server, organizations achieved high availability and disaster recovery by

using technologies

such as failover clustering, database mirroring, log shipping, and peer-to-peer

replication.

Although organizations achieved great success with these solutions, they were tasked with

combining these native SQL Server technologies to achieve their business requirements related to

their Recovery Point Objective (RPO) and Recovery Time Objective (RTO).

Figure 2-1 illustrates a common high-availability and disaster-recovery strategy used by

organizations

with the previous versions of SQL Server. This strategy includes failover clustering to

protect SQL Server instances within each datacenter, combined with asynchronous database mirroring

to provide disaster-recovery capabilities for mission-critical databases.

22 PART 1 Database Administration

Primary

Datacenter

Asynchronous Data Movement

with Database Mirroring

Secondary

Datacenter

SQL Server 2008 R2

Failover Cluster

SQL Server 2008 R2

Failover Cluster

FIGURE 2-1 Achieving high availability and disaster recovery by combining failover clustering with database

mirroring

in SQL Server 2008 R2

Likewise, for organizations that either required more than one secondary datacenter or that did

not have shared storage, their high-availability and disaster-recovery deployment incorporated

synchronous

database mirroring with a witness within the primary datacenter combined with log

shipping for moving data to multiple locations. This deployment strategy is illustrated in Figure 2-2.

Primary

Datacenter

Log Shipping

Disaster Recover

Datacenter 1

SQL Server 2008 R2

SQL Server 2008 R2

Database Mirroring

Witness

Disaster Recover

Datacenter 2

SQL Server 2008 R2

Synchronous Data Movement

with Database Mirroring

Log Shipping

Log Shipping

FIGURE 2-2 Achieving high availability and disaster recovery with database mirroring combined with log shipping

in SQL Server 2008 R2

CHAPTER 2 High-Availability and Disaster-Recovery Enhancements 23

Figures 2-1 and 2-2 both reveal successful solutions for achieving high availability and disaster

recovery. However, these solutions had some limitations, which warranted making changes. In addition,

with organizations constantly evolving, it was only a matter of time until they voiced their own

concerns and sent out a request for more options and changes.

One concern for many organizations was regarding database mirroring. Database mirroring is

a great way to protect databases; however, the solution is a one-to-one mapping, making multiple

secondaries unattainable. When confronted with this situation, many organizations reverted to log

shipping as a replacement for database mirroring because it supports multiple secondaries. Unfortunately,

organizations encountered limitations with log shipping because it did not provide zero data

loss or automatic failover capability. Concerns were also experienced by organizations working with

failover clustering because they felt that their shared-storage devices, such as a storage area network

(SAN), could be a single point of failure. Similarly, many organizations thought that from a cost

perspective their investments were not being used to their fullest potential. For example, the passive

servers in many of these solutions were idle. Finally, many organizations wanted to offload reporting

and maintenance tasks from the primary database servers, which was not an easy task to achieve.

SQL Server has evolved to answer many of these concerns, and this includes an integrated solution

called AlwaysOn. AlwaysOn Availability Groups and AlwaysOn Failover Cluster Instances are new

features,

introduced in SQL Server 2012, that are rich with options and promise the highest level of

availability and disaster recovery to its customers. At a high level, AlwaysOn Availability Groups is

used for database protection and offers multidatabase failover, multiple secondaries, active secondaries,

and integrated HA management. On the other hand, AlwaysOn Failover Cluster Instances is a

feature tailored to instance-level protection, multisite clustering, and consolidation, while consistently

providing flexible failover polices and improved diagnostics.

AlwaysOn Availability Groups

AlwaysOn Availability Groups provides an enterprise-level alternative to database mirroring, and it

gives organizations the ability to automatically or manually fail over a group of databases as a single

unit, with support for up to four secondaries. The solution provides zero-data-loss protection and is

flexible. It can be deployed on local storage or shared storage, and it supports both synchronous and

asynchronous data movement. The application failover is very fast, it supports automatic page repair,

and the secondary replicas can be leveraged to offload reporting and a number of maintenance

tasks, such as backups.

Take a look at Figure 2-3, which illustrates an AlwaysOn Availability Groups deployment strategy

that includes one primary replica and three secondary replicas.

70%

50%

25%

15%

70%

50%

25%

15%

Primary

Datacenter

Replica2

Replica3

Reports

Reports Backups

Backups

Secondary

Datacenter

Synchronous Data Movement

Asynchronous Data Movement

Replica4

A

A

A

A

A

Secondary Replica

Primary Replica

A

SQL Server

SQL Server

SQL Server

SQL Server

Replica1

FIGURE 2-3 Achieving high availability and disaster recovery with AlwaysOn Availability Groups

In this figure, synchronous data movement is used to provide high availability within the primary

datacenter and asynchronous data movement is used to provide disaster recovery. Moreover, secondary

replica 3 and replica 4 are employed to offload reports and backups from the primary replica.

It is now time to take a deeper dive into AlwaysOn Availability Groups through a review of the new

concepts and terminology associated with this breakthrough capability.

Understanding Concepts and Terminology

Availability groups are built on top of Windows Failover Clustering and support both shared and

nonshared

storage. Depending on an organization’s RPO and RTO requirements, availability groups

can use either an asynchronous-commit availability mode or a synchronous-commit availability

mode to move data between primary and secondary replicas. Availability groups include built-in

compression

and encryption as well as support for file-stream replication and auto page repair.

Failover between replicas is either automatic or manual.

When deploying AlwaysOn Availability Groups, your first step is to deploy a Windows Failover

Cluster. This is completed by using the Failover Cluster Manager Snap-in within Windows Server 2008

R2. Once the Windows Failover Cluster is formed, the remainder of the Availability Groups configurations

is completed in SQL Server Management Studio. When you use the Availability Groups wizards

to configure your availability groups, SQL Server Management Studio automatically creates the

appropriate

services, applications, and resources in Failover Cluster Manager; hence, the deployment

is much easier for database administrators who are not familiar with failover clustering.

Now that the fundamentals of the AlwaysOn Availability Groups have been laid down, the most

natural question that follows is how an organization’s operations are enhanced with this feature.

Unlike

database mirroring, which supports only one secondary, AlwaysOn Availability Groups supports

one primary replica and up to four secondary replicas. Availability groups can also contain more

than one availability database. Equally appealing is the fact you can host more than one availability

group within an implementation. As a result, it is possible to group databases with application

dependencies

together within an availability group and have all the availability databases seamlessly

fail over as a single cohesive unit as depicted in Figure 2-4.

FIGURE 2-4 Dedicated availability groups for Finance and HR availability databases

In addition, as shown in Figure 2-4, there is one primary replica and two secondary replicas with

two availability groups. One of these availability groups is called Finance, and it includes all the

Finance databases; the other availability group is called HR, and it includes all the Human Resources

databases. The Finance availability group can fail over independently of the HR availability group,

and unlike database mirroring, all availability databases within an availability group fail over as a

single unit. Moreover, organizations can improve their IT efficiency, increase performance, and reduce

total cost of ownership with better resource utilization of secondary/passive hardware because these

secondary replicas can be leveraged for backups and read-only operations such as reporting and

maintenance. This is covered in the “Active Secondaries” section later in this chapter.

Now that you have been introduced to some of the benefits AlwaysOn Availability Groups offers

for an organization, let’s take the time to get a stronger understanding of the AlwaysOn Availability

Groups concepts and how this new capability operates. The concepts covered include the following:

¦ ¦ Availability replica roles

¦ ¦ Data synchronization modes

¦ ¦ Failover modes

¦ ¦ Connection mode in secondaries

¦ ¦ Availability group listeners

Availability Replica Roles

Each AlwaysOn availability group comprises a set of two or more failover partners that are referred to

as availability replicas. The availability replicas can consist of either a primary role or a secondary role.

Note that there can be a maximum of four secondaries, and of these four secondaries only a maximum

of two secondaries can be configured to use the synchronous-commit availability mode.

The roles affiliated with the availability replicas in AlwaysOn Availability Groups follow the same

principles as the legendary Sith rule of two doctrines in the Star Wars saga. In Star Wars, there can be

only two Siths at one time, a master and an apprentice. Similarly, a SQL Server instance in an availability

group can be only a primary replica or a secondary replica. At no time can it be both because the

role swapping is controlled by Windows Server Failover Cluster (WSFC).

Each of the SQL Server instances in the availability group is hosted on either a SQL Server failover

cluster instance (FCI) or a stand-alone instance of SQL Server 2012. Each of these instances resides on

different nodes of a WSFC. WSFC is typically used for providing high availability and disaster recovery

for well-known Microsoft products. As such, availability groups use WSFC as the underlying mechanism

to provide internode health detection, failover coordination, primary health detection, and

distributed change notifications for the solution.

Each availability replica hosts a copy of the availability databases in the availability group. Because

there are multiple copies of the databases being hosted on each availability replica, there isn’t a

prerequisite for using shared storage like there was in the past when deploying traditional SQL Server

failover clusters. On the flip side, when using nonshared storage, an organization must realize that

storage requirements increase as the number of replicas it plans on hosting increases.

Data Synchronization Modes

To move data from the primary replica to the secondary replica, each mode uses either

synchronous-

commit availability mode or asynchronous-commit availability mode. Give

consideration

to the following items when selecting either option:

¦ ¦ When you use the synchronous-commit mode, a transaction is committed on both replicas

to guarantee transactional consistency. This, however, means increased latency. As such, this

option might not be appropriate for partners who don’t share a high-speed network or who

reside in different geographical locations.

¦ ¦ The asynchronous-commit mode commits transactions between partners without waiting for

the partner to write the log to disk. This maximizes performance between the application and

the primary replica and is well suited for disaster-recovery solutions.

Availability Groups Failover Modes

When configuring AlwaysOn availability groups, database administrators can choose from two

failover modes when swapping roles from the primary to the secondary replicas. For administrators

who are familiar with database mirroring, you’ll see that to obtain high availability and disaster

recovery the failover modes are very similar to the modes in database mirroring. These are the two

AlwaysOn failover modes available when using the New Availability Group Wizard:

¦ ¦ Automatic Failover This replica uses synchronous-commit availability mode, and it supports

both automatic failover and manual failover between the replica partners. A maximum of two

failover replica partners are supported when choosing automatic failover.

¦ ¦ Manual Failover This replica uses synchronous or asynchronous commit availability mode

and supports only manual failovers between the replica partners.

Connection Mode in Secondaries

As indicated earlier, each of the secondaries can be configured to support read-only access for

reporting

or other maintenance tasks, such as backups. During the final configuration stage of

AlwaysOn

availability groups, database administrators decide on the connection mode for the

secondary

replicas. There are three connection modes available:

¦ ¦ Disallow connections In the secondary role, this availability replica does not allow any connections.

¦ ¦ Allow only read-intent connections In the secondary role, this availability replica allows

only read-intent connections.

¦ ¦ Allow all connections In the secondary role, this availability replica allows all connections

for read access, including connections running with older clients.

Availability Group Listeners

The availability group listener provides a way of connecting to databases within an availability group

via a virtual network name that is bound to the primary replica. Applications can specify the network

name affiliated with the availability group listener in connection strings. After the availability group

fails over from the primary replica to a secondary replica, the network name directs connections to

the new primary replica. The availability group listener concept is similar to a Virtual SQL Server Name

when using failover clustering; however, with an availability group listener, there is a virtual network

name for each availability group, whereas with SQL Server failover clustering, there is one virtual

network name for the instance.

You can specify your availability group listener preferences when using the Create A New

Availability

Group Wizard in SQL Server Management Studio, or you can manually create or modify

an availability group listener after the availability group is created. Alternatively, you can use

Transact-

SQL to create or modify the listener too. Notice in Figure 2-5 that each availability group

listener requires a DNS name, an IP address, and a port such as 1433. Once the availability group

listener is created, a server name and an IP address cluster resource are automatically created within

Failover Cluster Manager. This is certainly a testimony to the availability group’s flexibility and tight

integration with SQL Server, because the majority of the configurations are done within SQL Server.

FIGURE 2-5 Specifying availability group listener properties

Be aware that there is a one-to-one mapping between availability group listeners and availability

groups. This means you can create one availability group listener for each availability group. However,

if more than one availability group exists within a replica, you can have more than one availability

group listener. For example, there are two availability groups shown in Figure 2-6: one is for

the Finance

availability databases, and the other is for the Accounting availability databases. Each

availability

group has its own availability group listener that clients and applications connect to.

FIGURE 2-6 Illustrating two availability group listeners within a replica

Configuring Availability Groups

When creating a new availability group, a database administrator needs to specify an availability

group name, such as AvailablityGroupFinance, and then select one or more databases to be part of

in the availability group. The next step involves first specifying one or more instances of SQL Server

to host secondary availability replicas, and then specifying your availability group listener preference.

The final step is selecting the data-synchronization preference and connection mode for the secondary

replicas. These configurations are conducted with the New Availability Group Wizard or with

Transact-SQL PowerShell scripts.

Prerequisites

To deploy AlwaysOn Availability Groups, the following prerequisites must be met:

¦ ¦ All computers running SQL Server, including the servers that will reside in the disaster-recovery

site, must reside in the same Windows-based domain.

¦ ¦ All SQL Server computers must participate in a single Windows Server failover cluster even if

the servers reside in multiple sites.

¦ ¦ All servers must partake in a Windows Server failover cluster.

¦ ¦ AlwaysOn Availability Groups must be enabled on each server.

¦ ¦ All the databases must be in full recovery mode.

¦ ¦ A full backup must be conducted on all databases before deployment.

¦ ¦ The server cannot host the Active Directory Domain Services role.

Deployment Examples

Figure 2-7 shows the Specify Replicas page you see when using the New Availability Group Wizard.

In this example, there are three SQL Server instances in the availability group called Finance:

SQL01\Instance01, SQL02\Instance01, and SQL03\Instance01. SQL01\Instance01 is configured as the

Primary replica, whereas SQL02\Instance01 and SQL03\Instance01 are configured as secondaries.

SQL01\Instance01 and SQL02\Instance01 support automatic failover with synchronous data movement,

whereas SQL-03\Instance01 uses asynchronous-commit availability mode and supports only

a forced failover. Finally, SQL01\Instance01 does not allow read-only connections to the secondary,

whereas SQL02\Instance01 and SQL03\Instance01 allow read-intent connections to the secondary. In

addition, for this example, SQL01\Instance01 and SQL02\Instance01 reside in a primary datacenter for

high availability within a site, and SQL03\Instance01 resides in the disaster recovery datacenter and

will be brought online manually in the event the primary datacenter becomes unavailable.

FIGURE 2-7 Specifying the SQL Server instances in the availability group

One thing becomes vividly clear from Figure 2-7 and the preceding example: there are many

different

deployment configurations available to satisfy any organization’s high-availability and

disaster-recovery requirements. See Figure 2-8 for the following additional deployment alternatives:

¦ ¦ Nonshared storage, local, regional, and geo target

¦ ¦ Multisite cluster with another cluster as the disaster recovery (DR) target

¦ ¦ Three-node cluster with similar DR target

¦ ¦ Secondary targets for backup, reporting, and DR

Nonshared Storage, Local,

Regional, and Geo Target

A

A

A

A

A

A

A A

A

A

A

A A A

A

A

A

A A

A

A

A A A

Multisite Cluster with Another

Cluster as Disaster Recovery Target

Three-Node Cluster with Similar

Disaster Recovery Target

Secondary Targets for Backup,

Reporting, and Disaster Recovery

FIGURE 2-8 Additional AlwaysOn deployment alternatives

Monitoring Availability Groups with the Dashboard

Administrators have an opportunity to leverage a new and remarkably intuitive manageability

dashboard in in SQL Server 2012 to monitor availability groups. The dashboard, shown in Figure 2-9,

reports the health and status associated with each instance and availability database in the availability

group. Moreover, the dashboard displays the specific replica role of each instance and provides synchronization

status. If there is an issue or if more information on a specific event is required, a database

administrator can click the availability group state, server instance name, or health status hyperlinks

for additional information. The dashboard is launched by right-clicking the Availability Groups

folder in the Object Explorer in SQL Server Management Studio and selecting Show Dashboard.

FIGURE 2-9 Monitoring availability groups with the new Availability Group dashboard

Active Secondaries

As indicated earlier, many organizations communicated to the SQL Server team their need to improve

IT efficiency by optimizing their existing hardware investments. Specifically, organizations hoped their

production systems for passive workloads could be used in some other capacity instead of remaining

in an idle state. These same organizations also wanted reporting and maintenance tasks offloaded

from production servers because these tasks negatively affected production workloads. With SQL

Server 2012, organizations can leverage the AlwaysOn Availability Group capability to configure

secondary replicas, also referred to as active secondaries, to provide read-only access to databases

affiliated with an availability group.

All read-only operations on the secondary replicas are supported by row versioning and are

automatically

mapped to snapshot isolation transaction level, which eliminates reader/writer

contention.

In addition, the data in the secondary replicas is near real time. In many circumstances,

data latency between the primary and secondary databases should be within seconds. Note that the

latency of log synchronization affects data freshness.

For organizations, active secondaries are synonymous with performance optimization on a primary

replica and increases to overall IT efficiency and hardware utilization.

Read-Only Access to Secondary Replicas

Recall that when configuring the connection mode for secondary replicas, you can choose Disallow

Connections, Allow Only Read-Intent Connections, and Allow All Connections. The Allow Only

Read-

Intent Connections and Allow All Connections options both provide read-only access to

secondary

replicas. The Disallow Connections alternative does not allow read-only access as implied

by its name.

Now let’s look at the major differences between Allow Only Read-Intent Connections and Allow

All Connections. The Allow Only Read-Intent Connections option allows connections to the databases

in the secondary replica when the Application Intent Connection property is set to Read-only in the

SQL Server native client. When using the Allow All Connections settings, all client connections are

allowed independent of the Application Intent property. What is the Application Intent property in

the connection string? The Application Intent property declares the application workload type when

connecting

to a server. The possible values are Read-only and Read Write. Commands that try to

create

or modify data on the secondary replica will fail.

Backups on Secondary

Backups of availability databases participating in availability groups can be conducted on any of the

replicas. Although backups are still supported on the primary replica, log backups can be conducted

on any of the secondaries. Note that this is independent of the replication commit mode being

used.synchronous-commit or asynchronous-commit. Log backups completed on all replicas form a

single log chain, as shown in Figure 2-10.

Primary Replica Backups

Backups Supported

If Desired

Secondary Replica 2 Backups

Step 2: Log Backup

LSN 41-60

Secondary Replica 1 Backups

Step 1: Log Backup

LSN 21-40

Secondary Replica 3 Backups

Step 3: Log Backup

LSN 61-80

FIGURE 2-10 Forming a single log chain by backing up the transaction logs on multiple secondary replicas

As a result, the transaction log backups do not all have to be performed on the same replica.

This in no way means that serious thought should not be given to the location of your backups. It is

recommended that you store all backups in a central location because all transaction log backups are

required to perform a restore in the event of a disaster. Therefore, if a server is no longer available

and it contained the backups, you will be negatively affected. In the event of a failure, use the new

Database Recovery Advisor Wizard; it provides many benefits when conducting restores. For example,

if you are performing backups on different secondaries, the wizard generates a visual image of a

chronological timeline by stitching together all of the log files based on the Log Sequence Number

(LSN).

AlwaysOn Failover Cluster Instances

You’ve seen the results of the development efforts in engineering the new AlwaysOn Availability

Groups capability for high availability and disaster recovery, and the creation of active secondaries.

Now you’ll explore the significant enhancements to traditional capabilities such as SQL Server failover

clustering that leverage shared storage. The following list itemizes some of the improvements that

will appeal to database administrators looking to gain high availability for their SQL Server instances.

Specifically, this section discusses the following features:

¦ ¦ Multisubnet Clustering This feature provides a disaster-recovery solution in addition to

high availability with new support for multisubnet failover clustering.

¦ ¦ Support for TempDB on Local Disk Another storage-level enhancement with failover

clustering is associated with TempDB. TempDB no longer has to reside on shared storage as

it did in previous versions of SQL Server. It is now supported on local disks, which results in

many practical benefits for organizations. For example, you can now offload TempDB I/O from

shared-storage devices (SSD) like a SAN and leverage fast SSD storage locally within the server

nodes to optimize TempDB workloads, which are typically random I/O.

¦ ¦ Flexible Failover Policy SQL Server 2012 introduces improved failure detection for the SQL

Server failover cluster instance by adding failure condition-level properties that allow you to

configure a more flexible failover policy.

Note AlwaysOn failover cluster instances can be combined with availability groups to offer

maximum SQL Server instance and database protection.

With the release of Windows Server 2008, new functionality enabled cluster nodes to be

connected

over different subnets without the need for a stretch virtual local area network (VLAN)

across networks. The nodes could reside on different subnets within a datacenter or in another

geographical

location, such as a disaster recovery site. This concept is commonly referred to as

multisite

clustering, multisubnet clustering, or stretch clustering. Unfortunately, the previous versions

of SQL Server could not take advantage of this Windows failover clustering feature. Organizations

that wanted to create either a multisite or multisubnet SQL Server failover cluster still had to create

a stretch VLAN to expose a single IP address for failover across sites. This was a complex and challenging

task for many organizations. This is no longer the case because SQL Server 2012 supports

multisubnet

and multisite clustering out of the box; therefore, the need for implementing stretch

VLAN technology no longer exists.

Figure 2-11 illustrates an example of a SQL Server multisubnet failover cluster between two

subnets

spanning two sites. Notice how each node affiliated with the multisubnet failover cluster

resides on a different subnet. Node 1 is located in Site 1 and resides on the 192.168.115.0/24 subnet,

whereas Node 2 is located in Site 2 and resides on the 192.168.116.0/24 subnet. The DNS IP address

associated with the virtual network name of the SQL Server cluster is automatically updated when a

failover from one subnet to another subnet occurs.

SQL Server 2012

Node 1

192.168.115.0/24 Subnet

SQL Server 2012

Node 2

192.168.116.0/24 Subnet

SQL Server Failover Cluster Instance

Node 1 – 192.168.115.5

Node 2 – 192.168.116.5

Data Replication Between Storage Systems

Failover

Site 1 Site 2

FIGURE 2-11 A multisubnet failover cluster instance example

For clients and applications to connect to the SQL Server failover cluster, they need two IP

addresses

registered to the SQL Server failover cluster resource name in WSFC. For example, imagine

your server name is SQLFCI01 and the IP addresses are 192.168.115.5 and 192.168.116.5. WSFC automatically

controls the failover and brings the appropriate IP address online depending on the node

that currently owns the SQL Server resource. Again, if Node 1 is affiliated with the 192.168.115.0/24

subnet and owns the SQL Server failover cluster, the IP address resource 192.168.115.6 is brought

online

as shown in Figure 2-12. Similarly, if a failover occurs and Node 2 owns the SQL Server

resource,

IP address resource 192.168.115.6 is taken offline and the IP address resource 192.168.116.6

is brought online.

FIGURE 2-12 Multiple IP addresses affiliated with a multisubnet failover cluster instance

Because there are multiple IP addresses affiliated with the SQL Server failover cluster instance

virtual name, the online address changes automatically when there is a failover. In addition,

Windows

failover cluster issues a DNS update immediately after the network name resource name

comes online.

The IP address change in DNS might not take effect on clients because of cache

settings; therefore, it is recommended that you minimize the client downtime by configuring the

HostRecordTTL

in DNS to 60 seconds. Consult with your DNS administrator before making any DNS

changes, because additional load requests could occur when tuning the TTL time with a host record.

Support for Deploying SQL Server 2012 on Windows Server

Core

Windows Server Core was originally introduced with Windows Server 2008 and saw significant

enhancements

with the release of Windows Server 2008 R2. For those who are unfamiliar with Server

Core, it is an installation option for the Windows Server 2008 and Windows Server 2008 R2 operating

systems. Because Server Core is a minimal deployment of Windows, it is much more secure because

its attack surface is greatly reduced. Server Core does not include a traditional Windows graphical

interface and, therefore, is managed via a command prompt or by remote administration tools.

Unfortunately, previous versions of SQL Server did not support the Server Core operating system,

but that has all changed. For the first time, Microsoft SQL Server 2012 supports Server Core installations

for organizations running Server Core based on Windows Server 2008 R2 with Service Pack 1 or

later.

Why is Server Core so important to SQL Server, and how does it positively affect availability?

When you are running SQL Server 2012 on Server Core, operating-system patching is drastically

reduced.by up to 60 percent. This translates to higher availability and a reduction in planned

downtime for any organization’s mission-critical databases and workloads. In addition, surface-area

attacks are greatly reduced and overall security of the database platform is strengthened, which again

translates to maximum availability and data protection.

When first introduced, Server Core required the use and knowledge of command-line syntax to

manage it. Most IT professionals at this time were accustomed to using a graphical user interface

(GUI) to manage and configure Windows, so they had a difficult time embracing Server Core. This

affected its popularity and, ultimately, its implementation. To ease these challenges, Microsoft created

SCONFIG, which is an out-of-the-box utility that was introduced with the release of Windows Server

2008 R2 to dramatically ease server configurations. To navigate through the SCONFIG options, you

only need to type one or more numbers to configure server properties, as displayed in Figure 2-13.

FIGURE 2-13 SCONFIG utility for configuring server properties in Server Core

The following sections articulate the SQL Server 2012 prerequisites for Server Core, SQL Server

features supported on Server Core, and the installation alternatives.

SQL Server 2012 Prerequisites for Server Core

Organizations installing SQL Server 2012 on Windows Server 2008 R2 Server Core must meet the

following

operating system, features, and components prerequisites.

The operating system requirements are as follows:

¦ ¦ Windows Server 2008 R2 SP1 64-bit x64 Data Center Server Core

¦ ¦ Windows Server 2008 R2 SP1 64-bit x64 Enterprise Server Core

¦ ¦ Windows Server 2008 R2 SP1 64-bit x64 Standard Server Core

¦ ¦ Windows Server 2008 R2 SP1 64-bit x64 Web Server Core

Here is the list of features and components:

¦ ¦ .NET Framework 2.0 SP2

¦ ¦ .NET Framework 3.5 SP1 Full Profile

¦ ¦ .NET Framework 4 Server Core Profile

¦ ¦ Windows Installer 4.5

¦ ¦ Windows PowerShell 2.0

Once you have all the prerequisites, it important to become familiar with the SQL Server

components

supported on Server Core.

SQL Server Features Supported on Server Core

There are numerous SQL Server features that are fully supported on Server Core. They include

Database

Engine Services, SQL Server Replication, Full Text Search, Analysis Services, Client Tools

Connectivity, and Integration Services. Likewise, Sever Core does not support many other features,

including

Reporting Services, Business Intelligence Development Studio, Client Tools Backward

Compatibility, Client Tools SDK, SQL Server Books Online, Distributed Replay Controller, SQL Client

Connectivity SDK, Master Data Services, and Data Quality Services. Some features such as Management

Tools – Basic, Management Tools – Complete, Distributed Replay Client, and Microsoft Sync

Framework are supported only remotely. Therefore, these features can be installed on editions of the

Windows operating system that are not Server Core, and then used to remotely connect to a SQL

Server instance running on Server Core. For a full list of supported and unsupported features review

the information at this link: http://msdn.microsoft.com/en-us/library/hh231669(SQL.110).aspx.

Note To leverage Server Core, you need to plan your SQL Server installation ahead of

time. Give yourself the opportunity to fully understand which SQL Server features are

required

to support your mission-critical workloads.

SQL Server on Server Core Installation Alternatives

The typical SQL Server Installation Setup Wizard is not supported when installing SQL Server 2012 on

Server Core. As a result, you need to automate the installation process by either using a commandline

installation, using a configuration file, or leveraging the DefaultSetup.ini methodology. Details

and examples for each of these methods can be found in Books Online: http://technet.microsoft.com

/en-us/library/ms144259(SQL.110).aspx.

Note When installing SQL Server 2012 on Server Core, ensure that you use Full Quiet

mode by using the /Q parameter or Quiet Simple mode by using the /QS parameter.

Additional High-Availability and Disaster-Recovery

Enhancements

This section summarizes some of the additional high-availability and disaster recovery enhancements

found in SQL Server 2012.

Support for Server Message Block

A common trend for organizations in recent years has been the movement toward consolidating

databases and applications onto fewer servers.specifically, hosting many instances of SQL Server

running on a failover cluster. When using failover clustering for consolidation, the previous versions of

SQL Server required a single drive letter for each SQL Server failover cluster instance. Because there

are only 23 drive letters available, without taking into account reservations, the maximum amount of

SQL Server instances supported on a single failover cluster was 23. Twenty-three instances sounds like

an ample amount; however, the drive letter limitation negatively affects organizations running powerful

servers that have the compute and memory resources to host more than 23 instances on a single

server. Going forward, SQL Server 2012 and failover clustering introduces support for Server Message

Block (SMB).

Note You might be thinking you can use mount points to alleviate the drive-letter pain

point. When working with previous versions of SQL Server, even with mount points, you

need at least one drive letter for each SQL Server failover cluster instance.

Some of the SQL Server 2012 benefits brought about by SMB are, of course, database-storage

consolidation and the potential to support more than 23 clustering instances in a single WSFC. To

take advantage of these features, the file servers must be running Windows Server 2008 or later

versions

of the operating system.

Database Recovery Advisor

The Database Recovery Advisor is a new feature aimed at optimizing the restore experience for

database

administrators conducting database recovery tasks. This tool includes a new timeline feature

that provides a visualization of the backup history, as shown in Figure 2-14.

FIGURE 2-14 Database Recovery Advisor backup and restore visual timeline

Online Operations

SQL Server 2012 also includes a few enhancements for online operation that reduce downtime during

planned maintenance operations. Line-of-business (LOB) re-indexing and adding columns with

defaults

are now supported.

Rolling Upgrade and Patch Management

All of the new AlwaysOn capabilities reduce application downtime to only a single manual failover

by supporting rolling upgrades and patching of SQL Server. This means a database administrator

can apply

a service pack or critical fix to the passive node or nodes if using a failover cluster or to

secondary

replicas if using availability groups. Once the installation is complete on all passive nodes

or secondaries, a database administrator can conduct a manual failover and then apply the service

pack or critical fix to the node in an FCI or replica. This rolling strategy also applies when upgrading

the database platform.

41

CHAP TE R 3

Performance and Scalability

Microsoft SQL Server 2012 introduces a new index type called columnstore. The columnstore

index feature was originally referred to as project Apollo during the development phases of SQL

Server 2012 and during the distribution of the Community Technology Preview (CTP) releases of the

product. This new index combined with the advanced query-processing enhancements offer blazingfast

performance optimizations for data-warehousing workloads and other similar queries. In many

cases, data-warehouse query performance has improved by tens to hundreds of times.

This chapter aims to teach, enlighten, and even dispel flawed beliefs about the columnstore index

so that database administrators can greatly increase query performance for their data warehouse

workloads. The questions this chapter focuses are the following:

¦ ¦ What is a columnstore index?

¦ ¦ How does a columnstore index drastically increase the speed of data warehouse queries?

¦ ¦ When should a database administrator build a columnstore index?

¦ ¦ Are there any well-established best practices for a columnstore index deployment?

Let’s look under the hood to see how organizations will benefit from significant data-warehouse

performance gains with the new, in-memory, columnstore index technology, which also helps with

managing increasing data volumes.

Columnstore Index Overview

Because of a proliferation of data being captured across devices, applications, and services,

organizations

today are tasked with storing massive amounts of data to successfully operate their

businesses. Using traditional tools to capture, manage, and process data within an acceptable time is

becoming increasingly challenging as the data users want to capture continues to grow. For example,

the volume of data is overwhelming the ability of data warehouses to execute queries in a timely

manner, and considerable time is spent tuning queries and designing and maintaining indexes to try

to get acceptable query performance. In many cases, so much time might have elapsed between the

time the query is launched and the time the result sets are returned that organizations have difficulty

recalling what the original request was about. Equally unproductive are the cases where the delay

causes the business opportunity to be lost.

42 PART 1 Database Administration

With the many issues organizations are facing as one of their primary concerns, the Query

Processing

and Storage teams from the SQL Server product group set to work on new technologies

that would allow very large data sets to be read quickly and accurately while transforming the data

into useful information and knowledge for organizations in a timely manner. The Query Processing

team reviewed academic research in columnstore data representations and analyzed improved

query-execution capabilities for data warehousing. In addition, they collaborated with the SQL Server

product group Analysis Services team to gain a stronger understanding about the other team’s work

with their columnstore implementation known as PowerPivot for SQL Server 2008 R2. Their research

and analysis led the Query Processing team to create the new columnstore index and query optimizations

based on vector-based execution capability, which significantly improves data-warehouse

query performance.

When developing the new columnstore index, the Query Processing team committed to a number

of goals. They aimed to ensure that end users who consume data had an interactive and positive

experience with all data sets, whether large or small, which meant the response time on data must be

swift. These strategies also apply to ad hoc and reporting queries. Moreover, database administrators

might even be able to reduce their needs for manually tuning queries, summary tables, indexed

views, and in some cases OLAP cubes. All these goals naturally impact total cost of ownership (TCO)

because hardware costs are lowered and fewer people are required to get a task accomplished.

Columnstore Index Fundamentals and Architecture

Before designing, implementing, or managing a columnstore index, it is beneficial to understand how

they work, how data is stored in a columnstore index, and what type of queries can benefit from a

columnstore index.

How Is Data Stored When Using a Columnstore Index?

With traditional tables (heaps) and indexes (B-trees), SQL Server stores data in pages in a row-based

fashion. This storage model is typically referred to as a row store. Using column stores is like turning

the traditional storage model 90 degrees, where all the values from a single column are stored contiguously

in a compressed form. The columnstore index stores each column in a separate set of disk

pages rather than storing multiple rows per page, which has been the traditional storage format. The

following examples illustrate the differences.

Let’s use a common table populated with employee data, as illustrated in Table 3-1, and then

evaluate the different ways data can be stored. This employee table includes typical data such as an

employee ID number, employee name, and the city and state the employee is located in.

CHAPTER 3 Performance and Scalability 43

TABLE 3-1 Traditional Table Containing Employee Data

EmployeeID

Name

City

State

1

Ross

San Francisco

CA

2

Sherry

New York

NY

3

Gus

Seattle

WA

4

Stan

San Jose

CA

5

Lijon

Sacramento

CA

Depending on the type of index chosen—traditional or columnstore—database administrators can

organize their data by row (as shown in Table 3-2) or by column (as shown in Table 3-3).

TABLE 3-2 Employee Data Stored in a Traditional “Row Store” Format

Row Store

1 Ross San Francisco CA

2 Sherry New York NY

3 Gus Seattle WA

4 Stan San Jose CA

5 Lijon Sacramento CA

TABLE 3-3 Employee Data Stored in the New Columnstore Format

Columnstore

1 2 3 4 5

Ross Sherry Gus Stan Lijon

San Francisco New York Seattle San Jose Sacramento

CA NY WA CA CA

As you can see, the major difference between the columnstore format in Table 3-3 and the row

store method in Table 3-2 is that a columnstore index groups and stores data for each column and

then joins all the columns to complete the whole index, whereas a traditional index groups and

stores data for each row and then joins all the rows to complete the whole index.

Now that you understand how data is stored when using columnstore indexes compared to

traditional

B-tree indexes, let’s take a look at how this new storage model and advanced query

optimizations

significantly speed up the retrieval of data. The next section describes three ways that

SQL Server columnstore indexes significantly improve the speed of queries.

How Do Columnstore Indexes Significantly Improve the Speed

of Queries?

The new columnstore storage model significantly improves data warehouse query speeds for

many reasons. First, data organized in a column shares many more similar characteristics than data

organized

across rows. As a result, a much higher level of compression can be achieved compared

to data organized across rows. Moreover, the columnstore index within SQL Server uses the VertiPaq

compression algorithm technology, which in SQL Server 2008 R2 was found only in Analysis Server

for PowerPivot. VertiPaq compression is far superior to the traditional row and page compression

used in the Database Engine, with compression rates of up to 15 to 1 having been achieved with

VertiPag. When data is compressed, queries require less IO because the amount of data transferred

from disk to memory is significantly reduced. Reducing IO when processing queries equates to faster

performance-response times. The advantages of columnstore do not come to an end here. With less

data transferred to memory, less space is required in memory to hold the working set affiliated with

the query.

Second, when a user runs a query using the columnstore index, SQL Server fetches data only for

the columns that are required for the query, as illustrated in Figure 3-1. In this example, there are 15

columns in the table; however, because the data required for the query resides in Column 7, Column

8, and Column 9, only these columns are retrieved.

C1 C2 C3 C4 C5 C6

C7 C8 C9

C10 C11 C12 C13 C14 C15

FIGURE 3-1 Improving performance and reducing IO by fetching only columns required for the query

Because data-warehouse queries typically touch only 10 to 15 percent of the columns in large fact

tables, fetching only selected columns translates into savings of approximately 85 to 90 percent of an

organization’s IO, which again increases performance speeds.

Batch-Mode Processing

Finally, an advanced technology for processing queries that use columnstore indexes speeds up

queries yet another way. Speaking about processes, it is a good time to dig a little deeper into how

queries are processed.

First, the data in the columns are processed in batches using a new, highly-efficient vector

technology

that works with columnstore indexes. Database administrators should take a moment

to review the query plan and notice the groups of operators that execute in batch mode. Note

that not all operators execute in batch mode; however, the most important ones affiliated with

data warehousing

do, such as Hash Join and Hash Aggregation. All of the algorithms have been

significantly

optimized to take advantage of modern hardware architecture, such as increased core

counts and additional RAM, thereby improving parallelism. All of these improvements affiliated

with the columnstore index contribute to better batch-mode processing than traditional row-mode processing.

Columnstore Index Storage Organization

Let’s examine how the storage associated with a columnstore index is organized. First I’ll define the

new storage concepts affiliated with columnstore indexes, such as a segment and a row group, and

then I’ll elucidate how these concepts relate to one another.

As illustrated in Figure 3-2, data in the columnstore index is broken up into segments. A segment

contains data from one column for a set of up to about 1 million rows. Segments for the same set

of rows comprise a row group. Instead of storing the data page by page, SQL Server stores the row

group as a unit. Each segment is internally stored in a separate Large Object (LOB). Therefore, when

SQL Server reads the data, the unit reading from disk consists of a segment and the segment is a unit

of transfer between the disk and memory.

C1 C2 C3 C4 C5 C6 C7 C8 C9 C10 C11 C12 C13 C14 C15

Segment

Row Group

FIGURE 3-2 How a columnstore index stores data

Columnstore Index Support and SQL Server 2012

Columnstore indexes and batch-query execution mode are deeply integrated in SQL Server 2012, and

they work in conjunction with many of the Database Engine features found in SQL Server 2012. For

example, database administrators can implement a columnstore index on a table and still successfully

use AlwaysOn Availability Groups (AG), AlwaysOn failover cluster instances (FCI), database mirroring,

log shipping, and SQL Server Management Studio administration tools. Here are the common business

data types supported by columnstore indexes:

¦ ¦ char and varchar

¦ ¦ All Integer types (int, bigint, smallint, and tinyint)

¦ ¦ real and float

¦ ¦ string

¦ ¦ money and small money

¦ ¦ All date, time, and DateTime types, with one exception (datetimeoffset with precision greater

than 2)

¦ ¦ Decimal and numeric with precision less than or equal to 18 (that is, with less than or exactly

18 digits)

¦ ¦ Only one columnstore index can be created per table.

Columnstore Index Restrictions

Although columnstore indexes work with the majority of the data types, components, and features

found in SQL Server 2012, columnstore indexes have the following restrictions and cannot be

leveraged

in the following situations:

¦ ¦ You can enable PAGE or ROW compression on the base table, but you cannot enable PAGE or

ROW compression on the columnstore index.

¦ ¦ Tables and columns cannot participate in a replication topology.

¦ ¦ Tables and columns using Change Data Capture are unable to participate in a columnstore

index.

¦ ¦ Create Index: You cannot create a columnstore index on the following data types:

• decimal greater than 18 digits

• binary and varbinary

• BLOB

• CLR

• (n)varchar(max)

• uniqueidentifier

• datetimeoffset with precision greater than 2

¦ ¦ Table Maintenance: If a columnstore index exists, you can read the table but you cannot

directly update it. This is because columnstore indexes are designed for data-warehouse

workloads

that are typically read based. Rest assured that there is no need to agonize. The

upcoming “Columnstore Index Design Considerations and Loading Data” section articulates

strategies on how to load new data when using columnstore indexes.

¦ ¦ Process Queries: You can process all read-only T-SQL queries using the columnstore index, but

because batch processing works only with certain operators, you will see that some queries

are accelerated more than others.

¦ ¦ A column that contains filestream data cannot participate in a columnstore index.

¦ ¦ INSERT, UPDATE, DELETE, and MERGE statements are not allowed on tables using columnstore

indexes.

¦ ¦ More than 1024 columns are not supported when creating a columnstore index.

¦ ¦ Only nonclustered columnstore indexes are allowed. Filtered columnstore indexes are not allowed.

¦ ¦ Computed and sparse columns cannot be part of a columnstore index.

¦ ¦ A columnstore index cannot be created on an indexed view.

Columnstore Index Design Considerations and Loading Data

When working with columnstore indexes, some queries are accelerated much more than others.

Therefore, to optimize query performance, it is important to understand when to build a columnstore

index and when not to build a columnstore index. The next sections cover when to build columnstore

indexes, design considerations, and how to load data when using a columnstore index.

When to Build a Columnstore Index

The following list describes when database administrators should use a columnstore index to optimize

query performance:

¦ ¦ When workloads are mostly read based—specifically, data warehouse workloads.

¦ ¦ Your workflow permits partitioning (or a drop-rebuild index strategy) to handle new data.

Most commonly, this is associated with periodic maintenance windows when indexes can be

rebuilt or when staging tables are switching into empty partitions of existing tables.

¦ ¦ If most queries fit a star join pattern or entail scanning and aggregating large amounts

of data.

¦ ¦ If updates occur and most updates append new data, which can be loaded using staging

tables and partition switching.

You should use columnstore indexes when building the following types of tables:

¦ ¦ Large fact tables

¦ ¦ Large (millions of rows) dimension tables

When Not to Build a Columnstore Index

Database administrators might encounter situations when the performance benefits achieved by

using traditional B-tree indexes on their tables are greater than the benefits of using a columnstore

index. The following list describes some of these situations:

¦ ¦ Data in your table constantly requires updating.

¦ ¦ Partition switching or rebuilding an index does not meet the workflow requirements of your

business.

¦ ¦ You encounter frequent small look-up queries. Note, however, that a columnstore index might

still benefit you in this situation. As such, you can implement a columnstore index without any

repercussions because the query optimizer should be able to determine when to use the traditional

B-tree index instead of the columnstore index. This strategy assumes you have updated

statistics.

¦ ¦ You test columnstore indexes on your workload and do not see any benefit.

Loading New Data

As mentioned in earlier sections, tables with a columnstore index cannot be updated directly.

However,

there are three alternatives for loading data into a table with a columnstore index:

¦ ¦ Disable the Columnstore Index This procedure consists of the following three steps:

1. First disable the columnstore index.

2. Update the data.

3. Rebuild the index when the updates are complete.

Database administrators should ensure a maintenance window exists when leveraging this

strategy. The time affiliated with the maintenance window will vary per customers workload.

Therefore test within a prototype environment to determine time required.

¦ ¦ Leverage Partitioning and Partition Switching Partitioning data enables database

administrators

to manage and access subsets of their data quickly and efficiently while

maintaining

the integrity of the entire data collection. Partition switching also allows database

administrators to quickly and efficiently transfer subsets of their data by assigning a table as

a partition to an already existing partitioned table, switching a partition from one partitioned

table to another, or reassigning a partition to form a single table. Partition switching is fully

supported with a columnstore index and is a practical way for updating data.

To use partitioning to load data, follow these steps:

1. Ensure you have an empty partition to accept the new data.

2. Load data into an empty staging table.

3. Switch the staging table (containing the newly loaded data) into the empty partition.

To use partitioning to update existing data, use the following steps:

1. Determine which partition contains the data to be modified.

2. Switch the partition into an empty staging table.

3. Disable the columnstore index on the staging table.

4. Update the data.

5. Rebuild the columnstore index on the staging table.

6. Switch the staging table back into the original partition (which was left empty when the

partition was switched into the staging table).

¦ ¦ Union All Database administrators can load data by storing their main data in a fact table

that has a columnstore. Next, create a secondary table to add or update data. Finally, leverage

a UNION ALL query so that it returns all of the data between the large fact table with the

columnstore index and smaller updateable tables. Periodically load the table from the secondary

table into the main table by using partition switching or by disabling and rebuilding the

columnstore index. Note that some queries using the UNION ALL strategy might not be as fast

as when all the data is in a single table.

Creating a Columnstore Index

Creating a columnstore index is very similar to creating any other traditional SQL Server indexes. You

can use the graphical user interface in SQL Server Management Studio or Transact-SQL. Many individuals

prefer to use the graphical user interface because they want to avoid typing all of the column

names, which is what they have to do when creating the index with Transact-SQL.

A few questions arise frequently when creating a columnstore index. Database administrators

often want to know the following:

¦ ¦ Which columns should be included in the columnstore index?

¦ ¦ Is it possible to create a clustered columnstore index?

When creating a columnstore index, a database administrator should typically include all of the

supported columnstore index columns associated with the table. Note that you don’t need to include

all of the columns. The answer to the second question is “No.” All columnstore indexes must be

nonclustered;

therefore, a clustered columnstore index is not allowed.

The next sections explain the steps for creating a columnstore index using either of the two

methods

mentioned: SQL Server Management Studio and Transact-SQL.

Creating a Columnstore Index by Using SQL Server Management

Studio

Here are the steps for creating a columnstore index using SQL Server Management Studio (SSMS):

1. In SQL Server Management Studio, use Object Explorer to connect to an instance of the SQL

Server Database Engine.

2. In Object Explorer, expand the instance of SQL Server, expand Databases, expand a database,

and expand a table in which you would like to create a new columnstore index.

3. Expand the table, right-click the Index folder, choose New Index, and then click Non-Clustered

Columnstore Index.

4. On the General tab, in the Index name box, type a name for the new index, and then click

Add.

5. In the Select Columns dialog box, select the columns to participate in the columnstore index

and then click OK.

6. If desired, configure the settings on the Options, Storage, and Extended Properties pages. If

you want to maintain the defaults, click OK to create the index, as illustrated in Figure 3-3.

FIGURE 3-3 Creating a nonclustered columnstore index with SSMS

Creating a Columnstore Index Using Transact-SQL

As mentioned earlier, you can use Transact-SQL to create a columnstore index instead of using the

graphical user interface in SQL Server Management Studio. The following example illustrates the

syntax

for creating a columnstore index with Transact-SQL:

CREATE [ NONCLUSTERED ] COLUMNSTORE INDEX index_name

ON <object> ( column [ ,…n ] )

[ WITH ( <column_index_option> [ ,…n ] ) ]

[ ON {

{ partition_scheme_name ( column_name ) }

| filegroup_name

| “default”

}

]

[ ; ]

<object> ::=

{

[database_name. [schema_name ] . | schema_name . ]

table_name

{

<column_index_option> ::=

{

DROP_EXISTING = { ON | OFF }

| MAXDOP = max_degree_of_parallelism

}

The following bullets explain the arguments affiliated with the Transact-SQL syntax to create the

nonclustered columnstore index:

¦ ¦ NONCLUSTERED This argument indicates that this index is a secondary representation of the

data.

¦ ¦ COLUMNSTORE This argument indicates that the index that will be created is a columnstore

index.

¦ ¦ index_name This is where you specify the name of the columnstore index to be created.

Index names must be unique within a table or view but do not have to be unique within a

database.

¦ ¦ column This refers to the column or columns to be added to the index. As a reminder, a

columnstore index is limited to 1024 columns.

¦ ¦ ON partition_scheme_name(column_name) Specifies the partition scheme that defines the

file groups on which the partitions of a partitioned index are mapped. The column_name

specifies the column against which a partitioned index will be partitioned. This column must

match the data type, length, and precision of the argument of the partition function that the

partition_scheme_name is using: partition_scheme_name or filegroup.. If these are not specified

and the table is partitioned, the index is placed in the same partition scheme using the same

partitioning column as the underlying table.

FIGURE 3-5 Reviewing the Columnstore Index Scan results

Using Hints with a Columnstore Index

Finally, if you believe the query can benefit from a columnstore index and the query execution plan is

not leveraging it, you can force the query to use a columnstore index. You do this by using the WITH

(INDEX(<indexname>)) hint where the <indexname> argument is the name of the columnstore index

you want to force.

The following example illustrates a query with an index hint forcing the use of a columnstore index:

SELECT DISTINCT (SalesTerritoryKey)

FROM dbo.FactResellerSales WITH (INDEX (Non-ClusteredColumnStoreIndexSalesTerritory)

GO

The next example illustrates a query with an index hint forcing the use of a different index, such

as a traditional clustered B-tree index over a columnstore index. For example, let’s say there are two

indexes on this table called SalesTerritoryKey, a clustered index called ClusteredIndexSalesTerritory,

and a nonclustered columnstore index called Non-ClusteredColumnStoreIndexSalesTerritory.

Instead

of using the columnstore index, the hint forces the query to use the clustered index known as

ClusteredIndexSalesTerritory:

93

CHAP TE R 6

Integration Services

Since its initial release in Microsoft SQL Server 2005, Integration Services has had incremental

changes in each subsequent version of the product. However, those changes were trivial in comparison

to the number of enhancements, performance improvements, and new features introduced

in SQL Server 2012 Integration Services. This product overhaul affects every aspect of Integration

Services, from development to deployment to administration.

Developer Experience

The first change that you notice as you create a new Integration Services project is that Business

Intelligence Development Studio (BIDS) is now a Microsoft Visual Studio 2010 shell called SQL Server

Data Tools (SSDT). The Visual Studio environment alone introduces some slight user-interface changes

from the previous version of BIDS. However, several more significant interface changes of note are

specific to SQL Server Integration Services (SSIS). These enhancements to the interface help you to

learn about the package-development process if you are new to Integration Services, and they enable

you to develop packages more easily if you already have experience with Integration Services. If you

are already an Integration Services veteran, you will also notice the enhanced appearance of tasks and

data flow components with rounded edges and new icons.

Add New Project Dialog Box

To start working with Integration Services in SSDT, you create a new project by following the same

steps you use to perform the same task in earlier releases of Integration Services. From the File menu,

point to New, and then select Project. The Add New Project dialog box displays. In the Installed

Templates

list, you can select the type of Business Intelligence template you want to use and then

view only the templates related to your selection, as shown in Figure 6-1. When you select a template,

a description of the template displays on the right side of the dialog box.

94 PART 2 Business Intelligence Development

FIGURE 6-1 New Project dialog box displaying installed templates

There are two templates available for Integration Services projects:

¦ ¦ Integration Services Project You use this template to start development with a blank

package

to which you add tasks and arrange those tasks into workflows. This template type

was available in previous versions of Integration Services.

¦ ¦ Integration Services Import Project Wizard You use this wizard to import a project from

the Integration Services catalog or from a project deployment file. (You learn more about

project deployment files in the “Deployment Models” section of this chapter.) This option is

useful when you want to use an existing project as a starting point for a new project, or when

you need to make changes to an existing project.

Note The Integration Services Connections Project template from previous versions is no

longer available.

CHAPTER 6 Integration Services 95

General Interface Changes

After creating a new package, several changes are visible in the package-designer interface, as you

can see in Figure 6-2:

¦ ¦ SSIS Toolbox You now work with the SSIS Toolbox to add tasks and data flow components

to a package, rather than with the Visual Studio toolbox that you used in earlier versions of

Integration Services. You learn more about this new toolbox in the “SSIS Toolbox” section of

this chapter.

¦ ¦ Parameters The package designer includes a new tab to open the Parameters window for

a package. Parameters allow you to specify run-time values for package, container, and task

properties or for variables, as you learn in the “Parameters” section of this chapter.

¦ ¦ Variables button This new button on the package designer toolbar provides quick access

to the Variables window. You can also continue to open the window from the SSIS menu or by

right-clicking the package designer and selecting the Variables command.

¦ ¦ SSIS Toolbox button This button is also new in the package-designer interface and allows

you to open the SSIS Toolbox when it is not visible. As an alternative, you can open the SSIS

Toolbox from the SSIS menu or by right-clicking the package designer and selecting the SSIS

Toolbox command.

¦ ¦ Getting Started This new window displays below the Solution Explorer window and

provides

access to links to videos and samples you can use to learn how to work with

Integration

Services. This window includes the Always Show In New Project check box, which

you can clear if you prefer not to view the window after creating a new project. You learn

more about using this window in the next section, “Getting Started Window.”

¦ ¦ Zoom control Both the control flow and data flow design surface now include a zoom

control

in the lower-right corner of the workspace. You can zoom in or out to a maximum size

of 500 percent of the normal view or to a minimum size of 10 percent, respectively. As part

of the zoom control, a button allows you to resize the view of the design surface to fit the

window.

SSIS Toolbox

Zoom Control Getting Started

Parameters

Variables

Button

SSIS Toolbox

Button Parameters

FIGURE 6-2 Package-designer interface changes

Getting Started Window

As explained in the previous section, the Getting Started window is new to the latest version of

Integration

Services. Its purpose is to provide resources to new developers. It will display automatically

when you create a new project unless you clear the check box at the bottom of the window. You

must use the Close button in the upper-right corner of the window to remove it from view. Should

you want to access the window later, you can choose Getting Started on the SSIS menu or right-click

the design surface and select Getting Started.

In the Getting Started window, you find several links to videos and Integration Services samples.

To use the links in this window, you must have Internet access. By default, the following topics are available:

¦ ¦ Designing and Tuning for Performance Your SSIS Packages in the Enterprise This

link provides access to a series of videos created by the SQL Server Customer Advisory Team

(SQLCAT) that explain how to monitor package performance and techniques to apply during

package development to improve performance.

¦ ¦ Parameterizing the Execute SQL Task in SSIS This link opens a page from which you can

access a brief video explaining how to work with parameterized SQL statements in Integration

Services.

¦ ¦ SQL Server Integration Services Product Samples You can use this link to access the

product samples available on Codeplex, Microsoft’s open-source project-hosting site. By

studying the package samples available for download, you can learn how to work with various

control flow tasks or data flow components.

Note Although the videos and samples accessible through these links were developed for

previous versions of Integration Services, the principles remain applicable to the latest version.

When opening a sample project in SSDT, you will be prompted to convert the project.

You can customize the Getting Started window by adding your own links to the SampleSites.xml

file located in the Program Files (x86)\Microsoft SQL Server\110\DTS\Binn folder.

SSIS Toolbox

Another new window for the package designer is the SSIS Toolbox. Not only has the overall interface

been improved, but you will find there is also added functionality for arranging items in the toolbox.

Interface Improvement

The first thing you notice in the SSIS Toolbox is the updated icons for most items. Furthermore, the

SSIS Toolbox includes a description for the item that is currently selected, allowing you to see what it

does without needing to add it first to the design surface. You can continue to use drag-and-drop to

place items on the design surface, or you can double-click the item. However, the new behavior when

you double-click is to add the item to the container that is currently selected, which is a welcome

time-saver for the development process. If no container is selected, the item is added directly to the

design surface.

Item Arrangement

At the top of the SSIS Toolbox, you will see two new categories, Favorites and Common, as shown

in Figure 6-3. All categories are populated with items by default, but you can move items into

another

category at any time. To do this, right-click the item and select Move To Favorites or Move

To Common.

If you are working with control flow items, you have Move To Other Tasks as another

choice, but if you are working with data flow items, you can choose Move To Other Sources, Move To

Other Transforms, or Move To Other Destinations. You will not see the option to move an item to the

category in which it already exists, nor are you able to use drag-and-drop to move items manually.

If you decide to start over and return the items to their original locations, select Restore Toolbox

Defaults.

FIGURE 6-3 SSIS Toolbox for control flow and data flow

Shared Connection Managers

If you look carefully at the Solution Explorer window, you will notice that the Data Sources and Data

Source Views folders are missing, and have been replaced by a new file and a new folder. The new

file is Project.params, which is used for package parameters and is discussed in the “Package Parameters”

section of this chapter. The Connections Managers folder is the new container for connection

managers

that you want to share among multiple packages.

Note If you create a Cache Connection Manager, Integration Services shares the in-memory

cache with child packages using the same cache as the parent package. This

feature

is valuable for optimizing repeated lookups to the same source across multiple

packages.

To create a shared connection manager, follow these steps:

1. Right-click the Connections Managers folder, and select New Connection Manager.

2. In the Add SSIS Connection Manager dialog box, select the desired connection-manager type

and then click the Add button.

3. Supply the required information in the editor for the selected connection-manager type, and

then click OK until all dialog boxes are closed.

A file with the CONMGR file extension displays in the Solution Explorer window within the

Connections

Managers folder. In addition, the file also appears in the Connections Managers tray

in the package designer in each package contained in the same project. It displays with a (project)

prefix to differentiate it from package connections. If you select the connection manager associated

with one package and change its properties, the change affects the connection manager in all other packages.

If you change your mind about using a shared connection manager, you can convert it to a

package

connection. To do this, right-click the connection manager in the Connection Managers

tray, and select Convert To Package Connection. The conversion removes the CONMGR file from

the Connections

Manager folder in Solution Explorer and from all other packages. Only the package

in which you execute the conversion contains the connection. Similarly, you can convert a package

connection to a shared connection manager by right-clicking the connection manager in Solution

Explorer and selecting Convert To Project Connection.

Scripting Engine

The scripting engine in SSIS is an upgrade to Visual Studio Tools for Applications (VSTA) 3.0 and

includes support for the Microsoft .NET Framework 4.0. When you edit a script task in the control

flow or a script component in the data flow, the VSTA integrated development environment (IDE)

continues

to open in a separate window, but now it uses a Visual Studio 2010 shell. A significant

improvement

to the scripting engine is the ability to use the VSTA debug features with a Script

component

in the data flow.

Note As with debugging the Script task in the control flow, you must set the

Run64BitRunTime project property to False when you are debugging on a 64-bit computer.

Expression Indicators

The use of expressions in Integration Services allows you, as a developer, to create a flexible package.

Behavior can change at run-time based on the current evaluation of the expression. For example, a

common reason to use expressions with a connection manager is to dynamically change connection

strings to accommodate the movement of a package from one environment to another, such as from

development to production. However, earlier versions of Integration Services did not provide an easy

way to determine whether a connection manager relies on an expression. In the latest version, an

extra icon appears beside the connection manager icon as a visual cue that the connection manager

uses expressions, as you can see in Figure 6-4.

FIGURE 6-4 A visual cue that the connection manager uses an expression

This type of expression indicator also appears with other package objects. If you add an expression

to a variable or a task, the expression indicator will appear on that object.

Undo and Redo

A minor feature, but one you will likely appreciate greatly, is the newly added ability to use Undo and

Redo while developing packages in SSDT. You can now make edits in either the control flow or data

flow designer surface, and you can use Undo to reverse a change or Redo to restore a change you

had just reversed. This capability also works in the Variables window, and on the Event Handlers and

Parameters tabs. You can also use Undo and Redo when working with project parameters.

To use Undo and Redo, click the respective buttons in the standard toolbar. You can also use Ctrl+Z

and Ctrl+Y, respectively. Yet another option is to access these commands on the Edit menu.

Note The Undo and Redo actions will not work with changes you make to the SSIS

Toolbox, nor will they work with shared connection managers.

Package Sort By Name

As you add multiple packages to a project, you might find it useful to see the list of packages in

Solution

Explorer display in alphabetical order. In previous versions of Integration Services, the only

way to re-sort the packages was to close the project and then reopen it. Now you can easily sort the

list of packages without closing the project by right-clicking the SSIS Packages folder and selecting

Sort By Name.

Status Indicators

After executing a package, the status of each item in the control flow and the data flow displays in the

package designer. In previous versions of Integration Services, the entire item was filled with green to

indicate success or red to indicate failure. However, for people who are color-blind, this use of color

was not helpful for assessing the outcome of package execution. Consequently, the user interface

now displays icons in the upper-right corner of each item to indicate success or failure, as shown in

Figure 6-5.

FIGURE 6-5 Item status indicators appear in the upper-right corner

Control Flow

Apart from the general enhancements to the package-designer interface, there are three notable

updates for the control flow. The Expression Task is a new item available to easily evaluate an expression

during the package workflow. In addition, the Execute Package Task has some changes to make it

easier to configure the relationship between a parent package and child package. Another new item

is the Change Data Capture Task, which we discuss in the “Change Data Capture Support” section of

this chapter.

Expression Task

Many of the developer experience enhancements in Integration Services affect both control flow and

data flow, but there is one new feature that is exclusive to control flow. The Expression Task is a new

item available in the SSIS Toolbox when the control flow tab is in focus. The purpose of this task is to

make it easier to assign a dynamic value to a variable.

Rather than use a Script Task to construct a variable value at runtime, you can now add an

Expression

Task to the workflow and use the SQL Server Integration Services Expression Language.

When you edit the task, the Expression Builder opens. You start by referencing the variable and

including

the equals sign (=) as an assignment operator. Then provide a valid expression that resolves

to a single value with the correct data type for the selected variable. Figure 6-6 illustrates an example

of a variable assignment in an Expression Task.

FIGURE 6-6 Variable assignment in an Expression Task

Note The Expression Builder is an interface commonly used with other tasks and data flow

components. Notice in Figure 6-6 that the list on the left side of the dialog box includes

both variables and parameters. In addition, system variables are now accessible from a

separate folder rather than listed together with user variables.

Execute Package Task

The Execute Package Task has been updated to include a new property, ReferenceType, which appears

on the Package page of the Execute Package Task Editor. You use this property to specify the

location

of the package to execute. If you select External Reference, you configure the path to the

child package

just as you do in earlier versions of Integration Services. If you instead select Project

Reference,

you then choose the child package from the drop-down list.

In addition, the Execute Package Task Editor has a new page for parameter bindings, as shown

in Figure 6-7. You use this page to map a parameter from the child package to a parameter value or

variable value in the parent package.

FIGURE 6-7 Parameter bindings between a parent package and a child package

Data Flow

The data flow also has some significant updates. It has some new items, such as the Source and

Destination

assistants and the DQS Cleansing transformation, and there are some improved items

such as the Merge and Merge Join transformation. In addition, there are several new data flow

components

resulting from a partnership between Microsoft and Attunity for use when accessing

Open Database Connectivity (ODBC) connections and processing change data capture logs. We

describe the change data capture components in the “Change Data Capture Support” section of this

chapter. Some user interface changes have also been made to simplify the process and help you get

your job done faster when designing the data flow.

Sources and Destinations

Let’s start exploring the changes in the data flow by looking at sources and destinations.

Source and Destination Assistants

The Source Assistant and Destination Assistant are two new items available by default in the Favorites

folder of the SSIS Toolbox when working with the data flow designer. These assistants help you easily

create a source or a destination and its corresponding connection manager.

To create a SQL Server source in a data flow task, perform the following steps:

1. Add the Source Assistant to the data flow design surface by using drag-and-drop or by

double-clicking the item in the SSIS Toolbox, which opens the Source Assistant – Add New

Source dialog box as shown here:

Note Clear the Show Only Installed Source Types check box to display the

additional

available source types that require installation of one of the following

client providers: DB2, SAP BI, Sybase, or Teradata.

2. In the Select Connection Managers list, select an existing connection manager or select New

to create a new connection manager, and click OK.

3. If you selected the option to create a new connection manager, specify the server name,

authentication method, and database for your source data in the Connection Manager dialog

box, and click OK.

The new data source appears on the data flow design surface, and the connection manager

appears in the Connection Managers tray. You next need to edit the data source to configure

the data-access mode, columns, and error output.

ODBC Source and Destination

The ODBC Source and ODBC Destination components, shown in Figure 6-8, are new to Integration

Services in this release and are based on technology licensed by Attunity to Microsoft. Configuration

of these components is similar to that of OLE DB sources and destinations. The ODBC Source supports

Table Name and SQL Command as data-access modes, whereas data-access modes for the ODBC

Destination are Table Name – Batch and Table Name – Row By Row.

FIGURE 6-8 ODBC Source and ODBC Destination data flow components

Flat File Source

You use the Flat File source to extract data from a CSV or TXT file, but there were some data formats

that this source did not previously support without requiring additional steps in the extraction

process. For example, you could not easily use the Flat File source with a file containing a variable

number of columns. Another problem was the inability to use a character that was designated as a

qualifier as a literal value inside a string. The current version of Integration Services addresses both of

these problems.

¦ ¦ Variable columns A file layout with a variable number of columns is also known as a

ragged-right delimited file. Although Integration Services supports a ragged-right format,

a problem arises when one or more of the rightmost columns do not have values and the

column delimiters for the empty columns are omitted from the file. This situation commonly

occurs when the flat file contains data of mixed granularity, such as header and detail transaction

records. Although a row delimiter exists on each row, Integration Services ignored the row

delimiter and included data from the next row until it processed data for each expected column.

Now the Flat File source correctly recognizes the row delimiter and handles the missing

columns as NULL values.

Note If you expect data in a ragged-right format to include a column delimiter

for each missing column, you can disable the new processing behavior by changing

the AlwaysCheckForRowDelimiters property of the Flat File connection

manager to False.

¦ ¦ Embedded qualifiers Another challenge with the Flat File source in previous versions of Integration

Services was the use of a qualifier character inside a string encapsulated within qualifiers.

For example, consider a flat file that contains the names of businesses. If a single quote

is used as a text qualifier but also appears within the string as a literal value, the common

practice is to use another single quote as an escape character, as shown here.

ID,BusinessName

404,’Margie”s Travel’

406, ‘Kickstand Sellers’

In the first data row in this example, previous versions of Integration Services would fail to

interpret the second apostrophe in the BusinessName string as an escape character, and

instead would process it as the closing text qualifier for the column. As a result, processing of

the flat file returned an error because the next character in the row is not a column delimiter.

This problem is now resolved in the current version of Integration Services with no additional

configuration required for the Flat File source.

Transformations

Next we turn our attention to transformations.

Pivot Transformation

The user interface of the Pivot transformation in previous versions of Integration Services was a

generic editor for transformations, and it was not intuitive for converting input rows into a set of

columns for each row. The new custom interface, shown in Figure 6-9, provides distinctly named fields

and includes descriptions describing how each field is used as input or output for the pivot operation.

FIGURE 6-9 Pivot transformation editor for converting a set of rows into columns of a single row

Row Count Transformation

Another transformation having a generic editor in previous versions is the Row Count transformation.

The sole purpose of this transformation is to update a variable with the number of rows passing

through the transformation. The new editor makes it very easy to change the one property for this

transformation that requires configuration, as shown in Figure 6-10.

FIGURE 6-10 Row Count transformation editor for storing the current row count in a variable

Merge and Merge Join Transformations

Both the Merge transformation and the Merge Join transformation allow you to collect data from two

inputs and produce a single output of combined results. In earlier versions of Integration Services,

these transformations could result in excessive memory consumption by Integration Services when

data arrives from each input at different rates of speed. The current version of Integration Services

better accommodates this situation by introducing a mechanism for these two transformations to

better manage memory pressure in this situation. This memory-management mechanism operates

automatically, with no additional configuration of the transformation necessary.

Note If you develop custom data flow components for use in the data flow and if

these components accept multiple inputs, you can use new methods in the

Microsoft.SqlServer.Dts.Pipeline namespace to provide similar memory pressure

management

to your custom components. You can learn more about implementing these

methods by reading “Developing Data Flow Components with Multiple Inputs,” located at

http://msdn.microsoft.com/en-us/library/ff877983(v=sql.110).aspx.

DQS Cleansing Transformation

The DQS Cleansing transformation is a new data flow component you use in conjunction with Data

Quality Services (DQS). Its purpose is to help you improve the quality of data by using rules that

are established for the applicable knowledge domain. You can create rules to test data for common

misspellings

in a text field or to ensure that the column length conforms to a standard specification.

To configure the transformation, you select a data-quality-field schema that contains the rules to

apply and then select the input columns in the data flow to evaluate. In addition, you configure error

handling. However, before you can use the DQS Cleansing transformation, you must first install and

configure DQS on a server and create a knowledge base that stores information used to detect data

anomalies and to correct invalid data, which deserves a dedicated chapter. We explain not only how

DQS works and how to get started with DQS, but also how to use the DQS Cleansing transformation

in Chapter 7, “Data Quality Services.”

Column References

The pipeline architecture of the data flow requires precise mapping between input columns and

output

columns of each data flow component that is part of a Data Flow Task. The typical workflow

during data flow development is to begin with one or more sources, and then proceed with the

addition

of new components in succession until the pipeline is complete. As you plug each subsequent

component into the pipeline, the package designer configures the new component’s input

columns to match the data type properties and other properties of the associated output columns

from the preceding

component. This collection of columns and related property data is also known as

metadata.

If you later break the path between components to add another transformation to pipeline,

the metadata in some parts of the pipeline could change because the added component can add

columns, remove columns, or change column properties (such as convert a data type). In previous

versions of Integration Services, an error would display in the data flow designer whenever metadata

became invalid. On opening a downstream component, the Restore Invalid Column References editor

displayed to help you correct the column mapping, but the steps to perform in this editor were not

always intuitive. In addition, because of each data flow component’s dependency on access to metadata,

it was often not possible to edit the component without first attaching it to an existing component

in the pipeline.

Components Without Column References

Integration Services now makes it easier to work with disconnected components. If you attempt to

edit a transformation or destination that is not connected to a preceding component, a warning message

box displays: “This component has no available input columns. Do you want to continue editing

the available properties of this component?”

After you click Yes, the component’s editor displays and you can configure the component as

needed. However, the lack of input columns means that you will not be able to fully configure the

component using the basic editor. If the component has an advanced editor, you can manually add

input columns and then complete the component configuration. However, it is usually easier to use

the interface to establish the metadata than to create it manually.

Resolve References Editor

The current version of Integration Services also makes it easier to manage the pipeline metadata if

you need to add or remove components to an existing data flow. The data flow designer displays an

error indicator next to any path that contains unmapped columns. If you right-click the path between

components, you can select Resolve References to open a new editor that allows you to map the

output

columns to input columns by using a graphical interface, as shown in Figure 6-11.

FIGURE 6-11 Resolve References editor for mapping output to input columns

In the Resolve References editor, you can drag a column from the Unmapped Output Columns list

and add it to the Source list in the Mapped Columns area. Similarly, you can drag a column from the

Unmapped Input Columns area to the Destination list to link the output and input columns. Another

option is to simply type or paste in the names of the columns to map.

Tip When you have a long list of columns in any of the four groups in the editor, you

can type a string in the filter box below the list to view only those columns matching the

criteria

you specify. For example, if your input columns are based on data extracted from

the Sales.SalesOrderDetail table in the AdventureWorks2008R2 database, you can type

unit in the filter box to view only the UnitPrice and UnitPriceDiscount columns.

You can also manually delete a mapping by clicking the Delete Row button to the right of each

mapping. After you have completed the mapping process, you can quickly delete any remaining

unmapped input columns by selecting the Delete Unmapped Input Columns check box at the bottom

of the editor. By eliminating unmapped input columns, you reduce the component’s memory

requirements

during package execution.

Collapsible Grouping

Sometimes the data flow contains too many components to see at one time in the package designer,

depending on your screen size and resolution. Now you can consolidate data flow components into

groups and expand or collapse the groups. A group in the data flow is similar in concept to a se

quence container in the control flow, although you cannot use the group to configure a common

property for all components that it contains, nor can you use it to set boundaries for a transaction or

to set scope for a variable.

To create a group, follow these steps:

1. On the data flow design surface, use your mouse to draw a box around the components that

you want to combine as a group. If you prefer, you can click each component while pressing

the Ctrl key.

2. Right-click one of the selected components, and select Group. A group containing the

components

displays in the package designer, as shown here:

3. Click the arrow at the top right of the Group label to collapse the group.

Data Viewer

The only data viewer now available in Integration Services is the grid view. The histogram, scatter plot,

and chart views have been removed.

To use the data viewer, follow these steps:

1. Right-click the path, and select Enable Data Viewer. All columns in the pipeline are automatically

included.

2. If instead you want to display a subset of columns, right-click the new Data Viewer icon (a

magnifying glass) on the data flow design surface, and select Edit.

3. In the Data Flow Path Editor, select Data Viewer in the list on the left.

4. Move columns from the Displayed Columns list to the Unused Columns list as applicable

(shown next), and click OK.

Change Data Capture Support

Change data capture (CDC) is a feature introduced in the SQL Server 2008 database engine. When

you configure a database for change data capture, the Database Engine stores information about

insert,

update, and delete operations on tables you are tracking in corresponding change data

capture

tables. One purpose for tracking changes in separate tables is to perform extract, transform,

and load (ETL) operations without adversely impacting the source table.

In the previous two versions of Integration Services, there were multiple steps required to develop

packages that retrieve data from change data capture tables and load the results into destination.

To expand Integration Services’ data-integration capabilities in SQL Server 2012 by supporting

change data capture for SQL Server, Microsoft partnered with Attunity, a provider of real-time data-integration

software. As a result, new change data capture components are available for use in the

control flow and data flow, simplifying the process of package development for change data capture.

Note Change data capture support in Integration Services is available only in Enterprise,

Developer, and Evaluation editions. To learn more about the change data capture feature

in the Database Engine, see “Basics of Change Data Capture” at http://msdn.microsoft.com

/en-us/library/cc645937(SQL.110).aspx.

CDC Control Flow

There are two types of packages you must develop to manage change data processing with

Integration

Services: an initial load package for one-time execution, and a trickle-feed package for

ongoing execution on a scheduled basis. You use the same components in each of these packages,

but you configure the control flow differently. In each package type, you include a package variable

with a string data type for use by the CDC components to reflect the current state of processing.

As shown in Figure 6-12, you begin the control flow with a CDC Control Task to mark the start of

an initial load or to establish the Log Sequence Number (LSN) range to process during a trickle-feed

package execution. You then add a Data Flow Task that contains CDC components to perform the

processing of the initial load or changed data. (We describe the components to use in this Data Flow

Task later in this section.) Then you complete the control flow with another CDC Control Task to mark

the end of the initial load or the successful processing of the LSN range for a trickle-feed package.

FIGURE 6-12 Trickle-feed control flow for change data capture processing

Figure 6-13 shows the configuration of the CDC Control Task for the beginning of a trickle-feed

package. You use an ADO.NET Connection Manager to define the connection to a SQL Server database

for which change data capture is enabled. You also specify a CDC control operation and the

name of the CDC state variable. Optionally, you can use a table to persist the CDC state. If you do not

use a table for state persistency, you must include logic in the package to write the state to a persistent

store when change data processing completes and to read the state before beginning the next

execution of change data processing.

FIGURE 6-13 CDC Control Task Editor for retrieving the current LSN range for change data to process

CDC Data Flow

To process changed data, you begin a Data Flow Task with a CDC Source and a CDC Splitter, as

shown in Figure 6-14. The CDC Source extracts the changed data according to the specifications

defined by the CDC Control Task, and then the CDC Splitter evaluates each row to determine whether

the changed data is a result of an insert, update, or delete operation. Then you add data flow

components

to the each output of the CDC Splitter for downstream processing.

FIGURE 6-14 CDC Data Flow Task for processing changed data

CDC Source

In the CDC Source editor (shown in Figure 6-15), you specify an ADO.NET connection manager for the

database and select a table and a corresponding capture instance. Both the database and table must

be configured for change data capture in SQL Server. You also select a processing mode to control

whether to process all change data or net changes only. The CDC state variable must match the

variable you define in the CDC Control Task that executes prior to the Data Flow Task containing the

CDC Source. Last, you can optionally select the Include Reprocessing Indicator Column check box to

identify reprocessed rows for separate handling of error conditions.

FIGURE 6-15 CDC Source editor for extracting change data from a CDC-enabled table

CDC Splitter

The CDC Splitter uses the value of the _$operation column to determine the type of change

associated

with each incoming row and assigns the row to the applicable output: InsertOutput,

UpdateOutput,

or DeleteOutput. You do not configure this transformation. Instead, you add downstream

data flow components to manage the processing of each output separately.

Flexible Package Design

During the initial development stages of a package, you might find it easiest to work with hard-coded

values in properties and expressions to ensure that your logic is correct. However, for maximum

flexibility,

you should use variables. In this section, we review the enhancements for variables and

expressions—the cornerstones of flexible package design.

Variables

A common problem for developers when adding a variable to a package has been the scope

assignment.

If you inadvertently select a task in the control flow designer and then add a new variable

in the Variables window, the variable is created within the scope of that task and cannot be changed.

In these cases, you were required to delete the variable, clear the task selection on the design surface,

and then add the variable again within the scope of the package.

Integration Services now creates new variables with scope set to the package by default. To change

the variable scope, follow these steps:

1. In the Variables window, select the variable to change and then click the Move Variable button

in the Variables toolbar (the second button from the left), as shown here:

2. In the Select New Scope dialog box, select the executable to have scope—the package, an

event handler, container, or task—as shown here, and click OK:

Expressions

The expression enhancements in this release address a problem with expression size limitations and

introduce new functions in the SQL Server Integration Services Expression Language.

Expression Result Length

Prior to the current version of Integration Services, if an expression result had a data type of DT_WSTR

or DT_STR, any characters above a 4000-character limit would be truncated. Furthermore, if an

expression

contained an intermediate step that evaluated a result exceeding this 4000-character limit,

the intermediate result would similarly be truncated. This limitation is now removed.

New Functions

The SQL Server Integration Services Expression Language now has four new functions:

¦ ¦ LEFT You can now more easily return the leftmost portion of a string rather than use the

SUBSTRING function:

LEFT(character_expression,number)

¦ ¦ REPLACENULL You can use this function to replace NULL values in the first argument with

the expression specified in the second expression:

REPLACENULL(expression, expression)

¦ ¦ TOKEN This function allows you to return a substring by using delimiters to separate a string

into tokens and then specifying which occurrence to return:

TOKEN(character_expression, delimiter_string, occurrence)

¦ ¦ TOKENCOUNT This function uses delimiters to separate a string into tokens and then

returns

the count of tokens found within the string:

TOKENCOUNT(character_expression, delimiter_string)

Deployment Models

Up to now in this chapter, we have explored the changes to the package-development process in

SSDT, which have been substantial. Another major change to Integration Services is the concept of

deployment models.

Supported Deployment Models

The latest version of Integration Services supports two deployment models:

¦ ¦ Package deployment model The package deployment model is the deployment model

used in previous versions of Integration Services, in which the unit of deployment is an

individual

package stored as a DTSX file. A package can be deployed to the file system or to

the MSDB database in a SQL Server database instance. Although packages can be deployed

as a group and dependencies can exist between packages, there is no unifying object in

Integration

Services that identifies a set of related packages deployed using the package

model. To modify properties of package tasks at runtime, which is important when running

a package in different environments such as development or production, you use configurations

saved as DTSCONFIG files on the file system. You use either the DTExec or the DTExecUI

utilities

to execute a package on the Integration Services server, providing arguments on the

command line or in the graphical interface when you want to override package property values

at run time manually or by using configurations.

¦ ¦ Project deployment model With this deployment model, the unit of deployment is a

project, stored as an ISPAC file, which in turn is a collection of packages and parameters. You

deploy the project to the Integration Services catalog, which we describe in a separate section

of this chapter. Instead of configurations, you use parameters (as described later in the

“Parameters” section) to assign values to package properties at runtime. Before executing

a package, you create an execution object in the catalog and, optionally, assign parameter

values or environment references to the execution object. When ready, you start the execution

object by using a graphical interface in SQL Server Management Studio by executing a stored

procedure or by running managed code.

In addition to the characteristics just described, there are additional differences between

the package

deployment model and the project deployment model. Table 6-1 compares these differences.

TABLE 6-1 Deployment Model Comparison

Characteristic

Package Deployment Model

Project Deployment Model

Unit of deployment

Package

Project

Deployment location

File system or MSDB database

Integration Services catalog

Run-time property value assignment

Configurations

Parameters

Environment-specific values

for use in property values

Configurations

Environment variables

Package validation

Just before execution using:

• DTExec

• Managed code

Independent of execution using:

• SQL Server Management Studio

interface

• Stored procedure

• Managed code

Package execution

DTExec

DTExecUI

SQL Server Management Studio

interface

Stored procedure

Managed code

Logging

Configure log provider or

implement custom logging

No configuration required

Scheduling

SQL Server Agent job

SQL Server Agent job

CLR integration

Not required

Required

When you create a new project in SSDT, the project is by default established as a project

deployment

model. You can use the Convert To Package Deployment Model command on the Project

menu (or choose it from the context menu when you right-click the project in Solution Explorer)

to switch to the package deployment model. The conversion works only if your project is compatible

with the package deployment model. For example, it cannot use features that are exclusive to

the project deployment model, such as parameters. After conversion, Solution Explorer displays

an additional

label after the project name to indicate the project is now configured as a package

deployment

model, as shown in Figure 6-16. Notice that the Parameters folder is no longer available

in the project, while the Data Sources folder is now available in the project.

FIGURE 6-16 Package deployment model

Tip You can reverse the process by using the Project menu, or the project’s context

menu in Solution Explorer, to convert a package deployment model project to a project deployment

model.

Project Deployment Model Features

In this section, we provide an overview of the project deployment model features to help you

understand

how you use these features in combination to manage deployed projects. Later in this

chapter, we explain each of these features in more detail and provide links to additional information

available online.

Although you can continue to work with the package deployment model if you prefer, the primary

advantage of using the new project deployment model is the improvement in package management

across multiple environments. For example, a package is commonly developed on one server,

tested on a separate server, and eventually implemented on a production server. With the package

deployment model, you can use a variety of techniques to provide connection strings for the correct

environment at runtime, each of which requires you to create at least one configuration file and,

optionally, maintain SQL Server tables or environment variables. Although this approach is flexible,

it can also be confusing and prone to error. The project deployment model continues to separate

run-time values from the packages, but it uses object collections in the Integration Services catalog to

store these values and to define relationships between packages and these object collections, known

as parameters, environments, and environment variables.

¦ ¦ Catalog The catalog is a dedicated database that stores packages and related configuration

information accessed at package runtime. You can manage package configuration and execution

by using the catalog’s stored procedures and views or by using the graphical interface in

SQL Server Management Studio.

¦ ¦ Parameters As Table 6-1 shows, the project deployment model relies on parameters to

change task properties during package execution. Parameters can be created within a project

scope or within a package scope. When you create parameters within a project scope, you

apply a common set of parameter values across all the packages contained in the project. You

can then use parameters in expressions or tasks, much the same way that you use variables.

¦ ¦ Environments Each environment is a container of variables you associate with a package at

runtime. You can create multiple environments to use with a single package, but the package

can use variables from only one environment during execution. For example, you can create

environments for development, test, and production, and then execute a package using one

of the applicable environments.

¦ ¦ Environment variables An environment variable contains a literal value that Integration

Services assigns to a parameter during package execution. After deploying a project, you can

associate a parameter with an environment variable. The value of the environment variable

resolves during package execution.

Project Deployment Workflow

The project deployment workflow includes not only the process of converting design-time objects in

SSDT into database objects stored in the Integration Services catalog, but also the process of retrieving

database objects from the catalog to update a package design or to use an existing package as a

template for a new package. To add a project to the catalog or to retrieve a project from the catalog,

you use a project-deployment file that has an ISPAC file extension. There are four stages of the

project

deployment workflow in which the ISPAC file plays a role: build, deploy, import, and convert.

In this section, we review each of these stages.

Build

When you use the project deployment model for packages, you use SSDT to develop one or more

packages as part of an Integration Services project. In preparation for deployment to the catalog,

which serves as a centralized repository for packages and related objects, you build the Integration

Services project in SSDT to produce an ISPAC file. The ISPAC file is the project deployment file that

contains project information, all packages in the Integration Services project, and parameters.

Before performing the build, there are two additional tasks that might be necessary:

¦ ¦ Identify entry-point package If one of the packages in the project is the package that

triggers the execution of the other packages in the project, directly or indirectly, you should

flag that package as an entry-point package. You can do this by right-clicking the package in

Solution Explorer and selecting Entry-Point Package. An administrator uses this flag to identify

the package to start when a package contains multiple projects.

¦ ¦ Create project and package parameters You use project-level or package-level

parameters

to provide values for use in tasks or expressions at runtime, which you learn

more about how to do later in this chapter in the “Parameters” section. In SSDT, you assign

parameter

values to use as a default. You also mark a parameter as required, which prevents a

package from executing until you assign a value to the variable.

During the development process in SSDT, you commonly execute a task or an entire package

within

SSDT to test results before deploying the project. SSDT creates an ISPAC file to hold the

information

required to execute the package and stores it in the bin folder for the Integration Services

project. When you finish development and want to prepare the ISPAC file for deployment, use the

Build menu or press F5.

Deploy

The deployment process uses the ISPAC file to create database objects in the catalog for the project,

packages, and parameters, as shown in Figure 6-17. To do this, you use the Integration Services

Deployment

Wizard, which prompts you for the project to deploy and the project to create or update

as part of the deployment. You can also provide literal values or specify environment variables as

default parameter values for the current project version. These parameter values that you provide in

the wizard are stored in the catalog as server defaults for the project, and they override the default

parameter values stored in the package.

SQL Server

Database Engine

(Source)

SQL Server

Database Engine

(Destination)

Integration Services

Catalog

Integration Services

Catalog

Project Project

Packages Packages

Project

Deployment File

Deployment

Wizard

FIGURE 6-17 Deployment of the ISPAC file to the catalog

You can launch the wizard from within SSDT by right-clicking the project in Solution Explorer and

selecting Deploy. However, if you have an ISPAC file saved to the file system, you can double-click the

file to launch the wizard.

Import

When you want to update a package that has already been deployed or to use it as basis for a new

package, you can import a project into SSDT from the catalog or from an ISPAC file, as shown in

Figure 6-18. To import a project, you use the Integration Services Import Project Wizard, which is

available in the template list when you create a new project in SSDT.

SQL Server

Database Engine

(Source)

SQL Server

Business Intelligence Development Studio

Integration Services

Catalog

Project

Packages

Project

Packages

Project

Deployment File

Import Project

Wizard

FIGURE 6-18 Import a project from the catalog or an ISPAC file

Convert

If you have legacy packages and configuration files, you can convert them to the latest version of

Integration Services, as shown in Figure 6-19. The Integration Services Project Conversion Wizard is

available in both SSDT and in SQL Server Management Studio. Another option is to use the Integration

Services Package Upgrade Wizard available on the Tools page of the SQL Server Installation

Center.

Legacy Packages

and Configurations

SQL Server

2005 Package

Configurations

Project

Deployment File

Migration

Wizard

SQL Server

2005 Package

FIGURE 6-19 Convert existing DTSX files and configurations to an ISPAC file

Note You can use the Conversion Wizard to migrate packages created using SQL Server

2005 Integration Services and later. If you use SQL Server Management Studio, the original

DTSX files are not modified, but used only as a source to produce the ISPAC file containing

the upgraded packages.

In SSDT, open a package project, right-click the project in Solution Explorer, and select Convert To

Project Deployment Model. The wizard upgrades the DTPROJ file for the project and the DTSX files

for the packages.

The behavior of the wizard is different in SQL Server Management Studio. There you right-click the

Projects node of the Integration Services catalog in Object Explorer and select Import Packages. The

wizard prompts you for a destination location and produces an ISPAC file for the new project and the

upgraded packages.

Regardless of which method you use to convert packages, there are some common steps that

occur

as packages are upgraded:

¦ ¦ Update Execute Package tasks If a package in a package project contains an Execute

Package

task, the wizard changes the external reference to a DTSX file to a project reference

to a package contained within the same project. The child package must be in the same

package

project you are converting and must be selected for conversion in the wizard.

¦ ¦ Create parameters If a package in a package project uses a configuration, you can choose

to convert the configuration to parameters. You can add configurations belonging to other

projects to include them in the conversion process. Additionally, you can choose to remove

configurations from the upgraded packages. The wizard uses the configurations to prompt

you for properties to convert to parameters, and it also requires you to specify project scope

or package scope for each parameter.

¦ ¦ Configure parameters The Conversion Wizard allows you to specify a server value for each

parameter and whether to require the parameter at runtime.

Parameters

As we explained in the previous section, parameters are the replacement for configurations in legacy

packages, but only when you use the project deployment model. The purpose of configurations was

to provide a way to change values in a package at runtime without requiring you to open the package

and make the change directly. You can establish project-level parameters to assign a value to one

or more properties across multiple packages, or you can have a package-level parameter when you

need to assign a value to properties within a single package.

Project Parameters

A project parameter shares its values with all packages within the same project. To create a project

parameter in SSDT, follow these steps:

1. In Solution Explorer, double-click Project.params.

2. Click the Add Parameter button on the toolbar in the Project.params window.

3. Type a name for the parameter in the Name text box, select a data type, and specify a value

for the parameter as shown here. The parameter value you supply here is known as the design

default value.

Note The parameter value is a design-time value that can be overwritten during

or

after deployment to the catalog. You can use the Add Parameters To Configuration

button on the toolbar (the third button from the left) to add selected

parameters to

Visual Studio project configurations, which is useful for testing package executions

under

a variety of conditions.

4. Save the file.

Optionally, you can configure the following properties for each parameter:

¦ ¦ Sensitive By default, this property is set to False. If you change it to True, the parameter

value is encrypted when you deploy the project to the catalog. If anyone attempts to view the

parameter value in SQL Server Management Studio or by accessing Transact-SQL views, the

parameter value will display as NULL. This setting is important when you use a parameter to

set a connection string property and the value contains specific credentials.

¦ ¦ Required By default, this property is also set to False. When the value is True, you must

configure

a parameter value during or after deployment before you can execute the package.

The Integration Services engine will ignore the parameter default value that you specify on

this screen when the Required property is True and deploy the package to the catalog.

¦ ¦ Description This property is optional, but it allows you to provide documentation to an

administrator responsible for managing packages deployed to the catalog.

Package Parameters

Package parameters apply only to the package in which they are created and cannot be shared with

other packages. The center tab in the package designer allows you to access the Parameters window

for your package. The interface for working with package parameters is identical to the project parameters

interface.

Parameter Usage

After creating project or package parameters, you are ready to implement the parameters in your

package much like you implement variables. That is, anywhere you can use variables in expressions

for tasks, data flow components, or connection managers, you can also use parameters.

As one example, you can reference a parameter in expressions, as shown in Figure 6-20. Notice the

parameter appears in the Variables And Parameters list in the top left pane of the Expression Builder.

You can drag the parameter to the Expression text box and use it alone or as part of a more complex

expression. When you click the Evaluate Expression button, you can see the expression result based

on the design default value for the parameter.

FIGURE 6-20 Parameter usage in an expression

Note This expression uses a project parameter that has a prefix of $Project. To create an

expression that uses a package parameter, the parameter prefix is $Package.

You can also directly set a task property by right-clicking the task and selecting Parameterize on

the context menu. The Parameterize dialog box displays as shown in Figure 6-21. You select a property,

and then choose whether to create a new parameter or use an existing parameter. If you create

a new parameter, you specify values for each of the properties you access in the Parameters window.

Additionally, you must specify whether to create the parameter within package scope or project

scope.

FIGURE 6-21 Parameterize task dialog box

Post-Deployment Parameter Values

The design default values that you set for each parameter in SSDT are typically used only to supply a

value for testing within the SSDT environment. You can replace these values during deployment by

specifying server default values when you use the Deployment Wizard or by configuring execution

values when creating an execution object for deployed projects.

Figure 6-22 illustrates the stage at which you create each type of parameter value. If a parameter

has no execution value, the Integration Services engine uses the server default value when executing

the package. Similarly, if there is no server default value, package execution uses the design default

value. However, if a parameter is marked as required, you must provide either a server default value

or an execution value.

Design Deployment

Project

Package

Parameter

“pkgOptions”

Package

Design Default Value = 1

Execution

Project

Package

Parameter

“pkgOptions”

Package

Project

Package

Parameter

“pkgOptions”

Package

Server Default Value = 3

Design Default Value = 1

Exception Value = 5

Server Default Value = 3

Design Default Value = 1

FIGURE 6-22 Parameter values by stage

Note A package will fail when the Integration Services engine cannot resolve a parameter

value. For this reason, it is recommended that you validate projects and packages as

described

in the “Validation” section of this chapter.

Server Default Values

Server default values can be literal values or environment variable references (explained later in this

chapter), which in turn are literal values. To configure server defaults in SQL Server Management

Studio,

you right-click the project or package in the Integration Services node in Object Explorer,

select

Configure, and change the Value property of the parameter, as shown in Figure 6-23. This

server

default value persists even if you make changes to the design default value in SSDT and

redeploy

the project.

FIGURE 6-23 Server default value configuration

Execution Parameter Values

The execution parameter value applies only to a specific execution of a package and overrides all other

values. You must explicitly set the execution parameter value by using the catalog.set_ execution_

parameter_value stored procedure. There is no interface available in SQL Server Management Studio

to set an execution parameter value.

set_execution_parameter_value [ @execution_id = execution_id

, [ @object_type = ] object_type

, [ @parameter_name = ] parameter_name

, [ @parameter_value = ] parameter_value

To use this stored procedure, you must supply the following arguments:

¦ ¦ execution_id You must obtain the execution_id for the instance of the execution. You can

use the catalog.executions view to locate the applicable execution_id.

¦ ¦ object_type The object type specifies whether you are setting a project parameter or a

package parameter. Use a value of 20 for a project parameter and a value of 30 for a package

parameter.

¦ ¦ parameter_name The name of the parameter must match the parameter stored in the

catalog.

¦ ¦ parameter_value Here you provide the value to use as the execution parameter value.

Integration Services Catalog

The Integration Services catalog is a new feature to support the centralization of storage and the

administration of packages and related configuration information. Each SQL Server instance can

host only one catalog. When you deploy a project using the project deployment model, the project

and its components are added to the catalog and, optionally, placed in a folder that you specify in

the Deployment

Wizard. Each folder (or the root level if you choose not to use folders) organizes its

contents

into two groups: projects and environments, as shown in Figure 6-24.

SQL Server Database Engine

Integration Services Catalog

Environment

Reference

Folder

Project

Project

Parameter

Package

Parameter

Package

Environment

Environment

Variable

FIGURE 6-24 Catalog database objects

Catalog Creation

Installation of Integration Services on a server does not automatically create the catalog. To do this,

follow these steps:

1. In SQL Server Management Studio, connect to the SQL Server instance, right-click the

Integration

Services Catalogs folder in Object Explorer, and select Create Catalog.

2. In the Create Catalog dialog box, you can optionally select the Enable Automatic Execution Of

Integration Services Stored Procedure At SQL Server Startup check box. This stored procedure

performs a cleanup operation when the service restarts and adjusts the status of packages

that were executing when the service stopped.

3. Notice that the catalog database name cannot be changed from SSISDB, as shown in the

following

figure, so the final step is to provide a strong password and then click OK. The

password

creates a database master key that Integration Services uses to encrypt sensitive

data stored in the catalog.

After you create the catalog, you will see it appear twice as the SSISDB database in Object Explorer.

It displays under both the Databases node as well as the Integration Services node. In the Databases

node, you can interact with it as you would any other database, using the interface to explore

database

objects. You use the Integration Services node to perform administrative tasks.

Note In most cases, multiple options are available for performing administrative tasks with

the catalog. You can use the graphical interface by opening the applicable dialog box for a

selected catalog object, or you can use Transact-SQL views and stored procedures to view

and modify object properties. For more information about the Transact-SQL API, see

http://msdn.microsoft.com/en-us/library/ff878003(v=SQL.110).aspx. You can also use

Windows PowerShell to perform administrative tasks by using the SSIS Catalog Managed

Object Model. Refer to http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.

integrationservices(v=sql.110).aspx for details about the API.

Catalog Properties

The catalog has several configurable properties. To access these properties, right-click SSISDB under

the Integration Services node and select Properties. The Catalog Properties dialog box, as shown in

Figure 6-25, displays several properties.

FIGURE 6-25 Catalog Properties dialog box

Encryption

Notice in Figure 6-25 that the default encryption algorithm is AES_256. If you put the SSISDB database

in single-user mode, you can choose one of the other encryption algorithms available:

¦ ¦ DES

¦ ¦ TRIPLE_DES

¦ ¦ TRIPLE_DES_3KEY

¦ ¦ DESX

¦ ¦ AES_128

¦ ¦ AES_192

Integration Services uses encryption to protect sensitive parameter values. When anyone uses the

SQL Server Management Studio interface or the Transact-SQL API to query the catalog, the parameter

value displays only a NULL value.

Operations

Operations include activities such as package execution, project deployment, and project validation,

to name a few. Integration Services stores information about these operations in tables in the catalog.

You can use the Transact-SQL API to monitor operations, or you can right-click the SSISDB database

on the Integration Services node in Object Explorer and select Active Operations. The Active

Operations

dialog box displays the operation identifier, its type, name, the operation start time,

and the caller of the operation. You can select an operation and click the Stop button to end the operation.

Periodically, older data should be purged from these tables to keep the catalog from growing

unnecessarily large. By configuring the catalog properties, you can control the frequency of the SQL

Server Agent job that purges the stale data by specifying how many days of data to retain. If you

prefer, you can disable the job.

Project Versioning

Each time you redeploy a project with the same name to the same folder, the previous version

remains

in the catalog until ten versions are retained. If necessary, you can restore a previous version

by following these steps:

1. In Object Explorer, locate the project under the SSISDB node.

2. Right-click the project, and select Versions.

3. In the Project Versions dialog box, shown here, select the version to restore and click the

Restore

To Selected Version button:

4. Click Yes to confirm, and then click OK to close the information message box. Notice the

selected version is now flagged as the current version, and that the other version remains

available as an option for restoring.

You can modify the maximum number of versions to retain by updating the applicable

catalog

property. If you increase this number above the default value of ten, you should continually

monitor the size of the catalog database to ensure that it does not grow too large. To manage the

size of the catalog, you can also decide whether to remove older versions periodically with a SQL

Server agent job.

Environment Objects

After you deploy projects to the catalog, you can create environments to work in tandem with

parameters to change parameter values at execution time. An environment is a collection of environment

variables. Each environment variable contains a value to assign to a parameter. To connect an

environment to a project, you use an environment reference. Figure 6-26 illustrates the relationship

between parameters, environments, environment variables, and environment references.

Integration Services Catalog

Folder

Project

Project

Parameter

“p1”

Package

Parameter

“p2”

Package

Environment

Environment

Variable

“ev1”

Environment

Variable

“ev2”

Environment

Reference

FIGURE 6-26 Environment objects in the catalog

Environments

One convention you can use is to create one environment for each server you will use for package

execution. For example, you might have one environment for development, one for testing, and one

for production. To create a new environment using the SQL Server Management Studio interface,

follow

these steps:

1. In Object Explorer, expand the SSISDB node and locate the Environments folder that

corresponds

to the Projects folder containing your project.

2. Right-click the Environments folder, and select Create Environment.

3. In the Create Environment dialog box, type a name, optionally type a description, and click

OK.

Environment Variables

For each environment, you can create a collection of environment variables. The properties you

configure

for an environment variable are the same ones you configure for a parameter, which is

understandable when you consider that you use the environment variable to replace the parameter

value at runtime. To create an environment variable, follow these steps:

1. In Object Explorer, locate the environment under the SSISDB node.

2. Right-click the environment, and select Properties to open the Environment Properties dialog

box.

3. Click Variables to display the list of existing environment variables, if any, as shown here:

4. On an empty row, type a name for the environment variable in the Name text box, select a

data type in the Type column, type a description (optional), type a value in the Value column

for the environment variable, and select the Sensitive check box if you want the value to be

encrypted in the catalog. Continue adding environment variables on this page, and click OK

when you’re finished.

5. Repeat this process by adding the same set of environment variables to other environments

you intend to use with the same project.

Environment References

To connect environment variables to a parameter, you create an environment reference. There are

two types of environment references: relative and absolute. When you create a relative environment

reference, the parent folder for the environment folder must also be the parent folder for the project

folder. If you later move the package to another without also moving the environment, the package

execution will fail. An alternative is to use an absolute reference, which maintains the relationship

between the environment and the project without requiring them to have the same parent folder.

The environment reference is a property of the project. To create an environment reference, follow

these steps:

1. In Object Explorer, locate the project under the SSISDB node.

2. Right-click the project, and select Configure to open the Configure <Project> dialog box.

3. Click References to display the list of existing environment references, if any.

4. Click the Add button and select an environment in the Browse Environments dialog box. Use

the Local Folder node for a relative environment reference, or use the SSISDB node for an

absolute environment reference.

5. Click OK twice to create the reference. Repeat steps 4 and 5 to add reference for all other applicable

environments.

6. In the Configure <Project> dialog box, click Parameters to switch to the parameters page.

7. Click the ellipsis button to the right of the Value text box to display the Set Parameter Value

dialog box, select the Use Environment Variable option, and select the applicable variable in

the drop-down list, as shown here:

8. Click OK twice.

You can create multiple references for a project, but only one environment will be active during

package execution. At that time, Integration Services will evaluate the environment variable based on

the environment associated with the current execution instance as explained in the next section.

Administration

After the development and deployment processes are complete, it’s time to become familiar with the

administration tasks that enable operations on the server to keep running.

Validation

Before executing packages, you can use validation to verify that projects and packages are likely

to run successfully, especially if you have configured parameters to use environment variables. The

validation process ensures that server default values exist for required parameters, that environment

references are valid, and that data types for parameters are consistent between project and package

configurations and their corresponding environment variables, to name a few of the validation checks.

To perform the validation, right-click the project or package in the catalog, click Validate, and

select

the environments to include in the validation: all, none, or a specific environment. Validation

occurs asynchronously, so the Validation dialog box closes while the validation processes. You can

open the Integration Services Dashboard report to check the results of validation. Your other options

are to right-click the SSISDB node in Object Explorer and select Active Operations or to use of the

Transact-SQL API to monitor an executing package.

Package Execution

After deploying a project to the catalog and optionally configuring parameters and environment

references, you are ready to prepare your packages for execution. This step requires you to create

a SQL Server object called an execution. An execution is a unique combination of a package and its

corresponding

parameter values, whether the values are server defaults or environment references.

To configure and start an execution instance, follow these steps:

1. In Object Explorer, locate the entry-point package under the SSISDB node.

2. Right-click the project, and select Execute to open the Execute Package dialog box, shown

here:

3. Here you have two choices. You can either click the ellipsis button to the right of the value and

specify a literal execution value for the parameter, or you can select the Environment check

box at the bottom of the dialog box and select an environment in the corresponding dropdown

list.

You can continue configuring the execution instance by updating properties on the Connection

Managers tab and by overriding property values and configuring logging on the Advanced tab. For

more information about the options available in this dialog box, see http://msdn.microsoft.com/en-us

/library/hh231080(v=SQL.110).aspx.

When you click OK to close the Execute Package dialog box, the package execution begins.

Because

package execution occurs asynchronously, the dialog box does not need to stay open

during execution. You can use the Integration Services Dashboard report to monitor the execution

status, or right-click the SSISDB node and select Active Operations. Another option is the use of the

Transact-

SQL API to monitor an executing package.

More often, you will schedule package execution by creating a Transact-SQL script that starts

execution

and save the script to a file that you can then schedule using a SQL Server agent job. You

add a job step using the Operating System (CmdExec) step type, and then configure the step to use

the sqlcmd.exe utility and pass the package execution script to the utility as an argument. You run

the job using the SQL Server Agent service account or a proxy account. Whichever account you use, it

must have permissions to create and start executions.

Logging and Troubleshooting Tools

Now that Integration Services centralizes package storage and executions on the server and has

access to information generated by operations, server-based logging is supported and operations

reports are available in SQL Server Management Studio to help you monitor activity on the server and

troubleshoot problems when they occur.

Package Execution Logs

In legacy Integration Services packages, there are two options you can use to obtain logs during

package execution. One option is to configure log providers within each package and associate log

providers with executables within the package. The other option is to use a combination of Execute

SQL statements or script components to implement a custom logging solution. Either way, the steps

necessary to enable logging are tedious in legacy packages.

With no configuration required, Integration Services stores package execution data in the

[catalog].[

executions] table. The most important columns in this table include the start and end times

of package execution, as well as the status. However, the logging mechanism also captures information

related to the Integration Services environment, such as physical memory, the page file size, and

available CPUs. Other tables provide access to parameter values used during execution, the duration

of each executable within a package, and messages generated during package execution. You can

easily write ad hoc queries to explore package logs or build your own custom reports using Reporting

Services for ongoing monitoring of the log files.

Note For a thorough walkthrough of the various tables in which package execution log

data is stored, see “SSIS Logging in Denali,” a blog post by Jamie Thomson at

http://sqlblog.com/blogs/jamie_thomson/archive/2011/07/16/ssis-logging-in-denali.aspx.

Data Taps

A data tap is similar in concept to a data viewer, except that it captures data at a specified point in

the pipeline during package execution outside of SSDT. You can use the T-SQL stored procedure

catalog.

add_data_tap to tap into the data flow during execution if the package has been deployed

to SSIS. The captured data from the data flow is stored in a CSV file you can review after package

execution

completes. No changes to your package are necessary to use this feature.

Reports

Before you build custom reports from the package execution log tables, review the built-in

reports

now available in SQL Server Management Studio for Integration Services. These reports provide

information

on package execution results for the past 24 hours (as shown in Figure 6-27),

performance,

and error messages from failed package executions. Hyperlinks in each report allow

you to drill through from summary to detailed information to help you diagnose package execution problems.

FIGURE 6-27 Integration Services operations dashboard.

To view the reports, you right-click the SSISDB node in Object Explorer, point to Reports, point to

Standard Reports, and then choose from the following list of reports:

¦ ¦ All Executions

¦ ¦ All Validations

¦ ¦ All Operations

¦ ¦ Connections

Security

Packages and related objects are stored securely in the catalog using encryption. Only members of

the new SQL Server database role ssis_admin or members of the existing sysadmin role have permissions

to all objects in the catalog. Members of these roles can perform operations such as creating

the catalog, creating folders in the catalog, and executing stored procedures, to name a few.

Members of the administrative roles delegate administrative permissions to users who need

to manage a specific folder. Delegation is useful when you do not want to give these users access

to the higher privileged roles. To give a user folder-level access, you grant the MANAGE_OBJECT_PERMISSIONS

permission to the user.

For general permissions management, open the Properties dialog box for a folder (or any other

securable object) and go to the Permissions page. On that page, you can select a security principal

by name and then set explicit Grant or Deny permissions as appropriate. You can use this method to

secure folders, projects, environments, and operations.

Package File Format

Although legacy packages stored as DTSX files are formatted as XML, their structure is not compatible

with differencing tools and source-control systems you might use to compare packages. In the

current

version of Integration Services, the package file format is pretty-printed, with properties

formatted

as attributes rather than as elements. (Pretty-printing is the enhancement of code with

syntax conventions for easier viewing.) Moreover, attributes are listed alphabetically and attributes

configured with default values have been eliminated. Collectively, these changes not only help you

more easily locate information in the file, but you can more easily compare packages with automated

tools and more reliably merge packages that have no conflicting changes.

Another significant change to the package file format is the replacement of the meaningless

numeric lineage identifiers with a refid attribute with a text value that represents the path to the

referenced object. For example, a refid for the first input column of an Aggregate transformation in a

data flow task called Data Flow Task in a package called Package looks like this:

Package\Data Flow Task\Aggregate.Inputs[Aggregate Input 1].Columns[LineTotal]

Last, annotations are no longer stored as binary streams. Instead, they appear in the XML file as

clear text. With better access to annotations in the file, the more likely it is that annotations can be

programmatically extracted from a package for documentation purposes.

141

CHAP TE R 7

Data Quality Services

The quality of data is a critical success factor for many data projects, whether for general business

operations or business intelligence. Bad data creeps into business applications as a result of user

entry, corruption during transmission, business processes, or even conflicting data standards across

data sources. The Data Quality Services (DQS) feature of Microsoft SQL Server 2012 is a set of

technologies

you use to measure and manage data quality through a combination of computer-assisted

and manual processes. When your organization has access to high-quality data, your business

process can operate more effectively and managers can rely on this data for better decision-making.

By centralizing

data quality management, you also reduce the amount of time that people spend

reviewing

and correcting data.

Data Quality Services Architecture

In this section, we describe the two primary components of DQS: the Data Quality Server and Data

Quality Client. The DQS architecture also includes components that are built into other SQL Server

2012 features. For example, Integration Services has the DQS Cleansing transformation you use to

apply data-cleansing rules to a data flow pipeline. In addition, Master Data Services supports DQS

matching so that you can de-duplicate data before adding it as master data. We explain more about

these components in the “Integration” section of this chapter. All DQS components can coexist on the

same server, or they can be installed on separate servers.

Data Quality Server

The Data Quality Server is the core component of the architecture that manages the storage of

knowledge and executes knowledge-related processes. It consists of a DQS engine and multiple

databases

stored in a local SQL Server 2012 instance. These databases contain knowledge bases,

stored procedures for managing the Data Quality Server and its contents, and data about cleansing,

matching, and data-profiling activities.

Installation of the Data Quality Server is a multistep process. You start by using SQL Server Setup

and, at minimum, selecting the Database Engine and Data Quality Services on the Feature Selection

page. Then you continue installation by opening the Data Quality Services folder in the Microsoft SQL

Server 2012 program group on the Start menu, and launching Data Quality Server Installer. A command

window opens, and a prompt appears for the database master key password. You must supply

142 PART 2 Business Intelligence Development

a strong password having at least eight characters and including at least one uppercase letter, one

lowercase letter, and one special character.

After you provide a valid password, installation of the Data Quality Server continues for several

minutes. In addition to creating and registering assemblies on the server, the installation process

creates

the following databases on a local instance of SQL Server 2012:

¦ ¦ DQS_MAIN As its name implies, this is the primary database for the Data Quality Server. It

contains the published knowledge bases as well as the stored procedures that support the

DQS engine. Following installation, this database also contains a sample knowledge base

called DQS data, which you can use to cleanse country data or data related to geographical

locations in the United States.

¦ ¦ DQS_PROJECTS This database is for internal use by the Data Quality Server to store data

related to managing knowledge bases and data quality projects.

¦ ¦ DQS_STAGING_DATA You can use this database as intermediate storage for data that

you want to use as source data for DQS operations. DQS can also use this database to store

processed

data that you can later export.

Before users can use client components with the Data Quality Server, a user with sysadmin

privileges

must create a SQL Server login for each user and map each user to the DQS_MAIN

database

using one of the following database roles created at installation of the Data Quality Server:

¦ ¦ dqs_administrator A user assigned to this role has all privileges available to the other

roles plus full administrative privileges, with the exception of adding new users. Specifically, a

member of this role can stop any activity or stop a process within an activity and perform any

configuration task using Data Quality Client.

¦ ¦ dqs_kb_editor A member of this role can perform any DQS activity except administration.

A user must be a member of this role to create or edit a knowledge base.

¦ ¦ dqs_kb_operator This role is the most limited of the database roles, allowing its members

only to edit and execute data quality projects and to view activity-monitoring data.

Important You must use SQL Server Configuration Manager to enable the TCP/IP protocol

for the SQL Server instance hosting the DQS databases before remote clients can connect

to the Data Quality Server.

Data Quality Client

Data Quality Client is the primary user interface for the Data Quality Server that you install as a

stand-

alone application. Business users can use this application to interactively work with data

quality

projects, such as cleansing or data profiling. Data stewards use Data Quality Client to create

or maintain

knowledge bases, and DQS administrators use it to configure and manage the Data Quality

Server.

CHAPTER 7 Data Quality Services 143

To install this application, use SQL Server Setup and select Data Quality Client on the Feature

Selection

page. It requires the Microsoft .NET Framework 4.0, which installs automatically if necessary.

Note If you plan to import data from Microsoft Excel, you must install Excel on the Data

Quality Client computer. DQS supports both 32-bit and 64-bit versions of Excel 2003, but it

supports only the 32-bit version of Excel 2007 or 2010 unless you save the workbook as an

XLS or CSV file.

If you have been assigned to one of the DQS database roles, or if you have sysadmin privileges

on the SQL Server instance hosting the Data Quality Server, you can open Data Quality Client, which

is found in the Data Quality Services folder of the Microsoft SQL Server 2012 program group on the

Start menu. You must then identify the Data Quality Server to establish the client-server connection. If

you click the Options button, you can select a check box to encrypt the connection.

After opening Data Quality Client, the home screen provides access to the following three types of

tasks:

¦ ¦ Knowledge Base Management You use this area of Data Quality Client to create a new

knowledge base, edit an existing knowledge base, use knowledge discovery to enhance a

knowledge base with additional values, or create a matching policy for a knowledge base.

¦ ¦ Data Quality Projects In this area, you create and run data quality projects to perform

data-cleansing or data-matching tasks.

¦ ¦ Administration This area allows you to view the status of knowledge base management

activities,

data quality projects, and DQS Cleansing transformations used in Integration

Services

packages. In addition, it provides access to configuration properties for the Data

Quality Server, logging, and reference data services.

Knowledge Base Management

In DQS, you create a knowledge base to store information about your data, including valid and invalid

values and rules to apply for validating and correcting data. You can generate a knowledge base from

sample data, or you can manually create one. You can reuse a knowledge base with multiple data

quality projects and enhance it over time with the output of cleansing and matching projects. You

can give responsibility for maintaining the knowledge base to data stewards. There are three activities

that you or data stewards perform using the Knowledge Base Management area of Data Quality

Client: Domain Management, Knowledge Discovery, and Matching Policy.

Domain Management

After creating a knowledge base, you manage its contents and rules through the Domain

Management

activity. A knowledge base is a logical collection of domains, with each domain

corresponding

to a single field. You can create separate knowledge bases for customers and products,

or you can combine these subject areas into a single knowledge base. After you create a domain, you

define trusted values, invalid values, and examples of erroneous data. In addition to this set of values,

you also manage properties and rules for a domain.

To prevent potential conflicts resulting from multiple users working on the same knowledge base

at the same time, DQS locks the knowledge base when you begin a new activity. To unlock an activity,

you must publish or discard the results of your changes.

Note If you have multiple users who have responsibility for managing knowledge, keep

in mind that only one user at a time can perform the Domain Management activity for a

single knowledge base. Therefore, you might consider using separate knowledge bases for

each area of responsibility.

DQS Data Knowledge Base

Before creating your own knowledge base, you can explore the automatically installed knowledge

base, DQS Data, to gain familiarity with the Data Quality Client interface and basic knowledge-base

concepts. By exploring DQS Data, you can learn about the types of information that a knowledge

base can contain.

To get started, open Data Quality Client and click the Open Knowledge Base button on the home

page. DQS Data displays as the only knowledge base on the Open Knowledge Base page. When

you select DQS Data in the list of existing knowledge bases, you can see the collection of domains

associated

with this knowledge base, as shown in Figure 7-1.

FIGURE 7-1 Knowledge base details for DQS Data

Choose the Domain Management activity in the bottom right corner of the window, and click

Next to explore the domains in this knowledge base. In the Domain list on the Domain Management

page, you choose a domain, such as Country/Region, and access separate tabs to view or change the

knowledge associated with that domain. For example, you can click on the Domain Values tab to see

how values that represent a specific country or region will be corrected when you use DQS to perform

data cleansing, as shown in Figure 7-2.

FIGURE 7-2 Domain values for the Country/Region domain

The DQS Data knowledge base, by default, contains only domain values. You can add other

knowledge

to make this data more useful for your own data quality projects. For example, you could

add domain rules to define conditions that identify valid data or create term-based relations to

define how to make corrections to terms found in a string value. More information about these other

knowledge

categories is provided in the “New Knowledge Base” section of this chapter.

Tip When you finish reviewing the knowledge base, click the Cancel button and click Yes

to confirm that you do not want to save your work.

New Knowledge Base

On the home page of Data Quality Client, click the New Knowledge Base button to start the process

of creating a new knowledge base. At a minimum, you provide a name for the knowledge base, but

you can optionally add a description. Then you must specify whether you want to create an empty

knowledge base or create your knowledge base from an existing one, such as DQS Data. Another

option is to create a knowledge base by importing knowledge from a DQS file, which you create by

using the export option from any existing knowledge base.

After creating the knowledge base, you select the Domain Management activity, and click Next so

that you can add one or more domains in the knowledge base. To add a new domain, you can create

the domain manually or import a domain from an existing knowledge base using the applicable

button

in the toolbar on the Domain Management page. As another option, you can create a domain

from the output of a data quality project.

Domain When you create a domain manually, you start by defining the following properties for the

domain (as shown in Figure 7-3):

¦ ¦ Domain Name The name you provide for the domain must be unique within the knowledge

base and must be 256 characters or less.

¦ ¦ Description You can optionally add a description to provide more information about the

contents of the domain. The maximum number of characters for the description is 2048.

¦ ¦ Data Type Your options here are String, Date, Integer, or Decimal.

¦ ¦ Use Leading Values When you select this option, the output from a cleansing or matching

data quality project will use the leading value in a group of synonyms. Otherwise, the output

will be the input value or its corrected value.

¦ ¦ Normalize String This option appears only when you select String as the Data Type. You

use it to remove special characters from the domain values during the data-processing stage

of knowledge discovery, data cleansing, and matching activities. Normalization might be

helpful for improving the accuracy of matches when you want to de-duplicate data because

punctuation

might be used inconsistently in strings that otherwise are a match.

¦ ¦ Format Output To When you output the results of a data quality project, you can apply

formatting to the domain values if you change this setting from None to one of the available

format options, which will depend on the domain’s data type. For example, you could choose

Mon-yyyy for a Date data type, #,##0.00 for a Decimal data type, #,##0 for an Integer data

type, or Capitalize for a String data type.

¦ ¦ Language This option applies only to String data types. You use it to specify the language to

apply when you enable the Speller.

¦ ¦ Enable Speller You can use this option to allow DQS to check the spelling of values

for a domain with a String data type. The Speller will flag suspected syntax, spelling, and

sentence-

structure errors with a red underscore when you are working on the Domain Values

or Term-

Based Relations tabs of the Domain Management activity, the Manage Domain

Values

step of the Knowledge Discovery activity, or the Manage And View Results step of the Cleansing

activity.

¦ ¦ Disable Syntax Error Algorithms DQS can check string values for syntax errors before

adding

each value to the domain during data cleansing.

FIGURE 7-3 Domain properties in the Create Domain dialog box

Domain Values After you create the domain, you can add knowledge to the domain by setting

domain values. Domain

values represent the range of possible values that might exist in a data source

for the current domain, including both correct and incorrect values. The inclusion of incorrect domain

values in a knowledge base allows you to establish rules for correcting those values during cleansing

activities.

You use buttons on the Domain Values tab to add a new domain value manually, import new valid

values from Excel, or import new string values with type Correct or Error from a cleansing data quality

project.

Tip To import data from Excel, you can use any of the following file types: XLS, XLSX, or

CSV. DQS attempts to add every value found in the file, but only if the value does not already

exist in the domain. DQS imports values in the first column as domain values and

values in other columns as synonyms, setting the value in the first column as the leading

value. DQS will not import a value if it violates a domain rule, if the data type does not

match that of the domain, or if the value is null.

When you add or import values, you can adjust the Type to one of the following settings for each

domain value:

¦ ¦ Correct You use this setting for domain values that you know are members of the domain

and have no syntax errors.

¦ ¦ Error You assign a Type of Error to a domain value that you know is a member of the domain

but has an incorrect value, such as a misspelling or undesired abbreviation. For example, in a

Product domain, you might add a domain value of Mtn 500 Silver 52 with the Error type and

include a corrected value of Mountain-500 Silver, 52. By adding known error conditions to the

domain, you can speed up the data cleansing process by having DQS automatically identify

and fix known errors, which allows you to focus on new errors found during data processing.

However, you can flag a domain value as an Error value without providing a corrected value.

¦ ¦ Invalid You designate a domain value as Invalid when it is not a member of the domain and

you have no correction to associate with it. For example, a value of United States would be an

invalid value in a Product domain.

After you publish your domain changes to the knowledge base and later return to the Domain

Values tab, you can see the relationship between correct and incorrect values in the domain values

list, as shown in Figure 7-4 for the product Adjustable Race. Notice also the underscore in the user

interface to identify a potential misspelling of “Adj Race.”

FIGURE 7-4 Domain value with Error state and corrected value

If there are multiple correct domain values that correspond to a single entity, you can organize

them as a group of synonyms by selecting them and clicking the Set Selected Domain Values As

Synonyms button in the Domain Values toolbar. Furthermore, you can designate one of the synonyms

as a leading value by right-clicking the value and selecting Set As Leading in the context menu.

When DQS encounters any of the other synonyms in a cleansing activity, it replaces the nonleading

synonym

values with the leading value. Figure 7-5 shows an example of synonyms for the leading

value Road-750 Black, 52 after the domain changes have been published.

FIGURE 7-5 Synonym values with designation of a leading value

Data Quality Client keeps track of the changes you make to domain values during your current

session.

To review your work, you click the Show/Hide The Domain Values Changes History Panel

button,

which is accessible by clicking the last button in the Domain Values toolbar.

Term-Based Relations Another option you have for adding knowledge to a domain is term-based

relations, which you use to make it easier to find and correct common occurrences in your domain

values. Rather than set up synonyms for variations of a domain value on the Domain Values tab, you

can define a list of string values and specify the corresponding Correct To value on the Term-Based

Relations tab. For example, in the product domain, you could have various products that contain the

abbreviation Mtn, such as Mtn-500 Silver, 40 and Mtn End Caps. To have DQS automatically correct

any occurrence of Mtn within a domain value string to Mountain, you can create a term-based

relation

by specifying a Value/Correct To pair, as shown in Figure 7-6.

FIGURE 7-6 Term-based relation with a value paired to a corrected value

Reference Data You can subscribe to a reference data service (RDS) that DQS uses to cleanse,

standardize,

and enhance your data. Before you can set the properties on the Reference Data tab

for your knowledge base, you must configure the RDS provider as described in the “Configuration”

section

of this chapter.

After you configure your DataMarket Account key in the Configuration area, you click the Browse

button on the Reference Data tab to select a reference data service provider for which you have a

current subscription. On the Online Reference Data Service Providers Catalog page, you map the

domain

to an RDS schema column. The letter M displays next to mandatory columns in the RDS

schema that you must include in the mapping.

Next, you configure the following settings for the RDS (as shown in Figure 7-7):

¦ ¦ Auto Correction Threshold Specify the threshold for the confidence score. During data

cleansing, DQS autocorrects records having a score higher than this threshold.

¦ ¦ Suggested Candidates Specify the number of candidates for suggested values to retrieve

from the reference data service.

¦ ¦ Min Confidence Specify the threshold for the confidence score for suggestions. During data

cleansing, DQS ignores suggestions with a score lower than this threshold.

FIGURE 7-7 Reference data service provider settings

Domain Rules You use domain rules to establish the conditions that determine whether a domain

value is valid. However, note that domain rules are not used to correct data.

After you click the Add A New Domain Rule button on the Domain Rules tab, you provide a name

for the rule and an optional description. Then you define the conditions for the rule in the Build A

Rule pane. For example, if there is a maximum length for a domain value, you can create a rule by

selecting Length Is Less Than Or Equal To in the rule drop-down list and then typing in the condition

value, as shown in Figure 7-8.

FIGURE 7-8 Domain rule to validate the length of domain values

You can create compound rules by adding multiple conditions to the rule and specifying whether

the conditions have AND or OR logic. As you build the rule, you can use the Run The Selected Domain

Rule On Test Data button. You must manually enter one or more values as test data for this procedure,

and then click the Test The Domain Rule On All The Terms button. You will see icons display to

indicate whether a value is correct, in error, or invalid. Then when you finish building the rule, you

click the Apply All Rules button to update the status of domain values according to the new rule.

Note You can temporarily disable a rule by clearing its corresponding Active check box.

Composite Domain Sometimes a complex string in your data source contains multiple terms, each

of which has different rules or different data types and thus requires separate domains. However, you

still need to validate the field as a whole. For example, with a product name like Mountain-500 Black,

40, you might want to validate the model of Mountain-500, the color Black, and the size 40 separately

to confirm the validity of the product name. To address this situation, you can create a composite

domain.

Before you can create a composite domain, you must have at least two domains in your knowledge

base. Begin the Domain Management activity, and click the Create A Composite Domain button on

the toolbar. Type a name for the composite domain, provide a description if you like, and then select

the domains to include in the composite domain.

Note After you add a domain to a composite domain, you cannot add it to a second composite

domain.

CD Properties You can always change, add, or remove domains in the composite domain on the CD

Properties tab. You can also select one of the following parsing methods in the Advanced section of

this tab:

¦ ¦ Reference Data If you map the composite domain to an RDS, you can specify that DQS use

the RDS for parsing each domain in the composite domain.

¦ ¦ In Order You use this setting when you want DQS to parse the field values in the same order

that the domains are listed in the composite domain.

¦ ¦ Delimiters When your field contains delimiters, you can instruct DQS to parse values based

on the delimiter you specify, such as a tab, comma, or space. When you use delimiters for

parsing, you have the option to use knowledge-base parsing. With knowledge-base parsing,

DQS identifies the domains for known values in the string and then uses domain knowledge to

determine how to add unknown values to other domains. For example, let’s say that you have

a field in the data source containing the string Mountain-500 Brown, 40. If DQS recognizes

Mountain-500 as a value in the Model domain and 40 as a value in the Size domain, but it

does not recognize Brown in the Color domain, it will add Brown to the Color domain.

Reference Data You use this tab to specify an RDS provider and map the individual domains of

a composite domain to separate fields of the provider’s RDS schema. For example, you might have

company information for which you want to validate address details. You combine the domains

Address,

City, State, and Zip to create a composite domain that you map to the RDS schema. You then

create a cleansing project to apply the RDS rules to your data.

CD Rules You use the CD Rules tab to define cross-domain rules for validating, correcting, and standardizing

domain values for a composite domain. A cross-domain rule uses similar conditions available

to domain rules, but this type of rule must hold true for all domains in the composite domain

rather than for a single domain. Each rule contains an If clause and a Then clause, and each clause

contains one or more conditions and is applicable to separate domains. For example, you can develop

a rule that invalidates a record if the Size value is not S, M, or L when the Model value begins with

Mountain-, as shown in Figure 7-9. As with domain rules, you can create rules with multiple conditions

and you can test a rule before finalizing its definition.

FIGURE 7-9 Cross-domain rule to validate corresponding size values in composite domain

Note If you create a Then clause that uses a definitive condition, DQS will not apply the

rule to both domain values and their synonyms. Definitive conditions are Value Is Equal To,

Value Is Not Equal To, Value Is In, or Value Is Not In. Furthermore, if you use the Value Is

Equal To condition for the Then clause, DQS not only validates data, but also corrects data

using this rule.

Value Relations After completing a knowledge-discovery activity, you can view the number of

occurrences

for each combination of values in a composite domain, as shown in Figure 7-10.

FIGURE 7-10 Value relations for a composite domain

Linked Domain You can use a linked domain to handle situations that a regular domain cannot

support. For example, if you create a data quality project for a data source that has two fields that use

the same domain values, you must set up two domains to complete the mapping of fields to domains.

Rather than maintain two domains with the same values, you can create a linked domain that inherits

the properties,

values, and rules of another domain.

One way to create a linked domain is to open the knowledge base containing the source domain,

right-click the domain, and select Create A Linked Domain. Another way to create a linked domain is

during an activity that requires you to map fields. You start by mapping the first field to a domain and

then attempt to map the second field to the same domain. Data Quality Client prompts you to create

a linked domain, at which time you provide a domain name and description.

After you create the linked domain, you can use the Domain Management activity to complete

tasks such as adding domain values or setting up domain rules by accessing either domain. The

changes are made automatically in the other domain. However, you can change the domain

properties

in the original domain only.

End of Domain Management Activity

DQS locks the knowledge base when you begin the Domain Management activity to prevent others

from making conflicting changes. If you cannot complete all the changes you need to make in a single

session, you click the Close button to save your work and keep the knowledge base locked. Click the

Finish button when your work is complete. Data Quality Client displays a prompt for you to confirm

the action to take. Click Publish to make your changes permanent, unlock the database, and make the

knowledge base available to others. Otherwise, click No to save your work, keep the database locked,

and exit the Domain Management activity.

Knowledge Discovery

As an alternative to manually adding knowledge to your knowledge base, you can use the Knowledge

Discovery activity to partially automate that process. You can perform this activity multiple times, as

often as needed, to add domain values to the knowledge base from one or more data sources.

To start, you open the knowledge base in the Domain Management area of Data Quality Client

and select the Knowledge Discovery activity. Then you complete a series of three steps: Map,

Discover,

and Manage Domain Values.

Map

In the Map step, you identify the source data you want DQS to analyze. This data must be available

on the Data Quality Server, either in a SQL Server table or view or in an Excel file. However, it does not

need to be from the same source that you intend to cleanse or de-duplicate with DQS.

The purpose of this step is to map columns in the source data to a domain or composite domain in

the knowledge base, as shown in Figure 7-11. You can choose to create a new domain or composite

domain at this point when necessary. When you finish mapping all columns, click the Next button to

proceed to the Discover step.

FIGURE 7-11 Mapping the source column to the domain for the Knowledge Discovery activity

Discover

In the Discover step, you use the Start button to begin the knowledge discovery process. As the

process

executes, the Discover step displays the current status for the following three phases of

processing

(as shown in Figure 7-12):

¦ ¦ Pre-processing Records During this phase, DQS loads and indexes records from the

source in preparation for profiling the data. The status of this phase displays as the number

of pre-processed records compared to the total number of records in the source. In addition,

DQS updates all data-profiling statistics except the Valid In Domain column. These statistics

are visible

in the Profiler tab and include the total number of records in the source, the total

number of values for the domain by field, and the number of unique values by field. DQS also

compares the values from the source with the values from the domain to determine which

values are found only in the source and identified as new.

¦ ¦ Running Domain Rules DQS uses the domain rules for each domain to update the Valid In

Domain column in the Profiler, and it displays the status as a percentage of completion.

¦ ¦ Running Discovery DQS analyzes the data to add to the Manage Domain Values step and

identifies syntax errors. As this phase executes, the current status displays as a percentage of

completion.