Allen White

Using PowerShell and SQL Server Together

I've mentioned before my company manages trade shows, and we've got a series of web sites managed by an application which uses a different SQL Server database for each site, with a master database (I'll call it Global, to differentiate it from SQL Server's master database. Well, we have a number of shows which have multiple show locations, and these use a parent-child set of databases, where information pertaining to all the shows is in the parent database, and then information to each specific location is in the child database.

There is information that is important to both parent and child, and that's kept in the parent database, and we have views in the child which return the data from the parent database that's specific to the child. The problem is when the application is changed, and the views need to be modified. Each child site has a technically different view based on it's show ID and the name of the parent database.

Yesterday I had to make such a change, and I decided a PowerShell script was the best way to approach the problem. Now, there's a table defining all the parent/child relationships in the Global database, so the first step is to query that table to get the necessary information:

#build_exibitors_view.ps1
#This script will recreate the 'exhibitors' view in all child databases

$cn = new-object system.data.SqlClient.SqlConnection("Data Source=MyServer\MyInstance;Integrated Security=TRUE;Initial Catalog=Global");
$ds = new-object "System.Data.DataSet" "dsChildSites"
$q = "SELECT [childShowID]"
$q = $q + "      ,[parentDBName]"
$q = $q + "      ,[childDBName]"
$q = $q + "  FROM [Global].[dbo].[ParentChild]"
$da = new-object "System.Data.SqlClient.SqlDataAdapter" ($q, $cn)
$da.Fill($ds)

Now, once the DataSet is populated it's time to loop through the results building the new view for each child database. I create a DataTable from the DataSet, then use the FOREACH-OBJECT cmdlet to step through the results, and then create variables for the values returned for each iteration.

$dtChild = new-object "System.Data.DataTable" "dsChildSites"
$dtChild = $ds.Tables[0]
$dtChild | FOREACH-OBJECT { 
	$pDB = $_.parentDBName
	$cDB = $_.childDBName
	$cshowID = $_.childShowID

Inside the loop I need to connect to each child database and first drop the existing view. I concatenate the name of the child database to the connection string for the SqlCommand object.

	$cn = new-object System.Data.SqlClient.SqlConnection("Data Source=MyServer\MyInstance;Integrated Security=TRUE;Initial Catalog=" + $cDB)
	$cn.Open()
	$sql = "IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[exhibitors]')) DROP VIEW [dbo].[exhibitors]"
	$cmd = new-object "System.Data.SqlClient.SqlCommand" ($sql, $cn)
	$cmd.ExecuteNonQuery() | out-null

Once the existing view is deleted I can create the new one. Here I concatenate the name of the parent database in the FROM clause, and filter the resultset based on the showID of the child show.

	$sql = "CREATE VIEW [dbo].[exhibitors]"
	$sql = $sql + " AS"
	$sql = $sql + " SELECT exhibID, name, description"
	$sql = $sql + " FROM " + $pDB + ".dbo.exhibitors AS e"
	$sql = $sql + " WHERE (showID = " + [string]$cshowID + ")"

Now that the view has been built for the child database I can execute the query to create it.

	$cmd2 = new-object "System.Data.SqlClient.SqlCommand" ($sql, $cn)
	$cmd2.ExecuteNonQuery() | out-null
	$cn.Close()

	}

(Note that the actual view used is much more complicated than this, but I wanted to share the technique. Rather than use an editor to make changes to a couple of dozen views across as many databases I used PowerShell to automate the process and made the changes in a few seconds.

Allen

Create Agent Jobs to run PowerShell Scripts

Actually, let's use PowerShell and SMO to create an Agent job which will run a PowerShell script. (Or is that a circular reference?)

I've created a number of PowerShell scripts which automate database management processes, and I wanted to be able to run them from an Agent job. The trick, of course, is to be able to get to the PowerShell environment from within Agent. It's not as hard as I thought. Let's say I have a script to back up my SQL Server user databases, and that script is in C:\Admin and it's called backupdb.ps1. The command from a Windows command window is this:

powershell "& c:\Admin\backupdb.ps1"

Try it - it's a lot easier than I'd thought. Once you have that little tidbit it's just a matter of setting up the job to execute that exact command line from within an Agent job. First, we need to load the SMO dll, and connect to the SQL Server where we want the job to run.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'MyServer\MyInstance'

Next we instantiate an Agent Job object, set its properties, and create it.

$j = new-object ('Microsoft.SqlServer.Management.Smo.Agent.Job') ($s.JobServer, 'FullBackup')
$j.Description = 'Backup User Databases'
$j.Category = '[Uncategorized (Local)]'
$j.OwnerLoginName = 'sa'
$j.Create()

Once the job exists we can create the step to execute the PowerShell script. By default the step default SubSystem is TransactSQL, so we have to specify that we want the command shell, so we set the JobStep.SubSystem property to 'CmdExec' and the command property to the powershell command we looked at earlier.

$js = new-object ('Microsoft.SqlServer.Management.Smo.Agent.JobStep') ($j, 'Step 01')
$js.SubSystem = 'CmdExec'
$js.Command = 'powershell "& C:\Admin\backupdb.ps1"'
$js.OnSuccessAction = 'QuitWithSuccess'
$js.OnFailAction = 'QuitWithFailure'
$js.Create()

The job needs to be scheduled so we create a JobSchedule object and set its properties. The time of day properties require a System.Timespan object, so we have to instantiate a couple of those, one for the start time and one for the end time, but that's pretty straightforward.

$jsch = new-object ('Microsoft.SqlServer.Management.Smo.Agent.JobSchedule') ($j, 'Sched 01')
$jsch.FrequencyTypes = 'Daily'
$jsch.FrequencySubDayTypes = 'Once'
$startts = new-object System.Timespan(2, 0, 0)
$jsch.ActiveStartTimeOfDay = $startts
$endts = new-object System.Timespan(23, 59, 59)
$jsch.ActiveEndTimeOfDay = $endts
$jsch.FrequencyInterval = 1
$jsch.ActiveStartDate = get-date
$jsch.Create()

Once this is done the job will run every day at 2am and execute the backupdb.ps1 script we created for this purpose.

Allen

Automating SQL Server Administrative tasks with PowerShell

I'll be presenting this topic at the January meeting of the Ohio North SQL Server Users Group on Thursday, January 10, 2008. Here's the Agenda:

5:00 - 5:45 PM - Early Arrival/Discussions around SQL Server Management Tools - Bruce Szabo
Are you new to SQL Server? Are you looking to start building your skills set but aren’t sure where to turn. We are going to use the early time to have a very open dialogue around various SQL Tools and Techniques to build your skill set. We are hopeful this will be an open dialogue lead by various User Group members to try and build the skill set of new SQL users. This month we will tackle some of the Management tools Including SQL Server Management Studio (SSMS), SQL Profiler and Database Tuning Advisor.

5:45 - 6:00 PM - Dinner

6:00 - 7:00 PM - Automating SQL Server Administrative tasks with PowerShell - Allen White
Most administrative functions consist of repetitive sequences of tasks which just take time. PowerShell is a powerful scripting environment which allows an administrator to automate those repetitive tasks, providing better results to the organization while saving the administrator time, and ensuring that the tasks get done properly every time. This session will give an introduction to PowerShell and demonstrate a number of scripts which help SQL Server administrators effectively manage their environments.

7:00 - 7:30 PM - Wrap up / Ongoing discussions

To register, go to www.bennettadelson.com/sql and click on the link "reserve your seat".

I'd love to see you there!

Allen

[OT] Daddy's Little Girl

Today at 4:45PM EST my daughter is getting married.

Her fiance is in the Army and will fly to Frankfurt, Germany for assignment as a Combat Engineer. They're getting married today so she can join him on base in Germany as soon as he arranges housing for the two of them. The big wedding she's always wanted will wait until next year, when they'd planned it originally, but unless they're married she can't join him. So my beautiful darling baby girl is getting married today.

I wish them the best of luck together. He's a good kid, and they adore each other.

While she was growing up I watched her in all her various activities. She was in gymnastics for a while, and Girl Scouts, and Rec Center Basketball, and something called "Odyssey of the Mind" - a kind of creative, problem-solving, improvisational competition they had for kids from about 8 to 16 years old. She would remind me of her mother in many ways, of me in many others (very scary), yet has always been a unique and wonderful individual herself.

When she was in Fifth and Sixth grades she took an interest in the special needs kids in her school, and gave up her recess time to help out in their classroom. She earned an award as the VFW's State of Ohio Volunteer of the Year when she was in middle school for the work she did. In High School she was active in Band, Orchestra, Theatre, National Honor Society, and other groups I don't remember now.

She went to college to study Special Education, never having lost that love for working with special needs kids. While in college she joined Phi Mu Sorority and actively participated in the Dance Marathon every year, which raises thousands of dollars for the Children's Miracle Network. She graduated last May with a Bachelor's Degree in Education. She's been working as a substitute this year, while her fiance was in Basic Training.

And today she's getting married, and I want to cry. I'm so proud of her.

I hope you all, like her mother and me, wish them the best for the future.

Allen