|
|
|
Valued Member
 
Group: General Forum Members
Last Login: Tuesday, March 16, 2010 9:11 AM
Points: 74,
Visits: 188
|
|
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
|
|
|
|
|
SSC Eights!
Group: General Forum Members
Last Login: Tuesday, March 09, 2010 11:06 AM
Points: 951,
Visits: 1,329
|
|
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.
It is not enough to succeed. Others must fail.
SQLSlayer.com
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Friday, October 09, 2009 7:14 PM
Points: 2,732,
Visits: 23,071
|
|
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.
|
|
|
|
|
SSC Eights!
Group: General Forum Members
Last Login: Friday, March 19, 2010 2:26 PM
Points: 825,
Visits: 367
|
|
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
http://www.linkedin.com/in/timothyford
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 7:25 AM
Points: 20,183,
Visits: 13,713
|
|
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".
"Data isn't the only thing that's supposed to have Integrity."
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
|
|
|
|
|
SSChampion
Group: Administrators
Last Login: Today @ 3:50 AM
Points: 23,170,
Visits: 6,927
|
|
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
|
|
|
|
|
Valued Member
Group: General Forum Members
Last Login: Tuesday, March 16, 2010 9:11 AM
Points: 74,
Visits: 188
|
|
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.
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Monday, December 28, 2009 3:43 PM
Points: 245,
Visits: 298
|
|
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
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 7:25 AM
Points: 20,183,
Visits: 13,713
|
|
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".
"Data isn't the only thing that's supposed to have Integrity."
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
|
|
|
|
|
SSC Eights!
Group: General Forum Members
Last Login: Today @ 7:04 AM
Points: 912,
Visits: 1,511
|
|
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.
|
|
|
|
