Reviewing SQL Server Modelling CTP (Nov 09)


You may have heard of the upcoming SQL Server Modelling suite formerly known as ‘Oslo’.  The suite, know known as the SQL Server Modelling CTP, features the ‘m’ modelling language and a graphical workbench known as (or codenamed) ‘Quadrant’.  I’ve been meaning to look into codename ‘Oslo’ for a while now, and I’ve finally had a chance to review how it works this weekend.

Designing for SQL Server has always been a fairly subjective art form.  Microsoft has not exactly been kind to data architects, continually changing its position on the how and why of data modelling tools.  Once upon a time, a database architect could rely on tools like Visio (for Enterprise Architects) for forward and reverse engineering database schema.

Microsoft is endeavouring to build a more comprehensive set of tools for what we term “DSL” languages or domain specific languages.  This essentially allows data architects to focus on business specific (or domain specific) architecture and to abstract away from the physical implementation of persisted storage in large enterprise designs.

What is SQL Server Modelling?

Well, let’s see what the official MSDN article mentions, below.

[1] The SQL Server Modelling CTP includes:

  • A database designed for models (built on SQL Server 2008 and called SQL Server Modeling Services) that is highly optimized to provide your data schemas and instances with system-provided best practices for scalability, availability, security, versioning, change tracking, and localization.
  • A visual tool (Microsoft code name “Quadrant”) for browsing any SQL Server database without writing a line of code. Using “Quadrant” you can easily browse any set of related tables by dragging and dropping icons that represent the related data. You can also filter data by writing “M” queries (you do not have to know Transact-SQL), and you can customize the viewer to display data how you want to see it, rather than seeing the physical database implementation.
  • A language (Microsoft code name “M”) with features that enable you to model (or describe) your data structures, data instances, and data environment (such as storage, security, and versioning) in an interoperable way. It also offers simple yet powerful services to create new languages or transformations that are even more specific to the critical needs of your domain. This allows .NET Framework runtimes and applications to execute more of the described intent of the developer or architect while removing much of the coding and recoding necessary to enable it.

At this point I haven’t had much to do with the ‘M’ language, aside from briefly reviewing the demonstration project which describes some aspects of the Windows Management Instrumentation model [2] which you can download and use with the latest CTP.  From what I understand, the ‘M’ language is designed to allow architects to query data without needing to know Transact-SQL.

The ‘M’ Language

The ‘M’ language also allows for design of models outside the traditional tools, in domain-specific environments. 
From the official website:

[1] The “M” Language and Features

Use the "M" language and its features to define custom languages, schema for data (data models), and data values. Doing so brings the meaning of the code you write closer to your own domain of experience. With more of your own intent encoded in models, it becomes easier for you—and other developers working with your domain—to write code and applications faster, especially by sharing models and reusing them.

  • You can use the schema (or model) definition feature of “M” to design interoperable models—data schemas—for the data in your particular field of interest (called a domain). Using the schema definition feature makes statements about the structure, constraints, and relationships, but says nothing about how the data is stored or accessed, or about what specific values an instance might contain. By default, “M” models are stored in a SQL Server 2008 database, but you are free to modify the output to any storage or access format. If you are familiar with XML, the schema definition feature is like XSD.
  • You can also use the value definition feature of “M” to create model (that is, schema) instances of your data structures. The value features make statements about the data values that are to populate instances of the models you or others have created, but again, interoperability is maintained. While the default compilation output can be inserted into SQL Server 2008 or into a Modeling Services database, you can transform the output into any format you need to support your scenario. If you are familiar with XML, defining values is like creating an XML file that conforms to an XSD file.
  • You use the language definition feature to create new, custom languages (sometimes called domain-specific languages, or DSLs) for your own domain of expertise that you control, without having to build a parser, lexer, or any other supporting infrastructure. Building a custom language makes it vastly simpler for you or your customers to express their specific desires, dramatically lowering the barrier to using your particular domains and applications built on them.
  • By default, the output of “M” is either Transact-SQL that can be inserted in any SQL Server 2008 database, or a more customized version of Transact-SQL that uses the advanced features of Modeling Services, such as security, versioning, change tracking, and so on. However, “M” languages are extensible and can output any specific format you care to implement. Model-driven applications must be interoperable, and where they interoperate depends upon your circumstances, requirements, and resources.

There’s also a utility called ‘Intellipad’ which appears to be an editor for the ‘M’ language and can be used if Visual Studio is unavailable, and supports solutions and projects.

image 
Intellipad showing the primer/help

Let’s take a look at the various parts of the Modelling Toolkit –

The Model Database (Repository)

The database sits in a local instance, so you’ll need access to an instance of SQL server.  Since SQL Express [3] is free, you shouldn’t really have any trouble with installing a local copy.  Once installed, you can install the CTP and take a look at the database that SQL Modelling uses.

image image
The Repository Database                            The WMI Model Sample

As you can see, the Modelling Service uses a database and many tables to persist the working models.  We’ll come to see how this is useful shortly, when we take a look at possibly the most useful part of the suite – codename ‘Quadrant’.

image

Let’s take a look..

image
The ‘Quadrant’ Workbench

Above is the main environment for the ‘Quadrant’ application.  I’ve got four windows open, and they constitute some of the key areas you’d be using when modelling.  On the top left is a view of a database I have attached in my local instance called ‘Vinyl’.  Below in the center is a view of some of the data from the “Album” table in that database.

In the top middle window is a T-SQL command window where ad-hoc queries can be performed.  Lastly, the top right window contains the default view of ‘Quadrant’’s own view – you can see all the namespaces and, if you have installed it – also the WMI modelling.

For more on ‘Quadrant’ take a read of this overview: http://msdn.microsoft.com/en-us/library/dd857506%28v=VS.85%29.aspx

Anyhow, this is a brief overview of the main parts – in the next entry we shall take a more proactive look at what we can do to model with a more domain-specific view.

You may download the CTP Here.

[1] http://msdn.microsoft.com/library/dd129514.aspx
[2] http://code.msdn.microsoft.com/SQLModCTPWMI/Release/ProjectReleases.aspx?ReleaseId=3470
[3] http://www.microsoft.com/express/database/


About Rob Sanders

IT Professional and TOGAF 9 certified architect with nearly two decades of industry experience, 18 years in commercial software development and 11 years in IT consulting. Check out the "About Rob" page for more information.

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>