Sunday, 27 December 2009

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.

Saturday, 26 December 2009

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
[Description] nvarchar(200),
[Photo] varbinary(max),
[Location] geography)

-- Create a table to hold country data
CountryName nvarchar(255),
CountryShape geography)

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

Monday, 12 January 2009

Adventures in Filestream Data

Regular readers of this blog may have seen my previous article on using SQL Server 2008's new spatial data support to create the Beanie Tracker Web application. The application tracks the travels of a small white bear named Beanie, using Virtual Earth to show photos of him at various locations around the world. The idea for creating the application came from a whitepaper and a hands-on lab I created for the SQL Server marketing team at Microsoft.

As part of the same project for the SQL Server marketing team, I wrote a whitepaper on managing unstructured data in SQL Server 2008, which includes a short description of another new feature: FILESTREAM data. The idea behind FILESTREAM is to help solve a common problem in database solutions that need to store binary large object (BLOB) data such as images or multimedia objects. In the past, you've basically had two choices for storing BLOBs; you can store them in the database in varbinary columns, or you can store them on the file system and include a file path reference in the database so that applications can find them. The debate about which of these approaches is the best one is well documented, and usually boils down to a tradeoff between the performance advantages and data access flexibility of storing the data on the file system versus the manageability benefits of storing the data in the database. FILESTREAM support in SQL Server 2008 is designed to offer a best of both worlds approach in which the data is stored physically on the file system, but managed as if it were stored in the database. Additionally, a dual programming model enables the data to be accessed through Transact-SQL statements or through the Win32 file streaming API. FILESTREAM is supported in all editions of SQL Server 2008 (other than Compact Edition) - including SQL Server Express; and it's worth noting that because the data is physically stored in the file system, your binary data is not counted when restricting the size of a SQL Server Express database to 4 GB. To try all this for myself, I decided to modify the Beanie Tracker application to store the photos of Beanie as FILESTREAM data. This proved remarkably simple.

The first step was to configure FILESTREAM support in the SQL Server instance. To do this, you view the properties of the SQL Server instance in SQL Server Configuration Manager and set the appropriate values on the FILESTREAM tab as shown here:


Next you need to use sp_configure to enable FILESTREAM as shown here:

EXEC sp_configure filestream_access_level, 2

Now that FILESTREAM is enabled, you can create a database that can store FILESTREAM data:

ON PRIMARY(NAME=BeanieData, FILENAME='c:\data\BeanieData.mdf'),
LOG ON(NAME=BeanieLog, FILENAME='c:\data\BeanieLog.ldf')

Note the inclusion of a filegroup that contains FILESTREAM data. The FILENAME parameter for this filegroup is the folder on the file system where the FILESTREAM data will be stored. If you take a look at this location in Windows Explorer, you'll see the files and folders that SQL Server uses to store the data as shown here (you need to give yourself permission to view the folder):


Now you can create a table with a varbinary(max) column for your FILESTREAM data. By specifying the FILESTREAM attribute for the column, you ensure that the data is stored in the FILESTREAM filegroup (and therefore on the file system) rather than in the database data pages:

([PhotoID] uniqueidentifier ROWGUIDCOL NOT NULL PRIMARY KEY,
[Description] nvarchar(200),
[Photo] varbinary(max) FILESTREAM NULL,
[Location] geography)

Note that you also need to include a ROWGUID column when storing FILESTREAM data. In the previous version of the Beanie Tracker database, the PhotoID column was an integer. I changed it to a uniqueidentifier column in this version.

After the table has been created, you can treat the FILESTREAM column just as if it were an ordinary varbinary column. For example, here's a Transact-SQL statement to insert a photo from an existing .jpg file:

([PhotoID], [Description], Photo, Location)
(Newid(),'Beanie in Paris',
geography::STPointFromText('POINT (2.328 48.8661)', 4326))

To access the data from a client application, you can use the Win32 streaming API to read the data from the file system, or you can use Transact-SQL. The client application does not need to do anything special for FILESTREAM data. Here's the Visual Basic .NET code in the Beanie Tracker Web application to retrieve the photo for a specific location:

Dim PhotoID As String = Request.QueryString("PhotoID")
'Connect to the database and bring back the image contents & MIME type for the specified picture
Using myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("BeanieTracker").ConnectionString)
Const SQL As String = "SELECT [Photo] FROM [Photos] WHERE [PhotoID] = @PhotoID"
Dim myCommand As New SqlCommand(SQL, myConnection)
myCommand.Parameters.AddWithValue("@PhotoID", PhotoID)
Dim myReader As SqlDataReader = myCommand.ExecuteReader
If myReader.Read Then
  Response.ContentType = "image/jpeg"
End If
End Using

The page containing this code is requested by the "tootip" in the Virtual Earth control when the user hovers the mouse over a pin on the map, so the appropriate image of Beanie is displayed in as shown here:







So all in all, it's pretty easy to use FILESTREAM data in a SQL Server database. In this example, I get the advantages of storing my images on the file system, freeing up valuable space in a SQL Server Express database and enabling access via Transact-SQL or the Win32 streaming API. However, I also get the manageability advantages of storing the data in a the database, so it's included when I perform a backup for example.