WEBINAR: On-demand webcast
How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >
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.
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.
Using Visual Studio 2010 beta I built the following assemblies:
SampleSharedClassLibrary, an assembly with a single class called
SampleConsoleApplication, a simple Console application that utilizes the
SampleWPF1, a WPF application that also utilizes
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
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
utility. You'll find 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.
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.
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.
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.
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.
Microsoft Unveils Vision and Road Map to Simplify SOA, Bridge Software Plus Services, and Take Composite Applications Mainstream
SQL Server Modeling Services CTP
SQL Server Modeling Services Documentation
PDC 2009 Sessions