Are you working on an SQL Server database project? Read this article to learn about the perfect SQL Server ER diagram tool.
SQL Server is a Relational Database Management System (RDBMS) developed by Microsoft Corporation. It allows database developers to build desktop and web-based database applications.
In addition, many IT professionals (including Database Architects, Software Engineers, CTOs, etc.) and semi-professionals (including university students) use SQL Server modeling tools. So, let’s learn about the best SQL Server ERD tools and their features.
Vertabelo is an online data modeling tool that supports SQL Server and all other major DBMSs. It allows Database Architects to model databases from the scratch through Conceptual, Logical, and Physical data modeling phases.
Also, Vertabelo has automated features for creating SQL Server physical databases and re-engineering existing databases to convert them back to physical data models. This supports continuous modifications and revisions to your database.
Let’s check out some of Vertabelo’s important features.
Features for Creating Your Conceptual/Logical Data Model From Scratch
As an online ERD tool, Vertabelo comes with all the required features and notations, such as crow’s foot notations, UML, and IDEF1X, to draw your data model (ER diagram) from scratch. Learn more about the notations supported by Vertabelo.
Below is a logical data model of a student registration system. It was drawn with the user-friendly features and notations of Vertabelo.
Generating an SQL Server Physical Data Model From a Logical Model
Vertabelo provides an automated option to generate your physical data model from the logical model you have already drawn. This feature saves you time and increases the accuracy of your work.
The steps for generating an SQL Server physical data model from the logical model in Vertabelo are shown in the above images. Learn more about generating a physical data model from a logical data model in Vertabelo.
Vertabelo Understands SQL Server
As an SQL Server modeling tool, Vertabelo supports and understands the Microsoft SQL Server DBMS. It knows the supported data types, naming constraints, etc. of SQL Server. Also, it supports many other DBMSs.
When you create the physical model for SQL Server from the logical model, Vertabelo checks these constraints according to the SQL Server DBMS. When there are any mismatches, it shows error messages, as shown below.
Vertabelo Generates SQL Server DDL Files
Vertabelo’s SQL Server ERD tool provides a key feature to generate DDL files for SQL Server and other supported DBMSs from your physical data model (ER diagram).
You can generate SQL Server scripts to create all elements or remove all elements in your SQL Server database. Also, you can create scripts to create or remove only the selected elements in your SQL Server database.
This is an example of SQL Server script generated in Vertabelo.
Read more about SQL Generation in Vertabelo.
Vertabelo Imports Existing SQL Server Databases
Vertabelo provides four ways to import existing supported databases into the tool and use them:
- Through live connection to the database using the Vertabelo Reverse Engineering Tool.
- By uploading an SQL file generated with the Create statement in your database to the Vertabelo tool as explained here.
- By importing the XML file into the Vertabelo tool with the model definition.
- By easily copying an existing data model that you have already created in Vertabelo.
Learn more about importing an existing database into Vertabelo.
Collaborative Work in Vertabelo
Vertabelo’s online data modeler supports collaborative work with your team. You can grant access to your team members at three access levels: owner, editor, and viewer.
To grant access to your data model, simply provide your team’s email addresses and click a button. Also, you can provide a public link to people who do not have a Vertabelo account.
Also, Vertabelo provides flexibility to Database Architects for drawing portable data models. Because Vertabelo comes as an online tool, you can use this tool on any platform and any device.
2. SQL Server Management Studio
SQL Server Management Studio (SSMS) is an integrated application developed by Microsoft Corporation. This tool is specifically for connecting and managing SQL Server and Azure SQL databases.
However, SSMS can be used as an SQL Server designing tool as well. It provides several features for creating database diagrams.
Let’s look at the SSMS database designing features.
Drawing Database Diagrams
SSMS has a feature to draw a database diagram of an existing database.
You can create a database diagram by right-clicking on “Database Diagrams” under your database name in the Object Explorer. Add the existing tables in your database into your diagram. Then, add relationships, keys, etc. into the diagram.
In SSMS, you can change and customize the table view by right-clicking on a table and using the “Table View” sub-menu. Adjust the layout of your diagram, copy and paste your diagram into other applications such as Microsoft Word, add or remove tables, etc.
You can maintain many diagrams for one database in SSMS based on the requirement. SSMS was developed as a tool to connect and manage your physical databases. But it also offers a diagramming tool for drawing database diagrams for your existing database.
You can modify your physical database graphically by adding keys, indexes, relationships, foreign keys, etc. Therefore, SSMS plays a different role as a SQL Server designing tool compared to the other data modeling tools. These tools support the entire data modeling process, starting from scratch.
Before using SSMS, you will have to install the application on your desktop or the server to use it. Learn more about SQL Server installing and running on Linux server.
SqlDBM is an online data modeler that allows collaborative work on many platforms. This tool can be considered an SQL Server ER diagram tool.
SqlDMB supports data modeling for the SQL Server DBMS. In addition, it can be used for data modeling with many other major DBMSs.
Drawing ER Diagrams in SqlDBM
This SQL Server ERD tool provides many features and notations like crow’s foot and IDEF1X notations to draw your data models, from the conceptual model to the physical data model. Below is a data model of a hotel reservation system drawn in SqlDBM.
Forward and Reverse Engineering in SqlDBM
You can generate SQL Server DDL script from your physical data model by simply clicking “Forward Engineer – Generate SQL” in the toolbar. After, click the “Generate SQL” button.
With SqlDBM’s reverse engineering feature, you can upload or copy SQL Server DDL script and generate the respective data model in SqlDBM. Then, you can modify it.
These forward and reverse engineering features help Database Architects do back-and-forth revisions in databases.
Collaborative Work, Change Management, and Version Control
You can collaborate on SqlDBM data models with your team. To share your data models with your team, provide their email addresses. Control their access level by simply checking or unchecking a box.
You can maintain different versions of your data model in SqlDBM. The “Compare Revisions” feature allows you to compare the changes between different versions.
SqlDBM is a useful online SQL Server ERD tool for Database Architects with many key features that support collaborative work, creating data models from scratch, repetitive back-and-forth database revisions, change management, and version control.
ERDPlus is an SQL server designing tool that can be used to create conceptual and logical ER diagrams, relational schemas (physical data models), SQL DDL scripts, etc. This tool supports SQL Server and many other major DBMSs, such as Oracle, MySQL, PostgreSQL, Teradata, and IBM DB2.
Let’s see how ERDPlus supports Database Architects as an SQL Server ERD tool.
Data Modeling in ERDPlus
ERDPlus provides the required features and notations, like crow’s foot, for your entire data modeling process (the conceptual, logical, and physical data modeling phases), starting from scratch.
There are notations for drawing regular and weak entities, various types of attributes, and all possible cardinality constraints of relationships, such as mandatory-many, mandatory-one, optional-many, and optional-one.
The below diagram shows a logical ER diagram drawn in ERDPlus for a simple library system.
You have two options for creating a physical data model (relational schema) in ERDPlus:
- This tool enables you to model physical data model (relational schema) elements such as tables, table columns (with primary/foreign keys), and referential integrity constraint lines (connecting lines from foreign keys to the respective primary keys) from the beginning in a simple and efficient way.
- This tool has an automated method for converting your logical model (logical ER diagram) into a physical data model (relational schema) with one click of a button as shown below:
Once you have created a physical data model, you can edit and modify your relational schema.
Generating SQL Script
In ERDPlus, you can generate SQL script from your relational schema to generate your physical database with one click as shown below:
Exporting to PNG Format
ERDPlus enables exporting your data models into images in png (portable network graphic) format.
ERDPlus is a good online SQL Server ERD tool for modeling your database from scratch. It has some automated and key features that a great data modeling tool should have.
Navicat supports SQL Server and many other major DBMSs. Like the other SQL Server modeling tools we have discussed, Navicat also enables data modeling and creating physical databases with many automated features. You can install Navicat on Windows, macOS, or Linux.
Data Modeling in Navicat
This design tool enables you to create all levels of ER diagrams for SQL Server and other supported DBMSs with simplified tasks. It provides three standard notations, crow’s foot, UML, and IDEF1X.
Navicat provides an automated feature to create logical and physical data models from your conceptual model.
Navicat also comes with a reverse engineering feature to import existing SQL Server and other supported databases into the tool and edit them visually.
Generating SQL Script
The “Export SQL” feature in the Navicat SQL Server ERD tool enables you to generate SQL scripts for each part of your data model, comments, referential integrity rules, etc. This feature will save you time.
Navicat is also an SQL Server ER diagram tool. It comes with many key features that will save you time and money throughout the data modeling process.
Because this tool supports many DBMSs, including SQL Server, your models become portable among many major DBMSs. Also, it is a multi-connection database development tool.
Let’s Choose the Perfect SQL Server ER Diagram Tool
SQL Server modeling tools are used by many professionals and semi-professionals to develop SQL Server databases for desktop and web-based database systems. There are several online and offline options on the market. To choose the perfect tool for developing your database, consider a few things.
It’s better to have a web-based online tool that supports many platforms and collaborative work. It should have a user-friendly environment, commonly used notations (crow’s foot, IDEF1X, etc.) for data modeling, and key features (forward and reverse engineering, change management, version control, etc.).
Find a data modeling tool that supports efficient data modeling throughout the whole process, from concept to physical database. In other words, find a data modeling tool that specializes in data modeling and not anything else.