SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by S. Kusen / Multi-Script, Multi-DB Deployments / Latest Posts

RE: Multi-Script, Multi-DB Deployments

curious_sqldba — Fri, 22 Oct 2010 08:06:01 GMT

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

RE: Multi-Script, Multi-DB Deployments

nishanth.marathe — Fri, 22 Oct 2010 04:54:43 GMT

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

RE: Multi-Script, Multi-DB Deployments

jhopkins-1015766 — Thu, 09 Sep 2010 07:34:25 GMT

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...

RE: Multi-Script, Multi-DB Deployments

Abi Chapagai — Tue, 24 Aug 2010 20:39:52 GMT

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

RE: Multi-Script, Multi-DB Deployments

SQLRNNR — Tue, 24 Aug 2010 09:58:04 GMT

[quote][b]S.K. (8/24/2010)[/b][hr][quote][b]CirquedeSQLeil (8/24/2010)[/b][hr]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.[/quote]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?[/quote]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.

RE: Multi-Script, Multi-DB Deployments

Varghese Pathisseril — Tue, 24 Aug 2010 09:21:46 GMT

Good article with some value added deatils and examples.

RE: Multi-Script, Multi-DB Deployments

S. Kusen — Tue, 24 Aug 2010 09:02:08 GMT

[quote][b]CirquedeSQLeil (8/24/2010)[/b][hr]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.[/quote]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?

RE: Multi-Script, Multi-DB Deployments

SQLRNNR — Tue, 24 Aug 2010 08:53:33 GMT

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.

RE: Multi-Script, Multi-DB Deployments

curious_sqldba — Mon, 23 Aug 2010 15:09:42 GMT

I am trying to test this tool, what is Product Code Name? I would like to run DDL's and DML's across different servers on different dbs. Please advice. Thanks

RE: Multi-Script, Multi-DB Deployments

curious_sqldba — Mon, 23 Aug 2010 15:03:41 GMT

Thanks S.K for the follow up, i haven't looked at the scripts yet but i want the Use[dbname] to be dynamic something like it should get the dbname by itself and use in "Use[dbname]".thanks

RE: Multi-Script, Multi-DB Deployments

S. Kusen — Mon, 23 Aug 2010 14:39:49 GMT

Thanks for taking a look at the article![quote][b]iqtedar (8/23/2010)[/b][hr]I have something similar to do but my scenario is little different, any suggestion would be highly appreciated. Lets say i need to deploy a stored proc, it has to be done across multiple servers with in different db( names are also different). For ex i need to deploy usp_test it has to be done on server 1(db1,db2,db3....) ,server 2(db4,db5,db6....)........ I know third party tools can do this but looking for an in-built way. Btw we do have a control server which has linked servers to all our prod servers. Thanks[/quote]If you look at the example SQL Server Agent Job in the attachment, you'd be able to accomplish that by making multiple calls to sqlcmd and by changing the -S parameter in each subsequent call.for example:[code="plain"]--run against instance 1EXEC xp_cmdshell 'SQLCMD -S"Instance1" -E -dmaster -i"\\path\01a_update_Instance1.sql" -o"\\path\script_output_Instance1.txt"'--run against instance 2EXEC xp_cmdshell 'SQLCMD -S"Instance2" -E -dmaster -i"\\path\02a_update_Instance2.sql" -o"\\path\script_output_Instance2.txt"'GO[/code]Your 01a_update_Instance1.sql would reference the update scripts containing "USE [DB1]" atop of them on Instance1 and 02a_update_Instance2.sql would then reference the update scripts containing "USE [DB2]" atop of them, each set of scripts being geared toward the specific instance you are running it against.To use the same exact script, you could likely change the -d parameter from master to your DB's on each instance and remove the USE statements at the top of each script.for example:[code="plain"]--run against instance 1EXEC xp_cmdshell 'SQLCMD -S"Instance1" -E -d"DB1" -i"\\path\update_stored_proc.sql" -o"\\path\script_output_Instance1.txt"'--run against instance 2EXEC xp_cmdshell 'SQLCMD -S"Instance2" -E -d"DB2" -i"\\path\update_stored_proc.sql" -o"\\path\script_output_Instance2.txt"'GO[/code]Hope that helps!

RE: Multi-Script, Multi-DB Deployments

curious_sqldba — Mon, 23 Aug 2010 14:20:34 GMT

I have something similar to do but my scenario is little different, any suggestion would be highly appreciated. Lets say i need to deploy a stored proc, it has to be done across multiple servers with in different db( names are also different). For ex i need to deploy usp_test it has to be done on server 1(db1,db2,db3....) ,server 2(db4,db5,db6....)........ I know third party tools can do this but looking for an in-built way. Btw we do have a control server which has linked servers to all our prod servers. Thanks

RE: Multi-Script, Multi-DB Deployments

S. Kusen — Mon, 23 Aug 2010 09:06:48 GMT

[quote][b]clementhuge (8/23/2010)[/b][hr]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[/quote]Thanks! I'll take a look at that. I haven't tested with that, but it would certainly eliminate the xp_cmdshell call as you recommended.

RE: Multi-Script, Multi-DB Deployments

clementhuge — Mon, 23 Aug 2010 08:30:17 GMT

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

RE: Multi-Script, Multi-DB Deployments

ashwin4all1 — Mon, 23 Aug 2010 08:27:31 GMT

This is also one good way to deploy the scripts on multi server ...the best way is that ssis ..

RE: Multi-Script, Multi-DB Deployments

S. Kusen — Mon, 23 Aug 2010 07:59:43 GMT

[quote][b]Grant Fritchey (8/23/2010)[/b][hr]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.[/quote]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.[quote]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.[/quote]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. :-)

RE: Multi-Script, Multi-DB Deployments

S. Kusen — Mon, 23 Aug 2010 07:50:47 GMT

[quote][b]rottensystem (8/23/2010)[/b][hr]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.[/quote]Thanks- I'll take a look at this.

RE: Multi-Script, Multi-DB Deployments

S. Kusen — Mon, 23 Aug 2010 07:50:21 GMT

[quote][b]clementhuge (8/23/2010)[/b][hr]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.[/quote]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.

RE: Multi-Script, Multi-DB Deployments

Grant Fritchey — Mon, 23 Aug 2010 06:12:44 GMT

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.

RE: Multi-Script, Multi-DB Deployments

WILLIAM MITCHELL — Mon, 23 Aug 2010 06:03:56 GMT

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:[code="sql"]IF NOT EXISTS(SELECT 1FROM sys.sysdatabasesWHERE [name] = 'ABC')RAISERROR ('INCORRECT DATABASE', 21, 1) WITH LOG[/code]which will disconnect from the server.

RE: Multi-Script, Multi-DB Deployments

alan-959739 — Mon, 23 Aug 2010 04:36:32 GMT

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.

RE: Multi-Script, Multi-DB Deployments

clementhuge — Mon, 23 Aug 2010 02:40:06 GMT

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.

Multi-Script, Multi-DB Deployments

S. Kusen — Sat, 21 Aug 2010 12:51:17 GMT

Comments posted to this topic are about the item [B]Multi-Script, Multi-DB Deployments[/B]