Month: January 2014

Script-based promotions in SQL Server

This is one of those “well duh” things that I SHOULD remember and have it burned in my brain – but then again, I could TOTALLY see me three months from now going, ‘how the hell did I get that to work again?’

It’s not too hard to move a smaller database from QA to PROD (for example) using only scripts. Here’s a low-impact way of doing this:

  1. Go into SQL Server and right click on the database. Select Tasks -> Generate scripts.

  2. In the Advanced button, under Advanced Scripting Options select “Schema only”.
    1. At this point you may also want to turn off descriptive headers too (since I find them annoying – honestly don’t I KNOW what a “DROP DATABASE” statement does?) – but it’s not necessary.
    2. I usually don’t select the DROP AND CREATE option since it creates much messier SQL. We’re going on the assumption that you’re populating an empty database, from scratch.

  3. Select the “Save to new query window” option. Click next and watch it populate away.
  4. Follow the same steps as above, only this time for Data only. Save each of these since you’ll want to come back to these. Call the first one “Database Creation Scripts – Schema Only” and the second one “… – Data Only.sql”.
  5. OK, see all that fancy crap in the header creating the database? You don’t need this and it’ll just get in the way. Blow it away – right down to where the stored procedures start dropping in.
  6. Go to your target SQL database, and manually create an empty database. Set up permissions as you’d like it (including creating any necessary users and passwords. You are using a strong password on prod no?)
  7. An interesting bug here. See that “CREATE SCHEMA” statement? Make sure this is run manually, first… otherwise SQL will try executing the entire script as a batch (in no particular order, in parallel) – and upchuck because it won’t find the schema. This is one step you definitely want to run as part of a series.
  8. Run the entire CREATE script. If this works – and it should – then run the data creation script. Easy peasy. You may want to think about kicking off the data creation script locally on the box versus using SSMS and a remote location – just for the sake of throughput times.

This is not too shabby a way of populating a database if you don’t have SSIS set up or just want a quick and dirty way of moving tables around. I’ve done this with Azure and as my British friends would say – it works a treat.

Another little gotcha – if you add your user, and set up permissions correctly (I typically set the user account up with ddladmin, datareader, datawriter access vs individual EXEC perms on sprocs only – for EF purposes) – you may still have connection issues. The new SQL account shows up great, all looks good – but you can’t log into SSMS locally using that account or using your app. Simple fix in some cases – just right click on the server and make sure its set up for Mixed mode authentication. You may have to restart, but this will give you the application access you need.


What I’m into at the moment…

What I’m thinking about: The Oregon coast. I love the elder moss-covered trees bending over the current.Image

What I’m listening to: A lot of Metric, Damien Jurado, Beth Orton, and the Raconteurs. The Black Keyes. The Blue Foundation, Bobby Bland, KT Tunstall. Bless you, Pandora.

What I’m watching: Doc Martin, from Britain. You have to love a grumpy guy who says what everybody else is thinking. He’s also a horrible human being who’s suffering from arrested development (another great show.)

What I’m reading: A lot of John Gierach, who never gets old. John’s best when read aloud by a fire, or while in a car to your family. I can’t get over how natural and fluid his writing is.

What I’m doing: Trying to work out about 3-4 times a week, and eating healthy. (Mixed success on both fronts). And I’m really enjoying getting back in touch with my inner nerd after four years of project management. Development = creativity = solving problems == bliss. I’m not saying I’d do it for FREE, you understand, but it doesn’t really feel like work at this point.


The bloody arena – Elmah and custom error pages.


Let’s start with a great quote from TR Roosevelt – a man who knew the value of a little drama:

It is not the critic who counts; not the man who points out how the strong man stumbles, or where the doer of deeds could have done them better. The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood; who strives valiantly; who errs, who comes short again and again, because there is no effort without error and shortcoming; but who does actually strive to do the deeds; who knows great enthusiasms, the great devotions; who spends himself in a worthy cause; who at the best knows in the end the triumph of high achievement, and who at the worst, if he fails, at least fails while daring greatly, so that his place shall never be with those cold and timid souls who neither know victory nor defeat.

  • T.R. Roosevelt

Ties in well with Plato’s “Be kind, for everyone you meet is fighting a great battle”, no?

Elmah – the courageous “little” error logging handler that could

Kind of a jump but let’s skip from the bloody arena to the even bloodier arena of exception handling. Scott H has been very vocal in how unfair it is on the amount of attention ELMAH hasn’t gotten. And the excellent CodingHorror site (GOD bless you sirs!) notes the compelling arguments behind exception driven development – where teams use exception logs as a defacto to-do list, checking them almost hourly:

  • 5-10% of application code is devoted to error handling. That’s significant – it’s not so “little” in terms of impact – and by its nature often isn’t reliably tested. So, the golden rule is –when a problem occurs in your application, always check first that the error was handled appropriately. If it wasn’t, always fix the handling code first. Always fix error handling before fixing errors. It’s like the airline rule of putting an oxygen mask on yourself before putting one on your child.
  • Broad-based trend analysis of error reporting data shows that 80% of customer issues can be solved by fixing 20% of the top-reported bugs. Even addressing 1% of the top bugs would address 50% of the customer issues. The same analysis results are generally true on a company-by-company basis too.
  • If I fix a bug that no actual user will ever encounter, what have I actually fixed? While I love TDD, as a bug-fixing mechanism its too much like premature optimization… I’d rather fix bugs that are problems in practice rather than theory. (Hmmmmm.) Bug-driven development has the inherent shortcoming of being hard to test/reproduce and crisis-driven. Like the guy with the leaky roof – when its raining, you can’t fix it because its raining out, and when it isn’t raining, there’s no leak!
  • Exception logs are possibly the most powerful form of feedback your customers can give you. It’s feedback based on shipping software that you don’t have to ask or cajole users to give you. Nor do you have to interpret your users’ weird, semi-coherent ramblings about what the problems are. The actual problems, with stack traces and dumps, are collected for you, automatically and silently. Exception logs are the ultimate in customer feedback.


Note that Scott’s blog, normally oh-so-reliable, only shows part of the steps you’ll need to get Elmah up and running for your app. Microsoft (in a rare exception) actually has a good post on start-to-finish implementation of Elmah.

Basically we’ll be following these steps:

  1. Download ELMAH and add Elmah.dll to your web app.
  2. Register ELMAH’s HTTP Modules and Handler in web.config
  3. Specify config options
  4. Create error log source infrastructure


Let’s get started. Go into NuGet and select ELMAH:



Let’s check our Web.config. What changes do we see?

A new HTTP handler has been configured in your application for consulting the

error log and its feeds. It is reachable at elmah.axd under your application

root. If, for example, your application is deployed at,

the URL for ELMAH would be You can, of

course, change this path in your application’s configuration file.


If I was to look in packages.config I see the following two lines (and a new “Elmah” node under References):

targetFramework=net45 />

targetFramework=net45 />


So far so good. Let’s get this actually hooked up to our app. I pulled the latest source code from the code site for Google and opened up SqlServer.sql, and ran the footer of the script on my local database instance. This created one table and three sprocs – nice and neat:

  • Elmah_Error (table)
  • Elmah_GetErrorXml (sproc)
  • Elmah_GetErrorsXml (sproc)
  • Elmah_LogError (sproc)


Now I add the following to web.config:




<!– For more information on Entity Framework configuration, visit –>

type=System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089
requirePermission=false />




type=Elmah.SecuritySectionHandler, Elmah/>

type=Elmah.ErrorLogSectionHandler, Elmah />

type=Elmah.ErrorMailSectionHandler, Elmah />

type=Elmah.ErrorFilterSectionHandler, Elmah/>






To the location path where I set up folder-level rolebased security, I added this node:


<!– AND lock down our exception logging page –>




roles=Admin />

users=* />







… and right above system.web I added this node


<!– This must be on same level as <system.web> nod. NOTE – allowRemoteAccess means its visible remotely, a potential security risk

dev points to our database instance –>


allowRemoteAccess=1 />

type=Elmah.SqlErrorLog, Elmah
connectionStringName=dev />


subject=Perceptive Site Runtime Error

async=true />


<!– to filter



<equal binding=”HttpStatusCode” value=”404″ type=”Int32″ />


</errorFilter> –>






Under <system.webserver> I added the following:


validateIntegratedModeConfiguration=false />



name=FormsAuthenticationModule />

type=Elmah.ErrorLogModule, Elmah
preCondition=managedHandler />

type=Elmah.ErrorLogModule, Elmah
preCondition=managedHandler />

preCondition=managedHandler />



name=ExtensionlessUrlHandler-Integrated-4.0 />

name=OPTIONSVerbHandler />

name=TRACEVerbHandler />

preCondition=integratedMode,runtimeVersionv4.0 />

<!– DH added the following –>

type=Elmah.ErrorLogPageFactory, Elmah
preCondition=integratedMode />



Above, if this was targeting an IIS6 webserver it would be under HttpHandlers and HttpModules.

I run the project and get a very … not too informative page. Let’s try to throw an error, shall we? Append a “/test” at the end of your elmah.axd call and then refresh – and you’ll see the following:


Clicking on the details gives us that treasured “yellow screen of death” that provides such awesome information in capturing and reproducing issues.

Let’s generate a few more just to be on the safe side. I add a few lines to my forms, one generating a DivideByZeroException within a try/catch block, the other straight up (where a naughty programmer forgot to wrap his logic in try/catch).

I get the above screen, the famed Yellow Screen of Death – and pointing the browser to elmah.axd gives me all the generated error messages. I can view them in the new ELMAH_Error table in SQL Server, and subscribe to it via a RSS feed. Very nifty!

Here’s the Catch logic I used to call the newer form of the elmah API:

catch(Exception ex)


//older API

//Elmah.ErrorLog.GetDefault(HttpContext.Current).Log(new Elmah.Error(ex));

//newer API

//This applies ELMAH filtering rules to exception, is subscription based (i.e. multi logger enabled)



Adding a Custom Error Page

Note that all we’ve done is trap application exceptions in one handy location. So, how do we trap these so the user is presented with a better, more friendly experience when the unexpected occurs?

Scott Mitchell put it best – “Every web application should have a custom error page. It provides a more professional-looking alternative to the Runtime Error YSOD, it is easy to create, and configuring the application to use the custom error page takes only a few moments.” Fair enough, and I WHOLEHEARTEDLY agree. So, how to add this to my app?

As my five year old would say, “Easy-peasy!” Add a new page to your application called “ErrorForm” – and link to a master page so its nice and spiffy. The code couldn’t be simpler:




<p>Looks like we’re having problems now with the application. Our site admins have been alerted: please follow the link below to go back to Home.</p>



NavigateUrl=”~/Default.aspx”>Return to the homepage</asp:HyperLink>






And add the following to your web.config:

<!– Remoteonly should be our default setting for local debugging on dev, Off to show explicit error messages outside of elmah –>

defaultRedirect=~/ErrorForm.aspx />




More information on error filtering – i.e. only throwing “top 20” errors like 404 etc – is found here: There’s some great information on ELMAH here –


This doesn’t mean that TDD is a waste of time and test projects are a brake on development speed. In our project, we’re remarkably weak still in not having a test project – that’s basic, and I pity the fool that doesn’t have “time” to write unit tests! This will be addressed over the next two weeks – it’s like not taking the time to buckle a seat belt before getting in and driving. Regression errors are starting to drive us crazy, and TDD/BDD or some form of this is the standard answer. That being said – THANKS ELMAH for making my life that much easier and not having to write some custom library to capture and log errors. This kind of direct feedback from the user community is too valuable to let go to waste.

And, side note, I’m loving GIMP for image editing. Cheaper than Photoshop, and much more functional than Paint.NET or Picasa. Yay open source!!!

TCP/IP vs Named Pipes Connections in Your Application

Recently faced an issue in connectivity where the SQL box was taking 5 seconds to do basic things like opening up a connection, etc – one fix proposed was to change our connection from TCP/IP to Named Pipes. Was this a correct choice?

This blog article echoed the statement from Books Online: Generally, TCP/IP is preferred in a slow LAN, WAN, or dial-up network, whereas named pipes can be a better choice when network speed is not the issue, as it offers more functionality, ease of use, and configuration options.” The author noted issues where the application would periodically disconnect from the database; and unlike TCP/IP, the app wouldn’t fail gracefully or attempt to reconnect. Named Pipes doesn’t support Kerberos, only NTLM.

This article also favored TCP/IP over Named Pipes for these reasons:

  • For small to moderate amounts of database traffic that aren’t heavily saturated, it doesn’t matter
  • Where connectivity is strained or there’s additional routing overhead (i.e. VPN) then TCP/IP has the advantage – esp where applications are chatty.
  • Named Pipes connectivity always uses more packets to get the same amount of work done with remote clients.
  • Named Pipes has a significant advantage when running an IIS app on the same box as your SQL backend (?!?!)


More WebDeploy adventures in Visual Studio…

Here’s a more coherent chain of events for setting up WebDeploy:

  1. Set up IIS (in Windows 8 this is done thru the “Add roles and features” in the Dashboard part of Server Manager. Very nice!) Also make sure ALL the features for .NET Framework 4.5 are set up.
    1. While you’re there, use Search to find inetmgr and add it to your Start/taskbar.
  2. Open up IIS and R-click once you expand Sites\Default Web Site to the wwwroot folder (typically c:\inetpub\wwwroot).
    1. Create a folder named after your website.
    2. Back in IIS, R-click on the folder name under Default Web Site and select Convert to Application.
  3. Note – it’s at this point that I hit an issue with the overly strict security settings in IE on the Windows Server 2012 QA/PROD boxes. So, to get around this, we abandon crappy IE and cut over to Google!
    1. Open up IE tools menu and add and to the list of Trusted Sites.
    2. Go to and download Chrome. Ah, THAT’S better! So long IE!!!!
  4. Download the Web Platform Installer (this should be a part of IIS; it usually will prompt you to run it if you open up inetmgr from scratch).
  5. In the Web Platform Installer I made sure – in the Frameworks tab – that the ASP.NET MVC 4 was set up (along with .NET Framework 4.5):

  6. I also made sure that the IIS Recommended Configuration and the Web Deploy 3.5 options were set up:

  7. If you’re prompted above for Web Deploy 3.5, the Typical configuration is fine.
  8. Time to doublecheck. Open up Control Panel, Administrative Tools – and make sure that Web Deployment Agent Service is running.
  9. If you open up your new {websitename} folder under wwwroot, you’ll also see that IIS_IUSRS for the local machine has read/write privileges. Excellent!
  10. Now back into Visual Studio, and select Publish. Give your profile a name…


The Databases section is interesting – here’s where you can replace on publish your connection strings with the correct database entry for QA /PROD, and test connection strings.


  • Note that if you’ve set up IIS before installing ASP.NET Framework 4.5, you MAY need to run aspnet_regiis to register the framework with IIS. (I didn’t have to do that). If you come across this issue, check some of the notes here.
  • You also want to make sure in the Settings section that you select the checkmark to Delete Additional Files upon Publish. In old-school x-copy deployment terms, this means the same thing as deleting the destination (target) folder before you copy over files.

Found some good notes here on setting up IIS on a completely blank IIS server: It really is (relatively) painless compared to what it used to be; typically your issues will be insufficient permissions on the local machine/network to handle remote connections.