Deployment of Individual Objects

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 Script command in Management Studio. 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 in the previous chapter.

To have better control, I like to use the TbDbScript tool 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 18-3).

Figure 18-3: Deployment scripts

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 util.ap_DataGenerator stored procedure, described in Chapter 15.

Use the util.ap_DataGenerator procedure on all tables with data that need to be scripted:

 set nocount on exec util.ap_DataGenerator 'AcquisitionType' exec util.ap_DataGenerator 'EqType' exec util.ap_DataGenerator 'Location' exec util.ap_DataGenerator 'OrderStatus' exec util.ap_DataGenerator 'OrderType' exec util.ap_DataGenerator 'Status' exec util.ap_DataGenerator 'Province' 

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).

Scripting Data in Visual Studio .NET

Alternatively, you can use Visual Studio 2003 .NET (but not Visual Studio 2005) to script data and add it to Visual SourceSafe:

1. Open Server Explorer, navigate through the nodes, and expand the Tables node in the Asset5 database.

2. Select the tables with seed data (such as AcquisitionType, EqType, OrderStatus, and OrderType).

3. Right-click the selection and select Export Data from the menu.

4. The program prompts you for Locations For Exported Data File and to confirm that you want to export the selected data. The default location will be the folder that contains the Create scripts you generated earlier.

5. When you confirm the export operation, the program generates a set of DAT files. You typically need to select the files in Solution Explorer and Check (them) In.

These files are not SQL Server scripts but simple binary files (see the content of a file in Figure 18-4).

Figure 18-4: Content of DAT file

Deploying Scripts: Traditional Approach

The deployment scripts can then be executed manually one by one in the Query window of Management Studio, but I have created one program and a couple of stored procedures that allow me to automate execution of a set of scripts.

To prepare for deployment, I need to create a list of scripts and save it in a text file. The procedure executes the scripts in the order in which they are listed in the text file:

 -- list of deployment scripts for Asset5 database Asset5   - database.DBS Asset5   - UDT.sql Asset5   - Table.sql Assets   - DRI.sql Assets   - Functions.sql Assets   - sp.sql Assets   - Views.sql 

Although Deferred Name Resolution allows you to ignore the order of creation of stored procedures, there are still some dependencies that must be followed. For example, indexes must be created after tables, tables after user-defined data types, and all of them after the database has been initiated.

With this in mind, one of the main advantages of util.ap_BatchExec_OA is that it preserves the order of execution of files. No human intervention is required and the opportunity for error is reduced. The procedure uses SQL Distributed Management Objects (SQL-DMO) to execute individual scripts against the database server. SQL-DMO is a set of COM objects that encapsulate the functionality needed for administering SQL Server. To use SQL-DMO from SQL Server, you have to use the system stored procedures for OLE Automation (COM), described in Chapter 21:

 alter proc util.ap_BatchExec_OA -- Execute all sql files in the specified folder using the alphabetical order. -- Demonstration of use of OLE Automation.      @ServerName sysname = '(local}\rc',      @UserId sysname = ' sa ',      @PWD sysname = 'my,password',      @DirName varchar(400)='C:\sql\test',      @File varchar(400) = 'list.txt',      @UseTransaction int = 0 as set nocount on declare @fileSystemObject int,         @objSQL int, @hr int,         @property varchar(255),         @return varchar(255),         @TextStream int.         @BatchText varchar(max),         @filePath varchar(500),         @ScriptId varchar(200),         @Cmd varchar(1000)  --- Get list of files  create table #FileList (ScriptId int identity(1,1),                          FileName varchar(500))  select  @Cmd = 'cd ' + @DirName + ' & type ' + @File  insert #FileList (FileName)  exec master.sys.xp_cmdshell @Cmd  -- remove empty rows and comments  delete #FileList where FileName is null  delete #FileList where FileName like '--%'  -- prepare COM to connect to SQL Server  EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @objSQL OUTPUT  IF @hr < 0  BEGIN    print 'error create SQLDMO.SQLServer'    exec sys.sp_OAGetErrorInfo @objSQL, @hr    RETURN  END  EXEC @hr = sp_OAMethod @objSQL, 'Connect', NULL, @ServerName, @Userld, @PWD  IF @hr < 0  BEGIN     print 'error Connecting'     exec sys.sp_OAGetErrorInfo @objSQL, @hr     RETURN END     EXEC @hr = sp_OAMethod @objSQL, 'VerifyConnection', @return OUTPUT     IF @hr < 0     BEGIN        print 'error verifying connection'        exec sys.sp_OAGetErrorInfo @objSQL, @hr     RETURN END -- prepare file system object EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @fileSystemObject OUTPUT IF @hr < 0 BEGIN    print 'error create FileSystemObject'    exec sp_OAGetErrorInfo @fileSystemObject, @hr    RETURN END -- begin transaction if @UseTransaction <> 0 BEGIN   EXEC @hr = sp_OAMethod @objSQL, 'BeginTransaction '   IF @hr < 0   BEGIN      print 'error BeginTransaction'      exec sp_OAGetErrorInfo @objSQL, @hr      RETURN   END END -- iterate through the temp table to get actual file names select @ScriptId = Min (ScriptId) from #FileList WHILE @ScriptId is not null BEGIN      select @filePath = @DirName + '\' + FileName      from #FileList where ScriptId = @ScriptId       if @filePath <> ''       BEGIN         print 'Executing ' + @filePath         EXEC @hr = sp_OAMethod @fileSystemObject, 'OpenTextFile',                                @TextStream output, @filePath       IF @hr < 0       BEGIN          print 'Error opening TextFile ' + @filePath          exec sp_OAGetErrorInfo @fileSystemObject, @hr          RETURN      END      EXEC @hr = sp_OAMethod OTextStream, 'ReadAll', @BatchText output      IF @hr < 0      BEGIN         print 'Error using ReadAll method.'         exec sp_OAGetErrorInfo OTextStream, @hr      RETURN END -- print @BatchText -- run it. EXEC @hr = sp OAMethod @objSQL, 'Executelmmediate', Null , @BatchText IF @hr <> 0 BEGIN    if @UseTransaction <> 0    BEGIN        EXEC @hr = sp_OAMethod @objSQL, 'RollbackTransaction '      IF @hr < 0         BEGIN            print 'error RollbackTransaction'            exec sp OAGetErrorInfo @objSQL, @hr         RETURN        END    END    print 'Error Executelmmediate.' --Transaction will be rolled back,    exec sp_OAGetErrorInfo @objSQL, @hr    RETURN  END   EXECUTE sp_OADestroy @TextStream END print 'Finished executing ' + @filePath select @ScriptId = Min(ScriptId) from #FileList where ScriptId > @ScriptId end print 'Finished executing all files.' drop table #FileList EXECUTE sp_OADestroy @fileSystemObject if @UseTransaction <> 0   BEGIN      EXEC @hr = sp OAMethod @objSQL, 'CommitTransaction '      IF @hr < 0      BEGIN      print 'error CommitTransaction'      exec sp_OAGetErrorInfo @objSQL, @hr      RETURN   END END RETURN 

To execute the util.ap_BatchExec_OA procedure, you need to specify values for the parameters for the SQL Server instance, login, password, and folder that contains your deployment scripts, and the name of the file containing the list of deployment scripts. You also need to decide whether deployment is to be performed as a transaction. Transactions cannot be used for initial deployment because database creation cannot be performed by a transaction. However, using transactions is very useful for incremental builds.

The util.ap_BatchExec_OA procedure has one limitation. It can process only short (up to 8,000 characters) scripts. Automation stored procedures, such as sp_OAMethod, were designed before SQL Server got varchar (max) data types, so they are limited to 8,000 characters. I have decided to include it in this the book for two reasons. First, 8,000 characters is probably enough for running an incremental build. Second, it's educational—it demonstrates the use of COM objects from Transact-SQL.

Alternatively, on SQL Server 2005, you can use an updated version that utilizes the varchar (max) data type— util.ap_BatchExec8. The procedure can be used to run larger scripts:

 create proc util.ap_BatchExec8 -- Execute specified sql files.      @ServerName sysname = ' .\rc' ,      @UserId sysname = 'sa' ,      @PWD sysname = 'my,password',      @DirName varchar(400)='C:\sql\test',      @File varchar(400) = 'list.txt',      @UseTransaction int = 0,      @debug int = 0 as set nocount on adeclare @filePath varchar(500)          @fileId int, OMaxFileID int,          @OldFileId int,          @Cmd varchar(1000),          @i int,          @iOId int,          @max int,          @s varchar(max),          @line varchar(max) -- Get list of files create table #FileList (FileId int identity(1,1),                         FileName varchar(500)) select  @Cmd = 'cd ' + @DirName + ' & type ' + @File insert #FileList (FileName) exec master.sys.xp_cmdshell @Cmd -- remove empty rows and comments delete #FileList where FileName is null delete #FileList where FileName like '--%' if @debug <> 0      select * from #FileList create table #script (SQL    varchar(max),                       LineId int identity) select @FileId = Min (FileId),        @MaxFileId = Max(FileId) from #FileList -- loop through files WHILE @FileId <= @MaxFileId BEGIN    -- get name of the file to be processed    select @FilePath = @DirName + '\' + FileName    from #FileList    where FileId = @FileId    if @FilePath <> ''    BEGIN        if @debug <> 0            print 'Reading ' + @FilePath        set @cmd = 'Type "' + @FilePath + '"'        insert #script (SQL)        exec master.sys.xp_cmdshell @Cmd        Select  @i = Min (LineId),                @max = Max(LineId),                @s = ' '        from #script      while @i <= @max      begin         Select @line = Coalesce(SQL, ' ')         from #script         where LineId = @i         if @debug <> 0             select 'read line =', @i i, @line line         if Left(@line, 2) <> 'GO'         begin            -- the line and go another round            select @s = @s + char(13) + char(10) + @line            if @debug <> 0                select @s [@s]         end         else         begin            begin try               if @debug = 0                  exec sp_sqlexec @s               else                  select @s            end try            begin catch                print Error_message()                print 'Process stopped.'                return            end catch            set @s = ''          end          -- continue line by line          set @iOId = @i          select @i = Min(LineId)          from #script          where LineId > @iOld      end    END    -- get next file    set @FileId = @FileId + 1    select @FileId FileId    truncate table #script END return 

You may find it strange that this stored procedure does not have a transaction inside. You can easily make it run under a transaction if you do something like this:

 Set xact_abort on Begin tran Exec util.ap_BatchExec8 "(local)\ss2k5", "sa", "my,password", "c:\script\test list.txt" Commit tran 

You should carefully consider the pros and cons of executing deployment scripts in transaction. It is nice to promise your IT management that you will roll back everything if there is any unforeseen problem, but operations will be slower if you run them in transaction; complete rollback may take much more time than you expected and some operations cannot be rolled back.

The third method for deploying database scripts is the BatchExec.exe program that you can download from http://www.Trigonblue.com/tsql.net/download.htm. This is a console C# application and you can run it on computers that have the .NET Framework installed using:

 BatchExec (local)\ss2k5 sa my,password c:\script\test list.txt 

Deploying Create Scripts in Visual Studio .NET

Create scripts generated in Visual Studio 2003 .NET (but unfortunately not in Visual Studio 2005) can also be "glued" together and deployed on other servers:

1. Select the Create Scripts folder in Solution Explorer, and then select Project | Create Command File.

2. Set the Name Of Command File and move all or just some of the scripts in the Available Scripts list to the list of Scripts To Be Added To The Command File.

   

3. If you have moved some of the table files (TAB) that have data files (DAT) associated with them, the Add Data button becomes available. Click the button and the program prompts you to confirm associations between files.

   

4. You probably do not need to change anything, so just click OK and the program returns you to the previous screen.

5. Click OK again and the program generates a command file (or batch file) that can be used to execute all Create scripts on any server (see Figure 18-5).

Figure 18-5: Command file for deploying Create scripts

Incremental Build: Traditional Approach

Whichever method you choose for performing a full build of the database, you will eventually need to deploy design changes and hot fixes while preserving data in the database. Such changes can even accumulate over time. Typically, code changes for procedures can simply be executed in their latest form against the production database, but changes to the database structure (tables) must be implemented in such a way that they preserve data.

util.ap_BatchExec8 is very useful for deploying incremental changes on the database server. Individual changes to database objects can be grouped by defect number or version number (see Figure 18-6).

Figure 18-6: Deployment script for incremental build

It is especially useful to run the process as a transaction in this case. If an unexpected error occurs during the deployment, it is preferable to roll back all the changes, leaving the production system intact. If changes are not very big, it may not take too much time.

Tip 

Once you assemble deployment scripts, it is critical to perform sufficient unit testing. You can run the scripts against a new server or a new instance on the existing server and test the changes.

You should repeat the deployment, fixing issues that you find, until it runs without a glitch. The ultimate test is whether the application can work with the database system without additional intervention.

Incremental Build in Visual Studio .NET

Alternatively, you can create incremental scripts in Visual Studio .NET. In this case, you create Change scripts and manage them in the folder of the same name (in Solution Explorer). You should again create a command file, but you should probably name it differently. You will use the same techniques and methods that have already been described regarding the full build in the earlier section "Scripting Data in Visual Studio .NET."

Tip 

You should pay special attention to avoid mixing versions of files and to execute database changes in the right order. It is a good idea to add sequential numbers or the date and time at the beginning of filenames. You can store different releases in different folders, or you can have different command files if you keep all Change scripts in the same folder. When you are done, test, test, and test again.