Reverse Engineering SQL Server DBs with Visio Pro DBRE

The news is out: due to popular demand DBRE (Database Reverse Engineering) is [almost] back in Visio. It is currently out in preview, as announced in MS Power User , for Visio Pro for Office365 users at least. It is, however, only providing support for MS SQL Server and Oracle Server, although there is an ODBC Generic Driver too.

My test machine is a Surface Pro 1, which does not have SQL Server installed, so my instinct was to test DBRE with a SQL Azure database that I created for my eXplore Visio app  ( see Windows Store ).

This slideshow requires JavaScript.

Try as I might, I could not get the full database structure with the Microsoft SQL Server driver that I had installed by default. With a little help from Microsoft, I discovered that I needed to download the Microsoft ODBC Driver 13 for SQL Server from Microsoft ( see Download Driver). However, I still needed to set the default database for Visio to use as Microsoft SQL Server, not ODBC Generic Driver. In retrospect, it makes sense, but it did confuse me for a while.

Once I understood that, I was able to change my default database to the desired visioFeatures one, and to reverse engineer my SQL Azure database tables, views and stored procedures.

This slideshow requires JavaScript.

So, I encourage all who are interested , and have Visio Pro for Office365, to test the preview DBRE add-in.

Post Script

I will declare a little disappointment with the treatment of database reverse and forward engineering in Visio over the years. I was a fully paid up user of the original InfoModeler application before Visio Corporation bought it in the late 90’s. I used InfoModeler, before I became a Visio user/developer even, to model databases in FORML (Formal Object Role Modelling Language) and to reverse engineer Sybase, Oracle, SQL Sevrer and MS Access databases. These schema diagrams were extremely useful for explain the database structure and relationships to others. I also used the schema reporting and forward engineering into DDL scripts, and it offered upgrading from MS Access to SQL Server way before Microsoft did. When Microsoft bought Visio Corporation, I believe that they decided that the legacy InfoModeler code within the then Visio Enterprise should be handled by the SQL Server team. The code base must have been completely unfamiliar, and the whole solution had been written in isolation, making its integration with other core Visio functionality very difficult. Well, the first features to disappear were the reporting and forward engineering, but the reverse engineering hung on for a while longer. Eventually, it too was cut, leaving some Visio users with no option but to remain on earlier versions of Visio if they wanted to keep that feature. There are some similarities to the network discovery and diagramming solution in earlier versions of Visio Enterprise.

Many Visio users, including myself, complained about the loss of DBRE, and it seems our voices were heard, because it is back, but in a limited form. Personally, I think it is a pity that it supports less databases than before, and I know that that there are many MS Access users who would love to be able to document their databases [sic] in a better manner than is available in Access itself. Also, it is a shame that the DBRE solution does not utilise the newer database schema diagrams introduced in Visio 2013 ( see  UML and Database Diagrams in the New Visio). However, it does mean that old articles on using DBRE are, for the most part, valid (see TechNet ) and even Terry Halpin’s book ( see Database Modeling with Microsoft Visio for Enterprise Architects  ) has some new life.

I looked at using the new Crow’s Foot Diagram template in an earlier article ( see Creating a Schema from Visio External Data record sets), and I have nearly finished making this into a VSTO add-in…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

davecra.wordpress.com/

Microsoft Office Development, and more...

PowerShell.Amsterdam

Automate, Accelerate, Accurate

johnvisiomvp

Life with Visio and other Microsoft Toys!

Title (Required)

Windows Server Essentials Tips & Tricks

Nilsandrey's Weblog

Just another WordPress.com weblog

Things that Should be Easy

Every so often (too often in the IT industry) I encounter things that should have been very easy to do but turned out to be far too complicated. My favorite topics include SharePoint, .Net development, and software architecture, especially distributed systems.

Visio Guy

Shapes, Stencils, Drawings Templates, Tutorials, Tips & Developer Info for Microsoft Visio

Hannes's Virtual Earth

Tips & Tricks around Mapping and Cloud Computing

Pluralsight blog

be smart, be clear, be visual ...

%d bloggers like this: