I've been using ERWin for about 10 years now. My version is ancient and I do not think it's worth paying the ridiculous upgrade price!
Problem is it's starting to show its age - I've been using it primarliy for MS SQL.
I wish I could justify Embarcadero's ER/Studio, but I just need something that will allow me to design a DB and sync it.
I've checked out http://stackoverflow.com/questions/674911/light-weight-er-diagram-tool, but nothing fits except Schemabank. But a web based tool has too many drawbacks (recurring fees, less integrated, off-site storage of data).
I've also used SQL Maestro for regular queries and DB management. It was OK, but has a few definite shortcomings. Has anybody used the ER designer successfully?
What do you guys use?
We just use Visual Studio Database Edition (which is now included in the Team System Developer Edition).
The possibility to create diagrams in SQL Server as well as linking ER diagrams in Visio.
We currently use ORM tools so the documentation diagrams are the ORM diagrams in some cases.
In short, 10 years ago we used and could defend the extra cost of a purpose built ER tool. Now we just use what is build into the other tools that we use.
Try Netbeans, its free and open source. http://www.netbeans.org/
Instructions on ER diagrams and SQL databases http://wiki.netbeans.org/ERDRequirementsV2
And instructions on connecting to MSSQL http://wiki.netbeans.org/AccessMssql
I quite like the Quest Toad Data Modeler - so far it seems quite capable, but it's a fraction of the cost of what other "professional grade" ER tools normally cost.
Check it out! I'd say it's worth a good look for sure.
You could also try DB designer -- freebie for MySQL.