SQL Server Modeling Services with Microsoft Visual Studio 2010 Beta 2

Introduction

More than two years ago Microsoft unveiled "Oslo" a multi-product initiative. In many ways Oslo was no different than other unveilings. It included promises for a more productive development environment, better systems, easier system management, and new features. Among the list of components setting the announcement apart from a typical new release announcement was something called the Repository. The Repository was to be the glue tying a long list of current products together in new and novel ways.

As Oslo unfolded parts of it morphed into what we now know as: AppFabric, Visual Studio 2010, and .NET 4.0. BizTalk Services (the Service Bus) was folded into Microsoft Azure and all that remained of Oslo was the Repository and its accompanying technologies: M (a new language) and Quadrant (a data tool). In November 2009 the remaining Oslo technologies became the "SQL Server Modeling CTP". SQL Server Modeling Services is a core part of the SQL Server Modeling CTP. Using some sample Visual Studio 2010 applications, I'm going to demonstrate how you can leverage SQL Server Modeling Services to gain new insights into your project components.

Overview

From what I've gathered in announcements and demonstrations, SQL Server Modeling Services appears to be what Oslo originally called the Repository. That is, the database at the core. In a prior article I took a look inside the Repository. Read my article, Exploring the Oslo Repository

Much of the laid foundation remains. So I'm focusing on the newer features release to the public over the course of last year. In particular I'm going to look at Quadrant and new components for populating the Modeling Services database.

If software doesn't demonstrate some sort of business value, it better be a game or it will have zero shelf-life. So, using Quadrant and some TSQL queries, I'm going to show how SQL Modeling Services can be leveraged for insight into inter-dependencies between a set of assemblies.

First, I'll explain what the assemblies are and then I'll explore parts of the assemblies metadata mined by the SQL Modeling Services utilities.

Sample Assemblies

Using Visual Studio 2010 beta I built the following assemblies:

• SampleSharedClassLibrary, an assembly with a single class called SampleSharedClass1
• SampleConsoleApplication, a simple Console application that utilizes the SampleSharedClassLibrary
• SampleWPF1, a WPF application that also utilizes SampleSharedClassLibrary.

Aside from invoking functions on the SampleSharedClassLibrary both applications do nothing. The sample code is simply a focal point to illustrate a larger concept. Before, mining data from the code above, the first step is getting the software loaded.

Installation and Feature Overview

SQL Server Modeling CTP requires Visual Studio 2010 Beta 2. You'll find helpful installation instructions for Visual Studio 2010 Beta 2in the release notes.

Unless you have a local SQL Server 2008, you'll need to perform separate actions to finish the install. Also, please be aware that these products are CTPs and Betas and, generally speaking, not something you want to install on a production machine.

SQL Server Modeling Services includes utilities for loading UML and assembly metadata. The UML utility leverages new Architecture features inside of Visual Studio 2010. A review of the UML features is beyond the scope of this article. As I stated earlier, I'm going to demonstrate how SQL Modeling Services can be leveraged to view assembly dependencies. Before I can do that I need to load assembly metadata.

Loading the Repository

Assembly data can be loaded from inside Quadrant a tool I'll explain later or from the LoadAssembly utility. You'll find the LoadAssembly utility in the C:\Program Files\Microsoft Oslo\1.0\bin directory. You need to open the Microsoft SQL Server Modeling CTP Command Prompt to invoke the utilities. Remember to select "RunAs" administrator if you are on Windows 7 or Vista.

Figure 1: CTP Command Prompt

Below are examples of command line invocations from a 64bit machine.

loadassembly  /targetFolder:300 /server:athena\sql2008 "C:\articles and presentations\Oslo - SQL Server Modeling" Services\Source\SampleSharedClassLibrary\SampleSharedClassLibrary\bin\Debug\SampleSharedClassLibrary.dll"

loadassembly  /targetFolder:300 /server:athena\sql2008 "C:\articles and presentations\Oslo - SQL Server Modeling
Services\Source\SampleConsoleApplication\SampleConsoleApplication\bin\Debug\SampleConsoleApplication.exe"

loadassembly  /targetFolder:300 /server:athena\sql2008 "C:\articles and presentations\Oslo - SQL Server Modeling Services\Source\SampleWPF1\SampleWPF1\bin\Debug\SampleWPF1.exe"

Now that the data is loaded, it's time to view it. I'm going to first browse some of the assembly metadata from Quadrant.

Quadrant

As I stated earlier, Quadrant ships with the SQL Server Modeling CTP. When you first open Quadrant you'll see a screen much like the one below.

Figure 2 Quadrant

SQL Modeling Services stores data by folder name. The SQL Modeling Services documentation advises using some sort of version naming scheme for your folders. The SQL Modeling Services foldering mechanism is implemented using SQL Server Views. Quadrant is aware of the underlying database mechanisms, interprets the database layout, and presents an intuitive hierarchal navigation experience, much like what you see below.

Figure 3: Quadrant Navigation

One particular thing you'll notice while exploring the assembly metadata, is that the information appears complete and intuitive in some places, but cryptic and lacking in other areas. As I stated earlier, this is a CTP and therefore just a taste of the coming features. While I found Quadrant useful for cursory looks at the data, I found the underlying SQL Server tables more useful for answering hard questions.

TSQL

Because SQL Server Modeling Services is simply a SQL Server database, a developer is free to use whatever their prefered tool is for viewing database data. In this example I opted for a TSQL query, but could have selected Reporting Services, Excel, or whatever other SQL Server reporting mechanism a developer is comfortable with.

By default LoadAssembly will put the metadata in a database called Repository. When navigating the Repository database you'll notice the following set of tables prefixed with the System_Runtime schema.

Figure 4: Database tables

The database is setup with all the appropriate foreign- keys so, either a developer or a reporting tool will be able to discern relationships between the tables. For this demonstration I put the following code together.

  SELECT meth.Name, called.CallCount
    FROM [System_Runtime].[CalledMethods] called
  INNER JOIN [System_Runtime].[Methods] meth ON Callee = meth.Id and called.Folder = meth.Folder
  WHERE called.Folder = 102
  ORDER BY CallCount DESC

The code will tell me which method in my group of applications and assemblies is called the most. Coupled with other information in the database I could tell whether a method I'm changing is heavily used and by whom.

As you can see above, the underlying database is not as friendly as the Quadrant experience. For example, from within Quadrant, I was navigating a folder named 300, but inside the database the Folder is 102, the underlying folder id value stored in the database. Also notice, using a SQL Server schema naming, the database adopts a namespace convention.

Conclusion

The demonstration was a bit trivial. Any developer could have simply viewed each solution and invoked one of the new Architecture tools to hunt for interdependencies. Indeed, why have SQL Server Modeling Services at all? When you consider, however, that most development projects will have hundreds of components rather than three simple components and that I didn't have to access source code to discover interdependencies, the business value of SQL Server Modeling Service becomes self evident.

If SQL Modeling Services fulfills the promise of providing the glue for other platforms like Team Foundation Server and Operations Manager, the potential exists to link a component's release metadata to metadata on how the component is functioning in the field from a single place using a simple reporting tool.

Resources

Microsoft Unveils Vision and Road Map to Simplify SOA, Bridge Software Plus Services, and Take Composite Applications Mainstream
On Oslo
SQL Server Modeling Services CTP
SQL Server Modeling Services Documentation
PDC 2009 Sessions