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.