Ronald Dameron - IT beats loading bombs

If you need to update several Azure App Services' Configuration to change the Platform setting from 32 bit to 64 bit under Configuration | General settings, this script will save you about six clicks per service and you won't forget to press the SAVE button. Ask me I know. 🙄 Login-AzureRmAccount Set-AzureRmContext  -SubscriptionName  "Your Subscription" $ResourceGroupName  =  'RG1' ,  'RG2', 'RG3' foreach  ( $g   in   $ResourceGroupName ) {       # Set PROD slot to use 64 bit Platform Setting      Get-AzureRmWebApp  -ResourceGroupName  $g  | Select Name |  %  {  Set-AzureRmWebApp  -ResourceGroupName  $g  -Name  $_ .Name  -Use32BitWorkerProcess  $false  }       # Set staging slot to use 64 bit Platform setting ...

I'm wrapping up the deployment of a cross-platform automation tool for our distributed databases(DB2 LUW, SQL Server, Oracle).  One of our major items to convert is a VBScript that does several post-installation tasks for SQL Server.  I've decided to take this opportunity to retire as much VBScript as possible.  Why might you ask?  Here's my first example... To check the version of Windows in our VBScript requires twelve lines of code PowerShell only needs one.  Well, we'll need two lines to store the Caption and CSDVersion in variables for later use. I expect I'll have several other great examples to show from this effort. Get stuff done faster with less code. Use PowerShell.

A few years ago, I wrote PowerShell scripts to assist our IT Risk department with auditing and ID management. With the deployment of SQL Server 2012 in our environment those scripts are starting to show their age.  The scripts were designed to run from a central server and they use SQL Server PowerShell Extensions version 1.6.1.  The scripts are throwing errors when trying to connect to SQL Server 2012 instances. The SMO assemblies are loaded with the following code by LibrarySmo.ps1: If I run this bit of code on our central server it returns the Version 10 SMO assemblies that allow it to connect to SQL Server 2008 R2 and below. Running this code on my laptop or server that has the SQL Server 2012 tools installed returns the Version 11 SMO assemblies which allow the scripts to run on SQL Server 2012. In addition, if the scripts are copied to a server with SQL Server 2012 installed, they run successfully because the code above is loading the later version o...

Time moves slower in a big company's data centers. Meaning, you might not get to run SQL Server AlwaysOn Availability groups on Windows Server 2012. If not,  you'll have to check several patches are applied for it to work correctly on Windows Server 2008 R2. This excellent resource  provides a list of the needed patches in the FREE AlwaysOn Setup Checklist PDF. I think the easiest way to determine if all the required patches are installed is by using the PowerShell cmdlet get-hotfix with the -computername and -id parameters. Beware, the example below is a single line. get-hotfix -computername YourServerNameHere -id KB2494036, KB2494162, KB2520235, KB2524478, KB2531907, KB2550886, KB2552040, KB2578103, KB2578113, KB2582281, KB2616514, KB2654347, KB2687741, KB976097, KB980915 If any of the patches are found, a five column result set is returned.  If all the patches are found, you'll see fifteen rows.  If none of the patches are found, the command throws ...

I was told d uring the initial implementation of Ola Hallengren's Maintenance Solution at my workplace that it was too hard to find database backup files in the default folder structure his solution creates.    The concern was that it took too long to find the oldest file or the biggest file when trying to resolve a space issue on a database server. Ola's solution creates a folder tree with these elements. Drive:\root\ServerName\DatabaseName\TypeOfBackup You specify the backup drive and root folder using the Directory parameter of the DatabaseBackup stored procedure. When I originally solved this problem, Windows XP was in use.  I wish I had spent more time figuring this out because I had to modify Ola's database backup procedure to dump all the backups into one folder in order for the DBA teams to sign off on the initial deployment. It only takes a couple of minutes to search using Windows Explorer in Windows XP but finding files in a folder tree has gotten mu...

I'll be speaking at SQL Saturday #85 this weekend in Orlando. Here's another example we'll review during my session "PowerShell by Example". This example uses the SQLPSX 1.6 function get-sqlserver to return server properties. This example was inspired by @PaulRandal's Ponderings on the instance-wide fillfactor setting in his SQLskills Insider Tips newsletter of 9/3/2011.   To become a SQLskills insider, go here . Looking forward to seeing everyone at SQL Saturday #85! 1: # In response to Paul Randal's Ponderings of Sep 3, 2011 regarding fill factor set at the instance level... 2: # His Call To Action is to verify it is set to 100. This script provides the basic means to check it on multiple instances. 3: # The results are sent to a csv file 4:   5: $FilePath = "C:\Output" 6: $OutFile = Join-Path -path $FilePath -childPath ( "ServerWide_FillFactor" + (get-date).toString( 'yyyyMMdd_hhmmt...

# SQLSaturday#85 in Orlando is September 24, 2011 # I'll be presenting "PowerShell by Example" # Here's a typical example we will review. # # Want more? Come to my session at 11 AM. # # Need more info on SQL Saturday Orlando, go here . # # Set permissions on multiple servers for GUARDIUM Vulnerability Assessment # Pre-requisites: # PowerShell V1 # SQLPSX 1.6.1 # add-pssnapin sqlserverprovidersnapin100 # add-pssnapin sqlservercmdletsnapin100 1: $serverName = "SQL999" 2: $databaseName = "InventoryDB" 3:   4: $FilePath = "C:\GUARDIUM\DataSources\Output" 5: $OutFile = Join-Path -path $FilePath -childPath ( "MSSQL_GDM_Output" + (get-date).toString( 'yyyyMMdd_hhmmtt' ) + ".txt" ) 6:   7: # here string contains SQL query 8: $qry = @" 9: SELECT DISTINCT 10: ISNULL(A.MACH_LPAR_NM, '') + CASE a.INST_NM 11: ...

# My quick and dirty PowerShell script to check for the oldest entry in MSDB. # I'm using a SQLPSX 1.6 function Get-SqlData to execute queries. # Runs against multiple servers 1: $start = get-date 2: write-host "Start: " $start 3:   4: $serverName = "SERVER\INSTANCE" 5: $databaseName = "dbINVENTORY" 6:   7: $FilePath = "C:\Output" 8: $OutFile = Join-Path -path $FilePath -childPath ( "MSDB_MIN_Date_" + (get-date).toString( 'yyyyMMdd_hhmmtt' ) + ".csv" ) 9:   10: # here string contains SQL query 11: $qry = @" 12: SELECT DISTINCT 13: ISNULL(A.MACH_LPAR_NM, '') + CASE INST_NM 14: WHEN 'DEFAULT' THEN '' 15: ELSE '\' 16: END + CASE INST_NM 17: WHEN 'DE...

I've submitted two abstracts for the SQLRally . If you're interested in learning PowerShell by example or want to hear how I automated our login provisioning and compliance reporting. Vote for my sessions. It's a win-win situation in the PowerShell track for attendees no matter who you vote for. Another great bunch of abstracts in the DBA track. Get out and vote.

I'll be speaking at SQL Saturday #62 in Tampa , FL on January 15, 2011. I'm presenting a session called "Why Learn PowerShell" because there was a request for a beginning PowerShell session.  Tons of speaker submissions for this SQL Saturday so the organizers gave most speakers one slot to give the maximum amount of people the chance to speak.  They are still trying to secure additional space to expand the schedule.     If you have any interest in learning PowerShell, I encourage you attend this session.   I'll explain why it's worth learning and provide REAL WORLD examples that I use regularly. To further convince you that SQL Saturday is worth your time consider the following: The variety of topics is pretty amazing for a FREE event. Local, regional, and national speakers will present. Several 2010 PASS Summit presenters will speak. The Microsoft Scripting Guy and The Scripting Wife will be attending A local Microsoft Certified Master will be...

Started the day off great with breakfast in the Daily Grill with @BrentO after he tweeted that he had three open spots at his table.  Can't let the dude eat breakfast alone. I attended these sessions on Day 1 of SQL PASS.  Made it through the day with minimal impact from jet lag. DBA283S " Virtualization and SAN Basics for DBAs " with Brent Ozar. This man has a gift for imparting information in a funny and entertaining way. Great stuff I can use to better understand what is happening at my workplace with links to even more information at his blog under his SAN and Virtual tags. This one was voted most popular yesterday so it's today's Second Chance session.  AD314 " Database Testing Overview " with Buck Woody and Alex Kuznetsov. Buck and Alex gave an overview for properly testing a database before deploying new features.  Buck said Microsoft got one of its biggest black eyes from a one line change that had only one word changed.  Wow! You ...

I'll be speaking at my THIRD SQLSaturday of the year in Orlando. I'm doing a mini session and a regular session. The mini session is a condensed version of my Why a DBA Should Learn PowerShell talk. In 15 minutes, I hope to convince you that learning PowerShell is worth your time. Powershell is the newest scripting language from Microsoft and it goes across the Windows platform, including SQL Server. So it's new and exciting, but is it ready for prime time? Is it worth the effort to learn? How will it help you? Join PowerShell advocate and user Ron Dameron for a quick discussion of why he thinks knowing Powershell is a key skill for a SQL Server DBA. My full session for the day is Automate Login Administration & Compliance Reports A re your internal auditors asking that the administration of SQL Server logins be handled by a third party other than the DBAs? Is your Compliance and Audit departments asking you to determine if pa...

SQL Saturday #40 in South Florida recap. Speaker dinner was great at Longhorn Steakhouse.  Thanks to Confio Software and SQLSkills.com for picking up the dinner and bar tab. Great to meet more SQL Tweeps in person and catch up with previous acquaintances. Evidently, the organizers did a fantastic job marketing this event because there were a large number of attendees.  The numbers I heard were in the 400 - 500 range. Rooms were full for the talks I saw and the Commons was busy. Check-in was very smooth.  Speaker room was great.  Plenty of water, refreshments, and food for the attendees.  Technical support from DeVry University staff was very helpful. The PowerShell room was packed for my first session at 8:30 am on "Why DBAs Should Learn PowerShell".  I think I may retitle this "Why ANYONE Can Learn PowerShell".  My second session on how to automate database login administration and compliance reporting was also well attended. Had a great t...

Need a few reasons to come to this event ? It’s FREE .  You’ll learn from a master. A Microsoft Certified Master.  Brent Ozar will do two sessions . His first at 0945 conflicts with my first session.  I hope they gave me a small room for my session.  I predict most attendees will be in his session, not mine. He’s guaranteed to keep you awake. No cover charge at the door. Costumes optional. I’ll get to see Brent’s second session on Disaster Recovery in Hurricane Alley.                     College football hasn’t started.  So, you won’t miss UF, UCF, USF, FSU or The U. It’s FREE . Another Microsoft MVP, Tim Ford, will be there to talk about indexes, DMVs, and mistakes to avoid. BTW, great post from him recently on how being involved in the SQL community allowed him to land softly at a new job when he wasn’t looking. More MVPs, Twitterati and local e...

Jack Corbett ( @unclebiguns ) and Andy Warren( @sqlAndy ) invited me to speak at this month’s Orlando PASS meeting. First, I want to thank them for the opportunity. I enjoyed it. For the benefit of those who couldn’t make the trip to Tampa last month for SQL Saturday #32, I did my talk on “Database Hardening via PowerShell”. I worked a half day and then headed to Orlando around 12:30 PM.  The plan was to speak and to visit my daughters at the University of Central Florida. I visited with the youngest daughter prior to the meeting.  When I asked if she was available, she asked “Does this mean I get a free lunch?”.  Yes, we had a late lunch . After lunch, I headed over to  the meeting location around 4 PM hoping to beat the rush hour traffic. Boy, was I wrong.  The constant rain that day slowed I-4 Eastbound traffic to a crawl.  Plus, I was wondering who let all these people off work early??? I arrived at the meeting location at 5 PM, a full hour bef...

SQL Saturday #32 was a great success this past weekend! Pam Shaw ( @pamshaw ) and Jorge Segarra( @SQLChicken ) did a great job organizing this event! Thanks to KForce for hosting the event and to all the sponsors for picking up the costs. If you missed this event, you also missed out on some of the best food ever at a SQL Saturday!  Spaghetti Warehouse in Ybor City did a great job with the speaker/volunteer dinner Friday night.  The day of the event all attendees got a taste of Ybor from “Latam at the Centro” for lunch.  BEST LUNCH I HAVE EVER HAD AT A SQL SATURDAY!  Hmmm. Best lunch I’ve had in a while. It was great to meet the following SQL Tweeps in person: @adam_jorgensen , Argenis Fernandez( @afernandez ), David Taylor(@ dyfhid ), @GarethSwan , Jason Strate( @StrateSQL ), Aaron Nelson( @SQLvariant ),and Jeff Truman( @jtruman0917 ). I hope I didn’t miss anybody. Great to see these tweeps again: @brianknight , @cmille19 , @GratefulDBA , @patrickdba , ...

A manager posed the question “Can we list all the Windows groups that have access to all of our database servers?”  The answer is “Yes.”  It is very easy to do with a short PowerShell script via SMO. The results are written to a CSV file. 1: ## List Windows Groups on a server ## 2: ## ./get-WinGrps.ps1 3: $start = get-date 4: write-host "Start: " $start 5:   6: [reflection.assembly]::LoadWithPartialName( "Microsoft.SqlServer.Smo" ) | out-null 7:   8: $FilePath = "C:\Output" 9: $OutFile = Join-Path -path $FilePath -childPath ( "WindowsGroupsOnServers_" + (get-date).toString( 'yyyyMMdd_hhmmtt' ) + ".csv" ) 10:   11: # Version inventory 12: @( foreach ($svr in get-content "C:\Input\TestServers.txt" ) 13: { 14: 15: $s = New-Object "Microsoft.SqlServer.Management.Smo.Serve...

I have to keep track of our SQL Server version inventory.  The goal is to reduce the SQL Server 2000 population as fast as possible. The following PowerShell script will produce a csv file containing the database server name and the version of SQL Server it's running. 1: ## Get SQL Version installed on multiple servers ## 2: ## ./sqlver.ps1 3: $start = get-date 4: write-host "Start: " $start 5:   6: [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null 7:   8: $FilePath = "C:\Output" 9: $OutFile = Join-Path -path $FilePath -childPath ("SQLVersions_" + (get-date).toString('yyyyMMdd_hhmmtt') + ".log") 10:   11: # Version inventory 12: @(foreach ($svr in get-content "C:\Input\AllLOBServers.txt") 13: { 14: $s = New-Object "Microsoft.SqlServer.Management.Smo.Server" $svr 15: $s | select Name, Version 16:   17: }) | export-csv -noType $OutFile 18:   1...

One of the first things I translated from a previous script language to PowerShell was a random password generator.  The previous script was 57 lines long. So, when I HAD to create a SQL Authenticated login I could provide a strong password. I googled (Bing wasn't out yet.) on random password PowerShell and found the TWO lines below in the comments to a post on Dimitry Sotnikov's blog dated July 2007.  The first line loads the assembly you need to obtain the GeneratePassword method. The second line actually produces the password.  You can change the length of  the password by modifying the first number inside the parentheses.  Examples below. 1: [Reflection.Assembly]::LoadWithPartialName(”System.Web” ;) 2: [System.Web.Security.Membership]::GeneratePassword(8,2) # 8 bytes long 3:     4: [System.Web.Security.Membership]::GeneratePassword(10,2) # 10 bytes long For a password, that would make Commander Data proud... 1: [System.Web.Se...

DBAs: Here's a small script that will find SQL Logins with NULL passwords on multiple servers.   1: foreach ($svr in get-content "C:\Input\ProdInstances.txt" | where {$_ -notmatch "^#"}) 2: { 3: $svr 4: $ExFile = 'C:\Audit\NULL_SQL_Passwords_' + $svr.Replace('\','_') + '.csv' 5: $con = "server=$svr;database=master;Integrated Security=sspi" 6: $cmd = "SELECT @@SERVERNAME AS Server, name, loginname, dbname, password, accdate FROM master..syslogins WHERE password IS NULL AND isntgroup = 0 AND isntname = 0 AND loginname NOT LIKE '##%'" 7: $da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con) 8: $dt = new-object System.Data.DataTable 9: trap {"Oops! $_"; continue } $da.fill($dt) | out-null 10: if ($dt.Rows.Count -gt 0) { $dt | SELECT Server, Name, Loginname, DBname, Password, Accdate | export-csv -noTypeInformation $ExFile } 1...