by Bill Wunder

Long ago most of us made the decision to use Visual SourceSafe as the repository for our SQL Server scripts. Truth be known, the decision was more of an adaptation than a choice. The need for versioning and source control was obvious to us all but there was no compelling reason that SourceSafe was the right tool. Instead there was the compelling reality that SourceSafe was already in wide use by other elements of the development staff and it was an easy and pragmatic effort to carve off a VSS project hierarchy for SQL scripts. Without a doubt, SourceSafe is far better than no source control.  

More recently the need for a modeling tool has followed a similar path for many of us. We find ourselves in Microsoft-centric shops where the systems folks are using Visio to document the infrastructure and plan the space, perhaps even the UMLers and web folks are designing with Visio, and in a few cases the project managers and application developers may even be using Visio to create data flow and entity relationship diagrams. At the same time, but on a completely different front, management is reluctant - often justifiably - to put up the time and dollars necessary for an adequate exposure to AllFusion ERwin Data Modeler or the Embarcadero ErStudio development suite for the SQL developers. Most shops have at least experimented with these top drawer tools and more often than not the large outlay of time and money has been wasted as the the tool has fallen into disuse. Instead, the more pragmatic among us might push headlong into an endeavor to use Visio to help us with our database designs, data modeling, and documentation of existing database structures and code for troubleshooting, collaboration, and educational purposes. As with SourceSafe, it might not be best of breed but it is without a doubt far better than no modeling and diagramming tool at all.

There is a version of Visio integrated into the Microsoft Visual Studio .NET 2003: Microsoft Visio for Enterprise Architects, part of Visual Studio .NET Enterprise Architect. This tool offers a fairly robust support for Object Role Modeling (ORM) and is well suited to the conceptual design phase of the software lifecycle. The product most of us are more likely to have available is Microsoft Office Visio Professional 2003. Don't confuse the two. This Office family product is the one we will consider here because it's more likely the one you already have access to in your shop. You can do data modeling with the the Microsoft Office product, but it doesn't make T-SQL scripts that you can easily use to create a skeleton of the DDL on a SQL Server like the Visio for Enterprise Architects ORM product. If you think Enterprise Architect may be for you, you can find lots of info in the MSDN Visual Studio .NET section Object Role Modeling- An Overview. To get an idea of the differences between the Office version of Visio and the Visual Studio version consider this TechNet document that compares Visual Studio Enterprise Architect and Visio 2002 professional. A desktop license for Microsoft Office Visio 2003 Professional is under $500 retail. A single user license of Enterprise Architect retails for around $2500. If someone in your shop is already running Enterprise Architect, maybe you can set at that workstation and play around to see if the added features are worth the investment. For the rest of us, lets consider why the Office Visio Professional product can be a useful tool.

With SQL Server 200 the main option we have for sharing data models with the designers, developers and project folks is the metadata Database Diagram tool in Enterprise Manager. There are at least two problem with this tool that you can overcome by using Visio and at several other features of Visio that are enticing. First, when you change a Database Diagram in Enterprise Manager and save it, it changes the underlying database. That's not such a good way to brainstorm or consider "what if" scenarios in a design meeting. Secondly, if the Database Diagram has more than a few objects, it cannot be easily printed and shared among the attendees of that design meeting. Your choices in Enterprise Manager are to reduce the elements you will print or plan on a little kindergarten quite time with the scotch tape dispenser before the meeting (cutting into the already insufficient time you have available to do real design work of course). If you export the database to Visio you can open it up on the overhead in the meeting or stick it out on the SharePoint server and make all the discussion changes you want. In fact there are plentiful "Notes" input boxes in the Visio diagram where you can easily document the discussion right in the document. Visio 2003 Professional cannot update the underlying SQL Server database. Visio can export the stored procedures and functions along with the tables and views so you can even look at all those non-portable business rules that you've embedded in the database. Now you could do that with Enterprise Manger as well, but again, if you make a change and save it, the underlying database is changed. Printing a Visio diagram offers all the options of the Enterprise Manager like showing or not showing data types or columns or constraints. On top of that, in Visio you have many more options for printing. You can stretch the page size with the drawing controls, use the page setup menu option to force a drawing to scale and fit on a single  page, even use the print dialog to sent the drawing to the plotter if your organization happens to have one so you can print the diagram and stick it on the wall as a single sheet of paper no matter how complex. One last capability that you may find useful is Visio's ability to save drawings as web pages. So if you get lucky and the application finally gets stable, you could even consider putting the diagram up on the intranet or maybe sending it to the customer.

None of the reasons to use Visio are barn burners. Just a lot of small details that can help you develop a better product, make information easier to share, and reduce the time and effort required over Enterprise Managers Diagramming tool to reverse engineer a data model for the next development iteration of collaboration. Not really much I can tell you about how to use the tool because it's simple. From the File menu you create a new database ERD and then from the Database menu open an ODBC connection to the database, pick the items you want in the diagram and you're done.

I'm a little concerned that this might sound like an advertisement, but trust me, I'm only bringing it up because it has helped me. If you are in a small to mid size shop with rapid development cycles and are feeling a frustrated with the "Built-In" SQL Server 2000 tools consider Visio 2003. It's worth a look because it can save you time and energy every time you use it.

Bill