tag:blogger.com,1999:blog-20662817668612870652024-03-05T14:21:10.149+00:00Graeme's PlaceGraeme Malcolmhttp://www.blogger.com/profile/02246562883877200692noreply@blogger.comBlogger30125tag:blogger.com,1999:blog-2066281766861287065.post-56999049931518102672014-09-10T14:20:00.001+01:002014-09-10T15:18:34.356+01:00Auditing for Microsoft Azure SQL Database<p>I was recently asked to contribute a module on Azure SQL Database to a new course on Azure for IT Professionals. This gave me an excuse to experiment with the new Auditing feature, which is currently in preview. Auditing enables you to record events that occur in your Azure database, such as logins, data access, data updates, and schema changes; and is an important addition to Azure SQL Database that supports security and compliance requirements in many organizations.</p> <p>Setting up Auditing is pretty straightforward, as you can see in this video.</p> <p align="center"><iframe height="480" src="//www.youtube.com/embed/VJehdC9SCDQ" frameborder="0" width="853" allowfullscreen></iframe></p> <p>To learn more about auditing in Azure SQL database, see <a title="http://azure.microsoft.com/en-us/documentation/articles/sql-database-auditing-get-started/" href="http://azure.microsoft.com/en-us/documentation/articles/sql-database-auditing-get-started/">http://azure.microsoft.com/en-us/documentation/articles/sql-database-auditing-get-started/</a>.</p> Graeme Malcolmhttp://www.blogger.com/profile/02246562883877200692noreply@blogger.com0tag:blogger.com,1999:blog-2066281766861287065.post-72222197364354096872014-05-10T11:25:00.001+01:002014-05-10T11:25:43.291+01:00Microsoft Azure SQL Database Self-Service Restore<p>You may have missed the recent enhancements to Azure SQL Database service tiers, which Microsoft announced last month. There are three new service tiers (or “editions” if you prefer) of SQL Database that are currently available in preview – basic, standard, and premium. That’s currently in addition to the Web and Business service tiers that were available previously, though these are being retired over the next year. The performance and scalability options that go with these new tiers are pretty compelling, and may make a good subject for a future post. However, the other key new feature that comes with these features is a self-service restore feature that provides a basic disaster recovery capability.</p> <p><a href="http://lh5.ggpht.com/-EnPzlgdNE4M/U23-otrdvCI/AAAAAAAAAoo/O--6j9jo7tk/s1600-h/AzureSQLRestore%25255B5%25255D.png"><img title="Restoring an Azure SQL Database" style="border-top: 0px; border-right: 0px; border-bottom: 0px; margin-left: 0px; border-left: 0px; display: inline; margin-right: 0px" border="0" alt="Restoring an Azure SQL Database" src="http://lh3.ggpht.com/-PGxNANX7VkI/U23-pSQIBEI/AAAAAAAAAow/tqqiXfJSg3g/AzureSQLRestore_thumb%25255B3%25255D.png?imgmax=800" width="644" height="424"></a> </p> <p>When you create a database with one of the new service tiers, Azure automatically maintains backups that you can use to back-out unintentional changes or recover an accidentally deleted database. The specific options for restoring a database depends on the service tier:</p> <ul> <li>Basic tier databases can be restored to the most recent daily backup. Backups are retained for 24 hours.</li> <li>Standard tier databases can be restored to a specific point in time, and backups are retained for 7 days.</li> <li>Premium databases can be restored to a specific point in time in the last 35 days.</li></ul> <p>You can restore databases using the Azure management portal, or with PowerShell. Restoring an existing database creates a new database of the same service tier with a name that reflects the date and time to which the database has been recovered. For example, suppose you executed a Transact-SQL command that accidentally deleted the contents of a table in your database. Depending on the service tier, you can restore the database to the most recently available recovery point before the data was deleted. After you’ve verified that the recovered database contains the required data, you can delete the original database and the use ALTER DATABASE statement to rename the restored database to match the original name.</p> <p>If you delete an entire database, it remains listed in the portal until its retention period has expired. If you accidentally delete a database, you can immediately restore it to the most recently available recovery point (again, depending on the service tier).</p> <p>Of course, this self-service recovery feature isn’t a replacement for a properly planned disaster recovery solution. However, it’s a nice addition to SQL Database that could potentially save you a lot of pain and stress one day!</p> Graeme Malcolmhttp://www.blogger.com/profile/02246562883877200692noreply@blogger.com3tag:blogger.com,1999:blog-2066281766861287065.post-49225892041892777132013-08-14T17:41:00.001+01:002013-08-14T17:41:18.966+01:00Using the Buffer Pool Extension in SQL Server 2014<p>SQL Server makes use of physical memory to cache frequently accessed data pages in a buffer pool. This reduces disk I/O and optimizes overall performance. An easy way to improve performance of I/O bound workloads is therefore to simply add more physical memory. However, in some cases, adding memory to a server is not possible – for example because of hardware limitations in the motherboard. Additionally, although the cost of memory continues to drop, when viewed as a per-megabyte cost, RAM is significantly more expensive than disk devices – including solid state disk (SSD) devices, which provide significant performance improvements over mechanical disks.</p> <p>SQL Server 2014 introduces the buffer pool extension; a feature that enables you to take advantage of non-volatile storage like SSD devices, and use them to extend the buffer pool. In some scenarios, this can be a cost-effective way to improve the performance of database workloads when adding more memory to the server is not an option. With the buffer pool extension enabled, SQL Server 2014 uses the non-volatile storage for clean pages (that is, pages that have been committed), making them faster to retrieve than if they had been paged out of the buffer to their disk storage location. By using the buffer pool extension for only clean pages, the risk of data loss in the event of a server or storage device failure is avoided (in the case of storage device failure, the buffer pool extension is automatically disabled).</p> <p>The following video demonstrates how to enable and disable the buffer pool extension in SQL Server 2014.</p> <p><iframe height="600" src="//www.youtube.com/embed/Y1E97p75q6g?rel=0" frameborder="0" width="800" allowfullscreen></iframe></p> <p>This article is based on the CTP 1 release of SQL Server 2014, and details are subject to change between now and the eventual release of the product. For more information about the buffer pool extension in SQL Server 2014, visit <a title="http://blogs.technet.com/b/dataplatforminsider/archive/2013/07/25/buffer-pool-extension-to-ssds-in-sql-server-2014.aspx" href="http://blogs.technet.com/b/dataplatforminsider/archive/2013/07/25/buffer-pool-extension-to-ssds-in-sql-server-2014.aspx">http://blogs.technet.com/b/dataplatforminsider/archive/2013/07/25/buffer-pool-extension-to-ssds-in-sql-server-2014.aspx</a>.</p> Graeme Malcolmhttp://www.blogger.com/profile/02246562883877200692noreply@blogger.com0tag:blogger.com,1999:blog-2066281766861287065.post-15889307939534909162013-07-25T11:23:00.001+01:002013-07-25T11:58:39.435+01:00Power Query for Excel Demo<p>A couple of weeks ago, I posted a <a href="http://graemesplaceblog.blogspot.co.uk/2013/07/data-explorer-demo-for-course-20467b.html" target="_blank">demo that instructors of Microsoft Learning course 20467B can use to demonstrate the Data Explorer add-in for Excel 2013</a>. Since then, Microsoft has rebranded Data Explorer as “Power Query”, and announced that it will form part of the <a href="http://office.microsoft.com/en-us/excel/power-bi-FX104080667.aspx" target="_blank">Power BI</a> capabilities in Office 365.</p> <p>A new version of the add-in is now available <a href="http://www.microsoft.com/en-us/download/details.aspx?id=39379" target="_blank">here</a> so I’ve updated the demo steps, which you can download from <a href="http://sdrv.ms/10x7UlN" target="_blank">my SkyDrive folder</a>. Other than the renaming of the <strong>Data Explorer</strong> tab on the ribbon to <strong>Power Query</strong>, the steps are much the same as they were before, so the following video is still a reasonably good guide to the tool.</p><iframe height="600" src="//www.youtube.com/embed/WCsdC9dgqBc?rel=0" frameborder="0" width="800" allowfullscreen></iframe> Graeme Malcolmhttp://www.blogger.com/profile/02246562883877200692noreply@blogger.com0tag:blogger.com,1999:blog-2066281766861287065.post-51914956392197301822013-07-12T17:01:00.001+01:002013-07-14T17:21:41.503+01:00Migrating SQL Server Databases to Windows AzureWhen the IT industry started getting excited about this thing called “the cloud” a few years ago, there were many (myself included) who were sceptical about the willingness of organizations to abandon their own on-premises infrastructure and start moving business applications to the web. At first, the idea of relying on IT services hosted and managed by some Internet provider seemed dubious at best – sure, individual consumers could use an email server provided by their ISP, but businesses need to manage their own IT. Don’t they?<br />
Then, gradually, we started to make concessions.<br />
…OK, maybe a a hosted Exchange Server would reduce some administrative overheads.<br />
…Yes, maybe using a hosting service can provide better availability for some Web applications.<br />
…Alright, using a software-as-a-service solution for CRM might reduce licensing and hardware costs.<br />
Fast forward to today, and we’ve come a long way – to the point where even that stalwart of enterprise software packages, Microsoft Office, is now delivered to 1 in 4 of Microsoft’s enterprise customers in the form of the Office 365 cloud service rather than as on-premises desktop software. Despite the initial doubts of the naysayers, it’s beginning to look like this cloud thing might just catch on after all! Most of the applications we use every day, as consumers and increasingly as employees, are delivered as cloud services that we can consume anywhere and on an increasing array of mobile devices. Organizations are seeing this extended reach, while at the same time reducing overheads for hardware, software licensing, maintenance, and other costs – A win-win scenario if ever I saw one.<br />
However, there’s always been one section of the IT community that is even more conservative than the Finance department. One group of IT professionals that that regards even the smallest change with deep suspicion. One last bastion of fierce resistance to new fangled trends. I’m talking of course, about database administrators. You can move my Exchange Server to the cloud. You can store all of our documents in SharePoint Online. You can even deliver Office applications through a browser. But you’ll have to take my on-premises database from my cold, dead hands!<br />
But even that resistance is beginning to crumble. It makes sense for web-hosted applications to store their data on the web, and as more and more IT services are moved to the cloud, it also makes sense to include traditional business application data stores as a part of that migration. They key issues that need to be addressed are:<br />
<ul>
<li>Can the data be moved to the cloud without compromising security or compliance requirements?
<li>Can a hosted solution cope with the volume of data in our databases, and support our transactions without compromising performance?
<li>Can a hosted database meet our availability and disaster recovery requirements?
<li>Can we migrate some, but not all, of our databases – and still retain centralized, consistent administration of all data stores?</li>
</li>
</li>
</li>
</ul>
Then, assuming that the answer to those three questions is “yes” (and in many cases, it is), the only remaining question is:<br />
<ul>
<li>Can we migrate our databases with minimal interruption of service, and without breaking our existing applications?</li>
</ul>
Well, let’s see how Windows Azure shapes up in terms of these critical questions.<br />
Windows Azure is Microsoft’s cloud platform, and it underpins many of the cloud services that the company offers. It also provides Platform-as-a-Service (PaaS) and Infrastructure-as-a-Service(IaaS) solutions that organizations can use to build new solutions, and migrate existing IT services to the cloud. In terms of database offerings, there are two primary options to consider:<br />
<ul>
<li>Windows Azure SQL Database – a PaaS offering that enables you to host data in a (mostly) SQL Server-compatible service without having to worry about configuring and managing hardware or the operating system.
<li>SQL Server in a Windows Azure Virtual Machine – an IaaS offering that is exactly what it sounds like – a virtual machine running Windows with SQL Server installed in it.</li>
</li>
</ul>
<h3>
Windows Azure SQL Database</h3>
OK, let’s start with Windows Azure SQL Database. This was formerly known as SQL Azure, and provides many of the data storage and management features of SQL Server without the need to manage the operating system or SQL Server instance. If your database consists of traditional relational data in tables, with views and stored procedures used to provide a layer of abstraction, then Windows Azure SQL Database may well be a good option for you. It can’t support “special” data, such as spatial or XML data types, and there are a few other limitations see <a href="http://msdn.microsoft.com/en-us/library/windowsazure/ee336245.aspx" target="_blank">General Guidelines and Limitations (Windows Azure SQL Database)</a> on MSDN for details; but it supports all of the functionality required by a large percentage of typical business application databases.<br />
So, how does it stack up against the questions we asked earlier?<br />
<strong>Can the data be moved to the cloud without compromising security or compliance requirements?</strong><br />
It depends on your specific requirements; but network connectivity to the database can be restricted to a specific range of IP addresses, and can be performed over SSL connections. Client requests are authenticated using SQL Server native authentication based on a login and password, and the same permissions-based authorization scheme used in SQL Server is used to control access to tables and other database objects. In terms of compliance policies, you have control over which geographic region in which the data center hosting your Windows Azure SQL database server is located.<br />
<strong>Can a hosted solution cope with the volume of data in our databases, and support our transactions without compromising performance?</strong><br />
You specify the size of your database as you create it, but it can grow to a maximum of 150 GB. Additionally, you can use federations to partition data across multiple databases to increase scalability and performance.<br />
<strong>Can a hosted database meet our availability and disaster recovery requirements?</strong><br />
Windows Azure provides built-in resiliency by internally replicating your database across three redundant storage locations within the data center where your server is hosted. You can back up a SQL Database by copying it to another SQL Database.<br />
<strong>Can we migrate some, but not all, of our databases – and still retain centralized, consistent administration of all data stores?</strong><br />
Because SQL database is a PaaS offering, much of the physical server administration you would typically need to manage for a SQL Server instance is handled for you by Windows Azure. For logical administration tasks, such as managing users or creating database objects, you can use SQL Server Management Studio to connect to Windows Azure SQL database – enabling you to manage on-premises SQL Server instances and cloud-based Windows Azure SQL Database instances in the same tool.<br />
<strong>Can we migrate our databases with minimal interruption of service, and without breaking our existing applications?</strong><br />
Well, let’s take a look at this demonstration and find out:<br />
<iframe allowfullscreen="" frameborder="0" height="480" src="//www.youtube.com/embed/Vwy9F647kWM?rel=0" width="640"></iframe><br />
As you can see, it’s really pretty straightforward to migrate an on-premises SQL Server database to Windows Azure SQL Database. If your database doesn’t depend on any features of SQL Server that aren’t supported by Windows Azure SQL Database, and you’re happy to let Windows Azure look after the physical configuration of your database server, then Windows Azure SQL Database is a good option for you.<br />
<h3>
SQL Server in a Windows Azure Virtual Machine</h3>
So, what about applications where you need to support SQL Server capabilities that aren’t available in Windows Azure SQL Database? Or where you specifically want control over the operating system and server-level configuration of your database server?<br />
In this case, provisioning a virtual machine in Windows Azure might be a better option. There are a number of pre-defined virtual machine images, some of which include an installation of SQL Server; and if none of them suits you, there’s always the option to create your own and install whatever software you require. So how does this option meet our data migration requirements?<br />
<strong>Can the data be moved to the cloud without compromising security or compliance requirements?</strong><br />
As with Windows Azure SQL Database, you can choose the geographic region of the data center where your virtual machine will be hosted. Access to the server is controlled through an endpoint that you must define for your Windows Azure virtual machine, and all network connectivity to the virtual machine can be restricted by using Windows firewall. You can use Windows Azure virtual networking to integrate virtual machines in Windows Azure with your corporate Active Directory infrastructure, and use Windows authentication to connect to SQL Server. Or alternatively, you can use SQL Server native authentication through logins and passwords, or even set up certificate-based authentication – exactly the same as with an on-premises instance of SQL Server. Additionally, you can make use of SQL Server’s security-related capabilities such as transparent database encryption and auditing.<br />
<strong>Can a hosted solution cope with the volume of data in our databases, and support our transactions without compromising performance?</strong><br />
When you provision a virtual machine in Windows Azure, you can specify the number of virtual cores and the amount of memory allocated to the VM. At the moment, the largest VM available has 8 cores and 56 GB of memory, but I’d expect that to get larger over time. The VM uses Windows Azure storage for its virtual hard disks, and you can add multiple VHDs and use filegroups to stripe data across them. This technique has been shown to improve IOPS performance.<br />
<strong>Can a hosted database meet our availability and disaster recovery requirements?</strong><br />
As with all Windows Azure storage blobs, the VHDs for the VM are replicated across three redundant physical data storage devices in the data center. Additionally, you can use SQL Server HA capabilities, such as AlwaysOn Availability Groups to protect against failure of a VM. You can back up databases in a Windows Azure VM just as you would for an on premises instance of SQL Server, and use the SQL Agent to automate backup tasks on a scheduled basis.<br />
<strong>Can we migrate some, but not all, of our databases – and still retain centralized, consistent administration of all data stores?</strong><br />
SQL Server in a virtual machine in Windows Azure is still just SQL Server. You can use SQL Server Management Studio to connect to it, and you can use all of the same management tools and agents you use for your on-premises database servers.<br />
<strong>Can we migrate our databases with minimal interruption of service, and without breaking our existing applications?</strong><br />
Once again, here’s a demonstration:<br />
<iframe allowfullscreen="" frameborder="0" height="480" src="//www.youtube.com/embed/BfnKWL7UZp0?rel=0" width="640"></iframe><br />
Note that this demonstration is based on pre-release software, and may not reflect what actually ships with SQL Server 2014. However, it’s clear that the intention is to include a simple, wizard-based tool that will help you easily migrate on-premises SQL Server databases to Windows Azure virtual machines.<br />
<h3>
Conclusion</h3>
Migration of IT services to the cloud is inevitable. There are simply too many cost, scalability, and mobility advantages to justify not doing it. However, I don’t think it will happen in one big mass movement – and in particular, I think corporate databases will be among the last elements to be migrated. For at least a while, probably many years, we’ll be living in a hybrid world where some data is managed on-premises, and other data is moved to the cloud. To support that scenario, we need tools and technologies that make it easy to move data from one place to the other, and to manage it consistently wherever it’s hosted.<br />
The combination of SQL Server on-premises, Windows Azure SQL Database, and SQL Server in a Windows Azure virtual machine manages to pull this trick off well. With similar merging of private and public cloud network infrastructure support in Windows Server and System Center, the lines between “the cloud” and “the enterprise” are blurring to the point where, from an IT management perspective, it really doesn’t matter where a service is physically located.<br />
If you want to learn more about Windows Azure database options, visit <a href="http://www.windowsazure.com/en-us/solutions/data-management/" title="http://www.windowsazure.com/en-us/solutions/data-management/">http://www.windowsazure.com/en-us/solutions/data-management/</a>.Graeme Malcolmhttp://www.blogger.com/profile/02246562883877200692noreply@blogger.com0tag:blogger.com,1999:blog-2066281766861287065.post-47350936738109904222013-07-05T14:39:00.001+01:002013-10-29T10:29:32.209+00:00What the Heck is Hekaton?<p>SQL Server 2014 introduces a new in-memory OLTP capability that was previously known by its codename, “Hekaton”. The technology introduces two new concepts to SQL Server: memory-optimized tables and native stored procedures. This article explores these features and provides a simple demonstration of how to use them.</p> <p>The idea of optimizing data access performance by using in-memory storage is not new. SQL Server has always used caching to keep recently accessed data in memory, and recent releases have seen the addition of in-memory technology for large volume data analytics (PowerPivot and tabular models in Analysis Services) and high-performance table indexes that primarily benefits data warehouse workloads (columnstore indexes). What’s new in SQL Server 2014 is the ability to optimize an entire table for in-memory storage, effectively eliminating disk i/o for CRUD operations and massively improving query performance.</p> <p><strong>Memory-Optimized Tables</strong></p> <p>Memory-optimized tables are tables that you define using CREATE TABLE statements, in a similar fashion to traditional disk-based tables. However, memory-optimized tables are different from disk-based tables in the following ways:</p> <ul> <li>The CREATE TABLE statement is used to generate a C struct, which is in turn compiled into a DLL and loaded into memory. <li>All data for the table is stored in memory, and all operations on the data occur in memory. By default, memory-optimized tables are durable (so they’re persisted to disk in order to survive restarts and support high-availability); but when the database is online, the table is always accessed directly in memory with no need to read pages from disk. <li>Columns in memory-optimized tables are indexed using hash indexes (range indexes may be supported in a later build), in which the result of hashing the indexed value determines the in-memory “bucket” in which the row is stored. Rows with the same hashed value are stored as a linked list within the bucket. <li>Table data is persisted to disk as a stream, not in 8K pages like a traditional table. The data must be stored in a filegroup that is created with the CONTAINS MEMORY_OPTIMIZED_DATA option. Indexes are not persisted, and will be regenerated in the event of a restart. <li>Some data types - notably text, image, and nvarchar(max) - are not supported. Similarly some features such as identity columns and foreign-key constraints cannot be used in memory-optimized tables.</li></ul> <p><strong>Native Stored Procedures</strong></p> <p>Memory-optimized tables can co-exist with disk-based tables, and you can execute Transact-SQL queries that contain joins between disk-based tables and memory-optimized tables. In fact, you can use Transact-SQL to query memory optimized tables just like any other table, so you can improve the performance of some workloads by changing existing disk-based tables to memory-optimized tables without breaking existing applications that query them. The ability to use regular Transact-SQL to query memory optimized tables is provided by an interop layer in the SQL Server engine that does the necessary work to convert Transact-SQL statements into C code that can access the in-memory data structures in the compiled DLL for the table. </p> <p>However, if your application code only needs to access data in memory optimized tables, you can further improve performance by using native stored procedures. Native stored procedures are created using the familiar CREATE STORED PROCEDURE statement to define the Transact-SQL statements you want to execute. The code is then translated into C and compiled into a DLL, just like a memory optimized table. The DLL is then loaded into memory, and since the instructions it contains are now compiled as native machine code, execution performance is greatly improved. There are some limitations in this release, and only the most commonly used Transact-SQL statements and functions are supported in native stored procedures; but for a large percentage of common database workloads, you should find that using memory optimized tables and native stored procedures can significantly improve application performance.</p> <p>The following demonstration shows how to use memory optimized tables and native stored procedures. </p> <p align="center"><iframe height="600" src="http://www.youtube.com/embed/uYuQNgEmvvI?feature=player_detailpage&ap=%2526fmt%3D18" frameborder="0" width="800" allowfullscreen></iframe></p> <p>So, should you convert all of your tables and stored procedures to take advantage of this new technology? Probably not (at least, not yet). There are some workloads where the new in-memory capabilities will bring enormous benefits in terms of improved performance; but there are also some cases where current limitations prevent them from being used. Even when an existing disk-based table is fully compatible with a memory optimized schema, you may find minimal improvement for some i/o workloads.</p> <p>The important thing to understand when planning to use (or not use) memory optimized tables, is that the performance benefit is not purely a result of storing the data in memory. After all, SQL Server does a pretty good job of caching commonly accessed data in disk-based tables anyway. The crucial difference to the way data in a memory optimized table is accessed is that no locks or latches are used to support concurrency. In a disk-based table, if multiple transactions need to access the data concurrently, locks are used to ensure consistency and avoid one transaction’s results being affected by the data modifications of another transaction. Although SQL Server does support row-level locking, transactions that affect multiple rows can quickly escalate locking to page-level – causing concurrency issues that affect query performance. This can be especially acute in tables with so-called “hotspots” – for example a table with a clustered index on an incrementing key value, where all new rows are inserted at the end of the table. Memory optimized tables do not use locks to manage concurrency. Instead, a form of row-versioning is used to track modifications to rows by multiple transactions; which in any case usually happen so quickly (sub-millisecond) that concurrency clashes are extremely rare. If the i/o pattern for your table typically incurs a lot of locking, then making the table memory optimized will probably improve performance. If not, then you may not benefit significantly from changing the table. As an example, in the video demo, the 500,000 inserts were wrapped in a single transaction – which when executed against the disk-based table incurred locking to support isolation for 500,000 atomic INSERT statements. When creating the demo, I noticed that removing the BEGIN TRAN and COMMIT statements that enclose the loop (so that the inserts were done as 500,000 independent INSERT statements) resulted in a much less significant difference in the time taken to load the disk-based table and the time taken to load the memory optimized table (typically, the memory optimized table was around 5-6 seconds quicker).</p> <p>This article is based on the CTP 1 release of SQL Server 2014, and details are liable to change between now and the eventual release of the product. You can download the preview of SQL Server 2014 from <a title="http://www.microsoft.com/en-us/sqlserver/sql-server-2014.aspx" href="http://www.microsoft.com/en-us/sqlserver/sql-server-2014.aspx">http://www.microsoft.com/en-us/sqlserver/sql-server-2014.aspx</a>.</p> <p>The Transact-SQL code used in this demonstration is available from <a href="http://sdrv.ms/16iBxKp" target="_blank">here</a>.</p> Graeme Malcolmhttp://www.blogger.com/profile/02246562883877200692noreply@blogger.com0tag:blogger.com,1999:blog-2066281766861287065.post-77171055881003934232013-07-03T13:52:00.001+01:002013-07-08T17:38:55.915+01:00GeoFlow Demo for Course 20467B<p>Yesterday <a href="http://graemesplaceblog.blogspot.co.uk/2013/07/data-explorer-demo-for-course-20467b.html" target="_blank">I posted a demo</a> that Microsoft Certified Trainers can use in course <a href="http://www.microsoft.com/learning/en/us/course.aspx?ID=20467B">20467B: Designing Business Intelligence Solutions with Microsoft SQL Server 2012</a> to show students how to use the Data Explorer add-in for Excel 2013. GeoFlow is another new Excel add-in that you might want to demonstrate in class. It enables users to visualize data that includes geographic and temporal dimensions on an animated map, showing how data points in specific geographic locations change over time. You can get the GeoFlow add-in from the <a href="http://office.microsoft.com/en-us/download-geoflow-for-excel-FX104036784.aspx" target="_blank">Microsoft Office web site</a>, and you can <a href="http://sdrv.ms/10x7UlN" target="_blank">download the demo steps in a PDF document from here</a>.</p> <p>Click the thumbnail below to view the demonstration in a new window/tab.</p> <p><iframe height="276" src="https://skydrive.live.com/embed?cid=DDAD9079CFF45619&resid=DDAD9079CFF45619%21735&authkey=AKxtSyGo5gByXcE" frameborder="0" width="319" scrolling="no"></iframe></p> <p>Note that GeoFlow is a preview release at the moment, and is subject to change in the future. The demo is provided as-is, and no support will be provided for it by Microsoft Learning or Content Master.</p> <p>Enjoy!</p> <p> </p> <p>Update: GeoFlow has been renamed <strong>Power Map</strong>, and forms part of the Power BI capability being added to Microsoft Office 365.</p> Graeme Malcolmhttp://www.blogger.com/profile/02246562883877200692noreply@blogger.com0tag:blogger.com,1999:blog-2066281766861287065.post-42661465633920661342013-07-02T16:09:00.001+01:002013-07-08T17:42:44.847+01:00Data Explorer Demo for Course 20467B<p>Earlier this year we released Microsoft Official Curriculum course <a href="http://www.microsoft.com/learning/en/us/course.aspx?ID=20467B" target="_blank">20467B: Designing Business Intelligence Solutions with Microsoft SQL Server 2012</a>. Since then, Microsoft has released a preview of <a href="http://office.microsoft.com/en-us/excel/download-data-explorer-for-excel-FX104018616.aspx" target="_blank">Data Explorer</a>, an add-in for Excel that enables users to browse and query data in a variety of sources. Data Explorer builds on the self-service BI techniques taught in course 20467B, and if you are an instructor delivering the course, you can add value by demonstrating how to use it within the context of a Microsoft-based BI solution. To help you, I’ve put together a simple demo that should be easy to set up and perform in the virtual machine environment for the course. It will probably work best towards the end of module 8 (<em>Designing a Microsoft Excel-Based Reporting Solution</em>), and you can <a href="http://sdrv.ms/10x7UlN" target="_blank">download the steps in a PDF document from here</a>.</p> <p>Click the thumbnail below to view the demo.</p> <p><iframe style="height: 286px; width: 324px" height="120" src="https://skydrive.live.com/embed?cid=DDAD9079CFF45619&resid=DDAD9079CFF45619%21733&authkey=ABuYOg4aJbkICV4" frameborder="0" width="98" scrolling="no"></iframe></p> <p>Of course, bear in mind that Data Explorer is a preview release at the moment, and is subject to change in the future. The demo is provided as-is, and no support will be provided for it by Microsoft Learning or Content Master. Nevertheless, I hope you find it useful!</p> <p>Update: Data Explorer has been renamed <strong>Power Query</strong>, and forms part of the Power BI capability being added to Microsoft Office 365.</p> Graeme Malcolmhttp://www.blogger.com/profile/02246562883877200692noreply@blogger.com1tag:blogger.com,1999:blog-2066281766861287065.post-50374800723877592512012-10-05T16:11:00.001+01:002012-10-05T16:11:41.932+01:00Role Playing Games with SQL Server 2012 Analysis Services<p>I’m currently working with Microsoft Learning, writing a course on designing BI solutions with SQL Server 2012. Obviously, this is a huge subject to try to cover, and raises a whole bunch of really interesting design considerations. One of the new things BI developers need to consider with SQL Server 2012, is whether to use a “traditional” multidimensional data model, or whether to use the new-fangled tabular model. In most cases, from an end-user’s perspective (no pun intended), there is little to pick between the two. In fact, in an Excel PivotTable, most users will struggle to spot any difference. However, for the cube developer, there are some significant differences. There are some things you can do easily in multidimensional projects (or indeed, things that are done automatically for you by SQL Server data Tools) which require (sometimes extremely complex) custom development in a tabular model. Other things are relatively straightforward to accomplish in both models, but require different implementations. An example of the latter is the implementation of role-playing dimensions. You can do this in both models, but there are some differences.</p> <p>Role-playing dimensions are used to create multiple cube dimensions that are based on the same underlying dimension in the database. The classic example is a date dimension in which each member represents a calendar date. In your cube, you may have a a <em>Sales Order </em>measure group that is related to the <em>Date</em> dimension by multiple keys, for example an <em>Order Date</em> and a <em>Delivery Date.</em> Another example might be an <em>Address</em> dimension that is related to a <em>Shipment</em> measure group by both an <em>Origin</em> key and a <em>Destination</em> key. This multi-use of the same underlying dimension means that the dimension table is defined only once, but users can use it to slice the data by different keys – so for example, a user could view sales by order date or by delivery date (or both).</p> <p>OK, so first, let’s see how a role-playing dimension is implemented in a multidimensional model. I’m using the AdventureWorksDW2012 sample database, which contains a <em>FactResellerSales </em>table that is related to a <em>DimDate</em> table using three key columns – <em>OrderDateKey</em>, <em>ShipDateKey</em>, and <em>DueDateKey</em>. When I create a data source view from the data warehouse tables in the multidimensional project, all three of the relationships are detected as shown here.</p> <p><a href="http://lh4.ggpht.com/-NNCXGze9DdQ/UG74SY8I12I/AAAAAAAAAYg/LQO8c7jMo-0/s1600-h/image%25255B19%25255D.png" target="_blank"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/-R6EVrKMPzEA/UG74UW7cHJI/AAAAAAAAAYo/emC5lS2OY9I/image_thumb%25255B13%25255D.png?imgmax=800" width="916" height="772" /></a></p> <p>Using the wizard to create a cube automatically detects the multiple relationships, and results in a single <em>DimDate</em> dimension in the database but three role-playing dimensions in the cube (<em>Order Date</em>, <em>Ship Date</em>, and<em> Due Date</em>) as shown here.</p> <p><a href="http://lh5.ggpht.com/-Px67bKSe_tQ/UG74VdrRlaI/AAAAAAAAAYw/Vpw9bL3rdYQ/s1600-h/image%25255B18%25255D.png" target="_blank"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/-1P_LAd1bZm8/UG74XLlH1NI/AAAAAAAAAY4/bdBS5WBWbJM/image_thumb%25255B12%25255D.png?imgmax=800" width="916" height="772" /></a></p> <p>The role-playing dimensions are really just references to the same <em>DimDate</em> dimension, but aggregations will be calculated based on each relationship. I’ll go ahead and add a hierarchy to the <em>DimDate</em> dimension:</p> <p><a href="http://lh4.ggpht.com/-QxrlwNV65gE/UG74YNbdLmI/AAAAAAAAAZA/j6q7O5Gln6U/s1600-h/image%25255B24%25255D.png" target="_blank"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/-PvqG9VdFoEM/UG74Z2RwxYI/AAAAAAAAAZI/E2J46A_fEjg/image_thumb%25255B16%25255D.png?imgmax=800" width="916" height="772" /></a></p> <p>When a user browses the cube in Excel, each of the three role-playing dimensions is available for them to slice the sales data, and all three of these dimensions have the same <em>Calendar Date</em> hierarchy that I defined for the base <em>DimDate</em> dimension:</p> <p><a href="http://lh5.ggpht.com/-2jaBjOPuPlw/UG74bPW2s_I/AAAAAAAAAZQ/HijRS-WSTEA/s1600-h/image%25255B29%25255D.png" target="_blank"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh3.ggpht.com/-GcwrGONdJMs/UG74cl7CnqI/AAAAAAAAAZY/FN7LnoyF2FQ/image_thumb%25255B19%25255D.png?imgmax=800" width="916" height="772" /></a></p> <p>Now let’s compare the experience with a tabular model. When I import the same tables into a tabular model project, the relationships are detected, and I can create the same hierarchy as before in the <em>DimDate</em> table. However, notice that two of the relationships are shown as dotted lines, while one is shown as a solid line.</p> <p><a href="http://lh5.ggpht.com/-zKePvXgVks4/UG74eEROkEI/AAAAAAAAAZg/gXw2NboReSU/s1600-h/image%25255B34%25255D.png" target="_blank"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh5.ggpht.com/-t_z2kfTDc7Q/UG74gNsmpbI/AAAAAAAAAZo/phMdWi6m18o/image_thumb%25255B22%25255D.png?imgmax=800" width="916" height="772" /></a></p> <p> This indicates that although the relationships have all been detected, only one of them is <em>active</em> at any one time. When a user browses the model in Excel, they only see one <em>DimDate</em> dimension, which will show aggregations for the active relationship (in this case, <em>Order Date</em>, but there’s no easy way for the user to tell that from the user interface):</p> <p><a href="http://lh4.ggpht.com/-5zZQznIy9Zc/UG74hVC87YI/AAAAAAAAAZw/12-szZ_JbME/s1600-h/image%25255B39%25255D.png" target="_blank"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/-S-t_cWI88j8/UG74ihj7t9I/AAAAAAAAAZ4/kGsvBwe7iSM/image_thumb%25255B25%25255D.png?imgmax=800" width="916" height="772" /></a></p> <p>The solution to this problem is obvious. So obvious in fact, that it took me a while to figure it out! The answer is to import the same table multiple times, and rename it appropriately:</p> <p><a href="http://lh4.ggpht.com/-oL2o1QmlvPw/UG74j1U-WnI/AAAAAAAAAaA/fHCL7sBAG7E/s1600-h/image%25255B44%25255D.png" target="_blank"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh5.ggpht.com/-R74DiWLNjlM/UG74lr98tlI/AAAAAAAAAaI/yvoU6LzVg1I/image_thumb%25255B28%25255D.png?imgmax=800" width="916" height="772" /></a></p> <p>After you’ve imported one copy of the table for each role-playing dimensions, you simply delete the <em>inactive</em> relationships from the original table, and create new ones to join the relevant keys in the fact table to the new dimension tables. You’ll also need to create duplicates of any hierarchies you want to appear in all of the dimensions.</p> <p><a href="http://lh6.ggpht.com/-bTFYapUBcyw/UG74m2uAjoI/AAAAAAAAAaQ/ciWC35rTwDk/s1600-h/image%25255B49%25255D.png" target="_blank"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh5.ggpht.com/-IVb2pzQgF94/UG74oVUVLqI/AAAAAAAAAaY/rB4Q5Sk0bVk/image_thumb%25255B31%25255D.png?imgmax=800" width="916" height="772" /></a></p> <p>Now when users browse the model, they’ll see all three dimensions, and as long as you’ve assigned appropriate names to each copy of the table, it should be obvious what each dimension represents.</p> <p><a href="http://lh4.ggpht.com/-4JSuExV2d3w/UG74pkbflYI/AAAAAAAAAag/gLD5qtDG_mY/s1600-h/image%25255B54%25255D.png" target="_blank"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/-Q2ev18oBGFE/UG74qzxcUSI/AAAAAAAAAao/6JKCpfH_J90/image_thumb%25255B34%25255D.png?imgmax=800" width="916" height="772" /></a></p> Graeme Malcolmhttp://www.blogger.com/profile/02246562883877200692noreply@blogger.com3tag:blogger.com,1999:blog-2066281766861287065.post-14359142475054097132012-07-23T12:28:00.001+01:002012-07-23T12:28:05.758+01:00PowerPivot and Power View in Excel 2013<p>It’s just typical of my job that just a few short weeks after the publication of some Microsoft Official Curriculum courses that I’ve spent months working on, Microsoft should choose to make a preview of the next release of the software on which they are based  available! As you may know, we recently published courses <a href="http://www.microsoft.com/learning/en/us/Course.aspx?ID=10778A" target="_blank">10778A</a> and <a href="http://www.microsoft.com/learning/en/us/Course.aspx?ID=40009A" target="_blank">40009A</a>, both of which  make use of the PowerPivot and Power View features in Excel and SharePoint 2010; so it was with a  certain amount of trepidation that I installed the preview of Office 2013 to get a  first look at the enhancements that have been made.</p> <p>The first, and most obvious, change is that the PowerPivot  add-in for Excel no longer needs to be installed from a separate package. It’s built into Excel and only needs to be enabled, which you do by configuring the COM Add-ins in Excel’s options as shown here.</p> <p><a href="http://lh4.ggpht.com/-l2FUnrsWScQ/UA00_eCVycI/AAAAAAAAAWQ/Ds9C3V41v2U/s1600-h/Picture1%25255B4%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture1" border="0" alt="Picture1" src="http://lh4.ggpht.com/-ZQpFWNltfok/UA01AtfkIdI/AAAAAAAAAWY/n_XDqK-t7HE/Picture1_thumb%25255B2%25255D.png?imgmax=800" width="640" height="413" /></a></p> <p>Note that there’s also a Power View add-in – more about that later!</p> <p>After the PowerPivot add-in has been enabled, users will see the <strong>POWERPIVOT</strong> tab on the ribbon, as shown here.</p> <p><a href="http://lh3.ggpht.com/-cNUgWr_X6p0/UA01B_eWjMI/AAAAAAAAAWg/3HBcXtuyUHc/s1600-h/Picture2%25255B4%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture2" border="0" alt="Picture2" src="http://lh4.ggpht.com/-4ZDk3F6vpYw/UA01CoNoDGI/AAAAAAAAAWo/zefA1S8FcPI/Picture2_thumb%25255B2%25255D.png?imgmax=800" width="640" height="456" /></a></p> <p>With this ribbon, you can not only manage a PowerPivot tabular data model for the workbook as you can in Excel 2010, but you can also create calculated fields and KPIs without having to directly edit the model – making the process a little bit more intuitive for information workers.</p> <p>Clicking <strong>Manage</strong> opens the PowerPivot window, which is similar to that of the previous release. There are a few enhancements of course, but anyone familiar with PowerPivot in Excel 2010 will find themselves in familiar territory. In this case, I’ve opened a PowerPivot workbook I created with Excel 2010 based on data in the <strong>AdventureWorksDW</strong> SQL Server sample database. The changes to this release meant that I was prompted to allow Excel to update the data model and re-save the workbook, so one thing to be aware of is that you can open (and update) Excel 2010 PowerPivot workbooks in Excel 2013, but after they’ve been updated you won’t be able to open them in Excel 2010. You can see the diagram view of my PowerPivot data model below – note that it includes a hierarchy in the <strong>Sales Territory</strong> table.</p> <p><a href="http://lh3.ggpht.com/-Y4qW2VOTtoE/UA01D9mGu3I/AAAAAAAAAWw/K471qJHKZLc/s1600-h/Picture3%25255B4%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture3" border="0" alt="Picture3" src="http://lh4.ggpht.com/-8NLc4trXIFo/UA01FLpiL_I/AAAAAAAAAW4/NVPm59pJDaQ/Picture3_thumb%25255B2%25255D.png?imgmax=800" width="640" height="460" /></a></p> <p>After you’ve created the data model in your workbook, you can use it as a source for PivotTables, just as you could in Excel 2010. There are however, one or two nice enhancements on a new <strong>ANALYZE</strong> tab of the ribbon that make it easier to do things like create slicers. Another new feature is the ability to create timeline filters that make it easier to analyse data based on chronological periods. To add a timeline, just click <strong>Insert Timeline</strong> and specify any of the time-based attributes that Excel identifies as having a suitable relationship in the model.</p> <p><a href="http://lh3.ggpht.com/-6Ooi9m-1Krk/UA01Gj97OlI/AAAAAAAAAXA/PowRweiJfkc/s1600-h/Picture4%25255B4%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture4" border="0" alt="Picture4" src="http://lh3.ggpht.com/-2GO-dxHXP7Q/UA01H91eRKI/AAAAAAAAAXI/_oFZ2aDKVpM/Picture4_thumb%25255B2%25255D.png?imgmax=800" width="640" height="456" /></a></p> <p>After you’ve inserted a timeline, you can use it to filter the data in the PivotTable as shown here.</p> <p><a href="http://lh5.ggpht.com/-9EYv7_u3Cqo/UA01I7ag1II/AAAAAAAAAXQ/8mJboMbGsEM/s1600-h/Picture5%25255B4%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture5" border="0" alt="Picture5" src="http://lh5.ggpht.com/-RtL5sN0ZLkM/UA01J-b8t_I/AAAAAAAAAXY/BrGge3mr4qA/Picture5_thumb%25255B2%25255D.png?imgmax=800" width="640" height="456" /></a></p> <p>Earlier, I mentioned that Excel 2013 includes a Power View add-in. This enables information workers to create Power View reports from the data model in the workbook (and external data sources). Previously, Power View was only available in SharePoint Server 2010, but in Office 2013 you can use it right there in an Excel workbook.</p> <p>To create a Power View report from the data model in the workbook, just click Power View on the <strong>INSERT</strong> tab of the ribbon.</p> <p><a href="http://lh3.ggpht.com/-NxuMeSZauf4/UA01K-tWL_I/AAAAAAAAAXg/cx3dsYceFJM/s1600-h/Picture6%25255B4%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture6" border="0" alt="Picture6" src="http://lh5.ggpht.com/-xYwD_FWZ3WE/UA01LgGJ-GI/AAAAAAAAAXo/K-gaHJk1yqc/Picture6_thumb%25255B2%25255D.png?imgmax=800" width="640" height="114" /></a></p> <p>If necessary, you’ll be prompted to install Silverlight (which is required by the Power View add-in), and after doing so you’ll be able to create a Power View report from the data in your PowerPivot data model as shown here.</p> <p><a href="http://lh5.ggpht.com/-0KJHHHtw5go/UA01MkgZKNI/AAAAAAAAAXw/MvGUaCE4u_M/s1600-h/Picture7%25255B4%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture7" border="0" alt="Picture7" src="http://lh6.ggpht.com/-S9PTjciOano/UA01N0CtrkI/AAAAAAAAAX4/8558EuqmvHk/Picture7_thumb%25255B2%25255D.png?imgmax=800" width="640" height="456" /></a></p> <p>Note that you can include hierarchies in a Power View report, which wasn’t supported in the previous release. There are several other enhancements in this release, including support for new data visualizations (such as pie charts), and even visualization of geographical data on a Bing Maps map, as shown here.</p> <p><a href="http://lh5.ggpht.com/-oaHxzJNQXCw/UA01PYRWMJI/AAAAAAAAAYA/x9IzAX6hI8o/s1600-h/Picture8%25255B4%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture8" border="0" alt="Picture8" src="http://lh6.ggpht.com/-WEKZ5wb3OWI/UA01Qn6KEuI/AAAAAAAAAYI/kNPY0KuV2FM/Picture8_thumb%25255B2%25255D.png?imgmax=800" width="640" height="456" /></a></p> <p>This short article just highlights a few of the improvements to PowerPivot and Power View in Excel 2013. There are many more new features in Excel, as well as greater ability to share BI capabilities across the enterprise through enhancements in SharePoint 2013 and SQL Server 2012 SP1, which I look forward to exploring in more depth.</p> Graeme Malcolmhttp://www.blogger.com/profile/02246562883877200692noreply@blogger.com0tag:blogger.com,1999:blog-2066281766861287065.post-72463251776305260432012-07-03T16:18:00.001+01:002012-07-03T16:19:25.996+01:00Matching Data with SQL Server 2012 Data Quality Services<p>In  a <a href="http://graemesplaceblog.blogspot.co.uk/2012/04/cleansing-data-with-sql-server-2012.html" target="_blank">previous post</a>, I described how you can use Data Quality Services (DQS) to create a knowledge base for the domains (data columns) used in your business data and use it to cleanse data by correcting invalid or inconsistent values. Data cleansing is however only one side of the coin when it comes to DQS. You can also use DQS to perform data matching – in other words, finding records that potential duplicates of one another and consolidating them to a single surviving record.</p> <p>When you think about it, the potential for duplicate data entry in most complex business environments is enormous. For example, let’s imagine an e-commerce site where customers need to register before placing orders. It’s perfectly conceivable that a customer who only uses the site occasionally might re-register with slightly different details because they’ve forgotten that they had registered previously or can’t remember their login credentials.  Even if the site applies a policy that demands a unique email address for each registration, there’s nothing to stop the same customer registering multiple times with different email addresses. For an individual sales order, the fact that the customer is registered multiple times is inconsequential – as long as the payment and delivery address details are correct, the order can be processed successfully. However, then the company wants to use its data to perform any kind of business intelligence (BI) reporting or analysis that aggregates information per customer, then the duplicate entries can lead to misleading results.</p> <p>To use DQS to match data, you must first add a <em>matching policy</em> to a knowledge base. You can use an existing knowledge base that is also used for data cleansing, or you can create a knowledge base specifically for data matching. In this example, I’m opening an existing knowledge base that contains domains for customer records for the <strong>Matching Policy</strong> activity.</p> <p><a href="http://lh4.ggpht.com/-bBkcdCLAQy4/T_MNITE_uNI/AAAAAAAAATc/E67tCK8bMxk/s1600-h/Picture1%25255B5%25255D.jpg" target="_blank"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture1" border="0" alt="Picture1" src="http://lh4.ggpht.com/-WvGC9CD6EU4/T_MNJGzMLeI/AAAAAAAAATk/ThkzI0osE1Y/Picture1_thumb%25255B3%25255D.jpg?imgmax=800" width="644" height="458" /></a></p> <p>Just as when performing knowledge discovery, I need to map some sample data to the domains defined in the knowledge base. This enables me to test the matching policy against a known data set as I build it, and therefore verify that it successfully identifies known duplicate records. In this case, I’m using data in an Excel workbook as the source for my sample data, but you can also use a table in a SQL Server database.</p> <p><a href="http://lh5.ggpht.com/-vUxFzZb6q8U/T_MNJ_IaLDI/AAAAAAAAATs/brR9ck4RCyY/s1600-h/Picture2%25255B4%25255D.jpg" target="_blank"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture2" border="0" alt="Picture2" src="http://lh4.ggpht.com/-7Mjfc_-IBZg/T_MNKohGrDI/AAAAAAAAAT0/_WEm4hc82Oc/Picture2_thumb%25255B2%25255D.jpg?imgmax=800" width="644" height="455" /></a></p> <p>Having mapped sample data to the domains, I can now define the matching rules for my matching policy. You can include multiple rules, and each one uses a set of weighted comparisons of domain values to identify clusters of records that are potential duplicates of one another.</p> <p><a href="http://lh5.ggpht.com/-10DYrWyzInM/T_MNLWsUedI/AAAAAAAAAT8/LbN124ANic4/s1600-h/Picture3%25255B4%25255D.jpg" target="_blank"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture3" border="0" alt="Picture3" src="http://lh6.ggpht.com/-jJ-7xoy8jbM/T_MNMKqCqaI/AAAAAAAAAUE/5qQFPzwB-fE/Picture3_thumb%25255B2%25255D.jpg?imgmax=800" width="644" height="456" /></a></p> <p>Potential matches are determines based on a score that is calculated from the weighted comparisons you define in the rule. Here are the comparisons I’ve used in my <strong>Match Customer</strong> rule:</p> <div align="left"> <table border="0" cellspacing="0" cellpadding="2" width="651" align="center"><tbody> <tr> <td valign="top" width="245"><strong>Domain</strong></td> <td valign="top" width="122"><strong>Similarity</strong></td> <td valign="top" width="164"><strong>Weight</strong></td> <td valign="top" width="118"><strong>Prerequisite</strong></td> </tr> <tr> <td valign="top" width="245">Birth Date</td> <td valign="top" width="122">Exact</td> <td valign="top" width="164"> </td> <td valign="top" width="118"> <p align="center">X</p> </td> </tr> <tr> <td valign="top" width="245">Email Address</td> <td valign="top" width="122">Exact</td> <td valign="top" width="164">20</td> <td valign="top" width="118"> </td> </tr> <tr> <td valign="top" width="245">Postal Code</td> <td valign="top" width="122">Exact</td> <td valign="top" width="164">10</td> <td valign="top" width="118"> </td> </tr> <tr> <td valign="top" width="245">Country/Region</td> <td valign="top" width="122">Exact</td> <td valign="top" width="164">10</td> <td valign="top" width="118"> </td> </tr> <tr> <td valign="top" width="245">First Name</td> <td valign="top" width="122">Similar</td> <td valign="top" width="164">10</td> <td valign="top" width="118"> </td> </tr> <tr> <td valign="top" width="245">Last Name</td> <td valign="top" width="122">Similar</td> <td valign="top" width="164">10</td> <td valign="top" width="118"> </td> </tr> <tr> <td valign="top" width="245">Street Address</td> <td valign="top" width="122">Similar</td> <td valign="top" width="164">20</td> <td valign="top" width="118"> </td> </tr> <tr> <td valign="top" width="245">City</td> <td valign="top" width="122">Similar</td> <td valign="top" width="164">10</td> <td valign="top" width="118"> </td> </tr> <tr> <td valign="top" width="245">State</td> <td valign="top" width="122">Similar</td> <td valign="top" width="164">10</td> <td valign="top" width="118"> </td> </tr> </tbody></table> </div> <p>Note that an exact match of the <strong>Birth Date</strong> domain is specified as a prerequisite. In other words, only records where the birth date is an exact match will be considered as candidates for a potential duplicate. Prerequisite domains in a matching rule must use the <strong>Exact</strong> similarity and have no weighting value. All of the other domains are calculated based on an exact or similar match, and have weightings, which add up to a total of 100. </p> <p>Assuming the birth date for the records being compared is a match, DQS then makes the other comparisons defined in the matching rule and adds the specified weighting value for each comparison that is true to produce an overall score. For example, consider two records with identical <strong>Birth Date</strong> values being compared using the <strong>Match Customer</strong> rule defined above. If the  <strong>Email Address</strong> domains for both records is an exact match, 20 is added to the score. If the <strong>First Name</strong> domains are similar (for example, “Rob” and “Robert”), another 10 is added to the score, and so on until all of the comparisons in the rule have been made. The resulting score is then compared to the minimum matching score defined for the matching rule (in this case 80). If the score exceeds the minimum matching score, then the records are considered a match. Multiple records that are considered matches for one another are grouped into a cluster.</p> <p>After you have defined the matching rules, you can use them to find matches in the sample data you mapped earlier. This gives you the opportunity to verify that the rules behave as expected against a known dataset. In this case, the dataset results in a single cluster of matches that includes two records – one for Daniel Garcia and another for Dan Garcia.</p> <p><a href="http://lh4.ggpht.com/-tT8M53JKd18/T_MNMxy9hWI/AAAAAAAAAUM/t-J_VpJ0Kvk/s1600-h/Picture4%25255B4%25255D.jpg" target="_blank"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture4" border="0" alt="Picture4" src="http://lh5.ggpht.com/-Sw5FLDfEPAI/T_MNNj5DyRI/AAAAAAAAAUU/Ff1vPFwnVZU/Picture4_thumb%25255B2%25255D.jpg?imgmax=800" width="644" height="456" /></a></p> <p>Now that I’ve defined my matching policy, I can publish the knowledge base and allow the data stewards in my organization to use it for data matching.</p> <p>To use a knowledge base to perform data matching, create a new data quality project, specify the knowledge base, and specify the <strong>Matching</strong> activity as shown here.</p> <p><a href="http://lh4.ggpht.com/-tu3BoUvc9hU/T_MNObpTaTI/AAAAAAAAAUc/8zopj2Xs4Z4/s1600-h/Picture5%25255B4%25255D.jpg" target="_blank"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture5" border="0" alt="Picture5" src="http://lh3.ggpht.com/-Ti405r-y5Uo/T_MNO8tYn6I/AAAAAAAAAUk/vNC99u-Wkvw/Picture5_thumb%25255B2%25255D.jpg?imgmax=800" width="644" height="456" /></a></p> <p>The first step, as it is in any DQS project, is to map the fields in your data source to the domains in the knowledge base. Just as before, the data source can be a table in a SQL Server database or an Excel file. This time, I’m using the <strong>Customers</strong> table in the <strong>Staging</strong> SQL Server database.</p> <p><a href="http://lh6.ggpht.com/-fLP0Dwq06PI/T_MNPgqoo_I/AAAAAAAAAUs/7wj7e6bmy7w/s1600-h/Picture6%25255B4%25255D.jpg" target="_blank"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture6" border="0" alt="Picture6" src="http://lh5.ggpht.com/-wr1YXf3I9bs/T_MNQKMdp6I/AAAAAAAAAU0/GKr-w8VwvX8/Picture6_thumb%25255B2%25255D.jpg?imgmax=800" width="644" height="455" /></a></p> <p>After you’ve mapped the domains, you can start the matching process. When the process is complete, the clusters of matched records is displayed. In this case, there are two clusters, each containing two matches. At tis stage, you can choose to reject any matches that you know aren’t duplicates.</p> <p><a href="http://lh3.ggpht.com/-9i6oNO-vPio/T_MNRBD_IuI/AAAAAAAAAU8/4H9ITAgTvL0/s1600-h/Picture7%25255B4%25255D.jpg" target="_blank"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture7" border="0" alt="Picture7" src="http://lh6.ggpht.com/-4HcCk-iEu7Y/T_MNRvGAKQI/AAAAAAAAAVE/tUdDUMkUFfY/Picture7_thumb%25255B2%25255D.jpg?imgmax=800" width="644" height="458" /></a></p> <p>When the matches have all been identified, you can export the results to a SQL Server table or an Excel file. You can also export survivors (one record from each cluster that is chosen as the correct one) based on one of the following survivorship rules:</p> <ul> <li><strong>Pivot record</strong> – A record in the cluster that is chosen arbitrarily by DQS.</li> <li><strong>Most complete and longest record</strong> – The record that has the fewest null field values and the longest overall data length.</li> <li><strong>Most complete record</strong> – The record that has the fewest null fields.</li> <li><strong>Longest record</strong> – The record that has the longest overall data length.</li> </ul> <p><a href="http://lh4.ggpht.com/-Ar5Kje9-Nx8/T_MNSCR0cHI/AAAAAAAAAVM/8Bwq_ylxFhI/s1600-h/Picture8%25255B4%25255D.jpg" target="_blank"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture8" border="0" alt="Picture8" src="http://lh6.ggpht.com/-yUXXDjmcktw/T_MNShcEsJI/AAAAAAAAAVU/7g-mu1YrTdI/Picture8_thumb%25255B2%25255D.jpg?imgmax=800" width="644" height="454" /></a></p> <p>The exported results include all of the source data with additional columns for the clusters of matching records to indicate the matching rule used and score calculated for each match, and the pivot record for each match cluster.</p> <p><a href="http://lh3.ggpht.com/-640ekxHTtYQ/T_MNTn5V8YI/AAAAAAAAAVc/ziYQ2LIk_IU/s1600-h/Picture9%25255B4%25255D.jpg" target="_blank"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture9" border="0" alt="Picture9" src="http://lh4.ggpht.com/-P2o09z5m5Ic/T_MNUUvjEzI/AAAAAAAAAVk/rOToG-7Sxvo/Picture9_thumb%25255B2%25255D.jpg?imgmax=800" width="644" height="455" /></a></p> <p>The exported survivors contain all of the non-matching records from the original data source and one version of each matched record based on the survivorship rule you selected. In the following example, I’ve highlighted the surviving records from my matching process.</p> <p><a href="http://lh5.ggpht.com/-GfD5xZMCErs/T_MNVXA0LhI/AAAAAAAAAVs/MFTmboLnKMw/s1600-h/Picture10%25255B4%25255D.jpg" target="_blank"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture10" border="0" alt="Picture10" src="http://lh4.ggpht.com/-1B0BoHNqZF0/T_MNWW7nh3I/AAAAAAAAAV0/WCorkrSuCzk/Picture10_thumb%25255B2%25255D.jpg?imgmax=800" width="644" height="459" /></a></p> <p>In some case, you can simply replace the original data set with the survivor records to create a de-duplicated set of records. However, in most business scenarios you’ll need to apply some logic (manual or automated) to handle relationships between duplicate records and other tables. For example, before I eliminate the duplicate customer records identified by the matching process in the above example, I would need to reassign any sales orders that are currently related to those customer records to the surviving records.</p> <p>Hopefully you’ve found this brief introduction to data matching with DQS useful. To learn more about DQS and its use in a data warehousing solution, you can attend Microsoft Official Curriculum (MOC) course <a href="http://www.microsoft.com/learning/en/us/Course.aspx?ID=10777A&Locale=en-us">10777A: Implementing a Data Warehouse with SQL Server 2012</a>.</p> Graeme Malcolmhttp://www.blogger.com/profile/02246562883877200692noreply@blogger.com1tag:blogger.com,1999:blog-2066281766861287065.post-5502322640371369642012-04-07T15:40:00.001+01:002012-04-07T15:40:55.475+01:00Cleansing Data with SQL Server 2012 Data Quality Services<p>I’ve been a bit quiet on the blogging side of things for a while, and in my defence I’ve been pretty heads-down working as a vendor for Microsoft as the lead author for a couple of new courses on SQL Server 2012 data warehousing and BI (courses <a href="http://www.microsoft.com/learning/en/us/Course.aspx?ID=10777A&Locale=en-us" target="_blank">10777A</a> and <a href="http://www.microsoft.com/learning/en/us/Course.aspx?ID=10778A&Locale=en-us" target="_blank">10778A</a> if you’re interested). As part of this work, I’ve been exploring the new data cleansing capabilities in SQL Server 2012 Data Quality Services (DQS). This article is a simple walkthrough of how to use DQS to cleanse data as part of an Enterprise Information Management (EIM) or Extract, Transform, and Load (ETL) solution.</p> <p>So, what is data cleansing all about then? Well, most people involved in building or managing data-driven applications and BI solutions will have come across the problem of inconsistent or invalid data values for columns (or “domains”) that are used for business analysis. For example, let’s suppose your database stores customer data, including the customer’s address; and you you want to count customer sales by country. When customers or sales employees enter customer data into the system, it’s perfectly possible (and actually quite likely given a large enough volume of customers) that some values will be either entered incorrectly (for example “Unted States” instead of “United States”) or inconsistently (for example, some customers may enter “United States”, some others may enter “USA”, and others still may enter “America”). When you try to aggregate sales, you’ll end up with inaccurate counts because there are several values in use for the same country.</p> <p>Here’s an Excel workbook containing a subset of data extracted from a SQL Server database table to show some typical data quality problems.</p> <p><a href="http://lh4.ggpht.com/-4rPa6O9plMU/T4BQUD27mdI/AAAAAAAAAMk/nfSPXijF0ZA/s1600-h/Picture1%25255B3%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture1" border="0" alt="Picture1" src="http://lh5.ggpht.com/-ynUGFIHUy0w/T4BQVQkrq4I/AAAAAAAAAMs/J8gZeT6ReYQ/Picture1_thumb%25255B1%25255D.jpg?imgmax=800" width="554" height="484" /></a></p> <p>Note that the data contains a number of problems, including:</p> <ul> <li>The <strong>City</strong> column contains “New York” and “NYC” for New York City.</li> <li>The <strong>Country</strong> column contains “United States” and “USA” for the US.</li> <li>The <strong>Country</strong> column also contains “United Kingdom” and “Great Britain” for the UK.</li> </ul> <p>DQS enables you to address this problem by cleansing the data based on a known set of values and rules for the key domains (columns) that exist in your datasets. The way that DQS does this is by enabling you to create and maintain a knowledge base that contains the known valid values for a related set of domains, along with validation rules (for example, an <strong>EmailAddress</strong> value must include a “@” character)  and common synonyms can be corrected to a leading value (for example, by correcting “USA” and “America” to the leading value “United States”). After you have created a knowledge base, you can use it to cleanse any data that includes the same domains (so for example, if you create a knowledge base for geographical domains such as <strong>City</strong>, <strong>State</strong>, and <strong>Country</strong>, you can use it to cleanse any data that includes these fields – such as customer data or employee address data. SQL Server 2012 includes the Data Quality Services Client tool (shown below), which you can use to create, maintain, and use DQS knowledge bases.</p> <p><a href="http://lh5.ggpht.com/-dSMzfJhcLjY/T4BQW6GxV8I/AAAAAAAAAM0/Eb1Sogr7eeM/s1600-h/Picture2%25255B5%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture2" border="0" alt="Picture2" src="http://lh3.ggpht.com/-K3YrjY_qgnM/T4BQXitQkFI/AAAAAAAAAM8/AN2vEJkS5bw/Picture2_thumb%25255B3%25255D.jpg?imgmax=800" width="563" height="394" /></a></p> <p>When you create a new knowledge base, you can do so from scratch, or you can use an existing knowledge base as a starting point. SQL Server 2012 ships with a pre-existing knowledge base for US-based demographic data named <strong>DQS Data</strong>, and in this example, I’ll use it as the basis for my own <strong>CustomerKB</strong> knowledge base as shown below.</p> <p><a href="http://lh6.ggpht.com/-7WctFvGn7Bo/T4BQZPRxfeI/AAAAAAAAANE/sLpk9HvWpTk/s1600-h/Picture3%25255B4%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture3" border="0" alt="Picture3" src="http://lh6.ggpht.com/-GoKwz9jclic/T4BQZ7ySIXI/AAAAAAAAANI/r6YXOpt3aTw/Picture3_thumb%25255B2%25255D.jpg?imgmax=800" width="559" height="396" /></a></p> <p>The <strong>DQS Data</strong> knowledge base includes a number of pre-defined domains, as shown in the image above. I only need some of these domains, and I’ll need to add some additional ones that are specific to my own data; so I’ve initially selected the <strong>Domain Management</strong> activity as I create the <strong>CustomerKB</strong> knowledge base. I only intend to use the <strong>Country/Region</strong>, <strong>US – Last Name</strong>, and <strong>US – State</strong> domains from the DQS Data knowledge base, so I’ll delete the others. The domains I’m retaining contain official values for country and US state names, and common last names (surnames) based on US demographic data such as the 2000 US census.</p> <p><a href="http://lh4.ggpht.com/-_1brmX-vo28/T4BQa9xcGSI/AAAAAAAAANU/3FLJ9jUNsgY/s1600-h/Picture4%25255B5%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture4" border="0" alt="Picture4" src="http://lh6.ggpht.com/-zsZFnb7BwUA/T4BQb4Van_I/AAAAAAAAANc/zZ5U8TH1wzE/Picture4_thumb%25255B3%25255D.jpg?imgmax=800" width="562" height="408" /></a></p> <p>Since my customer data includes records for customers all over the world, I’ll rename the domains in my knowledge base to remove the “US” prefix. I’ll also add a new domain named <strong>City</strong> so that I can validate city names in the data.</p> <p><a href="http://lh6.ggpht.com/-Rmg_g1l0mKg/T4BQdGpFBsI/AAAAAAAAANk/Ta-QL-bDqQ0/s1600-h/Picture5%25255B5%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture5" border="0" alt="Picture5" src="http://lh6.ggpht.com/-8JuxwUCHVLU/T4BRLXik1qI/AAAAAAAAAN0/J6X9rdBP-TE/Picture5_thumb%25255B3%25255D.jpg?imgmax=800" width="557" height="387" /></a></p> <p>Note that I can select each domain and view the known values that are currently defined in the knowledge base as shown below. The <strong>City</strong> domain has no known values (because I’ve just created it), and the others have inherited values from the <strong>DQS Data</strong> knowledge base. The image below shows the known values for the <strong>Country/Region</strong> domain. Note that the knowledge base defines <em>leading</em> values for each country (such as “Afghanistan”) and synonyms that, while valid in their own right, should be corrected to the leading value to ensure consistency.</p> <p><a href="http://lh5.ggpht.com/-uz_uGhE3-SM/T4BRMlciMiI/AAAAAAAAAN8/f4zPby2aBdU/s1600-h/Picture6%25255B4%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture6" border="0" alt="Picture6" src="http://lh3.ggpht.com/-5U0LgoWT50s/T4BRNmxc0zI/AAAAAAAAAOA/jtlgsRC-6_M/Picture6_thumb%25255B2%25255D.jpg?imgmax=800" width="559" height="390" /></a></p> <p>I’ve now completed my initial knowledge base, so I’m ready to finish the domain management activity. Clicking <strong>Finish</strong> produces a prompt to publish the knowledge base as shown below, but before I’m ready to use it I want to populate the known values for the <strong>City</strong> domain from my existing data by performing some knowledge discovery; so I’ll click <strong>No</strong>.</p> <p><a href="http://lh5.ggpht.com/-XO7CxR1oQyE/T4BROt02iLI/AAAAAAAAAOM/RNs0AAt8mA4/s1600-h/Picture7%25255B7%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture7" border="0" alt="Picture7" src="http://lh4.ggpht.com/-H594K_i_2zg/T4BRPp6NzVI/AAAAAAAAAOU/0eTCom9DbBU/Picture7_thumb%25255B5%25255D.jpg?imgmax=800" width="486" height="150" /></a></p> <p>Knowledge Discovery is an activity in which you connect to a data source and map fields in the source to domains in the knowledge base. DQS can then use the data source to discover new values for the domains defined in the knowledge base. The first step in this process is to open the knowledge base for the <strong>Knowledge Discovery</strong> activity as shown here. Note that the activity is performed using a wizard interface, with a sequence of steps.</p> <p><a href="http://lh5.ggpht.com/-1GKGw8c8sxw/T4BRRN1o-fI/AAAAAAAAAOc/vit7qE3LGNQ/s1600-h/Picture8%25255B4%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture8" border="0" alt="Picture8" src="http://lh6.ggpht.com/-eefvwyK0EVc/T4BRSI-JVdI/AAAAAAAAAOg/wqxMmzwDX_Q/Picture8_thumb%25255B2%25255D.jpg?imgmax=800" width="562" height="408" /></a></p> <p>After opening the knowledge base, I need to select a data source (I’m using the Excel workbook we saw earlier), and map the columns in the data source to the domains in the knowledge base as shown below. Note that the data source can include columns that are not mapped to domains, and does not need to include a column for every domain in the knowledge base. However, only the mapped domains will be included in the knowledge discovery process.</p> <p><a href="http://lh5.ggpht.com/-D47IztK3J5g/T4BRTIMYQtI/AAAAAAAAAOs/mWMIp2GA_fQ/s1600-h/Picture9%25255B4%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture9" border="0" alt="Picture9" src="http://lh6.ggpht.com/-2jNP6pBUWWM/T4BRUDUxr6I/AAAAAAAAAO0/JtBRCjOVQME/Picture9_thumb%25255B2%25255D.jpg?imgmax=800" width="559" height="407" /></a></p> <p>On the next page, I can start the data discovery analysis. DQS will read the source data and identify new values for the domains in the knowledge base, as shown here.</p> <p><a href="http://lh6.ggpht.com/--A1nuVaZRqM/T4BRVR3U39I/AAAAAAAAAO8/RwAkGLs3lOI/s1600-h/Picture10%25255B4%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture10" border="0" alt="Picture10" src="http://lh3.ggpht.com/-lyM3I_pa01s/T4BRWb79RwI/AAAAAAAAAPE/VF5vut4Mveg/Picture10_thumb%25255B2%25255D.jpg?imgmax=800" width="560" height="422" /></a></p> <p>On the final page of the wizard, you can view the values that have been discovered for each domain. In this example, the values discovered for the City domain include New York and NYC, as shown below. I can identify these as synonyms by selecting them both and clicking the Set selected domain values as synonyms button.</p> <p><a href="http://lh6.ggpht.com/-G3800uKQuzA/T4BRXXeoCMI/AAAAAAAAAPM/ybxfQdgYbfE/s1600-h/Picture11%25255B4%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture11" border="0" alt="Picture11" src="http://lh5.ggpht.com/-aCQ7n1ZCUTI/T4BRYtmCg-I/AAAAAAAAAPU/OfCFWFaMTqs/Picture11_thumb%25255B2%25255D.jpg?imgmax=800" width="561" height="402" /></a></p> <p>The value I selected first becomes the leading value, as shown here.</p> <p><a href="http://lh3.ggpht.com/-zTuyV3aR1CE/T4BRZzlLsQI/AAAAAAAAAPc/cZvNC9qyq7E/s1600-h/Picture12%25255B6%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture12" border="0" alt="Picture12" src="http://lh4.ggpht.com/-6Y3EZITwV2U/T4BRa9DcHBI/AAAAAAAAAPk/snc1gADKeos/Picture12_thumb%25255B4%25255D.jpg?imgmax=800" width="561" height="402" /></a></p> <p>For the <strong>Country/Region</strong> domain, DQS has discovered a new “Great Britain” value. I can mark this as invalid and specify an existing value to which it should be corrected (in this case, “United Kingdom”).</p> <p><a href="http://lh4.ggpht.com/-7Cdzfm4FiRw/T4BRbz9Qy7I/AAAAAAAAAPs/PPSA1OXLx8o/s1600-h/Picture13%25255B4%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture13" border="0" alt="Picture13" src="http://lh6.ggpht.com/-R_gfyfFJzhU/T4BRc9XaPiI/AAAAAAAAAP0/SIHuA_9sp8k/Picture13_thumb%25255B2%25255D.jpg?imgmax=800" width="561" height="416" /></a></p> <p>Clearing the <strong>Show Only New</strong> checkbox reveals the values that already existed before knowledge discovery, and I can see that “Great Britain” is now under the “United Kingdom” leading value. I can also see that there were 151 instances of the existing “United States” value found, along with a further 42 instances of “USA”, which was already specified as a synonym for “United States”.</p> <p><a href="http://lh3.ggpht.com/-HenLz84mkkk/T4BReCumXII/AAAAAAAAAP8/wuXMpD5VFlY/s1600-h/Picture14%25255B4%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture14" border="0" alt="Picture14" src="http://lh4.ggpht.com/-x3AVv8GVhCc/T4BRfFbfLHI/AAAAAAAAAQE/zcxGEiqcO2U/Picture14_thumb%25255B2%25255D.jpg?imgmax=800" width="552" height="408" /></a></p> <p>Now I’m ready to finish the knowledge discovery activity and publish the knowledge base.</p> <p>After you have published a knowledge base, you can use it to cleanse data from any data source containing columns that can be mapped to the domains defined in it. The simplest way to do this is to create a new data quality project based on the knowledge base and specify the <strong>Cleansing</strong> activity, as shown here.</p> <p><a href="http://lh5.ggpht.com/-qHJercrcmZI/T4BRgWXntVI/AAAAAAAAAQM/PMXSV6xc5q0/s1600-h/Picture15%25255B4%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture15" border="0" alt="Picture15" src="http://lh4.ggpht.com/-ATeWVjVgqRg/T4BRhl9hpPI/AAAAAAAAAQU/ippXXa0vlAg/Picture15_thumb%25255B2%25255D.jpg?imgmax=800" width="558" height="407" /></a></p> <p>Again, the activity takes the form of a wizard with sequential steps. The first step is to map the columns in the data source to the domains in the knowledge base, just as I did when performing the knowledge discovery activity previously; only this time I’m using the full <strong>Customers</strong> table in my <strong>CustomerDB</strong> SQL Server database instead of the sample data I had extracted to Excel.</p> <p><a href="http://lh5.ggpht.com/-xxh-2zquTIs/T4BRjMoQOYI/AAAAAAAAAQc/IEhQaZGlmjI/s1600-h/Picture16%25255B4%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture16" border="0" alt="Picture16" src="http://lh3.ggpht.com/-iHtlqnninHE/T4BRkXsxSNI/AAAAAAAAAQk/Y1wBPjzvNnM/Picture16_thumb%25255B2%25255D.jpg?imgmax=800" width="561" height="408" /></a></p> <p>Next, I run the cleansing process and DQS applies the knowledge base to the source data to identify corrected and suggested values. Corrected values are corrections DQS makes to the data based on known rules and synonyms. Suggested values are further possible corrections or new values that are generated based on a number of data quality heuristics that DQS uses when analyzing data.</p> <p><a href="http://lh6.ggpht.com/-sxovd3KuKvY/T4BRlY1AycI/AAAAAAAAAQs/tu55NQ2eIDk/s1600-h/Picture17%25255B4%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture17" border="0" alt="Picture17" src="http://lh3.ggpht.com/-Xncr1JYsq9Q/T4BRmupEjhI/AAAAAAAAAQ0/HLxHW2MxlhA/Picture17_thumb%25255B2%25255D.jpg?imgmax=800" width="561" height="414" /></a></p> <p>On the next page, on the <strong>Suggestions</strong> tab for each domain, I can view the suggestions identified by DQS. Here, DQS has identified a <strong>City</strong> domain value of "W. York”, which is sufficiently similar to the known value “York” for a correction to be suggested. Note that I can select the value and view the records that contain it to verify that “W. York” is commonly being used to denote “York” in England (as opposed for example, to “New York” in the United States). I can then choose to approve or reject individual instances of the correction, or accept/reject the suggestion that “W. York” should be considered a synonym of “York” (if I approve the suggestion) or added as a new known value in its own right (if I reject the suggestion).</p> <p><a href="http://lh4.ggpht.com/-eznXrWq0MRk/T4BRoaM-nuI/AAAAAAAAAQ8/Cyypkk_L28Y/s1600-h/Picture18%25255B4%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture18" border="0" alt="Picture18" src="http://lh3.ggpht.com/-UkR7l1fUvUM/T4BRpNPqg9I/AAAAAAAAARE/pPOYB-NvPIE/Picture18_thumb%25255B2%25255D.jpg?imgmax=800" width="563" height="411" /></a></p> <p>On the <strong>New</strong> tab, I can view the new values that were discovered for the domain. In this case, a number of new values were identified for the <strong>City</strong> domain, including <strong>Bracknell</strong> in England.</p> <p><a href="http://lh6.ggpht.com/-8db3sXYeR2g/T4BRqXP8JqI/AAAAAAAAARM/t7mNW2IsoFU/s1600-h/Picture19%25255B4%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture19" border="0" alt="Picture19" src="http://lh4.ggpht.com/-Obu8AA2ea8o/T4BRrBVvBvI/AAAAAAAAARU/OFdvoKikgE8/Picture19_thumb%25255B2%25255D.jpg?imgmax=800" width="561" height="399" /></a></p> <p>On the <strong>Corrected</strong> tab, I can view the values that were corrected based on pre-existing known synonyms or suggestions that I have approved.</p> <p><a href="http://lh3.ggpht.com/-6z5FzJC6PGo/T4BRsszEIXI/AAAAAAAAARc/NDWD7d9lmt4/s1600-h/Picture20%25255B4%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture20" border="0" alt="Picture20" src="http://lh3.ggpht.com/-Ig3-IVGxxWA/T4BRtbdJptI/AAAAAAAAARk/qoTIUFyA20I/Picture20_thumb%25255B2%25255D.jpg?imgmax=800" width="559" height="398" /></a></p> <p>After reviewing the results of the cleansing activity, I can export the cleansed data to a SQL Server database table, and .csv file, or an Excel workbook. Note that I can choose to export just the cleansed data values or I can include the cleansing information for further analysis.</p> <p><a href="http://lh3.ggpht.com/-EDQPco4UIss/T4BRuxl_ZjI/AAAAAAAAARs/gEYlWi2SZxg/s1600-h/Picture21%25255B4%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture21" border="0" alt="Picture21" src="http://lh5.ggpht.com/-koZFxeX99QA/T4BRv-Rx0ZI/AAAAAAAAAR0/jgvzAgE7pig/Picture21_thumb%25255B2%25255D.jpg?imgmax=800" width="559" height="391" /></a></p> <p>The exported results are shown in the following image. Note that the results include all of the source columns, and that for each of the columns that was mapped to a domain there are five columns in the results: The source value, the output value, the reason for any corrections, the level of confidence (between 0 and 1) for the correction, and the status of the column (correct or corrected).</p> <p><a href="http://lh6.ggpht.com/-VAi8XnNqP_I/T4BRxF5xv1I/AAAAAAAAAR8/lEtbjazaL7c/s1600-h/Picture22%25255B3%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture22" border="0" alt="Picture22" src="http://lh3.ggpht.com/-anq-5LeJqzg/T4BRyoX4_SI/AAAAAAAAASE/TzjCprzfekk/Picture22_thumb%25255B1%25255D.jpg?imgmax=800" width="552" height="484" /></a></p> <p>By creating a data cleansing project, a business user who understands the data domains can act as a “data steward” and enforce the quality of the data in application databases or analytical and reporting systems. Additionally, when you are confident in the ability of your knowledge base to cleanse data, you can incorporate DQS data cleansing into a SQL Server Integration Services (SSIS) data flow that extracts data from a source as part of an ETL process for data warehousing or EIM. The following image shows an SSIS data flow that includes the DQS Cleansing transformation.</p> <p><a href="http://lh6.ggpht.com/-4WeZhiQd_Po/T4BRzXV8E6I/AAAAAAAAASM/ojUD8i2nJ-E/s1600-h/Picture23%25255B4%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture23" border="0" alt="Picture23" src="http://lh4.ggpht.com/-I-mH5LfnERI/T4BR0wgcENI/AAAAAAAAASU/DdxByBgZGcM/Picture23_thumb%25255B2%25255D.jpg?imgmax=800" width="368" height="251" /></a></p> <p>In this example, the <strong>CustomerDB</strong> data source uses an OLE DB connection to extract data from the <strong>Customers</strong> table in SQL Server. The DQS Cleansing transformation is then configured to use the <strong>CustomerKB</strong> knowledge base and map the appropriate columns from the data source to domains for cleansing, as shown here.</p> <p align="center"><a href="http://lh6.ggpht.com/-hSLTobJjXeI/T4BR174irII/AAAAAAAAASc/GCylWOJZDPY/s1600-h/Picture24%25255B4%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="Picture24" border="0" alt="Picture24" src="http://lh6.ggpht.com/-F8X9jdWHAcg/T4BR2oYvx4I/AAAAAAAAASg/bVIRFAn54nA/Picture24_thumb%25255B2%25255D.jpg?imgmax=800" width="427" height="398" /></a>     <a href="http://lh4.ggpht.com/-GWVI9KXz9Xs/T4BR3a52P9I/AAAAAAAAASo/HEMy-5Mf5CA/s1600-h/Picture25%25255B4%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="Picture25" border="0" alt="Picture25" src="http://lh6.ggpht.com/-8VwR5yweGvA/T4BR4vOnK5I/AAAAAAAAAS0/vtEp8uQGQkY/Picture25_thumb%25255B2%25255D.jpg?imgmax=800" width="427" height="395" /></a></p> <p>The Staging DB destination uses an OLE DB connection to load data from the data flow into a staging table as part of an ETL process for a data warehousing solution. The output columns for the mapped domains are used to load the cleansed values into the staging table, as shown here.</p> <p><a href="http://lh6.ggpht.com/-XOEF1UAzuio/T4BR6VdQErI/AAAAAAAAAS8/ozvoxr6nTbw/s1600-h/Picture26%25255B4%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture26" border="0" alt="Picture26" src="http://lh4.ggpht.com/-hREcANgyUDQ/T4BR7OfHQMI/AAAAAAAAATE/yLsx-OJ7vHE/Picture26_thumb%25255B2%25255D.jpg?imgmax=800" width="444" height="437" /></a></p> <p>Running the SSIS package extracts the source data, applies the DQS knowledge base to cleanse the mapped columns, and loads the cleansed data into the staging database as shown here.</p> <p><a href="http://lh5.ggpht.com/-y0F75jFyh2I/T4BR8B0sdvI/AAAAAAAAATM/TpSFRLhnXvo/s1600-h/Picture27%25255B5%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="Picture27" border="0" alt="Picture27" src="http://lh5.ggpht.com/-0Q9pdICJhrs/T4BR9B48YkI/AAAAAAAAATU/rHvo23WYeyA/Picture27_thumb%25255B3%25255D.jpg?imgmax=800" width="349" height="281" /></a></p> <p>This walkthrough provides a simple example of how you can use DQS to cleanse data and improve data quality for reporting and analysis. There are a number of additional features of DQS that are not shown here, including the ability to define composite domains that consist of multiple columns and the ability to include external reference cleansing data from the Windows Azure Data Market in your knowledge base (for example to apply post code validation and correction rules based on standard data from a postal service authority). You can learn more about using DQS to cleanse data by attending course <a href="http://www.microsoft.com/learning/en/us/Course.aspx?ID=10777A&Locale=en-us" target="_blank">10777A: Implementing a Data Warehouse with SQL Server 2012</a>.</p> Graeme Malcolmhttp://www.blogger.com/profile/02246562883877200692noreply@blogger.com17tag:blogger.com,1999:blog-2066281766861287065.post-31730318978843466052011-02-22T18:19:00.001+00:002012-04-07T15:43:06.746+01:00SQL Server “Denali” Integration Services – Projects and Parameters<p>Some previous posts in this blog have discussed new features in the SQL Server “Denali” database engine. For this post however, I want to focus on some of the key enhancements in SQL Server “Denali” Integration Services (SSIS). SSIS first appeared in SQL Server 2005 as an evolution of the Data Transformation Services (DTS) component in previous releases, and has steadily become a core element of Extract, Transform, and Load (ETL) operations for many data warehousing implementations.</p> <p>The big news in the “Denali” release of SQL Server Integration Services, is a whole new deployment model for SSIS solutions. In previous releases, the only available unit of deployment is the package (a .dtsx file), and this could be deployed either to the file system or to the MSDB database in a SQL Server instance. This single-package deployment model is at-odds with the development model for SSIS solutions, in which a developer can create a single project that contains multiple packages. Prior to “Denali”, each package must be deployed and any variables that need to be set at runtime must be managed through a package configuration for each individual package. SSIS in “Denali” still supports this “legacy” deployment model, but now also supports project-level deployment to a the new Integration Services Catalog, and project-level parameters that can be used to set variables across multiple packages within a project.</p> <p>The first thing you need to do to take advantage of this new deployment model, is to create an Integration Services catalog on an instance of SQL Server. The Integration Services catalog is a central database in which SSIS projects can be stored and managed, and you can have one catalog per SQL Server instance. The Integration Services catalog uses the SQLCLR (the .NET common language runtime hosted within SQL Server), so you need to enable this first by using the following Transact-SQL:</p> <blockquote> <p><font face="Courier New">sp_configure 'show advanced options', 1; </font></p> <p><font face="Courier New">GO</font></p> <p><font face="Courier New">RECONFIGURE;</font></p> <p><font face="Courier New">GO</font></p> <p><font face="Courier New">sp_configure 'clr enabled', 1;</font></p> <p><font face="Courier New">GO</font></p> <p><font face="Courier New">RECONFIGURE;</font></p> <p><font face="Courier New">GO</font></p> </blockquote> <p>Now you’re ready to create an Integration Services catalog, which you can in SQL Server Management Studio as shown here.</p> <p><a href="http://lh3.ggpht.com/_WKO1IFE4fMA/TWP9aOaTshI/AAAAAAAAAJQ/f0L1zXiulrs/s1600-h/Picture1%5B6%5D.png" target="_blank"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Picture1" border="0" alt="Picture1" src="http://lh6.ggpht.com/_WKO1IFE4fMA/TWP9a9DTy6I/AAAAAAAAAJU/fZowtfnB2o0/Picture1_thumb%5B4%5D.png?imgmax=800" width="528" height="484" /></a> </p> <p>When you create the Integration Services catalog, you’re prompted for a password that can be used to protect the databases master key used to encrypt the data.</p> <p><a href="http://lh3.ggpht.com/_WKO1IFE4fMA/TWP9cgEFtaI/AAAAAAAAAJY/fXB-fVq2iS0/s1600-h/Picture2%5B6%5D.png" target="_blank"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Picture2" border="0" alt="Picture2" src="http://lh6.ggpht.com/_WKO1IFE4fMA/TWP9dTzJ4WI/AAAAAAAAAJc/71hclY5nEoE/Picture2_thumb%5B4%5D.png?imgmax=800" width="644" height="440" /></a></p> <p> <a href="http://lh6.ggpht.com/_WKO1IFE4fMA/TWP9eLhiVuI/AAAAAAAAAJg/wmVrqRNVeZc/s1600-h/Picture3%5B12%5D.png" target="_blank"><img style="border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; margin-left: 0px; border-left-width: 0px; margin-right: 0px" title="Picture3" border="0" alt="Picture3" align="left" src="http://lh6.ggpht.com/_WKO1IFE4fMA/TWP9ekuAzqI/AAAAAAAAAJk/QxhykTzPIGA/Picture3_thumb%5B10%5D.png?imgmax=800" width="246" height="327" /></a></p> <p>After clicking OK, refreshing the Object Explorer view reveals two new items as shown here. The first is a database named <strong>SSISDB</strong>, and the second is an <strong>SSISDB</strong> node beneath the <strong>Integration Services</strong> folder. The database is a regular SQL Server database that contains a number of tables, views, and stored procedures that you can use to manage and run SSIS projects and packages stored in the catalog. It is also where the projects and  packages in your catalog are physically stored. The <strong>SSISDB</strong> node under the <strong>Integration Services</strong> folder provides a management interface for the catalog and enables you to define a logical folder structure for your catalog.</p> <p><a href="http://lh6.ggpht.com/_WKO1IFE4fMA/TWP9fFqu6eI/AAAAAAAAAJo/LL1kKNleIdE/s1600-h/Picture4%5B7%5D.png" target="_blank"><img style="border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; margin-left: 0px; border-left-width: 0px; margin-right: 0px" title="Picture4" border="0" alt="Picture4" align="right" src="http://lh6.ggpht.com/_WKO1IFE4fMA/TWP9fZ05NlI/AAAAAAAAAJs/0YGpN7U_OwE/Picture4_thumb%5B5%5D.png?imgmax=800" width="195" height="90" /></a></p> <p>To create a folder in your catalog, simply right-click the <strong>SSISDB</strong> node under the <strong>Integration </strong><strong>Services</strong> folder, and click Create Folder.  Here I’ve created a folder with the imaginative name <strong>My Folder</strong>. Note that subfolders named <strong>Projects</strong> and <strong>Environments</strong> have automatically been created – we’ll return to these later.</p> <p>OK, so now we have an Integration Services catalog that contains a folder to which we can deploy an SSIS project; so I guess it’s time we went ahead and created a project to deploy. For our purposes, we’ll create a simple SSIS project that includes a data flow task that extracts the list of database names from the <strong>sysdatabases</strong> system view in the <strong>master</strong> database and copies it to a table in another database. I’m going to copy the database list to a table in a database called <strong>CmSampleDB</strong>, and to make matters a little more interesting, I’m going to create two tables that can act as the destination for the list – one to be used when testing the SSIS solution, and another to be used in production. We’ll design the SSIS project to support a project-level parameter so you can specify which table to use at runtime. Here’s my Transact-SQL code to create the destination tables:</p> <blockquote> <p><font face="Courier New">USE CmSampleDB</font></p> <p><font face="Courier New">GO</font></p> <p><font face="Courier New"></font></p> <p><font face="Courier New">CREATE TABLE TestDBList</font></p> <p><font face="Courier New">(name nvarchar(250))</font></p> <p><font face="Courier New">GO</font></p> <p><font face="Courier New"></font></p> <p><font face="Courier New">CREATE TABLE DBList</font></p> <p><font face="Courier New">(name nvarchar(250))</font></p> <p><font face="Courier New">GO</font></p> </blockquote> <p>Now we can go ahead and create the SSIS project using SQL Server Business Intelligence Development Studio (BIDS). Creating an SSIS project in “Denali” is exactly the same as in previous versions, just select the <strong>Integration Services Project</strong> template as shown here:</p> <p><a href="http://lh4.ggpht.com/_WKO1IFE4fMA/TWP9jaQTkvI/AAAAAAAAAJ4/B7UV0VD-UjA/s1600-h/Picture5%5B6%5D.png" target="_blank"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Picture5" border="0" alt="Picture5" src="http://lh5.ggpht.com/_WKO1IFE4fMA/TWP9kOZFp9I/AAAAAAAAAJ8/i1uKZ_Df-Ec/Picture5_thumb%5B4%5D.png?imgmax=800" width="644" height="466" /></a> </p> <p>When the new project is created, it will contains a single package named <strong>Package.dtsx</strong>, which you can rename to suit your own requirements – I’m going<a href="http://lh3.ggpht.com/_WKO1IFE4fMA/TWP9kpz993I/AAAAAAAAAKA/HX31E46HEcg/s1600-h/Picture6%5B5%5D.png" target="_blank"><img style="border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; margin-left: 0px; border-left-width: 0px; margin-right: 0px" title="Picture6" border="0" alt="Picture6" align="right" src="http://lh4.ggpht.com/_WKO1IFE4fMA/TWP9kyu5-xI/AAAAAAAAAKE/T_8WddMqh-s/Picture6_thumb%5B3%5D.png?imgmax=800" width="244" height="176" /></a> to name my <strong>My Package.dtsx</strong>. You can add more packages to the project as required, so for example, I’ll add a second package which I’ll name, um, <strong>My Other Package.dtsx</strong>. In Solution Explorer, my project now looks like this.</p> <p>So far, nothing is very different from how you would create an SSIS project in previous releases of SQL Server, but here’s where we’re going to use a new feature – P<strong>roject Parameters</strong>. Project parameters are, as the name suggests, parameters that can be used to pass variable values to the project at runtime. Because these parameters are scoped at the project level, they can be used by any package in the project. To add a project parameter, right-click the project in Solution Explorer and click <strong>Project Parameters</strong>, or click <strong>Project Parameters</strong> on the <strong>Project</strong> menu. Either of these actions displays the <strong>Parameters</strong> pane as shown here:</p> <p><a href="http://lh6.ggpht.com/_WKO1IFE4fMA/TWP9luAQ60I/AAAAAAAAAKI/JEZVEdUbPYk/s1600-h/Picture7%5B6%5D.png" target="_blank"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Picture7" border="0" alt="Picture7" src="http://lh3.ggpht.com/_WKO1IFE4fMA/TWP9mGi7BMI/AAAAAAAAAKM/POWpeS5QLQ0/Picture7_thumb%5B4%5D.png?imgmax=800" width="644" height="186" /></a> </p> <p>As you can see, I’ve used this pane to create a project-level parameter named <strong>TableName</strong> with a default value of <strong>TestDBList</strong>. This default value is more correctly known as the <em>Design</em> default value, since it’s used when I run the project within BIDS. When I deploy the project, I can set a <em>Server</em> default value that will override this one when packages in this project are run on the server.</p> <p>Now I need to create the data flow task that copies the database names from <strong>sysdatabases</strong> in the <strong>master</strong> database to the table indicated by the <strong>TableName</strong> parameter in the <strong>CmSampleDB</strong> database. To do this I just need to drag a <strong>Data Flow</strong> task to the design surface of <strong>My Package.dtsx</strong> as shown here:</p> <p><a href="http://lh5.ggpht.com/_WKO1IFE4fMA/TWP9n7yatQI/AAAAAAAAAKQ/OYl_eaJYOco/s1600-h/Picture8%5B7%5D.png" target="_blank"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Picture8" border="0" alt="Picture8" src="http://lh6.ggpht.com/_WKO1IFE4fMA/TWP9ovmIuHI/AAAAAAAAAKY/6h6rHxbfDGE/Picture8_thumb%5B5%5D.png?imgmax=800" width="608" height="497" /></a> </p> <p>Next, I’ll double-click the data flow task to view the data flow design surface, and use the <strong>Source Assistant</strong> item on the SSIS Toolbox to create a new connection to the master database on my SQL Server instance. Then I can configure the OLE DB source that gets created to extract the <strong>name</strong> column from the <strong>sysdatabases</strong> system view by using the following SQL command:</p> <blockquote> <p><font face="Courier New">SELECT name FROM sysdatabases</font></p> </blockquote> <p>The data flow surface now looks like this:</p> <p><a href="http://lh3.ggpht.com/_WKO1IFE4fMA/TWP9qUTSqiI/AAAAAAAAAKc/EDURiX0IYw0/s1600-h/Picture9%5B7%5D.png" target="_blank"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Picture9" border="0" alt="Picture9" src="http://lh5.ggpht.com/_WKO1IFE4fMA/TWP9rdk4T2I/AAAAAAAAAKg/DlT7HqSDdeo/Picture9_thumb%5B5%5D.png?imgmax=800" width="608" height="497" /></a> </p> <p>Next I’ll use the <strong>Destination Assistant</strong> to add a connection to the <strong>CmSampleDB</strong> database on my SQL Server instance, and connect the output from the source to the destination as shown here:</p> <p><a href="http://lh5.ggpht.com/_WKO1IFE4fMA/TWP9tsV3MxI/AAAAAAAAAKk/hji8tutY0nc/s1600-h/Picture10%5B6%5D.png" target="_blank"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Picture10" border="0" alt="Picture10" src="http://lh5.ggpht.com/_WKO1IFE4fMA/TWP9uSNQdfI/AAAAAAAAAKo/NdiKy_TwCEI/Picture10_thumb%5B4%5D.png?imgmax=800" width="583" height="484" /></a> </p> <p>To complete the data flow, I need to configure the destination to insert the output from the source into the table specified in the project-level <strong>TableName</strong> parameter, as shown here:</p> <p><a href="http://lh3.ggpht.com/_WKO1IFE4fMA/TWP9vFC3ZMI/AAAAAAAAAKs/CRCGBOLaKRo/s1600-h/Picture11%5B6%5D.png" target="_blank"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Picture11" border="0" alt="Picture11" src="http://lh6.ggpht.com/_WKO1IFE4fMA/TWP9vsP43JI/AAAAAAAAAK0/zyEiFRRIbJ8/Picture11_thumb%5B4%5D.png?imgmax=800" width="579" height="484" /></a></p> <p>Now I’m ready to build and deploy the project to the Integration Services catalog I created earlier. Building the project in BIDS creates a .ispac file, which you can then import into the catalog using SQL Server Management Studio, or deploy directly to the catalog from BIDS by clicking <strong>Deploy</strong> on the <strong>Project</strong> menu (or by right-clicking the project in Solution Explorer and clicking <strong>Deploy</strong>). Whichever approach you use, deployment to the catalog is accomplished via the Integration Services Deployment Wizard. After the Welcome screen, the wizard prompts you to select the project you want to deploy – in this case, the .ispac file I just built.</p> <p><a href="http://lh3.ggpht.com/_WKO1IFE4fMA/TWP9wqmpnRI/AAAAAAAAAK4/fmWUhEesPvI/s1600-h/Picture12%5B6%5D.png" target="_blank"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Picture12" border="0" alt="Picture12" src="http://lh6.ggpht.com/_WKO1IFE4fMA/TWP9xL5OsRI/AAAAAAAAAK8/wtP-h74QtGA/Picture12_thumb%5B4%5D.png?imgmax=800" width="554" height="484" /></a> </p> <p>Next, the wizard loads and validates the project before prompting you for the destination. This consists of the server where the Integration Services catalog is hosted, and the path to the folder where you want to deploy the project. Here, I’ve select the <strong>My folder</strong> folder i created earlier.</p> <p><a href="http://lh4.ggpht.com/_WKO1IFE4fMA/TWP9yBs8BdI/AAAAAAAAALA/YqgHO6RELWo/s1600-h/Picture13%5B6%5D.png" target="_blank"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Picture13" border="0" alt="Picture13" src="http://lh6.ggpht.com/_WKO1IFE4fMA/TWP9ysWAEoI/AAAAAAAAALE/tjKgEK0Ordk/Picture13_thumb%5B4%5D.png?imgmax=800" width="554" height="484" /></a> </p> <p>Finally, the wizard prompts you to set Server default values for any project parameters. You can use the Design default value, specify a new value, or use an environment variable. We’ll look at environment variables shortly, but for now I’ve set the Server default value for the <strong>TableName</strong> parameter to <strong>DBList</strong>.</p> <p><a href="http://lh4.ggpht.com/_WKO1IFE4fMA/TWP90I2Qj7I/AAAAAAAAALI/eDKyeT75aZw/s1600-h/Picture14%5B6%5D.png" target="_blank"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Picture14" border="0" alt="Picture14" src="http://lh6.ggpht.com/_WKO1IFE4fMA/TWP90hiocQI/AAAAAAAAALM/j-aDVr62lQ8/Picture14_thumb%5B4%5D.png?imgmax=800" width="549" height="484" /></a></p> <p> </p> <p>Completing the wizard deploys the project to the catalog, which you can verify in SQL Server Management Studio. note that the project is actually saved to the <strong>Projects</strong> sub-folder of the path specified in the wizard, and that all packages within the project are deployed as a single unit.</p> <p><a href="http://lh5.ggpht.com/_WKO1IFE4fMA/TWP903y2YNI/AAAAAAAAALQ/48mHq8B--_A/s1600-h/Picture15%5B5%5D.png"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Picture15" border="0" alt="Picture15" src="http://lh6.ggpht.com/_WKO1IFE4fMA/TWP91c39W0I/AAAAAAAAALU/ZrYY31oLES0/Picture15_thumb%5B3%5D.png?imgmax=800" width="244" height="138" /></a></p> <p>The final thing I want to do is to define a test environment and a production environment that can be used to control the execution context for the project. To do this, I’ll right-click the <strong>Environments</strong> folder and click <strong>Create Environment</strong>. Using this approach I’ve created two environments called <strong>Test</strong> and <strong>Production</strong>.</p> <p><a href="http://lh5.ggpht.com/_WKO1IFE4fMA/TWP91yOKV1I/AAAAAAAAALY/NuS8gcfHvjI/s1600-h/Picture16%5B5%5D.png" target="_blank"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Picture16" border="0" alt="Picture16" src="http://lh4.ggpht.com/_WKO1IFE4fMA/TWP92K_Y09I/AAAAAAAAALc/UZFRU1q2kUU/Picture16_thumb%5B3%5D.png?imgmax=800" width="244" height="168" /></a> </p> <p>You can edit the properties of each environment to create environment variables, which in turn can be used to set project parameters when project packages are run in the context of the environment. For example, here I’m creating an environment variable named <strong>tName</strong> in the <strong>Test</strong> environment with a value of <strong>TestDBList</strong>. I’ve also created an environment variable with the same name in the <strong>Production</strong> environment and assigned the value <strong>DBList</strong>.</p> <p><a href="http://lh6.ggpht.com/_WKO1IFE4fMA/TWP93cWpg7I/AAAAAAAAALg/4iYFtbP4E7g/s1600-h/Picture17%5B6%5D.png" target="_blank"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Picture17" border="0" alt="Picture17" src="http://lh5.ggpht.com/_WKO1IFE4fMA/TWP93-2KHPI/AAAAAAAAALk/hW3i360Qb6U/Picture17_thumb%5B4%5D.png?imgmax=800" width="644" height="441" /></a> </p> <p>Finally, I can hook the environments up to the project by editing the properties of the project in the Integration Services catalog and adding environment references, as shown here…</p> <p><a href="http://lh5.ggpht.com/_WKO1IFE4fMA/TWP95NBZFgI/AAAAAAAAALo/1Z6Y6j7AESg/s1600-h/Picture18%5B6%5D.png" target="_blank"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Picture18" border="0" alt="Picture18" src="http://lh5.ggpht.com/_WKO1IFE4fMA/TWP95yMOckI/AAAAAAAAALs/-sBIm9bPhZc/Picture18_thumb%5B4%5D.png?imgmax=800" width="644" height="440" /></a> </p> <p>… and setting parameters to get their values from environment variables as shown here (note that in the CTP release, you must click <strong>OK</strong> after adding the environment references on the <strong>References</strong> page before re-opening the Properties window and changing the parameter value on the <strong>Parameters</strong> page):</p> <p><a href="http://lh3.ggpht.com/_WKO1IFE4fMA/TWP97GUDOcI/AAAAAAAAALw/kTt3rrtZJ-I/s1600-h/Picture19%5B6%5D.png" target="_blank"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Picture19" border="0" alt="Picture19" src="http://lh5.ggpht.com/_WKO1IFE4fMA/TWP97w-mGiI/AAAAAAAAAL0/oQ7fKmjShY8/Picture19_thumb%5B4%5D.png?imgmax=800" width="644" height="440" /></a> </p> <p>So now we have a project deployed to our Integration Services catalog. The project contains two packages - one of which doesn’t actually do anything, and another that copies the list of database names from the <strong>sysdatabase</strong> system view in the <strong>master</strong> database to a table in the <strong>CmSampleDB</strong> database.  There is a project-level parameter that is used to indicate which table the database names should be copied to, and this is set to <strong>TestDBList</strong> or <strong>DBList</strong> depending on the environment that the package is executed in. To test this, I can right-click <strong>My Package.dtsx</strong> in Object Explorer and click <strong>Run</strong>, which produces the following dialog box:</p> <p><a href="http://lh3.ggpht.com/_WKO1IFE4fMA/TWP98_6EgDI/AAAAAAAAAL4/AL0Fuj2pJf8/s1600-h/Picture20%5B6%5D.png" target="_blank"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Picture20" border="0" alt="Picture20" src="http://lh3.ggpht.com/_WKO1IFE4fMA/TWP99YKCUSI/AAAAAAAAAL8/-GbvnB28Fwk/Picture20_thumb%5B4%5D.png?imgmax=800" width="644" height="440" /></a> </p> <p>Note that I can select the environment reference I want to use, which will determine the value of the <strong>tName</strong> environment variable, which will in turn set the value for the <strong>TableName</strong> project parameter and ultimately determine which table the data is copied to. For this example, I’ll select the Test environment and run the package, and the data is coped to the <strong>TestDBList</strong> table as shown below:</p> <p><a href="http://lh3.ggpht.com/_WKO1IFE4fMA/TWP9-9dEjxI/AAAAAAAAAMA/g7U6PLmFbm8/s1600-h/Picture21%5B6%5D.png" target="_blank"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Picture21" border="0" alt="Picture21" src="http://lh6.ggpht.com/_WKO1IFE4fMA/TWP9_0vLekI/AAAAAAAAAME/yhmtR5eb5bw/Picture21_thumb%5B4%5D.png?imgmax=800" width="644" height="463" /></a> </p> <p>To review past operations in the catalog, you can right-click SSISDB under the Integration Services folder in Object Explorer and click Operations. This shows a list of all operations that have been performed in the catalog in reverse order of occurrence, so in this example you can see that a project was deployed and then a package was executed.</p> <p><a href="http://lh4.ggpht.com/_WKO1IFE4fMA/TWP-Aq3q2uI/AAAAAAAAAMM/eWolcdEC_cM/s1600-h/Picture22%5B6%5D.png" target="_blank"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Picture22" border="0" alt="Picture22" src="http://lh5.ggpht.com/_WKO1IFE4fMA/TWP-BHgdtFI/AAAAAAAAAMQ/s7BuR4YJRDQ/Picture22_thumb%5B4%5D.png?imgmax=800" width="582" height="484" /></a></p> <p>Double-clicking an entry in the list reveals more information about the operation. For example, here’s the  details for the package execution:</p> <p><a href="http://lh6.ggpht.com/_WKO1IFE4fMA/TWP-CgukcbI/AAAAAAAAAMU/ipkHqbtuTBY/s1600-h/Picture23%5B6%5D.png" target="_blank"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Picture23" border="0" alt="Picture23" src="http://lh3.ggpht.com/_WKO1IFE4fMA/TWP-DQBeiKI/AAAAAAAAAMY/muyHDbdbcwc/Picture23_thumb%5B4%5D.png?imgmax=800" width="581" height="484" /></a> </p> <p>Note that clicking the Parameters tab shows the parameter values that were used for this particular execution:</p> <p><a href="http://lh6.ggpht.com/_WKO1IFE4fMA/TWP-EKLCi5I/AAAAAAAAAMc/gjLzyVc_wrQ/s1600-h/Picture24%5B5%5D.png" target="_blank"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Picture24" border="0" alt="Picture24" src="http://lh4.ggpht.com/_WKO1IFE4fMA/TWP-EuSm2II/AAAAAAAAAMg/DEvvLLotKF4/Picture24_thumb%5B3%5D.png?imgmax=800" width="609" height="484" /></a> </p> <p>I’ve used this article to give you a quick tour of the new deployment model for SSIS and how you can use project parameters and environment variables to create a more flexible but manageable ETL solution with SSIS in SQL Server “Denali”. For more information about what’s new in SSIS in “Denali”, see <a href="http://msdn.microsoft.com/en-us/library/bb522534(v=SQL.110).aspx" target="_blank">SQL Server Books Online</a>.</p> Graeme Malcolmhttp://www.blogger.com/profile/02246562883877200692noreply@blogger.com0tag:blogger.com,1999:blog-2066281766861287065.post-48986014307130255742011-01-27T13:43:00.001+00:002012-04-07T15:43:06.746+01:00Paging With SQL Server “Denali”<p>One of the more common tasks in a data-bound application is to display data in a “page-able” user interface – often some sort of data grid. So for example, you might want to create a Web application that shows a list of available products, but which limits the list to ten products at a time. The user then “pages” through the data by clicking “Next Page” or something similar. There are loads of user interface design and implementation patterns that enable this kind of functionality, but they usually either involve fetching all of the data from the database and caching it locally in the application, or implementing some sort of “current page” tracking functionality to adjust the query used to retrieve the data on each page.</p> <p>SQL Server “Denali” introduces a new way to manage paging within the SELECT statement itself. More specifically, you can use the new OFFSET and FETCH keywords in the ORDER BY clause to limit the query results to a specific page of data. The OFFSET keyword is used to indicate the “starting row” of the results (i.e. the number of rows to skip before this page), and the FETCH keyword is used to indicate the number of pages to be returned (i.e. the page size). For example, the following query skips 20 rows in the underlying dataset and then returns the next 10 rows:</p> <blockquote> <p><font face="Courier New">SELECT so.SalesOrderID, so.OrderDate, c.CustomerName <br />FROM SalesOrder so <br />JOIN Customer c ON so.Customer = c.CustomerID <br />ORDER BY SalesOrderID ASC <br />OFFSET 20 ROWS <br />FETCH NEXT 10 ROWS ONLY</font></p> </blockquote> <p>For this to work predictably, the ORDER BY clause must include a unique column (or combination of columns) and the underlying dataset must not change between queries.</p> <p>With this functionality, you can implement an effective paging solution that tracks the position of the first row in the current page, the first row in the next page, and the first row in the previous page. For example, the following stored procedure retrieves the requested page of data based on page size and offset parameter values, and then returns the first row positions for the next and previous pages:</p> <blockquote> <p><font face="Courier New">CREATE PROCEDURE GetSalesOrders(@PageSize int, @Offset int, <br />                                @NextPage int OUTPUT, @PrevPage int OUTPUT) <br />AS <br />-- Retrieve the requested page of data <br />SELECT so.SalesOrderID, so.OrderDate, c.CustomerName <br />FROM SalesOrder so <br />JOIN Customer c ON so.Customer = c.CustomerID <br />ORDER BY SalesOrderID ASC <br />OFFSET @Offset ROWS <br />FETCH NEXT @PageSize ROWS ONLY </font></p> <p><font face="Courier New">-- Set the row position markers <br />SET @NextPage = @@ROWCOUNT + @Offset <br />SET @PrevPage = @Offset - @PageSize <br />GO</font> </p> <p><font face="Courier New"></font></p> </blockquote> <p>You can then call this stored procedure to navigate forward and backward through the data like this:</p> <blockquote> <p><font face="Courier New">DECLARE @StartRow int = 0, @Next int, @Prev int <br />EXECUTE GetSalesOrders 10, @StartRow, @Next OUTPUT, @Prev OUTPUT <br />SET @StartRow = @Next <br />EXECUTE GetSalesOrders 10, @StartRow, @Next OUTPUT, @Prev OUTPUT <br />SET @StartRow = @Next <br />EXECUTE GetSalesOrders 10, @StartRow, @Next OUTPUT, @Prev OUTPUT <br />SET @StartRow = @Prev <br />EXECUTE GetSalesOrders 10, @StartRow, @Next OUTPUT, @Prev OUTPUT</font></p> </blockquote> <p>This code calls the stored procedure 4 times, retrieving the initial page (with an offset of zero), the next two pages, and then the second page again, as shown in the results here:</p> <blockquote> <p><font face="Courier New">SalesOrderID OrderDate  CustomerName <br />------------ ---------- ------------------------------ <br />1            2010-01-20 Kasumi Fernandez <br />2            2010-01-21 Rod Dechamps <br />3            2010-01-22 Jane Dechamps <br />4            2010-01-23 Freddy Dechamps <br />5            2010-01-24 Pierre Dechamps <br />6            2010-01-25 Kasumi Dechamps <br />7            2010-02-01 Rod Smith <br />8            2010-02-01 Jane Smith <br />9            2010-02-01 Freddy Smith <br />10           2010-02-01 Pierre Smith </font></p> <p><font face="Courier New">(10 row(s) affected) </font></p> <p><font face="Courier New">SalesOrderID OrderDate  CustomerName <br />------------ ---------- ------------------------------ <br />11           2010-02-01 Kasumi Smith <br />12           2010-02-01 Rod Jones <br />13           2010-02-01 Jane Jones <br />14           2010-02-01 Freddy Jones <br />15           2010-02-01 Pierre Jones <br />16           2010-02-01 Kasumi Jones <br />17           2010-02-01 Rod Yamamoto <br />18           2010-02-01 Jane Yamamoto <br />19           2010-02-01 Freddy Yamamoto <br />20           2010-02-01 Pierre Yamamoto </font></p> <p><font face="Courier New">(10 row(s) affected) </font></p> <p><font face="Courier New">SalesOrderID OrderDate  CustomerName <br />------------ ---------- ------------------------------ <br />21           2010-02-01 Kasumi Yamamoto <br />22           2010-02-01 Rod Fernandez <br />23           2010-02-01 Jane Fernandez <br />24           2010-02-01 Freddy Fernandez <br />25           2010-02-01 Pierre Fernandez <br />26           2010-02-01 Kasumi Fernandez <br />27           2010-02-01 Rod Dechamps <br />28           2010-02-01 Jane Dechamps <br />29           2010-02-01 Freddy Dechamps <br />30           2010-02-01 Pierre Dechamps </font></p> <p><font face="Courier New">(10 row(s) affected) </font></p> <p><font face="Courier New">SalesOrderID OrderDate  CustomerName <br />------------ ---------- ------------------------------ <br />11           2010-02-01 Kasumi Smith <br />12           2010-02-01 Rod Jones <br />13           2010-02-01 Jane Jones <br />14           2010-02-01 Freddy Jones <br />15           2010-02-01 Pierre Jones <br />16           2010-02-01 Kasumi Jones <br />17           2010-02-01 Rod Yamamoto <br />18           2010-02-01 Jane Yamamoto <br />19           2010-02-01 Freddy Yamamoto <br />20           2010-02-01 Pierre Yamamoto </font></p> <p><font face="Courier New">(10 row(s) affected)</font></p> </blockquote> <p>Note that this simple example doesn’t handle the issue of “falling off the end” of the underlying dataset, so an attempt to move forward beyond the last page will return an empty result set, and an attempt to move backward to a position before the first row in the dataset will result in an error (since the OFFSET value cannot be less than zero). You could easily add some basic validation checks in the stored procedure to account for this (for example, resetting @PrevPage to 0 if it becomes negative or setting @NextPage to COUNT(*) - @PageSize if it gets larger than the underlying dataset).</p> <p>You can download SQL Server “Denali” CTP1 from <a href="http://www.microsoft.com/sqlserver/en/us/product-info/future-editions.aspx" target="_blank">here</a>, a script to create the sample database I used for the above example from <a href="http://public.blu.livefilestore.com/y1pYMcclO4pIH2Y4AzVIdphzaKapkazjc9UErttcrfhK45NWmCw1Pyj3k1gfK7evzqrrBbKzIoAyus1QmPl5xYOlg/CreateSampleDB.sql?download&psid=1" target="_blank">here</a>, and the paging example shown in this article from <a href="http://public.blu.livefilestore.com/y1pYMcclO4pIH2aDVjZDR8aoiYrWImeOy5LPGMjpNaPO0_6MLpwPzx3Xjz49JldB-5qApnjDpgSpUniRlMvz1DSiw/Paging.sql?download&psid=1" target="_blank">here</a>.</p> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:ce7a2fad-a596-4b2a-9cda-2b8310f485bd" class="wlWriterEditableSmartContent">del.icio.us Tags: <a href="http://del.icio.us/popular/SQL+Server+Denali" rel="tag">SQL Server Denali</a></div> Graeme Malcolmhttp://www.blogger.com/profile/02246562883877200692noreply@blogger.com0tag:blogger.com,1999:blog-2066281766861287065.post-16765357101632634602011-01-20T17:50:00.001+00:002012-04-07T15:43:06.747+01:00SQL Server “Denali” – Promising the Earth!<p>I’ve previously posted several <a href="http://graemesplaceblog.blogspot.com/search/label/Spatial%20Data" target="_blank">articles about spatial data support in SQL Server</a>, and as I continue my exploration of SQL Server “Denali” CTP1, I’ve encountered a few interesting new enhancements to the <strong>geometry</strong> and <strong>geography</strong> data types. I won’t go into full details here, because Ed Katibah and Milan Stojic have already saved me the trouble by writing a <a href="http://sqlcat.com/whitepapers/archive/2010/11/09/new-spatial-features-in-sql-server-code-named-denali-community-technology-preview-1.aspx" target="_blank">whitepaper</a> that provides comprehensive round-up of the changes in this release; however, I do want to make a few observations relating to the new enhanced support for curved lines and shapes.</p> <p>SQL Server “Denali” introduces a few new spatial shapes, including CIRCULARSTRING, COMPOUNDCURVE, and CURVEPOLYGON. A CIRCULARSTRING line is a sequence of an odd number of at least three points, which are connected to form a curved arc. For example, consider the following Transact-SQL:</p> <blockquote> <p><font face="Courier New">DECLARE @g geography = 'CIRCULARSTRING(-4.115 55.778, -3.399 56.990, -2.237 54.009)'</font></p> </blockquote> <p>This creates a <strong>geography</strong> instance that represents a curved line like this:</p> <p><a href="http://lh6.ggpht.com/_WKO1IFE4fMA/TTh13SyIKkI/AAAAAAAAAIc/s2bvXizMzis/s1600-h/CIRCULARSTRING%5B7%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px" title="CIRCULARSTRING" border="0" alt="CIRCULARSTRING" src="http://lh5.ggpht.com/_WKO1IFE4fMA/TTh13tI2RuI/AAAAAAAAAIg/I5L7yjLhx-U/CIRCULARSTRING_thumb%5B3%5D.png?imgmax=800" width="240" height="119" /></a> </p> <p>Compare this to the line produced by using the LINESTRING shape, as shown here:</p> <blockquote> <p><font face="Courier New">DECLARE @g geography = 'LINESTRING(-4.115 55.778, -3.399 56.990, -2.237 54.009)'</font></p> </blockquote> <p><a href="http://lh5.ggpht.com/_WKO1IFE4fMA/TTh14Ve3JLI/AAAAAAAAAIk/xjmYRTWTxmc/s1600-h/LINESTRING%5B4%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px" title="LINESTRING" border="0" alt="LINESTRING" src="http://lh6.ggpht.com/_WKO1IFE4fMA/TTh14gM3QjI/AAAAAAAAAIo/7PckFNpFovA/LINESTRING_thumb%5B2%5D.png?imgmax=800" width="240" height="122" /></a> </p> <p>Of course, both of these lines are “open”. You can create a closed CIRCULARSTRING line by defining at least five points and making the final point in the line the same as the first, as shown here:</p> <blockquote> <p><font face="Courier New">DECLARE @g geography = 'LINESTRING(-4.115 55.778, -3.399 56.990, -2.237 54.009, -3.168 53.863, -4.115 55.778)'</font></p> </blockquote> <p><a href="http://lh6.ggpht.com/_WKO1IFE4fMA/TTh15MSB2zI/AAAAAAAAAIs/nvG7UFFRT0I/s1600-h/ClosedCircleString%5B4%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px" title="ClosedCircleString" border="0" alt="ClosedCircleString" src="http://lh5.ggpht.com/_WKO1IFE4fMA/TTh15ioGs2I/AAAAAAAAAIw/0pw1HmCVwno/ClosedCircleString_thumb%5B2%5D.png?imgmax=800" width="240" height="161" /></a> </p> <p></p> <p></p> <p></p> <p>A COMPOUNDCURVE is a curved shape that is composed of one or more CIRCULARSTRING arcs and linear sections that are contiguously joined by having the final point in each segment the same as the first point in the next segment. For example, the following Transact-SQL creates a COMPOUNDCURVE shape from two CIRCULARSTRING arcs and a linear section. Note that you do not specify a keyword for the linear sections.</p> <blockquote> <p><font face="Courier New">DECLARE @g geography = 'COMPOUNDCURVE( <br />                          CIRCULARSTRING(-4.000 55.000, -4.500 54.500, -4.000 54.000), <br />                          (-4.000 54.000, 1.000 54.000), <br />                          CIRCULARSTRING(1.000 54.000, 1.500 54.500, 1.000 55.000))'</font></p> </blockquote> <p><a href="http://lh5.ggpht.com/_WKO1IFE4fMA/TTh16HmIWFI/AAAAAAAAAI0/nBFlTI5ZVj0/s1600-h/CompoundCurve%5B4%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px" title="CompoundCurve" border="0" alt="CompoundCurve" src="http://lh3.ggpht.com/_WKO1IFE4fMA/TTh16m2ktRI/AAAAAAAAAI4/ajdKNAhPq8s/CompoundCurve_thumb%5B2%5D.png?imgmax=800" width="240" height="121" /></a> </p> <p>A CURVEPOLYGON is a surface area that is formed by a closed curved line, which can be defined by a CIRCULARSTRING or a COMPOUNDCURVE. When working with the <strong>geometry</strong> data type, the points in the curved area can be defined in any order, but when using he <strong>geography</strong> type, you must observe the “left foot rule”, which dictates that you must describe the shape as if you were pacing it out on the ground and the “inside” of the shape is always on your left. For example, here’s a Transact-SQL statement that defines a CURVEPOLYGON based on a CIRCULARSTRING:</p> <blockquote> <p><font face="Courier New">SELECT geography::Parse('CURVEPOLYGON(</font></p> <p><font face="Courier New">                           CIRCULARSTRING(-4.889 55.844,</font></p> <p><font face="Courier New">                                          -3.924 55.738,</font></p> <p><font face="Courier New">                                          -2.731 56.058,</font></p> <p><font face="Courier New">                                          -4.201 56.134,</font></p> <p><font face="Courier New">                                          -4.889 55.844)</font></p> <p><font face="Courier New">                                       )')</font></p> </blockquote> <p>This defines an area in the UK within the so-called “central belt” of Scotland, as shown here. Note that the points describe the shaded area, which is what would be on your left if you paced out the area from point to point in the sequence in which they are specified.</p> <p><a href="http://lh6.ggpht.com/_WKO1IFE4fMA/TTh17_CeM5I/AAAAAAAAAI8/8AW_tvQkRyQ/s1600-h/CentralBelt%5B5%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px" title="CentralBelt" border="0" alt="CentralBelt" src="http://lh3.ggpht.com/_WKO1IFE4fMA/TTh19amlhZI/AAAAAAAAAJA/GdxHK3e-cV4/CentralBelt_thumb%5B3%5D.png?imgmax=800" width="465" height="480" /></a> </p> <p>Now, what happens if I reverse the order of the points as shown here?</p> <blockquote> <p><font face="Courier New">SELECT geography::Parse('CURVEPOLYGON(</font></p> <p><font face="Courier New">                           CIRCULARSTRING(-4.889 55.844,</font></p> <p><font face="Courier New">                                          -4.201 56.134,</font></p> <p><font face="Courier New">                                          -2.731 56.058,</font></p> <p><font face="Courier New">                                          -3.924 55.738,</font></p> <p><font face="Courier New">                                          -4.889 55.844)</font></p> <p><font face="Courier New">                                       )')</font></p> </blockquote> <p>The “left foot rule” clearly tells SQL Server to include everything on my left side as I pace out the shape, and since the Earth is a sphere, this shape actually describes the entire surface of the planet except for the “hole” defined by the points in the CIRCULARSTRING.</p> <p><a href="http://lh4.ggpht.com/_WKO1IFE4fMA/TTh1-xhEtgI/AAAAAAAAAJE/RPHCGbubnfg/s1600-h/EverythingElse%5B5%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px" title="EverythingElse" border="0" alt="EverythingElse" src="http://lh3.ggpht.com/_WKO1IFE4fMA/TTh2AXZZ0FI/AAAAAAAAAJI/mi1XGyCGuTc/EverythingElse_thumb%5B3%5D.png?imgmax=800" width="507" height="480" /></a> </p> <p>In previous releases of SQL Server, this would have caused an error because shapes larger than a hemisphere were not supported. However, in SQL Server “Denali”, you can create a shape that covers as much of the surface of the Earth as you like, so this code is perfectly valid. Clearly, the message here is that you need to be very careful when defining surface areas (be they CURVEPOLYGON or regular POLYGON shapes) to apply the “left foot rule” to include the surface area you actually intend to, and not the rest of the world!</p> <p>Speaking of which, one other new feature worth mentioning is the inclusion of a FULLGLOBE shape, which returns a surface area that covers, you guessed it, the full globe. For example, the following code returns the area of the planet’s surface in square kilometres:</p> <blockquote> <p><font face="Courier New">DECLARE @theEarth geography = geography::STGeomFromText('FULLGLOBE', 4326) <br />SELECT @theEarth.STArea()/1000000</font></p> </blockquote> <p>The Spatial Reference Identifier (SRID) 4326 specifies that the WGS84 standard model of the earth’s shape should be used, which means that the result of the call to the  STArea method is returned in square metres, which we then divide by 1000000 to get the answer in square kilometres.</p> <p>And just in case you’re interested, the Earth’s surface is just a little over  510,065,621 km2!</p> <p></p> <p></p> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:9bd614e8-5b9e-4804-82da-6b301de3b6ab" class="wlWriterEditableSmartContent">del.icio.us Tags: <a href="http://del.icio.us/popular/SQL+Server+Denali" rel="tag">SQL Server Denali</a></div> Graeme Malcolmhttp://www.blogger.com/profile/02246562883877200692noreply@blogger.com0tag:blogger.com,1999:blog-2066281766861287065.post-38207604027270210992011-01-11T16:54:00.001+00:002012-04-07T15:43:06.747+01:00Contained Databases in SQL Server “Denali”<p>OK, so here’s a common scenario. You’ve developed an application that uses a SQL Server database on a development machine, and now you need to deploy the application (and its database) to a staging or production environment. Of course, you can generate scripts to recreate the database on another server, or you could simply back it up and restore it to the other server. However, while that would successfully move the database, it would not take across any server-level objects that the application depends on, such as logins or Agent jobs. SQL Server 2008 R2, introduced Data-Tier applications as a way to package up databases with their server-level dependencies and deploy them to another server (which I covered in <a href="http://graemesplaceblog.blogspot.com/2010/01/data-tier-applications-in-sql-server.html" target="_blank">a previous blog post</a>), and this is a great step forward, but to be honest, wouldn’t it be nice if the application database was simply self-contained with no need to rely on server-level objects at all?</p> <p>Well, in SQL Server “Denali”, you get your wish in the form of <em>Contained Databases</em>. When you create a database, you now have the option of setting its <strong>containment type</strong> property to <em>partial</em>, which enables you to create “contained” objects that would normally be defined at the server-level within the database itself – most notably logins. The “partial” containment type value specifies that the database can use a mixture of contained and non-contained objects (so for example, traditional server-level logins can be used to access the database as well as contained logins) – a further option of “full” containment (which disallows the use of non-contained objects) is promised but not supported in the current CTP 1 release.<a href="http://lh3.ggpht.com/_WKO1IFE4fMA/TSyLKwyXITI/AAAAAAAAAH4/DNbeicTbOE0/s1600-h/Picture1%5B6%5D.png"><img style="border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; margin-left: 0px; border-left-width: 0px; margin-right: 0px" title="Picture1" border="0" alt="Picture1" align="right" src="http://lh4.ggpht.com/_WKO1IFE4fMA/TSyLLuQpO9I/AAAAAAAAAIA/fOwHmR6QYOA/Picture1_thumb%5B4%5D.png?imgmax=800" width="447" height="397" /></a></p> <p>So let’s see an example of how to create and use a contained database. First, you need to enable contained databases in the server. You can do this in the Server Properties dialog box as shown here, or you can use the following Transact-SQL code: </p> <blockquote> <p><font face="Courier New">sp_configure 'show advanced options', 1 ; </font></p> <p><font face="Courier New">GO</font></p> <p><font face="Courier New">RECONFIGURE ; </font></p> <p><font face="Courier New">GO </font></p> <p><font face="Courier New">sp_configure 'contained database authentication', 1; </font></p> <p><font face="Courier New">GO</font></p> <p><font face="Courier New">RECONFIGURE ; </font></p> <p><font face="Courier New">GO </font></p> <p><font face="Courier New">sp_configure 'show advanced options', 0 ; </font></p> <p><font face="Courier New">GO</font></p> <p><font face="Courier New">RECONFIGURE ; </font></p> <p><font face="Courier New">GO</font></p> </blockquote> <p><a href="http://lh5.ggpht.com/_WKO1IFE4fMA/TSyLNBBo7hI/AAAAAAAAAIE/jlyG-f2gthU/s1600-h/Picture2%5B6%5D.png"><img style="border-right-width: 0px; margin: 0px 15px 0px 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="Picture2" border="0" alt="Picture2" align="left" src="http://lh6.ggpht.com/_WKO1IFE4fMA/TSyLNtRn5RI/AAAAAAAAAII/XsTOhrqXrb0/Picture2_thumb%5B4%5D.png?imgmax=800" width="446" height="394" /></a>After you’ve enabled contained databases, you can create one like this:</p> <blockquote> <p><font face="Courier New">CREATE DATABASE [MyContainedDB]</font></p> <p><font face="Courier New">CONTAINMENT = PARTIAL</font></p> <p><font face="Courier New">GO</font></p> </blockquote> <p>Or alternatively, you can use the New Database dialog box in SQL Server Management Studio and set the <strong>Containment type</strong> property as shown here.</p> <p> </p> <p></p> <p> </p> <p> </p> <p> </p> <p> </p> <p>Now that you have a contained database, you can create users within it. The important point here is that you can create users that do not map to server-level logins, so the traditional dependency between a user object in a database and a login object in the server instance where that database is hosted is broken. To create a contained SQL Server user that has a password, you can use a Transact-SQL CREATE USER statement in the contained database like this one:</p> <blockquote> <p><font face="Courier New">USE [MyContainedDB]</font></p> <p><font face="Courier New">GO</font></p> <p><font face="Courier New">CREATE USER [MyContainedUser] WITH PASSWORD = ‘5up3r53cret’</font></p> <p><font face="Courier New">GO</font></p> </blockquote> <p>Or, you can create a contained user for a Windows account like this:</p> <blockquote> <p><font face="Courier New">CREATE USER [DEVBOX\MyAppAccount]</font></p> <p><font face="Courier New">GO</font></p> </blockquote> <p>Of course, you can also use the graphical tools in SQL Server Management Studio to create a contained user, as shown here:</p> <p><a href="http://lh4.ggpht.com/_WKO1IFE4fMA/TSyLOHirR_I/AAAAAAAAAIM/Et4I_vVXQbA/s1600-h/Picture3%5B5%5D.png"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Picture3" border="0" alt="Picture3" src="http://lh5.ggpht.com/_WKO1IFE4fMA/TSyLO9KF2CI/AAAAAAAAAIQ/5U9FA7XL6J8/Picture3_thumb%5B3%5D.png?imgmax=800" width="548" height="484" /></a> </p> <p>Note that if you want to use a SQL Server user, you need to enable so-called “mixed mode” authentication at the server-level – even though there’s no server-level SQL Server logins.</p> <p>As stated before, contained users do not rely on server-level logins, they exist only within the contained database. This means that when you want to connect to the contained database from a client application, you need to specify the database name in the connection string or the connection will fail because SQL Server will attempt to default to the master database and try to authenticate your credentials as a server-level login. In a typical client application, you can specify the database in a connection string like this:</p> <blockquote> <p><font face="Courier New">“SERVER=mysqlserver; <strong>DATABASE=MyContainedDB</strong>; USER ID=MyContainedUser; PASSWORD=5up3r53cret”</font></p> </blockquote> <p>When connecting from tools like SQL Server Management Studio, you can specify the database name in the connection dialog box like this:</p> <p><a href="http://lh3.ggpht.com/_WKO1IFE4fMA/TSyLQeEVm7I/AAAAAAAAAIU/Btid_m7Qego/s1600-h/Picture4%5B5%5D.png"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Picture4" border="0" alt="Picture4" src="http://lh6.ggpht.com/_WKO1IFE4fMA/TSyLQ38qGOI/AAAAAAAAAIY/g17CAX_ofAY/Picture4_thumb%5B3%5D.png?imgmax=800" width="411" height="484" /></a> </p> <p>The ability to create contained databases that include users with no dependency on server-level logins can be extremely useful when you need to move the database from one server to another. However, you should be aware of the security implications of using database-specific authentication that effectively bypasses the usual security management architecture. You can find out more about these security implications <a href="http://msdn.microsoft.com/en-us/library/ff929055(v=SQL.110).aspx" target="_blank">here</a>.</p> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:2f21f020-7fcc-4e14-8769-0ae80eb4b26e" class="wlWriterEditableSmartContent">del.icio.us Tags: <a href="http://del.icio.us/popular/SQL+Server+Denali" rel="tag">SQL Server Denali</a></div> Graeme Malcolmhttp://www.blogger.com/profile/02246562883877200692noreply@blogger.com0tag:blogger.com,1999:blog-2066281766861287065.post-64605565400035884762011-01-10T18:28:00.001+00:002011-01-10T18:28:42.170+00:00Getting Started with SQL Azure<p>Towards the end of last year, I was the lead author on Microsoft Learning course <a href="http://www.microsoft.com/learning/en/us/Course.aspx?ID=10337A&Locale=en-us" target="_blank">10337A: Updating Your Microsoft SQL Server 2008 BI Skills to SQL Server 2008 R2</a>. While this is a course primarily for BI developers, we included a module on SQL Azure; and to make the course work in a classroom, we created a Silverlight-based simulation that students can use to walk through the steps required to set up and use a SQL Azure database. Additionally, <a href="http://msdn.microsoft.com/en-gb/ff728568.aspx" target="_blank">Hilton Giesnow presents a useful video introduction to SQL Azure on the MSDN site</a>.</p> <p>However, the release cycle for cloud-based technology moves even faster than that of traditional software products, and predictably enough, the Azure team at Microsoft has created a new version of the Web portal used to manage Azure platform subscriptions – including SQL Azure. So, I thought it might be useful to provide a short walkthrough based on the latest portal (before they go and change it again!)</p> <p>To start with, you need to sign-up for an Azure subscription at <a title="http://www.microsoft.com/windowsazure/offers/" href="http://www.microsoft.com/windowsazure/offers/">http://www.microsoft.com/windowsazure/offers/</a>. It may take as long as a couple of days to provision your account, but mine took less than half an hour. After you’ve signed up, you’ll be able to access the Azure portal using your Windows Live ID:</p> <p align="center"><a href="http://lh4.ggpht.com/_WKO1IFE4fMA/TStN-cKn0QI/AAAAAAAAAGI/lTr1KCp98MA/s1600-h/Picture1%5B5%5D.png"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="Picture1" border="0" alt="Picture1" src="http://lh5.ggpht.com/_WKO1IFE4fMA/TStOB8CCbQI/AAAAAAAAAGM/eJxi6DeuIeI/Picture1_thumb%5B3%5D.png?imgmax=800" width="609" height="480" /></a></p> <p>To create a SQL Azure server (which you can think of as a cloud-based computer running an instance of SQL Server – it’s not, but you can think of it like that!), click the New Database Server button in the toolbar (which I guess like everything else these days is probably more properly called the “ribbon”), which will display all of the Azure subscriptions your Windows Live ID is associated with that include SQL Azure services. In this case, I have a single subscription named CM Azure Subscription, which currently has no SQL Azure database servers defined in it.</p> <p align="center"><a href="http://lh5.ggpht.com/_WKO1IFE4fMA/TStOHIr5zFI/AAAAAAAAAGQ/BmS5vH4P4Os/s1600-h/Picture2%5B4%5D.png"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="Picture2" border="0" alt="Picture2" src="http://lh6.ggpht.com/_WKO1IFE4fMA/TStOKtfsL3I/AAAAAAAAAGU/EgljtAB2LGc/Picture2_thumb%5B2%5D.png?imgmax=800" width="609" height="480" /></a></p> <p>No you can select the subscription in which you want to create the SQL Azure server, and click the <strong>Create</strong> button in the toolbar/ribbon, which will start a wizard, the first step in which is to select the geographical location where you want the server to be hosted:</p> <p align="center"><a href="http://lh3.ggpht.com/_WKO1IFE4fMA/TStOQSoutzI/AAAAAAAAAGY/EDUYwhL3odM/s1600-h/Picture3%5B4%5D.png"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="Picture3" border="0" alt="Picture3" src="http://lh5.ggpht.com/_WKO1IFE4fMA/TStOT2keiuI/AAAAAAAAAGc/lxxSCEM2mxg/Picture3_thumb%5B2%5D.png?imgmax=800" width="609" height="480" /></a></p> <p>After selecting a location, you need to specify the Administrator credentials for the server. There are some restrictions on login name and password complexity (for example, you can’t create a login named <strong><em>Administrator</em></strong> with the password <strong><em>password</em></strong>).</p> <p align="center"><a href="http://lh3.ggpht.com/_WKO1IFE4fMA/TStOY4-MhoI/AAAAAAAAAGg/e6jgg8HhrDo/s1600-h/Picture4%5B4%5D.png"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="Picture4" border="0" alt="Picture4" src="http://lh5.ggpht.com/_WKO1IFE4fMA/TStOcSrUhaI/AAAAAAAAAGk/h5vWEVLzIbM/Picture4_thumb%5B2%5D.png?imgmax=800" width="609" height="480" /></a></p> <p>Next, the wizard prompts you to specify the firewall rules that control connectivity to your server. By default, nothing (including any other Azure services you may have) can access your server, so you’ll typically want to enable access for Windows Azure services as shown here:</p> <p align="center"><a href="http://lh6.ggpht.com/_WKO1IFE4fMA/TStOg-ij4fI/AAAAAAAAAGs/nOXSOvpVi70/s1600-h/Picture5%5B4%5D.png"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="Picture5" border="0" alt="Picture5" src="http://lh6.ggpht.com/_WKO1IFE4fMA/TStOk00PnEI/AAAAAAAAAGw/zyyVTsWW2bU/Picture5_thumb%5B2%5D.png?imgmax=800" width="609" height="480" /></a></p> <p>Additionally, you’ll probably want to allow at least some computers to connect to the server across the Internet – even if initially this is limited to your own development workstation. To do this, you’ll need to add a firewall rule that specifies a range of IP addresses from which you want to enable connectivity. In this example, I’ve created a rule that allows connections from any computer on the Internet.</p> <p align="center"><a href="http://lh3.ggpht.com/_WKO1IFE4fMA/TStOqW72_9I/AAAAAAAAAG0/j-x0CH-T6FA/s1600-h/Picture6%5B4%5D.png"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="Picture6" border="0" alt="Picture6" src="http://lh4.ggpht.com/_WKO1IFE4fMA/TStOuaMrq5I/AAAAAAAAAG4/atl8NxNChAM/Picture6_thumb%5B2%5D.png?imgmax=800" width="609" height="480" /></a></p> <p>After completing the wizard, your SQL Azure server is provisioned with a name that looks like a random stream of characters and shows up in the <strong>Database</strong> section of the Azure portal as shown here:</p> <p align="center"><a href="http://lh4.ggpht.com/_WKO1IFE4fMA/TStO0ITEWlI/AAAAAAAAAG8/qex-7Kg0Vvk/s1600-h/Picture7%5B4%5D.png"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="Picture7" border="0" alt="Picture7" src="http://lh6.ggpht.com/_WKO1IFE4fMA/TStO4HDy3CI/AAAAAAAAAHA/l0qbpC5N7Fs/Picture7_thumb%5B2%5D.png?imgmax=800" width="609" height="480" /></a></p> <p>Now that you have a SQL Azure server, you can select it in the Azure portal and create databases in it by clicking the <strong>Create</strong> button in the ribbon. Doing this results in a prompt for a name, edition, and size for your database. There are two editions available (Web and Business), each with their own range of possible sizes (and corresponding prices), so when you select an edition, the available sizes will reflect the sizes supported by that edition. In this example, I’ve created a 1GB Web edition database.</p> <p align="center"><a href="http://lh5.ggpht.com/_WKO1IFE4fMA/TStO-qbPKgI/AAAAAAAAAHI/T3bYhJed5Rk/s1600-h/Picture8%5B4%5D.png"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="Picture8" border="0" alt="Picture8" src="http://lh4.ggpht.com/_WKO1IFE4fMA/TStPBqki1GI/AAAAAAAAAHM/YFgIOYSWM1g/Picture8_thumb%5B2%5D.png?imgmax=800" width="609" height="480" /></a></p> <p>After creating your database, you can view its properties in the portal as shown here:</p> <p align="center"><a href="http://lh4.ggpht.com/_WKO1IFE4fMA/TStPIGvcvHI/AAAAAAAAAHQ/uBxi9umLaSI/s1600-h/Picture9%5B4%5D.png"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="Picture9" border="0" alt="Picture9" src="http://lh4.ggpht.com/_WKO1IFE4fMA/TStPL-WVJ0I/AAAAAAAAAHU/le26153PmnM/Picture9_thumb%5B2%5D.png?imgmax=800" width="609" height="480" /></a></p> <p>The latest version of the Azure portal includes a management tool that you can use to manage your SQL Azure database. To launch this, simply select the database you want to manage in the Azure portal and click <strong>Manage</strong> in the ribbon. The first time you do this you’ll be prompted to accept the terms and conditions for the management tool, and then the tool itself will open in a new browser window and prompt you to log in as shown here:</p> <p align="center"><a href="http://lh6.ggpht.com/_WKO1IFE4fMA/TStPRxv7MdI/AAAAAAAAAHY/WHa0MwMxkp4/s1600-h/Picture11%5B4%5D.png"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="Picture11" border="0" alt="Picture11" src="http://lh3.ggpht.com/_WKO1IFE4fMA/TStPVBXzjAI/AAAAAAAAAHc/Rf_KEzxMxoQ/Picture11_thumb%5B2%5D.png?imgmax=800" width="609" height="480" /></a></p> <p>You can log in using the administrator credentials you specified when you provisioned the SQL Azure server, and start the manage the database by using the management user interface as shown here:</p> <p align="center"><a href="http://lh5.ggpht.com/_WKO1IFE4fMA/TStPbKtdpCI/AAAAAAAAAHg/nJV0dtvliA8/s1600-h/Picture12%5B4%5D.png"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="Picture12" border="0" alt="Picture12" src="http://lh6.ggpht.com/_WKO1IFE4fMA/TStPfGkyyTI/AAAAAAAAAHk/WoZsHRB7Ywc/Picture12_thumb%5B2%5D.png?imgmax=800" width="609" height="480" /></a></p> <p>Additionally, if you have installed SQL Server Management Studio for any edition of SQL Server 2008 R2 (including the free Express edition), you can connect to your SQL Azure server and manage it by specifying the fully-qualified server name and SQL Server login credentials as shown here:</p> <p align="center"><a href="http://lh6.ggpht.com/_WKO1IFE4fMA/TStPjblRvcI/AAAAAAAAAHo/cq93z13Awfw/s1600-h/Picture13%5B4%5D.png"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="Picture13" border="0" alt="Picture13" src="http://lh3.ggpht.com/_WKO1IFE4fMA/TStPoeKimtI/AAAAAAAAAHs/3xDfsmVUSh0/Picture13_thumb%5B2%5D.png?imgmax=800" width="615" height="480" /></a></p> <p>Note that the server name takes the form <em>YourSQLAzureServerName</em><strong>.database.windows.net</strong>, and the login name takes the form <em>YourAdminLoginName</em><strong>@</strong><em>YourSQLAzureServerName</em>.</p> <p>SQL Azure servers are displayed in SQL Server Management Studio like this:</p> <p align="center"><a href="http://lh4.ggpht.com/_WKO1IFE4fMA/TStPsEOgOXI/AAAAAAAAAHw/X6E_vF0VRVA/s1600-h/Picture14%5B4%5D.png"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="Picture14" border="0" alt="Picture14" src="http://lh4.ggpht.com/_WKO1IFE4fMA/TStPuXSfPeI/AAAAAAAAAH0/JXPIrbW-t5I/Picture14_thumb%5B2%5D.png?imgmax=800" width="615" height="480" /></a></p> <p>From here, you can manage your SQL Azure database in a similar way to how you manage on-premise SQL Server instances, though you’ll find that there are some SQL Server features that are not supported by SQL Azure. You can also connect to your SQL Azure database from client applications in a similar fashion to on-premise SQL Server databases by specifying the fully-qualified SQL Azure server name in the connection string.</p> <p>For more information about SQL Azure, see <a title="http://msdn.microsoft.com/en-us/library/ff937661.aspx" href="http://msdn.microsoft.com/en-us/library/ff937661.aspx">http://msdn.microsoft.com/en-us/library/ff937661.aspx</a>.</p> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:70df84d3-688a-4158-b717-217de5aaf82f" class="wlWriterEditableSmartContent">del.icio.us Tags: <a href="http://del.icio.us/popular/SQL+Azure" rel="tag">SQL Azure</a></div> Graeme Malcolmhttp://www.blogger.com/profile/02246562883877200692noreply@blogger.com0tag:blogger.com,1999:blog-2066281766861287065.post-62837139760258491142010-12-28T15:56:00.001+00:002012-04-07T15:43:06.747+01:00Creating a User-Defined Server Role in SQL Server “Denali”<p>“Denali” is the code-name for the next release of Microsoft SQL Server, and a community technology preview (CTP) is available for download from <a href="http://www.microsoft.com/sqlserver/en/us/product-info/future-editions.aspx" target="_blank">here</a>. My colleague Geoff Allix has already posted a couple of articles about the enhancements Denali includes for debugging Transact-SQL scripts <a href="http://cm-bloggers.blogspot.com/2010/11/first-look-at-sql-server-denali.html" target="_blank">here</a> and <a href="http://cm-bloggers.blogspot.com/2010/11/first-look-at-sql-server-denali-part-2.html" target="_blank">here</a>, and as the Content Master data platform team continues to investigate the CTP, I’m sure more posts will appear. In this post, I want to discuss a new feature that makes it easier to delegate server-level administrative tasks – user-defined server roles.</p> <p>If you’re familiar with previous releases of SQL Server, you’ll know that there are essentially two levels of security principal within SQL Server (well alright, 3 if you include the operating system) – <em>server-level</em> principals, such as <em>logins</em>, and <em>database-level</em> principals, such as <em>users</em>. Permissions can be granted to these principals in order to allow them to use or manage resources (generally known as <em>securables</em>) at the relevant level. For example, you can grant permissions on server-level securables (such as endpoints and certificates) to server-level principals, and you can grant permissions on database-level securables such as (tables and views) to database-level principals. Obviously, managing permissions for individual principals can become complex (and error-prone) as the number of principals increases, so in common with most software systems, SQL Server supports the idea of grouping principals into roles, enabling you to grant the required permissions to the role, and simply add or remove principals from the role in order to allow or disallow them access to the securables.</p> <p>So far, so ordinary.</p> <p>Previous releases of SQL Server included a pre-defined set of server-level roles and database-levels roles that are already granted commonly required permissions, and to which you can simply add your principals (for example, logins at the server level or users at the database-level) in order to quickly enable people to access the resources they need while maintaining the principle of “least privilege” (i.e. not granting any permissions to anyone who doesn’t require them). Additionally, you can create your own user-defined database-level roles but crucially, until SQL Server “Denali” you could not create your own user-defined server-level roles.</p> <p>To understand how the ability to create and manage your own server-level roles is useful, let’s consider a scenario where a corporation uses a SQL Server instance to host multiple application databases. Many of these databases are used by internal “home grown” ASP.NET Web applications or client/server applications that use Windows integrated authentication, and to control access to these databases, the DBA has simply created logins in SQL Server for the appropriate Windows Active Directory groups. However, the environment also includes a couple of off-the-shelf applications that do not support Windows-integrated authentication, and therefore require their own SQL Server logins. Let’s also suppose that these applications are supported by team of dedicated application administrators who need to be able to manage the SQL Server logins for the applications, for example to periodically change the password.</p> <p>To accomplish this, I can create a user-defined server role by right-clicking the <strong>Server Roles</strong> folder in SQL Server Management Studio and clicking <strong>New Server Role</strong>, as shown below. Alternatively, I can use the new CREATE SERVER ROLE Transact-SQL statement.</p> <p><a href="http://lh6.ggpht.com/_WKO1IFE4fMA/TRoIuKDDPVI/AAAAAAAAAFo/XlTAQk49ziE/s1600-h/Picture1%5B4%5D.png"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Picture1" border="0" alt="Picture1" src="http://lh5.ggpht.com/_WKO1IFE4fMA/TRoIuo6QIHI/AAAAAAAAAFs/CiluYApPvKA/Picture1_thumb%5B2%5D.png?imgmax=800" width="644" height="454" /></a> </p> <p>Using the SQL Server Management Studio UI reveals the <strong>New Server Role</strong> dialog box, enabling me to define the server role. In this case, I want to create a role named <strong>SQLAccountsAdmin</strong>, which will be owned by the built-in <strong>sa</strong> login. I can also specify the server-level securables I want to assign permissions for, and I can select each securable and set the required permissions. In this case, I’ve selected the <strong>AcctsPackage</strong> and <strong>AppSvcAccount</strong> logins (yes, principals can also be securables!) and granted the full set of available permissions on these logins to the <strong>SQLAccountsAdmin</strong> role.</p> <p><a href="http://lh6.ggpht.com/_WKO1IFE4fMA/TRoIvbc-lXI/AAAAAAAAAFw/RV3D2gpgjvQ/s1600-h/Picture2%5B14%5D.png"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Picture2" border="0" alt="Picture2" src="http://lh3.ggpht.com/_WKO1IFE4fMA/TRoIwJtca9I/AAAAAAAAAF0/TaTXIMEeSyo/Picture2_thumb%5B10%5D.png?imgmax=800" width="495" height="484" /></a> </p> <p>To grant permissions to a user-defined server role by using Transact-SQL, you can use the GRANT, DENY, and REVOKE Transact-SQL commands just like you would for any other server-level principal.</p> <p></p> <p></p> <p>Now I need to add some server-level principals to the role, so that they can use their role membership to gain the permissions required to manage the two SQL Server logins. You can do this on the Members tab of the dialog box or by using the ALTER SERVER ROLE Transact-SQL statement.</p> <p><a href="http://lh5.ggpht.com/_WKO1IFE4fMA/TRoIwgmYjdI/AAAAAAAAAF4/75zzUQA8L3c/s1600-h/Picture3%5B5%5D.png"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Picture3" border="0" alt="Picture3" src="http://lh3.ggpht.com/_WKO1IFE4fMA/TRoIxDrIq7I/AAAAAAAAAF8/pyehia5hJOc/Picture3_thumb%5B3%5D.png?imgmax=800" width="495" height="484" /></a> </p> <p>Finally, it’s worth noting that you can nest user-defined server roles within other server-level principals, including the fixed server roles provided out-of-the-box by SQL Server. In general, I’d advise against this as you can often find yourself granting unnecessary and unintended permissions, but it’s shown here for completeness.</p> <p><a href="http://lh4.ggpht.com/_WKO1IFE4fMA/TRoIx4H6dcI/AAAAAAAAAGA/iRlXDnxtnJ0/s1600-h/Picture4%5B5%5D.png"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Picture4" border="0" alt="Picture4" src="http://lh5.ggpht.com/_WKO1IFE4fMA/TRoIyVelHCI/AAAAAAAAAGE/PutT14Phw3Y/Picture4_thumb%5B3%5D.png?imgmax=800" width="495" height="484" /></a> </p> <p>So, there you have it – user-defined server roles in SQL Server “Denali” provide a flexible way to delegate administrative tasks at the server-level.</p> Graeme Malcolmhttp://www.blogger.com/profile/02246562883877200692noreply@blogger.com0tag:blogger.com,1999:blog-2066281766861287065.post-78557877519768505842010-12-24T18:22:00.001+00:002010-12-24T18:24:31.960+00:00Installing SharePoint 2010 on Windows 7<p>I generally do most of my development and “technology exploration” in an environment that reflects the actual production environment as closely as possible – for example, by developing against multiple virtual servers running Windows Server 2008 in a domain configuration. This approach has the advantage of reducing the opportunity for “well, it works on my laptop” style configuration issues when trying to deploy the application into production, but, let’s be honest, it makes life difficult – especially when the “real world” configuration requirements are as onerous as those of SharePoint-based solutions.</p> <p>Microsoft has documented a way to deploy SharePoint 2010 on a single Windows 7 (or Vista if you prefer) development box, so when I recently needed to do some basic SharePoint development, I decided to ignore my existing virtualized, multi-server SharePoint development and testing environment, and try out <a href="http://msdn.microsoft.com/en-us/library/ee554869(office.14).aspx" target="_blank">Microsoft’s instructions for creating a single-box development environment</a><a title="http://msdn.microsoft.com/en-us/library/ee554869(office.14).aspx" href="http://msdn.microsoft.com/en-us/library/ee554869(office.14).aspx">.</a> For the most part, this went OK, but I did hit a few issues along the way, so I thought it might be useful to document my experience.</p> <p>First, I installed Windows 7 (64-bit, since SharePoint is 64-bit only!) and then downloaded <a href="http://www.microsoft.com/downloads/en/details.aspx?FamilyID=49c79a8a-4612-4e7d-a0b4-3bb429b46595&displaylang=en" target="_blank">Microsoft SharePoint Foundation 2010</a>. The download is an executable named <em>SharePointFoundation.exe</em>, which you can simply run if you intend to install on the supported Windows Server platform, but which you need to extract to the file system in order to install on Windows 7 (or Vista). For example, to extract the installation files to a folder named C:\SharePointFiles, I used the following command:</p> <blockquote> <p><font face="Courier New">SharePointFoundation /extract:c:\SharePointFiles</font></p> </blockquote> <p>Next, I needed to edit the config.xml file provided with the SharePoint files, and add a <Setting> entry to enable installation on a client OS, as shown below:</p> <p><a href="http://lh6.ggpht.com/_WKO1IFE4fMA/TRTkm5veiOI/AAAAAAAAAE0/katQELuRbKg/s1600-h/Picture1%5B6%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px" title="Picture1" border="0" alt="Picture1" src="http://lh6.ggpht.com/_WKO1IFE4fMA/TRTkoQiAUFI/AAAAAAAAAE4/kIGaR_GBEbQ/Picture1_thumb%5B4%5D.png?imgmax=800" width="915" height="772" /></a> </p> <p>The SharePoint installation files include a tool to automatically install and configure SharePoint prerequisites, but this only works on the supported Windows Server OS – you can’t use it on Windows 7, so you need to install and configure the prerequisites manually. The first of these is the Microsoft Filter Pack, and it’s included in the extracted files, as shown here:</p> <p><a href="http://lh3.ggpht.com/_WKO1IFE4fMA/TRTkp8LXwTI/AAAAAAAAAE8/tx4o11evGm4/s1600-h/Picture2%5B4%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px" title="Picture2" border="0" alt="Picture2" src="http://lh5.ggpht.com/_WKO1IFE4fMA/TRTksxWJLNI/AAAAAAAAAFA/Vr3zJ2bKeJM/Picture2_thumb%5B2%5D.png?imgmax=800" width="915" height="772" /></a> </p> <p>Links to the remaining prerequisites are in the <a href="http://msdn.microsoft.com/en-us/library/ee554869(office.14).aspx" target="_blank">Microsoft documentation</a>, and I simply downloaded and installed the ones I required for SharePoint Foundation on a Windows 7 machine (which included the <a href="http://go.microsoft.com/fwlink/?LinkID=141237" target="_blank">Sync Framework</a>, the <a href="http://go.microsoft.com/fwlink/?LinkId=123718" target="_blank">SQL Server 2008 Native Client</a>, and the <a href="http://support.microsoft.com/kb/974405" target="_blank">Windows Identity Foundation</a>).</p> <p>Next I needed to enable all of the IIS features that SharePoint requires. Microsoft provide the following command, which you can copy to a command prompt window (on a single line) and execute.</p> <blockquote> <p><font face="Courier New">start /w pkgmgr /iu:IIS-WebServerRole;IIS-WebServer;IIS-CommonHttpFeatures; <br />IIS-StaticContent;IIS-DefaultDocument;IIS-DirectoryBrowsing;IIS-HttpErrors; <br />IIS-ApplicationDevelopment;IIS-ASPNET;IIS-NetFxExtensibility; <br />IIS-ISAPIExtensions;IIS-ISAPIFilter;IIS-HealthAndDiagnostics; <br />IIS-HttpLogging;IIS-LoggingLibraries;IIS-RequestMonitor;IIS-HttpTracing;IIS-CustomLogging;IIS-ManagementScriptingTools; <br />IIS-Security;IIS-BasicAuthentication;IIS-WindowsAuthentication;IIS-DigestAuthentication; <br />IIS-RequestFiltering;IIS-Performance;IIS-HttpCompressionStatic;IIS-HttpCompressionDynamic; <br />IIS-WebServerManagementTools;IIS-ManagementConsole;IIS-IIS6ManagementCompatibility; <br />IIS-Metabase;IIS-WMICompatibility;WAS-WindowsActivationService;WAS-ProcessModel; <br />WAS-NetFxEnvironment;WAS-ConfigurationAPI;WCF-HTTP-Activation; <br />WCF-NonHTTP-Activation</font></p> </blockquote> <p>This enables the required features, which you can verify in the Windows Features Control Panel applet as shown below:</p> <p><a href="http://lh3.ggpht.com/_WKO1IFE4fMA/TRTkujKsH-I/AAAAAAAAAFE/CCqqKkPEbac/s1600-h/Picture3%5B4%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px" title="Picture3" border="0" alt="Picture3" src="http://lh5.ggpht.com/_WKO1IFE4fMA/TRTkxSaDgpI/AAAAAAAAAFI/ghDvHKgTRc8/Picture3_thumb%5B2%5D.png?imgmax=800" width="915" height="772" /></a> </p> <p>Now I was ready to install SharePoint Foundation. I ran <em>Setup.exe</em> and chose the Standalone installation option:</p> <p><a href="http://lh4.ggpht.com/_WKO1IFE4fMA/TRTky8-wbyI/AAAAAAAAAFQ/cDU1ddJE3PI/s1600-h/Picture4%5B4%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px" title="Picture4" border="0" alt="Picture4" src="http://lh4.ggpht.com/_WKO1IFE4fMA/TRTk1dCKxLI/AAAAAAAAAFU/K4_7XK5Ly_c/Picture4_thumb%5B2%5D.png?imgmax=800" width="915" height="772" /></a> </p> <p>After installation is complete, I was prompted to run the SharePoint Product Configuration wizard, and this is where the wheels fell off! The Standalone installation of SharePoint includes the installation of a SQL Server 2008 Express database server instance (named SHAREPOINT) to host the configuration database, but somewhat annoyingly, you need to apply the <a href="http://support.microsoft.com/kb/970315" target="_blank">Microsoft SQL Server 2008 KB 970315 x64 hotfix</a> before you can run the configuration wizard. However, even after doing this, I still found that the SharePoint Products Configuration wizard failed to connect to the database server in order to create the configuration database. In desperation, I upgraded the SQL Server 2008 Express instance that had been installed to SQL Server 2008 R2 Express – still no luck.</p> <p>My investigations resulted in finding a number of useful blog articles, which are listed below – none of these actually solved my specific problem, but they contain some really useful tips!</p> <ul> <li><a title="http://blah.winsmarts.com/2009-11-SharePoint_2010_Development_Environment_-and-ndash;_Practical_Tips.aspx" href="http://blah.winsmarts.com/2009-11-SharePoint_2010_Development_Environment_-and-ndash;_Practical_Tips.aspx">http://blah.winsmarts.com/2009-11-SharePoint_2010_Development_Environment_-and-ndash;_Practical_Tips.aspx</a></li> <li><a title="http://blogs.msdn.com/b/vesku/archive/2010/02/01/sharepoint-2010-team-development-environment.aspx" href="http://blogs.msdn.com/b/vesku/archive/2010/02/01/sharepoint-2010-team-development-environment.aspx">http://blogs.msdn.com/b/vesku/archive/2010/02/01/sharepoint-2010-team-development-environment.aspx</a></li> <li><a title="http://blogs.msdn.com/b/opal/archive/2009/11/16/installation-notice-for-sharepoint-2010-public-beta.aspx" href="http://blogs.msdn.com/b/opal/archive/2009/11/16/installation-notice-for-sharepoint-2010-public-beta.aspx">http://blogs.msdn.com/b/opal/archive/2009/11/16/installation-notice-for-sharepoint-2010-public-beta.aspx</a></li> <li><a title="http://www.codersbarn.com/post/SharePoint-Development-Environment.aspx" href="http://www.codersbarn.com/post/SharePoint-Development-Environment.aspx">http://www.codersbarn.com/post/SharePoint-Development-Environment.aspx</a></li> <li><a title="http://www.dev4side.com/community/blog/2010/3/1/principal-errors-during-sharepoint-2010-beta-2-installation.aspx" href="http://www.dev4side.com/community/blog/2010/3/1/principal-errors-during-sharepoint-2010-beta-2-installation.aspx">http://www.dev4side.com/community/blog/2010/3/1/principal-errors-during-sharepoint-2010-beta-2-installation.aspx</a></li> <li><a title="http://www.eggheadcafe.com/software/aspnet/28909552/failed-to-connect-to-database-configuration-wizard.aspx" href="http://www.eggheadcafe.com/software/aspnet/28909552/failed-to-connect-to-database-configuration-wizard.aspx">http://www.eggheadcafe.com/software/aspnet/28909552/failed-to-connect-to-database-configuration-wizard.aspx</a></li> <li><a title="http://www.myfriedmind.com/techBlog/2010/03/12/Sharepoint2010ConfigurationWizardFailedToRegisterSharepointServicesWithSystemSecurityCryptographyCryptographicExceptionObjectAlreadyExists.aspx" href="http://www.myfriedmind.com/techBlog/2010/03/12/Sharepoint2010ConfigurationWizardFailedToRegisterSharepointServicesWithSystemSecurityCryptographyCryptographicExceptionObjectAlreadyExists.aspx">http://www.myfriedmind.com/techBlog/2010/03/12/Sharepoint2010ConfigurationWizardFailedToRegisterSharepointServicesWithSystemSecurityCryptographyCryptographicExceptionObjectAlreadyExists.aspx</a></li> </ul> <p>After some poking around, I discovered a command-line version of the configuration wizard in the <em>C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN folder</em> named <em>psconfig.exe</em>, and by examining its parameter info I discovered a <strong>standaloneconfig</strong> value for the <strong>cmd</strong> parameter, as shown below:</p> <p><a href="http://lh3.ggpht.com/_WKO1IFE4fMA/TRTk2_5L_sI/AAAAAAAAAFY/_LHdUWBzmRw/s1600-h/Picture5%5B4%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px" title="Picture5" border="0" alt="Picture5" src="http://lh5.ggpht.com/_WKO1IFE4fMA/TRTk5nFutwI/AAAAAAAAAFc/3LR11QYDfZs/Picture5_thumb%5B2%5D.png?imgmax=800" width="915" height="772" /></a> </p> <p>This seemed to solve my problem, and I now have a fully configured SharePoint Foundation 2010 environment on a Windows 7 virtual machine, as shown below.</p> <p><a href="http://lh4.ggpht.com/_WKO1IFE4fMA/TRTk6ux1u3I/AAAAAAAAAFg/q08ldsOhwk0/s1600-h/Picture6%5B4%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px" title="Picture6" border="0" alt="Picture6" src="http://lh4.ggpht.com/_WKO1IFE4fMA/TRTk9n9JNlI/AAAAAAAAAFk/O8npebQPH3A/Picture6_thumb%5B2%5D.png?imgmax=800" width="915" height="772" /></a> </p> <p>All-told, it took me the best part of an afternoon to create my “simple” SharePoint development environment – but to be fair, a large percentage of that was spent scrabbling around to try to figure out how to get the configuration wizard to work. Hopefully, your installation will go a little more smoothly!</p> <p>Happy Holidays!</p> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:aaad46c3-4d77-4271-8aa1-2def1f9416c7" class="wlWriterEditableSmartContent">del.icio.us Tags: <a href="http://del.icio.us/popular/SharePoint" rel="tag">SharePoint</a></div>Graeme Malcolmhttp://www.blogger.com/profile/02246562883877200692noreply@blogger.com0tag:blogger.com,1999:blog-2066281766861287065.post-2302430015676917842010-11-23T14:41:00.001+00:002010-11-23T14:41:00.140+00:00Drupal and SQL Server<p>One of the more “out of left field” projects I’ve worked on recently involved creating a sample solution that demonstrates how you can integrate Drupal 7 with SQL Server. For those who don’t know, Drupal is an open source content management system that’s widely used on Linux/Apache sites. In recent times, Microsoft has sought to widen the appeal of its Web development platform by adding support for applications and technologies normally used by LAMP (Linux, Apache, MySQL, and PHP) developers, and a version of Drupal that runs in IIS on Windows and which uses SQL Server as its underlying content database is one outcome of that initiative.</p> <p>Anyway, Content Master was asked to create two sample Drupal solutions to showcase the advantages of using SQL Server with Drupal – one that integrates Drupal with SQL Server Reporting Services, and another that incorporates location-based content with SQL Server’s spatial data support. I ended up working on this project together with a couple of colleagues named David Miles and James Millar – I designed and implemented the SQL Server reports as well as the spatial data and Bing Maps functionality, and David and James handled the PHP programming and Drupal-specific scripting elements of the solutions.</p> <p>You can view more details of the <a href="http://cm-bloggers.blogspot.com/2010/11/drupal-7-and-sql-server-reporting.html" target="_blank">Reporting Services solution</a> and the <a href="http://cm-bloggers.blogspot.com/2010/11/drupal-7-and-sql-server-spatial-data.html" target="_blank">Spatial Data solution</a> on the <a href="http://cm-bloggers.blogspot.com/" target="_blank">Content Master blog</a>.</p> <p>Enjoy!</p> Graeme Malcolmhttp://www.blogger.com/profile/02246562883877200692noreply@blogger.com2tag:blogger.com,1999:blog-2066281766861287065.post-68497840537003776762010-10-13T11:35:00.001+01:002010-10-13T11:37:18.625+01:00Using a Transparent Background in Reporting Services<p>While watching the Japanese Formula 1 Grand Prix on Sunday, it struck me that TV sports broadcasters make a lot of use of transparent overlays when showing scores, results, times, statistics, or whatever. In the case of the Grand Prix, the driver rankings in the world championship were displayed on a semi-transparent overlay, behind which the live footage of the race circuit could be seen.</p> <p>So, naturally I started to wonder how I could achieve a similar visual effect in a Reporting Services report, like this:</p> <p><a href="http://lh4.ggpht.com/_WKO1IFE4fMA/TLWLRMSWnTI/AAAAAAAAAEA/3560BxN0hS4/s1600-h/Product%20Sales%20Report%5B6%5D.jpg"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Product Sales Report" border="0" alt="Product Sales Report" src="http://lh5.ggpht.com/_WKO1IFE4fMA/TLWLR0ko7pI/AAAAAAAAAEE/ZNrNsZ_43Ls/Product%20Sales%20Report_thumb%5B4%5D.jpg?imgmax=800" width="464" height="484" /></a> </p> <p>My first thought was to look at the <strong>BackgroundColor</strong> property of the Tablix data region and set the <strong>Transparency</strong> level. However, when I looked at the color picker control for the property, this is what I saw:</p> <p><a href="http://lh6.ggpht.com/_WKO1IFE4fMA/TLWLSfWLc_I/AAAAAAAAAEI/iQ_4PqYkWjE/s1600-h/ColorProperties%5B6%5D.jpg"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="ColorProperties" border="0" alt="ColorProperties" src="http://lh6.ggpht.com/_WKO1IFE4fMA/TLWLSzrp8PI/AAAAAAAAAEM/G1iJ-rxmz5s/ColorProperties_thumb%5B4%5D.jpg?imgmax=800" width="520" height="394" /></a> </p> <p>Note that the Transparency control is disabled. It turns out you can only set a transparency level for gauges and charts in Reporting Services – not for shapes or data regions. So, I needed to find an alternative approach.</p> <p>The answer I came up with was to create a semi-transparent .png graphic, and use it as the background image for the data region. I created this with PowerPoint, though of course you can use any graphics tool you like. I also used PowerPoint to find a suitable clipart image to use as the background for the report (on which the semi-transparent data region will be overlaid). In this case, I’m using the Adventure Works Cycles sample data, so a photo of a cyclist seems like a good choice.</p> <p><a href="http://lh4.ggpht.com/_WKO1IFE4fMA/TLWLTa9ZmsI/AAAAAAAAAEQ/HxHlBpoKAqM/s1600-h/PowerPoint%5B5%5D.jpg"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="PowerPoint" border="0" alt="PowerPoint" src="http://lh3.ggpht.com/_WKO1IFE4fMA/TLWLUM0JscI/AAAAAAAAAEU/ZpQL70w96b4/PowerPoint_thumb%5B3%5D.jpg?imgmax=800" width="605" height="484" /></a> </p> <p>You can take one of two approaches when it comes to sizing the semi-transparent image – you can make an extremely small image and then set the <strong>BackgroundRepeat</strong> property of the data region to <strong>Repeat</strong>, or you can make it bigger than the data region is ever likely to be and set the <strong>BackgroundRepeat</strong> property to <strong>Clip</strong> (or <strong>Repeat</strong> – it won’t matter since the image will be bigger than the data region anyway!). I found that PowerPoint tends to add some whitespace to the edge of a shape when you save it as a .png image, which showed up when repeating the background image, so I went with a large background image. Of course, had I used a more comprehensive graphics tool,  could have easily avoided this issue and got away with repeating a smaller image.</p> <p>To embed the images to the report, I added them to the <strong>Images</strong> folder in the <strong>Report Data</strong> pane in Report Designer.</p> <p><a href="http://lh3.ggpht.com/_WKO1IFE4fMA/TLWLUlRP92I/AAAAAAAAAEY/etmi_sqkVSQ/s1600-h/ReportData%5B6%5D.jpg"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="ReportData" border="0" alt="ReportData" src="http://lh6.ggpht.com/_WKO1IFE4fMA/TLWLVHxISLI/AAAAAAAAAEc/8TL820DusEg/ReportData_thumb%5B4%5D.jpg?imgmax=800" width="256" height="349" /></a> </p> <p>Then I set the <strong>BackgroundImage</strong> property of the tablix data region in which the report data is displayed, like so:</p> <p><a href="http://lh5.ggpht.com/_WKO1IFE4fMA/TLWLVwd8DWI/AAAAAAAAAEg/vJLOKS_WTlg/s1600-h/TablixProperties%5B5%5D.jpg"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="TablixProperties" border="0" alt="TablixProperties" src="http://lh3.ggpht.com/_WKO1IFE4fMA/TLWLWYYorxI/AAAAAAAAAEk/6MFowAnrJGg/TablixProperties_thumb%5B3%5D.jpg?imgmax=800" width="612" height="484" /></a> </p> <p>I’ve also used the semi-transparent image as the background for the report title textbox, which appears above the tablix data region.</p> <p>The next challenge was to apply the cyclist image to the background of the report, and ensure that the layout of the report overlays the data neatly. If you have a small data set with a known number of records(for example, in a “top 10 products” report), then this is relatively straightforward. However, for a dataset with an unknown size, the data region will be resized dynamically, and automatic pagination may break the report into multiple pages. In my case, I want to ensure that the report title appears on all pages, and that the table of data has a suitable space above and below it on all pages.</p> <p>To accomplish this, I added a page header and footer to the report and put the report title in the header. This ensures that if the report is paginated, the table on the second (and all subsequent pages) doesn’t start right at the top of the page. Similarly, the report footer ensures that there’s always a space after the table – it never goes all the way to the bottom of the page. I set the <strong>BackgroundImage</strong> of the report to the cyclist picture (clipped so it doesn’t repeat), and I set the <strong>InteractiveSize</strong> property of the report so that when viewed in the browser, the report has a maximum size that will keep the tablix well within the background image area. This was made tricky by the fact that Report Designer does not show the background image of the report in design view, so I had to preview the report and assess the right size through trial and error.</p> <p><a href="http://lh4.ggpht.com/_WKO1IFE4fMA/TLWLXB0-FrI/AAAAAAAAAEo/ImXjms0Dx3c/s1600-h/Report%20Designer%5B6%5D.jpg"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Report Designer" border="0" alt="Report Designer" src="http://lh6.ggpht.com/_WKO1IFE4fMA/TLWLXi6yi7I/AAAAAAAAAEs/fW2AylnLDaY/Report%20Designer_thumb%5B4%5D.jpg?imgmax=800" width="644" height="435" /></a> </p> <p>Obviously, the report size is optimized for interactive viewing, and though you can set the <strong>PageSize</strong> property of the report to an appropriate size for any other renderers you plan to use, my experience is that using background images and contrived layouts in reports you intend to render to a different format can result in some pretty horrible looking exported reports. One solution I have used in the past for this is to create the version that’s tailored for online viewing, and include a link to an offline version that has more conventional formatting for printing or exporting.</p> <p>You can download the sample report I created from <a href="http://cid-ddad9079cff45619.office.live.com/self.aspx/Public/Report%20Project.zip" target="_blank">here</a>. You’ll also need SQL Server 2008 R2 with Reporting Services (you can get the free Express edition from <a href="http://www.microsoft.com/express/Database/InstallOptions.aspx" target="_blank">here</a>) and the AdventureWorksDW2008R2 sample database (which you can get from <a href="http://msftdbprodsamples.codeplex.com/" target="_blank">here</a>).</p> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:41603c29-f652-49ec-9aef-cb35be4c2bb5" class="wlWriterEditableSmartContent">del.icio.us Tags: <a href="http://del.icio.us/popular/SQL+Server" rel="tag">SQL Server</a>,<a href="http://del.icio.us/popular/Reporting+Services" rel="tag">Reporting Services</a></div>Graeme Malcolmhttp://www.blogger.com/profile/02246562883877200692noreply@blogger.com0tag:blogger.com,1999:blog-2066281766861287065.post-70010258089651836152010-08-21T19:10:00.001+01:002010-08-21T19:30:21.986+01:00Geocoding (and reverse-geocoding) with Bing Maps<p>In <a href="http://graemesplaceblog.blogspot.com/search/label/Bing%20Maps" target="_blank">previous posts</a>, I’ve explored how to use Bing Maps with SQL Server spatial data. In this post, I want to explore the Bing Maps control a little more. Specifically, I want to look at how to use the Bing Maps control to geocode a street address (that is, find the latitude and longitude coordinates of the address), and how to reverse-geocode a spatial location to find the corresponding street address.</p> <p>The first step in building a Web page that uses the Bing Maps control to geocode an address, is to add a <div> tag to host the map control and use the page body’s <strong>onload</strong> event to call a JavaScript function that loads the map – like this:</p> <blockquote> <p><font face="Courier New"><head></font></p> <p><font face="Courier New"><!-- add a reference to the Virtual Earth map control --> <br /><script type="text/javascript" <br />        src="</font><a href="http://dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=6.3""><font face="Courier New">http://dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=6.3"</font></a><font face="Courier New">> <br /></script></font></p> <p><font face="Courier New"><script type="text/javascript"> <br />    function GetMap() { <br />        map = new VEMap('mapDiv'); <br />        map.LoadMap(); <br />    } <br /></script></font></p> <p><font face="Courier New"></head> <br /><body onload="GetMap()"> </font></p> <p><font face="Courier New">    <div id="mapDiv" style="position:relative; width:600px; height:400px;"> <br />    </div> <br /></body></font></p> </blockquote> <p>Next, you need a textbox so that users can enter the address they want to geocode, a button they can click to geocode the address, and two textboxes to show the resulting latitude and longitude coordinates.</p> <blockquote> <p><font face="Courier New">Address:<input id="txtAddress" type="text" style="width:340px" /> <br />        <input style="width:60px" id="btnFind" type="button" value="Find" onclick="return btnFind_onclick()" /> <br />Latitide:<input id="txtLat" type="text" style="width:400px" /> <br />Longitide:<input id="txtLong" type="text" style="width:400px" /></font></p> </blockquote> <p>Note the <strong>onclick</strong> property of the button control, this calls the function that uses Bing Maps to geocode the address. Here’s the code to do that:</p> <blockquote> <p><font face="Courier New">function btnFind_onclick() { <br />    //Geocode the address to find the Lat/Long location <br />    map.Geocode(document.getElementById("txtAddress").value, onGeoCode, new VEGeocodeOptions()) <br />} </font></p> </blockquote> <p>Note that the code in the btnFind_onclick function calls the <strong>Geocode</strong> method of the map control, specifying the address to be geocoded, the name of the callback function to use to process the results (<strong>onGeoCode</strong>), and a <strong>VEGeocodeOptions</strong> object that ensures the user is shown a list of options when the address has multiple possible matches. The calback function looks like this:</p> <blockquote> <p><font face="Courier New">function onGeoCode(layer, resultsArray, places, hasMore, veErrorMessage) { <br />    var findPlaceResults = null; </font></p> <p><font face="Courier New">    // verify the search location was found <br />    if (places == null || places.length < 1) { <br />        alert("The address was not found"); <br />    } <br />    else { <br />        // we've successfully geocoded the address, so add a pin <br />        findPlaceResults = places[0].LatLong; <br />        addPinToMap(findPlaceResults); <br />    } <br />}</font></p> </blockquote> <p>The callback function is called when the geocode method returns, and assuming a location has been found the JavaScript calls the following <strong>addPinToMap</strong> function to display the results:</p> <blockquote> <p><font face="Courier New">function addPinToMap(LatLon) { <br />    // clear all shapes and add a pin <br />    map.Clear() <br />    var pushpoint = new VEShape(VEShapeType.Pushpin, LatLon); <br />    map.AddShape(pushpoint); </font></p> <p><font face="Courier New">    // center and zoom on the pin <br />    map.SetCenterAndZoom(LatLon, 13); </font></p> <p><font face="Courier New">    // display the Lat and Long coordinates <br />    document.getElementById("txtLat").value = LatLon.Latitude; <br />    document.getElementById("txtLong").value = LatLon.Longitude; <br />}</font></p> </blockquote> <p>This adds a pin to the map and centers and zooms to ensure it can be seen clearly. It then displays the latitude and longitude in the textboxes defined earlier.</p> <p>We now have all the code required to geocode an address, but  what about the opposite? Ideally, we also want the user to be able to click a location on the map and reverse-geocode the point that was clicked to find the address.</p> <p>Of course, the Bing Maps map control already responds to user clicks, so our application will use right-clicks to enable users to specify a location. We’ll do this by attaching an <strong>onclick</strong> event handler to the map control in the GetMap function (which you will recall is called when the page loads to display the map), and then checking for a right-click before reverse-geocoding the clicked location:</p> <blockquote> <p><font face="Courier New">// added to the GetMap function <br />map.AttachEvent("onclick", map_click);</font></p> <p><font face="Courier New"></font></p> </blockquote> <blockquote> <p><font face="Courier New">function map_click(e) { <br />    // check for right-click <br />    if (e.rightMouseButton) { <br />        var clickPnt = null; </font></p> <p><font face="Courier New">        // some map views return pixel XY coordinates, some Lat Long <br />        // We need to convert XY to LatLong <br />        if (e.latLong) { <br />            clickPnt = e.latLong; <br />        } else { <br />            var clickPixel = new VEPixel(e.mapX, e.mapY); <br />            clickPnt = map.PixelToLatLong(clickPixel); <br />        } </font></p> <p><font face="Courier New">        // add a pin to the map <br />        addPinToMap(clickPnt) </font></p> <p><font face="Courier New">        //reverse-geocode the point the user clicked to find the street address <br />        map.FindLocations(clickPnt, onReverseGeoCode); <br />    } <br />}</font></p> <p><font face="Courier New"></font></p> </blockquote> <p>This code finds the latitude and longitude of the clicked location (in some views, the map control uses X and Y pixel coordinates so we need to check for that), displays a pin on the map at the clicked location, and then uses the <strong>FindLocations</strong> method of the map control to find the address. A callback function named <strong>onReverseGeoCode</strong> is used to process the results:</p> <blockquote> <p><font face="Courier New">function onReverseGeoCode(locations) { <br />    // verify the search location was found <br />    if (locations == null || locations.length < 1) { <br />        document.getElementById("txtAddress").value = "Address not found"; <br />    } <br />    else { <br />        // we've successfully found the address, so update the Address textbox <br />        document.getElementById("txtAddress").value = locations[0].Name; <br />    } <br />}</font></p> </blockquote> <p>The completed application looks like this:</p> <p><a href="http://lh5.ggpht.com/_WKO1IFE4fMA/THAWnPRwMTI/AAAAAAAAADw/UfBzPUR943E/s1600-h/BingGeocoder%5B5%5D.png"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="BingGeocoder" border="0" alt="BingGeocoder" src="http://lh3.ggpht.com/_WKO1IFE4fMA/THAWn4NHd0I/AAAAAAAAAD0/COQb0hLEVIg/BingGeocoder_thumb%5B3%5D.png?imgmax=800" width="581" height="484" /></a> </p> <p>You can try the page out for yourself <a href="http://www.graemesplace.com/BingGeocoder.htm" target="_blank">here</a>, and you can download the source code from <a href="http://cid-ddad9079cff45619.office.live.com/self.aspx/Public/BingGeocoder.zip" target="_blank">here</a>.</p> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:e1696512-1b2a-4cf9-a397-3be83a25efb2" class="wlWriterEditableSmartContent">del.icio.us Tags: <a href="http://del.icio.us/popular/Bing+Maps" rel="tag">Bing Maps</a></div> Graeme Malcolmhttp://www.blogger.com/profile/02246562883877200692noreply@blogger.com0tag:blogger.com,1999:blog-2066281766861287065.post-35631950257840826932010-06-08T13:42:00.001+01:002010-10-08T15:55:19.585+01:00Creating Multi-Sheet Workbooks with SQL Server 2008 R2 Reporting Services<p>One thing I’ve learned in over ten years of creating database and reporting solutions, is that no matter how dynamic and interactive you make online reports, no matter how much you embed live reporting into the user interface of applications, and no matter how funky a dashboard you design; many executives don’t believe data is real unless it’s in a spreadsheet. That’s why one of the most used features of Reporting Services is the ability to render reports in Excel format.</p> <p>However, I recently encountered a situation where my company hosts a <a href="http://www.cm-luminosity.co.uk/" target="_blank">Luminosity</a> learning management system, and uses SQL Server Reporting Services to generate reports of student activity in Excel format. The number of students has grown substantially over time, and we hit an unforeseen problem – The Excel 2003 format that Reporting Services renders the reports in supports a maximum of 65,536 rows per worksheet, and the report (which shows students and all training they have completed) has grown to exceed this limit.</p> <p>After some head scratching, I investigated enhanced new page-break support in SQL Server 2008 R2 and came up with a solution that works, and which can enhance the ability to create complex reports in Excel format for those pesky executives – so I thought I’d share it here.</p> <p>Let’s imagine your executives want a report in Excel format that lists every customer, along with their contact details. If you have less than 65,537 customers, you could design a report that simply lists them in a worksheet, but if you have more customers than that (or you want to include headers, spaces, or other elements in your report that will use rows when rendered to Excel), then you’ll need a better solution. Ideally, you might want to create something like this – an Excel workbook with multiple worksheets, consisting of a generic “cover page” and a tab for each letter of the alphabet so that you can view customers by last name.</p> <p><a href="http://lh6.ggpht.com/_WKO1IFE4fMA/TA46r7sAlrI/AAAAAAAAAC4/NhmClQo2d1k/s1600-h/Workbook%5B7%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px" title="Workbook" border="0" alt="Workbook" src="http://lh5.ggpht.com/_WKO1IFE4fMA/TA46s2o_X_I/AAAAAAAAAC8/-VO0gm7xd5I/Workbook_thumb%5B5%5D.png?imgmax=800" width="554" height="484" /></a> </p> <p>You can download a copy of this workbook from <a href="http://cid-ddad9079cff45619.office.live.com/view.aspx/Public/Customers.xls" target="_blank">here</a>.</p> <p>Each worksheet in the workbook lists customers with a last name that begins with the letter on the corresponding worksheet tab, as shown here:</p> <p><a href="http://lh6.ggpht.com/_WKO1IFE4fMA/TA46ttuhwfI/AAAAAAAAADA/rfwqHHZqbmQ/s1600-h/Customers-A%5B6%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px" title="Customers-A" border="0" alt="Customers-A" src="http://lh3.ggpht.com/_WKO1IFE4fMA/TA46uls6aII/AAAAAAAAADE/RwNWNKurju8/Customers-A_thumb%5B4%5D.png?imgmax=800" width="554" height="484" /></a> </p> <p>To create this report, I used the AdventureWorks2008R2 sample database (which you can download from <a href="http://msftdbprodsamples.codeplex.com/" target="_blank">here</a>) and the following Transact-SQL query:</p> <p><font face="Times New Roman">SELECT Title, FirstName, LastName, AddressLine1, City, StateProvinceName, PostalCode, CountryRegionName <br />FROM Sales.vIndividualCustomer</font></p> <p>The report includes a tablix data region that includes a details grouping (in which all fields are displayed) and a grouping based on the following expression (which returns the first character of the <strong>LastName</strong> field in upper-case):</p> <p><font face="Times New Roman">=ucase(left(Fields!LastName.Value, 1))</font></p> <p>I also added an image and a textbox to the report, and placed them above the tablix data region as shown here:</p> <p><a href="http://lh3.ggpht.com/_WKO1IFE4fMA/TA46vX1_r7I/AAAAAAAAADI/YI6xKnogzpA/s1600-h/ReportDesign%5B4%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px" title="ReportDesign" border="0" alt="ReportDesign" src="http://lh3.ggpht.com/_WKO1IFE4fMA/TA46wWOv8mI/AAAAAAAAADM/UCo-42Lo8zw/ReportDesign_thumb%5B2%5D.png?imgmax=800" width="567" height="484" /></a> </p> <p>To create the page breaks that generate the worksheets when rendered to Excel,  I’ve used some of the new page-break support in SQL Server 2008 R2. First of all, I’ve set the report’s <strong>InitialPageName</strong> property to <em>Customer Addresses</em>, as shown here:</p> <p><a href="http://lh6.ggpht.com/_WKO1IFE4fMA/TA46wywwQpI/AAAAAAAAADQ/2c06Bp0hlFY/s1600-h/ReportProperties%5B4%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px" title="ReportProperties" border="0" alt="ReportProperties" src="http://lh3.ggpht.com/_WKO1IFE4fMA/TA46xfvHnrI/AAAAAAAAADU/JaYAUXT-Y_8/ReportProperties_thumb%5B2%5D.png?imgmax=800" width="280" height="484" /></a> </p> <p>This property defines the default name for the first page of the report (or for all pages if no explicit page breaks with page names are defined). That’s why in the Excel workbook, the “cover page” has this name on its worksheet tab (if the <strong>InitialPageName</strong> property wasn’t set, the worksheet tab would show the the report name).</p> <p>Next, I created a page break at the start of the tablix as shown here:</p> <p><a href="http://lh5.ggpht.com/_WKO1IFE4fMA/TA46x2gnQLI/AAAAAAAAADY/eC1RHCRCpBw/s1600-h/TablixProperties%5B4%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px" title="TablixProperties" border="0" alt="TablixProperties" src="http://lh5.ggpht.com/_WKO1IFE4fMA/TA46ymC0xpI/AAAAAAAAADc/Y4vn0crupIE/TablixProperties_thumb%5B2%5D.png?imgmax=800" width="280" height="484" /></a></p> <p>This causes the data in the table to be displayed on a new page, effectively defining the “cover page” as “everything before this”.</p> <p>Finally, I used the properties of the grouping I defined earlier to create a page break between each instance of the grouping, and apply a page name based on the same expression used to define the grouping. In other words, there will be a page for each first character of the <strong>LastName</strong> field, and the page name for this page will be the grouping character.</p> <p><a href="http://lh6.ggpht.com/_WKO1IFE4fMA/TA46zCMvZeI/AAAAAAAAADg/9Wt6DqKoR2o/s1600-h/GroupProperties%5B4%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px" title="GroupProperties" border="0" alt="GroupProperties" src="http://lh5.ggpht.com/_WKO1IFE4fMA/TA46ziFIaxI/AAAAAAAAADk/TJJKlpgUruc/GroupProperties_thumb%5B2%5D.png?imgmax=800" width="392" height="484" /></a> </p> <p>You can download the complete solution from <a href="http://cid-ddad9079cff45619.office.live.com/self.aspx/Public/My%20Reports.zip" target="_blank">here</a>. You’ll need to have an instance of SQL Server 2008 R2 with the AdventureWorks2008R2 database (the DataSet in the report assumes that this is in the default instance of SQL Server 2008 R2 on the local computer).</p> <p>Exporting this report to Excel creates the desired multi-sheet workbook, with a tab for each initial character of the last name, and a “cover page”.</p> <p>Hopefully, you can see from this article how easy it is to create multi-sheet workbook reports that will add value to your reporting solutions.</p> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:6ac998c7-4586-4549-9466-8900d5d76a22" class="wlWriterEditableSmartContent">Technorati Tags: <a href="http://technorati.com/tags/SQL+Server+2008+R2" rel="tag">SQL Server 2008 R2</a>,<a href="http://technorati.com/tags/Reporting+Services" rel="tag">Reporting Services</a>,<a href="http://technorati.com/tags/Excel" rel="tag">Excel</a></div>Graeme Malcolmhttp://www.blogger.com/profile/02246562883877200692noreply@blogger.com0tag:blogger.com,1999:blog-2066281766861287065.post-36565296634179387352010-01-23T22:53:00.001+00:002010-01-25T09:09:04.255+00:00First Steps with the Silverlight Bing Maps Control<p>A while back, I posted an <a href="http://graemesplaceblog.blogspot.com/2009/12/adventures-in-spatial-data-part-2.html" target="_blank">article about displaying spatial data from SQL Server with what was then called the Virtual Earth Maps control</a>. The article demonstrated an application that retrieves information about locations visited by a toy stuffed bear named Beanie, and displays those locations on a map. Since then, the Virtual Earth Map control has been renamed Bing Maps, and a Silverlight version of the map control is now available – so naturally, the time has come to update the Beanie Tracker application.</p> <p>Unlike the Javascript version of the Bing Maps control, to use the Silverlight Bing Maps control, you need to sign up at the <a href="https://www.bingmapsportal.com/" target="_blank">Bing Maps Account Center</a> and obtain a key.  However, this is a straightforward process (and free!). Once you have a key, you can create Silverlight applications that display and manipulate the Bing Maps control. To do this, download and install the Bing Maps control.  Then create a new Silverlight application and add a reference to the assemblies provided with the control as shown here:</p> <p><a href="http://lh3.ggpht.com/_WKO1IFE4fMA/S1t9-HrD3QI/AAAAAAAAACo/jAU6FEQ1ylo/s1600-h/Ref%5B5%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px" title="Ref" border="0" alt="Ref" src="http://lh4.ggpht.com/_WKO1IFE4fMA/S1t9-x2NPlI/AAAAAAAAACs/5EtSLPT74ro/Ref_thumb%5B3%5D.png?imgmax=800" width="488" height="414" /></a> </p> <p>Now that you have a reference to the Map control, you can add its namespace to a XAML UserControl and include a map object in the Silverlight user interface as shown here, referencing the key you obtained from the Bing Maps Account Center:</p> <p><font face="Arial"><UserControl x:Class="BeanieTracker.MainPage" <br />    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" <br />    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" <br />    xmlns:d="http://schemas.microsoft.com/expression/blend/2008" <br />    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" <br />    <font color="#ff0080">xmlns:m="clr-namespace:Microsoft.Maps.MapControl;assembly=Microsoft.Maps.MapControl" <br /></font>    mc:Ignorable="d" d:DesignWidth="400" d:DesignHeight="500" Width="700" Height="400"> <br />  <Grid x:Name="LayoutRoot"> </font></p> <p><font face="Arial">        <Grid.RowDefinitions> <br />            <RowDefinition Height="Auto"/> <br />        </Grid.RowDefinitions> <br />        <Grid.ColumnDefinitions> <br />            <ColumnDefinition Width="200" /> <br />            <ColumnDefinition Width="*"/> <br />        </Grid.ColumnDefinitions> </font></p> <p><font face="Arial">        <StackPanel Grid.Column="0" Grid.Row="0" Orientation="Vertical"> <br />            <Image Name="imgBeanie" Source="Beanie.jpg"></Image> <br />            <Button Cursor="Hand" Width="195" Height="25" HorizontalAlignment="Left" Content="Show Locations" x:Name="b1" Margin="2,10,0,1" Click="b1_Click"></Button> <br />       </StackPanel> </font></p> <p><font face="Arial">        <font color="#ff0080"><m:Map Name="map" Grid.Column="1" Grid.Row="0" CredentialsProvider="YOUR_KEY" Width="475" Height="300" /></font> </font></p> <p><font face="Arial">    </Grid> <br /></UserControl></font></p> <p>Adding the map control displays a Bing Maps map in Silverlight user interface, enabling users to view the map and interact with it through it’s built in controls for changing the zoom level or view, and moving around the map. However, to add custom functionality, you need to write some code to manipulate the map control.</p> <p>The Silverlight map control exposes a number of objects with properties and methods you can control programmatically, though some of the functionality in the Javascript version of the control has not been implemented in the Silverlight version. Unfortunately, the functionality that enables you to import a GeoRSS feed as a ShapeLayer onto the map is not implemented in the Silverlight control, so a simpler version of the Beanie Tracker application is required. In this version, I’ve written code to retrieve the GeoRSS feed, and then parse the XML feed and create a pushpin for each GML <strong>pos</strong> element, as shown here:</p> <p><font face="Arial">private void b1_Click(object sender, RoutedEventArgs e) <br /> { <br />     Uri url = new Uri("../Feed.aspx?data=locations", UriKind.Relative); <br />     WebClient client = new WebClient(); <br />     client.DownloadStringCompleted += new DownloadStringCompletedEventHandler(client_DownloadStringCompleted); <br />     client.DownloadStringAsync(url); <br /> } </font></p> <p><font face="Arial"> void client_DownloadStringCompleted(object sender, DownloadStringCompletedEventArgs e) <br /> { <br />     if (e.Error == null) <br />     { <br />         StringReader stream = new StringReader(e.Result); <br />         XmlReader reader = XmlReader.Create(stream); <br />         string gmlURI = "http://www.opengis.net/gml";</font></p> <p><font face="Arial">         while (reader.Read()) <br />         { <br />             if (reader.NodeType == XmlNodeType.Element) <br />             { <br />                 if (reader.NamespaceURI == gmlURI && reader.Name == reader.Prefix + ":pos") <br />                 { <br />                     string[] loc = reader.ReadInnerXml().Split(" ".ToCharArray()); <br />                     double lat = Double.Parse(loc[0]); <br />                     double lon = double.Parse(loc[1]); <br />                     <font color="#ff0080">Pushpin p = new Pushpin(); <br />                     p.Location = new Location(lat, lon); <br />                     map.Children.Add(p); <br /></font>                 } <br />             } <br />         } <br />     } <br /> }</font></p> <p>You can see the resulting application at <a href="http://www.graemesplace.com/beanietracker.aspx" target="_blank">http://www.graemesplace.com/beanietracker.aspx</a>. </p>Graeme Malcolmhttp://www.blogger.com/profile/02246562883877200692noreply@blogger.com0tag:blogger.com,1999:blog-2066281766861287065.post-48611142006756066502010-01-04T16:33:00.001+00:002010-01-04T16:35:24.696+00:00Data-Tier Applications in SQL Server 2008 R2<p>In <a href="http://graemesplaceblog.blogspot.com/2009/12/multi-server-management-with-sql-server.html" target="_blank">a previous post</a>, I discussed some of the new multi-server management capabilities in SQL Server 2008 R2. One of the new features I conspicuously side-stepped covering in that post is the concept of a data-tier application – and that’s what I want to describe in this post.</p> <p>Data-tier applications provide a useful way to encapsulate all of the logical and physical components of an application that need to be deployed and managed as a unit on a SQL Server instance. For example, consider a typical business application. It probably consists of a number of tiers, including a presentation tier (which might be a Windows Form application or an ASP.NET Web application), a middle-tier (for example a library of .NET assemblies that provide objects to manage the business logic of the application), and a data-tier. The data-tier consists primarily of a logical database (and all the schemas, tables, views, and so on it contains) but it also includes server-level objects (such as any logins that the middle-tier uses to connect to the database server) and the physical database and log files used to store the database.</p> <p>In the past, deploying or migrating the data-tier of an application has involved examining the database to find its server-level dependencies and physical storage properties, moving the database from its test/staging server to the production server (via backup and restore, SSIS, or a Transact-SQL script to recreate the database schema and data – taking into account any differences in physical storage media), and creating a script to recreate any server-level objects used by the database.</p> <p>In SQL Server 2008 R2, this task has been simplified through the concept of data-tier applications. Software developers using Visual Studio 2010 will be able to create data-tier applications that encapsulate the entire data tier, or alternatively you can use new wizards in SQL Server Management Studio to create a data-tier application from an existing database, and deploy a data-tier application to a new database.</p> <p>To create a data-tier application from an existing database, right-click the database you want to package and start the data-tier extraction wizard as shown in the following screenshot.</p> <p><a href="http://lh3.ggpht.com/_SfPmeMl-v20/S0IWL0SJ3qI/AAAAAAAAAH4/nNvG0gflZ7w/s1600-h/Picture1%5B3%5D.png"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Picture1" border="0" alt="Picture1" src="http://lh4.ggpht.com/_SfPmeMl-v20/S0IWMiSDD1I/AAAAAAAAAH8/1FCy8J1fSL4/Picture1_thumb%5B1%5D.png?imgmax=800" width="624" height="484" /></a> </p> <p>This opens the following wizard screen:</p> <p><a href="http://lh4.ggpht.com/_SfPmeMl-v20/S0IWNe9A--I/AAAAAAAAAIA/sW79xwv8gvk/s1600-h/Picture2%5B3%5D.png"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Picture2" border="0" alt="Picture2" src="http://lh6.ggpht.com/_SfPmeMl-v20/S0IWONO3RgI/AAAAAAAAAIE/fEr_TmPaPVk/Picture2_thumb%5B1%5D.png?imgmax=800" width="524" height="484" /></a> </p> <p></p> <p>The first step is to set the properties of the data-tier application (note that the wizard uses the abbreviation “DAC” – technically, this stands for “Data Tier Application Component”, which you can think of as a unit of deployment, or a deployable package for a data-tier application. The term “data-tier application” is usually taken to mean a deployed instance of a DAC.</p> <p><a href="http://lh6.ggpht.com/_SfPmeMl-v20/S0IWOlddlFI/AAAAAAAAAII/9wYeiL-WQuQ/s1600-h/Picture3%5B3%5D.png"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Picture3" border="0" alt="Picture3" src="http://lh5.ggpht.com/_SfPmeMl-v20/S0IWPKTiriI/AAAAAAAAAIM/Fmw-XIEA2LQ/Picture3_thumb%5B1%5D.png?imgmax=800" width="524" height="484" /></a> </p> <p>As well as standard properties such as a name, version, and description for your data-tier application, you specify the file location where the DAC package should be created.</p> <p><a href="http://lh5.ggpht.com/_SfPmeMl-v20/S0IWP1Y__JI/AAAAAAAAAIQ/62F9YDYkDdY/s1600-h/Picture4%5B3%5D.png"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Picture4" border="0" alt="Picture4" src="http://lh3.ggpht.com/_SfPmeMl-v20/S0IWQmxaeNI/AAAAAAAAAIU/phoCQshYFfQ/Picture4_thumb%5B1%5D.png?imgmax=800" width="524" height="484" /></a> </p> <p></p> <p>The wizard then examines the database and its dependencies, and lists the objects that will included in the DAC. In the November CTP, not all database objects are supported in DACs – for example, you can’t include columns with spatial data types such as <strong>geometry</strong> or <strong>geography</strong>. The list of supported objects will no doubt expand over time. In this example, the wizard has identified the database objects included in the database, and also the users and associated logins that are required.</p> <p><a href="http://lh5.ggpht.com/_SfPmeMl-v20/S0IWRC4egaI/AAAAAAAAAIY/hKzQrJaCVSY/s1600-h/Picture5%5B5%5D.png"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Picture5" border="0" alt="Picture5" src="http://lh4.ggpht.com/_SfPmeMl-v20/S0IWRrCPU3I/AAAAAAAAAIc/4sbTaSrEjGY/Picture5_thumb%5B3%5D.png?imgmax=800" width="524" height="484" /></a> </p> <p>Finally, the wizard builds the package for the DAC. The package itself is a single file with the extension .dacpac, as shown here:</p> <p><a href="http://lh4.ggpht.com/_SfPmeMl-v20/S0IWSH8JVnI/AAAAAAAAAIg/J9Tuxzu1_g8/s1600-h/Picture6%5B4%5D.png"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Picture6" border="0" alt="Picture6" src="http://lh6.ggpht.com/_SfPmeMl-v20/S0IWSioVh8I/AAAAAAAAAIk/j4pbs3ySXfc/Picture6_thumb%5B2%5D.png?imgmax=800" width="540" height="392" /></a> </p> <p>This file is actually a zip archive that contains a number of XML files describing the components of the DAC. If you append a.zip extension to the filename, you can examine these files as shown here:</p> <p><a href="http://lh6.ggpht.com/_SfPmeMl-v20/S0IWTbzyBMI/AAAAAAAAAIo/UOM2mo-GjNI/s1600-h/Picture7%5B3%5D.png"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Picture7" border="0" alt="Picture7" src="http://lh3.ggpht.com/_SfPmeMl-v20/S0IWT_OB-1I/AAAAAAAAAIs/3laW2iaGES4/Picture7_thumb%5B1%5D.png?imgmax=800" width="566" height="392" /></a> </p> <p>To deploy the data-tier application defined in the DAC, right-click the server you want to deploy it to and click <strong>Deploy Data-tier Application</strong>:</p> <p><a href="http://lh6.ggpht.com/_SfPmeMl-v20/S0IWUb--djI/AAAAAAAAAIw/f2BalRzVDSg/s1600-h/Picture8%5B3%5D.png"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Picture8" border="0" alt="Picture8" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj90Bs388tLA0aiZsPL2v_9RCpOUhlipamKso_MoSa3kHkTQBAL_poReOiDb0h2hNCdkGoD975hOhnig0vw7xQTPPBDVoHDTr5KYq6lsCdesCtVPELHc7DZu92xm_JoDXW1guSdLLbYp_NF/?imgmax=800" width="624" height="484" /></a> </p> <p>This starts another wizard, as shown here:</p> <p><a href="http://lh4.ggpht.com/_SfPmeMl-v20/S0IWVsjzmAI/AAAAAAAAAI4/MoWwSwzHrmo/s1600-h/Picture9%5B3%5D.png"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Picture9" border="0" alt="Picture9" src="http://lh5.ggpht.com/_SfPmeMl-v20/S0IWWagJ0BI/AAAAAAAAAI8/UgJuuUD6CXo/Picture9_thumb%5B1%5D.png?imgmax=800" width="524" height="484" /></a> </p> <p></p> <p></p> <p></p> <p></p> <p>The first step is to select the DAC package file you want to deploy:</p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgI7vM7Iw7zDrDjPp55LcElkxjFy-0d7OHrrPvI1axq1MDqL8IFr1-PX-PebB2avU6VJgU_kQH0VBAETH3BdtxnXANQyP3z9J1Fcm27mtQWpTRLImgfN6-hQcxmwXZyUHjXe_xR_RKJxhRL/s1600-h/Picture10%5B3%5D.png"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Picture10" border="0" alt="Picture10" src="http://lh4.ggpht.com/_SfPmeMl-v20/S0IWXSs4VTI/AAAAAAAAAJE/tztjxA-uprM/Picture10_thumb%5B1%5D.png?imgmax=800" width="524" height="484" /></a></p> <p>Then you can change the database name and file locations if desired.</p> <p><a href="http://lh3.ggpht.com/_SfPmeMl-v20/S0IWYI88pmI/AAAAAAAAAJI/itF0VZGnxWI/s1600-h/Picture11%5B3%5D.png"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Picture11" border="0" alt="Picture11" src="http://lh4.ggpht.com/_SfPmeMl-v20/S0IWYpDzmlI/AAAAAAAAAJM/vollwm4e_Q8/Picture11_thumb%5B1%5D.png?imgmax=800" width="524" height="484" /></a> </p> <p>The wizard summarizes the settings, …</p> <p><a href="http://lh4.ggpht.com/_SfPmeMl-v20/S0IWZPaJLsI/AAAAAAAAAJQ/JkhQ70ugo1Y/s1600-h/Picture12%5B3%5D.png"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Picture12" border="0" alt="Picture12" src="http://lh5.ggpht.com/_SfPmeMl-v20/S0IWZtrA6gI/AAAAAAAAAJU/yYJAPQRo4rM/Picture12_thumb%5B1%5D.png?imgmax=800" width="524" height="484" /></a>  </p> <p>…,and then deploys the data-tier application to the server.</p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiaYtC5uJdpwxt14tUXO7631wepVirEvHHXxb8mFPmN7gUvQt03lzeUPbnzrEK-blKqSlP7zU3mvRGjFEA-RMyYfKa75VVnJ3smgrutfA5EPDKxytl-Hhj5OfUtsAIpiQm6M2B7nGw9U_8U/s1600-h/Picture13%5B3%5D.png"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Picture13" border="0" alt="Picture13" src="http://lh6.ggpht.com/_SfPmeMl-v20/S0IWa9OIMwI/AAAAAAAAAJc/szC0rvWIQ8Q/Picture13_thumb%5B1%5D.png?imgmax=800" width="524" height="484" /></a> </p> <p>You can then use SQL Server Management Studio to confirm that the database and any dependent objects has been deployed. In this case, you can see that the <strong>MyAppLogin</strong> login has been recreated on the target server along with the database.</p> <p><a href="http://lh4.ggpht.com/_SfPmeMl-v20/S0IWbYLISGI/AAAAAAAAAJg/Mt0HcVBBRDA/s1600-h/Picture14%5B3%5D.png"><img style="border-right-width: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto" title="Picture14" border="0" alt="Picture14" src="http://lh5.ggpht.com/_SfPmeMl-v20/S0IWcE1ya0I/AAAAAAAAAJk/0FPPCHTbhag/Picture14_thumb%5B1%5D.png?imgmax=800" width="644" height="482" /></a> </p> <p>This ability to treat the entire data-tier as a single, encapsulated package should simplify database application deployment and management significantly.</p> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:8e4c1c9a-f3f9-4043-a879-60c0dead5537" class="wlWriterEditableSmartContent">del.icio.us Tags: <a href="http://del.icio.us/popular/SQL+Server+2008+R2" rel="tag">SQL Server 2008 R2</a></div>Graeme Malcolmhttp://www.blogger.com/profile/02246562883877200692noreply@blogger.com0