You are here: Home > SQL Agent
Archive | SQL Agent RSS feed for this section

Automation: Granting Read Perms for Developers

by on August 16, 2010

Yeah, you heard me.

Do you like to read?

“But Kendra, why would we want to grant developers read  permissions? And why would we automate  it? And at what point did you lose your mind?”

Well, Virginia, there may or may not be a Santa Claus, but there are a lot of developers and good reasons to give them read access on many SQL Server instances. In my world, it’s every instance in the pre-production environment, which is a couple  hundred and growing. And it ain’t just read they’re needing, they should be empowered with all of:

  • In master: View server state– so they can look at all those pesky spids they can block, and which may be causing problems they’re investigating.
  • In msdb:
    • db_datareader — so they can query things not easily seen through the GUI
    • SQLAgentReaderRole — so they can look at currently executing jobs and history through the GUI
  • In each user database:
    • db_datareader — for troubleshooting, troublemaking, and general read-i-ness
    • view definition — so they can see what they’re dealing with
    • showplan — so there ain’t no excuse if they’re causing issues, and also to help further with the troubleshooting

And of course a user needs to be created in msdb and the user databases for this all to work out. Things to look out for: SQL Server will let you create a login and apply database level permissions with T-SQL without the user being created, but then you’ll find it doesn’t work.

When you’re dealing with a large pre-production environment and a large group  of developers (> 75 total) who periodically change roles, this permission set can be a bit complicated to manage.

How I Rigged this Up

In my implementation, this script is in a job on an MSX master server, and our server build steps for pre-production servers include configuring  the instance as a TSX subscriber and adding the job. The job is scheduled to run daily, so if new databases are created, restored, or dropped and recreated, permissions will automatically be re-applied. I like using the MSX  job for the implementation because it’s easy for our DBA team to see the job on the instance and see exactly what it does, and if there’s any reason to exempt an instance then that’s very simple to do.

So with this solution, I don’t have to worry about the mechanics of applying read permissions. I just have to worry about making sure the right people are in the active directory group, and I can pass that task on to their managers.

Important: Because of how our active directory and firewalls are configured, I do not have to worry about  this job being accidentally deployed to a production server and granting read where it should not. Something to think  about! Don’t automate yourself into security violations. This was another reason I chose the relatively “transparent” solution of an MSX subscriber job rather  than other solutions.

The Script

Disclaimer: This script was tossed together quickly. If you read the story above, you know I wasn’t even working on it for production. It supports windows groups only  in its current form. (Please don’t use sql authentication to grant read. Domain groups are so much more auditable and controllable!) It grants permissions directly to a login and does not create a role in each database. This is for a good reason in my world, but the best practice is to create a role, so keep that in mind.

As always, test the stuffing out of this and edit as needed if you’d like to make use  of it. Continue Reading →

Comments { 2 }

SQL 2008 Agent Jobs – Tokens work in PowerShell!

by on December 2, 2009

The Joy of Tokens and PoSH

I have been working away building out servers in our new prod test environment, and automating as much as possible along the way with PowerShell. I  have to say that it’s been really fun and PoSH has brought back that loving feeling that I always had for Perl.  If a programming language can be friendly, PowerShell manages it. 

One thing I had the chance to test out yesterday was using SQL Agent tokens in PowerShell type SQL Agent steps. I am very happy to find that the tokens work just like I was  hoping they would. In other words, this works:

$instanceName = "$(ESCAPE_SQUOTE(SRVR))"
$srv=New-Object 'Microsoft.SqlServer.Management.Smo.Server' $instanceName

The SRVR token will return the connection name of the sql server with the instance name. This works on standalone and named instances, and for clustered instances you get netname\instancename.  So this is perfect for creating a sql server object and connecting to it.

Why would you want to do this? Why not just use TSQL?

The primary reason I currently have to use a PoSH step is to execute commands against the operating system. In this case, I needed to connect to my sql  instance and get some information about it to then use in the OS level commands I needed to run. I  have a specific example of this I’ll post in the coming days.

How Do I Test This Out?

If you’re new to PowerShell, find yourself a test box to play around on. It needs to have PowerShell and SQL 2005 or 2008 (the client tools/SSMS are enough, they provide the SMO assemblies). Open a PowerShell command prompt as administrator. To get yourself connected to a SQL Server Instance, it’s as simple as this:

#This loads up the SMO module which will be used to connect to SQL
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

$instanceName = 'NETNAME\INSTANCE'; # or just "COMPUTERNAME" for the default instance

#this passes in the name you used above to create a new object
$srv=New-Object 'Microsoft.SqlServer.Management.Smo.Server' $instanceName

#this will show you a table of all the properties of your SQL Server.
$srv.Properties | format-table -auto

#this will show you a table of all the properties of tempdb on your SQL Server.
$srv.Databases['tempdb'].Properties | format-table -auto

More Information

Comments { 0 }

Average Daily Job Runtime

by on June 20, 2009

Here’s a query I found useful today– this week we moved many of our production datamart servers to SQL 2K5 SP3 CU4, and today among the course of other issues I wanted to take a look at my job runtimes to see if they might be noticeably slower or faster than prior runs. I often am in a similar situation after deploying significant changes to our codebase.

Since most of my processing runs in SQL agent jobs, looking at average runtime per day is a pretty convenient index of performance. However, the load in processing varies by day of week, so it’s frequently useful to check activity for only a certain day of the week.

This script allows for both. I usually want to tweak the conditions, so I don’t set them in variables at the top, I edit them within the query itself each time:

use msdb;

select
	d.jobname
	,d.servername
	, avgDurationMinutes=avg(d.durationMinutes)
	, daydate=convert(char(10),startdatetime,101)
from (
	select
		jobname=j.name
		,servername=server
		,startdatetime=
			CONVERT (DATETIME, RTRIM(run_date))
			+ (
				run_time * 9
				+ run_time % 10000 * 6
				+ run_time % 100 * 10
			) / 216e4
		, durationMinutes=
				(CAST(SUBSTRING((right('0000000' + convert(varchar(7), run_duration), 7)),1,3) AS INT) * 60 * 60
				 + CAST(SUBSTRING((right('0000000' + convert(varchar(7), run_duration), 7)),4,2) AS INT) * 60
				 + CAST(SUBSTRING((right('0000000' + convert(varchar(7), run_duration), 7)),6,2) AS INT)
				)/60.

		,enddatetime =
		dateadd
			(ss,
				(CAST(SUBSTRING((right('0000000' + convert(varchar(7), run_duration), 7)),1,3) AS INT) * 60 * 60
				 + CAST(SUBSTRING((right('0000000' + convert(varchar(7), run_duration), 7)),4,2) AS INT) * 60
				 + CAST(SUBSTRING((right('0000000' + convert(varchar(7), run_duration), 7)),6,2) AS INT)
				)
			,
			(CONVERT (DATETIME, RTRIM(run_date))
			+ (
				run_time * 9
				+ run_time % 10000 * 6
				+ run_time % 100 * 10
			) / 216e4 )
			)
		, retries_attempted
	from sysjobs j (nolock)
	join sysjobhistory h  on
		h.job_id = j.job_id
		and h.step_id = 0 -- look only at the job outcome step for the total job runtime
	where
		j.name in ('<strong>JobName</strong>')  -- Set the jobname here

) d
where
	datepart(dw,startdatetime)=7 -- Set  your day of week here if desired. 7=Saturday
group by
	d.jobname
	,servername
	,convert(char(10),startdatetime,101)
order by
	d.jobname
	,servername
	,cast(convert(char(10),startdatetime,101)as datetime) desc
Comments { 0 }

Backups – Using SQL Agent Tokens to Set the Date, and Why to go MultiFile

by on June 1, 2009

My life is a bit easier since I learned how to use SQL agent tokens. They are particularly nice for setting date and timestamps on backup files. Unfortunately they only work in the context of executing agent jobs.

We had some backups at work which were running quite slowly, and my colleague Gina reminded me of something I once learned, but had somehow forgotten: backups streamed to multiple files are much faster than those written to single files. This is true even when the files are sitting on the same drive.

We’ve switched over the large backups from the existing process, where they wrote a single file to local disk and then robocopied out to a remote location, to the new process where the backup is streamed directly across the network to the backup location using multiple files. The total job runtime is always lower, and in most cases the time running the backup step itself is slower. I am finding that even when the backup process time itself is slightly longer, I perfer this setup because we don’t need to execute batch files stored on the operating system (with its related security worries), nor do we have to worry about allowing memory space for the copy process to run in outside of sql server. (Along with everything else that uses up that memory space.)

Here is a sample of the modified backup commands we’re using— this one is from an 11GB SQL 2008 database where I’m using five files for the compressed backup. The total backup size across the file files is 5.5GB and it completes across the network (within a datacenter) in ~10 minutes.

BACKUP DATABASE MyDB
	TO DISK='\\BackupServer\MyBackupDirectoryShare\MyDB_db_$(ESCAPE_SQUOTE(DATE))$(ESCAPE_SQUOTE(TIME))_1of5.DMP'
	, DISK='\\BackupServer\MyBackupDirectoryShare\MyDB_db_$(ESCAPE_SQUOTE(DATE))$(ESCAPE_SQUOTE(TIME))_2of5.DMP'
	, DISK='\\BackupServer\MyBackupDirectoryShare\MyDB_db_$(ESCAPE_SQUOTE(DATE))$(ESCAPE_SQUOTE(TIME))_3of5.DMP'
	, DISK='\\BackupServer\MyBackupDirectoryShare\MyDB_db_$(ESCAPE_SQUOTE(DATE))$(ESCAPE_SQUOTE(TIME))_4of5.DMP'
	, DISK='\\BackupServer\MyBackupDirectoryShare\MyDB_db_$(ESCAPE_SQUOTE(DATE))$(ESCAPE_SQUOTE(TIME))_5of5.DMP'
WITH COMPRESSION
Comments { 0 }

A Table Summarizing All Agent Jobs with Steps…

by on April 19, 2009

Also on the topic of SQL Agent jobs– each time I work with a new system, it can take a while to familiarize myself with what all the Sql Agent jobs do. Often there are quite a few jobs, and sometimes they have legacy names that either don’t describe what the job does very well anymore, or is just hard to understand.

Plus, I don’t like opening jobs in the SQL Agent itself very much, since it only opens in an ‘edit’ view. I very much prefer selecting job details out of the tables in msdb, it’s just safer.

Because of this, a while back I wrote a SQL script that takes a lot of descriptive information about a job in MSDB and pivots it out into a table. The table will automatically have as many columns as are required– I have a server where a job has 41 steps, so it’s got 41 columns for step, each in order.
Continue Reading →

Comments { 0 }

SQL Agent Jobs- Checking for failed steps at the end of a job.

by on April 18, 2009

I use the SQL agent a lot, and it is handy for a lot of things, but  it can be frustrating to not be able to pass state information between steps.

For example, I have a job where I want to execute data verification steps against multiple tables. It makes sense to have the check for each table in its own step with a clear label to simplify troubleshooting– so when the job fails, you can see which step had an error and know from the name exactly what’s wrong.  But I want all steps in the job to run, regardless of whether a step fails— I want to check for failure at the end.

The most basic way to do this is to have each job step log to a table. This isn’t really bad, but I’d rather not maintain a table for every job of this type. It leaves room for failure, it’s more to maintain, and it just feels redundant for  the most part: all of the job history  is tracked in MSDB anyway, shouldn’t I be able to use that?

Well, I think I can…
Continue Reading →

Comments { 2 }