I have a SQL Server job which has got different steps, including running a SSIS package. Currently it is on my PC (my local instance).
I should deploy it to UAT and assume some steps need to be taken to deploy it, in terms of creating login which run it, removing my login from it, etc.
What are the steps involved to deploy it successfully? Please note there is a CLR function call in this job as well; what would you recommend for required security settings around that?
Best Answer
Deploying the stuff you need to move a job is pretty much just like packaging the stuff you need to move application code assuming that you correcly put all the new items and changes in source control. This includes your SSIS package, any object creation or alteration scripts such as tables, views, UDFs, stored procs, CLRS (Never ever create database objects using the GUI if you want to deploy later), any scripts to populate tables (such as lookup tables). You may need to number the items in the deplyment folder to ensure they are run in the correct order. Usually I write a deployment document as well because some of what we are deploying will go to differnt servers (Our ssis server is is differnt from our database server).
You can also script out the job, but you will need to review and change the script for the new environment. I often find it just as easy to set up the job on the other server manaully (but I have dba rights to all servers, if you do not, you will probaly need to this).