Stop Using Enterprise Manager! (Use DDL Instead)

Of all the tools that ship with SQL Server, Enterprise Manager is by far the most feature-packed and widely-used. Nearly every SQL Server developer is familiar with Enterprise Manager. They are comfortable using the wizards and GUI to do everything from creating a new table to adding a schedule job. But as a project grows to encompass more developers and environments, Enterprise Manager becomes a detriment to the development process.

Most applications exist in at least two different environments: a development environment and a production environment. Promoting changes to code from a lower level (development) to a higher level (production) is trivial. You just copy the executable code to the desired environment.

  • Click on the desired database.
  • Click on Action, New, then Table.
  • Add a column named "Shipper_Id" with a Data Type "char", give it a length of 5, and uncheck the "Allow Nulls" box.
  • In the toolbar, click on the "Set Primary Key" icon. Then you skip 22 steps.
  • In the toolbar, click on the "Manage Relationships…" button.
  • Click on the New button, and then select "Shippers" as the Foreign key table.
  • Select "Shipper_Id" on the left column and "Shipper_Id" on the right column. Skip the remaining steps.

Not only is this process tedious, but you're prone to making errors and omissions when using it. Such errors and omissions leave the higher-level and lower-level databases out of sync.

Fortunately, you can use an easier method to maintain changes between databases: Data Definition Language (DDL). The change described in the previous example can be developed in a lower-level environment and migrated to a higher-level environment with this simple script:

CREATE TABLE Shippers (
  Shipper_Id CHAR(5) NOT NULL 
    CONSTRAINT PK_Shippers PRIMARY KEY,
  Shipper_Name VARCHAR(75) NOT NULL,
  Active_Indicator CHAR(1) NOT NULL 
    CONSTRAINT CK_Shippers_Indicator 
      CHECK (Active_Indicator IN ('Y','N'))
)
ALTER TABLE Orders
  ADD Shipper_Id CHAR(5) NULL,
  ADD CONSTRAINT FK_Orders_Shippers
    FOREIGN KEY (Shipper_Id)
    REFERENCES Shippers(Shipper_Id)

You can manage all the DDL scripts with a variety of different techniques and technologies, ranging from network drives to source control. Once a system is put in place to manage DDL scripts, you can use an automated deployment process to migrate your changes. This process is as simple as clicking the "Deploy Changes" button.

The perceived difficulty of switching changes from Enterprise Manager to DDL scripts is one of the biggest hurdles for developers. The Books Online don't help change this perception. A quick look at the syntax for the CREATE TABLE statement is enough to discourage most developers from using DDL.

Enterprise Manager helps you with this transition. Before making database changes, Enterprise Manager generates its own DDL script to run against the database. With the "Save Change Script" button, you can copy the generated DDL script to disk, instead of running it against the database.

But as with any code generator, your resulting T-SQL script is far from ideal. For example, having Enterprise Manager generate the DDL required for the change described in the example involves six different ill-formatted statements. What do you do now? You can add a bit of refactoring to the generated script, and the result looks almost identical to the example script I showed earlier. After a few more rounds of generating and refactoring, you'll want to transition straight to DDL, and never look back at tiresome database development within Enterprise Manager.

13 Comments

  • There are a number of additional things needed - like version numbering of all your objects (I use a parallel table that matches sysobjects but has major version, minor version, release number etc.) and release scripts that are re-runable (i.e. check that a table doesn't exist before running CREATE TABLE, and so on)

  • I never considered the possibility that a professional software developer would use a GUI to create a table. I know it's possible, I know the result is probably the same but it feels wrong. Writing DLL code also has the copy-paste advantage; for example, if you want fields like "created_by", "creation_date", "modified_by", "modification_date" in all tables, it's easy to do in code but repetitive work in the GUI tool.

  • Why not use the GUI to create your tables, then have it generate DDL for you? It wouldn't be too hard to write a script that just compares DDL for two tables and creates an ALTER TABLE statement to modify the old one to have the same schema as the new one.

  • Gabe: At least for Oracle, such tools already exist; e.g. the free Tora can compare database schemes.

  • What would be best is to have an option to print out all SQL/DDL run against the server, perhaps with options to filter. This way, instead of always referencing the documentation, one could simply do the action once and glean the relevant information from the generated output. I know that Enterprise Manager allows some of this, but it could to much better, make it much easier to see what's going on, etc.

    Note that ALTER TABLE doesn't work so hot when you want to add a field in the middle of others and there are a plethora of constraints on the table. Life isn't as simple as one might think.

  • "Note that ALTER TABLE doesn't work so hot when you want to add a field in the middle of others and there are a plethora of constraints on the table."

    There is no reason that this should be done, ever. Column are *always* assumed to be unordered -- the SELECT statement is what brings them out in a desired order.


    "Life isn't as simple as one might think."

    Life is as complex as you make it. I can see why you believe it's so tough when you try to do things like this ...

  • Erich, for SQL server the same tool exists: it's called AdeptSql, there's a free trial available for download.

  • There are also tools for managing schema and data changes for sql server. While they're not free, they can offer a lot of help when managing these kinds of changes. Check out the tools www.red-gate.com (they have a free trial). While you're at it grab their free SQL Prompt.

    Other companies make similar tools as well.

  • Alex,

    You know I am SO onboard with you on most topics, but on this you've really lost me. Do you never use the designer in VS.NET, or use a code generator such as Codesmith or MyGeneration to auto-generate repetiive plumbing?

    I have been developing for 7 years. I almost always use Enterprise Manager for initial development. Its just much faster. If i'm making changes to an existing database, I'll use EM to script it out for me. Also, others mentioned tools like RedGate and AdeptSql to merge changes automatically. This saves a lot on development time. Its not that i can't write my own DDL, its just that it saves time not to.

    Only when I'm doing some REALLY complicated schema migration do I script this all out by hand.

    Craig

  • Enterprise _MANAGER_

    For managing.

    Not for development.

  • In fact, you can consider DDL like SQL Server's command line interface and SQL DDL scripts like batch files.

  • Nice article; sums up the advantages of DDL, and how to go about learning it.

  • //I never considered the possibility that a professional software developer would use a GUI to create a table//

    Hmm, that reads as rather sanctimonious!

    I often use the GUI to create an initial table schema , particularly look-up tables used to populate dropdowns etc. Then I will modify the DDL which is automatically generated as appropriate.

    'Professionals' should also be able to select the most efficent method of getting the job done ;-)

Comments have been disabled for this content.