Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Migrating from Dev to Prod Expand / Collapse
Author
Message
Posted Friday, March 21, 2008 10:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, December 14, 2014 10:22 PM
Points: 166, Visits: 388
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
Post #472950
Posted Friday, March 21, 2008 10:16 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 18, 2014 7:54 AM
Points: 1,171, Visits: 1,995
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.

Post #472951
Posted Friday, March 21, 2008 10:34 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,661, Visits: 23,078
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.
Post #472972
Posted Saturday, March 22, 2008 8:27 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 17, 2014 11:23 AM
Points: 1,019, 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
Post #473214
Posted Saturday, March 22, 2008 11:08 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 35,772, Visits: 32,445
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #473240
Posted Saturday, March 22, 2008 8:38 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: 2 days ago @ 3:11 PM
Points: 31,368, Visits: 15,837
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
Post #473259
Posted Monday, March 24, 2008 7:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, December 14, 2014 10:22 PM
Points: 166, Visits: 388
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.
Post #473453
Posted Monday, March 24, 2008 10:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, June 1, 2013 9:57 AM
Points: 252, Visits: 316
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
Post #473567
Posted Monday, March 24, 2008 5:23 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 35,772, Visits: 32,445
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #473794
Posted Tuesday, March 25, 2008 7:34 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 18, 2014 2:25 PM
Points: 1,187, Visits: 1,987
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.
Post #474053
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse