Version Tables


Another important concept when deploying changes is to add a version number to your changes, much like application developers do in their own version controlling. A table that we've used for years is db_version. This table holds what version of the database is installed now and what versions have been installed over the history of the database.

You can use the table to diagnose if the DBA that deployed the change skipped a build of the database by looking in the history of the changes. If you jump from 2.0 to 2.2, you know you may have an issue. It can also be used in conjunction with the application's version number. For example, you may have a document that says application 2.1 if the application requires 2.1.8 of the database. I've even had developers code the logic into the application at startup. If the application did not match the db_version table, it would throw an error. The table's schema looks like the following. You can download this code, called db_version.sql, from this book's page at www.wrox.com.

 if not exists (select * from sys.objects where object_id = object_id(N'[dbo].[DB_VERSION]') and OBJECTPROPERTY(object_id, N'IsUserTable') = 1)  BEGIN CREATE TABLE [DB_VERSION] (   [MajorVersion] [char] (5) NULL ,   [MinorVersion] [char] (5) NULL ,   [Build] [char] (5) NULL ,   [Revision] [char] (5) NULL ,   [OneOff] [char] (5) NULL,   [DateInstalled] [datetime] NULL CONSTRAINT [DF__Version__DateIns__0876219E] DEFAULT (getdate()),   [InstalledBy] [varchar] (50) NULL ,   [Description] [varchar] (255) NULL ) ON [PRIMARY] END 

For most environments, there are too many columns in this db_version table, but it was created as a catch-all and standard table. Every DBA that works at your company would know to go to the table, regardless of the application, to find the version. The version may match a document of known issues with the database. Typically, you may also find yourself creating views on top of the table to show you the last installed version. The following is a complete data dictionary for the table.

  • MajorVersion - Major release of the application. In the application version, it would be the following bolded number (1.0.5.1).

  • MinorVersion - Minor release of the application. In the application version, it would be the following bolded number (1.0.5.1).

  • Build - Build number of the application. In the application version, it would be the following bolded number (1.0.5.1).

  • Revision - Also can be called the minor service pack (or patch) position. This number would also refer to bug fixes found in QA. For example, you may have numerous iterations of Portal 2.5.3 as you fix bugs. You could increment this number (Application Build 2.5.3.2, for the second iteration). In the application version, it would be the following bolded number (1.0.5.1).

  • OneOff - In some cases, a customization code may be required. For example, Application A has a number of customized versions of Build 2.1.1. In those cases, you could have 2.1.1.0 - 1 to indicate a customization (1 being for Client B for example). This field is only used in specialized situations.

  • DateInstalled - The date this application was installed in the environment. This is set to getdate() by default, which will set it to today's date and time.

  • InstalledBy - The name of the installer or creator of the service pack

  • Description - Description of the service pack. This can be used in an environment where multiple clients are sharing one database (for example, "Upgrade Application A 2.5.1.2 for client."

To insert into the table, you would use the following syntax. You would need to make sure that the deploying DBA is disciplined to place the lines of code to create the table and insert into it at the top of each command file you create.

 INSERT INTO DB_VERSION  SELECT 1, 5, 0, 2, NULL, getdate(),'Brian Knight', 'Script to promote zip code changes' 

A last use for the table is to run a check before you perform a database upgrade. Before applying the database install for version 1.1, you can run an IF statement against the table to ensure that version 1.0 is installed. If it isn't installed, you can throw an error and stop the script from executing.



Professional SQL Server 2005 Administration
Professional SQL Server 2005 Administration (Wrox Professional Guides)
ISBN: 0470055200
EAN: 2147483647
Year: 2004
Pages: 193

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net