See this:

That’s right. The install worked. All I had to do was get a completely clean server set up. No domain controller. Now to get my learn on.

Microsoft has a web site with a number of introductory samples. I’ll start there and work through them. The very first example gets me set up with some data that it builds by running a Powershell script, importdata.ps1. But I’m not going to just blindly follow along. I want to see what the heck is happening so I can start understanding this stuff. By the way, thank you Microsoft for making the samples in PowerShell and not forcing me to relearn Python or something else. That would have been frustrating.

The script is really simple. It has two scenarios you can pass it, w3c or RunTraffic. They just change directory and run another PowerShell script, import.ps1, from two different directories. I’ll be the scripts are different. I’m running the w3c scenario, so let’s see what that script is doing.

Ah, now things are getting interesting. There are two functions, one for data generation which uses an executable to make up test data. The other a mechanism for calling Hadoop. Basically it uses two objects, System.Diagnostics.ProcessStartInfo and System.Diagnostics.Process. The ProcessStartInfo is for defining startup information for a process that you then define using the Process command. In this case it’s setting the location of hadoop:

$pinfo.FileName = $env:HADOOP_HOME + "\bin\hadoop.cmd";

Then it sets up arguments, if any. The actual calls to this from the code use a command, dfs, which has different settings -mkdir and -copyFromLocal. From what I can tell, it’s creating a storage location within Hadoop and then moving the data generated over. I’m good with all the scripts I can see except knowing where this dfs thing comes from.

Data load ran just fine:

Data loaded, time to test out a Map/Reduce job. Again there’s a powershell script included for running a simple job, so I check it out. First run, fails. Great. More stuff to try to troubleshoot in order to be able to see this work. This is not going to be easy.

Stepping through and running the scripts might not be the best way to learn this. So, I’m going to now start reading the Big Data Jumpstart Guide. I’ll post more as I learn it.

 

I’m continuing to work with the Surface.

BOOM

I just had my first crash. I was in IE when I tried to switch tabs and the pull down window would not stay open. So I tried closing IE. That didn’t work at all. I tried switching to the Start window or switching apps. No joy. I then did a hard restart, holding down the power button for about 10 seconds. On restarting, the system started updating itself, which was weird since I had made sure to do all the updates when I started. The update finished, I logged in, and noticed that the wifi symbol looked funny (wish I had a screen capture, ooh, is there a utility for that?). No big deal until I tried connecting to the wifi service, which just wouldn’t happen. In fact, it said there were no wifi services available. So I went to the settings. Wifi was gone. Not even an option.

I’m sitting there, heart in my mouth, my expensive new toy is toast. I’m just boarding an airplane when this happens, so I’m not going to be able to spend time troubleshooting. I finally decide to do another reboot, just because (have you tried turning it off and turning it on again). It reboots while I board the plane. Just before takeoff I check and wifi is back. Shew! But what caused the weird lockup?

I went to the desktop, which is a weird and funny place on a tablet like this. It’s Windows… mostly. I checked through the event logs, which look exactly like you’d expect. They’re also just about as useful as usual. I could see that I had an unexpected reboot. But I didn’t see any errors prior to the reboot that would indicate what had gone wrong. In fact, everything looked perfectly normal. More fun with the Surface.

POSH

Next up, PowerShell. Yeah, it’s Windows 8. This means we have a direct method for controlling the device. What’s more, since you can do Remoting from PowerShell, I can use this device to take charge of others. If you’re already using Windows 8, you know that the Start screen is actually in two layers, the pretty one, and the one with everything else. Go to the one with everything else and you get a whole bunch of apps that are immediately evident, including PowerShell. This opens inside the desktop.

I ran a little command, just to see what would come back. dir env:

Worked great. I can see the four processors, information about the machine and my login. Just like a real computer running PowerShell on a real operating system. Which, pretty much means I am running on a real computer with a real OS. This absolutely opens possibilities. I’m not sure what to do with it yet, but it’s something I’m going to keep in the back of my head as a mechanism for making this table more functional.

TWEETS

Part of my job is community engagement. Lots of that is done through web apps, posting on news groups, posts on G+ or Facebook, all through the browser. But, I also tweet. And tweeting through just the browser? Blech! I need an app. I’ve gone through four apps so far.

ROWI: Pretty. Very pretty. As a matter of fact, if most of the tweets I received had pictures, this would be a great app. Well, until you start tweeting yourself. No picture attachment capability at all that I could find. No completion when typing people’s twitter handles. No lookup for handles either. I couldn’t find a way to add search columns either. While this is a very pretty interface, it’s a pretty useless tool.

MetroTweet: This one was pretty good. It’s not as pretty as some of the other apps. Instead, it’s functional. It works really well. You can do most of what you would expect from a good twitter app. Tweets allow you to add location and pictures. You can set up searches and save them as new columns. I really liked this app but for one thing. It doesn’t allow for lookups on users handles. I’m sorry, but I’m not smart enough to remember the exact spelling of everyone’s twitter handle. So this is a contender, but not my preferred app on my Surface.

Tweeterlight: This is a clean and simple interface. It has to be because it gives you very little control over the environment. Basically, you have to set up searches in Twitter itself. Then they’ll be available in this app. Otherwise, nope. Tweeting is ok, but week. You can add pictures, but not take them. No name lookup at all. I’d rather use MetroTweet if these were my only choices.

TweeTro: My current winner. This app is very pretty. They really went after following the new RT styles. But, it’s still quite functional. You can add searches directly through the interface and move between columns by scrolling around. Tweets let you add pictures or take them. You get name look ups and searches directly from tweets. It doesn’t have location on the tweets, but that’s not something I use much. It even has a column just for the pictures that people are tweeting like ROWI. Tweetro is a solid, well put together, twitter client that does most of what you would need and does it with some style.

Welcome once more to the Miskatonic branch of SQL University. I see that most off you survived out last encounter… uh, class. Most of you even appear somewhat sane… somewhat. Today we’re going to talk about PowerShell and it’s use with SQL Azure. Which sounds a something like an eldritch horror breaking in from some outer dimension… hmmm… that could explain certain things… So, back to Powershell and SQL Azure.

You can’t run Powershell from SQL Azure. Thank you for attending and enjoy your trip home.

Still here? I did try. Let’s clarify. You can’t run PowerShell from SQL Azure, but I didn’t say that you couldn’t use SQL Azure as a target for PowerShell. It is possible to connect to your SQL Azure databases running PowerShell from a local desktop or server. Let’s examine the basic connections.

The strength of PowerShell, at least in my mind, is in the pipe. The ability to pass information from one command to the next through the pipe is how PowerShell stands out from just another scripting language. SQL Server 2008 R2, and by extension, SQL Azure, has very limited pipe capability (we’re all holding out hope for Denali). What little you can do comes through the Invoke-Sqlcmd (and yes, invoking things at Miskatonic is usually frowned upon).

Connecting to a SQL Azure database from PowerShell is just a question of configuring the connection appropriately. But appropriately isn’t that hard:

Invoke-Sqlcmd -Query "SELECT * FROM sys.dm_exec_requests;" -ServerInstance "myAzureDB.database.windows.net" -Username "MyUserName" -Password "APassword"}

Yeah, it’s that easy. This will connect up and run the query. I’d show you the output, but it’s not all that thrilling.

The other way to use PowerShell is to connect to stuff through SMO. Clearly, if I can connect through Invoke-Sqlcmd, then making an SMO connection should be just as easy, and it is:

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null

$cn = "Server=tcp:myAzureDB.database.windows.net;Database=master;User ID=MyUserName;Password=APassword;Trusted_Connection=False;"
$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = $cn

$srv = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $conn
$db = $srv.Databases["master"]

$db.ExecuteWithResults("SELECT * FROM sys.dm_exec_requests;")}

Well, OK, maybe not as easy, but it works. I’m not a fan of SMO.

The key to remember, any functionality that is not supported in SQL Azure, will not be supported through any PowerShell calls. But once you’re in, you’re in. You can begin making use of the abilities of PowerShell to perform automation on your SQL Azure database. Be aware though, you’re still paying for data movement charges. There’s nothing magic here. Also, since PowerShell doesn’t yet run on the SQL Azure server itself, remoting is right out.

Thanks for coming to my class. I hope you begin to employ PowerShell with your SQL Azure implementations. I also hope you all get home. The building’s beginning to shake. Since we’re no where near an active fault line, it usually means yet another visitation from beyond space and time, Why can’t they pick on Harvard occasionally, or go down to Princeton? In New Jersey, who’d notice? I’m heading for my bunker… uh, I mean office. See you next time, maybe.

A question came up on the SQL Server Central Forums, how could you use Red Gate SQL Compare to automate creating scripts for all your databases at once. What’s the first thing that popped into my head? Yep, Powershell.

SQL Compare Pro comes with a command line utility that allows you to automate just about everything you can do with the GUI. I like to use it when I’m setting up deployment mechanisms for systems because it makes one click deployments possible. I won’t go into all the methods of that type of automation here. For a lot more information on that topic, check out the Team-Based Development book.

If you’re interested in exploring how you can use the command line, just type this:

sqlcompare/?

Now is a good time to note that I’ve set SQL Compare’s location within my path because I use it so often from various locations in the disk, that having to type the full path every time I want it, is just too much.

Here is the PowerShell script:

$Server = "grant-red1\gfr1"
$Path = "c:\dbscripts"

Invoke-Sqlcmd -Query "sp_databases" -ServerInstance $Server | ForEach-Object {Invoke-Expression "sqlcompare.exe /s1:$Server /db1:$($_.DATABASE_NAME) /mkscr:$($Path)\$($_.DATABASE_NAME) /q" }

It’s just a single line of code, not counting setting the variables. I’ll be making it into a full blown script and posting it on the Red Gate site (location to be determined) so you can just call it passing variables. In the mean time, let me explain what I’ve done.

First, I use the Invoke-Sqlcmd utility to pass the query sp_databases to the server. This will retrieve the result set which consists of DATABASE_NAME, DATABASE_SIZE, and REMARKS for all user databases on the instance and any databases available through a gateway on that instance. From there I simply use the pipe, “|”, to pass the results to a ForEach-Object loop. Yes, I’d prefer not to use a loop, but this is a DOS command line utility, not a PowerShell commandlet, which means it’s not aware of the beauties of the pipe. Invoke-Expression is used to call sqlcompare.exe for each row within the result set. I use the abbreviation for the parameter /Server1, /s1, to set the instance through the variable $Server. I then use the abbreviation of the parameter /Database1, /db1, and the database name came from my object, and the column name, $_.DATABASE_NAME. Then the magic. The parameter /MakeScript, I again abbreviated to /mkscr, along with a path, in which I included the database name in order to separate out each database, was all that was needed to complete the command.

In short, three PowerShell commands and one call SQL Compare’s command line are all that are needed.

When you run this script, you’ll get a folder for each database and inside that will be a complete set of scripts for recreating the database. You can use this to script out your databases for inclusion in a source control system, or for whatever else you might need.

This example is just a small taste of what’s possible to automate using the command line utility in SQL Compare. Further, you can see that it’s very possible, easy even, to integrate SQL Compare with the primary means of server automation within Microsoft today, PowerShell.

---

PS: I won’t normally be posting much Red Gate stuff on my personal blog. I’ll be doing that inside the official Red Gate area where there are lot more eyeballs looking at it. But, until we get that all set up, I really wanted to share this little tid bit.

Microsoft gave to me, an excellent new management language. Yeah, so it doesn’t rhyme or match the song in any way, but as far as gifts of the season go, PowerShell is it. There are a couple of problems with PowerShell, first, it’s not installed everywhere, and second, DBAs just haven’t quite latched on to this new language as a management tool.

That second problem is absolutely not one that I would say about Aaron Nelson (blog|twitter), our next 12 Days of SQL blogger.  Aaron is one of the leading lights out there educating DBAs, and everyone else for that matter, about the strengths and capabilities of PowerShell as a management language for SQL Server. I’ve said it twice, and I hope you noticed, but I didn’t say scripting language, because PowerShell isn’t. It’s a management language. It’s a way to automate the management of your servers and that’s what Aaron’s work is all about. He blogs and tweets and presents quite a lot about PowerShell. If you’re trying to learn this excellent tool, you should be reading his stuff.

In particular, the post that has me the most excited, out of all the work that Aaron has done this year, is this excellent explanation of remoting with PowerShell. Why this post in particular? Because remoting, combined with asynchronous calls means you can send a PowerShell script to any or all servers in your environment, at the same time. It means you can perform serious, enterprise level management tasks in an automated and repeatable fashion and you can do it to all your servers at the same time, easily. Yeah, I said easily. That’s because it is easy. Read Aaron’s excellent explanation and you’ll agree. Once you understand how to call all your servers remotely, it’s even easier to then pass them a script, call a stored proc, send a DBCC command, or just about anything else. This is why I call PowerShell a management language, because we’re not talking about scripting here, we’re talking about managing your servers, and that’s exciting.

On the 3rd Day of SQL, Microsoft gave to me, 3 excited screams… Sorry, but you’ll have to wait until Monday for the next installment, but the wait, I assure you, will be worth it. That’s because David Stein (blog|twitter) will be shouting a post in your direction that will absolutely be something you want to hear (and you’ll hear it, loud & clear). Now, I’m teasing Dave, and you can ask him why, but I mean it in good fun. David’s a FreeCon alumn, a nice guy, and a talented individual. If you’re not checking his stuff regularly, you really should be.

Sigh… I just spent almost a full work day trying to come up with, what turned out to be a single line script. The requirements were simple. After patching of servers is completed, and everything is rebooted, sometimes, not often, but sometimes, SQL Server Agent doesn’t restart correctly. I was asked to put together a script that will check a list of servers to see if Agent is running. Any PowerShell guru’s reading this are either laughing or crying, already.

So, I started exploring how to connect to the server using using some type of PS-Drive, but I quickly figured out that I’d have to loop through the servers that way. Plus, I don’t think there’s a way to check Agent status through the SQL drive. So that went out the window. Then, it was to use Invoke-SQLCmd. After all, I can call multiple servers through the -ServerInstance property, so I just need to find a good T-SQL statement to check Agent status… Googling & Binging didn’t turn up a good T-SQL statement to check Agent status (not saying there isn’t a way, but I couldn’t find one easily, plus, this is a story about PowerShell). On to SMO. All I had to do was figure out how to connect to the server and the SQL Agent object through SMO. I’d have to loop through the servers, but for such a simple script, that shouldn’t be hard. Ever tried to connect directly to the Agent through SMO? Me neither. It’s a pain.

Finally, I referred back to Don Jones (blog|twitter) book, Windows Powershell 2.0, TFM (and a more appropriately named book, there isn’t), and noticed a statement, Get-Service… Hey! Isn’t SQL Agent basically a service? Yes. Yes it is. Here’s the final script, in all it’s simplistic glory:

Get-Service -DisplayName "SQL Server Agent*" -ComputerName (Get-Content "servers.txt") | Select-Object MachineName,Status

Yep, it was that easy. Kids, don’t try this at home. Take the time to learn what you’re doing. Don’t assume you know.

One of the best things to come out with Powershell V2 is remoting and asynchronous calls. Between the two of these, you can basically send commands simultaneously to a number of SQL Server instances. BUT… and there always seems to be one of those, there is a lot of work required to get this running right. I’m going to outline what I did recently to test the ability of PowerShell to begin administering my servers remotely. Hopefully this provide the basis for a checklist and a how-to. I’ll update this post over time so that I get things right.

Enable remoting on the machines you wish to call

This requires admin privileges, but it’s pretty simple unless you need to modify which ports are available, etc. But to get it going the easiest way:

Enable-PSRemoting

You’ll get a warning outlining what this will do to the system and asking if you want to continue:

Running command “Set-WSManQuickConfig” to enable this machine for remote management through WinRM service.
 This includes:
    1. Starting or restarting (if already started) the WinRM service
    2. Setting the WinRM service type to auto start
    3. Creating a listener to accept requests on any IP address
    4. Enabling firewall exception for WS-Management traffic (for http only).

Do you want to continue?
[Y] Yes  [A] Yes to All  [N] No  [L] No to All  [S] Suspend  [?] Help (default is “Y”):

There may be other prompts about the operations that are listed above (in fact, “will be” is a better way to phrase it). In general, work through the help files to understand what all that means.

Configure Sessions

We’re talking about working with SQL Server here, so you know that you need to load the snapin. Easy right. In fact, you probably already have it loading by default through your configuration. Ah, but here’s the issue. When you’re running remotely, the session on that other machine is not running under your context. So it’s not going to use your configuration at all. Instead you need to define a session. There are several ways you could do this. Steven Muraski (blog|twitter) has a mechanism for forcing configurations to the remote machines. I haven’t tried this yet. I did the brute force approach.

First you create a script that holds the session information you want on each of the machines, in this case:

add-pssnapin SqlServerCmdletSnapin100

add-pssnapin SqlServerProviderSnapin100

That loads the necessary snapins for SQL Server. Once that’s done, you have to register this as a named configuration on the system:

[sourcode language="powershell"]Register-PSSessionConfiguration -Name SQLShell -StartupScript C:\scripts\sql.ps1[/sourcecode]

Again, this is run on each machine that you wish to remote to. You can do this through remote sessions, funny enough. Once you’ve enabled remoting and set up the sessions, they’ll stay in place, as is, even after rebooting the system. This means you only have to do this once.

Remoting to SQL Server

You can create multiple sessions and then call them asynchronously, but instead, I tried a different tack. This is my full script:

$instances = @{"Server1"="Server1\InstanceA"; "Server2"="Server2\InstanceB"}

$job = Invoke-Command -ComputerName (Get-Content "c:\scripts\serverlist.txt") -ScriptBlock {param($rinstances) Invoke-Sqlcmd -ServerInstance $rinstances.$env:computername -Query "SELECT * FROM sys.dm_exec_requests WHERE session_id > 50"} -JobName tsql -ConfigurationName SqlShell -ArgumentList $instances

Wait-Job tsql

$results = Receive-Job -Name tsql
$results

Stop-Job -Name tsql
Remove-Job -Name tsql

I created a list of servers and put it in a file, serverlist.txt. Because I’m dealing with instances, I need a way to match the list of servers to the instances. I did this with the associative array (aka hash table), $instances. I’m calling Invoke-Command and passing it a list of computer names through the serverlist.txt file. I pass that a script block, more about that in a minute, and I set a JobName as tsql. This makes the Invoke-Command into a remote job, but in my case, a set of remote jobs. I pass it the the configuration we created earlier with -ConfigurationName.

The ScriptBlock is pretty straight forward from there. The one extra wrinkle that I had to get right, and thanks to Steven Muraski, who got me through this little roadblock as well as several others (all through Twitter by the way), is that you have to get your local parameter into the script block through the use of param and -ArgumentList. Then I just called it based on the server name I was running on (yes, what if I had more than one instance, I didn’t, OK?).

Then, because I wanted to see it and didn’t want to go through a process of collecting information as it finished, I just told it to wait on the job, by name. Then I collected the results, displayed them through the default and shut down the job and removed it.

So this is one, admittedly less than perfect, approach to getting remoting working with SQL Server in PowerShell. I’m going to spend more time on this to refine the process.

For a completely different view of the exact same problem, go check out what Aaron Nelson (blog|twitter) did. We were working on this at roughly the same time and exchanging notes. He helped me, I hope I helped him a bit.

I’m still trying to learn PowerShell better. The opportunity to answer simple questions and problems with the tool is hard to pass up. We had a need to clean up data directories where data files were left behind or people put inappropiate files, so I wrote the following Powershell script:

param([string]$filelocation="",[string]$sqlinstance="(local)")

Set-Location $filelocation

foreach($file in get-childitem)

{$base = $file.Name;

$result = Invoke-Sqlcmd -ServerInstance $sqlinstance -Query "SELECT DB_NAME(mf.database_id) AS db FROM sys.master_files mf WHERE RIGHT(mf.physical_name,LEN('$Base')) = '$Base' UNION ALL SELECT 'NoDb' AS db WHERE NOT EXISTS (SELECT DB_NAME(mf.database_id) AS db FROM sys.master_files mf WHERE RIGHT(mf.physical_name,LEN('$Base')) = '$Base');" ;

if($result.DB -eq "NoDb" -and $file.Extension -ne ".cer"){Remove-Item $base}}

It’s a very simple script. It takes a UNC and a server instance and then walks through the files in the UNC and validates whether or not those files exist within databases on the server. If they don’t exist, it deletes them. That’s it.

I’ve published this to the Technet Script Center Repository right over here. I’m going to work on making it a bit better, so for updates, go there.

I’m working with PowerShell, and digging it. I decided that I wanted to create a new script (blog post later, if I get it to work) and I wanted to try out different scripting tools to build it. I started with PrimalScript from Sapien. It’s slick. It’s powerful. It has very nice code completion, a great GUI, integration with source control out of the box.

I started scripting and everything was fine. Then, I needed to run invoke-Sqlcmd so I decided to change the profile on the shell so it would load the SQL Server cmdlets automagically. I started getting the error:

Get-ItemProperty : Cannot find path ‘HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps’ because it does not exist

Hmmm… weird. I’m pretty sure I used the same command on my workstation as on my laptop. So I checked it out. Sure enough, same command. So I navigated through the path to ensure everything was in place. I opened up a seperate shell on the laptop and on the workstation. They both showed the path just fine. I couldn’t figure it out. So I opened up the registry and browsed through directly. Yep, the path was there. I opened a shell directly from PrimalScript. I got the error again. Then I tried to navigate to my path. No love. Why?

Then I noticed, in the path in the window that opened from PrimalScript, two little numbers, 32. It’s running the 32 bit shell. Funny enough, the install of SQL Server is 64 bit. It’s visible to the 64 bit shell, the one I ran, but it’s not there to the 32 bit shell. Now I get to track down how to configure SQL Server cmdlets to be visible to both the 32 and the 64 bit shell. Fun.

Idera’s PowerShell Plus has a 64-bit version. I’m just trying to get Quest’s PowerGUI installed, but it looks like it’s 32-bit. This is something to keep an eye out for when choosing and configuring your editor.

I’ve been attending a Powershell fundamentals class with Don Jones (blog|twitter). If you read my blog you might be aware of the fact that I’ve posted a few PowerShell scripts in the past.  So why was I attending a fundamentals class? Because I didn’t know what I was doing. I knew going into the class that I needed a better grounding in the fundamentals of Posh, but after the first day of Don’s excellent class, I realized that I had been working with PowerShell and didn’t have a clue how it really worked.

Don’s class is excellent and I could spend a lot of time talking about just that (which I’m sure would make Don happy). However I want to concentrate on something that he said during class that really resonated because I think it’s true. You don’t hear “true” things all the time, so when one jumps up and bites you, it’s worth paying attention. I don’t have his exact quote written down, so this will be more than a bit of a paraphrase. Don laid down the argument that Microsoft is creating a two tier structure where low level admins will have a GUI and the real experts will be using PowerShell. He showed how some of the functionality available in AD is already split and he said that more and more products coming out of Microsoft are going to be showing this same split.

So why do I see this as important? Well, it’s simple really. I don’t want to be in the low level, second tier, LOWER PAID, MORE EASILY REPLACED, set of knowledge workers. I want to be in the group that really can make things go. I want to ensure my employability into the future (at least until I win the lottery… which will happen right after I start playing it). You may not like PowerShell. You may not like Microsoft, SQL Server, Windows or any of that stuff. You may be a devoted Mac-head (like Don, Brent, Aaron..). But if your job is related to managing Windows servers, SQL Server, Operations Manager, SharePoint, Exchange… you get the idea, regardless of how you feel about PowerShell and the rest, if you want to be better, more powerful, and more employable, you need to learn PowerShell.

The good news is, it’s not that hard. The better news is, you can contact Don and get into one of his classes. The even better news is, if you’re a lazy, good for nothing lay-about, like me, learning PowerShell will make you more efficient, which means more time to lay around doing nothing while still looking good for the boss. So for those of you who believed that they just didn’t need to learn PowerShell… well, you’re wrong. Go get started. I’d suggest learning the fundamentals first, not just hacking away at it like I did. Don’s book might be a good place to start (TFM…. [snicker]).