When SQL Server Agent runs its tasks it normally does so using the context of the service account you assigned to the Agent service. This may or may not be the best context for your application, so you can set up a Proxy. This allows you to run the task under the context of the account you set up in the proxy, and it's pretty easy to do.

First, set up a Credential. This defines the ADS login credentials the proxy will use. You can do this under the Security tree in Object Explorer, or you can set it up in Transact-SQL like this:

CREATE CREDENTIAL [cred_MyCredential] WITH IDENTITY = N'MyDomain\MyLogin', SECRET = N'S0meComp1exP@ssw0rd'
GO

Once the credential exists then you define the proxy. Expand the tree under SQL Server Agent/Proxies, select the job task type you plan to use the proxy for, then right-click and select New Proxy, or use this Transact-SQL:

USE [msdb]
GO
EXEC msdb.dbo.sp_add_proxy @proxy_name=N'proxy_MyProxy',@credential_name=N'cred_MyCredential',
		@enabled=1
GO
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'proxy_MyProxy', @subsystem_id=3
GO

EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N'proxy_MyProxy', @msdb_role=N'SQLAgentUserRole'
GO

In the sp_grant_proxy_to_subsystem stored procedure, @subsystem_id=3 indicates this proxy will be used to run Operating System commands.

Once the proxy exists it can be selected in setting up a job task by specifying the new proxy in the Run As dropdown in the task dialog. If setting up the job through Transact-SQL you specify the proxy in the sp_add_jobstep stored procedure with the @proxy_name parameter:

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step 01',
		@step_id=1,
		@cmdexec_success_code=0,
		@on_success_action=1,
		@on_success_step_id=0,
		@on_fail_action=2,
		@on_fail_step_id=0,
		@retry_attempts=0,
		@retry_interval=0,
		@os_run_priority=0, @subsystem=N'CmdExec',
		@command=N'"d:\ApplicationDirectory\MyJobStep.bat"',
		@flags=0,
		@proxy_name=N'proxy_MyProxy'

This may seem like a lot, but really it's not, and it ensures that the automation you're implementing is being done using the right context.

Allen

Last night I picked up my tickets for Commencement Exercises on May 11, and found out that I'll be graduating Summa Cum Laude from Baldwin-Wallace College with a Bachelors of Science Degree in Computer Information Systems.

Needless to say, I'm really pleased.

Allen

Last week I presented a session on Using SMO to Manage SQL Server at SQL Connections in Orlando, Florida. This was the third major conference I've presented this session, and each time I do this session I change more demos from VB.Net to PowerShell. This time it was the demo to create a new database.

The big thing about my demos is that I want to reflect what I actually do in my job as a DBA, and I now use this script whenever I want to create a new database. The demo files I included with my presentation contained the VB.Net code I used to use so I wanted to make the code available here.

The first thing we need to do is load the SMO object library and connect to the server.

#createdb.ps1
#Creates a new database using our specifications
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')  | out-null

$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'MyServer/MyInstance'

The next thing we'll do is set a string variable to the name of the database. This value can also be supplied as an argument to the script if you want. Then we'll instantiate the database object and add filegroups for PRIMARY (required for SQL Server), and another filegroup I call AppFG for the application data. I've found that I improve performance and recoverability by putting only the database metadata in the PRIMARY filegroup, setting its size to 5MB without expansion, then setting the AppFG (application filegroup) to be the default filegroup.

$dbname = 'SMO_DB'

# Instantiate the database object and add the filegroups
$db = new-object ('Microsoft.SqlServer.Management.Smo.Database') ($s, $dbname)
$sysfg = new-object ('Microsoft.SqlServer.Management.Smo.FileGroup') ($db, 'PRIMARY')
$db.FileGroups.Add($sysfg)
$appfg = new-object ('Microsoft.SqlServer.Management.Smo.FileGroup') ($db, 'AppFG')
$db.FileGroups.Add($appfg)

Once the filegroups have been created, we can create the files for the database. First we create the file for the database metadata. I've set the size to be 5MB with no growth. To create the database the PRIMARY filegroup has to be set to be the default, so we'll set that here as well.

# Create the file for the system tables
$syslogname = $dbname + '_SysData'
$dbdsysfile = new-object ('Microsoft.SqlServer.Management.Smo.DataFile') ($sysfg, $syslogname)
$sysfg.Files.Add($dbdsysfile)
$dbdsysfile.FileName = $s.Information.MasterDBPath + '\' + $syslogname + '.mdf'
$dbdsysfile.Size = [double](5.0 * 1024.0)
$dbdsysfile.GrowthType = 'None'
$dbdsysfile.IsPrimaryFile = 'True'

Next we'll create the file to hold the application tables. Normally 25MB works for my databases, so I've set that in the Size parameter, and I use a growth parameter of 25%, because 10% is too small an increment when growth is required, in my opinion. I've also set a maximum size for this file of 100MB. I have to watch this to make sure we don't run out of space, but this is rarely a problem in my environment, and this max helps prevent me from running out of physical disk. (Note that sizes are specified in KB units, so I "do the math" in the script so it's easier to read.)

# Create the file for the Application tables
$applogname = $dbname + '_AppData'
$dbdappfile = new-object ('Microsoft.SqlServer.Management.Smo.DataFile') ($appfg, $applogname)
$appfg.Files.Add($dbdappfile)
$dbdappfile.FileName = $s.Information.MasterDBPath + '\' + $applogname + '.ndf'
$dbdappfile.Size = [double](25.0 * 1024.0)
$dbdappfile.GrowthType = 'Percent'
$dbdappfile.Growth = 25.0
$dbdappfile.MaxSize = [double](100.0 * 1024.0)

Now I can create the file for the transaction log. I set this to an initial size of 10MB with 25% growth.

# Create the file for the log
$loglogname = $dbname + '_Log'
$dblfile = new-object ('Microsoft.SqlServer.Management.Smo.LogFile') ($db, $loglogname)
$db.LogFiles.Add($dblfile)
$dblfile.FileName = $s.Information.MasterDBLogPath + '\' + $loglogname + '.ldf'
$dblfile.Size = [double](10.0 * 1024.0)
$dblfile.GrowthType = 'Percent'
$dblfile.Growth = 25.0

We can create the database now, and once it's been created we can grab the AppFG filegroup, set it's default property to True, alter the filegroup and alter the database. Now it's ready for loading the tables and other objects necessary for the application to work properly.

# Create the database
$db.Create()

# Set the default filegroup to AppFG
$appfg = $db.FileGroups['AppFG']
$appfg.IsDefault = $true
$appfg.Alter()
$db.Alter()

This script allows me to quickly create a new application database without the tedium of clicking through the GUI dialogs, but I can run this script against any of my servers without changing it to reference the specific database file locations, because I pull that from the server's Information collection, so it's cleaner than using Transact-SQL scripts as well. (Note, in this case I'm using the MasterDBPath and MasterDBLogPath properties from the Information collection.)

Allen

The most important lesson I learned in flight training was in managing priorities:

1. Aviate
2. Navigate
3. Communicate

First, keep the plane flying straight and level. Second, keep it going on the intended flight path (and avoid any other objects in the area). Third, communicate your intentions, needs, etc. with the proper services, be they Air Traffic Control, Flight Services, the local airport Unicom, or simply another pilot in the same airspace.

These rules apply in managing database systems, if you think about it. First, aviate - make sure the database is running, or fix the problem preventing it from running. Second, figure out where the problem has taken you and how you need to get back to your normal production mode. Third, document what happened, why it happened, what you did to correct the problem and what you will do to ensure continued quality service.

This set of priorities can help you keep a cool head when problems come up and everyone's screaming at you.

Allen

The Windows/Visual Studio/SQL Server 2008 Launch Event is working its way around the country, and Tuesday, March 18, it hits Detroit. I'll be there to help out with the SQL Server portion of the event and answer people's questions on the new version. (It came to Cleveland last Thursday, but I was out of town.)

If you still haven't registered, here's the website: http://www.microsoft.com/heroeshappenhere/events/Detroit/default.mspx.

I hope to see you there!

Allen

Tonight's meeting of the Ohio North SQL Server Users Group will include the Detroit SQL Server Users Group as well. Here's the agenda:

5:30 - 6:00 PM - Early Arrival/PASS Intro and Presentation Tips and Techniques - Allen White
6:00 - 6:10 PM - Get Pizza and Settle/Coordinate Live Meeting with Detroit
6:15 - 7:15 PM - Bill Wolohan - SQL Server 2008 Change Tracking and Change Data Capture
7:15 - 7:30 PM - Q/A & Wrap Up

Go to http://www.bennettadelson.com/sql and click on the link "reserve your seat".

Allen

Normally our meetings are on the second Thursday of the month, but this year that falls on Valentine's Day, so we've moved the meeting to Thursday, February 21.

Here's the agenda for the February meeting of the Ohio North SQL Server Users Group:

5:00 - 5:45 PM - PASS Chapter Acceptance/SQL 2008 Heroes Happen Here: Presentation Tips and Tricks - Allen White

The Ohio North SQL Server Users Group is now officially a PASS chapter. We'll present the PASS Welcome presentation, followed by a "Heroes Happen Here" presentation on how to deliver quality presentations, because you have something interesting to share with the rest of us, and this presentation will show you some of the ways the top presenters get their message across effectively.

5:45 - 6:00 PM - Dinner

6:00 - 7:00 PM - Change Tracking and Change Data Capture in SQL Server 2008 - Bill Wolohan, Bennett-Adelson

SQL Server 2008 has 2 new features that can notify you when your data changes. Both are easy to set up and do not require you to create triggers on each table. Change Tracking requires less overhead, but Change Data Capture gives you more detail. This presentation will explain how each of these features works and when you might use one or the other.

Registration: Go to http://www.bennettadelson.com/sql and click on the link "reserve your seat".

I look forward to seeing you there!

Allen