Moving to SQL Azure is not a simple connection string change

Why?

Customers are every day more interested in moving workloads to the cloud and the ability to have hybrid scenarios is growing constantly, we see customers using ExpressRoute or VPNs to extend their data centers to the cloud. As one of the components of BizTalk360 is a SQL Server database we took the challenge of changing it to support the SQL Azure database PaaS offering. Since SQL Azure is a PaaS service we do not have the same level of control as with SQL Server on the underlying platform and only a subset of features are currently implemented.

How?

Since we are talking about SQL Server engines from Microsoft that share the same product team we could think that we could just simply change the connection string and point to a SQL Azure database. We maintain the BizTalk360 database in a SQL database project in our code base which helped with this process. We had to retarget this project to support Azure SQL Database, immediately a large number of compilation errors and warnings started to show.

What is supported?

Our intention was to make the BizTalk360 database to target both engines. Identifying what we were using that is not supported was the most important part of the equation.

Clustered Indexes

SQL Azure makes the existence of a clustered index mandatory. Some tables did not have clustered indexes in BizTalk360 and we had to create indexes making sure to not cause primary key violations during execution. This is critical to avoid data related and runtime bugs.

File groups

SQL Azure does not support file groups which we were using widely in the creation and upgrade scripts. We simply had to remove all file group references from the scripts since those are not required in SQL Server.

User management

We had some legacy stored procedures that were used for creating SQL users that since were not used could be dropped and that were not compatible with SQL Azure since they require access to some System Databases. With this, we concluded the schema changes in our database project but changes were not limited to that, we also need to change every other SQL Code interacting with the database.

SQL Bulk

SQL Bulk inserts are not supported in SQL Azure and we were using Bulk inserts in Event Log collection and Analytics to insert records fast and have a smooth behavior, we had in these two cases to implement an alternative by reverting to normal inserts when inserting records in those scenarios.

Installer changes

The first contact with a product is often the installer, so we need to maintain very high quality while at the same time make it easy to use for the customer. Connecting to an Azure SQL requires different input during setup to accept SQL Authentication and to generate the appropriate connection string.

SQL Code in the Assemblies and test coverage

SQL Statements initiated from the API and Services had to be fully tested to guarantee complete coverage and to identify any incompatible feature with SQL Azure. After the changes discussed above we found no additional problems and successfully completed this process.

Conclusion

Something that started as a simple discussion wondering if we could support SQL Azure for BizTalk360 (as part of our BizTalk360 in Azure Marketplace solution) resulted in extensive research on features and limitations and resulted in the expected outcome which is customers can seamlessly use BizTalk360 on SQL Server and SQL Azure. This will help customers with hybrid setups to be enabled to use more Cloud and have the choice.