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

More by Author

Must Read