What is the best ERD tool for forward-enginnering a SQL Server database?

Question

My current understanding is that Microsoft does not offer a viable tool for creating a DB ERD model and then forward-engineering the DDL to create the database. I've heard that Visio Enterprise Edition 2003 had this feature, but that it has been removed in subsequent editions, and that the old edition does not operate well when installed side by side with other critical products like Visual Studio 2008 etc.

I looked at Microsoft's descriptions of Oslo, but they are not very clear, the product is still in early CTPs, and it sounds like it might not be that straight forward to use for this purpose.

That being the case, what is the current best practice / best software to use for designing an ERD and then forward-engineering the model to create the DDL code for a SQL Server database? Should I just load up my old copy of ERWin?

Answer 1

Look at Sybase PowerDesigner

Answer 2

ERWin is still the most popular modeler out there.

Answer 3

I have had a good experiences with Embarcadero's ER/Studio for a few years. Expensive (as are most of the well known equivalents), but they have recently begun offering single platform licenses for MS SQL Server at a lower cost.

Dezign from Datanamic is a less featured, but less expensive alternative that you may want to look into.

Answer 4

Do you really need more than the Database Diagram feature of SQL Server Management Studio? Also, you can design individual tables through SSMS.

I typically design any non-trivial tables using NORMA, which does well enough forward engineering. However, it designs at the conceptual level, using Object-Role Modeling - above ER. Visio for Enterprise Architects included an implementation of ORM version 1; NORMA has version 2.

I happen to like designing at the conceptual level with this tool, since it allows me to express a richer data model, and the tool will generate most of the code necessary to ensure that all the constraints are enforced.

It can even produce an ER diagram for you, though I gather that gets memory-intensive for large models.

BTW, it's a free add-in to Visual Studio 2005 or 2008, Standard Edition or above.

Answer 5

I came across a blog describing a free component (Orthogonal "Toolbox") that does this when I needed to export a database design from Visio to get the SQL code to generate the corresponding database.

Looking at the site of Orthogonal "Toolbox" is a little confusing as to which version of Visio it works with, but for database export it says:

Microsoft® Visio® (2002/Version 10) for Enterprise Architects as bundled with Microsoft Visual Studio® .Net 2002/2003 Enterprise Architect Edition. This is the only version of Visio that supports the ORM/ER Source Models that this tool enhances. However, this tool also supports Microsoft Visio 2002/2003 Professional, but only for Database Model Diagrams (there are no ORM/ER Source Models in Visio Professional, and thus some features of this tool will be disabled).

Answer 6

I just did some significant research on this and found out about a reasonably priced though full-featured tool called ModelRight. For more details, you can see my full answer on this topic at: http://stackoverflow.com/questions/272816/what-is-a-good-visio-enterprise-architect-replacement.