Using Powershell to push registry fixes around multiple machines

PowerShellNeeding to push this (Microsoft KB 911272 around a bunch of web servers in a hurry. Luckily, thanks to 2 good design decisions:

  • Enabling PS remoting
  • A sane machine naming convention of the form server-1, server-2

We could push the change around with a small bit of Powershell:

$ccred = Get-Credential

    New-ItemProperty "HKLM:\Software\Microsoft\ASP.NET" -Name "FCNMode" -Value 1 -PropertyType "DWord" 
    New-ItemProperty "HKLM:\SOFTWARE\Wow6432Node\Microsoft\ASP.NET" -Name "FCNMode" -Value 1 -PropertyType "DWord" 

while ($i-lt 9){
    $cname = "app-prod-web-$i"
    Invoke-Command -ComputerName $cname -ScriptBlock $ScriptBlock -Credential $ccred

We grab some credentials for the remote boxes (remember, seperate accounts for desktop and server admin duties is a GOOD idea).

As we’re passing through multiple commands/lines to Invoke-Command using a scriptblock is the easiest way, so create one of those. Quickly use New-ItemProperty to add the required DWORD values under the right keys, and then an iisreset to make sure they take affect.

A nice quick and dirty while loop allows us to push the scriptblock to the 8 servers in this set, and we’re done. A quick change of the $cname variable and I can push it to the next set as well. And another edit and it’s pushed into the staging environments as well.

Nottingham SQL Server User group begins

After many successful years running in Leicester we’re proud to announce that the PASS East Midlands chapter is moving to Nottingham for 2016.

Gavin had been running the group since it’s birth, but has now handed the reigns over to myself and Tobiasz. As we’re both based in Nottingham we have decided to move the SQL User group to Nottingham. With the response we had for Nottingham’s first SQL Relay event we wanted to build on that, and this also makes it more accessible with Nottingham’s connections to the rest of the East Midlands and Lincolnshire area.

We’re going be based at Capital One’s Trent House on Station Street, easy to get to from Derby, Leicester, Loughborough, Lincoln, Sheffield and all parts of Nottingham

We have 4 SQL Server User group dates lined up for next year:

As with all PASS SQL User groups there is no charge for attending, yep, that’s free training! We’ll even provide some food and drinks while you learn!

Myself and Tobiasz are speaking at the first event, and we’re currently lining up some great home and international speakers for the other sessions.

But we’d love to hear from you if there’s any particular topics you’d like covering, I’m happy to go out and find speakers covering areas of special interest to our members. Also let us know at what level you’d like sessions presented, if you want an introduction to a particular topic that’s great, as is wanting a level 500 brain warper from the likes of Bob Ward!

All details for future meetings will be posted via our Meetup page – PASS East Midlands User Group.

Please register with the meetup group to keep up to date. You’ll also need to register for the event to attend. This is because we need to provide Capital One with a list of names to be signed in at security, so if you haven’t registered then you won’t be allowed in I’m afraid.

Look forward to seeing some of you there. Please post any comments here or at PASS East Midlands User Group

SQL Relay bringing free SQL Server Training to Nottingham

sqlrelaylogo70Having spoken at the last couple of SQL Relays, I’m very happy to announce that in 2015 we’re visiting Nottingham for the first time.

On the 7th October SQL Relay will be at the Nottingham Albert Hall conference center.  Speaker submissions are now coming in (and if you fancy speaking, please submit a session), and we hope to release the full lineup early in August. Based on previous years there will a wide range of high quality presenters.

At Nottingham we will be having 4 tracks on the day:

  • SQL Server
  • Business Intelligence
  • Business Analytics
  • Workshops provided by our sponsors

Across these we’ll be covering topics such as SQL Server performance, management and development, Azure/Cloud technology, R, Big Data, Cubes, Reporting , Dashboarding and much more.

If you’re a SQL Server DBA, then this could be a great opportunity to pick up some Business Intelligence crossover training. Or for a Business Intelligence developer, find our how to use Big Data solutions (on Azure, without the hardware costs) to analyse your data even further.

All of this great SQL Server content is available for the princely sum of Nothing. That’s right, its free, and we’ll even provide you with lunch.

We’re open for registrations, so sign up today and look forward to some quality free SQL training in Nottingham

If you’ve any questions, then please ask them.

Finding the bottom folders of a file tree using PowerShell

There’s a simple way of getting Powershell to tell you the last folder in a known path:

$path = \\server\share\folder1\folder2\folder3
$folder = Split-Path $path -Leaf

and that’s all you need.

Now, how do you go about doing that if you don’t know the path ahead of time? That is, you have a large collection of directories, and all you want are those folders than make up the bottom levels of the branches. In this graphic it would be all the folders underlined.

Example of bottom folders

As an example, current $EMPLOYER holds backups from all SQL databases on a share, with each database’s backups being in a folder. Due to various DBAs over the years the depth of the tree to the actual backups isn’t fixed. So I need to find the “bottom” folder so I can process the backup files in there.

Well, that’s a little trickier. So lets have a look at a couple of ways of doing that:
Continue reading

Bulk adding Active Directory service accounts with PowerShell

I’ve recently had to create a lot of new service accounts to support some new infrastructure projects. As these are supporting a lot of web services, there’s been a large number of service accounts that needed creating to own all the app pools.

To make life simpler when this moves from implementation to support, all of these accounts want to be setup the same way. And we want secure passwords as well.

And then those accounts also need to be setup for each of our prod, stage, dev, test environments.

So, a dull, repetitive task where making sure details are replicated exactly, this sounds like the perfect job to automate with some PowerShell.

[Reflection.Assembly]::LoadFile('C:\Windows\Microsoft.NET\Framework\v4.0.30319\system.web.dll') | out-null

function New-Password ($length = 12)
        $p = [System.Web.Security.Membership]::GeneratePassword($length,2)
        $password = $p | ConvertTo-SecureString -AsPlainText -Force
        $retpass = @{"string"=$p; "secstring"=$password}
        return $retpass

$environments = @()

$environments += @{"env"="prod";
                   "adpath"= "ou=Prod Accounts, ou=Service Accounts, ou=Domain Controllers, dc=ad, dc=contoso,dc=com,";

$environments += @{"env"="test";
                    "adpath"= "ou=Test Accounts, ou=Service Accounts, ou=Domain Controllers, dc=ad, dc=contoso,dc=com,";

$environments += @{"env"="dev";
                    "adpath"= "ou=Dev Accounts, ou=Service Accounts, ou=Domain Controllers, dc=ad, dc=contoso,dc=com,";

$Users = import-csv -path "c:\temp\users.csv"

$output = @()

foreach ($environment in $environments){
    $out += "`n "+$environment.env + "`n"
    foreach ($user in $Users){
        $password = New-Password( 15)
        $username = $environment.prefix+$user.user.trim()
        Aew-ADuser $username -Path $environment.adpath -AccountPassword $password.secstring -DisplayName $username -PasswordNeverExpires $true -ChangePasswordAtLogon $false -CannotChangePassword $true -Enabled $true
        Add-ADGroupMember $identgroup -Members $username
        Add-ADGroupMember $rogroup -Members $username
        $output += @{"username"=$username; "password"=$password.string;"Environment"=$environment.env

First off, we’ll want to be creating secure complex passwords. Rather than rewrite everything from scratch, we’ll use the built in function from the .Net System.Web class, which we load in the first line.

Then we’ll wrap this call in a PowerShell function to make calling things easier:

function New-Password ($length = 12)
        $p = [System.Web.Security.Membership]::GeneratePassword($length,2)
        $password = $p | ConvertTo-SecureString -AsPlainText -Force
        $retpass = @{"string"=$p; "secstring"=$password}
        return $retpass

We pass in a length parameter for later use, but set a default to guarantee at least 12 characters. Then we create a new password of required length, the 2nd paramater to GeneratePassword specifies the minimum number of special characters (@!# etc) that the password has to contain. GeneratePassword returns the password as a string, but we need it to be a SecureString to pass to an Active Directory cmdlet later on, so we convert it. Then return the password both as a string and a secure string via a Hash Table.

Now we setup our environments we’ll be creating our accounts for. I like to do this using arrarys and hashtables. We have an array/object $Environments which holds the details of all of the environments we want to create. For my needs, each environment is defined as follows:

$environments += @{"env"="dev";
                    "adpath"= "ou=Dev Accounts, ou=Service Accounts, ou=Domain Controllers, dc=ad, dc=contoso,dc=com,";

The values in the hash table are:

  • The environment Name
  • The prefix for the usernames for this environment
  • The path in Active Directory for the OU the user account should be created in
  • An group for all the accounts in this environment to belong to
  • Another group for all the accounts in this environment to belong to

This is based on the AD setup I’m currently working with, your needs may be different, if you’re not the Active Directory admin then please go talk to them about how they’d like things configured

Now that we’ve got everything setup, let’s create some accounts!

I load the usernames from a csv file that looks like this:


The first line stays at user, so that we can use it to get the values later on.

Create an array $output to hold our output.

Now we loop through each of our environments, and then for each environment we loop through out list of users.

For each user:

  • we create a new secure password using our previously defined function.
  • Build a full username using the supplied username and the appropriate prefix for the environment
  • Create the user in Active Directory based on our environment
  • Add them to any groups we want them in
  • Add the newly created users details to our output object

And we’re done. In the great tradition of PowerShell I’ve kept the user details in an object $output so they can easily be passed along to another script, command or whatever you want.

If you want to just dump them to screen, then you can use:

$output | Format-Table 

Or redirect the object to you secure password storage utility of choice.

And another dull repetitive task is handed off to a simple script.

Upcoming Presentations

Well, it’s been a while since I last did some presenting, mainly due to having purchased a new home and having gotten stuck in with renovating it.

After a successful session up at SQL Server User Group North East last week talking about using PowerShell to migrate SQL Server instances, I’ve another 3 sessions lined up around the country. 2 as part of the SQLRelay set of events, and 1 one at the Leicester SQL User Group

All 3 sessions will be my presentation “Transaction Log Basics”, this abstract should give you a good idea of what to expect:

Every SQL DBA has at least 5 transaction logs that they need to take care of. But what exactly is going on inside that file? Why can it be a performance issue? Is it really that important? And why does it keep filling my disks when I’m not looking?

This session will answer all of those questions. We’ll be looking at how SQL Server relies on it’s transaction, best practices that will keep it performing properly and explaining why it’s so vital to your databases and your job to keep is safe!

It’s a perfect introduction to SQL Server transaction logs and also a good way to make sure you won’t fall into problems later on.

The dates are:

SQL Relay Southhampton – 14th October
Leicester SQL Server User Group – 15th October
SQL Relay Birmingham – 29th October (unfortunately this event is fully subscribed, but there is a waiting list you can sign up for)

Looking forward to seeing plenty of people at these events. If you’re reading this and planning on attending, then let me know, or come and say hello on the day.

Deleting Large amounts of data in SQL Server – Part 1

There comes a time when you’ll be asked to remove a large amount of data from one of your SQL Server databases. Perhaps an archive of order information is needs pruning, or removing those session records that aren’t needed anymore.

Yu’ve been off to have a look at, and it all looks simple enough. Looks like there’s a couple of million rows to delete, but you can identify them all easily. How easy is this going to be you think:

DELETE * FROM Table1 where DateAdded<getdate()-90;

And then it just sits there, and sits there, and sits there. Or you get a nice error message about breaking a key constraint. What to do about it?

Continue reading

Adding SQL Server jobs using PowerShell

PowerShellA sudden requirement for a new SQL Server agent job to be put on to every instance in our Enterprise was hardly greeted with joy. At least by those who hadn’t been playing with PowerShell.

Just like everything else in SQL Server, we can automate SMO to achieve our ends. And once we’ve got it working on one SQL Sever instance, it’s simple to extend this to hundreds.
Continue reading

SQL Saturday Exeter 2014 Redux

sqlsat269_webAnother great event from the SQL SouthWest team this year. Great atmosphere at the pre event Surf Part on the Friday night, catching up with old friends and making some new ones. Those who had a go on the mechanical surfboard seemed to enjoy themselves, unfortunately my knee injury meant I couldn’t have a go.

As I’ve mentioned before I was given the opportunity to try something a little bit different. So I was presenting a double session on “PowerShell for the curious DBA”, which I’d targetted as a short introduction to PowerShell for SQL Server DBAs who’d never really met it before.  I’ve uploaded the deck and scripts here if you’d like a copy.

Feedback on the session and the format from the attendees was good, and I’m looking forward to seeing the ‘official’ feedback from the orgs as well. I though the format of the session was great, allowed a topic to be covered in more depth, but wasn’t such a long session that you were ‘comitted’ to missing lots of other sessions.

Talking of other sessions I went along to the following:

Hugo Kornelis (b | t) – Good session on using Windowing functions to work around some more complex tasks in T-SQL, definitely something you want in your toolbox.

Kevan Riley(t) – Exploring some of the darker parts of SQL Server explain plans. Turns out it’s well worth checking the underlying XML as well as the nice graphical views.

Kevin Chant (t) – Certainly looks like anyone wanting to generate proper management reports will want to be checking out SharePoint and PowerView

Allan Mitchell (t) – Finally a proper explanation of how Hadoop hangs together, much more information than lots of reading around on t’tinternet.

Whittling down to that selection from the available sessions (here) was quite a task as well, lots of quality content was on offer so it could be a hard task picking which session to go to.

Unfortunately I couldn’t stay for the post event Curry as I had a 4 hours drive back to Nottingham, but the photos make it look like a lot of fun, so already planning a 2nd night’s stay if there’s a 2015 event.

Installing CRM 2011 on Windows 2012, error parsing Config file

Just been banging my head repeatedly with this one, and have finally found the cause of all the heartache.

As has been written in many many places, to install Dynamics CRM 2011 on a Windows 2012 Server, you need to ‘slipstream’ in RollUp 13. So, after reading one of the many blogs online about how to do this, you’ve got the following example config.xml all ready to go:

<Patch update=”true”>C:\RU13\Server\server_kb2791312_amd64_1033.msp</Patch>

Excellent you think as you run SetupServer.exe

And then up pops the handy error:

CRM Config file parse error

You dutifully trek off to look at the install logs, where there’s the helpful error message:

Error| Error parsing config file(HRESULT = 0xC00CE502) (configfileparse.h:CConfigFileParser::Parse:435).

Which is as useful as a chocolate teapot. I tried all the suggestions that I could find. Searching for this error across the web throws up lots of suggestions. Randomly adding Administrator rights, changes to the paths of files, and various other goodies. In this particular case there’s only one change that needs to be made to the config file:

<Patch update="true">C:\RU13\Server\server_kb2791312_amd64_1033.msp</Patch>

Can you spot it? The change is to the "‘s in Patch tag. In a number of examples online (and my first one) these are ‘s. Just to make that clearer:

"‘s work. That’s the ascii character returned by select char(34)

‘s don’t work. That’s the ascii character returned by select char(148)

Hopefully this will help save a some people’s foreheads, and some companies from dents in their desks.