October 19, 2008 at 10:05 pm
We developed a fairly large database ERD in VISIO. I hoped that VISIO could generate the corresponding t-sql script to create the phsical database. In short, no such luck (and using the VEA verison proved to be a mess also.)
I know of ERwin (and the database diagrammer in sql 200\2005) but for our use its price is way out of line. What tool(s) can you recommend for database design and creation that are reasonably priced and relaitvely easy to use?
TIA,
Barkingdog
October 19, 2008 at 11:25 pm
to a limited extent, you can do this for free using the Database Diagramming tools built-in to SQL Server.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 20, 2008 at 7:35 am
We've been using ER diagrams less and less for development and instead relying on tools like Visual Studio Team System Database Edition. But we still do use ER diagrams for documentation and communication. The tool we use, which is cheaper, but not by much, than ERWin is ERStudio from Embarcadero. Great tool. Good company.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 20, 2008 at 4:05 pm
Grant,
You wrote
>>>
..we are relying on tools like Visual Studio Team System Database Edition.
>>>
I didn't think that VSTS, yet, offered any database design tools at all. (Am I wrong?)
Barkingdog
October 20, 2008 at 4:10 pm
I feel your pain Barkingdog.
I went thru the same thing. I'm so disappointed at Visio for not being to push the design to a database.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 20, 2008 at 8:38 pm
I am a little curious, why can't you just script the database that you have modeled or reverse engineered and then propagate that script?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 20, 2008 at 11:30 pm
>>> why can't you just script the database .....
I'm not sure how to do this. We are creating all the tables in VISIO (VEA) but find that its "forward engineering" script generator is hard to work with. For example, it is actually based on VISIO 2003 and doesn't fully support all sql 2005 data types. It also doesn't like one to use the same column name, even in different tables. And unless all of these errors are corrected it won't generate any t-sql script at all.
The main problem I see with sql 2000\sql2005 designer is that it has no separation between the conceptual and physical -- save a change to your model and it's saved to the physical database. It also lacks the ability to create separate files that can be distributed. I need to print out hardcopies and distribute them to all interested parties. Also, access to the actual diagrams themselves is restricted to the creator or dbo equivalents. These issues do not make it a "user\dba" friendly product.
Both products are far better than nothing but I'm surprised with all the databases designed in the last 30 years that there isn't a greater abundance of such tools.
Barkingdog
October 20, 2008 at 11:41 pm
Good points all. I think that on a shoestring youo can use manual procedures to fix up the holes, but I agree, there is a real lack of a reasonably priced well-integrated data modeling tool. IMHO this has been a huge market gap for a long time and it has always surprised me that no one has moved to fill this gap.
I guess that Microsoft's acquisition of Visio served to "chill" this potential market, whitout them ever having to actually fill it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 21, 2008 at 5:43 am
Barkingdog (10/20/2008)
Grant,You wrote
>>>
..we are relying on tools like Visual Studio Team System Database Edition.
>>>
I didn't think that VSTS, yet, offered any database design tools at all. (Am I wrong?)
Barkingdog
You're not wrong. If you mean by "database design tool" a gui mechanism for working with ER diagrams. VSTS just doesn't do that, yet (I hear rumors of new stuff for 2011). But we've been doing our physical design work more and more without the ER tool and simply plugging it right into code from day one. I've actually found it easier than trying to translate back & forth from a database into an ER diagram over & over.
Still, as far as ER tools, I'm pretty strong on Embarcadero's ERStudio. It isn't cheap, but I think it's one of the cheapest out there. In my opinion (worth about what you're paying for it), it's got the most bang for the buck of all the ER tools as well. But you are in an area that just isn't well served. Maybe Apex or Red Gate can be persuaded to put together something.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 21, 2008 at 8:00 am
Grant Fritchey (10/21/2008)
You're not wrong. If you mean by "database design tool" a gui mechanism for working with ER diagrams. VSTS just doesn't do that, yet (I hear rumors of new stuff for 2011).
And therein lies the problem with this potential product space (again, IMHO): No one's going to get into this if they think that MS might put them out of business overnight. But IF MS never does come out with it, or if they only go half-way (the way they have with Visio in ER for the last X years) then we never get anything really useable.
Still, as far as ER tools, I'm pretty strong on Embarcadero's ERStudio. It isn't cheap, but I think it's one of the cheapest out there. In my opinion (worth about what you're paying for it), it's got the most bang for the buck of all the ER tools as well. But you are in an area that just isn't well served.
Still, it will run you over $1500 which at best puts it in the "Middle-Tier Specialty Engineering Tool" category. ERWin occupies everything from that level up. What I really want is an "Almost Commodity SW Specialty Tool" which would put it in the $200-$400 range.
Maybe Apex or Red Gate can be persuaded to put together something.
Heck, I know that I could write the all of the under-the-hood guts of such a thing. If I could find someone to do the Design and GUI, ... hmmm 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 21, 2008 at 8:10 am
Several of the Red Gate people haunt these forums. Hopefully they'll pick it up & run with it. Contact them and offer your services. I'd like to see a cheaper alternative to ERStudio too. I hate recommending it to people in small shops, but it's still the best tool out there for the money.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 21, 2008 at 8:21 am
If anyone knows better then please correct me if I'm wrong.
I like what I can do with Visio for a reasonably low cost. The problem I have with Visio is that you cannot take the ERD an create, or add to, a database from it.
If you reverse engineer a database to create the ERD, then modify the ERD, can Visio update/modify the database?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 21, 2008 at 8:25 am
Alvin Ramard (10/21/2008)
If anyone knows better then please correct me if I'm wrong.I like what I can do with Visio for a reasonably low cost. The problem I have with Visio is that you cannot take the ERD an create, or add to, a database from it.
If you reverse engineer a database to create the ERD, then modify the ERD, can Visio update/modify the database?
Last time I used Visio like this, it could. However, if I understand what Grant is saying, then there are a lot of limitations with this approach.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 21, 2008 at 8:31 am
I primarily used Visio ER for documentation rather than modeling, but even there I found it very frustrating and far too time-consuming.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 21, 2008 at 8:44 am
I can only use Visio for documentation. From the help file:
This version of Visio can't forward engineer your database model. In other words it can't use a database model you create in a Visio to create a new schema in your database. However, you can forward enginner with Microsoft Visio Enterprise Architect edition, which is included in Visual Studio .NET 2003 Enterprise.
I'm not sure, but I'll bet a license for the Enterprise edition of VS will be as much or more than a license for ERStudio.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply