I tuned into twitter yesterday for a couple of minutes and found a great conversation going on between Nicolas Cain ( blog | twitter ) and Dave Levy ( blog | twitter ) about checking Disk Space & Mount Points. I really like what they were working on because it was actually one of my top priorities for the week. I already have some code for checking both regular disk drives and mount points with PowerShell but I was looking to improve it and get it ready for production monitoring. Everyone’s environment is different and they build things based on needs and pain points. For my own environment I took a little of what Dave put together and a little of what Nick put together and built my own function for my environment. I’m still trying to add some more information to it but here’s what I’ve got so far: Function Get-DisksSpace ([string]$Servername, $unit= “GB”) { $measure = “1$unit” Get-WmiObject -computername $serverName -query ” select SystemName, Name, DriveType, FileSystem, FreeSpace, Capacity, Label from Win32_Volume where DriveType = 2 or DriveType = 3″ ` | select SystemName ` , Name ` , @{Label=”SizeIn$unit”;Expression={“{0:n2}” -f($_.Capacity/$measure)}} ` , @{Label=”FreeIn$unit”;Expression={“{0:n2}” -f($_.freespace/$measure)}} ` , @{Label=”PercentFree”;Expression={“{0:n2}” -f(($_.freespace / $_.Capacity) * 100)}} ` , Label }#Get-DisksSpace The code above will create a PowerShell function (sorta kinda like a stored procedure only not really but just think of it like that if you’re a SQL person that’s new to PowerShell ) Here’s how you would call it: Get-DisksSpace “Win7NetBook” | Format-Table Or if you only wanted to know about the drives that are low on space: Get-DisksSpace “Win7NetBook” | where{$_.PercentFree -lt 20} | Format-Table This will return you the list of drives and mount points on the machine you listed and default the unit of measure to convert the results to gigabytes. The great news is that this code runs really fast. Their conversation yesterday literally saved me hours of work. I’ll keep working with this and post another blog when I put the monitoring portion into production.
November, 2010:
Finding SQL Servers with PowerShell Part 2
Can’t believe it’s taken me this long to get back to this series but man life has been busy! There are at least two other ways (and maybe even 3) that I want to show you for finding SQL Servers on your network. This next way is very similar the my first post but the results are a bit nicer because it actually puts the server and instance name together in a column called Name. For people just starting out with PowerShell this one difference is reason enough to use this method over the other This is [one of] the same method[s] that Mladen Prajdić ( blog | twitter ) uses in SSMS Tools Pack to find SQL Servers. Mladen was nice enough to send me the C# code that he uses and patient enough to explain to me what the heck to do with C# code! As luck would have it he was using the same method that I had found just a few days earlier. After I was finally able to translate it to PowerShell I was elated to find Mladen’s C# code and my PowerShell code returned the exact same list of instances in my environment. I changed the table around a little to accommodate the extra column from the function and also columns to track the method used and the date-time the discovery was made: CREATE TABLE FoundSQLServers ( Name VARCHAR(128), ServerName VARCHAR(128), InstanceName VARCHAR(128), IsClustered VARCHAR(5), VersionNumber VARCHAR(64), DiscoveryMethod VARCHAR(10), DiscoveryOccured datetime2 ) Beyond just the function being a little different I changed the output so that this time we’re inserting directly into a table in the database instead of diverting to CSV first. Fire up your favorite PowerShell editor and run this: $SQL = [Microsoft.SqlServer.Management.Smo.SmoApplication]::EnumAvailableSqlServers() | ` foreach { invoke-sqlcmd -query “INSERT INTO dbo.FoundSQLServers VALUES (‘$($_.Name)’, ‘$($_.Server)’ ` , ‘$($_.Instance)’, ‘$($_.IsClustered)’, ‘$($_.Version)’, ‘EnumAvail’, SYSDATETIMEOFFSET())” ` -database SandBox -serverinstance “Win7NetBook” } Now, you may or may not have received an error message with that last command and it completely depends on your setup but that’s a discussion for another post. For today we’ll just say that if you got this error message: Unable to find type [Microsoft.SqlServer.Management.Smo.SmoApplication]: make sure that the assembly containing this type is loaded.Then all that you’ll need to do is load this Assembly first (once, at the beginning of your PowerShell session) and you’ll be good to go. [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”) | out-null Back to that table… You’ll notice that the table doesn’t have a primary key and that’s because I’ve left that up to you. I use a table similar to this just to dump my results; I don’t use this as my master list or anything like that. I query this table to see what has shown up recently that I didn’t know about but I use a different table to sort instances between Prod/QA/UAT/Dev and what-not. SELECT [Name] ,[ServerName] FROM [dbo].[FoundSQLServers] GROUP BY [Name] ,[ServerName] ORDER BY [Name] There you have it, not just a different way to find SQL Servers but also a nice comparison between outputting to a CSV file and outputting straight to a table inside of SQL Server. There are some drawbacks to this method that I want to make sure to call out: 1) I’ve never seen it work over a VPN connection so make sure to RDP into another machine and run it from there. 2) This method is no good at crossing domains so you’d have to RDP into a machine on the other domain for that too. 3) Anytime the Version column isn’t populated you can expect the IsClustered column to be wrong. Hopefully the next method (or two) won’t take me as long to get posted
SQL Source Control, PowerShell, and TFS FTW!
A few weeks back I wrote a post for the Hey Scripting Guy Blog on TechNet on scripting out databases with PowerShell. The post was inspired by an issue I had using Red-Gate’s SQL Source Control tool. (If you aren’t familiar with SQL Source Control then check it out. The tool is so full of awesome that I wrote code to get around the only problem I found with it. ) While using the product I ran into a single small problem. When I check a database into Team Foundation Server via SQL Source Control it doesn’t include the drop statements. The deployment procedures at my company just go smoother with DROP statements in each of the stored procedures. Similarly, for databases that haven’t been deployed outside of the Development environment it was better for us to drop and re-create the tables from scratch. It isn’t the kind of thing you’d ever do in production, but given the length of some development projects it is very useful if you can do this when needed. The version of SQL Source Control I used doesn’t script drops or permissions by default. If there is an option to change this I can’t find it. Rather than switch tools I used this as an excuse to learn how to work with the different scripting options available inside the SMO exposed by PowerShell. Find your options this way: $dbname=”AdventureWorks” $server=”WIN7NetBook” [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | out-null $SMOserver = New-Object (’Microsoft.SqlServer.Management.Smo.Server’) -argumentlist $server $db = $SMOserver.databases[$dbname] $Scriptr = new-object (‘Microsoft.SqlServer.Management.Smo.Scripter’) ($SMOserver) $Scriptr.Options | Get-Member To work around this problem I linked my database to my TFS server (from SQL Source Control inside of SSMS). I committed all the changes to TFS so that everything was checked in. Then I opened Visual Studio, checked out out all the objects on to my local machine, generated the scripts with PowerShell just the way I wanted them, overwrote the local TFS code with the code I had just generated, and finally, I checked everything back in to TFS. Doing all this was pretty quick, worked great, and SQL Source Control didn’t care that the scripts weren’t the ones that it had generated. Be forewarned, when you include the ScriptDrops option it only generates the drop statement. To work around this see my tips here. Now all that is left is to add the Permissions option. The resulting code also does some cool things like divide objects into folders by type; put everything under a folder structure that includes the name of the database; and another folder that is simply datatime so that you can run it multiple times as you tweak the code to fit your environment, or, just keep old versions of the code laying around in case someone dropped something that hadn’t been checked into TFS yet. I’ve also included the portion of code that only adds the drop statements to objects like stored procedures but not tables. You can download the script here. Or just take a look at it here. When/if you run the script, it creates a PowerShell function. To call it all you have to do is something like this: Script-DBObjectsIntoFolders “AdventureWorks” “WIN7NetBook” And you could even call it in a loop to script out all of your user databases: foreach ($dbn in invoke-sqlcmd -query “SELECT name FROM sys.databases WHERE owner_sid !=0×01″` -database master -serverinstance WIN7NetBook ) { #Begin Loop $dbn; Script-DBObjectsIntoFolders $($dbn.name) “WIN7NetBook” } #End Loop Script-DBObjectsIntoFolders function global:Script-DBObjectsIntoFolders([string]$dbname, [string]$server){ [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | out-null $SMOserver = New-Object (’Microsoft.SqlServer.Management.Smo.Server’) -argumentlist $server $db = $SMOserver.databases[$dbname] $Objects = $db.Tables $Objects += $db.Views $Objects += $db.StoredProcedures $Objects += $db.UserDefinedFunctions #Build this portion of the directory structure out here in case scripting takes more than one minute. $SavePath = “C:\TEMP\Databases\” + $($dbname) $DateFolder = get-date -format yyyyMMddHHmm new-item -type directory -name “$DateFolder”-path “$SavePath” foreach ($ScriptThis in $Objects | where {!($_.IsSystemObject)}) { #Need to Add Some mkDirs for the different $Fldr=$ScriptThis.GetType().Name $scriptr = new-object (‘Microsoft.SqlServer.Management.Smo.Scripter’) ($SMOserver) $scriptr.Options.AppendToFile = $True $scriptr.Options.AllowSystemObjects = $False $scriptr.Options.ClusteredIndexes = $True $scriptr.Options.DriAll = $True $scriptr.Options.ScriptDrops = $False $scriptr.Options.IncludeHeaders = $True $scriptr.Options.ToFileOnly = $True $scriptr.Options.Indexes = $True $scriptr.Options.Permissions = $True $scriptr.Options.WithDependencies = $False <#Script the Drop too#> $ScriptDrop = new-object (‘Microsoft.SqlServer.Management.Smo.Scripter’) ($SMOserver) $ScriptDrop.Options.AppendToFile = $True $ScriptDrop.Options.AllowSystemObjects = $False $ScriptDrop.Options.ClusteredIndexes = $True $ScriptDrop.Options.DriAll = $True $ScriptDrop.Options.ScriptDrops = $True $ScriptDrop.Options.IncludeHeaders = $True $ScriptDrop.Options.ToFileOnly = $True $ScriptDrop.Options.Indexes = $True $ScriptDrop.Options.WithDependencies = $False <#This section builds folder structures. Remove the date folder if you want to overwrite#> $TypeFolder=$ScriptThis.GetType().Name if ((Test-Path -Path “$SavePath\$DateFolder\$TypeFolder”) -eq “true”) ` {“Scripting Out $TypeFolder $ScriptThis”} ` else {new-item -type directory -name “$TypeFolder”-path “$SavePath\$DateFolder”} $ScriptFile = $ScriptThis -replace “\[|\]” $ScriptDrop.Options.FileName = “” + $($SavePath) + “\” + $($DateFolder) + “\” + $($TypeFolder) + “\” + $($ScriptFile) + “.SQL” $scriptr.Options.FileName = “$SavePath\$DateFolder\$TypeFolder\$ScriptFile.SQL” #This is where each object actually gets scripted one at a time. IF ($ScriptThis.GetType().Name -NE “Table”) { $ScriptDrop.Script($ScriptThis) } $scriptr.Script($ScriptThis) } #This ends the loop } #This completes the function
Atlanta PowerShell User Group – November Meeting
This month is a special meeting as the Atlanta PowerShell User Group and the Virtual PowerShell User Group are joining forces to bring you Ed Wilson, the Scripting Guy. He will be joining us to give his presentation on PowerShell Best Practices. Please register so we know how much pizza to order. Orrrrr, don’t complain when we run out of pizza again. When: Tuesday, November 16, 2010 from 6:00 PM – 8:30 PM (ET) Agenda: 6:00pm: Networking, free pizza and soft drinks, and open discussions. 6:30pm: User Group News and Information – Mark Schill 6:45pm: PowerShell Best Practices – Ed Wilson Where: New Horizons Atlanta 211 Perimeter Center Parkway Suite 200 Atlanta, GA 30346 Speaker Bio: Ed Wilson is one of the Microsoft Scripting Guys and a well-known scripting expert. He is a Microsoft-certified trainer who has delivered a popular Windows PowerShell workshop to Microsoft Premier Customers worldwide. He has written 8 books including 5 on Windows scripting, including: Windows PowerShell Scripting Guide, and Microsoft Windows PowerShell Step by Step both published by Microsoft Press. He has also contributed to nearly a dozen other books, and is currently working on a PowerShell Best Practices book. Ed holds more than 20 industry certifications, including Microsoft Certified Systems Engineer (MCSE) and Certified Information Systems Security Professional (CISSP). Prior to coming to work for Microsoft he was a senior consultant for a Microsoft Gold Certified Partner where he specialized in Active Directory design and Exchange implementation. In his spare time he enjoys woodworking, underwater photography, and scuba diving. Live Meeting URL for remote participants: https://www.livemeeting.com/cc/UserGroups/join?id=8RFF4R&role=attend&pw=dDs%405B%21-X Atlanta PowerShell User Group: To receive the latest news and updates from the user group, sign up for the ATLPUG Mailing List at http://powershellgroup.org/content/atlanta-powershell-user-group-mailing-list.
Standing. Room. Only.
I wasn’t smart enough to take a picture of the over 400 people that crowded into the room I was speaking in but thankfully Scott Gleason ( blog | twitter ) managed to snag a shot of just a few of the many people that came up to ask questions after my session. I mentioned at the beginning of my session that it was my first time presenting at the PASS Summit and I was humbled by the shouts of “Congrats” and applause from crowd. I hope I did a good job for them. Resources: I mentioned during the session that my code and slides were available in yesterday’s blog post. I also pointed out the posts that Chad Miller, myself, and Sean McCown put together last week on the Hey Scripting Guy blog. Here are a list all 5 of those posts and also the link to the SQLPSX project. Oh, and please take a look at the PowerShell Virtual Chapter of PASS. Use PowerShell to Collect Server Data and Write to SQL Posted Mon, Nov 1 2010 Use PowerShell to obtain SQL Server database sizes Posted Tue, Nov 2 2010 Use PowerShell to Change SQL Server Service Accounts Posted Wed, Nov 3 2010 Use PowerShell to Script SQL Database Objects Posted Thu, Nov 4 2010 Using PowerShell to Add a Group of Users to a SQL Database Posted Fri, Nov 5 2010
Speaking at the PASS Summit Today
I’ll be speaking at the PASS Summit at 3pm today in room 2AB. Due to the size and shape of the room I’ll be using a different slide deck than I uploaded to the PASS website a few weeks ago so I wanted to make sure that was available to everyone. I also wanted to go ahead and post the scripts that I’m planning on using so that people can follow along if they want.
Get Answers for Paul Randal’s Survey FAST!
You may call it ‘lazy’ but I call it ‘efficiently gathering quality results’. Last week Paul Randal (twitter) blogged and asked people to take part in a survey asking what the top Wait Type on their systems are. Since I have 100s of SQL Servers and I wanted to give him an answer for each one. And because I’m lazy… I modified Paul’s (or Glen’s [ blog | twitter ] ) query to include a ServerName column then Select’ed the results of the query on my local machine into a table. Once I had the table set up I fired up PowerShell and used the same type of script that I showed off in today’s Hey Scripting Guy post. The only difference being that I swapped out the query and table name of course. CREATE TABLE [dbo].[TopWaitTypes]( [InstanceName] [nvarchar](128) NOT NULL, [WaitType] [nvarchar](60) NOT NULL, [Wait_S] [decimal](14, 2) NULL, [Resource_S] [decimal](14, 2) NULL, [Signal_S] [decimal](14, 2) NULL, [WaitCount] [bigint] NOT NULL, [Percentage] [decimal](4, 2) NULL ) ON [PRIMARY] Make sure to read to the bottom for the significantly less code version! foreach ($RegisteredSQLs in dir -recurse SQLSERVER:\SQLRegistration\’Database Engine Server Group’\Development | where {$_.Mode -ne “d”} ) { $dt=invoke-sqlcmd -query “WITH Waits AS (SELECT wait_type, wait_time_ms / 1000.0 AS WaitS, (wait_time_ms – signal_wait_time_ms) / 1000.0 AS ResourceS, signal_wait_time_ms / 1000.0 AS SignalS, waiting_tasks_count AS WaitCount, 100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage, ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum FROM sys.dm_os_wait_stats WHERE wait_type NOT IN ( ‘CLR_SEMAPHORE’, ‘LAZYWRITER_SLEEP’, ‘RESOURCE_QUEUE’, ‘SLEEP_TASK’, ‘SLEEP_SYSTEMTASK’, ‘SQLTRACE_BUFFER_FLUSH’, ‘WAITFOR’, ‘LOGMGR_QUEUE’, ‘CHECKPOINT_QUEUE’, ‘REQUEST_FOR_DEADLOCK_SEARCH’, ‘XE_TIMER_EVENT’, ‘BROKER_TO_FLUSH’, ‘BROKER_TASK_STOP’, ‘CLR_MANUAL_EVENT’, ‘CLR_AUTO_EVENT’, ‘DISPATCHER_QUEUE_SEMAPHORE’, ‘FT_IFTS_SCHEDULER_IDLE_WAIT’, ‘XE_DISPATCHER_WAIT’, ‘XE_DISPATCHER_JOIN’, ‘BROKER_EVENTHANDLER’, ‘TRACEWRITE’, ‘FT_IFTSHC_MUTEX’, ‘SQLTRACE_INCREMENTAL_FLUSH_SLEEP’) ) SELECT @@ServerName AS ‘ServerName’, W1.wait_type AS WaitType, CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S, CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S, CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S, W1.WaitCount AS WaitCount, CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage FROM Waits AS W1 INNER JOIN Waits AS W2 ON W2.RowNum <= W1.RowNum GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage HAVING SUM (W2.Percentage) – W1.Percentage < 95; — percentage threshold” -ServerInstance $RegisteredSQLs.ServerName -database master Write-DataTable -ServerInstance “Win7NetBook” -Database CentralInfo -TableName TopWaitTypes -Data $dt } After reading Chad Miller’s (Blog|Twitter) excellent post yesterday though, I decided to combine our approaches. If you haven’t done so already, you’ll need to download Chad’s invoke-sqlcmd2 and Write-DataTable functions and load them up in your environment. You’ll also need to store the edit the query from Paul’s blog to add a ServerName column and then store it in a .sql file in the working directory of your PowerShell session. Once that’s all done if you haven’t given up and closed your browser you can use a piece of code like this to gather up all of this information from all of the server you have in your Registered Servers list. For safety’s sake when I put out scripts like this I always make sure to dive down into the \Development branch of my Registered Servers list so that you will always be able to start somewhere ‘safe’ when you run this for the first time. Or just get a big red nasty error message if you don’t have a “Development” Registered Servers Group Here’s the code that I came up with for me: foreach ($RegisteredSQLs in dir -recurse SQLSERVER:\SQLRegistration\’Database Engine Server Group’\Development | where {$_.Mode -ne “d”} ) { $dt=invoke-sqlcmd2 -ServerInstance $RegisteredSQLs.ServerName -database master -InputFile ./PaulAndGlensWaitQuery.sql -As ‘DataTable’ Write-DataTable -ServerInstance “Win7NetBook” -Database CentralInfo -TableName TopWaitTypes -Data $dt } Paul had mentioned that “The free survey system only allows a single vote per IP address – if you have any other results, send them in email (mailto:paul@SQLskills.com?Subject=Wait stats) or attach a comment below.” so I plan to send him an email with the top wait from each of my servers, minus the server name of course. I Hope this helps start some ideas in your head about how you can leverage PowerShell in your environment! By the way… I got the idea for leveraging the Registered Servers piece from an old Buck Woody ( blog | twitter ) post so pretty much none of this is my code, I just put it all together. And THAT is why I love twitter!
Please Vote if You are Going to SQLRally
A few weeks back quite a few people asked me if I was going to be submitting a pre-con session on PowerShell for SQLRally. I hadn’t planned on submitting one but so many people asked me about it that I decided to go ahead and come up with one. My session is a half-day session and has been paired with another PowerShell session by PowerShell MVP Max Trinidad ( blog | twitter ). You can read about the options here and if you’d like to go the the sessions that Max and I are putting together please vote for them here. Please only cast your vote for which ones you’d be willing to pay up to $199 to attend. Voting is open until 8 AM PST November 2, 2011. The winning sessions will be announced at the 2010 PASS Summit in two weeks. The details about my session are in the link but in short I plan to take Data Professionals from knowing nothing about PowerShell to being able to use it to simplify their lives in just 4 hours. I will go over a few techniques that I use and then I will be showing a lot of different code examples. If you’ve never worked with PowerShell before I’m sure you’ll be blown away by how little code is needed to build most of these practical everyday examples.