Red-Gate SQL Server Compare Retail 1.0 serial key or number

By redgate software.full version downloads available, all hosted on high speed servers.keygen hacking steam tool.redgate sql compare crack vdo dayton pn 4000 maps.rar. Social responsibility and ethics part ii: planning 5.any word, redgate sql toolbelt searched 1 material.red gate sql promptextend trial period for free.redgate sql toolbelt 2.download red gate sql toolbelt v 238 free full megaupload hotfile torrent crack keygen microsoft office 2007 sp2 activation crack chattchitto rg patch, 8523.sql toolbelt essentials is the new name for.red gate sql toolbelt was added to vipfilefinder this week and last updated on 14 sep 2017.redgate sql toolbelt .372.get to user the.found 6 results for redgate sql toolbelt 20.redgate sql toolbelt 20.

Data totalcare 2017 portal.esoo.ssmsboost add in adds missing features and improves your productivity when working with microsoft sql server insql server client tools sql.sql toolbelt.download cracked version of red gate sql toolbelt v.497, integrates with ssms, ssms express, query analyzer and visual studio no registration is needed.redgate sql tool belt keygen for mac innodb full text search mysql version.together, they make you productive, your team agile, and your data safe.redgate sql developer found 7 results for redgate sql toolbelt 2011.may 2017.adobe photoshop keygen.nov .red gate sql toolbelt was added to downloadkeeper this week and last updated on 23 jul 2017.search the site.downloadsto create more accurate search results for redgate sql toolbelt 2.redgate sql toolbelt crack.transporter movie background.

Version of redgate sql toolbelt 2017 v.1139.visual studio 2017 launches today, and out of the box, users can install redgate data tools and extend their devops processes to sql server databases.redgate sql toolbelt keygen.04. May 2017. If you search for redgate sql toolbelt 2.sql toolbelt allows developers and dbas to gain access to all the sql server tools that red gate currently produces, including new releases.found 7 results for red gate sql toolbelt.download servers online: 7.redgate sql toolbelt keygen: dbforge data compare for sql server serials key.redgate sql toolbelt 2017 serial. Etc.updated 2017 ratings and reviews for.this build of sql.a small manual for download: click.incl keygen rar i.published by redgate software distributed by componentsource since 2005.published.

Cracked, mp4, etccrack, you will often see the word.just download and enjoy.we hope you enjoy our download service as much as we do.red gate sql toolbelt v.372. Serial. To produce more results for red gate sql toolbelt v.372, avoid using search words such as download, crack, keygen, serial, number, cd, key, generator, torrent, activation, code, license, full, version, rapidshare, unlockred gate sql toolbelt .238 full version, downloads found: 13, includes: crack serial keygen, date added: today.introduction to the sql toolbelt.mmositeget your gaming life recorded.incl keygen lz.0releaselog.redgate sql toolbelt patch. Added to the site on.redgate toolbelt crack found and available for download.it is free and will not take much time.netload, of, photoshop.crack inns.download cracked.

Redgate sql toolbelt key generator.redgate sql toolbelt 2016 feel free to post any comments about red gate sql toolbelt v 372 incl keygen lz0,.all retail software uses a serial number or key of some form.words like: crack, serial, keygen, free, full, version, hacked, torrent, cracked, mp4, etc.to create better search results for redgate sql toolbelt 20 avoid using keywords such as torrent, key, crack, cracked, keygen, full, serial, activation, unlock.outerspace blufftitler dx9 itv 8 1.0.2 portable crackpare sql crack redgate dataparison was conducted on .sql toolbelt 2013 crack red gate sql toolbelt crack redgate sql toolbelt free download red gate sql toolbelt. July 2017.but with the current push we.g.

Music mp3 download.redgate sql. Crack, keygen.download cracked version of redgate sql toolbelt .497. Everything you need to work with sql server.syed sohel 25 february 2017 at 22:48.posted on by synchsubfast.redgate sql toolbelt 2. Crack y. Red gate.support.where can you get red gate sql toolbelt product code for pc quora updated for 2017.non toxic, no poisons.the inn of many cracks, keygens, serial numbers.to create more accurate search results for redgate sql toolbelt try to exclude using commonly used keywords such as: crack, download, serial, keygen, torrent, warez, etc.simplifying your search should return more download results.if you are here, you are at the right place.replydelete.words like: crack, serial, keygen, free, version, full, hacked, torrent,.

The Best of Simple TalkSQL ServerCrib SheetCompendiumAmirthalingam PrasannaGrant FritcheyPhil FactorRobert SheldonRobyn PageIn association withISBN: 978-1-906434-14-4Shelving: Development/Computer Sciencewww.simpletalkpublishing.com

Page 1 of 90Table of ContentsTable of ContentsTable of Contents.......................................................................1Introduction................................................................................3Authors........................................................................................4Chapter 1: SQL Server Security Crib Sheet..............................5Overview 5Authentication – The Login system 6Authorisation: The Permissions System 8User Context 10Chapter 2: SQL Server XML Crib Sheet.................................11XML 11XML Support in SQL Server 12Querying XML Documents 13Transforming XML data 15The Document Object Model 15XML Web Services 15Glossary 16Happy reading 17Chapter 3: Reporting Services Crib Sheet ............................18The design of SSRS 18The components of SSRS 19SSRS DataSources and Datasets 21Conclusion 22Further Reading…. 22Chapter 4: SSIS 2008 Crib Sheet............................................23SSIS Architecture 23SSIS Designer Tasks and Components 28Data Integration 31Moving Forward 35Chapter 5: SQL Server Data Warehouse Crib Sheet.............36The Data Warehouse 36The Data Model 38The Fact Table 40The Dimension 40The Data 43Conclusion 44Chapter 6: SQL Server Database Backups Crib Sheet..........45General Issues 45SQL Server issues 46Storage Issues 50Backup History 51How Backups should be done 52Chapter 7: SQL Server Performance Crib Sheet ..................54Overview 54Measuring Performance 54Perfmon 54Profiler 57Third Party Tools 60Page 1Table of Contents

Page 2 of 90Table of ContentsTuning Performance 60TSQL Performance 62Client Access 63Testing Performance 63Suggested Reading 64Chapter 8: SQL Server Replication Crib Sheet .....................65Replication topologies 66Replication Methods 66Replication Agents 68Monitoring Replication 68Articles 69Programming Replication Topologies 70Further reading: 70Chapter 9: Entity Framework Crib Sheet...............................71ADO.NET Entity Data Model 71Storage Schema Definition (SSDL) 71Conceptual Schema Definition (CSDL) 72Mapping Schema (MSL) 73Entity Classes 74Working with the Designer and Tools 75Working with data 77Summary 81Further Reading 81Chapter 10: .NET performance Crib Sheet............................82Measuring and Identifying 82Writing optimizer-friendly code 83Coding for performance 85Minimising start-up times 85Using Memory Sensibly 86Common Language Runtime issues 88Conclusions 90Essential tools 90Handy References: 90Page 2Table of Contents

Page 3 of 90IntroductionIntroductionThe 'mission statement' for the Simple-Talk Crib Sheet is:'For the things you need to know, rather than want to know'As a developer, DBA or manager, you may not want to know all about XML, replication or Reporting Services,but if you next project uses one or more of these technologies heavily then the best place to start is from the'jungle roof'.Crib Sheets aim to give you the broad view. Each one tackles a key area of database development, administrationor deployment and provides both a management view and a technical view of that topic. Each starts with thebusiness reasons that will underpin a certain technology requirement and then moves on to the methods availableto implement them.A Crib Sheet is not about code solutions – see the Simple-Talk Workbench series for that – but about providing agood understanding of all the core concepts and terminology that surround a given technology or discipline. Theaim is to cover each topic in just enough detail to perform a given function, no more.This book contains a collection of Simple-Talk Crib Sheets published between 2006 and 2008. It focuses on SQLServer topics, but also covers two .NET issues that are relevant to all SQL Server developers and DBAs:• SQL Server Security• SQL Server XML• SQL Server Reporting Services• SQL Server Data Warehousing• SQL Server Database Backups• SQL Server Performance• SQL Server Replication• Entity Framework• .NET PerformancePage 3Introduction

Page 4 of 90AuthorsAuthorsAmirthalingam PrasannaPrasanna is a software engineer, technical author and trainer with over seven years' experience in the softwaredevelopment industry. He is a Microsoft MVP in the Visual developer category, a MCT and a MCPD onenterprise application development. You can read his blog at www.prasanna.ws and e-mail him atfeedback@prasanna.ws.Prasanna contributed Chapters 9 and 10Grant FritcheyGrant is a database administrator for a major insurance company. He has 18 years' experience in IT, includingtime spent in support and development. He has been working with SQL Server since version 6.0 back in 1995.He worked with Sybase for a few years. He has developed in VB, VB.Net, C# and Java. He is currently workingon methods for incorporating Agile development techniques into database design and development at hiscompany.Grant contributed Chapter 7Phil FactorPhil Factor (real name withheld to protect the guilty), aka Database Mole, has 20 years of experience withdatabase-intensive applications. Despite having once been shouted at by a furious Bill Gates at an exhibition inthe early 1980s, he has remained resolutely anonymous throughout his career.Phil contributed to Chapters 1, 2, 3, 6 and 8Robert SheldonAfter being dropped 35 feet from a helicopter and spending the next year recovering, Robert Sheldon left theColorado Rockies and emergency rescue work to pursue safer and less painful interests – thus his entry into theworld of technology. He is now a technical consultant and the author of numerous books, articles and trainingmaterial related to Microsoft Windows, various relational database management systems, and business intelligencedesign and implementation. He has also written news stories, feature articles, restaurant reviews, legal summariesand the novel Dancing the River Lightly. You can find more information at http://www.rhsheldon.com.Robert contributed Chapters 4 and 5.Robyn PageRobyn Page is a consultant with Enformatica and USP Networks. She is also a well-known actress, being mostfamous for her role as Katie Williams, barmaid in the Television Series Family Affairs.Robyn contributed to Chapters 1, 2, 3, 6 and 8Page 4Authors

Page 5 of 90Chapter 1: SQL Server Security Crib SheetChapter 1: SQL Server Security Crib SheetIn a production database, any access to data and processes must be restricted to just those who require it.Generally, the DBA will also want to know who did what within the system, at any point in time.Each production database will have its own security policy set out, agreed, and documented. This follows onlogically from the analysis of the value, sensitivity and nature of the data and processes within the application. Itshould be updated and available for inspection as part of any audit.SQL Server's security model is designed to give the flexibility to implement a number of different types ofsecurity policy, and allow for all the different application architectures currently in use.Firstly, SQL Server must only have those features enabled that are absolutely necessary. This is easier to do withSQL Server 2005, but possible with all previous releases. One can cause havoc with such features as Webassistant, Ad-hoc remote queries, OLE Automation, xp_CmdShell, and xp_sendmail. It is always best to startwith as many features turned off as possible and configure the database for their use as, or when, needed.Individuals, or applications, require one or more logins, or memberships of a group login, with which to connectto a database. A simple public-facing website may get its data from a database via one Login, whereas anapplication with a variety of sensitive, financial, or personal data will have a rich hierarchy of connection types.Ideally, each person who uses an application will have an associated Login. This is not always possible orpractical.Someone with a Login giving access to a Server will need a username, or alias, in each database that he needs toreach within that server. He will, in effect, need to be registered as a user of a database. Furthermore, that userneeds permission to access the various objects within the database, such as tables, procedures, views and so on,or to execute code that makes structural changes to the database. Typically, this is done by assigning him to a'Role', which then has the permissions assigned to it. As people come and go, their membership to the Role isassigned or revoked, without having to fiddle with permissions.A typical application will be used by a number of different Roles of users, the members of each Role havingsimilar requirements – something like HR, Management-reporting, Dispatch, for example. Each Role will requiredifferent types of access to the database depending on their function in the organization.Each Database Server can therefore manage its security at the server and database level. The 'owner' of aparticular database has the power of controlling access to his database via the 'Permission system'. Only theSystem Administrator can override this.OverviewSQL Server Security has grown and developed in response to the changing architecture of applications, thedemands of application developers and the requirement for simplicity for network administration. SQL Serverhas tried to keep backward compatibility when it has made these changes, so the result can be slightly confusingon first inspection.Originally SQL Server had its own simple login and password system, which was completely independent ofWindows security, and was logically consistent. All groupings of users were done at database level, and there wasjust one privileged login to administer the system. This made the adding and removal of users from the networkmore complex, as it required changing the Logins on every server as well as at the NT Domain level. Integratedsecurity was then introduced, with its concepts of domain users and domain groups, thereby solving some of theproblems. There were now, however, groups defined at network level and others, now renamed 'Roles', atdatabase level. The Server-based administration rights were then assigned, as special Roles, to Logins. Thedatabase 'Owner' rights were also reworked as 'Fixed Database Roles' that could be reassigned to other databaseusers. However, the old 'SA' login and 'DBO' user were kept for backward-compatibility. SQL Server 2005 hasintroduced more complexity, such as password policies and execution contexts, in order to tighten security.Page 5Chapter 1: SQL Server Security Crib Sheet

Page 6 of 90Chapter 1: SQL Server Security Crib SheetAuthentication – The Login systemTypes of authenticationSQL Server authentication allows the DBA to maintain security and auditing control for the database serversindependently of the system manager of the underlying operating system.The downside of SQL Server's own security is that users have to remember their password for accessing thedatabase and use this each time they connect. They have already supplied a password to log into their PCs. Thesetwo different passwords and logins may have different lifetimes and generation rules. Also, this type of security,when used for ODBC/ADO etc, always ends up with passwords being stored in unprotected places. Worse, thepasswords are transmitted unencrypted over TCP/IP.Only SQL Server logins can be used over simple TCP/IP. A connection must have a user name and password,which can be checked against entries in the syslogins table (sys.Server_principals in 2005); otherwise it isterminated.'Integrated security' can only be used if SQL Server is participating in the Windows Network. The advantages arepassword-encryption, password-aging, domain-wide accounts and windows administration. It is based on an"access token" which contains the user's unique security ID or 'sid', which is then used by the client to gain accessto network resources such as SQL Server, without having to supply login credentials again. If a user has an accesstoken, then it means that he has previously passed authentication checks.SQL Server can use Windows Security, or use both Windows Security and manage its own user logins. Thechances are that unless all access to the server is from within an intranet, both will be required.LoginsSQL Server will create some Logins automatically on installation (such as. SA), but most are subsequently createdby the System administrator. A login ID is necessary for access to a database but not sufficient for mostpurposes. It has to be granted access to the various resources on the server (Server instance in SQL Server 2005).It holds information that is relevant across databases, such as the user's default language.Before someone with a Login ID (Except for the SA) can access a database he requires a username or Rolewithin the database, and that username/role must be granted statement permissions and Object permissions.This, traditionally, could only be granted or revoked by the SA or DBO (Database owner). In later versions ofSQL Server, this can be done by anyone with the appropriate 'Fixed Server Role', thereby allowing SA rights tobe given to domain, or local, Groups of users.Fixed Server RolesLogins can, where necessary, be assigned to a number of Fixed Server Roles so that the SA can delegate some, orall, of the administration task. These Roles are:SysadminserveradminsetupadminSecurityadminProcessadminDbcreatorDiskadmincan perform any activity, and has complete control over all database functions.can change server configuration parameters and shut down the server.can add or remove linked servers, manage replication, create, alter or deleteextended stored procedures, and execute some system stored procedures, such assp_serveroption.can create and manage server logins and auditing, and read the error logs.can manage the processes running in SQL Server.can create, alter, and resize databases.can manage disk files.Page 6Chapter 1: SQL Server Security Crib Sheet

Page 8 of 90Chapter 1: SQL Server Security Crib Sheetdb_datawriterdb_ddladmindb_securityadmindb_backupoperatordb_denydatareaderdb_denydatawriterallows the user to add, change, or delete data from all user tables in the database.allows the user to make any data definition language commands in the database.allows the user to manage statement and object permissions in the database.allows the user to back up (but not restore) the database.will deny permission to select data in the database.will deny permission to change data in the database.To allow a user to add users to the database and manage roles and permissions, the user should be a member ofboth the db_accessadmin role and the db_securityadmin role.Some of these Roles are of a rather specialist nature. Of these Database Roles, possibly the most useful are thedb_denydatareader and db_denydatawriter role If the application interface consists entirely of views and storedprocedures, while maintaining ownership chains and completely avoiding dynamic SQL, then it is possible toassign the db_denydatareader and db_denydatawriter Role for regular users, to prevent their access to the basetables.Public Database RoleA Public Database Role is created when a database is created. Every database user belongs to the Public Role.The Public Role contains the default access permissions for any user who can access the database. This DatabaseRole cannot be droppedApplication RolesApplication Roles are the SQL Server Roles created to support the security needs of an application. They allow auser to relinquish his user permissions and take on an Application Role. However, they are not easy to use inconjunction with connection pooling.Authorisation: The Permissions SystemThe database user has no inherent rights or permissions other than those given to the Public Role. All rights mustbe explicitly granted or assigned to the user, the user's Roles, or the Public Role. The Permission Systemdetermines which Roles or users can access or alter data or database objects. It determines what every Role oruser can do within the database. The SA bypasses the permission system, and so has unrestricted access.Most commonly, permissions are given to use a database object such as a table, or procedure. Such objectpermissions allow a user, Role, or Windows NT user or group to perform actions against a particular object in adatabase. These permissions apply only to the specific object named when granting the permission, not to all theother objects contained in the database. Object permissions enable users to give individual user accounts therights to run specific Transact-SQL statements on an object.Permissions can be given or revoked for users and Roles. Permissions given directly to users take precedenceover permissions assigned to Roles to which the user belongs. When creating a permission system, it is often bestto set up the more general permissions first. Start with the Public Role first and then set up the other Roles,finally doing the overrides for individual users where necessary.The permission system has a hierarchy of users for which permissions are automatically given.Page 8Chapter 1: SQL Server Security Crib Sheet

Page 9 of 90Chapter 1: SQL Server Security Crib SheetSAThe SA account is actually a Login rather than a database user. The System Administrator is able to performserver-wide tasks. The System Administrator bypasses the entire permission system and can therefore repair anydamage done to the permission system. It can also perform tasks that are not specific to a particular databaseOnly the System Administrator can create a device, Mirror a device, stop a process, shut down SQL Server,Reconfigure SQL Server, perform all DBCC operations or maintain extended stored procedures. Normally, onlythe SA creates or alters databases, though this permission can be delegatedDBOA DBO has full permission to do anything inside a database that he owns. By default, the SA becomes the ownerof a database that he creates, but ownership can be assigned. There can be only one DBO for each database.Other than the SA, only a DBO can restore a database and transaction log, alter or delete a database, use DBCCcommands, impersonate a database user, issue a checkpoint, grant or revoke statement permissions. The DBOuser has all the rights that members of the db_owner role have. The dbo is the only database user who can add auser to the db_owner fixed database role. In addition, if a user is the dbo, when he or she creates an object, theowner of the object will be dbo of that object, as one might expect. This is not true for members of thedb_owner Fixed Database Role. Unless they qualify their object names with the dbo owner name, the owner'sname will be his or her username.Normally, a db_owner role member can restore a database, but the information on who belongs to the db_ownerRole is stored within the database itself. If the database is damaged to the point where this information is lost,only the DBO can restore the database.If a user is a member of the db_owner Role but not the dbo, he can still be prevented from accessing parts of thedatabase if 'Deny Permissions' has been set. This does not apply to the the dbo, because the dbo bypasses allpermissions checks within the database.Other DBO roles can be assigned to other users, such as creating objects and Backing up a database ortransaction logDBOOBy default, a user who creates an object is the owner of the object. Whoever creates a database object, theDBOO, or Database Object Owner, is granted all permissions on that object.. Every other user is denied accessuntil they are granted permissions. A user who creates a database object is the DBOO of that object. Members ofthe db_owner and db_ddladmin Fixed Database Roles can create objects as themselves, their usernames beinggiven as owner, or can qualify the object name as being owned by the dbo.Assigning PermissionsIf the database designer has been able to define an interface based on Stored Procedures, or views, then thepermission system will be simple, requiring fewer permissions to be set. The Database administrator will have setup users and Roles and will be able to assign 'Execute' permission to just those procedures that are appropriatefor that Role or user. As long as the tables accessed, updated or inserted-into by the stored procedure have thesame ownership as the stored procedure (unbroken ownership chain), then permission need not be assigned tothe tables. A stored procedure can even update a system table as long as the creator of the stored procedure hasthe requisite permission when the procedure was created, and the database is configured to allow such a thing.Security can be further enhanced by denying all access by application users to the base tables withdb_denydatareader and db_denydatawriter .If the Database administrator is unfortunate enough to be associated with a database which requires direct accessto tables or views, then permissions for 'Select', 'Insert', 'Update' and 'delete' access will need to be assigneddirectly to the tables that hold your data. They will also entail using column-level permissions, which can overlycomplicate the security administration model.Page 9Chapter 1: SQL Server Security Crib Sheet

Page 10 of 90Chapter 1: SQL Server Security Crib SheetIf you ever need to grant permission on individual columns of a table, it is usually quicker to create a view, andgrant permission on the view. This is carried forward to the individual columns of the tables that make up theview.It is so unusual for 'Statement permissions' to be assigned that it need not be considered here. However, largedevelopment projects may involve the assignment and revoking of permissions to create database objects such astables, views, procedures, functions, rules and defaults.Object-level permissions can be to:SelectInsertUpdateDeleteExecuteDRIView DefinitionSelect data from a table view or columnInsert new data into a table or viewUpdate existing data in a table view or columnDelete rows from a tableExecute a stored procedure, or a functionallows references to tables that are not owned by the user to be set up directlywithout select permission(SQL Server 2005 only) Allows the viewing of the metadata.SQL Server 2005 also provides 'Send', 'Receive', 'Take Ownership' and 'View Definition' object-level permissionsOwnership chainsSometimes a developer will come up against the problem of 'ownership chains'. When a view or stored procedureis used, permissions are only checked for the contributing objects if there is a change of ownership somewherealong the chain. The most common time this happens is when 'Dynamic SQL' is executed by an Execute() orsp_executeSQL and the user executing the procedure has no permission to access the objects involved. This isknown as a Broken Ownership chain, because more than one user owns objects in a dependency chain.User ContextWhen SQL Server is running, it needs a 'user context' in which to run. This is the user account that SQL Serveruses to access resources on the machine and network. When SQL Server is installed, it is set up with theLocalSystem account, which cannot access the domain. This can be changed for a Domain account if required forbacking up to a network disk, or for setting up replication. It is a good idea to use an account where the passwordis set to 'Password never expires'. SQL Executive will need a domain account in order to publish data forreplication.Page 10Chapter 1: SQL Server Security Crib Sheet

Page 11 of 90Chapter 2: SQL Server XML Crib SheetChapter 2: SQL Server XML Crib SheetThis crib sheet is written with the modest ambition of providing a brief overview of XML as it now exists inSQL Server, and the reasons for its presence. It is designed to supplement articles such as BeginningSQL Server 2005 XML Programming.XML has become the accepted way for applications to exchange information. It is an open standard that can beused on all technical platforms and it now underlies a great deal of the inter-process communication in multitieredand distributed architectures.XML is, like HTML, based on SGML. It is a meta-language, used to define new languages. Although it is notreally a suitable means of storing information, it is ideal for representing data structures, for providing data with acontext, and for communicating it in contextPrevious versions of SQL Server relied on delivering data to client applications as proprietary-format 'Recordsets',either using JDBC or ODBC/ ADO/ OLEDB. This limited SQL Server's usefulness on platforms that could notsupport these technologies. Before XML, data feeds generally relied on 'delimited' ASCII data files, or fixedformatlists that were effective for tabular data, but limited in the information they could provide.SQL Server has now been enhanced to participate fully in XML-based data services, and allow XML to beprocessed, stored, indexed, converted, queried and modified on the server. This has made complex applicationareas, such as data feeds, a great deal easier to implement and has greatly eased the provision of web servicesbased on XML technologies.XML has continued to develop and spawn new technologies. There are a number of powerful supersets of XML,such as XHTML, RSS, and XAML that have been developed for particular purposes. A number of technologieshave been developed for creating, modifying, and transforming XML data. Some of these have been short-lived,but there are signs that the standards are settling down.XMLExtensible Markup Language (XML) is a simple, flexible, text-based representation of data, originally designed forlarge-scale electronic publishing. XML is related to HTML, but is data-centric rather than display-centric. It wasdeveloped from SGML (ISO 8879) by employees of Sun (notably Jon Bosak) and Microsoft working for W3C,starting in 1996. In XML, as in HTML, tags mark the start of data elements. Tags, at their simplest, are merely thename of the tag enclosed in '' chevrons, and the end tag adds a '/'character after the '

Page 12 of 90Chapter 2: SQL Server XML Crib SheetXML Support in SQL ServerSQL Server is fundamentally a relational database, conforming where it can to the SQL standards. XML hasdifferent standards, so that integration is made more difficult by the fact that the XML data type standards arenot entirely the same as the relational data type standards. Mapping the two together is not alwaysstraightforward.XML has considerable attractions for the DBA or Database developer because it provides a way to pass a varietyof data structures as parameters, to store them, query and modify them. It also simplifies the process of providingbulk data-feeds. The challenge is to do this without increasing complexity or obscuring the clarity of the relationaldata-model.XML's major attraction for the programmer is that it can represent rowset (single table) and hierarchical(multiple-table) data, as well as relatively unstructured information such as text. This makes the creation,manipulation, and 'persisting' of objects far easier. XML can represent a complex Dataset consisting of severaltables that are related through primary and foreign keys, in such a way that it can be entirely reconstructed aftertransmission.XML documents can represent one or more typed rowsets (XML Information Set or 'Infoset'). To achieve this, areference to the relevant XML Schema should be contained in every XML document, or fragment, in order todata-type the XML content. SQL Server now provides a schema repository (library) for storing XML schemas,and it will use the appropriate schema to validate and store XML data.Loading XMLXML documents of any size are best loaded using the XML Bulk Load facility, which now has the ability to insertXML data from a flat file into an XML column. You can insert XML data from a file into base tables in SQLServer using the OPENROWSET table function, using the 'bulk rowset Provider', with an INSERT statement.The data can then be shredded to relational tables by using the xml.nodes function. (OpenXML can also be used.It is retained by SQL Server for compatibility with SQL Server 2000).Storing XMLXML documents, XML fragments and top-level text nodes can be stored as XML. XML can be used like anyother data type, as a table column, variable, parameter or function return-value. However, there are obviousrestrictions: although stored as UTF-16, the XML data is encoded and cannot be directly compared with otherXML data, neither can it be used as a primary or foreign key. It cannot have a unique constraint and the XMLdata is stored in a binary format rather than ASCII.Unlike other data types, the XML data type has its own methods to Create, Read, Update or Delete the elementswithin the XML document.XML data can have default values and can be checked by a variation of the RULE, where the validation isencapsulated within a user-defined function.XML data types can be allocated data by implicit conversion from the various CHAR formats, and TEXT, but noothers. There are no implicit conversions from XML data to other formats.Checking XML (XML Schemas)To specify the data type for an element or an attribute in an XML document, you use a schema. XML documentsare checked against XML Schemas. The XML Schema is a definition of the data structure used within an XMLDocument. This indicates, for example, whether a value such as "34.78" (which is stored as a text string withinthe XML) represents a character string, a currency value, or a numeric value.Page 12Chapter 2: SQL Server XML Crib Sheet

Page 13 of 90Chapter 2: SQL Server XML Crib SheetIf, for example, the XML document represents an invoice, the XML Schema describes the relationship betweenthe elements and attributes, and specifies the data types for that invoice.You can check, or validate, untyped XML, whether used in a column, variable or parameter, by associating it withan XML Schema. Once checked, it becomes 'typed'. This ensures that the data types of the elements andattributes of the XML instance are contained, and defined, in the schema. These names are valid within theparticular 'namespace' specified. An XML Schema definition is itself an XML document. These are catalogued inSQL Server as XML Schema collections, and shredded in order to optimise Schema validation. They are tied tospecific SQL Schema within a database.Using typed XML introduces integrity checking and helps the performance of XQuery.Accessing Data in XMLXML Data type columns can be indexed, and manipulated, using XQuery and XML Data Manipulation Language(XML DML), which adds 'Insert', 'delete' and 'replace' to XQuery.To make data-access more effective, XML in SQL Server can be indexed. To be indexed, the XML must be acolumn in a table that already has a primary key. The index can be over the document structure, or for the valuesof the elements.The XML data type can be viewed or modified by a number of methods. One can determine whether a nodeexists, get its value, retrieve it as table-result of a query, or modify its value.XML can be read by the XML parser into a 'Document Object Model' (DOM, see below) and then accessedprogrammatically via methods and properties, but it is not really a suitable server-side technology, due to theoverhead of parsing the document into the model.Shredding XMLThe process of converting XML data into a format that can be used by a relational database is called 'Shredding",or decomposition. One can either use the NODES method on an XML data type or, from a Document ObjectModel (DOM), use the OpenXML function. OpenXML is retained in SQL 2005, but the NODES method isgenerally preferable because of its simplicity and performance.Converting relational data to XMLXML fragments, or documents, can be produced from SQL Queries against relational tables, using the SELECT .. . For XML syntax. An inline XSD Format schema can be produced, and added to the beginning of thedocument. This is convenient but not covered by a W3C standard.Converting XML to other formatsXML documents can be converted into other XML documents, or into formats such as HTML, using XSLStylesheets (see below). These are themselves XML documents that provide a mixture of commands and text. Itis applied to an XML document by processing it via a parser.Querying XML DocumentsXQueryXQuery, derived in part from SQL, is the dominant standard for querying XML data. It is a declarative,functional query language that operates on instances of the XQuery/XPath Data Model (XDM) to query yourXML, using a "tree-like" logical representation of the XML. With XQuery you can run queries against variablesand columns of the XML data type using the latter's associated methods.Page 13Chapter 2: SQL Server XML Crib Sheet

Page 14 of 90Chapter 2: SQL Server XML Crib SheetXQuery has been around for a while. It evolved from an XML query language called Quilt, which in turn wasderived from XML Path Language (XPath) version 1.0, SQL, and XQL.XQuery has similarities with SQL, but is by no means the same. SQL is a more complete language. The SELECTstatement is similar to XQuery's language, but XQuery has to deal with a more complex data model.The XQuery specification currently contains syntax and semantics for querying, but not for modifying XMLdocuments, though these are effected by extensions to XQuery, collectively called the XML Data ManipulationLanguage (XML DML). This allows you to modify the contents of the XML document. With XML DML onecan insert child or sibling nodes into a document, delete one or more nodes, or replace values in nodes.Microsoft thoughtfully provided extensions that allow T-SQL variables and columns to be used to bind relationaldata inside XML data. Server 2005 adds three keywords: insert, update, and delete. Each of these is used withinthe modify() method of the XML data type.The XDM that XQuery uses is unlike the Document Object Model (DOM). Each branch (or "node") of theXDM tree maintains a set of attributes describing the node. In the tree, each node has an XML node type, XDMdata type information, node content (string and typed representations), parent/child information, and possiblysome other information specific to the node type.FLWORXQuery's FLWOR expressions (For, Let, Where, Order by, and Return) iterates XML nodes using the 'for'clause, limits the results using the 'where' clause, sorts the results using the 'order by' clause, and returns theresults via the 'return' clause. These constructs greatly extend the versatility of XQuery, making it comparable toSQLXPathXPath was designed to navigate an XML document to retrieve the document's elements and attributes. It alsoprovides basic facilities for manipulation of strings, numbers and Booleans. It represents the document as a treeof nodes, and allows reference to nodes by absolute or relative paths. One can specify criteria for the nodes thatare returned in square brackets.XML Template QueriesAn XML template query is an XML document with one or more TSQL or XPath queries embedded in it,allowing you to query an XML document. The results can be transformed with an XSLT stylesheet. Templatequeries are used in client code to update SQL Server data. They are templates with attributes and elements thatspecify data requiring updating and how that is to be done.UpdateGramAn UpdateGram is an XML template that is used to insert, update or delete data in a database. It contains animage of the data before and after the required modification. It is usually transmitted to the server by a clientapplication. Each element usually represents one record in a table. The data is 'mapped' either implicitly orexplicitly. One can pass parameters to them.DiffGramThis is an XML document format that is used to synchronise offline changes in data with a database server. It isvery similar to an UpdateGram, but is less complex. It is generally used for 'persisting' the data in data objects.Page 14Chapter 2: SQL Server XML Crib Sheet

Page 15 of 90Chapter 2: SQL Server XML Crib SheetTransforming XML dataXSLXSL is a stylesheet language for XML that is used to transform an XML document into a different format. Itincludes XSLT, and also an XML vocabulary for specifying formatting (XSL-FO). XSL specifies the styling of anXML and describes how an XML document is transformed into another document.Although the resulting document is often HTML, one can transform an XML document into formats such asText, CSV, RTF, TeX or Postscript. An application designer would use an XSL stylesheet to turn structuredcontent into a presentable rendition of a layout; he can use XSL to specify how the source content should bestyled, laid out, and paginated onto some presentation medium. This may not necessarily be a screen display butmight be a hand-held device, a set of printed pages in a catalogue, price-list, directory, report, pamphlet, or book.XSLTXSLT (XSL Transformations), a language for transforming XML documents into other XML documents, is anintrinsic part of XSL. XSLT and XSL are often referred to as if they were synonymous. However, XSLis thecombination of XSLT and XSL-FO (the XSL Formatting Objects).The Document Object ModelThe Document Object Model (DOM) is a platform- and language-neutral interface to enable programs andscripts to dynamically access and update the content, structure and style of XML documents.XML represents data in a tree structure. Any parser will try to convert the flat text-stream representation of anXML or HTML document into a structured model. The Document Object model provides a standardized way ofaccessing data from XML, querying it with XPath/XQuery and manipulating it as an object. This makes it a greatdeal easier for application languages to read or manipulate the data, using methods and objectsThe DOM defines the logical structure of the documents and the way they can be accessed. It provides aprogramming interface for XML documentsSQL Server's OpenXML function actually uses a DOM, previously created using the sp_xml_prepareDocumentstored procedure. This function is a 'shredder' that then provides rowsets from the DOM.XML Web ServicesSQL Server 2005 will support web services based on SOAP. SOAP is a lightweight, stateless, one-way messageprotocol for exchange of information in a decentralized, distributed environment. SQL Server's support makes itmuch easier for SQL Server to participate in systems based on Unix, Linux or mobile devices.XML Web services can be placed in the database tier, making SQL Server an HTTP listener. This provides a newtype of data access capability for applications that are centralized around Web services, utilizing the lightweightWeb server, HTTPSYS, that is now in the operating system, without Internet Information Services (IIS). SOAPcan potentially be used with a variety of other protocols other than HTTP but the HTTP-based service is theonly one in current use;SQL Server exposes a Web service interface to allow execution of SQL statements and invocation of functionsand procedures. Query results are returned in XML format and can take advantage of the Web servicesinfrastructure of Visual Studio. Web service methods can be called from a .NET application almost like any othermethod.A web service is created by:Page 15Chapter 2: SQL Server XML Crib Sheet

Page 16 of 90Chapter 2: SQL Server XML Crib Sheet• Establishing an HTTP endpoint on the SQL Server instance, to configure SQL Server to listen on aparticular port for HTTP requests.• Exposing Stored procedures or user-defined functions as Web Methods• Creating the WSDLThe web services can include SQL batches of ad-hoc queries separated by semicolons.GlossaryCharacter entitiesInfosetNamespaceRSSSGMLWSDLXDMXDRXHTMLXMLXML SchemaXQueryXSDXSLThese are certain characters that are represented by multi-character codes, so as notto conflict with the markup.This is an XML document that represents a data structure and is associated with aschema.Namespaces are designed to prevent clashes between data items that have the samename but in different data structures. A 'name', for example, may have differentmeanings in different part of a data map. Namespaces are generally defined in XMLSchemas. Elements in an XML document can be prefixed to attributes. SOAPNamespaces are part of SOAP messages and WSDL filesAn RDF vocabulary used for site summaries.The Standard Generalised Markup Language. HTML and XML are applications ofSGMLWeb Services Description Language (WSDL) is an XML format for describingnetwork services as a set of endpoints operating on messages containing eitherdocument-oriented or procedure-oriented informationThe Data model used by Xquery to shred XML documentsXML-Data reduced, a subset of the XML-Data schema method.A language for rendering web pages. It is basically HTML that conforms to generalXML rules and can be processed as an XML document.XML is an acronym for Extensible Markup Language and is a language that is usedto describe data and how it should be displayed.An XML Schema is an XML document that describes a data structure and metadatarather than the data itselfXQuery is a query language designed for querying XML data in much the same waythat SQL is used, but appropriate to the complex data structures possible in XMLdocumentsA schema-definition vocabulary, used in XML SchemaaA transformation language for XML documents: XSLT. Originally intended toperform complex styling operations, like the generation of tables of contents andindexes, it is now used as a general purpose XML processing language. XSLT is thuswidely used for purposes other than XSL, like generating HTML web pages fromXML data.Page 16Chapter 2: SQL Server XML Crib Sheet

Page 17 of 90Chapter 2: SQL Server XML Crib SheetWell-formed XML docXML fragmentXQueryA well-formed XML document is properly formatted in that the syntax is correctand tags match and nest properly. It does not mean that the data within thedocument is valid or conforms to the data definition in the relevant XML SchemaThis is well-formed XML that does not contain a root elementAn XML Query language, geared to hierarchical data.Happy reading• XML Support in Microsoft SQL Server 2005• Beginning SQL Server 2005 XML Programming• The XML 1.0 standard• XML 1.1 standard• The XSL family of recommendations• HTML Reference• The W3C website• The XQuery 1.0/XPath 2.0 Data Model (XDM)Page 17Chapter 2: SQL Server XML Crib Sheet

Page 18 of 90Chapter 3: Reporting Services Crib SheetChapter 3: Reporting Services Crib SheetSQL Server Reporting Services (SSRS) aims to provide a more intuitive way of viewing data. It allows businessusers to create, adapt and share reports based on an abstraction or 'model' of the actual data, so that they cancreate reports without having to understand the underlying data structures. This data can ultimately come from avariety of different sources, which need not be based on SQL Server, or even relational in nature. It also allowsdevelopers many ways of delivering reports from almost any source of data as part of an application.The reports are interactive. The word 'reporting', in SSRS, does not refer just to static reports but to dynamic,configurable reports that can display hierarchical data with drill-down, filters, sorting, computed columns, and allthe other features that analysts have come to expect from Excel. Users can specify the data they are particularlyinterested in by selecting parameters from lists. The reports can be based on any combination of table, matrix orgraph, or can use a customized layout. Reports can be printed out, or exported as files in various standardformats.SSRS provides a swift, cheap way of delivering to the users all the basic reports that are required from a businessapplication and can provide the basis for customized reports of a more advanced type.The design of SSRSThe surprising thing about Reporting Services is its open, extensible, architecture. With SSRS, Microsoft hastaken pains over a product that has an obvious long-term importance for data handling in .NET.From a programmer's perspective, the 'Big Idea' behind Reporting Services is to have a standard way ofspecifying reports. In a way, it is an attempt to do for reports what HTML did for rendering pages. ReportDefinition Language (RDL) is an XML-based open standard grammar. It was designed to provide a standard wayto define reports, to specify how they should appear, their layout and content. It specifies the data source to useand how the user-interaction should work.In theory, there could be a number of applications to design business reports; several ways of managing them,and a choice of alternative ways of rendering them. All these would work together because of the common RDLformat.SQL Server Reporting Services is the first product to adopt the architecture. It is a combination of reportauthoring, report management and report delivery. It is not limited to SQL Server data. It can take data from anyODBC source. Reporting Services can use a SQL Server Integration Services package as a data source, therebybenefiting from Analysis Service's multidimensional analysis, hierarchical viewing and data mining. It can just aseasily report from OLAP data as relational data. It can also render reports to a number of media including thebrowser, application window, PDF file, XML, Excel, CSV or TIFF.The API of SSRS is well enough documented to allow the use of custom data, custom ways of displaying data orspecial ways of delivering it. Because Microsoft has carefully documented the RDL files and the APIs of theReportingServices namespace, it is reasonably easy to extend the application for special data or securityrequirements, different data sources, or even the way the reports are rendered. One can of course replace acomponent such as the report authoring tool with one designed specially for a particular application.When SSRS is installed, it is set to deliver reports via a 'Report Server' which is installed as an extension to the IISservice on the same server as that on which SQL Server is installed. The actual portal, with its hierarchical menu,report models and security, can be configured either via a browser or from Visual Studio. The browser-basedtools are designed more for end-users, whereas the Visual Studio 'Business Intelligence Development Studio'tools are intended for the developer and IT administrator.The 'Report Server' is by no means the only possible way of delivering reports using Reporting Services, but it isenough to get you started.Page 18Chapter 3: Reporting Services Crib Sheet

Page 19 of 90Chapter 3: Reporting Services Crib SheetSo let's look in more detail at the three basic processes that combine to form SQL Server Reporting Services(SSRS): Report Authoring, Report Management and Report Rendering.The components of SSRSReport AuthoringThe Report Authoring tools produce, as their end-product, RDL files that specify the way that the report willwork.Any application capable of producing an XML file can produce an RDL file, since RDL is merely an XMLstandard. There is nothing to stop an application from producing an RDL and then using Microsoft'sReportViewer component to render the report.Hopefully, third-party 'Report Designer' packages will one day appear to take advantage of the applications thatare capable of rendering RDL files.The report designers of SSRS are of two types: 'Report Builder' designed for end users and 'Report Designer'designed for developers.Report BuilderReport Builder is an 'ad-hoc reporting tool', and designed for IT-savvy users to allow them to specify, modify andshare the reports they need. It can be run directly from the report server on any PC with the .NET 2 frameworkinstalled. It allows the creation of reports derived from 'report models' that provide a business-oriented model ofthe data. These reports can then be managed just like any others. The Report Builder allows the users to specifythe way data is filtered and sorted, and allows them to change the formulas of calculated columns or to insert newcolumns. These reports have drill-down features built into them.Report DesignerVisual Studio has a 'Report Designer' application hosted within Business Intelligence Development Studio. Itallows you to define, preview and publish reports to the Report Server you specify, or to embed them intoapplications. It is a different angle on the task of designing reports to 'Report Builder', intended for the moresophisticated user who understands more of the data and technology. It has a Query Builder, and expressioneditor and various wizards. The main designer has tabs for the data, layout and preview.With the embedded Query Designer, you can explore the underlying data and interactively design, and run, aquery that specifies the data you want from the data source. The result set from the query is represented by acollection of fields for the dataset. You can also define additional calculated fields. You can create as manydatasets as you need to for representing report data. The embedded Layout Designer allows the insertion oralteration of extra computed columns. With the Layout Designer, you can drag fields onto the report layout, andarrange the report data on the report page. It also provides expression builders to allow data to be aggregatedeven though it has come from several different data locations. It can then be previewed and deployed.Model DesignerThe Model designer in Visual Studio allows you to define, edit and publish 'report models' for Report Builder thatare abstractions of the real data. This makes the building of ad-hoc reports easier. These models can be selectedand used by Report Builder so that users of the system can construct new reports or change existing reports,working with data that is as close as possible to the business 'objects' that they understand. The model designerallows the programmer to specify the tables or views that can be exposed to the users who can then use themodels to design their reports. One can also use it to determine which roles are allowed access to them.Page 19Chapter 3: Reporting Services Crib Sheet

Page 20 of 90Chapter 3: Reporting Services Crib SheetReport ManagementThere are configuration, monitoring and management tools in SSRS which are provided within the BusinessIntelligence Development Studio.Report ManagerReport Manager is a web-based tool designed to ease the management task of connections, schedules, metadata,history and subscriptions. It allows the administrator to categorize reports and control user access. The datamodels that are subsequently used by the ad-hoc Report Builder tool to translate the data into business entitiescan be edited in this tool. The report portal , which provides the 'homepage' for the Report Server, can be editedto create or modify the directory hierarchy into which the individual reports are placed. The RDF files can beuploaded to the report server using this tool and placed in their logical position within the hierarchical menu.One can create or assign the Roles of users that are allowed access the various levels of access to this report.These Roles correspond to previously defined groups in the Active Directory. One can specify whether and howoften a report should be generated and email the recipients when the report is ready.SSRS uses Role-based security to ensure that appropriate access to reports is properly enforced. It controls accessto folders, resources and the reports themselves. With SQL Server Standard and Enterprise editions, one can addnew Roles, based on Active Directory groups. There are APIs for integrating other security models as well.Management StudioThe SQL Server Management Studio (SSMS) tool mirrors most of the capabilities of the Report Manager withthe addition of instance configuration and scripting. Management Studio itself uses RDL files in order toimplement the performance Dashboard so as to get reports on the performance of the server itself. This is easilyextended to provide additional reports.Report RenderingViewing Reports on an intranetWhen SSRS is installed, it sets up a virtual directory on the local IIS. From there, users with the correctpermissions can gain access to whatever reports you choose to deploy. The idea of allowing users to interact withreports and to drill-down into the detail is fundamental to the system, so it is possible to allow users to designtheir own reports, or use pre-existing ones, and to hyperlink between reports or drill down into data to get moredetailed breakdowns. SSRS now provides 'floating headers' for tables that remain at the top of the scrolled list soone can easily tell what is in each columnReport parameters are important in SSRS. If, for example, the users can choose a sales region for a sales reportthen all possible sales regions for which data exists are displayed for selection in a drop-down list. Thisinformation is derived from the data model that forms the basis for the report.Reports can be viewed via a browser from the report server, from any ASP.NET website and from a Sharepointportal.Reports in applicationsOne is not restricted to browser-based access of SSRS reports. Any .NET application can display such reportseasily. The latest version of SSMS, for example, uses reporting services in order to get performance reports.There are alternatives, such as the Web Browser control or the ReportViewer control.To use the Web Browser control in an application, all one needs to do is to provide the URL of the report server.The report is then displayed. One can of course launch the browser in a separate window to display the reports.The URL parameters provide precise control over what information is returned. Using the appropriateparameters, not only can you get the report itself for display, you can also access the contents of the Data SourcePage 20Chapter 3: Reporting Services Crib Sheet

Page 21 of 90Chapter 3: Reporting Services Crib Sheetas XML, the Folder-navigation page, the child items of the report, or resource contents for a report. You can alsospecify whether it should be rendered on the browser or as an image/XML/Excel file.The report viewer control, 'ReportViewer', ships with Visual studio 2005 and can be used in any Windows Formor web form surface, just by dragging and dropping. After you assign a report URL and path, the report willappear on the control. You can configure the ReportViewer in a local report-processing mode where theapplication is responsible for supplying the report data. In local-processing mode, the application can bind a localreport to various collection-based objects, including ADO.NET regular or typed datasets.One can use the Report Server Web Service to gain access to the report management functionality such ascontent, subscription and data source, as well as all the facilities provided by using a URL request. This allowsreporting via any development tool that implements the SOAP methods. This Web Service approach provides agreat deal of control over the reporting process and greatly facilitates the integration of Reporting Services intoapplications, even where the application is hosted in a different operating environment.SSRS DataSources and DatasetsSSRS Data SourcesData that is used to provide the Dataset that forms the basis for a report usually comes from SQL Server, or asource for which there is an OLEDB or ODBC provider. It is possible to create the dataset in anotherapplication, even a CLR, and bind it to a report. One can access other data sources, such as an ADO.NETdataset, by using a Custom Data Extension (CDE).Report delivery can be from a Sharepoint site, using the SharePoint Web parts that are included in the SSRSpackage.The information contained within a data source definition varies depending on the type of underlying data, buttypically includes information such as a server name, a database name, and user credentials.Data sources can include Microsoft SQL Server, Microsoft SQL Server Analysis Services, ODBC, and OLE DB,Report Server Model, XML, Oracle, SAP NetWeaver Business Intelligence or Hyperion EssbaseA data source can be contained within a report, or it can be shared by several. In the first case, the definition for areport-specific data source is stored within the report itself, whereas for a shared source, the definition is storedas a separate item on the report server. A report can contain one or more data sources, either report-specific orshared.SSRS DataSetsA Reporting Services DataSet, which is not the same as a .NET dataset, is the metadata that represents theunderlying data on a specific data source. It contains a data source definition, a query or stored procedure of thedata source and a resulting fields list, also the parameters, if any, of calculated fields as well as the collation. Areport can contain one or more datasets, each of which consists of a pointer to a data source, a query, and acollection of fields. These datasets can be used by different data regions on the report, or they can be used toprovide dynamic lists of parameters.The datasets used as the basis for reports can come from a wide variety of sources. Since the examples are mostlyqueries involving SQL Server base tables, this has given the impression that this is all that can be used. Reportscan in fact easily use Stored Procedures to provide the dataset for a report. However, the queries for datasets thatfetch the items in the drop-down Parameter lists must be provided too.Page 21Chapter 3: Reporting Services Crib Sheet

Page 22 of 90Chapter 3: Reporting Services Crib SheetDataset FieldsEach dataset in a report contains a collection of fields. These fields generally refer to database fields and contain apointer to the database field and a name property but this can be overwritten with a more meaningful namewhere necessary. Alternatively, these can , be calculated fields, which contain a name and an expression.ConclusionWhen implementing an application, one ignores Reporting Services at one's peril. The benefit to almost anyapplication of implementing standard reports from SSRS is immediate and always impressive to end-users. Theimpact is far greater than the effort involved. One of us (Phil) suffered intense embarrassment through believingthe users of an application when they said that they would never require interactive reports and only wantedstrictly defined and cross-checked standard reports in an application. When someone else implemented bothBusiness Intelligence and SSRS, and gave the users the freedom to explore their own data, Phil was left in nodoubt about his foolishness in having neglected to do so.There is always a point when developing an application that the standard fare that can be provided by SSRS is notquite enough for the more advanced reporting requirements. However, it is prudent to make sure that all otherreporting up to that point is done via SSRS.The worst mistake of all is dismissing SQL Server Reporting Services as being just an end-user tool for simplereports. Its architecture is such that it forms the basis of an extremely powerful tool for delivering information tousers of an application.Further Reading….• SQL Server 2005 Reporting Services• Technologies: Reporting Services• SQL Server 2005 Books Online: SQL Server Reporting Services• Configuring Reporting Services to Use SSIS Package Data• Introducing Reporting Services Programming (SQL 2000)• Report Definition Language Specification• Report Controls in SQL Server 2005 Reporting ServicesPage 22Chapter 3: Reporting Services Crib Sheet

Page 23 of 90Chapter 4: SSIS 2008 Crib SheetChapter 4: SSIS 2008 Crib SheetLike most SQL Server 2008 components, SQL Server Integration Services (SSIS) includes a number of newfeatures and enhancements that improve performance and increase developer and administrator productivity. Theimprovements range from changes to the architecture – in order to better support package development andexecution – to the addition of SSIS Designer tasks and components that extend SSIS capabilities and providemore effective data integration.In this crib sheet, I provide an overview of several of these enhancements and give a brief explanation of howthey work. Although this is not an exhaustive list of the changes in SSIS 2008, the information should provideyou with a good understanding of the product's more salient new features and help you better understand howthese improvements might help your organization.SSIS ArchitectureThe SQL Server 2008 team has made several important changes to the SSIS architecture, including redesigningthe data flow engine, implementing a new scripting environment, and upgrading Business IntelligenceDevelopment Studio (BIDS).Data Flow EngineIn SSIS 2005, the data flow is defined by a set of execution trees that describe the paths through which data flows(via data buffers) from the source to the destination. Each asynchronous component within the data flow createsa new data buffer, which means that a new execution tree is defined. A data buffer is created because anasynchronous component modifies or acts upon the data in such a way that it requires new rows to be created inthe data flow. For example, the Union All transformation joins multiple data sets into a single data set. Becausethe process creates a new data set, it requires a new data buffer which, in turn, means that a new execution tree isdefined.The following figure shows a simple data flow that contains a Union All transformation used to join together twodatasets.Page 23Chapter 4: SSIS 2008 Crib Sheet

Page 24 of 90Chapter 4: SSIS 2008 Crib SheetBecause the Union All transformation is asynchronous – and subsequently generates a new dataset – the data sentto the Flat File destination is assigned to a new buffer. However, the Data Conversion and Derived Columntransformations are synchronous, which means that data is passed through a single buffer. Even the ConditionalSplit transformation is synchronous and outputs data to a single buffer, although there are two outputs.If you were to log the PipelineExecutionTrees event (available through SSIS logging) when you run this package,the results would include information similar to the following output:begin execution tree 1output "OLE DB Source Output" (11)input "Conditional Split Input" (83)output "SalesReps" (143)input "Data Conversion Input" (187)output "Data Conversion Output" (188)input "Derived Column Input" (148)output "Derived Column Output" (149)input "Union All Input 1" (257)Page 24Chapter 4: SSIS 2008 Crib Sheet

From the OLE DB source error output to the Flat File destination input.From the Union All output to the Flat File destination input.From this, you can see that most of the work is done in the first execution tree. The issue that this approachraises is that in SSIS 2005, the data flow engine assigns only a single execution thread to each execution tree. (Tocomplicate matters, under some conditions, such as when there are not enough threads, a single thread can beassigned to multiple execution trees.) As a result, even if you're running your package on a powerfulmultiprocessor machine, a package such as the one above will use only one or two processors. This becomes acritical issue when an execution tree contains numerous synchronous components that must all run on a singlethread.And that's where SSIS 2008 comes in. The data flow can now run multiple components in an execution tree inparallel. If you were to run the same package in SSIS 2008, the PipelineExecutionTrees event output would lookquite different:Begin Path 0output "Union All Output 1" (258); component "Union All" (256)input "Flat File Destination Input" (329); component "Flat File Destination" (328)End Path 0Begin Path 1output "OLE DB Source Output" (11); component "OLE DB Source" (1)input "Conditional Split Input" (83); component "Conditional Split" (82)Begin Subpath 0output "SalesReps" (143); component "Conditional Split" (82)input "Data Conversion Input" (187); component "Data Conversion" (186)output "Data Conversion Output" (188); component "Data Conversion" (186)input "Derived Column Input" (148); component "Derived Column" (147)output "Derived Column Output" (149); component "Derived Column" (147)input "Union All Input 1" (257); component "Union All" (256)End Subpath 0Begin Subpath 1Page 25Chapter 4: SSIS 2008 Crib Sheet

Page 26 of 90Chapter 4: SSIS 2008 Crib Sheetoutput "NonReps" (169); component "Conditional Split" (82)input "Data Conversion Input" (219); component "Data Conversion 1" (218)output "Data Conversion Output" (220); component "Data Conversion 1" (218)input "Derived Column Input" (236); component "Derived Column 1" (235)output "Derived Column Output" (237); component "Derived Column 1" (235)input "Union All Input 2" (278); component "Union All" (256)End Subpath 1End Path 1Begin Path 2output "OLE DB Source Error Output" (12); component "OLE DB Source" (1)input "Flat File Destination Input" (388); component "Flat File Destination 1" (387)End Path 2The first thing you'll notice is that the execution trees are now referred to as "paths" and that a path can bedivided into "subpaths." Path 1, for example, includes two subpaths, which are each launched with theConditional Split outputs. As a result, each subpath can run in parallel, allowing the data flow engine to takebetter advantage of multiple processors. For more complex execution trees, the subpaths themselves can bedivided into additional subpaths that can all run in parallel. The best part is that SSIS schedules thread allocationautomatically, so you don't have to try to introduce parallelism manually into your packages (such as addingunnecessary Union All components to create new buffers). As a result, you should see improvements inperformance for those packages you upgrade from SSIS 2005 to 2008 when you run them on high-end,multiprocessor servers.Scripting EnvironmentSSIS includes the Script task and Script component, which allow you to write scripts that can be incorporatedinto the control flow and data flow. The basic function of the Script task and component are relatively the samein SSIS 2005 and SSIS 2008. However, the difference between the two versions is in the scripting environmentitself. In SSIS 2005, the Script task and component use Microsoft Visual Studio for Applications (VSA). In SSIS2008, the Script task and component use Microsoft Visual Studio 2005 Tools for Applications (VSTA).The following example shows the ScriptMain class for the Script task in SSIS 2008. The first thing you mightnotice is that the script is written in C#.Page 26Chapter 4: SSIS 2008 Crib Sheet

Page 27 of 90Chapter 4: SSIS 2008 Crib SheetIn SSIS 2005, you are limited to writing scripts in Visual Basic.NET. However, in SSIS 2008, because the VSTAenvironment is used, you can write scripts in C# or Visual Basic.NET.Another advantage to VSTA is that you can now add Web references to your script. (This option is not availablein SSIS 2005.) As a result, you can easily access the objects and the methods available to the Web services. VSTAalso lets you add managed assemblies to your script at design time and you can add assemblies from any folder onyour computer. In general, VSTA makes it easier to reference any .NET assemblies.If you're upgrading an SSIS 2005 package to SSIS 2008 and the package contains a Script task or component,SSIS makes most of the necessary script-related changes automatically. However, if your script referencesIDTSxxx90 interfaces, you must change those references manually to IDTSxxx100. In addition, you must changeuser-defined type values to inherit from System.MarshalByRefObject if those values are not defined in themscorlib.dll or Microsoft.SqlServer.VSTAScriptTaskPrx.dll assemblies.Business Intelligence Development StudioIn SSIS 2005, BIDS is based on Visual Studio 2005, but in SSIS 2008, BIDS is based on Visual Studio 2008. Forthe most part, you won't see much difference in your development environment. However, the biggest advantageto this change is that you can have BIDS 2005 and BIDS 2008 installed on the same machine, allowing you toedit SSIS 2005 and 2008 packages without having to switch between different environments.Page 27Chapter 4: SSIS 2008 Crib Sheet

Page 28 of 90Chapter 4: SSIS 2008 Crib SheetSSIS Designer Tasks and ComponentsSSIS Designer is the graphical user interface (GUI) in BIDS that lets you add tasks, components, and connectionmanagers to your SSIS package. As part of the changes made to SSIS to improve performance and increaseproductivity, SSIS Designer now includes the elements necessary to support data profiling, enhanced cachedlookups, and ADO.NET connectivity.Data ProfilingThe Data Profiling task, new to SSIS 2008, lets you analyze data in a SQL Server database in order to determinewhether any potential problems exist with the data. By using the Data Profiling task, you can generate one ormore of the predefined reports (data profiles), and then view those reports with the Data Profile Viewer tool thatis available when you install SSIS.To generate data profile reports, you simply add a Data Profiling task to your control flow and then select one ormore profile types in the Data Profiling Task editor (on the Profile Requests page). For example, the followingfigure shows the Column Statistics profile type.Although the figure shows only one configured profile type, you can add as many types as necessary, each specificto a data source. The Data profiling task supports eight profile types:Candidate KeyColumn Length Distrib.Provides details that let you determine whether one or more columns are suitable touse as a candidate key.Provides the lengths of distinct values in a string column and the percentage of rowsthat share each length.Page 28Chapter 4: SSIS 2008 Crib Sheet

Page 29 of 90Chapter 4: SSIS 2008 Crib SheetColumn Null RatioColumn PatternColumn StatisticsColumn Value Distrib.Provides the number and percentage of null values in a column.Provides one or more regular expressions that represent the different formats of thevalues in a column.Provides details about the values in a column, such as the minimum and maximumvalues.Provides the distinct values in a column, the number of instances of that value, andthe percentage of rows that have that value.Functional Dependency Provides details about the extent to which values in one column depend on thevalues in another column.Value InclusionProvides details that let you determine whether one or more columns are suitable asa foreign key.For each profile type that you select, you must specify an ADO.NET connection, a table or view, and thecolumns on which the profile should be based. You must also specify whether to save the profile data to avariable or to an .xml file. Either way, the data is saved in an XML format. If you save the results to a variable,you can then include other logic in your package, such as a Script task, to verify the data. For example, you cancreate a script that reads the results of a Column Statistics profile and then takes specific actions based on thoseresults.If you save the data to a file, you can use the Data Profile Viewer to view the data profile that you generatedwhen you ran the Data Profiling task. To use the Data Profile Viewer, you must run the DataProfileViewer.exeutility. By default, the utility is saved to the Program Files\Microsoft SQL Server\100\DTS\Binn folder on thedrive where you installed SSIS. After the utility opens, you can open the .xml file from within the utility window.The following figure shows the Column Statistics report generated for the OrderQty column in theSales.SalesOrderDetail table.Page 29Chapter 4: SSIS 2008 Crib Sheet

Page 30 of 90Chapter 4: SSIS 2008 Crib SheetIf you specified that multiple reports should be generated, all those reports will be available when you open thefile in the Data Profile Viewer. You simply maneuver through the database hierarchy to view the specific dataprofiles.Cached LookupsIn SSIS 2005, you perform lookup operations in the data flow by using the Lookup transformation to retrievelookup data from an OLE DB data source. You can, optionally, configure the component to cache the lookupdataset, rather than retrieve the data on a per row basis. In SSIS 2008, your caching options for performinglookup operations have been extended through the Cache transformation and Cache connection manager. Byusing the new transformation and connection manager, you can cache lookup data from any type of data source(not only an OLE DB source), persist the data to the memory cache or into a cache file on your hard drive, anduse the data in multiple data flows or packages.The primary purpose of the Cache transformation is to persist data through a Cache connection manager. Whenconfiguring the transformation, you must – in addition to specifying the connection manager – define the columnmappings. The following figure shows the Mappings page of the Cache Transformation Editor.As you can see, you must map the appropriate input columns to the output columns so the correct lookup data isbeing cached. In this example, I am caching employee IDs, first names, and last names. I will later use a Lookuptransformation to look up the employee names based on the ID.To support cached lookups, you must – as well as configuring the Cache transformation – configure the Cacheconnection manager. The following figure shows the General tab of the Connection Manager Editor.Page 30Chapter 4: SSIS 2008 Crib Sheet

Page 31 of 90Chapter 4: SSIS 2008 Crib SheetAt a minimum, you must provide a name for the connection manager. By default, the lookup data will be storedin the memory cache in the format in which it is received through the data flow pipeline. However, you caninstead store the data in a cache (.caw) file by providing a path and file name. You can also modify the dataformat (data type, length, etc.) on the Columns tab, within the restrictions that govern type conversion in SSIS.When you use the Cache transformation and connection manager to cache your lookup data, you must performthe caching operation in a package or data flow separate from the data flow that contains the Lookuptransformation. In addition, you must ensure that the caching operation runs prior to the package or data flowthat contains the Lookup transformation. Also, when you configure the Lookup transformation, be sure tospecify full cache mode and use the Cache connection manager you created to support the lookup operation.ADO.NETSSIS 2008 now includes the ADO.NET source and destination components. (The ADO.NET source replacesthe DataReader source in SSIS 2005; however, SSIS 2008 continues to support the DataReader destination.) TheADO.NET source and destination components function very much like the OLE DB source and destinationcomponents. The editors are similar in the way you configure data access, column mappings, error output, andcomponent properties. In addition, because you access data through an ADO.NET connection manager, you canaccess data through any supported .NET provider, including the ODBC data provider and the .NET providersfor OLE DB.Data IntegrationSSIS 2008 has made it easier to work with different types of data by enhancing the SQL Server Import andExport wizard and by adding new SSIS data types to provide better support for data/time data.Page 31Chapter 4: SSIS 2008 Crib Sheet

Page 32 of 90Chapter 4: SSIS 2008 Crib SheetSQL Server Import and Export WizardWhen you launch the SQL Server Import and Export wizard in BIDS, the wizard attempts to match the datatypes of the source data to the destination data by using SSIS types to bridge the data transfer. In SSIS 2005, youhad little control over how these SSIS types were mapped to one another. However, in SSIS 2008, a new screenhas been added to the wizard to allow you to analyze the mapping so you can address any type mismatch issuesthat might arise.The following figure shows the Review Data Type Mapping screen of the SQL Server Import and Export wizard.In this scenario, I am attempting to import data from a text file into a SQL Server database.The data in the text file is comma-delimited. For this example, you can assume that each row includes the correctnumber of columns (with the correct data) necessary to match the target table. The target table is based on thefollowing table definition:CREATE TABLE [dbo].[JobTitles]([FirstName] [nvarchar](30) NOT NULL,[LastName] [nvarchar](30) NOT NULL,[JobTitle] [nvarchar](30) NOT NULL,[EmpID] [varchar](10) NOT NULL,[HireDate] [datetime2](7) NULL) ON [PRIMARY]If you refer again to the figure above, you'll see that the screen shows how the columns are mapped from thesource data to the destination data. Each column is marked with one of the following icons:Page 32Chapter 4: SSIS 2008 Crib Sheet