Multi-Script, Multi-DB Deployments

Question

Post reply

Multi-Script, Multi-DB Deployments

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Here are some important points for me (some already mentioned):

1. Source control.

2. Ensuring that QA or beta is exactly like production (some automated process would be good here).

3. Manual entry of the scripts into the cmd scriptlet. Too much chance for a typo.

That said, I have used the same(similar) methodology in a few places for script deployment.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

S. Kusen SSChampion Points: 10936 More actions · Answer 2

CirquedeSQLeil (8/24/2010)
Here are some important points for me (some already mentioned):
1. Source control.
2. Ensuring that QA or beta is exactly like production (some automated process would be good here).
3. Manual entry of the scripts into the cmd scriptlet. Too much chance for a typo.
That said, I have used the same(similar) methodology in a few places for script deployment.

Thanks for the feedback, Jason. Points one and two have been a theme here and I'll keep that in mind for future articles that are submitted. For point number 3, how would you approach that?

All in all, as a first time submitter- would you have any pointers to improve on?

Varghese Pathisseril SSC Rookie Points: 25 More actions · Answer 3

Good article with some value added deatils and examples.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

S.K. (8/24/2010)
CirquedeSQLeil (8/24/2010)
Here are some important points for me (some already mentioned):
1. Source control.
2. Ensuring that QA or beta is exactly like production (some automated process would be good here).
3. Manual entry of the scripts into the cmd scriptlet. Too much chance for a typo.
That said, I have used the same(similar) methodology in a few places for script deployment.
Thanks for the feedback, Jason. Points one and two have been a theme here and I'll keep that in mind for future articles that are submitted. For point number 3, how would you approach that?
All in all, as a first time submitter- would you have any pointers to improve on?

I should have also noted that I thought the article was good.

One thing we use to get past that currently is to use CM to deploy scripts from specific locations in TFS. That seems to work pretty well. I am still looking for a real good method to bypass the need for typing of each of the scripts.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Abi Chapagai SSCrazy Points: 2666 More actions · Answer 5

Steve,

We used to have almost same methodology but xp_cmdshell was not used since it was not recommend to use at my company. It is definitely a helpful article but roll back script should be kept in mind along with this script.

Thanks for sharing.

Abi

jhopkins-1015766 Valued Member Points: 68 More actions · Answer 6

Very well-written article!

The process you describe is strikingly similar to one I developed for our environment, major exception being that we use source control, and the deploy is executed from the "DBA" source control working structure- thus enforcing that all changes are in the codebase.

One benefit you didn't really emphasize is the transparency and documentation trail left behind by this process, especially if you incorporate script logging (basically Print statements recording timestamped progress) in the change scripts and/or deploy scripts. We actually invoke SqlCmd via a series of calls in a DOS batch file, passing source code files from the DBA working location. The DOS "Deploy Scripts" are named referencing a sequential Deploy#, and copied locally to the server in an "Installs\SQL" location by the Deploy Job; the "Prepared Deploy" folder, including Deploy Instructions from the Developer, as well as DBA-mediated scripts like Alter Tables etc., also named with the Deploy#, is archived. This provides a very clear documentation trail of exactly what happened and when.

Now if we could only get the Developers to follow the prescribed development methodology, to allow this process to work as well as it should...

nishanth.marathe Newbie Points: 9 More actions · Answer 7

Hi,

Article is well written. I had one question though.

Say you are executing lots of script files. (hundred's).

Is there a way to Show a script file executed successfully or not?

Something like ABC.SQL => SUCCESS. or XYZ.SQL => FAIL. I could not find a way to get the name the script file being executed

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 8

Yes. It does show but it goes sequentially,if the script fails on anyone of the servers then it wont go to the other. You will need to specify begin catch. I have a script for that. Let me know what exactly you want to do and then i can send you. thanks