THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

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"
$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

Published Friday, January 25, 2008 12:05 PM by AllenMWhite

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Linchi Shea said:

Why not just use T-SQL? Would that be simpler?

I consider myself a scripting guy, but I must admit that I have not seen any compelling reason for using PowerShell in general. Most PS examples from MS seem to be rather contrived, and have better alternative solutions.

January 25, 2008 3:19 PM
 

AllenMWhite said:

I could have used T-SQL.  We're incorporating a variety of technology tools together here to automate processes, though, and by doing this in PowerShell the network admins who don't know T-SQL can run scripts like this when setting up a new trade show web site without running SSMS.  They just have to run a set of scripts and the site is done.

The best way to accomplish any given task?  It depends.

January 25, 2008 3:24 PM
 

moff said:

@Linchi - "I have not seen any compelling reason for using PowerShell in general"

With respect, I don't think you can have been looking very hard then. How about scripting out some objects in a database?

[reflection.assembly]::LoadwithPartialName("Microsoft.SQLServer.SMO") | out-Null

$server = New-Object 'Microsoft.sqlserver.management.smo.server' '<servername>'

$server.JobServer.jobs| foreach-Object {$_.script()}

This is the kind of thing that PS with SMO is superb at.

January 28, 2008 9:41 AM
 

Linchi Shea said:

Moff;

How's that so different from the following:

using System;

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Smo.Agent;

using Microsoft.SqlServer.Management.Common;

using System.Collections.Specialized;

class Junk {

   static void Main() {

       Server svr = new Server(new ServerConnection("server", "user", "password"));    

       foreach (Job job in svr.JobServer.Jobs) {

           StringCollection sc = job.Script();

           foreach (string s in sc) { Console.WriteLine(s); }

       }

   }

}

I mean, the key to 'scripting' script is to understand SMO. Once you are comfortable with SMO, you can use any .NET language. PS doesn't really provide much added value in this particular example. To me and for this particular example, all that PS provides is syntactic sugar--that is of very limited usage scope--one can do without.

I'd love to see a more compelling example. (Of course, there is a vendor-related compelling reason that I fully understand. That is, if MS pushes PS to all its platforms an apps, and make it ubiquitous. But evne then not all apps are written by MS. Personally I think MS carves the problem space wrong with PS.)

January 28, 2008 3:25 PM
 

moff said:

@ Linchi, it differs by about a fifth :-)

YMMV, I just prefer the simplicity and neatness of PS.

January 29, 2008 9:18 AM
 

Linchi Shea said:

I was being a bit facetious. I don't mind PS being so dependent on .NET and on these specialized .NET classes. It's fine to be able to pipe specialized .NET objects, but I wish PS were better in dealing with the traditional byte streams. More specifically, I'd like it to much better blend regular expressions into byte stream processing.

PS 2.0 does look a lot better. At least, it appears to be supporting background jobs and Script cmdlets.

January 29, 2008 12:18 PM
 

AllenMWhite said:

Linchi, in the presentations I've done both in local user group sessions and at the last two PASS conferences where I've presented SMO sessions I've gotten feedback that many DBA's aren't allowed to have Visual Studio on their desktops.  They're not developers, they're administrators.  For them, PowerShell is not just syntactic sugar, it's a way for pure administrators to automate processes.

January 29, 2008 8:01 PM
 

Linchi Shea said:

Allen;

But you don't need Visual Studio to write admin utilties in VB.NET or C#. I've written many console utilities in C# and never used VS.

I'm not here to try to convince anybody to agree that PS is bad. It's not that bad. But I personally don't think there is much gain for the kind of effort MS put in to develop PS. For the same amouont of investment, they could have just built on top of what has proven in many existing environments (e.g. a Korn shell, bash, or Perl), adding whatever they wanted to add (e.g. piping .NET objects).

We'll see how it plays out. But I doubt these pure administrators will really embrace PS if they are not real scripting guys. And if they are real scripting guys on the Windows platform, they would have to be versed in .NET. To be really versed in .NET, they need to know VB.NET or C#, and if they become comfortable in C# or VB.NET, they may go, "what the heck with this PS?" when they can go all way with VB.NET or C# without being shackled by the PS paradigm. Now, it may appear that I'm treating C# as a scripting language, in a way I am because of the .NET classes. Of course, C# is not a scripting language, and neither is PS. C# is too strongly typed and static for scripting, whereas I feel much handicapped or being put into a straightjack with PS. I'm baiscally a guy who likes to write quick dirty throw-away scripts to get a job done, and for that I want a scripting language that is powerful enough that I know I can always do it no matter what scripting job I may run into, and yet for many jobs often quicker to write a new dirty script than to find an old script that's tucked somewhere in an older folder.

January 29, 2008 11:35 PM
 

Max said:

I think this may be very cool becuase I am a .NET developer and I want scripts to run on any win machine to upsert records from access databases to SQL 2008. I've been looking for a script like this. As mentioned, there are few interesting PS scripts out there.  Anyway, the reader the author wrote for is probably me and I'm looking forward to trying these ideas out!  I already do a bunch of VBS scripts and not having the ease and power of .NET is a pain to anyone who scripts with wscript. Thanks!  Great work.

December 31, 2008 5:48 PM
 

Girish said:

I also never used PowerShell instead I wrote a set of functions in using VB script and kept reusing them whenever there was need for automation.

September 2, 2009 6:53 PM
 

Jim said:

I have been impressed with PS. Although I may be easily impressed. for us younger admins who have not got into VB that much and need a versitle scripting tool PS works great. If you compare many VB scripts to PS scripts PS scripts are shorter, easier to write and do the job really well. yes you have to know some .net to get the real work done, so what. PS is flexible and quickly becoming a great tool. Especially now that MS has included SQL support more directly, Data Protection Management support and even more. Anyone who has not looked at it should do so. Anyone who passed it up should give it a serious second look.

February 23, 2010 3:57 PM
 

Mark V said:

Allen,

Is there a way to script Windows Cluster Installation with PowerShell (or any other way)?

It would be really nice Disaster Recovery Script that builds out the cluster & configures it too.

June 17, 2010 2:59 PM
 

Jason said:

It is a fantasy. Don't forget any code needs debug feature (VS), reuse (oob) and easy to write correct syntax (C#). Powershell has none.

July 8, 2010 10:09 AM
 

AllenMWhite said:

Mark V

I'm sure there's a way to do it, but I'm a SQL Server guy, not a Cluster guy. Check the blogosphere, and I'm sure you'll find someone who's done it.

Jason, you're thinking like a developer. PowerShell is an administrator's scripting tool.  Admins have different requirements, and while there are ways of debugging PowerShell (http://blogs.msdn.com/b/jmstall/archive/2007/03/04/debugging-cmdlets.aspx), the points you make are much more appropriate to a development environment than an administrative one.  I personally would never write an application in PowerShell, but I use it a lot to automate the administrative tasks I do.

July 8, 2010 11:58 AM
 

Dominator 68 said:

I'm with Linchi Shea on this one.  Doesn't seem to offer much that can't be done alreday quite simply.

September 8, 2010 3:25 AM
 

Jason said:

Oh, I had both Administrator and Developer positions. I have been programming over 20 years, from oldest language to latest language. I have presented to SQLPASS several times. For things I cannot accomplish in T-SQL, say WMI to get Perfmon data, I will say you can use PS. However the debug feature is lagging behind for an experienced programmer. Non-programmer can spend tons of time to try to get their codes to work. Be honest, how many non-DBA admin can code? or even use a copied script correctly against databases. Ask what is a connect-string, hum? Do you really want to encourage them to run things against database? Even an inappropriate select can bring you a unwelcomed shared lock.

January 19, 2011 9:44 AM
 

Jason said:

I don't know what you mean a development environment of PS. You are it. The user (admin) is the developer. Do you expect a company to hire a PS developer? That is not going to happen.

(http://blogs.msdn.com/b/jmstall/archive/2007/03/04/debugging-cmdlets.aspx)

Currently, to debug PS, it is the older-timer technique I used often 20 years ago. That is what a lot of experienced programmers look down. It is a waste of time, but I agree you can eventually get it to work.

I have done high level languages like C#, Java, I had done low level programming like Assembly. The query syntax is like .Net. There is no debugger, watch, pause, intellisense. Anyway, if you have not seen it, you would not understand.

January 19, 2011 9:58 AM
 

Jason said:

For example, I don't see any equivalent error-handling like in higher level language try-catch. In case,  $cn.Open() failed, the nightly scheduled admin job might end up no where, nobody knows.

This is something people need to know, how it compares to T-SQL, C#, use it only at advantages with its associated cost.

January 19, 2011 12:26 PM
 

AllenMWhite said:

Jason, this blog post is two years old, and identifies a method of gathering data from SQL Server using PowerShell.  PowerShell 2.0 has Try-Catch-Finally in the language.  It's evident that you don't care to use or learn PowerShell, and that's fine.  Others will benefit from learning it.

January 19, 2011 1:45 PM
 

Jason said:

Allen, No. It has its use. The discussion is for "powershell in general" only post here. For example, if you try to access OS data, which you can use WMI in either C# or powershell. It has its short-comings that people need to be aware. Like I said again, "Use it only at its advantages with the associated cost." The debugger is also invented now such as Idera PowershellPlus. Hopefully, everything else will catch on so we can do real programming in this environment.

February 15, 2011 9:23 AM
 

Jason said:

For people who hasn't been programming long enough, for example, I have not seen Powershell solve the code-reuse issue. It is still old-time copy-paste or include. C# went over the evolution phase. The inherited problem with scripting is best illustrated in ASP. If PS does not solve these problems, it will have a fate like ASP (active server page .asp is gone now forever).

February 15, 2011 9:33 AM
 

Jason said:

This, for example, two years after powershell's invention, that operator does not seem to work correctly. (I spent a day try to get correct answer) which is a very simple concept in other languages.

# PowerShell cmdlet to list the files of C:\

$i=0

$GciFiles = Get-ChildItem "c:\" -force |where {(($_.attributes -band 0x20) -eq 0x20)}

foreach ($file in $GciFiles) {$i++}

$GciFiles |sort |ft name, attributes -auto

# PowerShell cmdlet to list the System files in the root of C:\

$i=0

$GciFiles = Get-ChildItem "c:\" -force |where {(($_.attributes -band 0x20) -eq 32)}

foreach ($file in $GciFiles) {$i++}

$GciFiles |sort |ft name, attributes -auto

February 17, 2011 4:25 PM

Leave a Comment

(required) 
(required) 
Submit

About AllenMWhite

Allen White is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He has worked as a Database Administrator, Architect and Developer for over 30 years, supporting both the Sybase and Microsoft SQL Server platforms over that period.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement