May 6, 2010 at 2:03 pm
Jon McGhee (5/6/2010)
Wow, that's really labor intensive! Compare that to the integration between a visual studio c# project and visual source safe, where the checkout/checkin can be done with a mouse click.
Not really.
I guess it might be labor intensive if that's all you did all day. But if we're talking several files a day maybe a minute or two maximum?
I haven't used visual studio for a number of years but the J++ and C++ versions were slow and clunky. The VSS integration was shaky and the PVCS Tracker integration was worse.
I find the SSIS version different enough that they aren't that comparable but I don't think it supports managing non SSIS very wel such as deployment and packaging that I would send out to a customer. As such I am using that sort of checkout anyway.
I still support some stuff that uses the old MS Java VM as well. The only thing I use that visual studio for the compiling. Their editor isn't worth the headache.
May 6, 2010 at 2:09 pm
Great job Mike! This will really help us.
I’ve been looking for something like this. Now we can export our schema setup to a folder every night and fire off an SVN commit afterwards if there are changes. This would help keep a constantly versioned set of scripts for our databases.
Up til now I'm the only dba in our company that does this (there are three of us) and only when I take the time. This way we can not only do it for our own internal databases but all those maintained by our 3rd party software. Occasionally upgrades break our own scripts and having tools like this would allow us to quickly do diffs on the scripts to find the exact changes.
Thanks!
May 7, 2010 at 1:53 am
Andy Llewellyn (5/6/2010)
We use RedGate SQL Compare to create all the scripts for a DB and this structure is then held in Subversion.We then develop using QA (or whatever suits the developer).
When we need to commit back to the branch then the first step is to compare the script folder with the development DB and synchronise any scripts that need it. Then you commit to subversion in the normal way.
We find this works well for our needs at the moment.
Andy,
Have you had a chance to try SQL Source Control? This should save you the additional steps of having to use SQL Compare to compare your dev DB to a scripts folder and commit to SVN, as it integrates this as a single action in SQL Server Management Studio. It's in early access at the moment, so I wouldn't recommend relying on it in a production environment, but I'd really welcome any feedback on the tool and the workflow.
http://www.red-gate.com/products/SQL_Source_Control/index.htm
David Atkinson
Product Manager
Red Gate Software
May 7, 2010 at 3:04 am
Thanks for +ve comments,
Anyone interested in sample code of what we did, take a look here
http://www.mediafire.com/?eim2mydnjti
Disclaimered as you'd expect.
Regarding the ongoing debate about "proper" source control usage, I think a lot of people are indeed missing something. I haven't seen anyone explain adequately how they prevent and detect accidents when rolling out changes to their production servers (e.g. authorized changes by dbo level users), nor how they can prove that what is in their source repository is exactly and only what is in their production environment.
Another example of where this sort of automation is useful is that by scripting to a folder and running a compare against a baseline, you can evaluate the performance and accuracy of a rollout by dba staff, and also have an exact list of changes made in a rollout that you can append to your change control schedule to show that what was meant to be changed is exactly what was changed. This captures those last minute "saves" or "tweaks" that would otherwise be invisible.
Finally, this also allows you to capture changes to more esoteric areas that in some environments are considered beyond the scope of a sql developers source control and falling into the reactive dba world, e.g. tweaked fill factors on indexes, statistics, etc, and can capture objects and settings that might not normally be created via a script (for example, mail profiles).
I wouldn't call the process here particularly onerous- I wrote the attached scripts in a day without extensive powershell experience.
May 7, 2010 at 6:15 am
mike.renwick-894639 (5/7/2010)
Thanks for +ve comments,Anyone interested in sample code of what we did, take a look here
http://www.mediafire.com/?eim2mydnjti
Disclaimered as you'd expect.
Regarding the ongoing debate about "proper" source control usage, I think a lot of people are indeed missing something. I haven't seen anyone explain adequately how they prevent and detect accidents when rolling out changes to their production servers (e.g. authorized changes by dbo level users), nor how they can prove that what is in their source repository is exactly and only what is in their production environment.
There are no guarantees in life. But, in an attempt to meet that level of assurance you're asking for, we only deploy our code out of source control and access to databases past development are controlled. There is no dbo level access except from the DBA's. I wouldn't say we're at 100% compliance across all databases, but we're doing very well and have a high, call it 99%, assurance that what we think is in production, is in production.
Another example of where this sort of automation is useful is that by scripting to a folder and running a compare against a baseline, you can evaluate the performance and accuracy of a rollout by dba staff, and also have an exact list of changes made in a rollout that you can append to your change control schedule to show that what was meant to be changed is exactly what was changed. This captures those last minute "saves" or "tweaks" that would otherwise be invisible.
Finally, this also allows you to capture changes to more esoteric areas that in some environments are considered beyond the scope of a sql developers source control and falling into the reactive dba world, e.g. tweaked fill factors on indexes, statistics, etc, and can capture objects and settings that might not normally be created via a script (for example, mail profiles).
I wouldn't call the process here particularly onerous- I wrote the attached scripts in a day without extensive powershell experience.
I agree as far as automation goes. Also, we make it a point of retaining the scripts that were generated for the final rollout to production, in case there's any question in the future what it was that got deployed.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 7, 2010 at 6:51 am
Makes sense.
Of course, I will add that meeting that level assurance is more important in our environment where there are only 3 IT professionals running the databases, none strictly speaking a pure-dba type (perhaps only one with any dba experience at all... no prizes for guessing who). Unfortunately it's impractical for me to be the only person to roll out scripts, as I'm not always in the office, so these sorts of setups are extremely beneficial in our smaller shop, where a high level of assurance is required, but there aren't the staff 🙂
May 7, 2010 at 8:26 am
Grant Fritchey (5/7/2010)
Also, we make it a point of retaining the scripts that were generated for the final rollout to production, in case there's any question in the future what it was that got deployed.
When we release to an environment we 'label' the files in PVCS so at a later date we can get any release that has gone into any environment just by getting the files by the relevant label.
May 7, 2010 at 8:51 am
jacroberts (5/7/2010)
Grant Fritchey (5/7/2010)
Also, we make it a point of retaining the scripts that were generated for the final rollout to production, in case there's any question in the future what it was that got deployed.When we release to an environment we 'label' the files in PVCS so at a later date we can get any release that has gone into any environment just by getting the files by the relevant label.
We cut a branch (we use Perforce) at "code freeze". Contained in that code branch is the application code (rich client, web tier, BLL, database changes, etc.) The schema creation (for new customers/databases) and update (for existing customers/databases) SQL scripts are then used for QA and eventual deployment. We also version stamp the schema in each database. So the upgrade script will fail right out of the gate if it is being applied to a database that is not the correct schema version. ALL changes to the database are done via scripts. So we know exactly what is running in production.
May 7, 2010 at 8:59 am
jacroberts (5/7/2010)
Grant Fritchey (5/7/2010)
Also, we make it a point of retaining the scripts that were generated for the final rollout to production, in case there's any question in the future what it was that got deployed.When we release to an environment we 'label' the files in PVCS so at a later date we can get any release that has gone into any environment just by getting the files by the relevant label.
Yep, same process here with TFS.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 7, 2010 at 9:50 am
mike.renwick-894639 (5/7/2010)
snip... how they can prove that what is in their source repository is exactly and only what is in their production environment. ...snip
One automatic way to do this is through database-level DDL triggers. That is, for every change there is a trigger that saves the code obtained from EventData(). The DDL trigger inserts these into an external db, which also collects similar changes from several databases. Then it's a matter of matching the contents of the field that stores /EVENT_INSTANCE/TSQLCommand of EventData() with the source.
See an earlier post on this thread for code showing a simple DDL trigger example using EventData().
This solution will have the effect of limiting the source to be one file for each object in the db. There also has to be some consistency in naming db objects and their corresponding files in the VCS. Comparing in SQL itself is slow unless we use CLR routines. CLRs can do fast DIFF on source file and the contents of that field and produce a nice table of all objects in production that differ from the source.
This method will also have the benefit of using a variety of VCS and not tied to a specific product. If you have multiple version control systems, then create one CLR for each to encapsulate that VCS's diff commands. Within each VCS also there could be different ways to compare: binary, line-by-line, UNICODE, etc. You can encapsulate these as well in the CLR. At various times, we had to integrate VSS, SVN, GIT, and Mercurial. Each time only the CLR needed tweaking while keeping the rest of the SQL side and VCS side unchanged.
We can also answer the question: which version of the source matches the production? How many changes were made since then? Which objects had most changes? Since all these are tied to dates and times, simple undo and redo can be implemented as well.
May 7, 2010 at 10:17 am
I've not come across DDL triggers before- they look quite interesting and a relatively sensible place to capture changes.
I can see these being helpful for evaluating roll-out accuracy. I think my concerns with this approach would probably start to split hairs, off the top of my head, they wouldn't capture anything that lives outside of a database but within the database server (mail profile and accounts, sql server jobs, linked servers for example), or indeed the databases themselves. Moreover, triggers are somewhat simple to disable.
In our setup, we weren't as concerned about being able to undo or redo changes automatically- I guess we're a simpler setup. Also, our VCS integration is significantly more basic than the one you outline here. Mostly we're satisfied if what's in the baseline matches what comes out the source extract.
May 7, 2010 at 10:29 am
mike.renwick-894639 (5/7/2010)
I've not come across DDL triggers before- they look quite interesting and a relatively sensible place to capture changes.
I've written this trigger to record when DDL changes were made to a table, it's quite useful.
The article I used as the initial source is here: http://www.databasejournal.com/features/mssql/article.php/3685661/Monitoring-Changes-to-your-Database-Schema.htm
If you look at the xml EVENTDATA() there is also the full create statement for the stored procedure under the value /EVENT_INSTANCE/TSQLCommand/CommandText.
-- ************************************************
-- TABLE DDLChangeCatalog
-- DDLChangeCatalog table used by the trigger:
-- ************************************************
DECLARE @ObjectName AS varchar(128)
SET @ObjectName = N'DDLChangeCatalog'
IF NOT EXISTS(SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @ObjectName)
BEGIN
CREATE TABLE dbo.DDLChangeCatalog
(
EventType nvarchar(100) NOT NULL,
SchemaName nvarchar(100) NOT NULL,
ObjectName nvarchar(100) NOT NULL,
ObjectType nvarchar(100) NOT NULL,
EventDate datetime NULL,
SystemUser nvarchar(100) NULL,
CurrentUser nvarchar(100) NULL,
OriginalUser nvarchar(100) NULL,
CONSTRAINT PK_DDLChangeCatalog PRIMARY KEY CLUSTERED
(
ObjectName ASC,
ObjectType ASC,
EventType ASC,
SchemaName ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
)
EXEC sys.sp_addextendedproperty @name=N'Summary', @value=N'This table stores DDL changes to the database server' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=@ObjectName
EXEC sys.sp_addextendedproperty @name=N'Used By', @value=N'change monitoring' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=@ObjectName
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'EventType' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=@ObjectName, @level2type=N'COLUMN', @level2name=N'EventType'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'SchemaName' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=@ObjectName, @level2type=N'COLUMN', @level2name=N'SchemaName'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ObjectName' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=@ObjectName, @level2type=N'COLUMN', @level2name=N'ObjectName'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ObjectType' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=@ObjectName, @level2type=N'COLUMN', @level2name=N'ObjectType'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'EventDate' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=@ObjectName, @level2type=N'COLUMN', @level2name=N'EventDate'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'SystemUser' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=@ObjectName, @level2type=N'COLUMN', @level2name=N'SystemUser'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'CurrentUser' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=@ObjectName, @level2type=N'COLUMN', @level2name=N'CurrentUser'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'OriginalUser' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=@ObjectName, @level2type=N'COLUMN', @level2name=N'OriginalUser'
END
GO
IF NOT EXISTS(SELECT *
FROM sys.triggers
WHERE [Name] = 'ChangeDDLCatalog')
BEGIN
EXEC ('CREATE TRIGGER ChangeDDLCatalog ON DATABASE FOR DDL_PROCEDURE_EVENTS AS SELECT 1')
END
GO
-- **********************************************************************
-- This DDL trigger uses the "DDL_DATABASE_LEVEL_EVENTS" event to identify when this trigger should fire.
-- This event will fire whenever any CREATE, ALTER, or DELETE command is executed against any object
-- in the database.
-- This trigger makes use of a series of EVENTDATA() function calls to identify information about
-- which object, and event was used to modify the database. Once all the pertinent data is gathered
-- this trigger then inserted the collected data into the "DDLChangeCatalog" table.
-- $Revision: 1.0 $
-- **********************************************************************
ALTER TRIGGER ChangeDDLCatalog
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
SET NOCOUNT ON
DECLARE @EventType nvarchar(100)
DECLARE @SchemaName nvarchar(100)
DECLARE @ObjectName nvarchar(100)
DECLARE @ObjectType nvarchar(100)
SET ANSI_PADDING ON -- This is needed for when subscriptions are added.
SELECT @EventType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)'),
@SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)'),
@ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)'),
@ObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)')
IF @EventType IS NULL
SET @EventType = N'Unknown'
IF @SchemaName IS NULL
SET @SchemaName = N'Unknown'
IF @ObjectName IS NULL
SET @ObjectName = N'Unknown'
IF @ObjectType IS NULL
SET @ObjectType = N'Unknown'
-- Is the default schema used?
IF @SchemaName = ' '
BEGIN
SELECT @SchemaName = default_schema_name
FROM sys.sysusers U
INNER JOIN sys.database_principals P
ON U.uid = P.principal_id
WHERE U.[Name] = Current_User
END
--END IF
IF @SchemaName IS NULL
BEGIN
SET @SchemaName = 'Unknown'
END
--END IF
IF EXISTS(SELECT 1
FROM dbo.DDLChangeCatalog
WHERE EventType = @EventType
AND SchemaName = @SchemaName
AND ObjectName = @ObjectName
AND ObjectType = @ObjectType)
BEGIN
-- Update existing row
UPDATE dbo.DDLChangeCatalog
SET EventDate = GetDate(),
SystemUser = SUser_SName(),
CurrentUser = Current_User,
OriginalUser = ORIGINAL_LOGIN()
WHERE EventType = @EventType
AND SchemaName = @SchemaName
AND ObjectName = @ObjectName
AND ObjectType = @ObjectType
END
ELSE
BEGIN
-- Update new row
INSERT INTO dbo.DDLChangeCatalog
(
EventType,
SchemaName,
ObjectName,
ObjectType,
EventDate,
SystemUser,
CurrentUser,
OriginalUser
)
VALUES
(
@EventType,
@SchemaName,
@ObjectName,
@ObjectType,
GetDate(),
SUser_SName(),
Current_User,
ORIGINAL_LOGIN()
)
END
--END IF
END
GO
May 7, 2010 at 10:42 am
Looks like your trigger captures more than just table changes, which is cool - presumably all sorts of alter/create statements include sprocs would find their way into that audit trail.
I like it for active monitoring- obviously the drawback of our setup is that it's a scheduled job and runs daily so all manner of nonsense can happen in between. Might try and implement this on our dev box and take a look at the detail in the xml events generated.
We do like the SMO stuff though, especially using a generic scripter to script a table, index, sql server job, linked server, assembly, without having to really know too much about it. Something quite clean about the powershell approach.
Thanks!
May 7, 2010 at 12:32 pm
Have you thought about the default trace as schema changes are already recorded there and easily available without the need for a ddl trigger. In fact Microsoft provides a schema change history report available as part of the basic installation. Why re-invent the wheel?
Again DDL triggers are part of the toolset and available to be used when most appropriate but a bit overkill to use a DDL trigger to get information on something that is already directly available.
This has also been documented in a good article from 2008
http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/
May 10, 2010 at 1:48 pm
For those of you who are willing to pay a bit, there's a product that does just that:
http://www.nobhillsoft.com/Randolph.aspx
install and setup is quick. more than just version control for the entire schema (tables included) it also provides clear reports, search through history, script at any point in time, rollbacks, email aleerts when things change, pushing schema into sourcesafe, subversion, TFS, and more...
Viewing 15 posts - 61 through 75 (of 76 total)
You must be logged in to reply to this topic. Login to reply