Source Code Management and Database Deployment - Deployment of Individual Objects

(Page 7 of 9 )

Some organizations choose to manage the code for individual objects and to deploy the database piecemeal by executing the code on the production server. This provides more flexibility, but requires more effort.

Deployment Scripts: Traditional Approach

Individual object scripts can be grouped in files with all objects of a particular type or even with all objects in a database. Such files can be created using the Generate SQL Script tool in Enterprise Manager. It can be set so that the group of objects of the same type is saved in a single file. It is also possible to use a custom tool to aggregate individual database object files from the Visual SourceSafe database. Most ERD modeling tools can also produce such scripts (but their scripts often require manual intervention). You can also use TbDbScript, described earlier in this chapter.

To have better control, I like to use the TbDbScript tool, or the Generate SQL Script tool in SQL Server, to create one deployment script for each type of database object. When the system contains more than one database, I find it very useful that TbDbScript names deployment script files using the Database - DbObjectType.sql convention (see Figure 11-7).

Scripting Data: Traditional Approach

Some tables contain data (seed, static, or lookup data) that needs to be deployed along with the database schema. To assist in deployment and to facilitate storing the data with the source code, use the setup_DataGenerator stored procedure, described in Chapter 9.

Use the setup_DataGenerator procedure on all tables with data that need to be scripted:

set nocount on
exec setup_DataGenerator 'AcquisitionType'
exec setup_DataGenerator 'EqType'
exec setup_DataGenerator 'Location'
exec setup_DataGenerator 'OrderStatus'
exec setup_DataGenerator 'OrderType'
exec setup_DataGenerator 'Status'
exec setup_DataGenerator 'Province'

Figure 11-7.  Deployment scripts

The result will be a script that consists of Insert statements (which had to be cropped to fit the page):

-----------------------------------------------------------
Insert into AcquisitionType (AcquisitionTypeId,AcquisitionType) values
Insert into AcquisitionType(AcquisitionTypeId,AcquisitionType) values
Insert into AcquisitionType(AcquisitionTypeId,AcquisitionType) values
Insert into AcquisitionType(AcquisitionTypeId,AcquisitionType) values
Insert into AcquisitionType(AcquisitionTypeId,AcquisitionType) values

-----------------------------------------------------------
Insert into EqType(EqTypeId,EqType) values (1,'Desktop') Insert into EqType(EqTypeId,EqType) values (2,'Notebook') Insert into EqType(EqTypeId,EqType) values (3,'Monitor') Insert into EqType(EqTypeId,EqType) values (4,'Ink Jet Printer')
...

Save the resulting scripts in a text file (I often use Database - Data.sql as the name of this file).

Next: Scripting Data in Visual Studio .NET >>
 

More MS SQL Server Articles
More By McGraw-Hill/Osborne

Comments On: Source Code Management and Database Deployment

>>> Be the FIRST to comment on this article!

Buy this book...

This article is excerpted from SQL Server 2000 Stored Procedure & XML Programming, second edition, written by Dejan Sunderic (McGraw-Hill/Osborne, 2004; ISBN: 0072228962). Check it out at your favorite bookstore today. Buy this book now.