Using Jenkins to deploy SQL code

1

I would like to write up a deployment methodology using Jenkins if anyone is interested?

2

I'd be interested to read about a deployment system (or any other tool / utility / method) that others on here find useful, especially this one because I have no knowledge of it

3

in previous company I worked at, a co-worker wrote a home grown tool using c# .net and it was my fav tool. it leveraged a Metadata database that had our whole dev/sqa/staging/prod eco-system. but it required a dba to manually deploy the code in prod

The beauty of jenkins is you can use it to deploy both front end and back end code. You can either point it to an svn server via a LDAP plugin to do continuous integration, or point it to a local file system (that is a copy of the files in your SVN).

Then you can create a schedule that can run hourly or however you would like it. As you make changes to your code, jenkins can pull and deploy these changes. I am really impressed. If any have any other tools they would recommend, I would love to hear their stories

1 Like
4

How would Jenkins handle something like when a NOT NULL column needs to be added to a table and the clustered index needs to be rebuilt because of the huge amount of page splitting that causes?

5

Have not tried that yet but I would think the following can be done.

in your bat file that is called from jenkins you add the following

sqlcmd -U JeffModen -P Hjufh787@3 -S your.sql.server -d your.database -i C:\Subversion\IT\Database\Hardwares\v1.2\your.add.not.null.column.script.sql -o C:\jenkinslogs\JeffModenLog.txt

sqlcmd -U JeffModen -P Hjufh787@3 -S your.sql.server -d your.database -i C:\Subversion\IT\Database\Hardwares\v1.2\rebuild.clustered.index.script.sql -o C:\jenkinslogs\JeffModenLog.txt

6

@yosiasz,

Thanks for the info.

Just another question though... how good is Jenkins at doing a rollback on a given script and knowing that it should not continue if such a problem occurs?

7

I guess you could have another job running in background that sniffs for long running processes. if it finds stinkiness then it can stop it. jenkins can run any type of scripts, .exe etc so your imagination is your limit on how you can handle rogue processes