Change Management

Managing change in SQL Server applications requires a process that protects data integrity & maximizes production uptime, while providing flexibility for new development. I would propose the following as a minimum configuration to accomplish these goals.

Create 3 database environments -- one for production, one for development, one for QA. Only the DBA has direct access to production or QA. The developer has direct access to only the development db. These 3 db's can sit on separate servers, or alternately the development and QA db's can run on named instances on the same server if size & activity is modest.

Create 3 application front-ends -- one for each database; containing some code, config file or other means to connect the front-end to the correct db; having some method to prominently display the connected server & database when it is not connected to the production db. (The front-end is really a single design, it is only the connection string that varies between the 3 copies.)

To begin, the DBA takes a backup from production & restores it to both the development & QA servers.

The developer connects the DEV front-end to the DEV db, writes T-SQL scripts to make any changes to the design, and updates the front-end for those changes. It is possible to use the GUI to generate the change script, but the GUI is never used to actually apply those changes - that is accomplished with the script. The developer also prepares documentation for the desired change in the front-end.

At this point, the developer may decide to share the DEV copy with select stakeholders, especially if the change is complex, for feedback & verification that the change meets the requirements. This step is commonly referred to as user acceptance testing.

When the developer verifies the change in that environment, they pass the change script to the DBA who reviews the script, and then applies it to the QA db. The developer will then connect the QA front-end to the QA db and verify the change.

The QA person will then evaluate & verify the change using the documentation provided by the developer. Upon their sign-off, the app is ready for release.

The developer notifies all users that the database update will commence.

The DBA will backup all 3 db's and then apply the script to production. Following that, the developer will connect the production front-end to the production db & verify. The developer publishes the new front-end & notifies the users that the update is complete.

The DBA takes a fresh production backup & restores to both DEV and QA, and the developer reconnects DEV to DEV and QA to QA. The DBA may also pass a copy of the backup file to the developer, if the 4th environment is used as follows.

A 4th possible environment is for the developer to maintain a copy of the DEV db on their PC, using e.g. the MSSQL Developer Edition along with a 4th copy of the front-end. This provides ultimate flexibility of design; the developer restores the backup to that system & can use that as a test-bed for feasibility studies of design changes without impacting the true DEV environment.

Throughout all the above processes, source control is used to maintain matching copies of the front-end & database scripts to allow rolling back changes that have unintended consequences.

SQL scripts without sp_executesql

There is a totally useless scripting option in SSMS 2005 that you think might mimic the behavior of EM 2000 but it does quite the contrary.

Using Enterprise Manager in MSSQL 2000 you could generate a script to DROP and / or CREATE an object. The DROP statement used the "if exists" syntax, so you could replace an object with a new version. If you wanted to change an object (except for tables) you generate just the CREATE and then change that one word to ALTER. Simple stuff.

However, in SSMS there is an option "Include IF NOT EXISTS clause" which does several totally unnecessary things:

First of all, you will see

EXEC dbo.sp_executesql @statement = N'
all your statements are here
'
If you choose Create, it tests IF NOT EXISTS followed by CREATE, but you just scripted an object that exists, so it does nothing.

If you choose Modify, it tests IF NOT EXISTS followed by ALTER, but how can you alter an object that does not exist?

What a mess.

If you want to generate a script like 2000, then you need to turn that option OFF, and then separately generate the scripts for the DROP and the CREATE, and put them together.

SQL - list columns in a table

I have gotten into the habit of using either sp_help or sp_columns to view a list of column names, for example

sp_help 'MyTable'

or

sp_columns 'MyTable'

which are much faster than using the Object Explorer in SSMS to drill-down thru the database, table and finally the columns. The downside of these sp's is that they display much more info than usually needed; I just want a list of columns & data types etc.

I created a new stored procedure wm_columns, click the link to view.

I began with sp_columns since it returns a single record set. Then, after looking in BOL I created a temp table to capture the output, and then execute the sp to populate the table:

DECLARE
@sql nvarchar(4000)

SET NOCOUNT ON

CREATE TABLE #temp
(
TABLE_QUALIFIER sysname,
TABLE_OWNER sysname,
TABLE_NAME sysname,
COLUMN_NAME sysname,
DATA_TYPE smallint,
[TYPE_NAME] sysname,
[PRECISION] int,
[LENGTH] int,
SCALE smallint,
RADIX smallint,
NULLABLE smallint,
REMARKS varchar(254),
COLUMN_DEF nvarchar(4000),
SQL_DATA_TYPE smallint,
SQL_DATETIME_SUB smallint,
CHAR_OCTET_LENGTH int,
ORDINAL_POSITION int,
IS_NULLABLE varchar(254),
SS_DATA_TYPE tinyint
)

INSERT INTO #temp
EXEC sp_columns @Table

Once that it done, it is simple to select only the columns that interest me:

SELECT
COLUMN_NAME,
[TYPE_NAME],
[PRECISION],
[LENGTH],
SCALE,
NULLABLE,
COLUMN_DEF
FROM
#temp

. . . but it would be nice to be able to sort the list alphabetically by the field name, so after populating the temp table, I create a unique index based on an argument @SortAlpha to my stored procedure:

EDIT ON 04/09/2009: There was a bug in this part of the code, the alpha sort did not work. Fixed here & also in the download file . . .

SET @sql = 'CREATE UNIQUE CLUSTERED INDEX UQ_temp ON #temp (' +
CASE @SortAlpha
WHEN 0
THEN 'ORDINAL_POSITION'
ELSE 'COLUMN_NAME'
END + ')'

. . .END OF EDIT

EXEC sp_executesql @sql

. . . which causes the ouput to be sorted according to the argument.

Linking Access to SQL Server

I have an Access app that links to SQL Server, and when I tried to attach a new SQL table in code I ran into a snag - the linked table would forget the user name & password, so Access would pop up one of those login dialog boxes.

I compared the value of the table Attributes (a long integer) & discovered that my new table was different. A quick trip to Access Help was truly helpful - I was missing the attribute dbAttachSavePWD so basically here is what worked (the fix is in bold):

Sub SQL_AttachOne(TableName As String)
  Dim db As DAO.Database, tdf As DAO.TableDef
  Const s = "the connection string goes here"
  Set db = CurrentDb
  Set tdf = db.CreateTableDef
  With tdf
    .Name = TableName
    .SourceTableName = TableName
    .Connect = s
    .Attributes = .Attributes Or dbAttachSavePWD
  End With
  db.TableDefs.Append tdf
End Sub

The Or operator sets the appropriate flag for Access to save the info.