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

Figure 2: Create Script Standard Settings

Figure 3: Create Script Standard Settings


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

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

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


Figure 6: Save your changes as a script file - Do NOT save the changes themselves.

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

	SELECT Max(ChangeID) FROM _zsDataVersion