| Author |
Topic |
|
TimS
Posting Yak Master
198 Posts |
 Posted - 2006-01-19 : 16:29:14
|
| To All:Anyone using or know of good software to use for SQL Change Management?I am NOT talking version control software; we are already using Visual SourceSafe 2005. We are spliting our job functions and need software to promote to production from the test/QA databases.Note: I wanted to just give the Production people an batch file and some sql files that would be ran using SQLCMD commands in the batch file.Thanks AllEdit: Production & QA are SQL 2000 Ent/Dev Editions with either SP3 or SP4 on Windows 2003 ServerTim S |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-01-19 : 19:32:58
|
| Are you talking about a product like SQL Compare by Red Gate? If so, I'd highly recommend that product. We do not use it to create our normal scripts as we know what has changed in those environments. We use for environments where we aren't sure what changes need to be applied to get it in sync with a different version. For normal development to test to production, the DBA should script out the changes in development, apply those to QA for testing, then using those same scripts, apply them to production. If you have already deployed to QA, then you should already have the scripts ready. I feel like I'm talking in circles, so I'll end here.Tara Kizeraka tduggan |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-01-19 : 19:50:24
|
| I'm with Tara. If you need to Sync up databases, SQL Compare is GREAT! There are a few products that do this, but I've own SQL Compare and swear by it.Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda> |
|
|
|
TimS
Posting Yak Master
198 Posts |
Posted - 2006-01-19 : 20:13:42
|
| I think like Tara and I should just use the same scripts on the Production upgrade as on the QA upgrade, but my manager thinks it should be a GUI-like app for the Production people who are NOT sql developers to use to promote the changes from QA to Production.Tim S |
|
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2006-01-19 : 22:45:16
|
| Tim, are you saying the production DBAs don't know much about SQL? That will be interesting...:) |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2006-01-20 : 04:07:51
|
Sounds like your Production DBA's are automated monkeys....a bit like management here  |
|
|
|
TimS
Posting Yak Master
198 Posts |
Posted - 2006-01-20 : 09:09:58
|
| There is four people in the group who will be in charge of promoting to production; only one knows only SQL and the process must be able to be done by the other 3 people.Tim S |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-01-20 : 11:52:01
|
| Tim, I hope your "DBA's" test their backups regularly :)<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda> |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-20 : 12:12:38
|
Our process is as follows:Every change to the DDL is scripted (we actually use Enterprise Manager : Design Table, but then use the "Create change script" button and abandon the changes, rather than letting E.M. actually make them).The script-lett is added to a "Patch" script. Patch Scripts are sequentially numbered. Thus we can run "all patch scripts, in order" to get any database from its current version to a newer version.We also store any data-change script stuff in the Patch scripts (e.g. if we need to split a table out into two, or globally set all NULLs in a column to a new default value, or whatever).Secondly we store all SProcs and Triggers in individual files. When we make a release we concatenate [into a single file] all Sproc files with a [file system] modified-date more recent that the previous "release" [in practice this is just a "branch" in our Version Control system]So we run a Patch script followed by an Sproc "update script". If the target database is more than one version "stale" then repeat for all intervening versions.The sequence of creation of the Sprocs is important if you need sysdepends to be accurate. I think its a bit of a waste of time to try to achieve that, but when we first run the Update Script on the QA database we duplicate [at the end of the Update Script] any Sprocs that give "sysdepends warnings" - so they, in effect, run twice and that sorts the problems [very occasionally we need to run them three, or more, times to overcome all sysdepends warning].So then we just have to run the scripts, in the right order, to bring a database up-to-date.The scripts could obviously be bundled into a GUI tool, or even an XML feed from a central "version management" server  to de-skill the task. We prefer to have a DBA watch over it in case anything unexpected happens.Kristen |
|
|
|
DustinMichaels
Constraint Violating Yak Guru
464 Posts |
Posted - 2006-01-20 : 12:52:33
|
quote:
Originally posted by TimS
There is four people in the group who will be in charge of promoting to production; only one knows only SQL and the process must be about to be done by the other 3 people.Tim S
Thats pretty scary. How the heck do people with no SQL skills get to be in charge of making changes to your companies production server. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-01-20 : 13:22:13
|
| Your manager is absolutely wrong. Part of the testing effort in QA is testing the scripts to upgrade the database. If you aren't going to use the same scripts, then you can't give a guarantee that everything will work. Most of the GUI tools will have issues occassionally. It's not every time that I run the software, but occassionally the script that the GUI tool generates fails. You wouldn't want this to fail in production. We only use a GUI tool for non-production environments where we aren't sure what version of the database we are at. Plus we can afford for a script to fail and not impact anyone.Tara Kizeraka tduggan |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-01-20 : 14:09:28
|
| Yeah, SQL Compare generally makes great scripts, but I ALWAYS test the scripts to make sure they are good before running them on production environment.Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda> |
|
|
|
TimS
Posting Yak Master
198 Posts |
Posted - 2006-01-20 : 15:27:28
|
quote:
Originally posted by MichaelP
Tim, I hope your "DBA's" test their backups regularly :)<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>
I am lucky that the NT system people do the backup and they have good system in place. They test backups sometimes and have restored to test server the production backup to recover data that was deleted in other SQL database systems.I think I am going to work on convincing my manager to use a batch file that uses the sql files about 7 total max sql files to do the promotion to production. I think I will need to create a copy of production to test my scripts on as the last test before giving the batch and SQL files to the production people.I currently use a two batch files that run two sql files ( created by running a batch file on several sql files) to promote to test & production. Tim S |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-21 : 05:10:04
|
| "I think I will need to create a copy of production to test my scripts"You'll find that "staging" step invaluable.We have a product that is used by a decent number of customers, so we get the chance to roll out an Upgrade more than once. Its probably harder to convince a non-IT manager for a one-time deal.However, you ought to have DEV, QA and PRODUCTION systems at the minimum, so that's 2 lots of roll-out.Here we aim to roll-out to QA several times a week (the QA process is about 99% automated), and we often come-from an old version. We plan to get the QA system to checkout whatever scripts it needs from the Version Control system, and then run the tests, autoMagically every night. Haven't quite got to that stage yet, but we are getting close.We then have TEST systems for each client - so a TEST database and a PRODUCTION database. The client gets to make their acceptance tests on the TEST system, and then we put the changes onto their PRODUCTION database. (TEST and PRODUCTION are on identical hardware, DEV and QA are not).So given our pattern preparing specfic Scripts for each roll-out means that they do get well used and shaken down!Maybe you can introduce more steps in the process to make a more cost efficient proposal to management? Kristen |
|
|
|
ward0093
Starting Member
15 Posts |
Posted - 2006-02-14 : 09:13:38
|
| Kristen... I have a issue here with Updating Databases in the field.We are a small team of Windows based developers and SQL Server Databases running locally on our client machines... I biggest problem is updating the Stored Procs in the database when we update the application.With what you have described, is there a way to fire off those Patch Scripts from our Code (when our application runs for the first time after an update)?Theres is absolutely nothing on the web on how to do this... if you have a way... you should post it up there... I am sure there is more than just myself trying to figure this out.Thanks,ward0093 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-02-14 : 11:15:27
|
| I suppose your application could "check from central source" to see what the current Version is. And if your local version is stale then download and run a script to update it.Lets assume you concatenate all scripting changes since the previous version into a "Version 2" script. You could store that on the central system (either as a file, or in a database table), and update the "Current Version Number". The script would need to contain (as the final statement) something to update the version number; so running the script would apply all the latest Sprocs, and then update the version number of that machine.I suspect it would be better to write some sort of harness that could get all the Sprocs to be change via, say, XML; run each one in turn; perform error checking; roll back if an error was encountered; update the version number only if no errors detected.Kristen |
|
|
|
ward0093
Starting Member
15 Posts |
Posted - 2006-02-14 : 12:35:24
|
| Correct! That is exactly what I am trying to do!However... How do I read that file and fire off the Script (inside that file) from my code (vb.net 2005)?Should I create a Stored Procedure (a TSQL Standard SProc) that is dedicated to reading a XML file and updating the central datastore? Or should I somehow read it in using code and passing the contents of the script file to the database via a connection and dynamic script manipulation? Are there tools I can use or is there a way to interact with SQLCMD or SSEUTIL from my code to fire off those scripts?I think that is the missing link? the fact that I am not sure how to fire off those scripts!what do you think?ward0093P.S. The whole point is to automate the Database Update routines via our application. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-02-15 : 02:29:17
|
| You can run the SQL scripts using OSQL (command line utility), or you can just fire them down your ADO connection at the server! [needs "god" login permissions, compared to normal applications, to change DDL etc. of course]OSQL can be triggered from within SQL Server itself - either as a scheduled task, or using xp_cmdshell. You could probably do:What's my current version? (say "5")Run OSQL to get script for version 6 [which may not exist]If the version is no longer = 5 then LOOPor better still:Get latest number version from central sourceWHILE LatestVersion > CurrentVersionRun Script For (CurrentVersion + 1)LOOPOSQL could probably do:OSQL -i \\MyCentralServer\MyShare\MyScriptVersion_6.SQL ... userid/password/DB/etcKristen |
|
|
|
|
SQL Change Management Software?