Whenever I set up a new SQL server, there are a number of tasks that have to be done before it's ready for service, and the process can become really tedious if I have to step through the wizards in SQL Server Management Studio (SSMS) to get them done. The key thing is that using the graphical interface, while good for newcomers, can easily lead to inconsistent configurations, because it's easy to click the wrong checkbox or make inadvertent changes to your standard settings when stepping through the pages of the wizard.
After loading the SQLPS module, like I did in "Gather SQL Server Instance Information with PowerShell," I'll set a variable to equal the name of the target instance.
$inst = 'WS12SQL'
In my scripts, I'll normally pass this in via a command-line argument. Once I have the instance name, I can connect to that instance.
$svr = new-object ('Microsoft.SqlServer.Management.Smo.Server') $inst
The SQL Server Agent objects are part of the JobServer object, under the Server object in the SMO hierarchy. Within the JobServer object is defined collections for Jobs, Alerts, and Schedules. I can now create a new Agent job by creating a Job object, specifying the server's JobServer object as one parameter and the name of the job as the other.
$j = new-object ('Microsoft.SqlServer.Management.Smo.Agent.Job') ($svr.JobServer, 'StartPerfCapt')
Once the Job object is created I can define its properties and create it.
$j.Description = 'Start Perfmon data capture' $j.OwnerLoginName = 'sa' $j.Create()
Since a job can't do anything without steps, I create a JobStep object using the Job and the name of the step as parameters, then define the properties for the step. The step I'm creating is going to use the PowerShell task type, so the Command property gets the PowerShell command line. This job is going to run a script that gathers perfmon data, and has three parameters:
- The server being monitored;
- The number of seconds to sleep between gathering the data; and
- An 'end date' which is a date after which the script will stop running.
Because I already have the target instance in a variable, I can use that variable in the command text by using double quotes (") when I define it. This is a feature of PowerShell—when you use double quotes to define a string, any embedded variables are replaced with their current value. If you use single quotes (') to define that variable, variable substitution isn't performed.
$js = new-object ('Microsoft.SqlServer.Management.Smo.Agent.JobStep') ($j, 'Step 01')
$js.SubSystem = 'PowerShell'
$js.Command = "c:\Scripts\getperf.ps1 '$inst' 60 '2199-12-31 23:59:59'"
$js.OnSuccessAction = 'QuitWithSuccess'
$js.OnFailAction = 'QuitWithFailure'
$js.Create()
Once the JobStep object is created, I need to grab the ID of that step so I can set the job to start with that step. I also need to tell the job to run the job on my target server, so I set those properties and alter the job.
$jsid = $js.ID $j.ApplyToTargetServer($s.Name) $j.StartStepID = $jsid $j.Alter()
The final step is to create a JobSchedule object for the job, again using the Job and the schedule name for parameters. I set the FrequencyTypes property to AutoStart because I want this job to be started whenever SQL Server Agent starts up, and I set the ActiveStartDate property to the current date/time, so it'll be automatically run the next time SQL Server Agent is started.
$jsch = new-object ('Microsoft.SqlServer.Management.Smo.Agent.JobSchedule') ($j, 'Sched 01')
$jsch.FrequencyTypes = 'AutoStart'
$jsch.ActiveStartDate = get-date
$jsch.Create()
By putting these steps into a script, I have an easy way to make sure that I'm always capturing my performance data on any server I set up automatically.
Check out more PowerShell articles by Allen White.
