To The Cloud: Moving Red Gate Tools to SQL Azure

Red Gate make a lot of tools for SQL Server. Two of our most popular are SQL Compare, which compares and synchronizes schemas, and SQL Data Compare, which does the same for data.

Well over a year ago now, when we first got our hands on SQL Azure, we did the eager, mindlessly optimistic, and utterly predictable thing: “Let’s point SQL Compare at it, and see what happens!” It didn’t work, and that’s not massively surprising. More surprising, perhaps, is how simple it was to make it work.

None of the developers I spoke to who’d worked on one of our Azure projects had anything really bad to say about SQL Azure, or could recall anything big, hairy, and challenging.

So what was there? What did we do with Azure, and what did we learn from it?

We’ve built six Azure-compatible products to date: thee existing tools now support SQL Azure, and there are three small new widgets for working with it.

  • SQL Compare and SQL Data Compare 8 and onwards now support migrating changes to, from, and between SQL Azure databases
  • SQL Prompt 5.1 can connect to an Azure database and offer basic intellisense.

The new bits are largely spun out of frustrations we’ve had ourselves:

  • CloudTally is a billing notification service
  • SQL Azure Backup does what it says on the tin
  • Query Anywhere is a Down Tools Week project, a browser-based SQL editor – SSMS light for Azure

I talked to the guys who built these, and the story each time was about most of the effort being in the edge cases, the UI, and around a few recurring issues I’ll tell you about.

Updating Apps For Azure

Getting SQL Compare and SQL Data Compare up and running for Azure began as a user request. Emmanuel Huna asked us to do it on our forums, and after a bit of back and forth, we got an early access build together. The project took about a month – two sprints, which is significantly less than any previous SQL Server version compatibility update. In fact, it took less time than developing some of the smaller SQL Compare features; object filtering, for example.

The main initial sources of confusion for the team were all around the way of working – you’ve got to be careful about how you create databases and move data around, because doing things in Azure costs money.

Debugging Azure Apps For As Little As Possible

It’s true that payment and setup are more admin problems than a development ones, but they did require some tweaks. The standard testing setup we use for our comparison tools creates and drops databases all over the place, and that’s going to get expensive pretty fast. So we had to re-architect a bit; first off so the automated tests didn’t spend all our money, and secondly so they didn’t rely on restoring backups (an impossibility under SQL Azure).

Unit tests were configured to run under each developer’s own Azure account, thereby making the most of the free allowances, and the big overnight integration tests we have set up for SQL Compare were moved over to relying on creation scripts. None of this is difficult, but it leaves you with a slightly disquieting feeling that anything you do might suddenly and unexpectedly cost you money. It’s an odd experience when you’re a developer used to throwing stuff around in a sandbox. Which is basically why we built CloudTally. Of course, once we’ve got SQL Compare working in Azure, we can just develop locally, and migrate. But we had to build it first.

The body of the work in getting SQL Compare and Data Compare to work with SQL Azure fell into two areas: accounting for the differences between Microsoft’s desktop and Azure flavours of T-SQL, and sorting out connection resilience.

It’s also worth noting that the SQL Azure update schedule is a lot more rapid than for on-premises SQL Server. This can really bite you, as Richard describes on his blog

T-SQL : Azure vs Desktop

The developer to take the first crack at Azure-ing up the comparison engine assures me that under the hood we’re basically looking at SQL Server 2008 with some features chopped off, and some object types missing. So you don’t have:

  • Application Roles
  • Assemblies
  • Asymmetric Keys
  • Certificates
  • Contracts
  • Defaults
  • Event Notifications
  • Full Text Catalogs
  • Message Types
  • Partition Functions
  • Partition Schemes
  • Queues
  • Application Roles
  • Routes
  • Rules
  • Services
  • Service Bindings
  • Extended Stored Procedures
  • Numbered Stored Procedures
  • Symmetric Keys
  • Remote Synonyms
  • User-Defined Types
  • XML Schema Collections
  • Common Language Runtime (CLR) User-Defined Types
  • Large User-Defined Types (UDTs)

Some of the more administrative features are out, too, for example:

  • Backups
  • Database mirroring
  • Distributed queries
  • Distributed transactions
  • Filegroup management
  • Global temporary tables
  • SQL Server configuration options
  • SQL Server Service Broker
  • System tables
  • Trace Flags

That sounds like a lot, and we’ve summarised some of this information on our website. It’s covered in the Microsoft documentation, too:

One thing that really caught us out is that tables now require a clustered index before you can insert data. Because SQL Compare often drops indexes at the beginning of a synchronization, and re-creates them at the end, this meant that for tables with data, our scripts would fail. So we had to add a few more table rebuilds than we typically like.

In the case of object types that no longer exist, there’s not a lot we can do. SQL Compare can’t create them if they’re not supported, so we just provide a warning and check for dependencies. For the syntax that’s different, we had to go through it on a case by case basis. The real issue was where information we were used to depending on was missing.

So, for example, SQL Compare needs to know a database’s compatibility level, and uses sp_dbcmptlevel to get it. This isn’t available in SQL Azure. But (and, boringly, this has been our Azure experience in microcosm) we didn’t have to do anything very complex or interesting to deal with it. Since SQL Azure is at compatibility level 100 we just needed to make sure SQL Compare determines the SQL Server version first. It didn’t take a lot, but it’s typical of the little tweaks we had to make all over the place.

Connection Resilience

The next issue was connections. Everybody complained about this, and there are two issues: connecting to multiple databases, and transient connection errors.

Under the desktop editions of SQL Server, you connect to a server, and can access as many databases as you have permissions for. Under SQL Azure, you connect to one database at a time, and USE statements just aren’t going to happen. Usually, when comparing databases, SQL Compare will query the Master database for login details. But under Azure you can’t chop and change the database you’re connected to. This gave us a bit of a headache.

In the end, the solution wasn’t elegant. SQL Compare just tries to create logins regardless, and to fail nicely if they already exist. Is it pretty? Frankly, no, but we’ve had surprisingly few error reports, and this approach did let us ship quickly.

Transient connectivity issues and resilience, by contrast, we just had to deal with. Microsoft is well aware of these issues with Azure. It’s documented, and they’ve even produced a “Transient Conditions Handling Framework” to help developers build resilience into their applications. But even using it, the error reports came in for connection issues. We knew, because one of the first things we did was hook in SmartAssembly, our detailed error reporting tool.

It tells us precisely where users are hitting issues, and they were hitting them around connection resilience. For example, with some connection interruptions, the early versions of SQL Data Compare would transactionally roll back an entire synchronization, and for a lot of data, that’s decidedly less than ideal. So we had to implement some of our own logic to cover more cases.

So, while executing the synchronization scripts, we keep track of where the current transaction began, and if we get an Exception which indicates a temporary problem (such as 40501 in the Microsoft documentation) we go back to the start of the failed transaction and start again. If we get disconnected 5 times in the same transaction we stop trying. Essentially, we keep going in the face of disconnections as long as we are still making progress.

Doing more for SQL Azure

Once we’d got the comparison tools up and running, we could tackle the other issue everybody had raised: backups. Microsoft offers the ability to copy an Azure database, and plenty of assurance about failover, but there are plenty of cases in which you want a backup too. Or, as Richard, who wrote our SQL Azure backup tool put it, after some frustration hosting websites on Azure: “To be honest, stuff it – I just want a backup solution!”

Here it is:

SQL Azure Backup in Action

Richard brushed up against the mandatory clustered indexes, and some problems with deployment being slow from Visual Studio, but otherwise he had the prototype up and running in about half a day. SQL Azure Backup uses the SQL Data Compare engine to copy your data down to a new local database, so it was very nearly just a case of wrapping a UI around the existing SDK. The main gotcha was matching the new local database to the properties of the SQL Azure one.

For instance, in SQL Azure the default collation is SQL_LATIN1_GENERAL_CP1_CI_AS, and collation can only be set at column and expression level. There’s no server level or database level collation. So when SQL Azure Backup first tried to create a database on a local SQL Server instance, it would fail if the collations didn’t match. This isn’t much of a problem – the local database SQL Azure Backup creates just has to specify Azure-friendly collation, rather than accepting the server level default. But it’s the kind of little difference that can surprise you when moving to Azure.

You can take a look at SQL Azure Backup here It’s currently in free beta, and Richard is working on the next version (with a “big new feature” he refuses to tell me about) right now.

And that’s basically the state of it. Creating working ports of our tools to SQL Azure wasn’t a massive challenge. In fact, the experience was surprisingly smooth, given how new the platform is. With transience dealt with, the bulk of the effort is in working through and accounting for small differences, and a heck of a lot of testing.

You might also like...

Comments

About the author

Roger Hart United Kingdom

Roger Hart is a technical author at Red Gate Software. He’s fairly sure of that much. He must be – it says so on his business cards. Day to day, he works with an amorphous blend of content strat...

Interested in writing for us? Find out more.

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“We better hurry up and start coding, there are going to be a lot of bugs to fix.”