Monthly Archives: June 2013

SQL in the City London

I had a wonderful time attending SQL in the City London.  It’s really great to see the new versions of the tools and exchange new ideas with friends.  Towards full disclosure, I’ve been a Friend of Red Gate for as long as I can remember, and also spoke at SQL in the City last year in both Austin and Seattle.  This year was a new format: leading attendees through a focused journey of continuous integration and deployment together in one large room.  It worked well.

There was also a small off-track room seating about 20 to 40 down a winding hallway, though with such a large attendee base, there was usually no room.  It did help us focus on the main event, but it did remove some of the free-flowing discussion that only a small group can bring.

The opening discussion was pretty much a spot-on repeat of last year.  We did the deployment balloon game, and successfully deployed only one of our 5 or 8 groups of balloons.  It’s a wonderful object lesson, though having seen the example and corresponding slides last year, it didn’t really grab my attention.  My fellow attendees seemed to enjoy the presentation — a wonderful reserved optimism that speaks volumes of the glorious British culture.  Breaks between classes helped us absorb the material and yielded great discussions.  We also queued very well for afternoon tea.  There wasn’t much space in the venue to hold us when we weren’t sitting down, so the displays and conversation areas were loud and cramped, and most of us either spilled out of the building or back into our seats.  “A Day in the Life of a DBA” concluded the day, and seemed like it could’ve been a wonderful discussion.  Alas, I quickly zoned out as it turned into a gripe session about everything DBAs hate about developers, networks, management, users, life, and why getting woken up is not fun.  All else being equal, I wish I had fit in the smaller room.

I’m really enjoying how the tools are maturing and coming together into a great suite, targeted nicely both towards on-premise use and cloud deployments, both tools to facilitate developers and tools to facilitate IT pros.  David Atkinson did a wonderful demo of continuous integration using the Team City plugin that drives SQL Compare, SQL Data Compare, SQL Docs, other tools, and in short order, database migrations.  Justin Caldicott and Grant Fritchey did a wonderful back-n-forth demo — from both developer’s and IT’s perspective — of the challenges of deployment and how Deployment Manager really saves the day.

Deployment Manager is struggling because it’s one of the few Red Gate tools that requires you accept their paradigm rather than fitting into a niche inside your process.  (SQL Compare, SQL Data Compare, SQL Monitor, .NET Reflector, ANTS Profiler, and even to a lesser extent SQL Source Control all get plugged into an existing workflow with ease.  Deployment Manager really needs to own the deployment process.  This is especially difficult to swallow since the IT Pro’s bread and butter is to ensure deployment is seamless, painless, and exact.  Like any good DBA, they get very OCD about it.  Deployment Manager asks the business to “trust us, we’ll do the right thing.”  Automation is wonderful, but black boxes scare people.  Deployment Manager seeks to walk this fine line, and does a descent job, but it is a very hard sell.  Alas, I digress.

I got tapped with only a few moments notice to lead a group discussion about Database Migrations — a topic I’m quite passionate about.  Of the 3 groups of 20-or-so, I chose the biggest challenge — the group that didn’t own any Red Gate tools.  We quickly got the standard gripe out of the way: the tools are expensive, and then we began exploring both the challenges of Database Migrations and the solutions that we were proposing.  (It was fun getting to use the royal “we” to discuss Red Gate for a few minutes.)  The methodology proposed seems very solid, and I’m really excited to see how they execute on this vision.  In a true continuous deployment system where the build server fully owns deploying all assets (web sites, command lines, services, GUI tools, and databases) the lynch pin to completely avoiding babysitting it is Database Migrations.  I’m really excited to see this product come to life.  The group seemed very receptive, and some said they’d give SQL Source Control a try.  (SQL Source Control is a great gateway drug into the Red Gate toolchest.)

SQL in the City is a wonderful event, and I really enjoyed attending again this year.  I’d highly recommend the free day of training and insight into Red Gate tools, and wish only that there were more of them in cities closer to me.  Many thank yous to Annabel and her team who went above and beyond to make this even a wonderful success.  Well done.

IIS Web Server Best Practices

Best practices I use when setting up IIS:

– Each site should be a separate (sub-)domain.  Thus instead of  This solves a few problems: “../../don’t/do/this.jpg” is avoided when you can presume the root of your site is “/”, which means you can avoid relative paths (even if you need not crawl up a directory) and you can avoid the ~.  It’s much cleaner all the way around.

– Because each site is its own (sub-)domain, you also avoid the pitfall of virtual directories.  A virtual directory is an app within an app.  ( and some changes to the outer app cascade into the inner app.  For example url rewrite rules, authentication, dll mapping, etc.  Basically the system starts at your app’s directory’s web.config, crawls up the folder stack layering behind every web.config it finds, layers behind that the web.config in the framework directory, and finally machine.config.  This is why you need not copy every changes in your regular web.config into the web.config in the Views folder in MVC.  Because a virtual directory is by definition an app inside another app, of necessity you’ll inherit the outer app’s web.config, potentially negatively impacting your app as their app evolves.

– The app pool is the execution space, so, each site should have its own.  That doesn’t completely protect you from another site blowing up your site, but it does help considerably.  Especially if a technician needs to recycle the app pool as part of an app upgrade or if only one site on the machine is having troubles.

– If you consistently change things on all sites like removing headers, configuring additional mime-types, rearranging or removing default document names, setting the error pages, etc, do these on the machine node in IIS rather than redundantly for each site. is a great tool for highlighting configuration errors and places where you’re exposing more information than necessary.

– Make sure you handle the “naked domain” (zone apex).  Mapping to is important, but users could just as easily hit (without the www) and if your site doesn’t handle both, a consistent portion of your users will consider your site “broken”.  For SEO purposes, permanently redirect one to the other.  (Which you choose is likely a matter of corporate culture or preference, and ultimately is irrelevant … provided you consistently choose it in IIS configurations, Google Webmaster Tools, etc.)

– If at all possible, run the apps in the most recent framework version, in “Integrated” mode, with 32-bit disabled, with modern .net frameworks installed on the box, and all Windows Updates.  Ideally you’ll also be on the most modern OS version too.  Your apps may need code changes to make this possible.  These are the defaults with new installs and are promoted as “modern techniques” [read: best practices], and ensuring your apps are compliant suggests future deployments to similar or newer hardware or OSs will be less traumatic.

– c:\inetpub\wwwroot\myapp is a very awful place to put your web site folder.  Because this is the default, if I’m a hacker trying to compromise your site and I find another way into your box (FTP, RDP, network share, etc) I can stick something in all such folders and compromise every site on the box.  Script kiddies have automated attack tools that can do this.  Instead, I create a folder like C:\Internet\ or C:\Websites\ or similar.  Inside, I have a folder for each site with sub-folders for:

  • db
  • logs
  • www

I’ll then put the IIS website content in the www folder, point IIS to put the site’s logs into the logs folder, (Ever crawl through IIS’s default log folder trying to figure out which log folder you want?  I’m sorry, “W3SVC6” is not sufficiently descriptive.) and if SQL Server is on the same machine (not best practice), point it to put the mdf and ldf files into the database folder.  Now when you want to backup “everything”, just stop IIS, stop SQL, and copy C:\Internet.  You got “everything” for all sites on the box (with the exception of C:\Windows\System32\inetsrv\config\applicationHost.config which you should also backup periodically.)

– applicationHost.config is not scary.  It’s the “Metabase” (using IIS 6’s term) and is just an xml file.  From time to time, IIS’s GUI has a wacky limitation or I want at it faster than that, so I just pop open C:\Windows\System32\inetsrv\config\applicationHost.config using an elevated notepad and hack away.  Want to setup the second server in the farm to exactly match the first?  Install IIS, install all the plugins you need via Web Platform Installer, run windows update a few times, then use Beyond Compare or WinMerge to diff old box’s applicationHost.config to new box’s, and copy across.  Ta-da!  Instant second box.  (BTW, don’t copy over the “encryption key” lines nor module or handler lines for plugins you didn’t install, and make careful backups before changing this file.  Either that, or you can get good at reformatting boxes.  I needed only make that mistake once.  :D)  Of particular interest in applicationHost.config is the <sites> node.

– One of the big challenges with IIS is “who owns the site’s web.config: The IT dept or developers?”  This is because site-specific changes made in IIS are stored in the app’s web.config.  (This is also why php and node apps hosted on windows have a web.config file even though they don’t use .net.) Alter the list of default documents or change the authentication scheme, and it’ll write these nodes into web.config.  On next deploy if you flush and reset everything — including web.config — you’ll remove these settings.  Oops.  (Also see the “if you do it on every site, do it to the box not to each site” note above.)

– The remote management service is incredibly cool.  Typically the only reason we RDP to the IIS machine is to use IIS Manager, run windows updates, or figure out why it ran out of hard drive space.  It is an increased attack surface to have the management service on, so perhaps configure the firewall to only expose port 8172 (or the one you configure) to the LAN and not to the public.  Now no more RDPing for config changes.

I’m confident this is hardly an exhaustive list, but based on these, you can get to a pretty good place in IIS, and probably get the Google-fu cranked up too.  Happy hosting!