A few months ago, Microsoft announced that SQL Server Management Studio (SSMS) will no longer include the visual Database Diagrams feature from v18.0 onward.
[Edit: Microsoft reversed this decision with SSMS 18.1, and the designer is back. Apparently a lot of people missed it. Keep reading for the original post.]
When releasing a new version of a product, Microsoft has the luxury of referencing usage statistics, and obviously this is an underused feature of SSMS.
I was a little disappointed at the news, because I remember using the Designer extensively on a no-budget project some years ago.
These days I still design new databases from scratch with pen and paper (or iPad and Apple Pencil), where the entity relationship diagram (ERD) is rudimentary and crows’ feet relationships are badly-scrawled. But it got me wondering which database modelling tools are on the market today (commercial and free).
My ideal tool should be able to design a new database from scratch and generate creation scripts in T-SQL without failing over common issues like referential integrity and dependencies. More importantly though, it should be able to reverse-engineer a database (like Microsoft Visio used to be able to). This is extremely useful for consulting engagements when I need to get a picture in my head of the database I’m looking at. This was the one place I’ve used the Database Designer in SSMS more than I had initially remembered.
One person I asked suggested the online-only but very capable SQL Database Modeler. The free version is able to generate scripts to generate two tables at a time, but to get the full features you need to pay a monthly subscription. I don’t think the pricing is unreasonable, but I personally wanted a tool that I can run locally without going to a website to do it.
Then I remembered that dbForge existed, by Devart. I had used one of their tools many years ago at a customer site and thought it was pretty good, but at the time I had thought it was expensive. Devart has been around for over 20 years, making a number of tools for database management. After some investigation (and installing the trial edition), I discovered that they offer free licences to Microsoft MVPs in return for a review. I asked them if I could get a copy of the Enterprise edition of dbForge Studio for SQL Server, and here we are.
Full disclosure: I did not pay for this product license, and this review is not an endorsement of their product. I would have made the same comments using the trial version.
The first thing I noticed is that the tool is “heavy” in terms of UI, download size, performance, and that sort of thing. It took seven seconds for the splash screen to show up, and another ten seconds to load the tool.
Before I get lambasted for that, I need to clarify that dbForge Studio is meant to replace SSMS entirely from what I can tell, and that’s something I wasn’t really prepared for. SSMS has a lot of quirks, and it isn’t always the most reliable tool when a lot of windows are open, but I know it well and it allows plugins and extensions like Redgate Software’s SQL Toolbelt (for which I have my own licence). I like the idea of integrating with SSMS, not replacing it. Learning a new tool to do my job requires a lot of effort.
The point is, SSMS takes a lot longer than 17 seconds to load on the same computer, because of all the extensions. I don’t close it if I can help it.
With that said, the main feature I was looking at is the Database Designer. I decided to reverse-engineer the most obnoxious database I could find. Rob Volk (blog | Twitter) has a reputation for doing bad things to databases and I considered using one of his to start with, but I didn’t have to go to that effort. A recent customer engagement I worked on had a SQL Server database with over 400 tables. That felt sufficient to test the abilities of the dbForge Designer.
The first thing I had to find was the design feature. Fortunately, the “Database Design” tab was clearly visible near the top of the screen, so I clicked and immediately found what I wanted: New Database Diagram.
So far, so good. When I clicked, it opened a new diagram with the very helpful message “You can drag database objects from Database Explorer here”, and Database Explorer was underlined and blue. I clicked on that and realized that there was a Database Explorer tool window on the left of the screen which was now active, and already connected to a SQL Server instance I had connected before.
I expanded the database, selected the tables I wanted to add to the diagram, and dragged them to the diagram window.
For the purposes of this blog post, screenshots refer to the WideWorldImporters database, not an actual customer database.
Anyone who has worked with third-normal form and referential integrity will have one or two tables that connect to every other table in the database (for example, a user or status table), and this has to be removed from the diagram so we can get a clearer picture of the database.
Using the Zoom feature on the menu, I found the table I wanted to remove. For WideWorldImporters, that would be the People table, so I right-clicked on it. What I saw next I like a lot.
Notice in the context menu how Drop from Database requires the Ctrl+Delete key combination, whereas Remove from Diagram uses the Delete key. This prevents accidents for people who like to use the keyboard. If you do accidentally click on Drop from Database, there’s a dialog box that prompts you to make sure.
With that table gone, I clicked the Layout Diagram option in the menu, and had a much more sensible diagram to look at.
It’s also easier to see the two tables on the top right that have no relationships specified, so I now have something to be aware of.
But can it add related tables?
No, dbForge Studio Enterprise cannot drop a single table on the Designer and then have a way to bring in related tables by right-clicking on the table. However it can provide a handy list of dependencies in the Database Explorer window, which can be dragged to the Designer canvas separately. It’s a workaround, but it’s there.
Summary
dbForge Studio Enterprise is extreme overkill for what I wanted, however it did exactly what I needed, and was relatively easy to use. Playing around without reading any documentation, I could see how to generate scripts for the table, and export the structure to a new query window or the clipboard. If I was gainfully employed full time and had to use this tool instead of SSMS, I would not be unhappy.
And in that context: as a full-time employee, dbForge Studio Enterprise is worth the price, and worth your time taking a look or taking it for a test drive.
Are you already doing database modelling visual diagrams outside of SSMS? Tell me your preferred software solution for that in the comments below.
I have heard that since Microsoft is removing database diagrams from SSMS that they are adding it back to Visio. Do you know anything about this yet being an MVP and all (only if that info isn’t under NDA)?
I can’t comment because I don’t know. Those are completely different teams in the organization.
I’ve used Toad Data Modeler in a previous job. A bit expensive, but nice drawings.
Comments are closed.