Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Migrating from Dev to Prod


Migrating from Dev to Prod

Author
Message
Matthew Cushing
Matthew Cushing
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 408
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@BigGrin
Adam Bean
Adam Bean
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1215 Visits: 2160
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.

---
SQLSlayer
Making SQL do what we want it to do.
Michael Earl-395764
Michael Earl-395764
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2705 Visits: 23078
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.
Timothy Ford-473880
Timothy Ford-473880
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1031 Visits: 446
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 MVP
http://www.sqlcruise.com
http://www.thesqlagentman.com
http://www.linkedin.com/in/timothyford
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45018 Visits: 39887
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

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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36077 Visits: 18738
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

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Matthew Cushing
Matthew Cushing
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 408
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.
Marvin D
Marvin D
SSC Veteran
SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)

Group: General Forum Members
Points: 252 Visits: 318
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
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45018 Visits: 39887
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

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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Mauve
Mauve
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1246 Visits: 2049
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.


(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search