10Dec 2010 by Grant Fritchey 15 Comments

12 Days of SQL, On the 2nd Day of SQL…

PowerShell
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,…
Read More
18Oct 2010 by Grant Fritchey 13 Comments

Powershell is Really Easy… If you know what you’re doing

PowerShell, SQL Server
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…
Read More
27Jul 2010 by Grant Fritchey 4 Comments

Powershell Remoting with SQL Server

PowerShell
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…
Read More
19Jul 2010 by Grant Fritchey 6 Comments

SQL University: Introduction to Indexes, Part the First

PowerShell, Spatial Data, SQL Server, T-SQL
Right, all eldritch tomes are to be closed and Elder Signs are to be put away during this course. Welcome to the History department here at the Miskatonic branch of SQL University. Why the History department? Well, first, because I like history and have frequently thought I would enjoy teaching it. Second, because I needed a hook upon which to hang part of the story I want to tell. What story is that you ask? Why, the story of the Dewey Decimal System. We are interested in studying history and historians must classify our subjects carefully. For advanced students we'll be covering the Library of Congress Classification System and the... Right, I give, this is the introductory class on indexes. If you thought we were covering something exciting and sexy like PowerShell,…
Read More
06Jul 2010 by Grant Fritchey 1 Comment

Small PowerShell Script

PowerShell
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: [sourcecode language="powershell"]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…
Read More
30Jun 2010 by Grant Fritchey 6 Comments

One of these things is not like the other

PowerShell
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.…
Read More
25Jun 2010 by Grant Fritchey 6 Comments

Powershell Fundamentals

PowerShell
I've had the opportunity this week to take class with Don Jones (blog | twitter), PowerShell guru and author of PowerShell 2.0 TFM (makes me laugh, each and every time). I'm not even going to attempt to try to teach you some of the amazing information Don has spent the last week pouring into my ears (since most of  it spilled on the floor). But, I do want to pass on some high points, gotchas, tips, insights, that Don communicated to us during the class because they might help you pick up on the fundamentals of PowerShell. That's the most important lesson I got out of this week, you need the fundamentals to really start to put PowerShell to work. It's very easy to make the mistake that I did. I…
Read More
23Jun 2010 by Grant Fritchey 4 Comments

Learning Powershell

PowerShell
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…
Read More
28May 2010 by Grant Fritchey 6 Comments

Buy vs. Build

PowerShell, Tools
It's the classic question faced by everyone in Information Services. I know how to do this and I could build software to do it, but I'm a lazy b_____d so I'd rather just pick up a piece of software that does it for me. I love working for large companies because I can usually get them to purchase stuff so that I can loll around stuffing my face all day instead of doing actual work. Unfortunately, not everyone can afford to pick up Microsoft's Operations Manager or Idera's Diagnostic Manager. But you still need to monitor your servers. With buy eliminated, that leaves build. Which, is where this excellent blog post by Laerte Junior comes in. He lays out how to build a wrapper around calls to get Performan Counter…
Read More
19Apr 2010 by Grant Fritchey No Comments

Powershell Script for Verifying Space

PowerShell
First let me say, I know my Powershell skills are sub-par. I'm working on it. Slowly but surely. That said, I had a problem to solve. It's one that I could have done with TSQL, but it would be very ugly TSQL, probably involving dynamic queries, and even for admin scripts, I try to avoid that. So, I went for SMO and WMI wrapped by Powershell to solve the problem. What was the problem you ask? We automate as many of our processes as we can. One process we do is resetting databases from production or other sources. Our processes work very well, but we occasionally run into a problem where the source system db has grown and the target system doesn't have the required disk space. So, we needed…
Read More