In a VS environment, I've always used database projects to implement the update scripts. I tend to use unimaginative names like "DatabaseUpdate17.sql" or "PriceUpdateFebruary2010.sql" for my scripts. Having them as database projects lets me tie them to Team Server tasks, bugs (and if we did code reviews, to them as well). I also include in each database (that I have authority over) a table specifically for the collection of changes to the schema.
CREATE TABLE [dbo].[AuditDDL](
[EventID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[EventData] [xml] NULL, -- what did they do
[EventUser] varchar(100) NOT NULL, -- who did it
[EventTime] [datetime] DEFAULT (getdate()) -- when did they do it
)
GO
Well, that takes care of 3 of the 6 Ws.
CREATE TRIGGER [trgAuditDDL]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
INSERT INTO AuditDDL(EventData, EventUser)
SELECT EVENTDATA(), original_login()
GO
I include an insert statement to log the beginning of a patch as well as the end of a patch. Events happening outside of patches are things to look into.
For example, a "begin patch" insert for "patch 17" would look like:
INSERT INTO [dbo].[AuditDDL]
([EventData]
,[EventUser])
VALUES
('<EVENT_INSTANCE><EventType>BEGIN PATCH 17</EventType></EVENT_INSTANCE>'
,ORIGINAL_LOGIN())
GO
Since it also catches when indices are rebuilt, you'll need to run the following every month or so to clear out those events:
DELETE FROM AuditDDL
WHERE [EventData].exist('/EVENT_INSTANCE/EventType/text()[fn:contains(.,"ALTER_INDEX")]') =1
GO
DELETE FROM AuditDDL
WHERE [EventData].exist('/EVENT_INSTANCE/EventType/text()[fn:contains(.,"UPDATE_STATISTICS")]') =1
GO
Earlier version previously posted on Server Fault.
In a SOX and PCI-DSS compliant environment, you will never have access to the production servers. Therefore the scripts need to be clear and exercised beforehand. The comments at the top of the update scripts include lists of new tables, stored procs, functions, etc as well as lists of modified tables, stored procs, functions, etc. If data gets modified, explain what is being modified and why.
A secondary problem is that you sometimes end up needing to manually coordinate changes if two separate tasks change the same database object. This may just be the way it is but it still seems like there should be some automated way of "flagging" these issues or something.
I've never come across a tool that lets us track this automatically. Previous employers used a principle of "database owner" - one and only one person who is personally in charge of the database. This person won't be the only developer working against that database, but rather all changes have to go through them. This has worked reasonably well to keep changes from colliding and damaging each other.
Interesting mess. Multiple options if you're looking for model refactoring (recommended). If it's not the case/there are time constraints you can try to exploit EXEC. There's neat feature that is not very popular (maybe it's good). Core concept:
DECLARE @cmd NVARCHAR(4000)
SET @cmd = N'dbo.YourProcedure_v11'
DECLARE @retcode INT
EXEC @retcode = @cmd @Param1 = 1,
@Param2 = 2,
@ParamN = 'N'
You have to create stub procedure ('master' accessed from DAL) and call your procedures depending on user e.g.:
CREATE PROCEDURE dbo.YourProcedure
(
@Param1 INT,
@Param2 INT,
@ParamN CHAR(1),
@UserID INT
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @cmd NVARCHAR(4000)
IF @UserID = 1
SET @cmd = N'dbo.YourProcedure_v11'
ELSE
SET @cmd = N'dbo.YourProcedure_v12'
DECLARE @retcode INT
EXEC @retcode = @cmd @Param1 = @Param1,
@Param2 = @Param2,
@ParamN = @ParamN
RETURN @retcode
END
Instead of passing UserID every time/to all procedures - you can use suser_sname
etc depending on your security model. Also user-version mappings should be somewhere in database - stored procedure names are hardcoded only for testing.
Best Answer
That's a big topic. The tactics depend in part on how big a change you're making. The strategy is that every move to production breaks nothing. Loosely speaking, that means
That equates to one to three moves to production. (I prefer three, with a prudent amount of time between them. Other developers might have more appetite for risk than I do.)
The most accessible online document might be Evolutionary/Agile Database Best Practices. It's relevant to some extent regardless of whether you follow agile practices. Look at Database refactoring and Agile data modeling first.
That depends on what kinds of problems you uncovered, how you documented them, and whether the TSQL code is under version control. Writing TSQL is software development; all the principles of good software development apply.