March 21, 2008 at 10:15 am
I finally convinced my managers that we should go with a dev-stage-prod environment but the major problem I'm dealing with is how to move changes from dev forward. I have a script to restore prod back to dev and staging so that's not an issue. The issue is if I add or change a column or a stored proc, I'd like to be able to move the changes forward easily.
Someone recommended http://www.xsqlsoftware.com/LiteEdition.aspx but I wondered if anyone else had other ideas.
Thanks,
M@:D
March 21, 2008 at 10:16 am
RedGate's SQL Compare is about the best of the best in my opinion ... a little pricey, but well worth it. If you want something a bit cheaper, but not as good, SQL Delta is a good choice.
I'd download the SQL Compare Bundle, demo it, and then smack your boss with a price tag.
March 21, 2008 at 10:34 am
I have used XSQLCompare, Red-Gate, and Quest SQL Compare over the years and they all work ok. I do like the Red-Gate package UI better than the others, but they are all pretty easy to use and get the job done.
Download a few packages and try them.
March 22, 2008 at 8:27 am
I really like the Red-Gate tool as well, in the scheme of things I don't see that it was all that pricey either. We purchased it as part of the SQL Toolbelt suite they offer and am a real big fan of SQLprompt, which was a part of that suite.
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
March 22, 2008 at 11:08 am
If you're gonna use a compare tool to promote from Dev to Prod, you might as well just give the developers full access to Prod... unless you do a code review of ALL the changes. And, how are you gonna know which changes should be promoted and when?
The best way to do this type of thing is to have a ticketing system that keeps track of the work to be done and each bit of code should be released through a Source Control System with some bit of configuration management so you can rollback if you need to and you know what's going in.
Last folks I know that did a diff and promote spent a day or two fixing their prod system because a developer got ticked off...
--Jeff Moden
Change is inevitable... Change for the better is not.
March 22, 2008 at 8:38 pm
I'm with Jeff. I work for Red Gate and heard great things about SQL Compare (haven't used it).
However my process is here: http://www.sqlservercentral.com/articles/System+Development+Life+Cycle/vcspart4/526/
That's part 4, go backwards to part 1
March 24, 2008 at 7:50 am
Cool, thanks Steve, I'll check it out.
Jeff - at this point the development team is all of one person, me. The db staff is all of one person, you guessed it...Me.
What I'm trying to lay in place is an easy way of moving code around so that I'm not testing development code against Production db's. There's nothing more annoying than going through your assignment tasks in one of our apps and seeing a ticket for Mr. Test Test.
Thanks for the info guys.
March 24, 2008 at 10:11 am
I actually use SSIS because it does have version control and it's free with SQL Server. It's a pain in the butt to get used to, but it serves the purpose and allows me to back out code that ummm let's say passed my checks but failed when uploaded (yes even DBAs can make mistakes).
But really look into SSIS, you can do database projects and as stated by Jeff and Steve, version control is a good thing.
Marvin Dillard
Senior Consultant
Claraview Inc
March 24, 2008 at 5:23 pm
Yeaup... version control is a good thing even if you're the only one doing everything. It's saved my hiney a couple of times...
--Jeff Moden
Change is inevitable... Change for the better is not.
March 25, 2008 at 7:34 am
I'm with Jeff and others about using a source code control system.
In our environment we continually create new databases (for new customers) and upgrade all existing databases (customers) when we make changes to the product.
We also use a Dev->QA->Stage->Prod environment. In addition, we only have a small maintenance window in which to apply the changes in the production environment. So we have to get it 100% right the 1st time.
To top it off, I'm the major (90+%) database developer.
All DDL code and stored procedures is stored as separate files within the source code control system. We then utilize a "build" script, with appropriate recipe files that takes these individual files and concatenates them together in the correct order into a single CreateSchema.sql file. So all new databases are exact copies of each other.
All database changes (e.g., adding a new column, new tables, etc.) are made to both the master DDL file(s) and a separate "TableChanges" file. Similar to the schema creation "build" process, a single "UpgradeSchema.sql" file is created by a schema upgrade build process.
Note that each schema change can have all sorts of dependencies.
Side note: The body of all procedures is filled in via ALTER PROCEDURE. Therefore, the same procedure code (as a separate file) is used in the build recipe for both the new schema and the scham upgrade. Similar to Oracle's CREATE OR REPLACE syntax. The same goes for VIEWs.
This UpgradeSchema.sql file is then applied to an existing database (in Dev, QA, Stage, Prod, etc.) to bring them up to the same level as new databases.
The process is 100% repeatable and by using what is in the source code control system I know exactly what the outcome will be.
To often, a "diff" of two different databases and/or reverse engineering out the DDL yields incorrect results. I've been burnt too many times (on multiple database systems) with these "magical" tools. If it's not 100% accurate (handling all of the complex dependencies) and I still have to make some manual tweaks, then I haven't gained anything.
March 25, 2008 at 8:11 am
That makes a whole lot of sense, thanks guys.
You bring up my biggest fear - using a tool and then having to manually massage things. I want to be run and done.
So forgive me for being naive, because truthfully I've no experience with being a dba, mostly just in charge because no one else knows diddley.
I have three enviornments, Dev, Staging, and Prod. Dev and Staging are separate db's on the same SQL2005 instance. Prod is a SQL2005 instance on a different machine, actually it's hosted by a datacenter offsite. The other two are in house in the room next to my office.
I've just finished making c# changes to my code, and I updated the dev db with a new table, a new view, and altered two other tables. The code generator, Iron Speed Designer, creates it's own stored procedures that would need to come - they get regenerated every time I compile the code. (no, I don't work for Iron Speed, I just really like the product)
How would I make sure that all of my db changes get moved from Dev to Staging? I've seen the create scripts option on each table, but I don't want to do a create, I just want to update the two tables for changes and then do the creates on the new table and new view.
Again, I'm not trying to cop out and get an answer without much work. I'm the development/db/architect all rolled into one and I'm trying to learn from the ground up some best practices so that if I EVER get any staff, there'll be something decent in place that will make sense to more than just me.
Thanks so much,
M@
March 25, 2008 at 8:22 am
If you always move all changes, because you don't keep things "in progress" in the development database, then SQL Compare from Red Gate (I work for them) or ApexSQL Diff will move the changes.
However there's not tool that can understand if you've done a weeks worth of development on 12 objects, but only 10 are moving out, which 10 to move. Maybe you have C# stuff not done that needs those changes, but you want to deploy part of what you've done.
The only way to make this easy with a tool is ensure all changes are working, and move them all to QA at once with a tool.
March 25, 2008 at 8:46 am
The answer may be Requirements document, "Validation Test Plan" , "Installation Plan" and as Jeff mentioned, a ticket system. Each change is caused by some requirement. You have to create a Validation Test Plan to make sure the requirement is fulfilled after the code changes. Make code changes for the Change Request ticket that was submitted towards the requirements, document these code changes, script the changes as much as possible. If it is not running as one "Alter..." file then create an Installation Plan.
Use this Installation Plan to propagate the changes to the Staging server. Test in Staging using your requirements-based Validation Test Plan. Once you are sure your Installation Plan causes correct changes in Staging then you can use it in Production. Do not forget to do regression testing to make sure your changes did not break other things. Delegate the testing to the superusers of the department that requested the change.
That sounds sort of time-consuming, but it works and takes less time then fixing production.
Regards,Yelena Varsha
March 25, 2008 at 9:00 am
Understood, thanks.
We have a projects db, but it's pretty crude and hand coded. Anyone using anything else that might organize things? I'd like to be able to use VSS to check in check out stuff and have some way of keeping track of what pieces I'm moving for what version, etc.
thanks.
M@
March 25, 2008 at 9:06 am
Check out my series on change control. I used to copy all changed things for a release to a second project as they were altered. This way we had the main branch of all code, but each release had the specific objects in it's own branch. It was a copy, not a branch as in code branch.
Not sure how easy it is to map things with Ironspeed. If it drops and recreates, then you'll be able to use the create date on the objects to tell what's changed, though you'll need to separate the stuff moving from the stuff still in development.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply