Which tool can help generate a SQL server 2005/2008 database from E-R diagram?

Question

In Visio 2003 Architect version I can generate a SQL Server Database on the fly from UML ER diagram. But there is no such a way by using Visio 2007. My question is: Any other good and latest tools can support ER-> DB or DB->ER very well for SQL2005/2008?

Answer 1

I remember that in Visio 2003 that was only possible, I believe in the Enterprise Edition. Could that be your issue with 2007?

Answer 2

I think Enterprise Architect can do that too.

http://www.sparxsystems.com.au/

Answer 3

You can do the ER diagram on Visual Studio creating a dbml file and then export to a Sql Server script using the huagati plugin. http://www.huagati.com/dbmltools/

Answer 4

Most of the tools don't handle SQL 2005/2008 correctly. But CA ERwin® Process Modeler does the best job (at a cost though).

There is a community edition but it is limited to 25 objects per model.

Give it a try and let us know.

Answer 5

If you're looking for a free/open source tool to achieve this, you could try:

DBDesigner

From the website:

DBDesigner 4 is a visual database design system that integrates database design, modeling, creation and maintenance into a single, seamless environment.

It combines professional features and a clear and simple user interface to offer the most efficient way to handle your databases.

DBDesigner 4 compares to products like Oracle's Designer©, IBM's Rational Rose©, Computer Associates's ERwin© and theKompany's DataArchitect© but is an Open Source Project available for Microsoft Windows© 2k/XP and Linux KDE/Gnome. It is release on the GPL.

And it supports the following features:

• Available on Linux / MS Windows
• User Interfaced based on industry standard layouting software
• Design Mode / Query Mode
• Reverse engineering MySQL, Oracle, MSSQL and any ODBC databases
• Userdefined schema-generation
• Model-To-Database syncronisation
• Index support
• Automatic foreign key placement
• Weak entity support
• Standard Inserts storing and sync
• Full documentation capabilities
• Advanced model printing
• Output as image
• All MySQL datatypes with all options
• User defined datatypes
• Database storage, ability to save model within database
• Network-/Multiuser access through database storage
• Version control*
• SQL Query Builder
• SQL Command History
• SQL Command storage within model
• Plugin interface

I think the bolded ones above (my own emphasis) should give you what you require (i.e. effectively two-way database/model synchronization).

I have used the tool in the past to reverse engineer an existing database to an ER diagram. From what I remember, it wasn't the most stable software I'd ever used (I seem to recall it crashed a couple of times) however, this was a little while ago so it may be more stable now.

Answer 6

http://code.google.com/p/database-diagram/