I’ve been enjoying the stability and extra performance of a 64 bit OS for about 2 years now so I am surprise and somewhat disappointed that the latest round of goodness from Microsoft – i.e. SharePoint 2010, Office 2010 and Visual Studio 2010 have some annoying incompatibilities in this mode.

Datasheet View of  a SharePoint list with a 64 bit version of Office does not work, you just get this annoying dialog:

Apparently this is because the ActiveX control is not available for 64 bit OS’s.  I have only tested on 2008 R2 server – Windows 7 64 bit may be ok.

Also, Intellitrace in Visual Studio – aka Historical Debugging - does not support SharePoint (or any 64 bit app?).

I’m sure Microsoft are aware of these limitations and will offer solutions in due course – but really, 64 bit has been coming for a while and I think we can expect not to see this sort of incompatibility, especially for Office. 

For me this has tarnished my experience only a tad, but overall, 64 bit everywhere is better than 32 bit in the same way that cake is better than two week old bagels.

The worlds most expensive hair dryer!

http://jonesie.net.nz/video/M2U00303.MPG

I’m sitting in a session at PDC 09 on SQL Azure futures.  Here’s some notes for me and anyone else who cares.

Backups and Clones

• Backups are coming.  Can now(?) create a clone of a database.  Something like CREATE DATABASE fred AS CLONE OF bob.

Operations

• API for provisioning – useful for SaaS providers.  Great for Multi-tenant too.
• Better deployment and upgrades.
• Data Sync.
• Upgrade and downgrade options between db sizes  (1 & 10 GB).

Scale-Out

• Woa!  TicketDirect mentioned again!
• Dynamic database splits.  And merge back of course.
• Additional db size options. Maybe up to 50 GB.
• Multiple db connections.
• Fan out query for multiple db’s.

Other

• Support for profiler
• Full Text Search
• CLR
• BI

Sensitive Data - Codename Vidalia – woosh – this went over my head.

SQL Azure will throttle your connections to protect resources in the data centre.  When SQL Azure does this is non-deterministic – or at least, Microsoft cannot specifically tell you when it will do this because there are many complicated factors. It could be the number of concurrent requests, the CPU or IO usages, the weather – who knows!  In the end it doesn’t really matter – you, THE DEVELOPER, must but be aware of this and deal with it.

SQL Throttling is manifested by a dropped connection.  This may appear as a Native Error 10054 when you try to open a connection or some other error if the connection is dropped after being opened.

To cope with this throttle a retry will normally be all that is required.  At the connection and command level this is easy but it can be complicated by unstructured code.  So, having a nice clean data layer will make the solution much easier to implement, but this applies equally to ‘messy’ code :)

We reused the Retry Policy mechanism that is provided with the Storage Client.  In the old sample storage client the syntax was a lot simpler but in the interest of less confusion I wont talk about that.  The new November SDK works just as well, but you need to do a tad more work.  The following sample is a connection manager that is used by the data layer.

using System;

using System.Data;

using System.Data.SqlClient;

using Microsoft.WindowsAzure.StorageClient;

 

namespace MyApp.DataLayer

{

  publicclassConnectionManager

  {

 

    ///<summary>

    /// Maximum number of retries

    ///</summary>

    privateconstint MAX_RETRIES = 3;

    ///<summary>

    /// The retry policy for operations that can be retried (sql etc)

    ///</summary>

 

    privateRetryPolicy RetryPolicy { get; set; }

 

    ///<summary>

    /// Wait 5 seconds between retries

    ///</summary>

    privateconstint WAIT_BETWEEN_RETRIES = 5000;

 

 

    public ConnectionManager()

    {

      this.RetryPolicy = RetryPolicies.Retry(MAX_RETRIES, TimeSpan.FromSeconds(5));

    }

 

 

    privateSqlConnection OpenConnection(string connectionString)

    {

      SqlConnection con = null;

      try

      {

        DieHard.RequestWithRetry(RetryPolicy, () =>

            {

 

              SqlConnection connection = newSqlConnection(connectionString);

              connection.Open();

              con = connection;

            });

        return con;

      }

      catch (Exception ex)

      {

        thrownewException("Error connecting to " + connectionString, ex);

      }

    }

  }

}

The interesting part is the DieHard helper class.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using Microsoft.WindowsAzure.StorageClient;

using System.Threading;

 

namespace MyApp.DataLayer

{

  publicstaticclassDieHard

  {

    publicstatic T RequestWithRetry<T>(RetryPolicy retryPolicy, Func<T> action)

    {

      ShouldRetry shouldRetry = retryPolicy();

 

      int retryCount = 0;

      while (true)

      {

        TimeSpan delay = TimeSpan.FromSeconds(0);

 

        try

        {

          return action();

        }

        catch (Exception ex)

        {

          if (!shouldRetry(retryCount, ex, out delay))

            throw;

        }

        retryCount++;

        Thread.Sleep(delay);

      }

    }

 

    publicstaticvoid RequestWithRetry(RetryPolicy retryPolicy, Action action)

    {

      ShouldRetry shouldRetry = retryPolicy();

 

      int retryCount = 0;

      while (true)

      {

        TimeSpan delay = TimeSpan.FromSeconds(0);

 

        try

        {

          action();

          return;

        }

        catch (Exception ex)

        {

          if (!shouldRetry(retryCount, ex, out delay))

            throw;

        }

 

        retryCount++;

        Thread.Sleep(delay);

      }

    }

  }

}

DieHard has 2 variants of the same Retry invoker – 1 for void actions and the other for a generic return value.

There is a couple of points to mention.  The timeout and the number of retries to attempt may vary depending on your situation and other factors – don’t use my 5 second 5 retries settings as the best possible option for you.  You need to be aware of transactions.  Retries should encapsulate the transaction as dropping a connection will rollback the transaction.  So, except for this example of retrying the open connection you should use the retry mechanism at the higher domain level.

Enjoy!

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. 

1. SSMS and SQLCMD is not always available or in the right place
2. 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!

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… :)

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!