SQL Server Private Cloud Demo Infrastructure

In a previous article, I posted a video of Darmadi Komo’s session at TechEd in which he showcased SQL Server in the Microsoft Private Cloud. The demo environment for this session was created by technologists at Content Master, and I thought it might be useful to share some of the “behind the scenes” details of the demo infrastructure. The important point to make about this is that this is a real, working private cloud environment – there are no smoke and mirrors for demo purposes. You might make some different hardware and architectural choices in a production scenario, but the demo environment works and could be used to implement a fully-functional self-service provisioned private cloud solution.

When we started designing a demo environment for the Microsoft Private Cloud, we set ourselves the challenge of ensuring that the entire demo could be run on a single laptop computer, and that it could be easily replicated on another computer if necessary. We selected a Dell XPS 15 laptop with 8GB of RAM, an Intel Core i5 CPU, and a 500GB hard disk as our development machine and started to think about how to design the software environment. Fundamentally, the environment requires the following components:

• An Active Directory domain
• A Hyper-V host
• A Virtual Machine management server running:
  • System Center Virtual Machine Manager 2008 R2 SP1
  • The VMM Self-Service Portal 2.0 SP1 (note that this is a Solution Accelerator tool provided by Microsoft and is separate from the Self-Service Portal provided with System Center Virtual Machine Manager)
  • The Microsoft Assessment and Planning (MAP)Toolkit
  • Microsoft Office Word and Excel (required by the MAP toolkit)
• A virtual machine template consisting of:
  • A sysprepped installation of Windows Server 2008 R2
  • A prepared installation of SQL Server 2008 R2
  • An ASP.NET application for the “Point of Sale” application shown in the demonstration
  • A batch file script to complete the prepared SQL Server installation when the server is first started
• A couple of virtual machines based on the template to use as existing POS servers in the demonstration
• A few more virtual machines based on the template to use as workstations for different users during the demonstration
• Some instances of SQL Server (various versions and editions) and Oracle for the MAP tool to discover

Our initial starting point for building the environment was the “Build Your Own” section of the Microsoft Private Cloud page, which includes a number of deployment guides. Then we struck gold and found Alan le Marquand’s excellent blog article on creating a private cloud. However, we soon discovered that while Alan’s approach is indeed a great way to try out Microsoft’s private cloud solution for yourself, it assumes that the bulk of the software – including the Active Directory domain and System Center components are installed on the physical host. This would satisfy our goal of building the entire demo environment on a single computer, but it wouldn’t meet our portability goal of being able to easily replicate the environment on another computer. Essentially, we’d have to install most of the software again.

Our preferred approach was to deploy as many of the components of the demo infrastructure as possible in virtual machines, so they can be easily copied to another physical computer and used to recreate the demo environment with a few reconfiguration steps. To achieve this, we came up with a high-level design that looks like this:

The host computer (HV-SRV-01) was straightforward enough to set up. We just installed Windows Server 2008 R2, applied updates until SP1 was installed, and then enabled the Hyper-V role. The SP1 update was required because we wanted to show the Dynamic Memory feature it adds to Hyper-V. We then configured a virtual network and changed the default folder for Virtual machines and VHDs to C:\VMs.

Next up was the domain controller (VM-DC-01). This is a virtual machine, which we created on the HV-SRV-01 host using Hyper-V Manager. We installed Windows Server 2008 R2 and used dcpromo to create an Active Directory domain named lucerne.msft. Since the domain controller would play a fairly minimal role in the demo, we also installed Oracle XE and SQL Server Express on it (so they could be discovered by the MAP toolkit during the demo). We added the DNS and DHCP roles and configured a scope of IP addresses that will be used by the various computers in the demo environment. Then we added the host (HV-SRV-01) to the lucerne.msft domain. It may seem somewhat counter-intuitive to add a physical host to a domain where the domain controller is running in a virtual machine, but it’s required because we want to be able to add HV-SRV-01 as a host to System Center Virtual Machine Manager on VM-VMM-01, and it’s a lot easier to do that if the host is in the same domain as the VMM server (you can add a non-domain joined host to VMM, but it requires installing and configuring the VMM agent on the host – which would affect the portability of the demo environment). In terms of running the demo, you can log onto the host as a local user that has permissions to start the virtual machines – so domain membership of the host had no real effect other than to make the laptop’s boot process a little slower as the host looks for a domain controller that isn’t running (because it’s a virtual machine defined on the host itself!)

The installation of VM-VMM-01 was the biggest task in creating the demo environment. We created the virtual machine for it with Hyper-V Manager, installed Windows Server 2008 R2, applied updates to SP1, and added it to the lucerne.msft domain. We enabled the Application Server role and ensured that Windows Authentication was enabled in IIS.  Then we installed SQL Server 2008 R2 Developer Edition, Microsoft Office 2010 Standard (only Word and Excel are required) and the MAP toolkit. Next we installed System Center Virtual Machine Manager 2008 R2 SP1 and attempted to add HV-SRV-01 as a host. At this point, we hit our first major blocker. When attempting to add the host, we encountered the following error:

Error (2923)
A malformed response was received while trying to contact the VMM agent on hv-srv-01.lucerne.msft. (Unspecified error (0x80004005))

After some troubleshooting, we figured out the cause of the problem. The Dell laptop we used came with an Intel wireless network card, and the driver installation included some WMI-based management and monitoring software. For some reason, this was causing WMI to return spurious data to the VMM server when installing and connecting to the VMM agent on the host. We removed the management software (but not the network card driver), and were able to successfully add our physical laptop as a Hyper-V host to SC VMM on our virtual machine.

Now that we had a working SC VMM installation, we set about installing the VMM Self-Service Portal 2.0 solution accelerator, and this is where we hit our second blocker. The installer for the self-service portal checks for a pre-requisite of SQL Server 2008 Enterprise edition. We had installed Developer edition, which while being functional identical to Enterprise edition did not meet the prerequisite requirements enforced by the portal setup program. So a little back-tracking later (thank heavens for Hyper-V snapshots) we had replaced SQL Server Developer edition with Enterprise edition and could continue. The self-service portal has two main components, each installed using a separate setup program. The self-service portal itself is basically a database back-end with an ASP.NET Web application through which users can request infrastructure resources. However, there is also an optional reporting dashboard for the self-service portal that requires a SharePoint site. To satisfy this requirement, we installed SharePoint Foundation 2010 as a front-end Web, and then configured a site to use the existing SQL Server 2008 R2 Enterprise instance on the virtual machine as a back-end database. Then we were able to install the VMM self-service portal reporting dashboard.

The next task was to create a virtual machine template for the “Point of Sale” server used in the demo. To accomplish this, we created a virtual machine on which we installed Windows Server 2008 R2 (with the Application Server role enabled) and deployed our demo application (a simple ASP.NET Web application that provides a point-of-sale system for selling books) in an IIS virtual directory. We then used SQL Server 2008 R2s “SQL Sysprep” feature to install a prepared instance of SQL Server on the virtual machine – this is in effect a partial installation of SQL Server with no server identity dependencies (so the server can be renamed, and the SQL Server installation completed at a later time). To automate the completion of the SQL Server installation, we ran the setup wizard as if completing the prepared instance manually, and then cancelled on the final screen and copied the .ini file that had been created by the setup program. We could then create a batch file that runs the SQL Server setup program (from a locally stored copy of the installation media) and specifies the .ini file to perform a silent, automated installation. We also created a simple Transact-SQL script to create the point-of-sale application database in the local SQL Server instance after its installation has completed and called that from the same batch file.

With everything now in place, we used System Center Virtual Machine Manager to create a virtual machine template from the virtual machine we had built. Essentially, this creates a Sysprepped image of the virtual machine that can be customized the first time it is started. Then, we configured the template by specifying the virtual hardware settings and default computer name, license key, domain membership etc. to be used to customize the image when started. We also specified that the batch file we had included in the image should be run automatically the first time the computer was started and logged into – completing the installation of SQL Server and the application database.

We then used the template as the basis for two virtual machines – VM-POS-NY and VM-POS-TechEd. These were created in System Center VMM from the template, and serve as pre-existing point-of-sale servers in the demo.

Finally, we created three simple virtual machines that included Windows Server 2008 R2 with Internet Explorer 9. We added the Desktop Experience feature, enabled the Themes service, and customized the desktop to use the Aero theme (so the user interface looks like Windows Vista/7 rather than the traditional Windows Server UI). We named these computers VM-WKSTN-Admin, VM-WKSTN-Operator, and VM-WKSTN-User, an they’re used in the demo to represent the workstations of the various user roles involved in a self-service provisioning scenario.

At TechEd, Darmadi presented the demo on a Dell Precision laptop with 16GB of RAM. To move the completed demo environment to this laptop, we simply:

1. Installed Windows Server 2008 R2 SP1 and named the computer HV-SRV-01
2. Copied all of the virtual machines to C:\VMs on the new laptop
3. Started VM-DC-01, removed the old HV-SRV-01 from the domain and added the new HV-SRV-01 to the domain
4. Started VM-VMM-01 and removed the old HV-SRV-01 host, and then added the new HV-SRV-01 host

I’ve missed out a few details, and I may return to the subject of using SQL Server prepared instances in VM templates in a future post; but for now, hopefully this has given you an insight into the single-computer private cloud environment we built for the TechEd demo.

Read More >>

Creating Multi-Sheet Workbooks with SQL Server 2008 R2 Reporting Services

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 to 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.

However, I recently encountered a situation where Content Master hosts a Luminosity 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.

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.

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.

You can download a copy of this workbook from here.

Each worksheet in the workbook lists customers with a last name that begins with the letter on the corresponding worksheet tab, as shown here:

To create this report, I used the AdventureWorks2008R2 sample database (which you can download from here) and the following Transact-SQL query:

SELECT Title, FirstName, LastName, AddressLine1, City, StateProvinceName, PostalCode, CountryRegionName
FROM Sales.vIndividualCustomer

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 LastName field in upper-case):

=ucase(left(Fields!LastName.Value, 1))

I also added an image and a textbox to the report, and placed them above the tablix data region as shown here:

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 InitialPageName property to Customer Addresses, as shown here:

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 InitialPageName property wasn’t set, the worksheet tab would show the the report name).

Next, I created a page break at the start of the tablix as shown here:

This causes the data in the table to be displayed on a new page, effectively defining the “cover page” as “everything before this”.

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 LastName field, and the page name for this page will be the grouping character.

You can download the complete solution from here. 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).

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”.

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.

Technorati Tags: SQL Server 2008 R2,Reporting Services,Excel

Read More >>

Data-Tier Applications in SQL Server 2008 R2

In a previous post, 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.

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.

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.

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.

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.

This opens the following wizard screen:

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.

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.

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 geometry or geography. 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.

Finally, the wizard builds the package for the DAC. The package itself is a single file with the extension .dacpac, as shown here:

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:

To deploy the data-tier application defined in the DAC, right-click the server you want to deploy it to and click Deploy Data-tier Application:

This starts another wizard, as shown here:

The first step is to select the DAC package file you want to deploy:

Then you can change the database name and file locations if desired.

The wizard summarizes the settings, …

 

…,and then deploys the data-tier application to the server.

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 MyAppLogin login has been recreated on the target server along with the database.

This ability to treat the entire data-tier as a single, encapsulated package should simplify database application deployment and management significantly.

Read More >>

Multi-Server Management with SQL Server 2008 R2

A key challenge for many medium to large businesses is the management of multiple database server instances across the organization. SQL Server has always had a pretty good story with regards to multi-server management through automated multi-server jobs, event forwarding, and the ability to manage multiple instances from a single administrative console. In SQL Server 2008, Microsoft introduced a new solution called Data Collector for gathering key server performance data and centralizing it in a management data warehouse; and in SQL Server 2008 R2, this technology underpins a new way to proactively manage server resources across the enterprise.

With SQL Server 2008 R2, database administrators can define a central utility control point (UCP) and then enroll SQL Server instances from across the organization to create a single, central dashboard view of server resource utilization based on policy settings that determine whether a particular resource is being over, under, or well utilized. So for example, a database administrator in an organization with multiple database servers can see at a glance whether or not overall storage and CPU resources across the entire organization are being utilized appropriately, and can drill-down into specific SQL Server instances where over or under utilization is occurring to identify where more resources are required (or where there is spare capacity).

Sounds pretty powerful, right? So you’d expect it to be complicated to set up and configure. However, as I hope to show in this article, it’s actually pretty straightforward. In SQL Server Management Studio, there’s a new tab named Utility Explorer, and a Getting Started window that includes shortcuts to wizards that you can use to set up a UCP and enroll additional server instances.

Clicking the Create a Utility Control Point link starts the following wizard:

The first step is to specify the SQL Server instance that you want to designate as a UCP. This server instance will host the central system management data warehouse where the resource utilization and health data will be stored.

Next you need to specify the account that will be used to run the data collection process. This must be a domain account rather than a built-in system account (you can specify the account that the SQL Server Agent runs as, but again this must be a domain account).

Now the wizard runs a number of verification checks as shown here:

Assuming all of the verification checks succeed, you’re now ready to create the UCP.

The wizard finally performs the tasks that are required to set up the UCP and create the management data warehouse.

After you’ve created the UCP, you can view the Utility Control Content window to see the overall health of all enrolled SQL Server instances. At this point, the only enrolled instance is the UCP instance itself, and unless you’ve waited for a considerable amount of time, there will be no data available. However, you can at least see the dashboard view and note that it shows the resource utilization levels for all managed instances and data-tier applications (another new concept in SQL Server 2008 R2 – think of them as the unit of deployment for a database application, including the database itself plus any server-level resources, such as logins, that it depends on).

To enroll a SQL Server instance, you can go back to the Getting Started window and click Enroll Instances of SQL Server with a UCP. This starts the following wizard:

As before, the first step is the specify the instance you want to enroll. I’ve enrolled a named instance on the same physical server (actually, it’s a virtual server but that’s not really important!), but you can of course enroll any instance of SQL Server 2008 R2 in your organization (It’s quite likely that other versions of SQL Server will be supported in the final release, but in the November CTP only SQL Server 2008 R2 is supported).

As before, the wizard performs a number of validation checks.

Then you’re ready to enroll the instance.

The wizard performs the necessary tasks, including setting up the collection set on the target instance.

When you’ve enrolled all of the instances you want to manage, you can view the overall database server resource health from a single dashboard.

In this case, I have enrolled two server instances (the UCP itself plus one other instance) and I’ve deliberately filled a test database. Additionally, the virtual machine on which I installed these instances has a small amount of available disk space. As a result, you can see that there is some over-utilization of database files and storage volumes in my “datacenter”. To troubleshoot this overutilization, and find the source of the problem, I can click the Managed Instances node in the Utility Explorer window and select any instances that show over (or under) utilization to get a more detailed view.

Of course, your definition of “over” or “under” utilized might differ from mine (or Microsoft’s!), you can configure the thresholds for the policies that are used to monitor resource utilization , along with how often the data is sampled and how many policy violations must occur in a specified period before the resource is reported as over/under utilized.

These policy settings are global, and therefore apply to all managed instances. You can set individual policy settings to override the global polices for specific instances, though that does add to the administrative workload and should probably be considered the exception rather than the rule.

My experiment with utility control point-based multi-server management was conducted with the November community technology preview (CTP), and I did encounter the odd problem with collector sets failing to upload data. However, assuming these kinks are ironed out in the final release (or were caused by some basic configuration error of my own!), this looks to be the natural evolution of the data collector that was introduced in SQL Server 2008, and should ease the administrative workload for many database administrators.

del.icio.us Tags: SQL Server 2008,SQL Server 2008 R2,Utility Control Point

Read More >>

Further Adventures in Spatial Data with SQL Server 2008 R2

Wow! Doesn’t time fly? In November last year I posted the first in a series of blog articles about spatial data in SQL Server 2008. Now here we are over a year later, and I’m working with the November CTP of SQL Server 2008 R2. R2 brings a wealth of enhancements and new features – particularly in the areas of multi-server manageability, data warehouse scalability, and self-service business intelligence. Among the new features that aren’t perhaps getting as much of the spotlight as they deserve, is the newly added support for including maps containing spatial data in SQL Server Reporting Services reports. This enables organizations that have taken advantage of the spatial data support in SQL Server 2008 to visualize that data in reports.

So, let’s take a look at a simple example of how you might create a report that includes spatial data in a map. I’ll base this example on the same Beanie Tracker application I created in the previous examples. To refresh your memory, this application tracks the voyages of a small stuffed bear named Beanie by storing photographs and geo-location data in a SQL Server 2008 database. You can download the script and supporting files you need to create and populate the database from here. The database includes the following two tables:

-- Create a table for photo records
CREATE TABLE Photos
([PhotoID] int IDENTITY PRIMARY KEY,
[Description] nvarchar(200),
[Photo] varbinary(max),
[Location] geography)
GO

-- Create a table to hold country data
CREATE TABLE Countries
(CountryID INT IDENTITY PRIMARY KEY,
CountryName nvarchar(255),
CountryShape geography)
GO

The data in the Photos table includes a Location field that stores the lat/long position where the photograph was taken as a geography point. The Countries table includes a CountryShape field that stores the outline of each country as a geography polygon. This enables me to use the following Transact-SQL query to retrieve the name, country shape, and number of times Beanie has had his photograph taken in each country:

SELECT CountryName,
CountryShape,
(SELECT COUNT(*)
FROM Photos p
WHERE (Location.STIntersects(c.CountryShape) = 1))
AS Visits
FROM Countries c

With the sample data in the database, this query produces the following results:

CountryName	CountryShape	Visits
France	0xE6100000 … (geography data in binary format)	1
Egypt	0xE6100000 … (geography data in binary format)	2
Kenya	0xE6100000 … (geography data in binary format)	1
Italy	0xE6100000 … (geography data in binary format)	2
United States of America	0xE6100000 … (geography data in binary format)	7
United Kingdom	0xE6100000 … (geography data in binary format)	2

To display the results of this query graphically on a map, you can use SQL Server Business intelligence Development Studio or the new Report Builder 3.0 application that ships with SQL Server 2008 R2 Reporting Services. I’ll use Report Builder 3.0, which you can install by using Internet Explorer to browse to the Report Manager interface for the SQL Server 2008 R2 Reporting Services instance where you want to create the report (typically http://<servername>/reports) and clicking the Report Builder button.

When you first start Report Builder 3.0, the new report or dataset page is displayed as shown below (if not, you can start it by clicking New on the Report Builder’s main menu).

This page includes an option for the Map Wizard, which provides an easy way to create a report that includes geographic data. To start the wizard, select the Map Wizard option and click Create. This opens the following page:

SQL Server 2008 R2 Reporting Services comes with a pre-populated gallery of maps that you can use in your reports. Alternatively, you can import an Environmental Systems Research Institute (ESRI) shapefile, or you can so what I’m doing and use a query that returns spatial data from a SQL Server 2008 database.

After selecting SQL Server spatial query and clicking Next, you can choose an existing dataset or select the option to create a new one. Since I don’t have an existing dataset, I’ll select the option to Add a new dataset with SQL Server spatial data and click Next, and then create a new data source as shown here:

On the next screen of the wizard, you can choose an existing table, view, or stored procedure as the source of your data, or you can click Edit as Text to enter your own Transact-SQL query as I’ve done here:

The next page enables you to select the spatial data field that you want to display, and provides a preview of the resulting map that will be included in the report.

Note that you can choose to embed the spatial data in the report, which increases the report size but ensures that the spatial map data is always available in the report. You can also add a Bing Maps layer, which enables you to “superimpose” your spatial and analytical data over Bing Maps tiles as shown here:

Next you can choose the type of map visualization you want to display. These include:

• Basic Map: A simple visual map that shows geographical areas, lines, and points.
• Color Analytical Map: a map in which different colors are used to indicate analytical data values (for example, you could use a color range to show sales by region in which more intense colors indicate higher sales)
• Bubble Map: A map in which the center point of each geographic object is shown as a bubble, the size or color of which indicates an analytical value.

To show the number of times Beanie has visited a country, I’m using a bubble map. Since the bubbles must be based on a data value, I must now choose the dataset that contains the values that determine the size of the bubbles.

Having chosen the dataset, I now get a confirm or chance to change the default matches that the wizard has detected.

Finally, you can choose a visual theme for the map and specify which analytical fields determine bubble size and the fill colors used for the spatial objects.

Clicking Finish, generates the report, which you can make further changes to with Report Builder.

Selecting the map reveals a floating window that you can use to edit the map layers or move the area of the map that is visible in the map viewport (the rectangle in which the map is displayed).

You can make changes to the way the map and its analytical data are displayed by selecting the various options on the layer menus. For example, you can:

• Click Polygon Properties to specify a data value to be displayed as a tooltip for the spatial shapes on the map.
• Click Polygon Color Rule to change the rule used to determine the fill colors of the spatial shapes on the map.
• Click Center Point Properties to add labels to each center point “bubble” on the map.
• Click Center Point Color Rule to change the rule used to determine the color of the bubbles, including the scale of colors to use and how the values are distributed within that scale.
• Click Center Point Size Rule to change the rule used to determine the size of the bubbles, including the scale of sizes to use and how the values are distributed within that scale.
• Click Center Point Marker Type Rule to change the rule used to determine the shape or image of the bubbles, including a range of shapes or images to use and how the values are matched to shapes or images in that range.

At any time, you can preview the report in Report builder by clicking Run. Here’s how my report looks when previewed.

When you’re ready to publish the report to the report server, click Save on the main menu, and then click Recent Sites and Servers in the Save As Report dialog box to save the report to an appropriate folder on the report server.

After the report has been published, users can view it in their Web browser through the Report manager interface. here’s my published report:

I’ve only scratched the surface of what’s possible with the map visualization feature in SQL Server 2008 R2 Reporting Services. When combined with the spatial data support in SQL Server 2008 it really does provide a powerful way to deliver geographical analytics to business users, and hopefully you’ve seen from this article that it’s pretty easy to get up and running with spatial reporting.

del.icio.us Tags: SQL Server,SQL Server 2008 R2,Spatial Data,Reporting Services

Read More >>