Tag Archives: Powershell

Admin, Development, Powershell

Powershell to Excel

1 Comment

I got this question on twitter recently so I decided to blog it real quick. The question is, how do you output data to excel from powershell?

Well, there’s a free function that I’ve used and I’ve been quite happy with it. I’m not going to give a lot of explanation here except to say that when using it on a large dataset I’ve found it to be painfully slow. And by painfully slow I mean several hours. However, using it with a small amount of data isn’t too bad. I like it because it does all the lifting for me. I just output to the function and I’m done. So anyway, here’s the link to the site. The dev does a good job of giving examples.

http://www.lucd.info/2010/05/29/beyond-export-csv-export-xls/

Admin, Powershell

SQL Server 2012 Install Gotcha

1 Comment

For those of you who like scripting your installs as much as I do there’s something you should be aware of when installing 2012 on Windows 2012 R2. I don’t remember coming across it in Windows 2012 RTM, but maybe I’m wrong. All the same though…

When you install SQL 2012 on Windows 2012 R2 you’ll get an error part way through saying that you have to install NetFx3.
Normally you’d do this in PS as part of your script like this:

Add-WindowsFeature net-framework-features

Unfortunately you’ll get an error when you try to install this by default on a fresh Windows install. Look at the pic below…

WindowsFeatures

You can see that while the net-framework-features are present, the net-framework-core is ‘Removed’. This is actually what you’re looking for. So where do you get it? On the Windows DVD of course. So now your PS code to install this features looks like this… I’m assuming your Windows DVD in in the D:

Add-WindowsFeature net-framework-features -source D:\Sources\sxs

Now that leaves us with a problem. How do we get the Windows guys to install this for us by default when they give us our box? Because I’ve had very limited success getting them to change their image in a lot of places and they don’t want to go in afterward and install anything manually. So here’s what you do.
Get a hold of the Windows media just once. You can pull it from your MSDN account, or from the network share where they keep the Windows install, or just ask them for it. When you get it copy that entire folder to your SQL install folder. It’s only 275MB. Now you can have a copy of it for yourself and you don’t have to bother them about it. And being able to pass in the -source param means you can point it to any directory you like.
So now the top of your PS install script will look like this:

Add-WindowsFeature as-net-framework
Add-WindowsFeature net-framework-features -source D:\Sources\sxs

Now, if you’re doing your install through the wizard you will have to do this step. And I think I’ve heard that setup will complete just fine if you don’t install this feature, but I haven’t confirmed that personally yet. I also suspect that if it does complete manually just fine without it that it may go ahead and complete if it scripted, but it may shoot up an error in the log. And there’s just no reason to create errors when you don’t have to.

I’ll try to find time to test a couple different scenarios and let you know how it goes… you know, in all my copious free time.

Admin, Powershell

Powershell: Format array elements for SQL queries

5 Comments

Ok, here’s the situation. You need to format an array of values for use in a SQL query. You want the final output to look something like this:

SELECT * from dbo.T1 where col1 IN (1, 15, 17, 23, 55, 67, 88)

The elements in the IN clause are what you got from the array. It doesn’t matter how you got the elements into the array, just that you did. However, for the purposes of this, we’ll just fill the array ourselves.

$a = 1, 15, 17, 23, 55, 67, 88
$a

So now that we’ve got the elements in the array. For me this is typically the result of a query. I’m usually doing something like this:

$a = invoke-sqlcmd -query "select ID from SomeTable where col1 = 'current'"
$a

That’s how I typically fill that array. So anyway, this method works really well as long as the values in the array are numbers. See, in the SQL statement they don’t have to be surrounded by quotes so you can just join them.
Here’s how you join array elements into a single string in PS.

$a = 1, 15, 17, 23, 55, 67, 88
$b = [string]::Join(", ", $a)
$b

$Query = "SELECT * from dbo.T1 where col1 IN ($b)"

The JOIN method takes 2 arguments: The first is the element you want to place between each array element, in my case a comma followed by a space, and the array to apply it to.

Like I said above though this doesn’t really help when the elements need to be quoted.
What I need this time is for the SQL query to look like this:

SELECT * from dbo.T1 where col1 IN (‘2/2/2103’, ‘2/3/2013’, ‘2/4/2013’, ‘2/5/2013’)

The problem is that the elements aren’t quoted when they come in. If you wanted you could add the quotes to the query as you get it back from SQL, but there’s no guarantee you have control over that. And let’s be honest, it’s just fun to do it in PS.

Here’s how to use the same JOIN method in PS to surround the array elements in quotes.

$a = '2/2/2103', '2/3/2013', '2/4/2013', '2/5/2013'
$b = [string]::Join("', '", $a)
$b

$Query = "SELECT * from dbo.T1 where col1 IN ('$b')"

Actually, the differences here are subtle. What’s changed is in the Join method, instead of placing a comma between the elements, I’m placing that comma in single quotes. Here’s the result what you’ll get right after you do the JOIN.

2/2/2103′, ‘2/3/2013’, ‘2/4/2013’, ‘2/5/2013

Notice it gave you the quotes everywhere except the outer boundaries. And we took care of that by placing single quotes around it in the $Query line.

Alright, that’s it. You can now surround array elements in quotes if you need to so you can format the array for use in a SQL query.
Good luck.

Admin, Powershell, Time-savers

Log Management Made Easy

Hey guys… I wrote a nice little log management script I thought some of you might like. It doesn’t do anything to your system so it’s safe to run. Well, I’m turning xp_cmdshell on and off so if you don’t like that then this script isn’t for you.
I’m just putting more info into SQLPERF. These are the most common things I need to know when I have a runaway log. Of course feel free to modify it any way you like. Unfortunately, due to when PS came into play, you can only run this on SQL2K8 boxes and above. If you want to run it on lower boxes you’ll have to take out the PS portion of it.

One more thing before I give you the script. You’re responsible for anything you run on your box. So don’t blame me if something happens and you mess something up. Like I said, I believe this script to be safe but only if you manage the xp_cmdshell portion properly. If you run the script as it is now it’ll turn off xp_cmdshell and if you’ve got processes that rely on it then they will fail. So just be warned yet again that this is a risk. And the reason I’m turning it off again is because I don’t want to open up anything on your box that shouldn’t be. But I really like having the extra info so I don’t have to go look it up. I hope you do too.

Some important notes about the script:
1. It gives you the physical location of the log files, so if there’s more than 1 you’ll see more than 1 entry for each DB.
2. The ShrinkCmd has a variable at the top used to control its default. I like to shrink down to 1GB a lot of times, but set this to whatever you like.
3. You can expand this for yourself in ways I can’t do for you. For instance you could tie it to your log backup job to see when the last execution was and if it failed.
4. I’ve added the last log backup date for you… you’re welcome.
5. The nature of how PS works, you have to change the instance name at the top to the current instance you’re working with. There’s nothing I could do about that. I tried to make it as simple as possible.
6. The PS portion relies on xp_cmdshell. However, I turn it on and off for you in the script. If you want it left on, make sure you comment that portion out of the code or you could have stuff that breaks because you just turned on xp_cmdshell.

declare @LogSize int,
		@Instance varchar(100)
SET @LogSize = 1024
 
SET @Instance = 'localhost\default' -- If default instance then put Default ex: 'MyServer\default'
 
SET NOCOUNT ON
 
CREATE TABLE #LogSpace
(
DBName varchar(100),
LogSizeInMB float,
LogSpaceUsedInPCT real,
Status tinyint
)
INSERT #LogSpace
EXEC ('dbcc sqlperf(logspace)')
 
 
CREATE TABLE ##LogSpacePSTemp
(
DBName varchar(100),
LogBackupDate varchar(25)
)
 
exec sp_configure 'show advanced options', 1
reconfigure
 
exec sp_configure 'xp_cmdshell', 1
reconfigure
 
declare @cmd nvarchar(2000)
		SET @cmd = 'sqlps "cd sqlserver:\sql\' + @Instance + '\databases; $a = dir; foreach($DB in $a){$DBName = $DB.Name; $LogBackupDate = $DB.LastLogBackupDate; invoke-sqlcmd -query ""INSERT ##LogSpacePSTemp SELECT ''$DBName'', ''$LogBackupDate''""" -SuppressProviderContextWarning}"'
 
		--PRINT @cmd
		exec xp_cmdshell @cmd, no_output
 
 
 
select 
LS.DBName
, LS.LogSizeInMB
, LS.LogSpaceUsedInPCT
, D.log_reuse_wait_desc as LogReuseWait
, LT.LogBackupDate as LastLogBackup
, DATEDIFF(mm, LT.LogBackupDate, getdate()) as MinsSinceLastLogBackup
, D.recovery_model_desc as RecoveryModel
, MF.physical_name 
, 'USE [' + D.name + ']; DBCC SHRINKFILE([' + MF.name + '], ' + CAST(@LogSize as varchar(10)) + ')' as ShrinkCmd
from #LogSpace LS
INNER JOIN master.sys.databases D
ON D.Name = LS.DBName
INNER JOIN master.sys.master_files MF
ON D.database_id = MF.database_id
INNER JOIN ##LogSpacePSTemp LT
ON LT.DBName = LS.DBName
ORDER BY LS.LogSizeInMB DESC
 
drop table #LogSpace
drop table ##LogSpacePSTemp
 
exec sp_configure 'xp_cmdshell', 0
reconfigure
exec sp_configure 'show advanced options', 0
reconfigure reconfigure
Admin

Finding Triggers in Powershell

1 Comment

I was helping someone out with something so I thought you guys would like to have these 2 short lines of code that can be pretty useful.  Here I’m going to show you how to check your DB for tables that have triggers, and then for tables with no indexes.  Do with them what you will.

First, let’s talk about triggers and how to avoid them and why.

Triggers are a tricky thing to support becuase the complicate things.  They can be nested and you won’t even realize it.  Their firing order isn’t even guaranteed.  You can specify the first trigger to fire, but no ordering after that.  So you could see intermittent perf problems from both of these aspects of triggers.  And the reasons I usually see people putting triggers on tables aren’t even valid.  They need something to happen in another table like a audit or maybe they even need to change a value in the current row.  That’s the one that drives me crazy the most.  If you need to update a value you just inserted, then just change it before you insert it.  Inserts should be as fast as possible and putting an update inside a trigger for a row you just inserted is just stupid.  Put that code in an SP and do whatever you need in params so by the time you get to the insert everything is as it should be.

And if you need to do something in another table, like auditing that an action took place, then again put that code in an SP and just do the 2nd table write after the main insert.  This really isn’t hard to figure out.  In my career I think I’ve seen maybe a couple honest uses for triggers and I can’t even remember them right now.  But pretty much every time you need a trigger, you would be better served by putting several statements into a single transaction in an SP.  At least someone could easily see what’s going on and you can tightly control the order of ops.

So to find all the tables in your DB with triggers, just go to the tables node in PS and run the following code.  Once you get it you can do anything with it you like.

dir | ?{$_.Triggers -ne $NULL} | ft schema, name, triggers -auto

Easy, right?

Now, if you do a get-member you’ll see a handful of properties that give you info on triggers.

dir | gm

HasAfterTrigger
HasDeleteTrigger
HasInsertTrigger
HasInsteadOfTrigger
HasUpdateTrigger

And of course you can use those, even for what we’re trying to do here, but it makes the code a little long to be practical.  The problem is you have to compare all of those properties. 

dir | ?{$_.HasAfterTrigger -eq $True -OR $_.HasDeleteTrigger -eq $True -OR $_.HasInsertTrigger -eq $True -OR $_.HasInsteadOfTrigger -eq $True -OR $_.HasUpdateTrigger -eq $True} | ft name, triggers -auto

Both of these lines of code do the same thing, but one is just less tedious than the other.

And you don’t have to filter it at all.  You’re free to just look at the raw data if you like:

dir | FT schema, name, triggers -auto

Ok, that’s it this time.

Admin, Development, Powershell

What is an Overload?

3 Comments

When I teach powershell the question I get from DBAs most often is what is an overload. Because when we get into methods you can’t go very long at all without stating that they’re overloaded and most DBAs just don’t live in that world enough to know programming terms like that.

Overloading is when a function, method, or whatever can be called with different sets of parameters. Let’s take a good example of calling the ChangePassword() method under the Logins context. I’m gonna walk you through this step by step because I want to make sure you understand.

Let’s start by opening SSMS and right-clicking on Logins. Then to go Start Powershell.
You’ll be presented with a prompt in the Logins context. Now, if you do a get-member you’ll see a list of properties and methods. We’re concerned with the methods here. As a quick aside, a property is an adjective. It tells you something about the object, while a method is a verb. It’s something you can do to the object. So in our case you can see several properties like SID. SID is a property of a login… it describes something about the login. But the method ChangePassword() is a verb. It’s something we can do to the login. Methods are like functions in SQL more or less. Anyway…

So here’s a query you can type to get down to the info we’re interested in. I’m going to narrow the results down to just the ChangePassword() method we’re interested in so we don’t have to pick through a bunch of results. 

dir | gm | ?{$_.name -eq "ChangePassword"} | FL

Here’s a screenshot of the results.
LoginsGM

Ok, now we can actually talk about the overloads. Notice that in the Definition column you there are 3 ways to call this method.

1. System.Void ChangePassword(string newPassword)
2. System.Void ChangePassword(string oldPassword, string newPassword)
3. System.Void ChangePassword(string newPassword, bool unlock, bool mustChange)

In the 1st one you can simply pass in the new password.
In the 2nd one you can pass in the old password and the new password.
and in the 3rd one you can pass in the new password along with a bit to unlock the acct and whether they must change it when they login next time.

Each one of these sets of parameter combinations is called an overload. Sometimes you’ll see a method that has no overloads. That means there’s only one way to call it. The Refresh() method is like this. It’s not overloaded because you can only call it one way… with no parameters.

dir | gm | ?{$_.name -eq "Refresh"} | FL

RefreshGM

You can see from the output here that there is only one item listed in the Definition column. There’s only one way to call the Refresh() method, and that’s with no parameters. If you try to call it any other way you will get an error.

So a method can either be overloaded or not. And this is how you know how to call any method. You do a get-member on it and look at its definition. You’ve probably noticed by now that the definition also gives you the data types and functions of the parameters it’s expecting. Above for the ChangePassword() method you can see that the newPassword parameter is a string. So you automatically know you’re going to call it like this:
ChangePassword(“MyN3wPassw0rd!!!”)
And you know the 3rd overload takes a string and 2 booleans. So it’ll look like this:
ChangePassword(“MyN3wPassw0rd!!!”, 1, 1)
or like this:
ChangePassword(“MyN3wPassw0rd!!!”, $true, $true)

Ok guys, that’s about it for overloads. I wanted to give a nice thorough explanation for the beginners out there. Sometimes these concepts can be a bit foggy to understand and .Net guys always explaining things with .Net terms doesn’t really help. So I wanted to try to put it in nice plain English.
Let me know if you have any questions.

Admin, Powershell, Time-savers

Why is adopting Powershell so hard?

3 Comments

One of the biggest battles I fight when I teach PS or show it to different companies is adoption.  DBAs and managers just don’t get why they should be using PS to manage their environments.  And the reason so many of them are slow to adopt is because the right message is very hard to come by.  In fact, I see the wrong message all the time.  This was driven home again just now as I received my “SQL Server Pro UPDATE” email (read it here).  The editorial was about why DBAs should care about PS.  It was written by Michael Otey (pronounced Otee) and I’m sorry to say that as the Sr. Technical Director he’s completely missed the mark.  He’s managed to relegate the most important management tool DBAs have to a mere sideline player that allows you to combine SQL work with OS-level work.  Dude, you’ve completely missed the boat!!!

I’ve said this a number of times and I’ll say it again here.  Let’s get the right message out about PS.  Let’s start telling everyone that truth about it and that truth is that you’re going to get left behind if you don’t know PS and you’re not going to be able to provide true value to your company.

I’m a working production DBA in a very large shop.  And I can tell you with absolute authority that PS is more than just a way to give you some OS-level functionality in your SQL processes.  PS is vital to the management of my shop.  The primary role of PS is to allow you to manage hundreds of objects at once with just a few lines of code.  As you’ve seen in my videos again and again, and in my classes again and again, you just can’t manage any decently sized environment by hand.  It’s just too much and it’s accident-prone.

Now, a large environment doesn’t have to mean hundreds of servers.  Oh no, it can mean dozens of DBs on a single server.  It can mean dozens of schemas in a single DB, or dozens of tables, SPs, views, etc.  Or it can even mean dozens of jobs or logins.  It doesn’t matter what you’re cycling through.  The point is that PS is much more efficient at managing these objects than T-SQL will ever be.  There may be a built-in SP for running a T-SQL statement against all the DBs on your server, and there’s even one for running something on all the tables I believe.  But there’s nothing for all the jobs, or SPs, or views, or logins, or credentials, or any of the other many objects you can manage natively with PS.  With just a few characters of code you can manage all the tables in your DB.  And with just a few more characters you can do that same operation against all the DBs on your server.  And with yet just a few more characters of code, you can do that same operation against all the DBs across as many servers as you like.  THAT’S THE POWER OF POWERSHELL.  It’s more than just a way to manage files while you’re doing some T-SQL operations.

A perfect example is adding user accounts to DBs on several boxes.  We quite often get contractors who come in and do specific work for us and they need to be given rights to all of the environments for a given application.  So this one we have has 500+ DBs on each server.  There are 7 prod servers, 3 QA servers, and 5 dev boxes.  All of them have 500+ DBs on them.  So that’s 15 servers, and at least 7500 DBs that I have to add these users to.  And using windows groups will only take you so far because when the next contractor comes in he may need different rights so you still find yourself modifying the perms regardless.  The point is I go through this exercise about once every 2mos and PS has made it a trivial matter.  And that’s just ONE example.

The time has come to stop downplaying the role of Powershell in SQL Server.  DBAs need to be running to it.  We’re being asked to do more and more with less time and fewer resources.  And there are too many courses being taught, too many SQLSaturday sessions, too many videos (FREE on MidnightDBA.com) for you to remain in the dark any longer.  I’ve managed 900+ servers by myself using mainly PS.  I’d say it goes quite a bit deeper than being a helper to keep you from using xp_cmdshell.

Admin, MidnightDBA News, Powershell, Videos

Powershell till you drop

Ok, well I’ve been very busy again and released 3 new PS vids today. 

There’s one on dropping tables.  I use regex to make this happen, so even if you’re not interested in dropping tables, you can come learn how to do a simple regex.

http://midnightdba.itbookworm.com/VidPages/PowershellDropTables/PowershellDropTables.aspx
http://midnightdba.itbookworm.com/VidPages/PowershellDropTables/PowershellDropTables.wmv

 

The next one is on truncating tables.  Here I just limit it by a specific schema.

http://midnightdba.itbookworm.com/VidPages/PowershellTruncateTables/PowershellTruncateTables.aspx
http://midnightdba.itbookworm.com/VidPages/PowershellTruncateTables/PowershellTruncateTables.wmv

And the last one is just damn good.  I show you how to get sp_configure functionality in PS.  There’s a trick to it so don’t discard it cause you think you can figure it out on your own. 

http://midnightdba.itbookworm.com/VidPages/PowershellServerConfigSettings/PowershellServerConfigSettings.aspx
http://midnightdba.itbookworm.com/VidPages/PowershellServerConfigSettings/PowershellServerConfigSettings.wmv

 

Admin, MidnightDBA News, Powershell, Videos

Get IP and DNS in Powershell

Hey guys, I posted a new video last night on how to get IP and DNS info from your servers.  I know there are more ways to do it so if you guys have a way you like better send it to me and I’ll make another vid.

http://midnightdba.itbookworm.com/VidPages/PowershellGetIPandDNS/PowershellGetIPandDNS.aspxhttp://midnightdba.itbookworm.com/VidPages/PowershellGetIPandDNS/PowershellGetIPandDNS.aspx