February 15, 2002 at 12:02 pm
I'm wondering if there are better ways to make changes to a production environment than those I use. Seeing as I only have about 2 and a half years of experience with databases.
For major rollouts of new features I create a script using the scripting feature of Enterprise Manager at the time they are ready to move from our development environment to our staging environment. After creation I execute the script in our staging environment. From then on I maintain the script until time to move the new and modified objects to production and then I execute the script in our production environment.
I normally script tables with primary keys, indexes, and triggers and stored procedures with permissions.
What ways do you use when moving changes to production?
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 15, 2002 at 12:49 pm
I generate a change script using SQL Compare.
Andy
February 15, 2002 at 2:21 pm
February 15, 2002 at 6:12 pm
For changes to inhouse items I use scripts or make the changes directly (usually when it is a handfull of simple things). The worst thing I ever had to do was related to a vendor who wanted me to send them our DB and then they would send me an updated version with our data (was knowledge management data nothing of an issue) and I have to use sp_attach_db, and I found out later that some vendors do it this way when they make updates (I about hit the roof with that).
February 16, 2002 at 11:03 am
Yikes. Can't see doing that. I'd have to strangle someone if they insisted on my sending them a db to update.
Steve Jones
February 16, 2002 at 8:49 pm
Technically since they created the system and we license it, they own the actually db.
February 18, 2002 at 3:59 am
Well I use a Changes Script. Every change that is made to the Database is scripted in this file and Yes always checking with 'if exists'. only 1 or 2 persons are allowed to do a schema level change so they make it sure that change is scripted properly. Just have to send this file to client.
February 18, 2002 at 4:57 am
This is perhaps the easiest for the client. However, given some of the database scripts I have seen coming out of vendors, I am not surprised that a couple are going the approach that Antares has to deal with.
One particular vendor we dealt with sent a script to extract data. It only did a couple of cross joins which generated several hundred million rows. They meant inner joins. Two of the DBAs looked at the script and immediately fired off an email saying, "Are you SURE you want to do that cross join?" The vendor replied back really quickly with a new script.
Another vendor sent a script with a create SQL Server login that has to have dbo rights. This is the same script for all installations of their application. Problem is, it had the sp_addlogin in plaintext (meaning anybody who opened up the script now has the password for said dbo level user). And the password can't be changed. It's hard-coded into the application. So is the user name. Yes, that's a major security hole.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 18, 2002 at 9:42 am
see
Administering SQL Server Release Control
direct link at the moment
http://home.btclick.com/NIGELRIVETT/SQLServerReleaseControl.htm
Cursors never.
DTS - only when needed and never to control.
March 13, 2002 at 6:30 am
This is a difficuilt process to see through as different types of installations usually need their own release processes defined.
What I do is, have every single database object scripted (Tables, defaults, checks, PK's, FK's, views, triggers, sprocs etc.) located in their own folders in SourceSafe. SourceSafe stored scripts MUST be the master code. By this I mean, if a database object gets changed it has to be changed in SourceSafe and a release script containing the changes needs to be applied to the database server.
Along with this, I have utility sprocs which would deal with setting permissions, dropping foreign key constraints, rebuilding indexes etc.
So, when I make changes or add new database objects, I build a seperate release script that gets labelled in SourceSafe when ready for release. A typical release script is made up of all the individual object scripts. The labelling process in SourceSafe is done as follows:
The release script gets labelled e.g. SYS.R01.001. Then each individual script that makes up the release script also gets labelled so you have the history of the release script make up. Although rolling back a change is not really an option so you may not want to label each individual script but when managing 100's of databases, the history as to why and when changes got made I find extremely useful.
Deon
March 13, 2002 at 6:48 am
We began using SourceSafe in Oct 2001. However, we only use it for stored procedures and for rollout scripts. I use the difference feature to analyze differences and we also use it for historical information.
Isn't it difficult and time consuming to maintain a script that is up to date for every object in your 100's of databases?
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
March 13, 2002 at 7:28 am
No, maintaining idividual scripts I find is the only sensible way of keeping control over your databases. How the system is supposed to work is, developers make object level changes and label the idividual objects. I am told about these changes and I formulate the release script from the changes list. Because the release script building is done through one "gatekeeper" you know what is going on and how this may effect other areas outside the scope of the developers.
I am in the process of writing my own Script building utility that generates release scripts by extracting all effected objects in SourceSafe (using the label number). Currently, it can build about 80% of all release scripts for me with a bit of manual work. Anyway, I give the release script the once over but the true test is when it gets deployed to the TEST environment.
Using this feature of SourceSafe also allows your developers to continue making changes to the objects because you extract a labelled version.
Hope that makes sense?
Deon
March 13, 2002 at 7:34 am
Hmmm... It never occured to me that I could access the information in SourceSafe and generate scripts from there.
What we have been doing is forcing the developers to place changes into SourceSafe before moving them to our testing environment. However, if they ask us to move more than one or two we script from the development environment using Enterprise Manager after spot checking that they have made changes to SourceSafe.
How are you getting the info out of SourceSafe to build your scripts?
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
March 13, 2002 at 7:58 am
There is a util in the Resource kit that does basically what you're talking about. I've also an a small utility that just applies all the scripts in a folder, nothing fancy, but handy sometimes.
Andy
March 18, 2002 at 7:54 am
New to list, but deal with this situation on a day-to-day basis. I would be interested in seeing how SourceSafe is being incorporated into this process. We currently use the methods described above (developers keep track of changes made to test databases in another database/spreadsheet and I create and control all scripts to make changes to the production DBs).
I would love to further "automate" and better control this process. Can anyone more expound, or point me to documentation or explanations of how to use SourceSafe to build these scripts? (We only use SourceSafe for code and scripts that have made it to production for clients).
Thanks to all,
Patrick Purviance
Associated Systems, Inc.
Wichita, KS
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply