The Ashdar Partners Blog

Lately, I have been trying to up my game a bit and one of the things I am doing is using some of the latest tools and bits of things. (Partly, this is because I have been working with some old technology lately and I am feeling "dated". Ask me about Fox Pro, if you dare.) PowerShell After many years of not seeing value in updating, I am moving to PowerShell 6. (Version 6.2, specifically.) Frankly, more things work than I expected. I haven't seen any performance regressions and things have gone smoothly. I think I spent more time incorporating the PowerShell version into my Prompt function than anything else that I had to do because of the upgrade. SSMS I am running the absolutely-latest build of SSMS. I skipped some SSMS builds when the removed the diagramming functionality. Now that it has been put back into the product, I am on the update train again. Aside: The diagramming feature isn't awesome but it is easy to use, requires no installation, doesn't ...

(Actually, they are up to version 1.0.2 already.) I've been using DbaTools for a number of years. I have come to rely on it more and more. Now, I find it at least as deeply entwined in my PowerShell code as the SqlServer module is. All right-thinking people use DbaTools. And you should, too. More seriously, DbaTools is one of the few PowerShell modules I absolutely rely on, on a daily basis. I use DbaTools so frequently that I can't easily categorize it all. In short, I can: Check servers, instances, databases, disks, security and many other things for issues on a daily basis Check business rules on a daily basis Restore databases to test environments on an ad-hoc basis. I have this working so smoothly that I forget that I have Ola Hallengren's backup scripts actually running the database backups.  Load data into databases Extract data from databases I've never actually used DbaTools for it's original purpose, which was migrating databases and instan...

If you want to see a cool ten-minute, "lightning" demo of how you can use PowerShell and Excel together to analyze data or produce nice reports, watch this YouTube video , from 7:47 to 18:22. This is presented by Doug Finke, the author of the Import-Excel PowerShell module.

Here is a Run As Radio chat with Jeffery Snover . Jeffery Snover is the person most responsible for PowerShell, which revolutionized my approach to database administration. He has a background in scripting going back to VMS (IIRC), which is how he came to create PowerShell. His Twitter bio lists "Microsoft Technical Fellow/ Lead Architect for Enterprise Cloud Group/ Azure Stack Architect/ PowerShell Architect / Science fan". From my point of view, he directs strategy for Windows administration. I listen to him because the things he talks about are likely to influence my work life and they give insight into how the PowerShell team expect people to use their product. The podcast covers a variety of things in a light way. The thing that grabbed my attention the most was that Snover seems to be saying that Windows will be implementing things similar to what linux does with root and sudo. (My linux experience is limited but my two takeaways are: You never log in ...

This blog post mentions a few of the most interesting articles that I have read recently, or recently-ish, with a little bit of my commentary on each article. What's the difference between a temp table and a table variable in SQL Server?  This  is the best answer I have seen. Here is a short and sweet bit of  PowerShell code  that can make a persons life a little more enjoyable by providing some feedback to long-running processes. This  is from 2010, but I'd never seen it until now. I would temper it with Malcolm Gladwell's view that it takes 10,000 hours to become expert at something. (I am not sure if Mr. Gladwell originated that thought, but he has popularized it.) I'm not so sure that 'polyglot developers' will know everything there is to know about all of the languages on their resume, plus Active Directory administration, plus database clustering, plus SAN administration, plus VMWare, plus being up on the latest half-dozen NOSQL darlings, but may...

Whenever I do anything with an SSRS server, I always backup the key and park it somewhere. Automating that would be handy, and  this seems to fit the bill nearly 100%. The only additional think that I would like to see something that uses PasswordSafe or KeePass to keep a password, rather than a table.

I have been using Powershell since I first got wind of the beta and realized that all of my Windows Scripting Host skills would become obsolete.  However, I've never had the opportunity to try out the Powershell job step type for SQL Agent jobs until today. I know that the Powershell that "comes with SQL Server" isn't the same as the regular Powershell but, other than some fiddling around with the SQL Server file system provider, I've bumped up against those differences before. It's just different enough to be frustrating, so I'm blogging about it. It's a bit of a rant, but maybe it will save someone some time or someone can point out something that I've overlooked. My goal is to have a simple script that I can use in a SQL Agent job that can automatically discover and backup any SSAS database that exists on the same computer that as the SQL Agent. (This is an improvement over my old method, which used hard-coded XMLA queries to backup speci...

A while back, I read Linchi Shea's blog posting on finding partition offsets . I recently found some time to rework his C# file plus perl script solution into a single Powershell script. The upsides to my solution are: It's Powershell-only, so there is nothing to compile and you don't need to install perl. Using Powershell provides some added bling, for no real cost. It's easy to construct a pipeline that will interrogate many servers or will provide sorting or filtering. The major downside to my solution is: You must install Powershell. This shouldn't be a problem on Windows Vista or Windows Server 2008. Please be careful of the formatting. While embedding the code here is more convenient than a zip file, I'm still learning blogger and things may not wrap the way that I would like them too. I've also looked for a way to post syntax-highlighted Powershell code. There are solutions for other languages, but nothing for Powershell is jumping out at me so I will...

Two quick ones for this post, before the US 4 th of July holiday. Both functions have one thing in common: use of environment variables. In order to see the environment variables that are available, just type: dir env : (This listing could be extensive. ( dir env :).Count returns 51 on my laptop.) Environment variables aren't as popular as they used to be, with settings located in registry entries, . ini files and . xml files. For a few things, it's still easier to find their values through an environment variable than to figure out where they hide in the registry or google for the proper .net calls. When I set up a new machine, I add a path to the environment that points to my local copy of my scripts. This can be very handy since I still have some . vbs and . wsf scripts lurking around. whoami simply displays the name of the currently logged in user. It's very simple, but it is also a good, simple example of how to use environment variables in a script. Here i...

For this posting, I present an uptime command. This is the sort of thing that most scripters wind up writing, I suppose. For me, the neat thing was how just a few lines of script produced a program that was more flexible and usable than an old, compiled EXE program that I had been carting with me around on a USB stick for years. A little bit of this code is a refactoring of code posted at BSOnPosh , but that page seems to have been taken down during a site re-arrangement and my search didn't find anything. IIRC, I wanted an easier way to get uptime information for more than one server, so I refactored some of his code (the WMIDate conversion, which I wanted to be able to re-use for other things, is sort of fiddly) and added my own code to loop over the pipe line input, to produce the TextDescription and to make the select-object code more readable by using the $fields variable to hold all of the necessary script blocks. Anyway, I present what I've got for your perusal. Persona...

Today's posting covers a function which mimics the 'watch' command, which is popular on Unix and Linux systems. The watch command will repetitively run a command line and display the output. This is useful for watch the progress of systems writing a series of files or waiting for a server to come online. Just start a shell, run the command and park the shell so it is still visible, but out of the way. Even though my function does not do a particularly thorough job mimicking the Unix watch command, I still get a lot of mileage out of it. This one is so simple that I am just going to paste it in here and hope that the blogging software doesn't drop the formatting. I'm still learning the quirks of blogger, so please bear with me. Here goes: ############################################################################## # Purpose: More-or-less mimic the popular unix "watch" command function Watch-Command { param ( [string] $command, [int] $i...

In this post, I will discuss Get-LogicalDisk . Get-LogicalDisk is a function that retrieves disk information from remote servers via WMI. This allows for quick and flexible reporting on storage usage and availibility. This is a bit of a departure from "SQL Server"-oriented functionality that I have been describing in recent posts. In some shops, free disk space or, more likely, the lack of it is a daily battle. Every morning, I run a script that checks for many different kinds of "negative events" that might have happened over night. A "negative event" might be a failed job, database growth or a server running low on disk space. The latter check calls Get-SQLServerList and Get-LogicalDisk to do all of the heavy work. The script is straightforward, so I'll just continue with my tradition of a brief overview with some examples. (This is a blog, after all, and not really suited to voluminous detail. FWIW, it is my opinion that detail on what a confusing p...

In my last post, I introduced Get-SQLServerList. In this post, I am going to describe some more ways to leverage the Get-SQLServerList and Invoke-SQLQuery functions. Basically, I am suggesting that you follow this formula: Step 1. Put your server names in a text file called "sqlserver-list.txt" and put the file in the root of your C drive. Step 2. dot-source the functions that I've given you. Step 3. Profit. (I'd like to remind you that I normally alias Get-SQLServerList as "ssl" and Invoke-SQLQuery as "iq". This is to save on typing. It's also worth pointing out that I normally use the "ft" alias instead of typing format-table. On with the show...) Let's say that your boss wants to know which 64 bit servers aren't at SP2. One way to do this would be to spend a lot of time manually connecting to each system, running a query and putting the results into a spreadsheet. Another way to do this is to go to the shared Word or Excel ...

In my last post, I described Invoke-SQLQuery. In this post, I describe Get-SQLServerList, which retrieves a list of SQL Servers. Get-SQLServerList leverages Invoke-SQLQuery and provides an easy way to provide server names to a pipeline. This time, the zip file for Invoke-SQLQuery contains two functions. The first function is Query-ServerCatalog. This function builds a simple SELECT statement and then uses Invoke-SQL Query to run it. This is useful as an example of the sort of thing that you can do with Invoke-SQLQuery. Query-ServerCatalog will return a result set full of servernames, assuming that you have a particular table in a particular database on a particular server that holds these names. Setting that up and keeping it current is a bit of work and probably is not worth doing unless you work in a large environment with a group of DBAs. The more interesting function is Get-SQLServerList. Some of the highlights of Get-SQLServerList are: It reads a text file to find the names of se...

In this posting, I am going to (briefly) discuss Invoke-SQLQuery , which will run a SQL Server query against a computer or a group of computers and then return the results as a Powershell object. This object can be fed to another element in a pipeline, where it can be sliced, diced and stored using Powershell's intrinsic capabilities. About a year ago, I was looking for a way to return a result set to Powershell. It seemed that I was forever logging into a SQL Server instance, running one query, then logging out and repeating the whole thing on a different instance. One day, I read a posting on Powershell and SMO by Muthusamy Anantha Kumar. I noticed some ado.net code towards the bottom of the page and I same the proverbial light: Powershell is to .Net objects what VBScript is to COM objects. Armed with that, I started working on a function that ultimately evolved into Invoke-SQLQuery. I'd like to point out the following facets of Invoke-SQLQuery: This function directly accept...

This posting continues with my series of articles that describes my Powershell environment by describing a function that I use to change volume labels on remote machines. A file with the function is available . I wrote this function because I needed a way to change labels associated with volumes on remote machines. Earlier versions of this function were actually written in VBScript. I rewrote that old code in Powershell because I wanted more consistency with the "standard" Powershell command line switches and I am (slowly) moving away from all of my old VBScript programs. Before I had a script, I would use RDP to connect to the remote machine, login and then use Windows Explorer to change the volume labels. This was a lot of effort for a small change. I use the volume label at one of my clients as a quick way to discern between SAN and non-SAN volumes by prepending "san" or "local" on the volume labels. When this convention was adopted, there were a fair n...

Anyone who has used sqlio knows that it's output files are not particularly readable. I used to manually run sqlio tests, then retype the output into a spreadsheet so that I could more easily analyze the data. This proved time-consuming, error-prone and boring, so I wrote a script that parses all of the data that I wanted from the sqlio output file. This post provides that code, and some background on how I use it. I will return to my series on my Powershell set up next time. The first thing to do is to run your tests with sqlio and get it's results into files. This is left as an excercise to the reader. I have a .cmd file that I use to do this. I use a .cmd file because most of the machines that I test do not have Powershell installed on them. I am including this file in the SQLIO.scripts.zip file. The reason that I break the running of the tests and the parsing of the output into two seperate processes is to decouple them. It can take quite a while for sqlio to run, especial...

Last time, I explained that my profile consists of several files, with various functions (and associated aliases) broken out by functionality. Each of these "subprofiles" is loaded when my standard Powershell profile runs. . "$env:winbin\psh\profile.Security.ps1" . "$env:winbin\psh\profile.RemoteService.ps1" . "$env:winbin\psh\profile.SQLServer.ps1" . "$env:winbin\psh\profile.dstrait.ps1" . "$env:winbin\psh\profile.$($env:userdomain).ps1" The last line will load a client-specific file, depending on the credentials that I am currently using. Some of the functions in these files are mine and some of them were borrowed from various web sites. When I borrow a function, I add a small comment header that describes where the function came from. This would allow me to go back and look for more interesting scripts from that author, or to look for improved versions or allow me to give credit where credit is due. Some of these functions ...

This posting gives an overview how I have set up Powershell on my workstation. Any heavy shell user normally customizes his (or her) environment. These environments are (and will likely always be) a work in progress. As you learn things and write additional functions to help you with your tasks, your environment will grow and change. I find that I am tweaking things even as a I write this posting. Aside: One of the things that I am forever tweaking is function names. I try to follow the Powershell verb-noun convention, but there are lots of spots where I have had to make judgment calls. It's not hard to find inconsistencies. For example, I vacillate between "Report", "List" and "Get". I'm not crazy about Get. My issue with Get is that it seems that Get should return an object and be used like this: $result = Get-Thingy $result.MemberFunction $param $result = $null whereas I run a lot of stuff that just returns information. A 'Report' is usu...