Version control and D, Test, Prod updates
-
Hi all,
I am interested to know how other developers maintain version control in the SQL Server environment. If version control is used, do you manually extract the DDL and add to an application like VSS?
Also, how do people update their changes from Dev to Test and Test to Prod in a controlled manner? I have worked in a number of Oracle environments and we managed this through sql scripts and running these through SQL*Plus.
Looking forward to your replies
Darcy
-
I use Scriptio to generate .sql files for the db schema. Its a free download. http://www.sqlteam.com/publish/scriptio/
On the 2nd tab, I specify One File per Object. Then I use a RE editor to remove the date/time stamp line from the sql files to ensure only 'real' changes are saved in version control. Then I update/commit the changes to CVS.
An improvement would be to script all 3 steps via a batch file or something.
-
Yes, I use SourceSafe to manage all DDL for my databases. I usually just use the Generate Script command, set to 1 file per object, script all indexes and keys, include drops, dump it to a directory, then use the project difference viewer to compare those files to the SourceSafe library. For files that are different, I use check out the file (making sure I set it to not get a local copy), then check it in.
I usually use labels in SourceSafe to mark these files for different production versions. In this way, I can get a list of all files that changed since last promotion to production. Usually this is good enough. But for more complex migrations, I use Red-Gate's SQL Compare (after the SourceSafe update), and have it generate a "change script" to run on the database to be updated.
Hope this helps.
Mark -
For an effective source control solution, it helps to go through and uninstall Enterprise Manager from anyone who will be touching your systems. Otherwise, you get folks who use it for development and updates instead of properly scripting stuff. There's no way anyone knows if they are overwriting anybody else's changes, and people can make changes and easily forget the didn't re-script the objects.
Sure, after they make their changes in EM, they can do the 'script to file' trick, but unless everybody has the exact same settings, and is in the habit of using the exact same file names, (and ensuring they script the file as ANSI text instead of unicode so a text compare can be performed later) then the scripts created by different people for the exact same object can be different, making a search for changes more difficult.
Once everything is in a file, then those files can be checked out, edited in Query Analyzer, saved, and checked back in.
Once you get a version control system in place, then every new object should start as a script file in QA or some other file-based SQL editor. Each file should:
* Include a USE [database] statement if the target will always be the same DB
* Fire all the SET options (ANSI_NULLS, QUOTED_IDENTIFIER, etc.)
* Check for existance, then either DROP the object or raise an error if it already exists
* Create the object
* Create the indexes (Table and indexed view files)
* Procedure and function script files should include, in comments, sample execute statements
* Table and view script files should include sample CRUD statements
* (If you've got expandable keywords turned on) Include a commented $History$ or other keyword-replacement block so the source control tool can insert some date/user stamps
After testing, the file can be saved and checked in.
To manage promotions from dev-> test -> prod, set up different SS projects for each environment. To promote a script from dev to test, do a Get Latest from the dev project and check it in to the test project. Everything will get stamped with date, time, and user, and you can add comments as well. If you're using expandable keywords, and adding "dev", "test", and "prod" to the project names, then each script will contain a complete history of its movement through QA. ("06/05/2006 14:34 - Checked in to $\MyServer\MyApp\Prod\MyDatabase\Tables\dbo.Customer.tab")
-Eddie
Eddie Wuerch
MCM: SQL -
Hi all,
Thanks for your comments. It is good to hear how other organisations/people handle the SDLC in the database world.
I received an email recently about a new MS product, haven't looked at it yet myself but may be useful -
'.....about the brand new product
Visual Studio Team Edition for Database Professionals. The url is below, so go and check it out. The CTP becomes available for download on June 11th, and it's seriously exciting. This gives database developers the chance to have a proper Development Life Cycle.
> http://msdn.microsoft.com/vstudio/teamsystem/products/dbpro/
Cheers
Darcy
-
> http://msdn.microsoft.com/vstudio/teamsystem/products/dbpro/
It's seriously exciting, provided your organization is willing to drop $5,500 per developer/architect/tester/db pro for any single VS Team Edition (architect, developer, or tester; DB pro should be the same price when it ships) or $10,000+ per person for the full team suite.
Those who aren't using VS Team Edition to hit the Foundation Server (such as the project managers hitting it from Project, a necessary component to taking advantage of the system) require a $499 CAL each. The various Team Editions include one Team Server CAL.
Plus the $2,500+ Team Foundation server to which all the above connects, plus the Windows Server, SharePoint, and SQL Server licenses for it, and the beefy hardware to host it all.
Those costs pale to the setup and training costs necessary to take advantage of Team Server.
Nice if you can get it. I'm not railing against it... the whole Team Suite is *really* cool, but properly leveraging it requires a culture - from the CIO on down - that supports proper development processes. They also need the vision to realize that the upfront investment in software, hardware, setup resources, and training will be paid back over time in cost savings from fewer project overruns and outright failures.
Back on Earth, Query Analyzer and SQL Server Management Studio are free (provided you are licensed to access the SQL Server(s) in question, which you also still need even if you drop $10k for VS Team Suite). SourceSafe is ubiquitous.
It's an uphill battle for those shops with cowboy developers, entrenched DBAs, weak project management, or a lack of concept-to-release process discipline.
OK, yes. I am just ranting that my current client is a little challenged in the requirements gathering, design, and project mangement departments.
Eddie Wuerch
MCM: SQL
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply