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

Staging Deployments Expand / Collapse
Author
Message
Posted Friday, November 30, 2012 9:22 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 07, 2014 7:35 AM
Points: 1,172, Visits: 2,413
Eric M Russell (11/30/2012)
If an application is loosely coupled (if the term is appropriate here) with the database using stored procedures, web services, and meta-data configuration, then even significant changes to the database may not require any changes to the application at all. The application should also be coded to accomodate things like the addition of optional stored procedure parameters or new columns in the resultset without breaking. That's the ideal architecture.
Yes, where I work there are separate teams working on the database changes and application changes; they submit change orders seperately to the production control / DBA team, and database vs. application changes typically get deployed at different times, sometimes even days or weeks apart with no adverse affect on the users.


Almost all (if not all - a sweeping statement I hesitate to make!) of our application code is coupled loosely enough to the DB to allow us to deploy database changes ahead of application changes. We insist that all stored procedure calls pass the parameters by name (i.e., EXEC storedProc @parameter1 = 'x', @parameter2 = 'y' rather than EXEC storedProc 'x','y'), so we can add parameters, with acceptable default values if necessary, without breaking the application code. Our application developers also write their code such that it works so long as the columns it expects are present in result sets and does not break if columns are returned in a different order or additional columns are included.

Since we database developers have to complete our work to a significant degree before the application developers can begin coding against it, the database changes are almost always ready for security review, testing, and deployment before the application code is finished. We usually push database changes through security review and QA and sometimes even deploy it to production before the corresponding application code. This keeps our security review and QA teams from being unduly burdened by big changes involving both database and application code. It also lets us find and fix any problems that show up in security review and testing while the application developers still have the hood up on their code to make any changes needed there more conveniently. Plus, we can deploy the database code to production at a time when we can roll back any changes if unexpected problems occur without necessitating a corresponding rollback of the application code.


Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Post #1391417
Posted Friday, November 30, 2012 9:50 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, February 14, 2014 11:17 AM
Points: 69, Visits: 79
Usually we deploy the database and application changes together, but not always. We actually use SQL Compare to make all of our database changes in an environment where multiple people review the changes before we decide if the database change is "in" or "out." If we know that the db change won't impact the application then we are okay pushing it out in advance of the application changes.
Post #1391431
Posted Friday, November 30, 2012 10:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 07, 2013 4:42 PM
Points: 10, Visits: 45
Most of the time, they happen in parallel, especially for new features. But, since most of our business rule are in SP's, it's common to release a new version of an SP without changing the .NET code.
Post #1391449
Posted Friday, November 30, 2012 1:51 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, January 29, 2014 5:04 PM
Points: 102, Visits: 96
Generally we do the database changes first. Usually these are just adding more nullable fields and using optional parameters at first for the extra functionality we've added. Which after we get all the application code up to date we might make these parameters optional no longer and the extra fields no long nullable. If the changes are going to break programs basic functionality we usually deploy both at the same time. Or as close as possible.
Post #1391547
Posted Sunday, December 02, 2012 3:17 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 11:38 AM
Points: 2,868, Visits: 1,711
For us it depends. Each release goes through an evaluation and sanity check prior to go-live.

Continuous integration and test driven development mean that we have a high degree of confidence that things will work as rehearsed in the pre-live environment.

Some releases are code only, some are DB only. Some require both but the DB can go first shortly prior to the live deployment. Some require that the changes be made together.
Go-live doesn't particularly worry me, it is roll back that causes me to tuck my trousers in my socks so no-one can see I'm scared.


LinkedIn Profile
Newbie on www.simple-talk.com
Post #1391751
Posted Monday, December 03, 2012 7:52 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, December 23, 2013 7:13 AM
Points: 163, Visits: 259
andycao (11/30/2012)
Seriously, if you architect things properly then DB changes can and should go out way ahead of the apps.


David Benoit (11/30/2012)
We deploy database changes separately from code changes and allow for a layer of abstraction. The benefits here are tremendous although there is a more complicated framework that has to be in place in order to support this. However, when you are able to make database changes, apply the data to those changes, all in preparation and before the application "knows" about it, can really be a huge savings on the day of an application release AND can allow for some pre-change testing to ensure that the application is going to perform as expected.


I agree with the above. If you are releasing on an agile schedule, you will almost always know about the database changes first, before the application code changes are complete. The design of the system and the release process should permit database changes to be released independently of the code changes. That way, database changes are already there when the application changes are released. This technique splits the testing of the two and can easily result in a reduction of implementation problems and issues.
Post #1391960
Posted Monday, December 03, 2012 8:27 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 1:08 PM
Points: 1,495, Visits: 4,313
David.Poole (12/2/2012)

...
Go-live doesn't particularly worry me, it is roll back that causes me to tuck my trousers in my socks so no-one can see I'm scared.

When we submit change orders to production control for deployment, we're required to provide a rollback script or at least a text file containing a set of instructions. For the web application, services, and BI developers, that's relatively straightforward, simply copy in the previous version of the modules, even if it involves temporarily taking the application offline.

If I'm deploying something like an insert/update/delete script, I will perform a [ROLLBACK] within the batch in the event of an error of if @@ROWCOUNT returns an unexpected value. However, in the realm of the database, there is no easy and reliable way to rollback a batch of updates or schema changes... not hours or days after the batch has already been committed in a TB sized production database with several hundred users.

In many cases the rollback instructions would go something like this:

#1 Restore database to most recent backup prior to deployment date. (LOL!)
OR
#2 Contact me, we'll discuss what specifically needs to be restored, and then I'll write the script.



"Winter Is Coming" - April 6, 2014
Post #1391975
Posted Wednesday, December 26, 2012 4:17 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:52 AM
Points: 2,820, Visits: 3,917
David Benoit (11/30/2012)
We deploy database changes separately from code changes and allow for a layer of abstraction.
For this we have started using ALTER TABLE approach insead of TMP..INSERT approach plus keep columns nullable which makes the database chanegs independent of application side changes but finger crossed that nobody is useing "select *" in any SP or sql query


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1400208
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse