Troubleshooting Slow BizTalk Admin Console

Since SQL Server is the backend storage of BizTalk, performance of SQL Server affects the rest of the BizTalk components. When the BizTalk Admin Console is running slowly

• Check the sizes of BizTalk databases
• Check the connectivity to the SQL Server
• Open a connection with SQL Server Management Studio see if it behaves the same
• Check for network connectivity
• Try out different protocol configurations
• Operation guys may comes in and update the SQL's port number to something rather than the default 1433 port leaving your BizTalk server untouched. The SQL client on the BizTalk server falls back to use Named Pipe after the TCP timeout (depends on the priority of protocol).
• Try set the Recovery Mode of all BizTalk databases from Full to Simple
• Not recommended in production environment
• You should know the implication. If you do not, please read MSDN.

PHP-Azure Migration - Part 2

In the previous post, I have discussed the database migration from MySQL to SQL Azure. Data migration is only the first part of data processing, the next step is to modify the queries in the web site due the the syntax differences between two database systems.

Driver Adaption
In the ASP.NET frontend, we only need to replace the ADO.NET driver from MySQL to SQL Server driver and it is all done. In the PHP CMS, however, we will need to do some adaptation since PHP did not have a unified API for database driver.

Lucky enough, it is not much work as there is a driver layer in the CMS. There were only three functions we need to change: query, fetch, total which corresponding to mysql_query, mysql_fetch_arry, and mysql_num_rows in MySQL driver and sqlsrv_query, sqlsrv_fetch_array, and sqlsrv_num_rows in SQL Server.

Query Modification
The major differences can be summarizes as follows:

1. Getting last inserted record identity
2. Paging
3. Scalar functions
4. Aggregation functions
5. Escaping strings

There is a DAO layer in the CMS that storing all queries.  So, all we did is go through all queries in the DAOs and modify them.

1. Getting last inserted record identity
MySQL PHP driver provides mysql_insert_id() to get the auto-increment column value of the last record inserted. There is no equivalent function in SQL Server PHP Driver. However, it is no more than just querying "@@IDENTITY" in SQL Server.

2. Paging
While doing paging in MySQL only takes "LIMIT n OFFSET skip", SQL Server will need

SELECT * FROM (
    SELECT TOP n * FROM (
        SELECT TOP z columns      -- (where z = n + skip)
        FROM table_name
        ORDER BY key ASC
    ) AS a ORDER BY key DESC
) AS b ORDER BY key ASC

3. Scalar functions
MySQL uses NOW() to get the current timestamp. SQL Server uses GETDATE().
MySQL provides RAND() so we could do random order. SQL Server provides NEWID(). Please note that NEWID() generate a GUID so the ordering is random. It does not mean NEWID() is same as RAND() in MySQL.

4. Aggregation functions
MySQL provides many convenient aggregation functions like GROUP_CONCAT(). The queries have to be rewritten for SQL Server or we need to create the corresponding user defined aggregation functions.

5. Escaping strings
Most developers use \' to escape single quote. In fact, both MySQL and SQL Server use two consecutive single quotes to escape a single quote. MySQL just accepting the other formats.

Up to this moment, we discussing the data layer (if you have a data layer in PHP). There is no code change in the business layer yet. This is also an example that how we could modularize a program if we partition layers clearly regardless which platform we are using.

PHP-Azure Migration - Part 1

Azure is Microsoft's cloud platform. The working models are different in a few ways. We migrated a web site to Azure. The web site consists of a PHP CMS, ASP.NET frontend, and a MySQL database.

Database Migration
There was some discussion among the team about whether using MySQL worker role or SQL Azure. As the final decision is SQL Azure, the first task we need to do is migrating the MySQL database to SQL Azure. The most obvious tools we can work on is Microsoft SQL Server Migration Assistant (SSMA) for MySQL.

SSMA was working fine in SQL Server. It reconstructed the SQL Server equivalent schema from MySQL and copied the data from MySQL to SQL Server. However, there are a few catches when migrating to SQL Azure:
1. Tables without primary keys:
For some reason, some tables in the web site did not have primary keys. The problem is a table in SQL Azure need a clustered index. No primary key means no clustered index (in SSMA sense). We have used a development SQL Server to re-create the schema first. Then, look for the tables without primary keys and add the primary keys back.

2. Large records
The CMS designer has a concept that all data should be put into the database as the backup strategies. So, everything including images files, Flash video files, and video files were stored in some attachment table as well. SSMA could handle most of them but not all. However, when the record is large (say 10MB), it will eventually failed and interrupted the how migration process. It is a very traditional timeout problem. We are just dealing with two:

1. Command timeout for SQL Server
2. Idle timeout for MySQL

Since we do not have control for timeout configuration and error handling strategies, we skip those tables during the migration. We dumped out the attachment into file systems. After a few tuning in the upload and download script to make the CMS working, we create a PHP script to migrate only the metadata columns in the attachment tables.

To conclude, the migration were performed in a two steps:

1. Execute the SQL script to create the tables
2. SSMA to copy most data
3. Execute the PHP script to migrate the attachment metadata

The performance was quite satisfactory.

Sorting NULL LAST in LINQ-to-SQL

There might be some time that you need to put NULL value into last when sorting a result set. In SQL Server, you can do something like this:

SELECT value
FROM foo
ORDER BY (CASE WHEN x IS NULL THEN 1 ELSE 0 END), x

You might want to ask the mapping of LINQ-to-SQL. Here you go:

foo.OrderBy(i => i.x == null ? 1 : 0)
   .ThenBy(i => i.x);