Day 3 of Jump in Camp started roughly the same as Day 2 except that I managed to get a few extra hours of sleep before having breakfast at 7.30.

The first subject of the day was about porting SQL queries to T-SQL. Transact-SQL (T-SQL) is Microsoft’s proprietary extension to SQL and varies from the MySQL version of SQL which most open-source developers are used too. For information on the differences and how to convert MySQL to T-SQL Microsoft provide a useful guide to the most common issues developers will have.

The guide can be downloaded from Guide to Migrating from MySQL to SQL Server 2008 and as you can see there are a number of differences which need to be dealt with by the developer. Most issues can be dealt with and both MySQL and SQL Server can supported simultaneously as long as you are prepared to think about how you write SQL statements to take into account the nuances.

The other path open to developers is to use something like Doctrine which will abstract these differences away from the developer. SQL Server support has also added to Kohana and Lithium so if you use either of those frameworks, adding support for SQL Server should be much less painful.

Just as I started to have panic attacks on just how much work it would be to get osCommerce to support SQL Server we were shown a rather cool tool which is called SSMA (SQL Server Migration Assistant) this will take a MySQL database and will convert its data structure and also the data itself and convert it into a format which SQL Server can understand. I tested this tool against the current 3.x database structure which is being used in osCommerce and because we hadn’t really ever supported any databases apart from MySQL I expected a number of problems to be flagged up.

After running through the tool however I was pleased to see that the entire database structure and all of the sample data we provide was migrated to SQL-Server without a hitch. Kudos to the SSMA team for their hard work in making it light work for me! If you are interested in trying SSMA out you can download it from Microsoft SQL Server Migration Assistant 2008 for MySQL

Much discussion was had about how we could best support SQL Server in osCommerce and we decided that a new database layer was needed in order to provide support for all of the database platforms osCommerce will officially support and also allow extensibility so that other platforms could be added without needing to re-write large swathes of core code. This work is currently in progress and hopefully the fruits of our labour can be commited into the Github repository soon.

After lunch came a series of interesting talks about how to optimize the T-SQL once you had it working. Most developers these days should know about the EXPLAIN statement in MySQL which will tell you how MySQL has executed a SELECT statement.

SQL Server has a pretty awesome graphical tool which is built into SQL Server Management Studio and provides more than enough information to help developers decipher what is going on under the hood to get the best performance possible out of SQL Server. I won’t go into details on how to use the tools I have mentioned here but I will be blogging on the specifically very soon with a walkthrough on converting the current osCommerce 3.x database structure to SQL Server and also how to use the SQL Server Management Studio to discover how your T-SQL is performing.

After an afternoon of discussions and talks about optimisation of T-SQL and SQL Server in general it was time for us to prepare for a small road-trip to the Space Needle where we would be spending the evening having dinner and relaxing.

I think this had to be one of the highlights of the whole Jump in Camp as the view from the top was amazing. The food and drink in the Restaurant was also top notch and I couldn’t find fault with anything apart from the Wind which was absolutely freezing when out on the observation deck.

The evening was rounded off with some good debates in the hotel bar over a few beers about what interoperability really meant and of course some really good banter about getting a Linux version of the SQL Server driver ;–)

Was then time for sleep in readiness for the fun of Day 4