James Boother

I took a trip up to Birmingham after work on Tuesday to present an Introduction to PowerShell for the nxtgenug user group. It was a good turnout and a very warm and receptive audience, thanks to everyone who came out for it. I enjoyed giving the presentation and hope that some of the audience will have been encouraged to take a look at PowerShell in more detail. I promised to get the slides from my presentation up onto my blog, it's taken slightly longer than I would have liked but you can now find them here. I will be blogging about some of the more in depth scripts soon so stay tuned folks.

I was fortunate enough to hit the developerdeveloperdeveloper.com website early enough to bag myself a ticket to the latest #Guathon taking place in London yesterday. This was a free event thanks to Microsoft sponsorship. I understand that there was very high contention for these tickets with a waitlist of over 700! To add to the drama the venue had to be switched in the 11th hour and it was only thanks to the great efforts of Phil Winstanley and Dave Sussman that the event was able to continue from it's new location in Leicester Square.

Phil introduced the days proceedings and included a mention to #GiveCodeCampUK and persuaded Paul Stack to stand up explain the event and provide further details. I've signed up for #GiveCodeCampUK, managed to persuade a few others to also sign up and would encourage you to join in as well.

Scott's first session was covering a brief overview of MVC 3.0 which he called "MVC 3.0 0 - 60" this was a great tour of the new features available in the framework and included a few points that I hadn't seen before:

• Modernizr is a JavaScript library that allows HTML 5 mark up to be used against legacy browsers.
• The team are going to be adding automatic minifying and bundling of JS files within MVC projects
• Glimpse - a tool available through nuget that behaves little bit like fiddler on the server..

MvcScaffolding - Another nuget package to create a repository pattern on top of the EF Code First framework.

I asked Scott if Code First EF can keep a data dude project in sync. At the moment this isn't possible but there is a new migration tool being released on nuget in the next 3 - 4 weeks that will allow migrations from one database to another. The team also have plans to add data dude project sync support in the next version of EF Code First which would be awesome. I don't think the lack of this feature should put you off using EF Code First with a data dude project because you could manually do the sync within data dude, but removing the manual step would help reduce the cognitive load during development.

Steve Sanderson then provided us with a really insightful presentation focusing on the use of Sync in the vNext feature set with MVC and how web applications that need to perform long running tasks can be made more performant with this technology. Although it doesn't have an immediate application I can see this becoming a standard pattern for web development in the coming years. Some of these features can be accessed today by installing the SignalR nuget package.

Steve's second presentation was of the Knockout framework. This is a JavaScript framework that allows the application developer to create MVVM web applications quickly and easily with HTML 5 and JavaScript. Creating an MVVM model for HTML/JavaScript web development looks like a very attractive proposition both from the developer  and end user's perspective. I'll certainly have a play with this framework, time permitting.

Scott then returned taking us "to the cloud", this presentation was a mixture of high level overview information mixed with details of the latest tooling available to create applications quickly and effectively that can scale well beyond a conventionally hosted application. Scott talked about how software can be composed very easily using the Azure building blocks such as the Service Broker, Web Role and SQL Azure to produce very robust, cost effective enterprise grade applications.

In summary this was a great day with two inspiring presenters. It has encouraged me to look at some of the  emerging technologies coming out of Microsoft and also to re-visit some of the production ready tech that I haven't already mastered.

Tonight I'm off to a SQLSocial evening with some of the SQL Server leadership team, hopefully I'll be able to report back on some of the exciting developments coming to SQL Server tomorrow, busy week!

To wrap up I'd like to thank Scott Guthrie and Steve Sanderson for filling my brain up all day, and to Phil Wilstanley and Dave Sussman for organising #Guathon.

I've kept a rough install list for a few years. I find it useful to have a general running order when installing a new PC to make sure that I'm on track and it avoids me finding an essential utility missing when I need it most. I've also been asked for the list a few times when friends and colleagues are trying to decide what to install on their fresh PC and in which order, having handed it out a few times and needing it myself a fair bit recently I decided to add it to my blog. This is very much my build list and not intended to be a definitive list of developer tools, Scott Hanselman has already done a very good job of this and I'd recommend you visit the Ultimate Developers Tools List for a very comprehensive list of developer tools.

Back to my build list. I've provided it in the order that I install a new PC:

Windows 7 with SP1 and then run all the essential and optional updates (hide foreign language packs)

If this machine is going onto a Domain I add it now and make sure that my domain account is an administrator. Reboot and then run everything in my domain profile to make sure I've got all the shortcuts etc. setup in my domain profile.

Evernote - I've only recently converted to Evernote, previously I was a OneNote fan, and I do prefer OneNote but unfortunately there isn't a client for my Android phone so I've switched to Evernote until I upgrade to a Windows Phone 7 handset. This is the first application I install after windows because it stores my build list. I won't necessarily need to install it first in future because I'll have my list here.

Spotify - Installing software can feel a bit like watching paint dry, so I need some tunes while I get all this software on-boarded and Spotify fits the bill here. We've also setup a shared playlist for the office here: Office

Microsoft Security Essentials - I switched to using Microsoft Security Essentials as my Virus Scanner shortly after it came out of beta and I've been very pleased with it. It doesn't install loads of other software that I don't need or want and doesn't drain all the resources from my PC to do it's scanning.

Windows Live Essentials - I don't install the whole suite but I use Messenger to communicate with friends and colleagues, Live Mesh to sync documents across PCs and as a remote desktop tool for my home PC, and Windows Live Writer for writing this blog. I'm careful when installing the suite to remove the Bing bar and the tools that I don't use.

IIS7 - Some of the work I do is web based and therefore hosted within IIS. I therefore add All the IIS7 features from Windows Features apart from ftp. It's still important for me to have the IIS6 script compatibility because some of our automation works against the IIS6 metabase.

IE9 - Call me a fan boy if you like, but I've always kept IE as my default browser. I'm hoping that IE9 will prove popular because my experience has been very positive and I won't be the ridicule of the office joke for having IE as my browser of choice - I also set Bing as my homepage (I like the daily picture)

Firefox 4 - I keep other browsers on my machine as well. I need to ensure that I've done cross browser testing even if I don't use these browsers for day to day surfing.

Chrome 9 - Chrome has very quickly taken considerable market share so I keep this for cross browser testing.

Opera 11 - Opera keep churning out good quality browsers although they've not managed to attract as many users from the PC user base I make sure that I've had a quick tour of the web pages within Opera.

Virtual Clone Drive - Virtual clone drive is a superb utility that will mount a .iso file stored on the local machine or on a network drive as a virtual DVD. This saves a lot of hassle storing DVDs and because a lot of the media we use is distributed via the Internet we only need to have physical OS DVDs now the rest are mounted straight from a HDD which is both fast and convenient.

SQL Server 2005 BIDS bits only - yes I still need this for development of SQL Server 2005 reports. I install SQL Server 2005 developer edition from my MSDN subscription making sure that I only install the developer tools options. I then install the latest service pack. Microsoft Support provide a convenient knowledge base article 913089 which always points to the latest Service Pack available.

SQL Server 2008 R2 - I then perform a Full install of SQL Server 2008 R2 developer edition with all features using the defaults saving a few decisions and mouse clicks - you never know when a feature will come in handy, and this is a developer box, right?

Visual Studio 2010 with SP1 - I do most of my development work within VS so this is possibly the most essential install item on the list. I installed my last machine just before SP1 RTMd so installed SP1 when it became available. Hopefully I'll be able to find a slip streamed install on MSDN this time otherwise I'll simply install SP1 straight after the RTM install.

This is the Visual Studio 2010 Feature Pack 2 - Additional testing and code visualisation / modelling features are made available in this feature pack. It can be downloaded from MSDN Subscriber downloads.

Visual Studio Power Tools - I love the find feature and the tab colouring and feel these two features make it worth the download and install time alone. There are a whole wealth of additional tools for VS bundled into the Productivity Power Tools pack.

Stylecop - Stylecop helps to improve code readability by having a set of rules that are applied to the source code. This is a C# only tool and is worth using even if you are working on a project alone because your code will become more consistent and readable as a direct result of using it. The tool really comes into it's own when used in a team setting because everyone in the team will now be coding with a common style, stylecop is provided with a set of MSBuild tasks so it can become part of an automated Continues Integration build enforcing coding standards before a team build can complete.

Ghostdoc - With the magic key chord of <CTRL>+<SHIFT>+<D> you can create a skeleton comment structure for a method, class, Interface etc. This means that XML comments can be created quickly providing a great way to document the API.

MVC 3 - What's not to like about MVC3? We've started making use of the Razor view engine in one project and I expect all future projects that are suitable candidates for MVC will use the MVC3 framework.

nunit - Although we use MSTest for internal projects some of our clients prefer NUnit so I make sure that I've got both options available and ready to roll on my machine. My view is that it doesn't matter which testing framework is being used as long as the code is verifiable, I don't want to enter a debate on this subject.

specflow - Allows business specifications to be bound to functionality within the application. This is part of the Behaviour Driven Devleopment movement and although I haven't used it in anger I can see how it could be beneficial. I've installed the framework onto my PC with a plan to investigate it.

Resharper - I'm a pretty new convert to resharper and after a short learning period I have seen many of the benefits in using this tool to help identify and refactor code.

Office 2010 - This is a productivity tool that is ubiquitously useful to any PC user not just developers.

BPOS Single Sign On Client - This tool keep logging onto BPOS tidy and has useful links to the sharepoint sites etc.

Open Outlook with the BPOS profile then setup all my additional email accounts

MetroTwit - I have a love hate relationship with Twitter clients, but for the time being have settled on MetroTwit on my PC.

notepad++ - Notepad++ is an extremely quick text rendering and editing tool. It comes with many language syntax colouring configurations and can also be used to compare two files.

feed demon - I like having a desktop blog aggregator. The nice thing about feed demon is that it synchronisers with google so I can have my cake and eat it here.

Paint.Net - Handy for making quick changes to image files, very powerful image editing software and free.

7-Zip - A fast and free compression tool.

PowerGui - I used PowerGui as my primary PowerShell script editor until I discovered the Visual Studio Extension listed below. The VSX actually uses PowerGui to render the scripts so I still need it installed.

PowerGui VSX - Visual Studio Power Gui Integration.

LastPass - A very handy password manager that adds into most modern browsers. It allows me to store all my important passwords online and sync between computers so I'm never stuck for a password again. I've also been able to generate more of my online passwords making everything more secure.

Flash player

adobe reader

Sysinernals tools - The sys internals tools are a must for any IT Pro worth his salt allowing further diagnostic and system tools that Windows does not provide out of the box.

VirtualBox - Virtual box is a virtualisation platform that allows me to virtualise both 32bit and 64bit client OSs. I use this as my primary virtualisation platform on my workstation, favouring HyperV on the server.

VirtualPC 2007 - I don't actually use Virtual PC for virtualisation because it doesn't support 64bit client OSs. I do however make sure that I create disks in Virtual PC and then use them in VirtualBox, this is because Virtual PC can create disks of the VHD format whereas VirtualBox can't create them but can use them. This gives me the option to move a Virtual Machine with it's disk created in VHD format on Virtual PC and then the machine created on Virtual Box to be moved to HyperV at a later date if I need.

I setup my synced folders with LiveMesh and allow that to complete it's sync process so I'm safe in the knowledge I've got my documents locally, especially if this is my laptop rebuild.

I know have a final sweep to tidy up and delete any shortcuts that slipped passed my manual install radar of every application and ended up on my desktop or on my taskbar. I then pin applications to my task bar so that I can use <Windows>+<NUM> where the number is the position the application appears in the taskbar to launch the application quickly.

I finish the install by Running Windows Update until I've installed every update and the water runs clear.

There you have it a clean developer pc ready for work. This pretty much covers the install list I use, I may have missed the odd item here or there and if I notice anything or my list changes I'll update my Build List.

I attended the London SQL Server User group last week. It was a great event with a good turnout especially when you consider that many of the familiar faces are out in Seattle at the MVP summit.

The evening kicked off with a whistle stop tour of XQuery by Neil Hambly (Twitter | Blog) this was a very energetic overview of the feature with some interesting examples where Neil had taken large volumes of XML data which in this case were Query Plans > 30 GB each. He cut each plan down into more manageable pieces and imported them into SQL Server. Once there he demonstrated how he could query the documents for key factors of interest and then pull out attributes and values into temporary tables that he could easily analyse and report against. He says that he's looking at ways to automate this process so that he can run it across 100s or even 1000s of servers a day to identify where the DBAs should be focussing effort. I hope that Neil has a chance to blog about this because it's a very interesting line of research.

We were then treated to an introduction to Stream Insight. I hadn't had an opportunity to look at Stream Insight before so it was great as a newbie to have an introduction with both conceptual aspects and technical details explained. From what I gleaned of the presentation Stream Insight is a whole new paradigm of BI where the service is analysing real-time events and reporting on only the events that match specific criteria. I am already thinking of ways it may help with projects I'm involved in.

Dave Ballantyne (Twitter | Blog) wrapped up the evening making querys go faster. It's always a pleasure to watch Dave masterfully tackle a slow running query and provide step by step instructions of how to iteratively optimise it without making any changes to the database schema.

Part of a user group is the social aspect and it was good to catch up with fellow SQL Server professionals including Gavin Payne (Twitter | Blog) and John Samson (Twitter | Blog). I'd been following John on Twitter and reading his blog but not had the chance to meet in person before.

Before the event Dave Ballantyne tweeted that "With the MVP's out of town at #MVP11 , the b-team steps up to the plate :) . London SQL UG tonight" however I disagree with this statement, we were privileged to have 3 very strong presentations which makes me think the UK SQL Server community is like a top flight Premiership football team who can field two A teams!

It just leaves me to say thanks to Neil for organising the event, to the speakers and to Avanade who hosted the evening.

On Saturday I presented an Introduction to PowerShell at DDD9 as promised during my presentation I've uploaded the slides and provided them for future references here on my blog.

I had a a great time at DDD and would like to thank the organisers, helpers, other speakers and everyone who came to my session. If you have any PowerShell questions please don't hesitate to contact me.

You can view the slide deck here

Happy Scripting!

I was creating a PowerShell script today that executed a query using SMO. Everything was working smoothly until I needed to run a query that took approximately 25 minutes to execute when I received a Method Invocation error.

I had a feeling that the command was timing out because I was receiving the error 30 seconds after the TSQL was being executed. It took me a while to find out how to set the timeout because I was looking for Command Timeout but in SMO it's referred to as statement timeout.

Hopefully this will save you a few minutes if you bump into a similar situation. See the code below to set an indefinite timeout value against the statement execution:

#Add reference for SMO assembly
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null

#Connect to the local SQL Server
$server = New-Object Microsoft.SqlServer.Management.Smo.Server "."

#Connect to the adventure works database
$database = $server.Databases["AdventureWorks"]

#Set the statement timeout to 0 which allows the statement to run indefinitely
$server.ConnectionContext.StatementTimeout = 0

#Execute a really long running query that would otherwise timeout
$database.ExecuteNonQuery("DBCC CHECKDB('AdventureWorks')")

Happy Scripting!

Over recent years there has been plenty of discussion about SQL Server index maintenance. The basic principle is that you must maintain your indexes to prevent them from becoming too fragmented resulting in reduced query performance.

Many TSQL maintenance scripts are available but I haven't seen much in the way of PowerShell index maintenance scripting. PowerShell is an obvious contender for achieving this task so I decided to create a simple script using PowerShell and SMO so that it can be run on a machine with only PowerShell 1 and SQL Server 2005 installed.

This script iterates through all the tables and all the indexes for a given database and identifies indexes that have a fragmentation between 5% and 30% and performs an index reorganization. It identifies indexes with a fragmentation of greater than 30% and performs a rebuild, all other indexes will be left alone. This is based on the recommendation from TechNet despite Paul Randal's blog post where he admits that he made up the thresholds.

The script takes two parameters; the server name and the database name. See the code listing below:

[string] $ServerName = $args[0]
[string] $TargetDatabaseName = $args[1]

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $ServerName
$targetDB = $server.Databases[$targetDatabaseName]

foreach ($table in $targetDB.Tables)
{
    foreach($index in $table.Indexes)
    {
        $fragmentation = $index.EnumFragmentation()
        $averageFragmentation = $fragmentation.Rows[0].AverageFragmentation
        
        if($averageFragmentation -lt .05)
        {
            continue
        }
        
        if($averageFragmentation -ge .05 -and $averageFragmentation -lt .3)
        {
            $index.Reorganize()
            continue
        }
        
        $index.Rebuild()
    }
}

I'll be using this script on a database that's the backend of a public facing website so I plan to extend the script to publish a holding page into IIS, run the index maintenance tasks and then pull the holding page from IIS so that the script can be scheduled to run at a time where little traffic is coming to the site but any users who do will not be affected by the maintenance.

If you have comments or ideas I'd be delighted to hear from you.

I was working with a customer reviewing a database server when we noticed that many maintenance plans had been created on the server that were not being backed up so in the event of a catastrophic failure someone would have to re-create the plans. Worse still there wasn't a document of the plans so they would have to be re-invented as well assuming that anyone could remember what had been created in the first place.

As a first step to improvement I suggested that we backup the plans which can be easily done from the SQL Server Management Studio (SSMS) as long as you know where to look, sometimes SSMS feels like a really big toolbox with many draws and it's important to remember which draw contains the tool you're after.

It's relatively straight forward to find the location for creating and editing maintenance plans. Simply connect SSMS to your database engine and navigate down the Management >> Maintenance Plans tree in the Object Explorer window. Unfortunately the product team didn't provide a way to backup plans here on the user interface.

In order to locate the UI to backup a plan it's important to remember that Maintenance Plans are actually SQL Server Integration Services (SSIS) packages, with this knowledge it's possible to locate the right draw in the toolbox. The first step is to connect SSMS to Integration Services instead of the database engine, the screen shot below shows the change on the Server Type drop down from the default Database Engine to Integration Services on the Connect to Server dialog:

Once connected to Integration services it's simple to locate the Maintenance Plans in Object Explorer because they're stored within SQL Server in a Maintenance Plans folder so simply navigate through Stored Packages >> MSDB >> Maintenance Plans in Object Explorer:

Select the plan that you want to back up and right click on it which reveals the Export Package… context menu item. Clicking this item displays the Export Package dialog:

You will need to select File System in the Package location drop down and then set the Package Path and the Protection Level before you're able to click ok and save the plan to disk. This creates a .dtsx file that you can import back in or import into a different server.

This is all well and good for a couple packages but as soon as you need to backup more than this the UI becomes a bit of a hindrance because the dialog resets back to the defaults shown above and it takes sometime to configure it to save the package to disk as you require. At this point I decided to turn to my new found friend PowerShell. I am now armed with the knowledge that I need to export SSIS packages to disk from the MSDB >> Maintenance Plans folder and so worked on a script to do just that.

The first stage of script development was to get a reference to the Managed SSIS library. Unfortunately there is a different version provided with SQL Server 2005 and 2008 so it's important to reference the one that's installed on the target computer. So if you are using SQL server 2005 add the following line to your script:

[reflection.assembly]::Load("Microsoft.SqlServer.ManagedDTS, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91") `
    | Out-Null

[reflection.assembly]::Load("Microsoft.SqlServer.ManagedDTS, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91") `
    | Out-Null

$app =  new-object ("Microsoft.SqlServer.Dts.Runtime.Application")

$plans = $app.GetPackageInfos("Maintenance Plans",".",$null,$null)

foreach ($plan in $plans)
{
    $package = $app.LoadFromSqlServer("Maintenance Plans\$($plan.Name)", ".", $null, $null, $null)    
    
    $package.ProtectionLevel = [Microsoft.SqlServer.Dts.Runtime.DTSProtectionLevel]::DontSaveSensitive
    $packageFileName = "C:\MaintenancePlanBackups\$($package.Name).dtsx"
    
    $app.SaveToXml($packageFileName, $package, $null);
}

We have computer equipment in two main locations our office in Hatfield and a remote Data Centre. This allows us to have our desktops and laptops in the office near to where we work (most of the time) and our servers in a safe and secure location with great power and Internet connectivity.

The location of the equipment is transparent to the users because we have ISA Server running at both locations and we use a Site to Site VPN routing IP traffic between the two locations, this has worked well for us for the past 5 months until last week when the connection mysteriously went down.

Initially I assumed that the servers had not recovered following an Internet outage in the office caused by some work BT had been doing so as all good IT Professionals would I initially rebooted the ISA Servers on both sides of the connection. Unfortunately shortly after both servers came back online I quickly realised that the Site to Site VPN connection was not being restored. I was able to connect a VPN client machine to an ad hoc VPN connection at both ends so I was fairly sure that nothing fundamental had changed such as a new public IP number being issued. This lead me to believe that I had an authentication issue between the two computers so I initially performed some rudimentary checks such as making sure all the required services were running on both servers and that the clocks were accurate, everything was as I would expect which caused me more concern than gave me re-assurance.

I did have an event log entry on one of the servers the RasClient with an Event ID of 20227 and an error code for the failure of 789. After some Binging and Googling I was ready to give up the error message was very generic and I was struggling to find an insight into what was causing the problem in my case. I was almost ready to resign myself to asking everyone to use a local VPN client until I had more time to investigate further. Then in a final desperate attempt I tried a different search term and discovered a post on the ISA Server org website error 789: The L2TP attempt failed... I had previously discounted this thread because it was a problem with Windows XP client but after reading it again the problem sounded very familiar and I was concerned that I had an authentication issue between just these two machines, so I took the advice and restarted the IP Sec service on the ISA Server with the event log error. Within seconds our VPN was restored and everyone was able to once again forget where servers in our topology are located.

I've posted this here in case I have this problem again and can't remember how to resolve the issue, it may also help you if you encounter a similar problem.

Mike and I set out in business just over 6 years ago, that is almost medieval times in the IT world and much has changed since then. We regularly plan and revise decisions we've made in the past, during a planning session earlier this year we started questioning whether we needed to host email on-premise.

In 2004 it was second nature that one would setup an Exchange server in order for us to have enterprise level office productivity suite this came at a big expense in hardware, software, and time. Fortunately we became a Microsoft Partner as soon as we setup so the software was not as expensive is it would have otherwise been. We had both worked as Microsoft developers for many years but we had to quickly learn a whole new set of skills in order to build and maintain a reliable IT infrastructure, we learnt all about networking, DNS, Firewalls (ISA Server in our case) Windows management & monitoring and Exchange itself with all the pre-requisites that are required for it.

In 2007 we upgraded our environment to 64bit in order to run the latest and greatest Exchange features, we had to learn some new skills again in order to migrate from Exchange 2003 to 2007. We took much of this in our stride but we did make mistakes along the way and I've always had that nagging feeling that if part of our infrastructure fails we are very exposed.

In 2009 we signed up as a BPOS reseller but I've not recommended it to anyone because I wasn't sure of the quality. That was until today because I've migrated our on-premise Exchange and SharePoint infrastructure to BPOS. I've been delighted with the migration and now don't worry about whether the Exchange server is running when I go to bed on a Saturday evening for example.

Once signed up for BPOS everything is extremely well laid out in a portal with plenty of useful help files and some utilities to assist with the migration. I used the on-premise to online migration utility to initially create and copy all our mailboxes online and then forward all mail onto the online accounts. I was then able to update the DNS which allowed us to have a zero downtime migration.

I was also impressed with the PowerShell cmdlets that are installed with the migration utilities. I was able to make further customisations with this functionality that isn't available elsewhere such as provide users with read/write permissions to different mailboxes.

Since the migration everything has gone very smoothly was have all connected our mobiles to the Push email these include Windows Mobile 6.5, Andriod, and Windows Phone 7. Webmail works very well as you'd expect and the Single Sign-on application makes signing into the different services a breeze.

We initially made the move to BPOS to have a more reliable Messaging infrastructure readily available online but we are also benefiting from the additional BPOS features. We're finalising the migration of all our documents onto SharePoint Online and we have made extensive use of Live Meeting. We are all going to have headsets and webcams so that we can make even more use of this great online conferencing tool.

I'm looking forward to the launch of 365 which should see SharePoint 2010, Exchange 2010, and Lync becoming available later this year.

I'd wholeheartedly recommend using BPOS for any SME size business and enjoying the benefits that it's bringing to my business.