PowerShell, SQL

Creating A Clustered SQL Install ini File Using PowerShell–Part 1

I’ve written a few posts recently around Clustering. Most of thse have been inspired by working on building out 4 new clusters consisting of 16 nodes and 14 new SQL instances. Each of the servers in the clusters are identical and each one of the SQL installs needed to be exactly the same.

Ensuring that all the installs are the same can be challenging when using the GUI, especially when you have you are installing a lot of instances. In this case:

  • Cluster A – 5 nodes x 4 SQL instances = 20 installs
  • Cluster B  – 4 nodes x 3 SQL instances = 12 installs
  • Cluster C – 4 nodes x 4 SQL instances = 16 installs
  • Cluster D – 3 nodes x 3 SQL instances = 9 installs
  • Total installs: 57

Given how slow it is to use the SQL installer GUI I figured it would take me about 2 1/2 lifetimes to perform the installs I needed.

 

A better and more consistent way to install SQL

So what would be a better way to perform these installs? It makes sense to perform automated installs using ini files that hold all the configuration information for the instance.

There’s a good MSDN article on “How to: Install SQL Server 2008 R2 Using a Configuration File”.

Using the SQL Server instance prep tool is again a major undertaking using the GUI. I figured that there had to be a better and faster way to build an ini file to perform the installs I needed. From previous cluster installs I had a couple of configuration.ini files laying around, it’s a pretty easy task to manipulate those to reference new clusters/disks and IP addresses. Thing is that it’s not particularly quick and also very prone to error.

So what would be a better way?

 

PowerShell to the rescue

Yet again, PowerShell is the savior in this situation.

I put together a script that is quick and lightweight, asks the relevant questions and provides you with the ini files that you need to install a new clustered SQL Server instance and the ini files that you need to perform the installs on the additional cluster nodes.

 

First things first

Before using the script we need to ensure the cluster is setup and ready to go. Here I’ll be using my home machine to install a new clustered SQL Server instance.

I’ve created a new cluster called CLUSTER1 consisting of two nodes CLUSTERNODEA and CLUSTERNODEB which consists of a private and public network (named as such).

 

The cluster had node and disk majority. Storage wise (as this is a demo) I’ve only got a couple of disks. SQL Data Disk to hold my SQL system, data and log files plus SQL Backup Disk to hold the backups. This is obviously not how I would setup a normal system, this is just iSCSI disk running on my laptop, don’t judge me too harshly.

 

 

In part 2 I’ll go over the configured Active Directory components so that everything is in place ready for the install to happen.

PowerShell, Windows

Grabbing The Newest File From Subdirectories Using PowerShell

Every once in a while I come up with a need for something a little out of the ordinary, in this instance I was moving backups from one machine to another. Robocopy is the obvious tool of choice to do this. Trouble was that the backups were not all within a single directory, rather they were in multiple subdirectories. Robocopy will of course handle this with the /S switch. What Robocopy can’t handle is the fact that I want only the most recent file from each one of those subdirectories, not all of them (in this case I just wanted to move the most recent differential backup from one location to another).

I figured I could sit down and query msdb for this information and dump that out. I mean it works, it’s functional and does exactly what I would need it to do. Where’s the fun in that though, really? Add to that it would only handle SQL backups, what if I had a need to do the same thing later on  for other types of files? The TSQL query wouldn’t work for me then.

 

PowerShell to the rescue

Seriously, I think that I’m going to get a cape for PowerShell that it can wear around the place as it’s that damn super (although I am not sure that I want to see it’s underwear outside its pants).

In this example I’m going to be working from C:Temp on my local machine.

Within C:Temp there are two folders and a subfolder:

Each of these folders contains a couple of files:

 

To grab the most recent file from a particular folder is a quick one liner:

dir c:tempsubfolder2 | sort-object {$_.LastWriteTime} -Descending | select-object -First 1

 

That is the basis for the rest of the script. Essentially everything else just handles recursing through the subdirectories to grab this information:

cls

$Path = 'C:Temp' #Root path to look for files

$DestinationPath = 'RemoteD$' #Remote destination for file copy

 

#Grab a recursive list of all subfolders

$SubFolders = dir $Path -Recurse | Where-Object {$_.PSIsContainer} | ForEach-Object -Process {$_.FullName}

 

#Iterate through the list of subfolders and grab the first file in each

ForEach ($Folder in $SubFolders)

    {

    $FullFileName = dir $Folder | Where-Object {!$_.PSIsContainer} | Sort-Object {$_.LastWriteTime} -Descending | Select-Object -First 1 

    

    #For every file grab it's location and output the robocopy command ready for use

    ForEach ($File in $FullFileName)

        {

        $FilePath = $File.DirectoryName

        $FileName = $File.Name

        Write-Output "robocopy $FilePath $DestinationPath $FileName /R:6 /W:30 /Z"

        }

    }

Running this gives the results:

 

Quick and easy, does just what it says on the box. Feel free to download CopyNewestFileFromSubDir.ps1 and give it a try. Let me know what enhancements you put around it (here’s a quick one, to limit the type of files evaluated change !$_.PSIsContainer to $_.Extension –eq “.bak” )

Uncategorized

Goals Update–July 2011

At the start of the year I posted a few goals that I was looking to achieve. April brought around a goals update post. Now that July is here it’s time for another update.

 

Certification

After completing both pre-requisite MCITP exams I set an additional goal of taking the MCM written test prior to the end of Q3. This is still on track although the amount of knowledge required (just based upon the reading list) is more than a little scary.

Goal Status: Complete & bonus is on track

 

Presenting

The goal was to present at four events this year and submit a session to the PASS Summit.

As for the PASS Summit, as I’ve previously posted I submitted two abstracts but neither were accepted so I won’t be speaking there. The goal was to submit so I have to mark that as complete as well.

In April I was at 50% on the presentations having spoken in Chicago and Olympia. In May I gave a presentation on Central Auditing of SQL Permissions with PowerShell & TSQL for the PASS PowerShell Virtual Chapter. I’m hoping to speak at SQL Saturday #89 in Atlanta. Provided that I get accepted for Atlanta my presentation goal for the year will be complete.

Goals Status: 80% complete

 

Blogging

I’ve been writing more and not writing long yawn inducing posts, so going good.

Goal Status: going well

 

Personal

Submit citizenship paperwork.

Waiting on a single piece of paperwork to come through. Out of my control at this point.

Goal Status: Still waiting

 

Bonus goal: Build a decent home lab

I’m acquiring pieces bit by bit for this. Case, motherboard and power supply are here, CPU and memory to come next month and disk in September will complete this.

Goal Status: Well on the way

 

Get motorbike endorsement

No movement on this one yet.

Goal Status: Slacking

 

Overall most of the goals are going really well, there are a couple of places where I need to improve things but for only half way through the year I’m quite pleased with my progress.

Next update right before the PASS Summit.

PowerShell, Windows

Using PowerShell To Set Resource Owners In A Cluster

Following up on my post about Using PowerShell To Add Owners For Cluster Disks here’s another quick post on how PowerShell can help you with your clusters.

In my new cluster build out I needed to quickly set it so that one of the SQL instances could only potentially live on two of the 5 nodes. This could have been quickly done using the GUI however it’s just as fast to do so using PowerShell.

Load up the Windows PowerShell Modules and you’re ready to go.

 

In this example we have a two node cluster made up of SERVER1 and SERVER2 and a single installed SQL instance called INST1.

In PowerShell the following would provide us a list of possible owners of INST1:

Get-ClusterOwnerNode -Group "SQL Server (INST1)"

ClusterObject                          OwnerNodes                                 
————-                                 ———-                                 
SQL SERVER (INST1)           {server1, server2}         

 

Now to change this so that only SERVER1 can potentially own the SQL instance is a very quick task:

Set-ClusterOwnerNode -Group "SQL Server (INST1)" -Owners SERVER1

 

Sadly you don’t get any feedback that the change has been made, but if you run Get-ClusterOwnerNode again:

Get-ClusterOwnerNode -Group "SQL Server (INST1)"

ClusterObject                          OwnerNodes                                 
————-                                 ———-                                 
SQL SERVER (INST1)           {server1}       

 

Adding SERVER2 back is as quick as running Set-ClusterOwnerNode again and providing a comma delimited list of servers:

Set-ClusterOwnerNode -Group "SQL Server (INST1)" -Owners SERVER1,SERVER2

ClusterObject                          OwnerNodes                                 
————-                                 ———-                                 
SQL SERVER (INST1)           {server1, server2}     

 

You have to love the simplicity of working with clusters in PowerShell.

Rant

Be A Courteous Blogger

Today I received an email, nothing unusual about that. This email happened to be from a SQL blogger. It’s one of those newletter type things, it even said at the bottom that I had opted into receiving this email. The only thing is that I, at no point, signed up to receive a newsletter. In fact this blog is not one I have visited other than on a couple of ocassions. I believe I may have commented on one of the posts I saw and that is how the blog owner got my email address.

My guess is that the blogger in question went through the WordPress database (yes WordPress site) and pulled emails for everyone that had posted comments and sent out this unsolicited email.

That is absolutely the wrong way to do something like this. A strongly worded email is on its way to the blog owner. Small mercies…they included an unsubscribe link on the email, so hopefully I won’t get another.

 

The right way to have done this was to do the exact same thing that Paul Randal (blog|twitter), Robert Davis (blog|twitter) and Brent Ozar PLF (blog|twitter) have done. That is let people know you have a newsletter and give folks the opportunity to opt in and receive it.

All you bloggers out there, please do the right thing.

PowerShell, Windows

Using PowerShell To Add Owners For Cluster Disks

Here’s a quick post about how great PowerShell can be for your clusters.

I’m in the middle of configuring a few machines in clusters and recently added a new node to a cluster that was already built (but not configured). I needed to have that final node be a possible owner for all 33 presented disks on that cluster.  To do that through the cluster GUI would require a heck of a lot of mouse clicks….PowerShell to the rescue!

The Windows PowerShell Modules (under Administrative Tools) allow you to do all kinds of wonderful things with clusters.

Allowing a single disk to be owned by the extra server is as simple as:

CLUSTER RESOURCE "Cluster Disk 1" /addowner:DBSERVER1

 

 

If I want to do that for 33 disks a quick PowerShell one-liner takes care of it:

$i=1;do {cluster resource `"Cluster Disk $i`" /addowner:DBSERVER1;$i = $i +1} while ($i -lt 33)

 

 

Here’s a version that’s a little more readable:

$DiskNo = 1

DO

    { CLUSTER RESOURCE `"Cluster Disk $DiskNo`" /addowner:DBSERVER1

        $DiskNo = $DiskNo+1

    }

    

WHILE ($DiskNo -lt 33)

 

Quick and easy. Saved my clicky finger lots of work.

 

More PowerShell and cluster fun coming up in the next few weeks.

PASS

Working on a PASS abstract review/selection team

A couple of days ago I posted how I was disappointed not to have either of my sessions accepted for presenting at the PASS Summit 2011. While I was saddened, I understood how not every session could be picked and understood completely how difficult it is in choosing what would be the winning abstracts. A large part of this understanding was because I worked on one of the PASS Abstract Review/Selection committees this year.

I was one of three committee members who had it in our hands to help choose 29 Business Intelligence sessions. These were broken down to just a single half day session, 7 spotlight sessions and 21 regular sessions.

That’s just 29 sessions to be chosen (plus 6 regular and one ½ day alternate) out of over 180 submitted. Only 16% of the BI sessions would be accepted. Wow! That made things tough. Tacked on top of that there’s no speaker who could have more than 2 sessions, oh and anyone that got a pre-con is required to present regular or spotlight session. That’s a lot to tally and manage.

Tim Ford (blog|twitter) already talked about how this year the speakers were ranked by a team of volunteers. With these numbers in a nice new tool our committee went about scoring the abstracts (and abstracts alone).

 

Abstract scoring

Here are a few of the key factors that I looked at when scoring:

  • A title and abstract that matched
  • Well outlined pre-requisites and takeaways from the session
  • Appeal to a good sized audience
  • Well written and clear abstract
  • Not a sales pitch
  • Scores from the speaker preference tool

 

Each committee member individually scored a 0-5 for every session on three criteria and those scores were averaged and saved in the tool. As an example:

  • Topic – 4
  • Abstract – 5
  • Subjective – 4
  • Abstract Score: 13/3 = 4.333

 

The abstract score was added to the speaker score to give a final score:

  • Speaker score: 4.600
  • Abstract score: 4.333
  • Total Score: 8.933

 

Session choosing

Once all of the committee had entered scores, we got together on the phone and via email to try and figure out which sessions would get picked. Even with the scoring this was not an easy task. Ensuring there was a broad range of topics as well as the other requirements on speaker limits made for some good discussion around things that should or should not be considered. In the end we completed our deliberations and finalized our decisions. All the sessions were chosen and everything returned to PASS for final approval. To their credit we only had a single change to make and that was because a speaker already had a couple of sessions in another track. That forced us to move up an alternate into a slot and promote another contender into the alternate position.

Overall it was a time consuming process and the general quality of the submitted abstracts was very high. The difference between a session being chosen and one not was very small. Be assured though every single session was given due consideration and thought. I made comments around each and every abstract I reviewed so that when it came to decision time I could quickly see why I gave the scores that I did. Believe me when I say that it was not an easy task and I know that there were sessions left out that I would have loved to have seen included but there were just not enough available slots.

 

Why I volunteered

This process was difficult and time consuming. So why did I do it? Last year I worked on the PowerPoint review committee and wanted to volunteer in some fashion again this time around. If you’ve not volunteered at something like a SQLSaturday event, PASS Summit or helped someone on Twitter or a forum then you’re missing out. The SQL community is one of the largest connected vibrant tech communities around. There are people from all walks of life from all over the world who work on different aspects of SQL and who have different skills and weaknesses.

When you first started out working with SQL Server was there someone that helped you? Guided you? Gave you advice, taught you the basics? Was patient with you while you worked your way through things? Have you gotten anything from reading blogs, books or attending events? Why do you think folks write those books? Trust me, it’s not for the money. Why would someone blog? I’m not getting a dime for this and rarely get feedback comments, yet I keep doing it. Why would someone speak at a SQLSaturday? Why would they travel to do so given that they don’t get paid? I’ve already travelled to Chicago to present this year and plan on hitting Atlanta in September. Why do these things? Because it is all about community.

Community: it’s why you read blogs (and why you are reading this post). It’s the reason you attend a SQLSaturday event or try to go to the PASS Summit. It’s the reason that local User Groups exist and that there’s a #SQLHelp tag on Twitter. It’s a large, passionate group of people that come together so that they can improve the collective knowledge. There are some great people with great ideas who’ve become great friends through the power of SQL.

Given that there’s this community rather than ask why I would volunteer to help out I’d like you to ask yourself why you wouldn’t volunteer.

Think about it and the next time you get a chance to help out at a local event put yourself out there. Trust me, it’s worth it.

PowerShell

Set The SQL TCP/IP Port With PowerShell

I’ve previously mentioned how I’m building out a whole bunch of clustered servers. In an effort to standardize all the installation and configuration components I’ve used multiple PowerShell scripts. One of the most challenging to get working was one that enabled the TCP/IP for SQL and set it to listen on Port 1433.

This script is based on the great post by Allen White (blog|twitter) on finding your enabled SQL Server protocols using PowerShell.

 

Is TCP/IP enabled?

For the script to work we have to load the SMO objects with the context of the SQL Server we want to change (if a clustered instance we would use the virtual name)

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null

$m = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') $SQLName

 

Then we build the path to the TCP/IP protocol in the instance and query the IsEnabled property.

$urn = "ManagedComputer[@Name='$SQLName']/ServerInstance[@Name='$Instance']/ServerProtocol[@Name='Tcp']"

$Tcp = $m.GetSmoObject($urn)

$Enabled = $Tcp.IsEnabled

 

Now to enable TCP/IP if it’s disabled.

IF (!$Enabled)

    {$Tcp.IsEnabled = $true }

 

 

Set the listening port

Now we drill down a little further and set the listening port.

$m.GetSmoObject($urn + "/IPAddress[@Name='IPAll']").IPAddressProperties[1].Value = "1433"

$TCP.alter()

 

 

Now for the fun stuff

What we have there is strictly functional. There’s no error checking or funky components.

First off, we need to run this as admin, so we can check to see if this is an administrator running this in an elevated shell:

$RunningAsAdmin = ([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole] "Administrator")

 

We can then check the value of $RunningAsAdmin. If true then continue on our way, otherwise error out and warn the user.

Next we’ll prompt the user for the SQL instance that we’ll be configuring, after all that’s pretty important. The entered value will be placed in the $SQLInstance variable and used in the script.

$SQLInstance = read-host "Enter the SQL instance to configure"

 

We’ll need to split out the server or virtual name from the instance name (if there is one) in order to use the values within the SMO portion (we’ll also make everything upper case).

$SQLInstance = $SQLInstance.ToUpper()

 

if ($SQLInstance -ilike "**") 

    {

    $string = $SQLInstance.Split("")

    $SQLName = $string[0]

    $Instance = $string[1]

    }

else

    {

    $SQLName = $SQLInstance

    $Instance = "MSSQLSERVER"

    }

 

Given that we are making a significant change we should provide the option to cancel the action. We do this with a quick confirmation.

$yes = New-Object System.Management.Automation.Host.ChoiceDescription "&Yes",""

$no = New-Object System.Management.Automation.Host.ChoiceDescription "&No",""

$choices = [System.Management.Automation.Host.ChoiceDescription[]]($yes,$no)

 

$captionTCP = "Question!"

$messageTCP = "Enable TCP/IP and set SQL to listen on port 1433 on SQL instance: $SQLInstance ?"

$resultTCP = $Host.UI.PromptForChoice($captionTCP,$messageTCP,$choices,0)

 

We can then check whether yes or no button was pressed and handle the action.

if($resultTCP -eq 0) { #SET TCP STUFF HERE }

    else { Write-Warning "TCP/IP changes cancelled" }

 

Quick and easy

The script only takes a few seconds to run and does exactly what is says on the box. You can copy the script below or download here. Happy playing.

 

#Check the executing user is running in an elevated shell & and an admin

$RunningAsAdmin = ([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole] "Administrator")

if ($RunningAsAdmin)

    {

 

 

<######################################

## WORK HERE                         ##

######################################>

 

#Get the SQL instance we'll be working with

$SQLInstance = read-host "Enter the SQL instance to configure"

 

$yes = New-Object System.Management.Automation.Host.ChoiceDescription "&Yes",""

$no = New-Object System.Management.Automation.Host.ChoiceDescription "&No",""

$choices = [System.Management.Automation.Host.ChoiceDescription[]]($yes,$no)

 

 

 

#########################################

### ENABLE TCP/IP & LISTENING ON 1433 ###

#########################################

$captionTCP = "Question!"

$messageTCP = "Enable TCP/IP and set SQL to listen on port 1433 on SQL instance: $SQLInstance ?"

$resultTCP = $Host.UI.PromptForChoice($captionTCP,$messageTCP,$choices,0)

#If yes prompt for confirmation then make the changes

if($resultTCP -eq 0)

    {

    Try

        {

 

$SQLInstance = $SQLInstance.ToUpper()

 

if ($SQLInstance -ilike "**") 

    {

    $string = $SQLInstance.Split("")

    $SQLName = $string[0]

    $Instance = $string[1]

    }

else

    {

    $SQLName = $SQLInstance

    $Instance = "MSSQLSERVER"

    }

 

 

$SQLName

$Instance

 

# Load SMO Wmi.ManagedComputer assembly

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null

 

Trap {

  $err = $_.Exception

  while ( $err.InnerException )

    {

    $err = $err.InnerException

    write-output $err.Message

    };

    continue

  }

  

# Connect to the instance using SMO

$m = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') $SQLName

 

 

$urn = "ManagedComputer[@Name='$SQLName']/ServerInstance[@Name='$Instance']/ServerProtocol[@Name='Tcp']"

$Tcp = $m.GetSmoObject($urn)

$Enabled = $Tcp.IsEnabled

 

#Enable TCP/IP if not enabled

IF (!$Enabled)

    {$Tcp.IsEnabled = $true }

 

#Set to listen on 1433

$m.GetSmoObject($urn + "/IPAddress[@Name='IPAll']").IPAddressProperties[1].Value = "1433"

$TCP.alter()

 

        "Success: SQL set to listen on TCP/IP port 1433. Please restart the SQL service for changes to take effect."

        }

    Catch { Write-Warning "Unable to enable TCP/IP & set SQL to listen on port 1433" }

     } 

else { Write-Warning "TCP/IP changes cancelled" }

    

    

    }

    

    else { Write-Warning "This script must be executed by an administrator in an elevated shell" }

 

 

 

 

PASS, Presenting

Not Presenting At PASS Summit 2011

Sadly neither of the two sessions that I submitted for the PASS Summit 2011 were chosen and so I won’t be presenting. I’m not going to claim that I am not disappointed because I am. I felt that I had a really good chance of being chosen, but when there are 649 abstract submitted and only 111 slots available someone is going to miss out. This year I was one of the majority that didn’t make it.

I’m not disheartened, I’ll continue speaking at other events this year and next and already plan on submitting sessions for next years Summit.

I will, however, be at the Summit this year. I paid for my submission early in the year and got a significant discount on the price…which reminds me, go register now and save $600 on the full admission price.

There are going to be some fantastic speakers and sessions, including half-day sessions for that deeper dive (don’t take my seat in Bob Wards session if you know what’s good for you). Don’t forget there are 2-days of precons this year. I know my buddy Aaron Nelson (blog|twitter) would love to spend a day talking your ear off about PowerShell.

Hope to see you there, and to enjoy watching you entertain everyone at SQL Karaoke!

SQL, Windows

MSDTC Failing In A Cluster

I’m merrily working away on installing SQL 2008R2 on a bunch of new clusters. The SQL installs have gone fine and I’m getting set to install MSDTC, one for each SQL instance (read the awesome post by Cindy Gross on this).

The install of MSDTC went smoothly and it seemed very happy. Then I failed over the cluster…

MSDTC failed.

It wouldn’t restart.

I failed back to the original node and it wouldn’t start there either.

 

What’s the error?

I dumped the last few minutes of the cluster log by opening a dos box command prompt and running…

cluster log /gen /span:5

 

This dumps the last five minutes of cluster logs into C:WindowsClustercluster.log

I scrolled through to get to the MSDTC error piece:

INFO  [RES] Physical Disk: Failed to get vol MP root for path ?, status 123
ERR   [RHS] Error 123 from ResourceControl for resource <instance>_MSDTC.
WARN  [RCM] ResourceControl(STORAGE_IS_PATH_VALID) to <instance>_MSDTC returned 123.

I checked the disk resource for MSDTC and it was online. Looking at the filesystem on that disk and there was an MSDTC directory, so I knew there were no access problems. It didn’t make any sense.

 

So what’s going on?

The key error here is the failure to get MP root for path ?

Apparently MSDTC is not supported does not work with Mount Points, which is what I had set the dependency to. There were no warnings on this when setting MSDTC up and I’d not seen or heard of any documentation that speaks to this.

I was finally pointed to a Connect item opened by someone who’d had the same issue https://connect.microsoft.com/SQLServer/feedback/details/576545/msdtc-fails-to-restart-in-sql-server-2008-r2-clustered-group

Side note: I love it when Connect items such as this are closed as by design. Why is this by design? Can someone explain to me why MSDTC shouldn’t be supported on Mount Points?

 

I deleted the MSDTC resource and added it again, this time using a regular drive as a dependency and everything worked perfectly. I was able to failover and have SQL perform distributed transactions.