At Tech Ed 2008 I made a mental note when Paul Randal mentioned that you can't use Database Mirroring when you've got the Filestream feature turned on. I like Database Mirroring and so that was useful information.

In preparing a bunch of demos for SQL Server 2008, I'm using the AdventureWorks2008 database currently available on the Codeplex site. Installing this database requires the Filestream feature, so I turned it on.

Some of the demos I'm working on are existing demos written for the AdventureWorks database on SQL Server 2005, and I've found many of them don't work. It turns out that the structure of the database has been modified, eliminating some tables commonly used in demos (Person.Contact, for example), and changing the column names of primary keys as well as other columns.

I also discovered that you can't set the READ_COMMITTED_SNAPSHOT and the ALLOW_SNAPSHOT_ISOLATION options on if the Filestream feature is enabled, so to demo those options I'll have to load the old AdventureWorks database as well as the new AdventureWorks2008 database.

Fun!

Allen

We all know them, they're usually developers or network admins who show some abilities when database problems arise, so they're "elected" to the role of DBA, whether they want it or not. They often don't have the necessary training or insight into the issues inherent to managing enterprise database systems, but they've got the job.

What tools do they need? Of course, we all know that backup is critical, as is the ability to restore those backups. What else?

In my presentation at Tech Ed 2008 on Automating DBA Tasks with PowerShell I offered a set of scripts to do the basic administrative tasks.

What do you think? What tools does the Reluctant DBA need?

Allen

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