.Net Jonesie

Processing a SQL Batch

Peter — Tue, 27 Oct 2009 01:20:37 GMT

This is something I have wanted for a while now. Today I finally got around to doing something about it. I created a simple console application that can execute a SQL script file that you would normally use in Management Studio or SQLCMD. You might well ask why I would do such a thing.

SSMS and SQLCMD is not always available or in the right place
I don't have the code for SSMS and SQLCMD so I cant change the way they work

In you read my previous post on deploying to SQL Azure then the first point will make sense. The next step is to make this script processing code work in Azure so I don't have to create databases from my client. Hopefully this will speed things up a little or a LOT.

Anyways, since I've always wanted someone to write this code and never found any, here it is in case you are in the same boat.

class Program

{

    staticint Main(string[] args)

    {

      string connectionstring = args[0];

      string sql;

      try

      {

        using (FileStream fs = newFileStream(args[1], FileMode.Open, FileAccess.Read))

        {

          byte[] bytes = newbyte[fs.Length];

          fs.Read(bytes, 0, (int)fs.Length);

          sql = Encoding.UTF8.GetString(bytes);

        }

      }

      catch (Exception ex)

      {

        Console.WriteLine("Unable to read sql script: " + ex.Message);

        return 1;

      }

      string batchTerminator = @"##GO##";

      //Regex regex = new Regex(@"(^|\s+)" + batchTerminator + @"\s", RegexOptions.IgnoreCase | RegexOptions.Singleline | RegexOptions.Compiled);

      string[] strings = sql.Split(newstring[] {batchTerminator}, StringSplitOptions.None);

      using (SqlConnection con = newSqlConnection(connectionstring))

      {

        con.Open();

        SqlCommand cmd = newSqlCommand();

        cmd.CommandType = System.Data.CommandType.Text;

        cmd.Connection = con;

        for (int i = 0; i < strings.Length; i++)

        {

          Console.Title = "Processing " + i.ToString() + " of " + strings.Length.ToString();

          //if ((!regex.IsMatch(strings[i])) && (!string.IsNullOrEmpty(strings[i].Trim())))

          if (!string.IsNullOrEmpty(strings[i].Trim()))

          {

            Console.WriteLine(strings[i]);

            try

            {

              cmd.CommandText = strings[i];

              cmd.ExecuteNonQuery();

              Console.ForegroundColor = ConsoleColor.Green;

              Console.WriteLine("O.K.");

              Console.ForegroundColor = ConsoleColor.Gray;

            }

            catch (Exception ex)

            {

              Console.ForegroundColor = ConsoleColor.Red;

              Console.WriteLine("Error: " + ex.Message);

              Console.ForegroundColor = ConsoleColor.Gray;

              return 2;

            }

            Console.WriteLine();

            Console.WriteLine();

          }

        }

        return 0;

        //Console.WriteLine("Press ENTER when complete");

        //Console.ReadLine();

      }

    }

}

Ok, so it’s not the prettiest or most bullet proof code on the planet, but it works. The RegEx is the key to the process. It’s not perfect – it expects GO commands to separate the batches and you need to have GO on a line by itself. It’s easy to extend this should you wish. [Update] Regex don't work! It’s just too hard to find all the GO’s accurately. Instead, I manually search and replace Go with ##GO##. This is a lot easier/safer to split with. I also removed all PRINT statements from my scripts but you could easily parse these out when processing and have then display as progress messages (no need to send them to the server).

Enjoy!

Migrating and Partitioning a Database to SQL Azure

Peter — Sun, 25 Oct 2009 22:07:49 GMT

Moving data from one SQL Server to another is a fairly trivial task these days. You have many options – backup/restore the database, SSIS, detach/copy/attach, ADO.Net, BCP, blah blah blah. Moving data to SQL Azure is more challenging. Here is my experience and some details of the solution.

The Scenario

The application we are creating has a master application database and database per ‘client’. The per client database will be partitioned into many smaller databases – each being a replica of the source database with a filter on one or two tables. The creation of the partitions will require the client application to be shut down for a period of time – not ideal but acceptable.

SQL Azure does not (yet) include any support for partitioning or sharding so we knew we would have to create the solution ourselves.

Options

We wanted to have a flexible partitioning solution so from the outset I thought that SSIS would provide a mechanism that would allow the client to create their own partition rules as required. When I started creating the solution BCP was not available.

Trials and Tribulations

Before partitioning of the database could be performed it was necessary to move the source database to SQL Azure. Before moving the database it’s necessary to have a script that will create the empty database schema. When I first did this there where no tools so I used the database tools in VSTS to import from the local database and then manually tweaked the script to make it compatible with SQL Azure. These days there is the Migration Wizard on CodePlex which does pretty good (but not perfect) job of migrating your schema and optionally data.

Problem 0

Executing the schema script is very very slow. Locally it takes seconds. In SQL Azure it can take up to fifteen minutes. We have tested this from several connections (inside and outside our network and with different service providers) and it’s never been less than eight minutes. Our contacts at Microsoft can run the same script in three minutes. I have some ideas why this is slow and a possible solution… more on this later if I solve it… but for now this is a big problem – 15 x 42 (the number of partitions) = way too long!

It’s been a long time since I’ve had to do anything with SQL Server beyond creating databases for SharePoint or EPiServer and SSIS was completely new to me. However, I managed to piece together a package that copied the tables from one database to another. This package called other packages – 1 for each table – that would truncate the destination table and copy the records.

Problem 1

Because there we 30 or so packages I used xml package configuration files to specify the database connections so that I could change the source and destination in one place. When running the packages in Visual Studio this would constantly remind me that the connection had changed (when it hadn’t) so I had to turn this off. Not a big problem but annoying.

Problem 2

Running each separate table copy worked fine most of the time but running all of the packages in sequence (see problem 3) would fail in different places for various reasons. Sometimes I would get duplicate key errors – which was theoretically impossible.

Problem 3

All but two of the tables have identity columns. To move the data I had to SET IDENTITY_INSERT ON for the destination table. OLE DB Destination tasks in the dataflow let you specify identity inserts but these don't work in SQL Azure – you must currently use ADO.Net source and destinations tasks.

You can only have a single SET IDENTITY_INSERT ON per database so this means I had to run all these identity table packages sequentially which makes the whole process very slow.

Problem 4

Reliability. SSIS in Visual Studio seems to me to be very unreliable and flaky. I was plagued with performance issues and crashes.

In the end, frustration got the better of me and I gave up on SSIS. Thankfully, BCP for SQL Azure became available last week so that provided an option with more control and visibility. ADO.Net includes a SQLBulkCopy class. This will let you move data from many different sources to a database table. It provides a few options to control the behaviour of the operation and this seemed like the best option – the last time I used command line BCP was back in ‘95 with SQL Server 6.5 I think – or it might have been Sybase (which in those days was pretty much the same thing).

I whipped up a simple unit test and some classes to test SQLBulkCopy and it didn’t take long to realise that this wasn’t going to work either.

Problem 5

I kept getting errors about not having SET XACT_ABORT set ON. Binging for this led me down the DTC path. This is the same path that leads to hell! I couldn’t understand why DTC was getting involved because I had not invoked any transactions. I thought that BCP or my source data reader query was doing something funky so I just added the following SQLCommand query before I started the bulk copy:

SqlCommand xact = new SqlCommand("set xact_abort on", dst);
xact.ExecuteNonQuery();

This seemed to work, for a while.

(It turned out that the base class for my unit test was declaring a TransactionScope that wrapped everything in my unit test. I didn’t spot this until I have up with SQLBulkCopy so it may well be that this is not necessary now).

Problems 6, 7 & 8

I’m not exactly sure when I decided to give up on SQLBulkCopy but it was somewhere around the problems with timeouts, lockups and random results.

My options were now reduced to a short list of one – command line BCP. After a few false starts (I don’t read instructions as well as I could) I managed to get something working quite well.

Success! Moistly…

I created a batch file to test the various options with BCP. It’s really pretty simple to use but I wanted the simplest possible solution that would be easy to support and maintain. Using the native option (-n) provided this but it does mean that should and destination tables need to be almost identical. When creating the schema script I had re-ordered the columns on one table. This was enough to cause a problem for native mode which generated some cryptic errors. Thankfully the error file (-e option) provided enough clues to figure out what was wrong.

Throttling

SQL Azure will drop connections when/if there is a heavy load on a database. This it to enable high availability – which sounds silly – how can it be highly available if the connections keep dropping? – but in reality this just means you need to be prepared for a connection to drop and transactions to fail if the load on the database / server is exceeded.

Determining what the threshold is for throttling of the connections is difficult. So difficult in fact that one might say impossible. Throttling is triggered by activity by all users of a server. The server is also virtual (not as in a virtual machine but as in a virtual connection to one or more virtual machines).

My BCP commands on one table (the largest) would consistently cause the throttling to kick in. This table has about 230,000 rows – not huge by and stretch of the imagination. Thankfully, BCP allows to to specify the batch size and the first and last rows to use. For this table I found that 10,000 rows per batch worked most of the time and executing BCP on chucks of 50,000 records seems to have stopped the throttling. Using the first and last row options also provided the opportunity to retry a chuck of records should throttling kill the connection.

The final (so far) code for running command line BCP in a mode that SQL Azure can handle looks like this. This code is far from complete but should be sufficient to explain the process. Please feel free to adopt and enhance as you see fit.

private void copyTableWithCmdLineBCP(string sourceServer, string sourceDatabase,

string sourceTable, string sourceRule, string sourceUserName, string sourcePassword,

string destinationServer, string destinationDatabase, string destinationTable, string destinationUserName,

string destinationPassword, int rowsToCopy)

    {

      string ofile = Path.GetTempFileName();

      string sourceQ = getSourceQuery(sourceDatabase + ".dbo." + sourceTable, sourceRule);

      string countQ = getCountQuery(sourceDatabase + ".dbo." + sourceTable, sourceRule);

      string args;

      // export to a temp file

      if (sourceUserName == null)

      {

        // no user name so use trusted security

        args = string.Format("\"{0}\" queryout \"{1}\" -S {2} -T -n -E", sourceQ, ofile, sourceServer);

      }

      else

      {

        args = string.Format("\"{0}\" queryout \"{1}\" -S {2} -U {3} -P {4} -n -E",

                 sourceQ, ofile, sourceServer, sourceUserName, sourcePassword);

      }

      execProcessAndCheck(Properties.Settings.Default.BCPCMDPath, args, "Bulk copy export failed for table " +

                sourceTable);

      // import the file one chuck at a time

      for (int startRow = 1; startRow <= rowsToCopy; startRow += MAX_ROWS_PER_BATCH)

      {

        int tries = 0;

        if (destinationUserName == null)

        {

          // no user name so use trusted security

          args = string.Format("{0}.dbo.{1} in {2} -S {3} -T -n -b 10000 -E -F {4}",

            destinationDatabase, destinationTable, ofile, destinationServer, startRow);

        }

        else

        {

          args = string.Format("{0}.dbo.{1} in {2} -S {3} -U {4} -P {5} -n -b 10000 -E -F {6}",

            destinationDatabase, destinationTable, ofile, destinationServer, destinationUserName,

            destinationPassword, startRow);

        }

        int endRow = startRow + MAX_ROWS_PER_BATCH - 1;

        if (endRow < rowsToCopy)

        {

          args += " -L " + endRow.ToString();

        }

        // execute with retries

        while (!execProcessAndCheck(Properties.Settings.Default.BCPCMDPath, args,

           "Bulk copy import failed for table " + destinationTable) && ++tries <= MAX_RETRIES)

        {

          Logger.WriteLog(LogCategory.Error, "Operation failed due to connection failure. Will retry.");

          Thread.Sleep(WAIT_BETWEEN_RETRIES);

        }

      }

    }

This uses a couple of helper methods. getSourceQuery and getCountQuery simply return queries for a specified table name and ‘rule’ The rule is a string which defines how to partition the table. A switch statement is used to determine the WHERE clause for a table based on the table name and rule value.

execProcessAndCheck executes a command line with arguments and captures the output.

    privatebool execProcessAndCheck(string cmdLine, string args, string errorMessage)

    {

      _processOutput = newStringBuilder();

      ProcessStartInfo psi = newProcessStartInfo(cmdLine, args);

      psi.RedirectStandardError = true;

      psi.RedirectStandardOutput = true;

      psi.UseShellExecute = false;

      psi.CreateNoWindow = true;

      Process prs = newProcess();

      prs.OutputDataReceived += newDataReceivedEventHandler(prs_OutputDataReceived);

      prs.StartInfo = psi;

      prs.Start();

      prs.BeginOutputReadLine();

      if (prs != null)

      {

        prs.WaitForExit(MAX_PROCESS_TIME);

        if (!prs.HasExited)

        {

          prs.Kill();

          thrownewException(errorMessage + " - command has exceeded the maximum allowable time and was exterminated.");

        }

      }

      return checkProcessResults(prs, errorMessage);

    }

The output is captured by a simple event handler:

    void prs_OutputDataReceived(object sender, DataReceivedEventArgs e)

    {

      if (!string.IsNullOrEmpty(e.Data))

        _processOutput.AppendLine(e.Data);

    }

_processOutput is a class scoped StringBuilder.

The results from the process are checked by checkProcessResults:

    privatebool checkProcessResults(Process prs, string message)

    {

      if (prs.ExitCode != 0)

      {

        // look for error 10054 - this means that SQL Azure has throttled and we should retry

        string output = _processOutput.ToString();

        if (output.Contains("NativeError = 10054"))

        {

          returnfalse;

        }

        message += ": Error Code = " + prs.ExitCode.ToString() + " : Message = " + output;

        thrownewException(message);

      }

      returntrue;

    }

This is all very specify to the way BCP.exe and SQLCMD.exe work – if you want to use this for other command line tools then some tweaking will no doubt be required.

Summary

SQL Azure is an extremely powerful platform - despite the issues described above. It will definitely get better as the product gets closer to release. Most of my problems are caused by my recent inexperience with SQL tools and the general nature of an unfinished product. Learning how to do this stuff now will provide a better understanding of the realities of cloud based computing as opposed to hosting a SQL Server on some internet connected remote server.

Performance of the database creation, upload and partitioning could be enhanced in several ways. Threading could help enormously when creating the partition databases but throttling may reduce any benefit when it comes to uploading the data. Using multiple SQL Azure servers would also help a little. Moving the functionality into the cloud could be possible if SQLBulkCopy would work betterer and if I had a mechanism for creating the database schema other than SQLCmd.exe.

All of these options are possible given more time – but PDC09 is our hard deadline - so what I have so far will hopefully be good enough for a wicked kick-arse sexed-up demo of what is possible with this awesome platform! I’ll talk more about this application when I can… :)

Deploying and Load Balancing EPiServer 5.2

Peter — Mon, 28 Sep 2009 08:40:52 GMT

It’s been a while since I’ve blogged anything about EPiServer – 2 reasons - 1) I haven’t done an awful lot with it lately and 2) it’s just so easy it seems silly to blog about. However, over the last couple of weeks I’ve been helping out on a web site replacement project and one of my tasks was to install and configure the various servers – development, staging, UAT & production. So, me thinks it’s time to talk about it.

SuperDeploy

Normally EPiServer installation is performed manually using the EPiServer setup and Deployment Centre tools. However, for this project we attempted to script the entire process using PowerShell scripts. We have created a deployment tool with PowerShell – aptly named SuperDeploy – that we use for SharePoint installation and database creation. This is very useful for creating fully automated installations that we can use during development and when we need to install on the clients environment. So, it seemed natural that we should extend this to work with EPiServer. SuperDeploy uses a simple XML file to define what to install and where - the how is hard coded into PowerShell functions in the script.

Anyways, SuperDeploy works very well and provided a nice repeatable installation for all servers – up to a point. With a bit more work it could do everything but time is.. you know what, so some manual steps were needed to finish off the production server.

The final production installation had 2 web servers identically configured. The EPiServer site files were installed to a folder on F:\ and this folder was mirrored between the 2 servers using Windows DFS – so changes to files on one server were automatically and instantly copied to the other server – essentially meaning we had a single set of files for both servers. We could have had the files on a external drive array of some sort but this would mean a single point of failure. Replicating the files between servers provides an extra level of redundancy. The EPiServer virtual file system (VPP) was also replicated using the same DFS mechanism. The SQL 2005 database was on a third server and this was mirrored using log shipping to a forth server.

Load Balancing

I’ve come to notice over recent years than when something looks hard and not very well documented – it turns out to be obvious and simple. Such is the case with the configuration of EPiServer load balancing.

Documentation of the setup of EPiServer load balancing is hard to find. In fact the only documentation I could find was a single blog post. This worried me somewhat as there isn’t a lot of detail in this post and it wasn’t an official EPiServer document. But I needn’t have worried – 5 of the 6 steps were correct but step 4 is applicable to IIS 6, not IIS 7 which we were using. For IIS 7, httpModules must be specified in the <system.webserver> section of the web.config – not in <system.web>. You can also configure these in IIS Manager.

The setup of Microsoft NLB was already done for us and the tool linked to in the above post proved that the network was indeed setup correctly.

The hardest part was testing that the servers were actually load balanced. Fortunately – a mistake by me editing the IIS config meant that one of the sites would not run. Browsing from a test client machine gave me an HTTP 500 error from IIS. It was impossible to know which server was failing so we disabled one of the servers from the load balancing by turning off the NLB agent. When the client browser was able to see the site correctly we new a) which server was broken and b) NLB was working. Crude? Yes, but good enough. Some better testing is still required of course, but that's another story.

Even the smoothest installation can have surprises. When we ran the site, we were prompted to login to view the home page – which is strange as it’s a public web site with anonymous access enabled. We compared the production server configuration of IIS and web.config to the other servers and these were identical – we thought. In the end, we configured the IIS anonymous access user to be the application pool account rather than IUSR and the setup was done.

Morals of the story

EPiServer installation is easy – even for multiple load balanced servers. If you do have multiple server then ensure they are identically configured – Virtual Machines are a life saver! Mirroring or sharing the site installation folder will help with this (but it also means a mistake on 1 server will potentially be a mistake on all servers – instantly!).

Script your installation as much as possible. It make repeating your installation simple.

Install often and early – preferably as part of your development cycle. Don’t wait till the end of the development to test your deployment – YOU WILL REGRET IT – trust me! If you can, have your automated build cycle run your deployment script every night.

Be pragmatic. Perfect is great if you can afford it but customers and bosses are rarely willing to pay for this. Good enough is usually good enough!

Slides & Code from Azure Development Getting Started

Peter — Thu, 20 Aug 2009 02:00:33 GMT

Here’s the slides and code from my user group session last night – Getting Started with Azure Development.

I covered the basics of Web and Worker roles, storage and a bit of the service bus.

Use as you see fit.

Drizle.zip (3.53 MB)

WindowsAzureStartingUp.zip (229.92 KB)

Service Bus Version Weirdness

Peter — Thu, 23 Jul 2009 02:54:08 GMT

After posting about using TCP with the .Net Service Bus and making it look sooooo simple, I’ve lost most of today trying to get the fracking thing working! I doesn’t matter what I do in the config, the service host keeps prompting me for a Card (from CardSpace) even though I had everything setup for UserNamePassword creds.

After much stuffing around I noticed that my solution had the wrong version of the service bus DLL (0.15.0.0 – March CTP I think). This was my bad – I had copied the DLL to a solution folder. So I grabbed the '”new” version (0.16.7.0) from the July CTP Assemblies folder and tried that.

Same result! Arg!

Looking in the GAC I could see it had version 0.16.0.0. What the ?!!

Time for a reinstall me think.

Download from MS. Install. Chug chug….

Ok, now I have version 0.16.71.1. Right. Fine.

But the GAC still has 0.16.0.0 and my app still is broked. Lord - give me strength!

I re-added the project assembly reference to my local non gac copy and set copy local and now it all works again.

I can’t delete the old version from the GAC without uninstalling the SDK completely so it will have to stay there.

Weird! The Joy of CTP.

What is Azure – best explanation I’ve seen

Peter — Tue, 21 Jul 2009 23:27:25 GMT

Checkout this cool video if you want a 4 minute elevator pitch (ok, so a slow elevator).

http://blog.smarx.com/posts/what-is-windows-azure-a-hand-drawn-video

Azure .Net Service Bus & TCP

Peter — Tue, 21 Jul 2009 21:42:56 GMT

We recently implemented the .Net Service bus to expose some in-house WCF services to the world wide world. It may be useful for me and others if I describe how to do this :) This setup allows you to switch between tcp and http relay binding with configuration.

The Host

For the in-house systems you need to create a host – you cant use IIS as the service bus requires a connection to be initiated by both ends of the communication. We created a Windows Service application that also runs as a console app. This is much simpler when developing and debugging. To create this service:

1) create a new windows service using the standard VS template and add a reference to Microsoft.ServiceBus (in addition to your service and data contracts which I hope are in separate assemblies!)

2) change the Application Output Type to Console Applications.

3) add some code to Program.cs to flick to console mode:

if (args.Length > 0 && args[0].ToLower() == "/console")
{
myService.RunConsole(args);
}
else
{
ServiceBase.Run(ServicesToRun);
}

4) in your service code add the following method:

internal void RunConsole(string[] args)
{
OnStart(args);
Console.WriteLine("My ServiceHost is running... Press Enter to stop the service");
Console.ReadLine();
OnStop();
}

Now for the service bus stuff.

5) in the service class, add a member for the service host:

ServiceHost _host = null;

6) in the OnStart method of your service add the following :

// create a behavior for the service bus – it need creds of some type
TransportClientEndpointBehavior behavior = new TransportClientEndpointBehavior();
behavior.CredentialType = TransportClientCredentialType.UserNamePassword;
behavior.Credentials.UserName.UserName = Properties.Settings.Default.ServiceBusSolutionName;
behavior.Credentials.UserName.Password = Properties.Settings.Default.ServiceBusPassword;

// create an address for the service bus. config allows a switch between tcp and http
Uri address;

if(Properties.Settings.Default.ServiceBusBinding == "nettcp")
{
address = ServiceBusEnvironment.CreateServiceUri("sb", Properties.Settings.Default.ServiceBusSolutionName, Properties.Settings.Default.ServiceBusServiceName);
}
else
if(Properties.Settings.Default.ServiceBusBinding == "basichttp")
{
    address = ServiceBusEnvironment.CreateServiceUri("http", Properties.Settings.Default.ServiceBusSolutionName, Properties.Settings.Default.ServiceBusServiceName);
}
else
    throw new ArgumentException("Invalid binding for Service Bus");

// create the host
_host = new ServiceHost(typeof(MyService), address);

// update all the end points with the new behavior – you may need more than one – or not – up to you
foreach (ServiceEndpoint endpoint in _host.Description.Endpoints)
{
    endpoint.Behaviors.Add(behavior);
}

// open the portal to another dimension – a dimension of sight and sound
_host.Open();

Now for some config. For me this was the hardest part. Hard coding WCF config is much simpler!

7) create some service model bindings for netTcpRelayBinding & basicHttpRelayBinding

<bindings>
<netTcpRelayBinding>
    <binding name="default" />
</netTcpRelayBinding>
<basicHttpRelayBinding>
    <binding name="default">
      <security mode="None" />
    </binding>
</basicHttpRelayBinding>
</bindings>

8) add an endpoint for your service:

<endpoint name="MyServiceEndpoint"
          address=""
          binding="netTcpRelayBinding"
          contract="MyApp.ServiceContracts.IMyService"
          bindingConfiguration="default" />

9) if you want to use basic http then add this instead:

<endpoint name="MyServiceEndpoint"
                address= http://mysolution.servicebus.windows.net/MyService
                binding="basicHttpRelayBinding"
                contract="MyApp.ServiceContracts.IMyService"
                bindingConfiguration="default" />

And that’s about it for the service host. If you need to have metadata support (WSDL) then that’s a whole other story that I will try to blog about seperately.

The Client

Azure worker roles do not have a web or app .config you can use for WCF configuration settings. Web roles do have a web.config but this can only be changed by a redeploy of package so it’s not the best idea to put anything but service configuration in there. In my case, I needed to access the in-house services from both the web and worker roles so I created a class lib project with a single helper class.

1) create a Windows Class Library and add references for Microsoft.ServiceBus plus your service and data contracts.

2) Create a static class with a single static method – call it what you like but something like GetClientChannel() will do. Add the following code:

IMyChannel channel; // this is a simple combo interface of IChannel and IMyService

Uri address;

// create the behavior for SB creds
TransportClientEndpointBehavior behavior = new TransportClientEndpointBehavior();
behavior.CredentialType = TransportClientCredentialType.UserNamePassword;

// get the creds from the cloud config
behavior.Credentials.UserName.UserName = RoleManager.GetConfigurationSetting("ServiceBusSolutionName");
behavior.Credentials.UserName.Password = RoleManager.GetConfigurationSetting("ServiceBusPassword");

// create a channel factory

ChannelFactory<IMyChannel> channelFactory = new ChannelFactory<IMyChannel>();

// create the binding – config allows us to select http or tcp

if (RoleManager.GetConfigurationSetting("ServiceBusBinding").ToLower() == "basichttp")
{
channelFactory.Endpoint.Binding = new BasicHttpRelayBinding(EndToEndBasicHttpSecurityMode.None, RelayClientAuthenticationType.None);
address = ServiceBusEnvironment.CreateServiceUri("http", RoleManager.GetConfigurationSetting("ServiceBusSolutionName"), RoleManager.GetConfigurationSetting("ServiceBusServiceName"));
}
else if (RoleManager.GetConfigurationSetting("ServiceBusBinding").ToLower() == "nettcp")
{
channelFactory.Endpoint.Binding = new NetTcpRelayBinding(EndToEndSecurityMode.Transport, RelayClientAuthenticationType.None);
address = ServiceBusEnvironment.CreateServiceUri("sb", RoleManager.GetConfigurationSetting("ServiceBusSolutionName"), RoleManager.GetConfigurationSetting("ServiceBusServiceName"));
}
else
{
throw new ArgumentException("Invalid service bus binding configuration option: " + RoleManager.GetConfigurationSetting("ServiceBusBinding"));
}

// update the factory for A B & C

channelFactory.Endpoint.Address = new EndpointAddress(address);
channelFactory.Endpoint.Behaviors.Add(behavior);
channelFactory.Endpoint.Contract.ContractType = typeof(ICRMChannel);
channel = channelFactory.CreateChannel(new EndpointAddress(address));
channel.Open();
return channel;

Tada! All done. Provided I haven’t introduced any bugs then this should give you a connection to you in-house services, by passing any firewall crap that those pesky IT people seem to insist on.

Enjoy :)

A Twit for One Week

Peter — Fri, 17 Jul 2009 03:42:30 GMT

So far, so good. Twitter has not taken over my life. I’ve tweeted and chirped a little this week and have found a few colleagues to follow and vice versa. It’s also been a little useful and has helped me solve a couple of problems quicker than the usual methods (forums and web searches).

It’s interesting to see Twitter going main stream. When one of our sales guys asked which twitter client to use I thought ‘oh well, twitter is surely dead now!’ but then I read that CRM 5 will have a Twitter interface and I’m guessing (truly – no insider info anymore) we will see similar functionality in Office 2010. Google Wave has this too.

I’m not tempted to create the next killer twitter client yet. Tweetdeck works well enough for me at this stage but I can see that I will soon run out of screen real estate.

Best thing about twitter? It’s go me blogging again :)

PowerShell Love – Converting a comma delimited string to an array

Peter — Thu, 16 Jul 2009 23:33:27 GMT

PowerShell takes a while to learn – especially if you have an aversion to manuals as I do – I prefer to learn on the fly. So, when I wanted to convert a variable containing a comma delimited string into an array I was lost. After a bit of playing and ‘binging’ I found that it was actually very very easy:

# start with a string
$roles = "db_owner"

# now it’s an array of 1 element
$roles = ,$roles

Sweet!

PowerShell – Run a command and wait

Peter — Tue, 14 Jul 2009 22:39:11 GMT

I searched for a while to find an easy way to run a command from a PowerShell script and wait for it to finish. Most of the example I found use Invoke-Expression but this wont wait. In the end I resorted to the tried and true .Net Fx:

# run a command line and wait for it to be done
function RunAndWait([string] $command, [string] $arguments)
    {
    $proc = New-Object     System.Diagnostics.Process
    $proc.StartInfo.FileName = $command
    $proc.StartInfo.Arguments = $arguments
    $proc.Start()
    $proc.WaitForExit()
    }

You need to seperate the command (aka FileName) from any arguments, but otherwise this seems to work nicely.

However, PowerGui doesn’t always wait when you are debugging so be careful if you are stepping through your script and expect it to stop in logical places.

Installing .Net Service Bus on 2003 or XP

Peter — Wed, 08 Jul 2009 20:49:59 GMT

The July CTP of the .Net Services SDK only support 2008, Vista or Windows 7. This is true of the previous CTP’s but it was not enforced until the latest release. Luckily for me there is a simple workaround.

On a machine that does have the July CTP installed, take a look in C:\Program Files\Microsoft .NET Services SDK (July 2009 CTP)\Assemblies. There is a tool in here called RelayConfigurationInstaller.exe. Copy this and the Microsoft.ServiceBus.dll to your target machine and run the exe with /i. Then copy the DLL to the GAC and your done.

It appears as though RelayConfigurationInstaller simply adds the required machine.config settings on the target machine. You could also do this manually if required.

Of course, this is not officially supported but MSFT are listening. If you really need legacy OS support for this then post a message to the forums and ask for it before it’s too late.

Full Credit to Clemens Vasters for this tip.

I’ve given in and started to tweet

Peter — Wed, 08 Jul 2009 04:39:01 GMT

Having a blog is like having a nagging wife – not blogging makes me feel guilty. The thing is, I just don’t have the time to to write the type of blog posts I like to write – long and accurate. Not that I’m ever very accurate, but well …

Anyways, I’ve been ranting about how stupid twitter is and what a complete time waster it is without actually trying it. Then it occurred to me that twitter might actually be a useful alternative to long exhaustive blog posts.

So, I’ve signed up again and am tweeting a little about very development focused things. You wont catch me tweeting about the weather or Michael Jackson etc. Currently I’m into Azure dev so I’m finding lots of little things to chirp about. I haven’t found a lot of tweets to follow yet apart from the obvious candidates – scottgu etc – but I did get my first reply today when I sent a message to a Microsoft tweet. Very sweet!

Follow me if you love me :) jonesienz

Azure .Net Service Bus & Required permissions cannot be acquired

Peter — Mon, 15 Jun 2009 22:46:01 GMT

I’ve been doing quite a bit of fun work with Azure lately – and it’s great! – mostly :) It’s early days yet and there are a couple of (hundred?) things to sort out before they go live I guess. The main problem with anything new like this is the lack of documentation and real world expertise when you strike a problem. So, I thought I should blog solutions to the problems we have struck lately. Here’s a little one.

The .Net Service Bus allows you to expose in-house systems to the wide wide web without all that pesky firewall configuration. Essentially, it sites between the client and server and routes (WCF) messages. You can read all about it here. It only took us a morning to figure out how to configure our WCF’s but we had problems when it came time to deploy the web-role client to Azure.

Required permissions cannot be acquired.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Security.Policy.PolicyException: Required permissions cannot be acquired.

So, after some research, the solution was very simple. You just need to allow full trust for the web role which is achieved by setting the web role’s enableNativeCodeExecution to true in the ServiceDefinition.csdef file. See here for the full story.

MSDN On Speed

Peter — Mon, 13 Apr 2009 22:08:41 GMT

MSDN are testing a new version of the Library pages that are designed for low bandwidth connections. If you browse to any library page you will see a link under the breadcrumb, e.g:

This will display a vastly stripped down version of the usual pages.

You will be returned to normal mode if you navigate to any other library page or use the persist option at the top right of the page.

The speed of MSDN in this mode is great. The only thing I miss is search.

Enjoy!

My Name in Lights

Peter — Sat, 04 Apr 2009 19:49:09 GMT

Recently I wrote an article for http://devshaped.com. This was a lot of fun. So much so that I want to do more!

Visual Studio 2010 Drops WIX :(

Peter — Thu, 02 Apr 2009 19:56:27 GMT

I haven't used WIX a lot so I'm no expert, but I do know that is way better packaging solution than anything else that ships with Visual Studio currently. Version 3 of WIX had progressed to the point that MS were helping to have it included in the VS 2010 out of the box. Which is slightly ironic if you know the history of WIX.

However, it appears than plans and people have changed and WIX will NOT ship with VS2010.

This is very sad for us poor developers who are left with little out of box choices for solution packaging. Sure you will still be able to get WIX yourself but many shops don't like to let developers help themselves to open source tools and are even meaner when it comes to paying for tools. Inclusion of WIX with the VS2010 release would have moved WIX to the mainstream and finally put a nail in the coffin of VDPROJ packages.

For the full story, read here.

If you think this is a mistake then it may not be too late. Microsoft always listen to customers. If enough of us talk about this and express opinions then they may change their mind or at least offer WIX as a Power Tools or some such thing.

If you would like to have your say then either blog about this yourself or send an email to Soma (VP of Developer Division) via his blog: http://blogs.msdn.com/somasegar/contact.aspx

Bye Bye MVP

Peter — Fri, 27 Mar 2009 18:30:51 GMT

It's been a fantastic time being an MVP for the last 6 years. I've really enjoyed being involved in the community and doing what I can to spread the good work that Microsoft do. However, all good things come to an end. I've had a year off presenting and organising to concentrate on personal projects so have not been re-awarded this year.

I'm going to miss the comradeship of the fellow MVP's and the occasional 'secret' that we got to hear. No doubt I will lose my sanity again before too long and get involved in some community activities. I'll be back :)

Recession Conspiracy Theory

Peter — Fri, 27 Mar 2009 18:22:31 GMT

Here's my grand conspiracy theory. It's 2007 and you realise that your puppet Bush & co is not going to be in office forever and that wars eventually end. Your next pet President is a has-been who is unlikley to get power. Your oil producing, weapons manufacturing empire is under threat. What do you do?

Well, how about an old fashioned recession? Its worked in the past, right? You can make a few quick billion on the stock market and use that to invest in the bargains that will follow the crash. Rattle a few cages and the price of oil will go up. Make sure your President pardons a few friends and free's up some land for development before he goes. Then make a bit of a mess for the next do-gooder President to keep him busy enough so that he leaves you alone for a while.

Hey, this would make a great board game! Call it Global Domination or Get Richer Quicker. It could work like Monopoly, except you would start with 400 hotels and $12 Billion. The idea would be to steal as much money off the other players as you can by manipulating politicians and the media, buying up the hotels and killing off the employees.

It's very hard not to be cynical but I believe that the current global financial crisis has largely bypassed New Zealand - or it may just be that the IT industry is more sheltered from it than other industries.

I have plenty of work. I'm not worried about being laid-off any time soon. My employer is doing pretty well this year. Other IT companies I know have plenty of work. There are still lots of jobs available in IT. Basically, everything is pretty much the same as it always is.

The main difference I can see is that people are slower at paying bills and the news media are constantly reminding us of the doom and gloom.

Recessions are great for some people. There is a lot of money to be made for those that are prepared and there is no better way to be ready for a recession than to start one yourself! I have no doubt that this has been created for the benefit of a few individuals and companies. Fortunately, New Zealand is small enough to be ignored most of the time.

SharePoint Themes

Peter — Thu, 19 Mar 2009 22:06:15 GMT

If you are into styling SharePoint - and who isn't! - then you might find these useful: Ten Themes for SharePoint is VSeWSS Projects. I think there is a yellow one in there ... :)

MVC Release Candidaite 1 - an issue for me

Peter — Sun, 01 Feb 2009 20:42:02 GMT

Microsoft have release ASP.Net MVC RC 1 last week - twice. The first release was quickly refreshed to fix some issues so if you downloaded early last week, you may need to get the download again. Check here.

I have had a quick attempt to upgrade my 1 project and it all worked pretty sweetly and the upgrade process was very SIMPLE.

However. Several of my crud views use FckEditor for long text fields. A post-pack of these views triggers page validation:

A potentially dangerous Request.Form value was detected from the client

Normally, turing off page request validation in the page and / or web.config will fix this :

pages validateRequest = " false " >

but for me and others (see comments at Phil Haack link above) this does not help.

So, looks like I will be rolling back to the beta until this is resolved.

Update:

RTFB! http://haacked.com/archive/2009/02/07/take-charge-of-your-security.aspx. I haven't tried this yet, but it is almost certainly the solution.

New Web Platform Installer

Peter — Sun, 01 Feb 2009 20:12:23 GMT

Reading Paul a post on Hacked.com I saw mention of a new thing - the Web Platform Installer. This is a nifty tool that can be used to install the .Net Framework, ASP.Net, Visual Web Developer, ASP.MVC, Silverlight tools and other bits and peices. All from one very convienient and SIMPLE interface.

Using Virtual Machines for development these days I am often setting up new machines. This will save me a bit of time there. It's also great for helping friends and noobs get started with web development and will reduce the amount of time I spend on emails and awkward phone calls.

Well done Microsoft!

End of an era - so long Rod

Peter — Sun, 18 Jan 2009 08:19:54 GMT

It's sad but understandable that Rod has decided to through in the blogging towel. While I haven't been following a lot of bloggers lately, Rod's posts were always insightful and often inspiring and I always tried to keep up to date with his feed. You will be missed Rod - which seems impossible now that I say it.

MVC DropDownList and blank options

Peter — Wed, 14 Jan 2009 20:34:24 GMT

I'm loving MVC. In fact, it is my new love. SharePoint is out - that was a bad relationship anyway - too sadistic.

There are however, a few things in the current beta that require a workaround or two. Here's one.

To create a dropdown list in MVC you use the HtmlHelper thus:

<%= Html.DropDownList("CityID") %>

then in your controller you add the following before calling the view:

ViewData["CityID"] = new SelectList(from c
in someDataContext.Cities orderby c.Name select c, 


"CityID","Name", selectedCityID);

where selectedCityID is the item you want preselected in the list. Note that the ViewData key and the control name all match. And this just works. Easy as cake.

But now, suppose you want an option at the top of the list such as "[select a city]" when you are adding new records. Again, this is a peice of pie, just do this :

<%= Html.DropDownList("[select a city]",
"CityID") %>

The dropdown list will now contain this extra option at the top of the list of city, and it's value will be blank.

BUT.. the specified selectedvalue of selectedCityID will not be selected any more. If you look a the page source generated by the HtmlHelper you will see that none of the options has selected=selected.

I dont know if this is a bug and/or if it will be fixed by the release candidate (due any day) but as a workaround I have used the following jQuery code to pre-select the default option:

$(function() {

 
$("select[@name='CityID'] option[@value='<%= ViewData.Model.CityID%>']").attr('selected',
'selected');

});

Hope this helps someone else :)

Script Tag Madness - Blank Pages

Peter — Sun, 28 Dec 2008 03:57:44 GMT

I've been writing a small app using the latest ASP.Net MVC Beta and JQuery - for fun and for work - and got caught out by a problem I'm sure Ive had before.

When ever you have a script tag dont self close it like this:

< script type ="text/javascript" src =" Scripts/jquery-1.2.6.min.js " / >

For me this would stop all subsequent scripts from executing and sometimes result in a completly blank page. I had the same result in Firefox and IE 7. Instead, allways have a seperate end tag:

<scripttype="text/javascript"src="Scripts/jquery-1.2.6.min.js"></script>

Happy New Year :)