AUTHOR: Richie Lee
salt - A SQL Agent Deploy PowerShell Module Guide
This module is designed to take a SQL Agent Job that is stored in an XML file and deploy the job to an instance of SQL. The PowerShell is idempotent in that if it is re-run on the same XML file then no changesa re applied. Only when there are changes in the XML file will we see changes. Each one of the modules used have their own documentation in their header. This readme will attempt to exapnd upon that documentation. It is strongly encouraged that you read that documentation.
Why is SQL Agent Stored in an XML File?
There is no obvious way to script out in T-SQL an alter statement to apply changes to a SQL Agent account. This module makes use of SMO to apply changes, as it is far more powerful and flexible. Therefore we have to present the properties of a SQL Agent job in a way that we can then map those to the corresponding methods to add/edit said properties of a given SQL Agent Job. XML proved to be a suitable method of storing all the properties in a strucuted way. Please refer to MSDN for full information on SQLAgent and SMO. This is not required reading; more of a reference to the module.
What is the structure for the XML file?
The structure can vary, depending on the Schedule and step type, but below is the basic structure of every element that can be used.
<Job> <Name></Name> <Description></Description> <Enabled></Enabled> <Category Include=""> <Value></Value> </Category> <TargetServers> <TargetServer Include=""> <Name></Name> </TargetServer> </TargetServers> <Operator> <Name></Name> <EMail></EMail> <NetSend></NetSend> <Page></Page> </Operator> <Notification> <SendEmail></SendEmail> <SendPage></SendPage> <SendNetSend></SendNetSend> <SendEventLog></SendEventLog> </Notification> <Schedules> <Schedule> <Name> Schedule</Name> <Enabled></Enabled> <Frequency> <Type></Type> <Interval></Interval> </Frequency> <DailyFrequency> <Every></Every> <Interval></Interval> <StartHour></StartHour> <StartMinute></StartMinute> <StartSecond></StartSecond> <EndHour></EndHour> <EndMinute></EndMinute> <EndSecond></EndSecond> </DailyFrequency> <StartDate></StartDate> <EndDate></EndDate> </Schedule> </Schedules> <Steps> <Step> <Step> <Name></Name> <SubSystem></SubSystem> <RunAs Include="RunAsAccount"> <Name></Name> </RunAs> <SsisServer Include="IntegrationServicesCatalogServer"> <Name></Name> </SsisServer> <SsisServerDetails Include=""> <SsisServerCatalog></SsisServerCatalog> <SsisServerCatalogFolder></SsisServerCatalogFolder> <SsisServerCatalogProject></SsisServerCatalogProject> <SsisServerCatalogPackage></SsisServerCatalogPackage> <SsisServerCatalogEnvironment></SsisServerCatalogEnvironment> </SsisServerDetails> <SsisServerCatalogEnvironment></SsisServerCatalogEnvironment> <OnSuccessAction></OnSuccessAction> <OnFailAction></OnFailAction> <RetryAttempts></RetryAttempts> <RetryInterval></RetryInterval> </Step> </Steps> </Job>
That's a lot of XML...
Indeed. Which is why it is possible to export a SQL Agent Job that already exists from an instance of SQL Server and save as an XML file. In most cases no changes will need to be applied to the XML file and this can be used as the XML file to other environments.
How Do I Export a SQL Agent Job from SQL to an XML File?
The process is to download the module from either source or ProGet, import the module, add SMO as a type, make a connection to the instance of SQL you require and run "Get-SqlAgentAsXML".
cls Import-Module .\ps_module\salt -Force Add-Type -Path "C:\Program Files\Microsoft SQL Server\130\SDK\Assemblies\Microsoft.SqlServer.Smo.dll" $SqlConnectionString = "data source = .; initial catalog = master; trusted_connection = true;" $SqlConnection = Connect-SqlConnection -ConnectionString $SqlConnectionString #Remove-Item c:\reports\* #Get-SqlAgentAsXml -SqlServer $SqlConnection -filePath ".\" Get-SqlAgentAsXml -SqlServer $SqlConnection -filePath ".\" -JobName "Our First Job" Disconnect-SqlConnection -SqlDisconnect $SqlConnection
Does salt drop and recreate everything, or does it alter?
salt will alter
- Job Properties
- Job Schedules
salt will drop and recreate
- Job Steps
Why aren't Job Steps Altered?
Job Steps are fiddly to work with in that they are not independent of themselves - they rely on other steps to exist. So easier to drop/recreate every time.
What if I Want To Drop and Recreate The Job Every Time?
There is a Switch on the function "Set-Job" called "dropAndRecreate". This will drop the Job every time.
Is There Anything That Needs to be Altered at Deploy Time?
Several changes are required. Any elements that need changing will have an attribute called "Include." The elements that have an Include attribute are as follows -
SSISServerDetails - SSISServerDetails is a special case and only needs to be updated if the ssis package exist in a different folder/project/environment from other environments. This should not be the case and is an edge case. If you do have your packages in different folders etc it may be more sensible for each environment to mirror one another.
Let's take TargetServer as an example - When running a deploy, in the PowerShell session there needs to be a variable with the exact same name that the value of "Include" is. If you are running this locally then you would just create a variable of that given name. Here is an example: Say my TargetServer is defined as below:
<TargetServers> <TargetServer Include="Local"> <Value>MyDevBox</Value> </TargetServer> </TargetServers>
I would have to have a variable called "Local" with the value set to whatever server the TargetServer is going to be. This will then be the value of the element "Value". So in the example below the trgetserver will be "ASGLH-WL-11718".
$Local = "MyDevEnvBox" Import-Module .\ps_module\SqlAgentJobDeploy -Force Add-Type -Path "C:\Program Files\Microsoft SQL Server\130\SDK\Assemblies\Microsoft.SqlServer.Smo.dll" $SqlConnectionString = "data source = .; initial catalog = master; trusted_connection = true;" $JobManifestXmlFile = ".\Our First Job.xml" $SqlConnection = Connect-SqlConnection -ConnectionString $SqlConnectionString [xml] $_xml = [xml] (Get-Content -Path $JobManifestXmlFile) $x = Get-Xml -XmlFile $_xml Set-JobCategory -SqlServer $SqlConnection -root $x Set-JobOperator -SqlServer $SqlConnection -root $x $sqlAgentJob = Set-Job -SqlServer $SqlConnection -root $x Set-JobSchedules -SqlServer $SqlConnection -root $x -job $SqlAgentJob Set-JobSteps -SqlServer $SqlConnection -root $x -job $SqlAgentJob Disconnect-SqlConnection -SqlDisconnect $SqlConnection
If I were to not include a variable then the deployment will fail. So there is no chance of accidentally targetting the wrong server.
How Do I Deploy SQL Agent
The process in PowerShell is outlined exactly above:
- Define variable for all target servers
- Add SMO
- Create SQL Connection ConnectionString
- Import XML
- Set Job Category - if it doesn't exist will create.
- Set Job Operator - if it doesn't exist it will create, otherwise will update.
- Set Job - if it doesn't exist will create, otherwise will update. Inside this function we call other functions to set owner, notifications etc. Returns the SQL Agent Job as this is used by further functions.
- Set Schedules - Will drop all schedules that relate to job and will create all jobs detailed in the XML.
- Set Job Steps - Will drop all current job steps and will create all job steps defined in the XML.
- Finally, Disconnect from SQL.
Is there anything optional?
There are two Test functions that check that the account running the deployment has the necessary permissions to run the changes/has access to proxies (RunAs accounts). The other function tests that SQL Agent is up and running.
$Local = "myLocalDevInstance2" Import-Module .\ps_module\salt -Force Add-Type -Path "C:\Program Files\Microsoft SQL Server\130\SDK\Assemblies\Microsoft.SqlServer.Smo.dll" $SqlConnectionString = "data source = .; initial catalog = master; trusted_connection = true;" $JobManifestXmlFile = ".\Our First Job.xml" $SqlConnection = Connect-SqlConnection -ConnectionString $SqlConnectionString [xml] $_xml = [xml] (Get-Content -Path $JobManifestXmlFile) $x = Get-Xml -XmlFile $_xml Test-SQLServerAgentService -SqlServer $SqlConnection Test-CurrentPermissions -SqlServer $SqlConnection -ProxyCheck -root $x Set-JobCategory -SqlServer $SqlConnection -root $x Set-JobOperator -SqlServer $SqlConnection -root $x $sqlAgentJob = Set-Job -SqlServer $SqlConnection -root $x Set-JobSchedules -SqlServer $SqlConnection -root $x -job $SqlAgentJob Set-JobSteps -SqlServer $SqlConnection -root $x -job $SqlAgentJob Disconnect-SqlConnection -SqlDisconnect $SqlConnection
Other than the ones set by SQL Agent, not that I am aware of. By limitations I mean things like job names have to be unique, job schedule names don't have to be unique other than for the job itself etc.
$sqlAgentJob = Set-Job -SqlServer $SqlConnection -root $x -dropAndRecreate
I Don't Want an Operator Created
If you don't want to include an operator, then leave it blank. The Function to set an operator checks to see if the name attribute is empty or not. If it is then it will skip creating an operator.
Checking SQL Agent Service is Up and Running
There is a check to verify that SQL Agent Service is up and running. As this requires access to master, and some accounts may not have permissions to master, you cna override this check by including the Switch "IgnoreCheck"
Another check we have to make in the function Connect-SqlConnection is that the connection to the instance is successful by querying all the database names and piping to "Out-Null". This is because of this [https://connect.microsoft.com/SQLServer/feedback/details/636401/smo-is-inconsistent-when-raising-errors-for-login-faliures#](Microsoft Connect Issue) where SMO is inconsistent with throwing login failures.
What Permissions Are Required on the SQL Server Instance?
Here is a list of the minimal permissions required if you are not going to attempt to alter the owner of the job.
- GRANT SELECT on dbo.sysschedules
- GRANT SELECT on dbo.sysjobschedules
- GRANT SELECT on dbo.sysjobs
- ROLE MEMBER of SQLAgentOperatorRole
- Grant the login to the RunAs proxy
- GRANT SELECT on dbo.sysprocesses
- GRANT VIEW SERVER STATE
If you wish to set the owner of the SQL Agent Job, then the account needs to be sysadmin. There are no minimal permissions for this option. Set-JobOwner runs a check before attempting to change owner.
if you plan on only altering what was created through using salt, the permisisons granted by the role SQLAgentOpertorRole will be enough. If you are planning on changing objects already deployed that are owned by other accounts, and the account running hte deployment is NOT a sysmadin, then you will need to manually alter those jobs/job schedules to be owned by the account that is runnning the deployment. [https://technet.microsoft.com/en-us/library/ms188283(v=sql.110).aspx](SQL Server Agent Fixed Database Roles)
Below is a sample SQL Script to add the permissions required.
--perms USE msdb GO CREATE USER [buildaccount] FOR LOGIN [buildaccountlogin] GO GRANT SELECT ON dbo.sysschedules TO [buildaccount] GRANT SELECT ON dbo.sysjobschedules TO [buildaccount] GRANT SELECT ON dbo.sysjobs TO [buildaccount] EXEC msdb.dbo.sp_addrolemember @membername = 'buildaccount', @rolename = 'SQLAgentOperatorRole' EXEC msdb.dbo.sp_grant_login_to_proxy @login_name = N'buildaccount', @proxy_name = N'proxyaccount'; use master GO CREATE USER [buildaccount] FOR LOGIN [buildaccountlogin] GO GRANT SELECT ON master.dbo.sysprocesses TO [buildaccount] GRANT VIEW SERVER STATE TO [buildaccount]
Assuming that you are using Integrated Security, you can run Test-CurrentPermissions, which will verify that the account executing deployment has the correct minimum permissions on the server before executing.