Implementation with DB

It comes the time to do an implementation. Implentation refers to the process to promote new development into production, In RUP definition, it belongs to phase 4 - Transition. Implementation may involve many aspects, hardware and software in broader sense. However database implementation seems more challenging because any inaproperate activity of it can crash production system, and it is hard to recover. This article talks about the concerns.

A typical scenario that I have encountered are applying database enhancement onto an older version application which is on the run. Commonly suggested approach is as following:

1. Backup the production database instance;
2. Create a new db instance with the backupped instance, making it a snapshot of the production database;
3. Run enhancement script to upgrade the database instance, that include data schema changes, script logic changes, and adding new entity or logic etc.
4. Test on upgraded instance. Once tests passed, run enhancement scripts on production database.

The basic idea is creating a production snapshot and test on that instance before applying to production.

There are ways to backup a database. Sybase provides backup SQL statement, or use designated Backup Server Compression Module.
The syntax of Backup statement:
BACKUP DATABASE TO archive-root
[ WAIT BEFORE START ]
[ WAIT AFTER END ]
[ DBFILE ONLY ]
[ TRANSACTION LOG ONLY ]
[ TRANSACTION LOG RENAME [ MATCH ] ]
[ TRANSACTION LOG TRUNCATE ]
[ ATTENDED { ON | OFF } ]
[ WITH COMMENT comment string ]
[ HISTORY { ON | OFF } ]

Sybase Backup Server Compression Module backups a database with a specific backup server, where a compression/decompression is involved along with backup and load activities. This backup approach is suggested for transaction-intensive application. To make the Backup Server work, we need to configure ip address and port through sql.ini file.

In addition, we can use DUMP statement and BCP tool to archive data. Fowllowing is a backup routine with DUMP statement:

-- Backup routine
use master
go
sp_dboption dbname, "single user", true
go
use dbname
go
checkpoint
go
dbcc checkdb (dname,skip_ncindex)
go
dbcc checkcatalog
go
dbcc checkalloc
go
use master
go
sp_dboption dbname, "single user", false
go
use dbname
go
checkpoint
go
dump tran dbname to device1
go
dump database dbname to device1
go

Dump statement can "dump" a database or transaction log that can then be used for recovery with "load" statement. So we can copy a db instance by:
1. Dump a db
2. Crreate a new db instance
3. Load the dumped file into the new instance.

In certain scenario, we need to use BCP - a powerful tool to import and export large amount of data between text file and a database. The syntex looks like the following:

bcp xx.dbo.contacts in "C:\temp\inventory.txt" -c -T
bcp xx.dbo.contacts out "C:\temp\inventory.txt" -c -T

Despite all the ways to choose based on concrete requirement, always check out the logs from the tools being used a good idea, it will tell what happened and may give direction to solve problems encountered.