May 18, 2009 at 10:35 pm
Comments posted to this topic are about the item Database Modeling and Diagrams with SQL Server 2008
May 19, 2009 at 12:56 am
In SqlServer2000 I used the Database Diagram for documentation.
I divided the Database (120 tables) in four diagrams (each the size of a single page A4) on the 4 subjects of the db (customers, employees, bookings, internet). I copy-pasted (printscreen) in in a Word document and had a low cost superb solution.
But then came 2005 and the captions (table name)came in a new big bold font. And it needed an empty line at the bottom (or you get a scrollbar which makes the table ugly and more width). And it didn't fit on a page any more. So 2005 blew up my documentation :crazy:
May 19, 2009 at 1:00 am
Hi,
Figure 6 is incorrect, you do not have to create the script and then return to the database model. You need to turn off the validation that SQL Management Studio has built in for changes that require a table to be recreated.
The option is available at Tools | Options | Designers | Prevent saving changes that require table re-create.
Regards,
Mark P Ashworth
May 19, 2009 at 2:26 am
I didn't understand the purpose of this article. These things existed in the last century as well 🙂
May 19, 2009 at 3:05 am
nice naming convention on your tables 😛
May 19, 2009 at 5:55 am
As far as export, you can also use one of last century's commands to get a file copy of your diagram:
bcp MY_DB..sysdiagrams out MY_DIAGRAM.bcp -c -T -S SERVERNAME
Then you can restore the db all you want and then bcp the diagram back in when needed. Yes, I know, there are other more modern methods. Just throwing this out there.
May 19, 2009 at 7:05 am
I gave this article a good score - I don't know why it is getting bashed in the ratings (only an average of 2 when I voted). The English is well written and it covers most of the basics of the diagram tool. I already knew all of the material (I always use the diagram tool) but I'm going to assign my intro database students to read this next semester along with the other articles they get assigned.
May 19, 2009 at 7:12 am
I like the database diagraming tools within SQL Server, however I learned not to use them because they are tied to intimately with the database. In 2005, I built up elaborate diagrams of each schema of the system I work on, but then when it came time to do an entire database refresh into our dev environment from the test environment, I couldn't find a way to export the diagrams out of the database in a way that could be imported back in later after the refresh (refresh was a backup and restore of test database). Does anyone know if they've solved this issue or found a way that I've missed? Untill then, I'll stick with outside tools to do my documentation and diagraming.
May 19, 2009 at 7:13 am
I stopped using the DD because when visualizing the DB and you link or make a change to the diagram it actually makes the change in the DB tables. What I have been looking for is a tool like this to use for documentation, and the actual foreign keys and linkages are done programmatically through views/SP/Scripts. Problem with Visio is our version will not read 2005 let alone 2008.
May 19, 2009 at 7:45 am
Parag Mehta (5/19/2009)
I didn't understand the purpose of this article. These things existed in the last century as well 🙂
Not everyone has been using SQL Server for more than one version. We get new people all the time and publish articles to help all levels of users.
May 19, 2009 at 7:47 am
The designer is nice, but limited. I recommend modeling every aspect of your project using a dedicated design tool (I use PowerDesigner) once you have generated the DB the Designer is really good at tuning things.
May 19, 2009 at 7:57 am
I like the article - it gives a clear rundown on the pros and cons of the simple built in diagramer, which I have found very handy.
The fact that the diagrams are tied to the actual system can be an advantage.
a) It encourages the use of referential integrity
b) There can be no "stale" diagrams
I always try to use referential integrity and set NOCHECK if the actual checking gets in the way.
The ref integrity constraints make for an instant diagram and also the relationships can be checked at some point by DBCC CHECKCONSTRAINTS. This is great for ETL processing where you may blow away the records in a lookup table and replace them later. At the end of all processing run DBCC CHECKCONSTRAINTS to see if any constraints are violated.
May 19, 2009 at 8:02 am
Where I work DD is used a lot by the other developers, so everyone but me as I very much dislike it.
The problems I have with it are:
1. It does modifications directly to the DB and in doing so recreates whole tables and relationships. This argument is not exclusively related to DD, but to any table modifications you do via the interface. In my view none of it is designed to be used extensivly on a filled database.
The problem is compounded when you need to move changes from the development to a test or production environment. Suddenly you need an database comparison tool as there have been no modification scripts made along the way. This also makes it hard to do partial updates.
Also, when I design a new (sub)-model, I want to be able to drop everything and recreate from scratch in the blink of an eye. This is very easy when you work from scripts or a tool that supports you in maintaining and updating those, but very hard with any direct action UI.
2. DD hides details that are very important for the quality of the modeling. I want to see at a glance what indexes there are, what fields are covered by them and any other relevant parameter that influences performance.
To have this kind of information in a consumable fashion I really need a create script that is compact and shows you these details in a declarative and well formatted form. This also helps me to keep an up to date modification script that can be directly applied to the target environment.
3. Organizing and printing is a pain for anything but the smallest of (sub-) models. I have used full blown modeling packages in the past such as Power Designer (Data architect module) from Sybase and its predecessor S-Designer. They are very expensive, but did work very well for me as you can model and do versioning, independent of the database and can generate update scripts between modeled versions automatically. It also features well functioning reverse engineer functionality which comes into its own when you are confronted with a database for the first time.
Just my 2c
May 19, 2009 at 8:17 am
One reason why they are poorly used (aside from the primary one - no one designs databases anymore, folks just throw a bunch of tables together) is that, for anyone working at the level that requires real modeling work, MS's feature is too restrictive.
I don't want to use one tool for SQL Server, another for ASE, and another for Oracle; to my firm, it's all 'the database'.
It may be better now, but also MS tends to limit choices of representation in tools like this, and then drop support as they run to the next marketing term. The best example is the (former) support for database modeling in Visio: MS would let you download modules to do this in the previous release, but they don't work in the newest Visio, and they have no plans to make it work.
That's why, to get serious about modelling and documentation in a non-trivial environment, you're out there looking at third party products.
Roger L Reid
May 19, 2009 at 8:46 am
The article covers the diagram feature well. I got an email with the subject line "Database Modeling and Diagrams with SQL Server 2008". It led me to think this article will talk about new features in SQL Server 2008. Most of the features covered in the article were already there in earlier versions. This article does not clearly indicate which features are propriety to SQL 2008 only.
I missed Steve's comment...
"Not everyone has been using SQL Server for more than one version. We get new people all the time and publish articles to help all levels of users."
In light of that, I think it was fair for the author to title it as he thought was proper.
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply