Often you’ll find that a project is of a size where the data model is easier to visualize if you…well…if you visualize it. The canonical way to do this for those of us on the MS stack is through database diagrams in SQL Server Management Studio.
Historically, a large number of organizations have worked with data analysts that develop the schema for an application given a set of requirements using a set of powerful designer tools. These tools are capable of generating change scripts, comparing databases for changes and drawing out the relationships between entities. This is more true for larger applications, certainly more true in the enterprise space, and is a practice you can still see in operation today particularily in working with legacy code.
However, for smaller shops and projects and in the agile space, this is not the common approach. Many folks choose to evolve data models on the fly, use tools like data projects or migration tools like Roundhouse. And, in today’s advancements in EF, there are many projects that elect to use code first migrations which have evolved far beyond the one-way, strongly opinionated roots that first came to Entity Framework back in the unicorn days.
In my travels and in mentoring developers over the last few years I’ve found a surprising number of folks who haven’t seen the tooling that is freely available to them through SQL Server Management Studio, also known as SSMS.
In an effort to better understand a fix or feature that I’m asked to help with, I often will sketch out the problem and the implementation. When this includes modifications to the schema I like to employ the use of a diagram in my effort. While pen-and-paper would do just fine for most cases, it’s often handy to know data types, see any relationships and move things around as often as needed to clarify the picture of the problem I’m trying to solve. And this is where tooling comes into play.
SSMS is a medium-weight utility that clocks in just under a gig (you can download it here). It’s by no means small, but it is powerful and has some great cloud-connectivity capabilities as well. It’s designed for the fully-featured SQL Server SKUs, but it works just fine for LocalDB as well.
To get started with diagrams in LocalDB, simply invoke the context menu with a right-click on your database’s “Database Diagrams” node in SSMS’s Object Explorer.
Click on the option for “Install Diagram Support”.
Note I run on Windows 10 and I am not on a domain. There is no active directory on my machine. I have found that when I use code-first databases and EF migrations that I will get an error like
Microsoft SQL Server Error: 15404 saying:
LocalDb could not obtain information about group/user (username)).
This error also surfaces as ‘SQL Server error 0x534’, ‘0x54b’, ‘0x2147’.
You can get past this error by elevating your privileges on the database in question using the following command. To execute, just right-click on your database to get that context menu back and select “New Query”, then paste this in (and fix your DB name):
alter authorization on database::[your-db-name-no-quotes] to sa
This is okay with LocalDb instances running on your machine, as I’m assuming that you’re going to be running this locally. Be sure to consider best practicies, application-level security and your company policies before changing database permissions for databases your organization makes available at large.
With security set and ready to go you’re pretty much off to the races.
This part is super easy. Now that diagram support has been added just invoke that context menu again, but instead, this time choose “New Database Diagram”. A blank canvas will appear and you’ll be prompted to add tables.
Select the tables you want to learn more about and add them to your diagram. SSMS draws out any relationships that exist.
By default, tables will be displayed with only the column names. A much more useful rendering is to switch to the “Standard” view which will reveal extended properties of the columns.
SSMS has a whole host of other features baked into diagrams that I tend to avoid. You can create new tables, modify columns, change properties and create relationships.
I can’t see many scenarios where this would be a good idea. These types of things should be done through a managed and repeatable process that can be easily propogated to other developer workstations or out into production. Using the designer to do this will break my heart and also a kitten will die.
There is one case where this might be useful. If you are learning and want to better understand the SQL that is generated through the lens of something meaningful to you (like entities in your project) you might be able to use this as a learning tool. Go through with your changes in the designer (they are not saved by default), then select Database Diagram -> Generate Change Scripts from the application menu. SSMS will prompt you to save out change scripts.
Note: You may need to enable the setting called “Prevent saving changes that require table re-creation” in order to get some types of edits to work, otherwise you’ll see a message saying that “Saving changes is not permitted.”
You can enable that setting in Tools -> Options -> Designers.
To it’s credit, SSMS does a healthy job of trying to preserve your data. Here, I’ve simply added a column to the
CampaignGoal table, but you can see what SSMS is trying to do behind the scenes with its change script:
Personally, I find
dotnet ef migrations add new-notes-field a lot easier to do.
Database Diagrams are local to your computer and are not part of your code base. If you’re using EF and code-first, chances are you’ve never seen a “picture” of your model. Grab yourself a copy of SQL Server Management Studio and get sketching.
That feature ain’t going to finish itself ;)