November 12, 2012 at 1:17 pm
I am a new DBA for a small database project that uses SQL Server Management Studio 2005. I have been asked to implement some sort of Configuration Management strategy such that each time a change is made to a table (or maybe the Database as a whole) the change is made and assigned a revision number such that if we needed to get back to that version I could make it available.
Now, I have described it the way the team has described it to me. I realize they are thinking of it kindof like a code project, but I think you all get the picture. I realize that what they get will not be exactly what they are asking for, but I need some ideas on how to approach this.
I am asking the forum to get me started with some strategies they are currently using.
I appreciate all your thoughtful responses. My existence here is sort of hanging in the balance.
Tim
November 12, 2012 at 2:20 pm
Yes, they're basically thinking of versioned deployments. This is achievable but will require you to approach design slightly differently.
My personal preference is feature based rollout/rollback scripts. For each module or feature (or ticket, depends on how granular you want to be) you create a rollout script for that version. You also generate a rollback script to return all the components to how they were. Now, this gets tricky for shared procs and you end up with dependencies, so it's a lot of paperwork/overhead.
Another method would be the equivalent where you have a single rollout script per version, and then you'd have a rollback script that would reverse the version.
The reason I prefer these methods is because you have defined all the components within a rollout and can reverse any significant data modifications you've made. The problem with the rest of the methodologies is that data reversal becomes difficult at best.
Another way of performing these tasks is to simply script out your database on each rollout. This will allow you to build the database in another another shell then use a third party tool, like SQLCompare, to find the differences and create scripts to rollout the version changes.
Yet another way is to use continuous integration type scripts. This is my least favorite option as you have to write a ton of conditional code to make sure you're not duplicating old work every pass. For example, you don't just drop/recreate a clustered index if you had to adjust it 30 versions ago, you need to check that the columns and inclusions are there, the columns are in the right order, it's on the correct filegroup... etc. I find this the most painful option.
There's nothing that will automatically 'bump' a version number for the db, however, nor anything integrated into SQL that allows for it. You will have to use third party tools, you will require a change of business processes, and you will need time for organization and administration of whatever method you decide to use.
Databases don't fit into a codeproject easily simply because of data persistance. The function/proc level fits in just fine, really, as long as there's no schema changes. Once you start dealing with schema changes you really have to dig into it and decide on how you want to approach adjustments to persistant data.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply