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.
I'm not aware of this being possible with SSDT unfortunately.
Depending on how big the project is and how many procedures you intend to enhance with 2012 goodies, it may be manageable with Composite Projects.
SSDT can combine a database project with one or more referenced
database projects or dacpacs to describe a single composite database
schema. Using a composite project allows a large database to be
broken down into more manageable chunks, allows different people or
teams to have responsibility for different parts of the overall
schema, and enables reuse of database object definitions in multiple
databases.
The notion would be to have a base project, containing the common object definitions and version specific projects for procedures that used new features. The 2012 project would reference the base project and a compile/build would combine objects from both.
The PITA would be that you can't override an object in the base project with an object in a composite, so you would have to maintain base, 2008 & 2012 projects. When you wanted a 2012 version of a particular procedure, you would have to remove it from base and create a version in both 2008 & 2012 projects.
Best Answer
There are tools out there, such as this from Redgate, but I have always found that best is to save as SQL files, perhaps even in a Database Project (SSDT?) in your solution.
Along with this, I suggest the following guidelines:
if exists then drop
" at the startYou can initially create these SQL files by scripting directly from SSMS, and you can set SSMS to script all your "
drop
" and "create
" as well as your permissions.