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 123»»»

Multi-Script, Multi-DB Deployments Expand / Collapse
Author
Message
Posted Saturday, August 21, 2010 12:51 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 9:03 AM
Points: 968, Visits: 965
Comments posted to this topic are about the item Multi-Script, Multi-DB Deployments
Post #972985
Posted Monday, August 23, 2010 2:40 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, August 31, 2014 10:04 AM
Points: 89, Visits: 294
Hello,

I might have read the article too quickly. So I apologize if I miss some information.

The methodology you are taking is very similar to the methodology in place at my work.
However, in real world, it is almost impossible to just let it run via sqlcmd, except for minor updates.
Like a lot of companies, There are 4 to 5 environments (dev, staging, integration prérelease, production) and a lot of environment variables make usually the things go wrong.

All can be variablize, no question. I love SqlCmd, do not get me wrong.
In the best world ever, developers would not messed up their code but they do all the time.

Second point, you apparently miss the rollback script. You should be able to rollback as soon as the scripts are deployed to check that rollback scripts work. Then you can after one day, decide to rollback if you see that performance is poor or that there are unpexpected behaviors by your applications.

Finally, I do not understand why you use xp_cmdshell. You can put you sqlcmd line directly on dos and you can just change the line within you agent first step.
I am guessing you can use xp_cmdshell if you like to parameterized in some sort of database repository all the scripts that you went through. Still, you should activate and deaxctivate xp_cmdshell as you would keep a security hole in place otherwise.


Clement
Post #973265
Posted Monday, August 23, 2010 4:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 25, 2010 5:10 PM
Points: 1, Visits: 7
This is almost identical to our process. In addition, I require our developers to ensure the scripts can be re-run without impacting the database nor requiring a back-out first. In the development environment we've automated testing by using Hudson to start a job that gets updated scripts from Subversion, restores a database snapshot that has not had any scripts applied, then executes the command file that runs the deployments scripts. When the process is done, Hudson starts the database unit tests, which then start the application unit tests. We have to strip the snapshot of all data not related to tests to to reduce the execution time.
Post #973326
Posted Monday, August 23, 2010 6:03 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:34 AM
Points: 1,510, Visits: 2,695
We have a similar concept but in our case the developer uses some Visual Basic code to build up a single change script. Each individual script is bracketed with a comment header that has the file name & last-modified date, and a footer with a GO statement to prevent any conflict between the scripts.

On one occasion, a junior DBA applied a QA script to the production db, so we also added this to the top of the change script:

IF NOT EXISTS
(SELECT 1
FROM sys.sysdatabases
WHERE [name] = 'ABC')
RAISERROR ('INCORRECT DATABASE', 21, 1) WITH LOG

which will disconnect from the server.
Post #973371
Posted Monday, August 23, 2010 6:12 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 11:12 AM
Points: 15,735, Visits: 28,139
The one thing that I see missing from this process is any hint that you're using some type of source control. As a matter of fact, I think a lot of your process, the naming & storage standards in particular, is built up because you're not using source control. At the root of it, this process is not dissimilar to the one we use, but because we have all our scripts stored in source control, in general, all we have for deployment is a listing of which scripts, from source control, by label, need to be run. This allows us to integrate our database deployments very tightly with the application deployments so that for any given application release, we have a well matched database release.

Also, I'd suggest changing your QA process so that you only ever do full deployments to it. If you build some type of production rollback process, so that prior to a deployment you reset the QA databases to what production looks like, you'll get a better test of the deployments before you go to production. That will help to eliminate a lot more errors that creep in because of people introducing changes to script outside of the process. This also would be helped by having the scripts in source control.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #973379
Posted Monday, August 23, 2010 7:50 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 9:03 AM
Points: 968, Visits: 965
clementhuge (8/23/2010)
Hello,

I might have read the article too quickly. So I apologize if I miss some information.

The methodology you are taking is very similar to the methodology in place at my work.
However, in real world, it is almost impossible to just let it run via sqlcmd, except for minor updates.
Like a lot of companies, There are 4 to 5 environments (dev, staging, integration prérelease, production) and a lot of environment variables make usually the things go wrong.

All can be variablize, no question. I love SqlCmd, do not get me wrong.
In the best world ever, developers would not messed up their code but they do all the time.

Second point, you apparently miss the rollback script. You should be able to rollback as soon as the scripts are deployed to check that rollback scripts work. Then you can after one day, decide to rollback if you see that performance is poor or that there are unpexpected behaviors by your applications.

Finally, I do not understand why you use xp_cmdshell. You can put you sqlcmd line directly on dos and you can just change the line within you agent first step.
I am guessing you can use xp_cmdshell if you like to parameterized in some sort of database repository all the scripts that you went through. Still, you should activate and deaxctivate xp_cmdshell as you would keep a security hole in place otherwise.


I appreciate the feedback!

I wasn't targeting the rollback as part of the article, but trying to show a way that we execute multiple updates against multiple databases. We ensure that the scripts have already been executed in the lower environments and proven by the time they get to the production release. Certainly, a second SQL Agent job could be utilized to handle the rollback in a single unit of deployment.

We utilize xp_cmdshell so that we can keep everything self-contained within SQL Server. When you say "change the line within you first agent step", can you elaborate? In the job step that invokes xp_cmdshell and calls sqlcmd, I am enabling xp_cmdshell, and then returning its state back to what it was after sqlcmd execution is complete. If it was disabled, it goes back to disabled.
Post #973441
Posted Monday, August 23, 2010 7:50 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 9:03 AM
Points: 968, Visits: 965
rottensystem (8/23/2010)
PragmaSQL (CodeProject winner of 2010 on Best General Database Tool category)can handle script execution on multiple databases with a single click.

Please check out www.pragmasql.com for details. You can install a personal edition for free, for multiple database execution you will have to purchase a professional editon though.


Thanks- I'll take a look at this.
Post #973442
Posted Monday, August 23, 2010 7:59 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 9:03 AM
Points: 968, Visits: 965
Grant Fritchey (8/23/2010)
The one thing that I see missing from this process is any hint that you're using some type of source control. As a matter of fact, I think a lot of your process, the naming & storage standards in particular, is built up because you're not using source control. At the root of it, this process is not dissimilar to the one we use, but because we have all our scripts stored in source control, in general, all we have for deployment is a listing of which scripts, from source control, by label, need to be run. This allows us to integrate our database deployments very tightly with the application deployments so that for any given application release, we have a well matched database release.


For this, I agree with you, but we (for better or worse) leave the source control up to our development teams. Since my team is primarily production support, we probably don't store scripts in the best way possible. With this folder layout, I was simply trying to provide a way that would make sense for purposes of the example.


Also, I'd suggest changing your QA process so that you only ever do full deployments to it. If you build some type of production rollback process, so that prior to a deployment you reset the QA databases to what production looks like, you'll get a better test of the deployments before you go to production. That will help to eliminate a lot more errors that creep in because of people introducing changes to script outside of the process. This also would be helped by having the scripts in source control.


Generally speaking, we do go with the full deployment in a lower environment tested against a copy of production. On occasion, we have last-minute updates come in, and we simply append them to the release because we already know the previous scripts were successful, so we only need to verify the implementation and rollback for the addition updates. Instead of trying to rebuild the full release, adding the implementation and rollback for the added changes logically works in our organization, but it may not in yours.

Thanks for taking the time to check out the article.

Post #973452
Posted Monday, August 23, 2010 8:27 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, January 9, 2014 6:06 AM
Points: 145, Visits: 293
This is also one good way to deploy the scripts on multi server ...the best way is that ssis ..

Ashwin VP
CSC India ...
Post #973478
Posted Monday, August 23, 2010 8:30 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, August 31, 2014 10:04 AM
Points: 89, Visits: 294
Thanks for your feedback on my two remarks.
I think we are on the same page as you enable then disable xp_cmdshell.

When I meant within the line, I was referring to the fact that you can have Dos instead of T-Sql as a job step.

Clement


Clement
Post #973482
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse