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):
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 _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)
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.
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
|
|
Figure : An example of a backend table
recording the version numbers
|
What about Data?
Use Spreadsheet.
Use
the handy tool Auto
Insert.
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
| |