Moving code to production
-
I've finally gotten things under control, with code for all SQL objects in VSS, and locking developers out of the production system.
Now I need a way to compile stored procedures into production at certain times of day (off hour moves). As I do not particularly like to be here late at night just to do this I am trying to come up with an automated way to run scripts (alter/create procedures and functions, table changes I like to be here for and don't happen very ofter). Anyone have any ideas for how to best do this?
-
SSIS or an OSQL run from Task Manager - if the later, be sure to redirect the output to a log file so you can verify everything happened as you planed.
-
I don't think there is an automated way but VSS keep track of .sql files moved into it from your last deployment but you still need to send the excel file to your developers tech lead so the person can email the list to the developers so all can verify their code is in the list. That way all with modified stored procs that are not checked in will be checked in so the tech lead can send you the list of what needs to be copied. That is how we do it in my last team.
Kind regards,
Gift Peddie -
First, very high congratulations on establishing full control of the production system.
So far as using an unattended system to install the procs in the production system goes... I wouldn't do it unless you have a QA department that absolutely guarantees that nothing will go wrong... go wrong... go wrong... <boink> go wrong while you're not looking at the runs.
That, not with standing, a simple batch job that reads filenames from a directory (FOR loop), executed by OSQL for each file name, and the correct use of Windows Task Scheduler would be one easy way... once written, the batch job would never need to be changed. You just need to move the proc code files into the staging directory and you're done.
Another (slightly more intensive manually) way to do it would be to open the code (all of it) in a single query analyzer window in the correct order of execution... add a WAIT FOR TIME at the very beginning of the code and you're done.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Any feedback, Anders?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Sorry took the weekend off completely! (i.e. my home computer had a meltdown..)
I personally agree that it is better to be here to do it. Or at least have some way for the developers to move approved code in when it is ready. Was my boss asking for some automated way to do it, since I am still here as a consultant, he don't want to pay for me being here after hours unless we have an emergency.
I think for now what I am going to do, is make a SQL login that I can give DBO rights to the databases as needed, but keep the password secret until someone need to move code in after hours. Then after they are done just change the password.
We have a very weak QA group, i.e. it is mostly the developers themselves. I'm working on making a better QA environment, but that is taking some time.
I work for what used to be a small company, but that has grown considerably in the last 5 years. There are few formalized plans for how things are done, heck, I'm the first DBA the company has ever had in it's 10 year history, so I am just now after 9 months starting to get the environment under control. Fun fun!
-
Heh... you just got control of the situation and now you're going to "have some way for the developers to move approved code in when it is ready."???
Having developers promote their own code without a DBA having a chance to look at it is inviting disaster especially since you have a weak QA department. If you want control, keep control... you tell them what day and time non-emergency changes go in and be available for emergency changes at night... just tell people to make bloody sure it's an emergency before they call. Yeaup... you'll probably have to get management backing for that but it's what DBA's do...
Protect the database, the server, and the data. --Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
LOL yeah the situation here is not ideal. Obviously not going into much detail on a public forum....
The good thing is the first thing I did after getting SQL 2005 up was to get a DDL trigger in that alerts me to any changes, so I at least CAN keep track of what they do.
I'm working on getting them all up to speed on proper production control, but it is a long uphill battle (one that I will win though).
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply