The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

SQL practice

Hi,

when a new application is designed,creating database,tables ,index,constraints..using query analyser,or enterprise manager(sql 2000)..which is better? and how to maintain changes?
thks
vishy
Thursday, July 13, 2006
 
 
==> using query analyser,or enterprise manager(sql 2000)..which is better? and how to maintain changes?

Neither.

Seriously.

Use your favorite text editor to maintain your scripts in text. Do *everything* via a script. You can't repeat an Enterprise Manager point-and-click action very reliably. You *can* repeat a text file script (run it again). You *can* track changes via a text file script with your favorite tool (we use Subversion/Tortoise).

We don't touch databases from the GUI tools -- even to enter data. We script it all, including the data files that populate initial data, test data, and whatnot.

Pointing and clicking your way around a GUI tool is a surefire direct route to disaster in a serious database environment.
Sgt.Sausage
Thursday, July 13, 2006
 
 
I disagree in certain respects.  Using the Enterprise Manager GUI tools during design in a development instance is very useful, this seems to be what Vishy is partly talking about. 

However, I agree that doing point-and-click admin on live production dbs is a bad practice, not only because of the lack of control and repeatable process, but because certain operations done through the EM GUI are done extremely inefficently behind-the-scenes. 

I don't see anything wrong with using Query Analyzer to execute your scripts, however.  If you use Visual Studio you can create a database project and your scripts will be properly source controlled.
Anonymous
Thursday, July 13, 2006
 
 
You can use visual tools to do your initial design/setup, but once you're maintaining it, USE SCRIPTS as Sgt Sausage says. 

Every _competent_ DBA I've worked with used/uses scripts for ongoing maintenance work.  They're repeatable, versionable and don't depend on a particular release of a particular tool. 

Even the folks I know who are in love with ER/Win suck scripts out after they're done futzing around in the designer and use the scripts to do the work.
a former big-fiver Send private email
Thursday, July 13, 2006
 
 
+1 for scripts. When I am forced to use some silly graphic tool like Erwin, I always reverse engineer. Life's too short to futz around in those GUI tools.
Steve Hirsch Send private email
Thursday, July 13, 2006
 
 
How about tools like Red Gate's SQL tools, or Apex's SQL tools that allow you to script changes between databases? I love them, but I'm not a DBA.
sloop
Thursday, July 13, 2006
 
 
We got SQL Compare at the office here to try to cut down on gotchas when migrating code to QA or production. For some reason, the user of the prog cannot reliably make migration scripts that actually work. Our office has too many users using point and click database modifications.
Peter
Thursday, July 13, 2006
 
 
What is the problem with the scripts? I've used it to sync up the development database with the production database, and it's worked great. I've also used their SQL Packager tool to easily move the database around to demo machines, and again that has been great. I'm normally all for scripts, but these tools (as well as Apex's tools) have worked very well for me. But again, I'm not a DBA so maybe there are pitfalls I haven't experienced.
sloop
Thursday, July 13, 2006
 
 
+Sgt.Sausage & scripts

Having worked in super-paranoid banking environments, using scripts to do >everything< is the way to go particularly if you are intending to make changes in a live environment. Scripts can be verified, debugged, tested and consistently executed.

"the Enterprise Manager GUI tools during design in a development instance is very useful"
Useful - sure for once off's and doing some what if's. But scripts allow you to verify that you did steps 1, 2 and then 3; rather than 1, 3 and then 2.

Cheers
Marcus from Melbourne
Friday, July 14, 2006
 
 
Yes, let me clarify for Vishy, that IMO using a GUI tool when initially designing the tables and relationships in a development db is simple and efficient.  However, once that db becomes live, then even in the development instance you should use scripts, otherwise if you do it by point-and-click you (or your DBA) will not be able to reliably repeat your modifications.  Anyway, a professional DBA would laugh at you if you tried to submit a change by telling him/her "OK, double-click on this line, right-click on this entity...".  I still don't see whats wrong with using Query Analyzer to run your scripts.
Anonymous
Friday, July 14, 2006
 
 
Anonymous, SQL Compare generates those scripts by comparing one database to another. Is there a problem with modifying your test database to what you would like it to be in a GUI tool, then use SQL Compare or ApexSQL Diff to generate the script to update the live database to match the test database?
sloop
Friday, July 14, 2006
 
 
Here's what we do in my shop: Every developer has a private copy of the database. We have a server that has a "Build" copy of the database. Whenever someone makes a change to their private copy and they want to integrate those changes into the next build, they use SQL Delta to compare their copy with the "build copy", and update the build copy.

During a build we use SQL Delta's command line scripting to get the differences between the current build copy and the "last released copy".

SQL Delta basically takes *everything* from a SQL server database and turn it into a text file (and then compare them all).

Anyway, whenever we release a version of the database we save that schema in our source control. Then if we want to we can take the schema of version 5.0.25.9132 and compare it to version 5.5.0.9353's schema and produce a script that we can use to upgrade from one to the other.
Wayne B Send private email
Sunday, July 16, 2006
 
 
BTW, thanks to Joel for letting me see the value of automating *everything* (one click builds, etc.)
Wayne B Send private email
Sunday, July 16, 2006
 
 
I'll be flamed but...

Throw away scripts, the mess you can provoke with a visual tool is far lower than the time lost to remember some arcane syntax to do scripting.
After modification, re generate the creation scripts and store them away safely.
Sevenoaks Send private email
Thursday, July 20, 2006
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz