Skip Navigation LinksHome > SSW Standards > Rules > SSW Deployment - Data Schema

Data Schema - How Changes are to be Implemented

One of the biggest deployment hassles is with the backend database. When you deploy the frontend from Development to Test to Production, it is usually a simple process of deleting the old files and replacing with a copy. The backend - particularly when it has data - is a different beast.

Following this standard will mean you only need to run the changes made since the last version. Usually a matter of a couple of SQL files. The basic run is "Every change to a deployed Database Schema (tables, views, stored procedures, user functions and jobs) must be saved as a .sql script file and then run from Visual a Studio Database Project (where available) or Query Analyzer."

THE PROBLEM:
You are the highly strung developer of a popular online Easter-Egg store. It is now 1am, and the build is due at 9am sharp. You have finally ironed out all of the bugs, and have made several database changes. Unfortunately, you will have to find out all the changes that have been made, and hope that your development team have fully documented them. If the changes haven't been documented, you are up the proverbial creek without a paddle. You need a simple way of changing.

THE SOLUTION:

The basic principle is you should be able to run all the scripts to get to the current version in a couple of minutes. To this end you need to follow these steps:

You talk to a client and create a schema (whatever way you want - we usually use Visual Studio.NET Database Project, SQL Server Enterprise Manager or Access 2002 ADPs). It is highly recommended that you use a Visual Studio.NET< Project, and add it to Visual SourceSafe database. This will allow you to:

  • Use templates, make/modify scripts and apply changes - all through the one integrated interface.
  • Track if you have made changes to the scripts/older versions of the scripts before deployment
  • Provide a checkin/checkout mechanism when multiple poeple are working on database schema changes.

Figure 1: Adding your Visual Studio.NET database project to Visual SourceSafe control.

Now, at this stage the database schema should be reasonably stable (ie there are no planned major developments.)

Make sure it includes a table for recording the current version of the backend. We call it _zsDataVersion (_zs for a system table defined/created by us)

FOR REPLICATED DATABASES ONLY:
Note:
Due to limitations in SQL Server Replication:

SQL Server 7.0:
A) If you have a replicated database in SQL Server 7 and you want to change table structure, then - before creating scripts, and after creating scripts - you will need to:

1. In Enterprise manager, manually remove subscriptions to the database.
2. Remove the articles on the publication for the table objects you want to change.
3. Make the changes in script.
4. Add the articles to publication again.
5. Recreate the subscriptions, and when creating the subscription, make sure that you select the option to say that you 'already have the schema and data'

SQL Server 2000:
B) If you have a replicated database in SQL Server 2000 and you want to drop or remove columns, this can be done via Enterprise Manager. Changes will be propagated automatically to other replicas in the database.
However, If you for example, you want to change the data type of a column in a table, you will need to follow steps 1 to 5 as per the SQL Server 7.0 procedure.

FOR ALL DATABASES (Replicated and Non-Replicated):
1) When you (and the client) are happy with the schema, open your Visual Studio Database Project (where available), and Right Click the database in the Server Explorer window. If Visual Studio.NET is not available, Open Enterprise Manager and Right Click the database, All Tasks, and Generate SQL Script.

Figure 2: Generate the Initial Script  e.g. ver001.sql - In Enterprise Manager (left), and Visual Studio.NET (right)

Make sure the correct options are selected (this interface is the same in Enterprise Manager and Visual Studio.NET database projects):

Create Script Standard
Figure 2: Create Script Standard Settings  

Create Script Standard Settings
Figure 3: Create Script Standard Settings 
 
Create Script Standard
Figure 4: Create Script Standard Settings  

Open the initial script within Visual Studio.NET editor (where available) or your text editor and add an INSERT statement at the bottom to add a record to _zsDataVersion (sample script for this table as below)

Create table _zsDataVersion using .sql

CREATE TABLE _zsDataVersionsql
( 
	ChangeID int NOT NULL,
	DateCreated datetime NULL ,
	BackEndPath nvarchar(255) NULL,
	EmpUpdated nvarchar(150) NULL,
	FrontEndPath nvarchar(255) NULL,
	rowguid uniqueidentifier ROWGUIDCOL NOT NULL,
	Note ntext NULL,
	SSWTimeStamp timestamp NOT NULL 
)
ON PRIMARY 
TEXTIMAGE_ON PRIMARY 
GO


 

 

 

 

 

 

 

 

 

If you require any sample data, make another SQL file, normally as SampleData.sql.

Note: You may need to import the data in order, or you will need to remove foreign key constraints temporarily within your SQL file, otherwise the data inserts may fail.

2) Changes after the initial full script (version 1 or ver001.sql) must be saved into the same directory. eg.

D:\DataSQLScripts

Before proceeding, make sure that the following options are checked on in Visual studio.NET (In Tools/Options)
Create it via SQL Server Enterprise Manager

Make changes in the normal way - via SQL Server Enterprise Manager (as per Figure 1)

Create it via SQL Server Enterprise Manager
Figure 5: Example of Adding a new field to a table

  • If you are in Enterprise Manager, Do NOT save changes   .
  • Instead save it as a script file (.sql)  

    If you are in Visual Studio .NET, click the save button and you will automatically be given the prompt as below

    Save your changes as a 
		script file
    Figure 6: Save your changes as a script file - Do NOT save the changes themselves.
  • Open the script file through Visual Studio.NET (where available) or your text editor and add the INSERT STATEMENT. Make sure that the insert statement is within a BEGIN TRANSACTION and a COMMIT TRANSACTION block. e.g.

    BEGIN TRANSACTION
    ALTER TABLE dbo.Categories ADD
    UnitID varchar(10) NULL
    GO

    INSERT INTO _zsDataVersion
    (ChangeID,DateCreated, BackEndPath, EmpUpdated, FrontEndPath, Note)
    VALUES
    ('387',GETDATE(),'Northwind','Peter/Peter/ALPACA','Northwind','Added field Category!UnitID')
    COMMIT

     

     

     

     

     

     

  •  

     

    Figure 7: Open the script file and add the INSERT STATEMENT (Text in red colour)

  • Save the script file. In visual studio.net, you will automatically be prompted for the name

    You now have a directory with all changes (see Figure 8) The idea is you run all the above scripts and you get up-to-date.  The _zsVersion table is a record of what state the database is at.

    Figure 8 (Above) - An example changes directory (left), the VS.NET changes directory (right)

    3) SYNCHRONISATION OF SCHEMAS:

    Determine the current version and the scripts to be run. You should check the current version of the database e.g.

    	SELECT Max(ChangeID) FROM _zsDataVersion

    and run the script which contains the next record for _zsDataVersion.  In VS.NET (where available),  right click on the script you want to run, and click Run or Run on to execute it.

    Figure: Running the scripts on the destination servers in VS.NET.

    Note:
    There are some important steps you should take before running these change scripts:
    1. Ideally, close off users (Check Management -> Process Info - > Current Activity). Otherwise, some major changes will time-out (because of locks held on those objects). Ideally, you should not have any other users with locks on the objects you are changing. 
    2. Backup database before making changes/running scripts. If there are major changes (esp large tables with many relationships), restore this backup to another SQL Server database (e.g. NorthwindScriptTest23112001 when major changes are about to be made. Run the scripts on this test database before running it on the database proper. Fix any data-related problems and generate associated scripts to make the updates.

    • Run each version script in Query Analyzer, first on the test database, then on the primary database. 

    Check the latest version in _zsVersion to confirm that the changes have been made.

    What about ADPs in ACCESS 2002?

    ACCESS 2002 is as great as you have a graphical view of stored procedure. It doesn't allow to save the changes to script file. But if you are disciplined then it is OK to use for stored procedures.


    Figure: Design View of Stored Procedure in Access ADP

    Sample table
    Figure : An example of a backend table recording the version numbers

     

    What about Data?

    Use Spreadsheet.

    Use the handy tool Auto Insertyou are about to leave the SSW site.

    Figure :Auto-Inserts results screen method

     

    SQL Compare should be only as a validation tool.
    We do not use SQL Compare on its own, as it does not record version numbers. e.g. There is no simple way of making sure what version I have  by looking at the two databases separately.

    The developer should be responsible for breaking the build as they make change without logging. This person is then in charge of checking SQL Compare until he finds the next person who breaks the build.

     

    Acknowledgments

    We hope this was helpful for you. If you have any comments or suggestions please contact us

    Adam Cogan


    Benefit from our knowledge and experience!

    SSW is the industry leader in Custom Software Development, Software Auditing & Developer Training.

    Call us on +61 2 9953 3000 or email us for a free consultation

    What does it cost? I’m not in Australia. Can you still help?