I’ve been working with Windows Azure SQL Database (WASD) on and off for about three years. This past year I’ve been a bit more involved. I did the technical review of Pro SQL Database for Windows Azure last year. I also delivered a precon seminar along with Dandy Weyn (blog | @ilikesql), Grant Fritchey (blog | @gfritchey) and help from Scott Klein (blog |@SQLScott) at TechEd in North America and Europe.
One common mistake I see with WASD deployments is that users don’t think of it is a distinct platform from SQL Server. They tend to view it as if it were any other instance (or version) of SQL Server. This usually comes back to haunt those users later.
When I hear stories about folks that tried to migrate to WASD with a brute-force-single-click and failed, I ask several follow up questions. I have found a common pattern in the answers: lack of strategy.
I’ve compiled a list of eight strategies that you must consider when moving to WASD. Failure to plan is the same as planning to fail, and this list is going to keep your WASD project moving forward. I’ve also put this list into some slides posted to Slideshare, you can view them at the bottom of this page as well.
1. Build Your Disaster Recovery Plan First
I am amazed at how contentious this topic is for administrators that are looking into WASD. The idea that there is no BACKUP command for WASD can be a conversation stopper. I cannot stress this enough: you can get a transactionally consistent copy of your data from WASD. In fact, it is now possible to schedule this process, so you no longer need to code this yourself using Powershell.
You are already managing your own disaster recovery plans for SQL Server. You will also have to do the same for WASD. Have your recovery point objectives (RPO) and recovery time objectives (RTO) defined. Don’t wait until you get to WASD to find out you aren’t able to recover data from a table someone just truncated by accident because you failed to build an adequate DR plan. That’s your fault, not WASDs fault.
2. Plan For Adequate Resources
When I ask attendees during my WASD sessions if they think they will need to learn new skills in order to be effective at using WASD the answer is “yes” for a majority of the audience. I do my best to inform everyone that most skills as data professionals will transfer to the WASD platform.
If you are planning on using WASD you will want to do some research to see just how much of your existing resources have the right skills to be deployed to your WASD projects. You should not assume that moving to WASD will require you to hire new staff. For example, if you already have an expert on staff for ETL then you won’t need to hire a new one.
3. Testing Your Tools
You already have a core set of tools in your shop. Do you know if they will work effectively against a WASD datasource? I’ve seen customers and clients make the mistake of assuming that their existing tools will work (or that they won’t work at all). Take the time to see what tools you will need. If you are already looking to hire someone to fill a specific need in WASD (see above) you might as well hire someone that has experience using a new tool, too.
Take the time to see which tools will work, which ones you need, and how much budget you have for tools, people, or both.
4. Breaking Changes
WASD is a different platform than traditional SQL Server. If you are looking to use WASD and expecting it to behave in the exact same way as the on-premises version then you are going to be disappointed. I’m always surprised to hear of a client or customer tell me “we were expecting [feature] to work, but it doesn’t”.
WASD publishes a list of supported features. Do not assume that your existing code (or schema) will port to WASD without any issues. In some cases you are going to need to make changes to existing code and schemas in order to get a solution deployed to WASD. Visual Studio 2012 does a wonderful job of helping you to take an existing on-premises database and getting it deployed to WASD, I gave an example of this during TechEd.
If you fail to spend the time researching this adequately then you are likely going to end up as one of those who tried moving to WASD and ended up moving back.
5. Try It Again
Many years ago applications were designed in such a way that they did not assume they would always be connected to their data source. Roughly 20 years ago or so a new breed of applications were being built that took an opposite approach: they assumed they would always be connected. When you go to the Cloud, however, you cannot assume you will always be connected.
One of the strengths of WASD is the high availability you are given. This HA architecture means that at some point you will lose your connection, likely as a result of a failover. For that reason I tell all my customers and clients that they must have retry logic built into their applications. It’s like stepping back in time, really, to when it was a best practice to have retry logic in our apps. Those days are coming around again as people understand that there is a tradeoff for having a platform focused on HA technology.
6. Troubleshooting Errors
One area where you will need to learn some new skills is troubleshooting. Fortunately if you already have some basic troubleshooting skills then you should be in good shape. All you need to port those skills to WASD is to know where to look for signs of trouble. Conversely, if you are the type of person that screams in terror at the thought of using Server Core then WASD is probably not your slice of cheese.
The first thing I do to help others understand how to troubleshoot issues with WASD is to understand the differences between basic connectivity errors and basic WASD errors. Being able to lump issues into one of those two buckets at the start really saves time. WASD also offers a handful of DMOs that help you find additional details about the issues and possible root causes. You are likely to find yourself spending a lot of time looking in the sys.event_log and sys.database_connection_stats views. You can also check this link to see if any of the Azure datacenters are experiencing issues by visiting the Azure Service Dashboard.
7. Tuning For Performance
I often hear people bemoan the fact that much of WASD is not in their control. They can’t touch the hardware, they can’t use tools like xEvents to diagnose issues, and they can’t adjust any server configuration settings. At first I found myself agreeing with them until two thoughts came to mind.
First, not being able to control everything is liberating feeling. Just knowing that so many options are off the table makes my job easier when diagnosing performance issues. Second, the number one reason for all performance problems in the history of databases is due to application code. Think about it. Your database server was running fine until you created the database and connected your applications!
Whenever I see folks having to touch hardware in order to help scale performance it is often because they aren’t allowed to touch code (thanks SharePoint!). Well, with WASD, code is the one thing you can touch. It also happens to be the thing that is likely the root cause of the problem anyway!
Great database performance starts with great database design. If you are moving to WASD then now is the time to focus on the design aspects and code.
8. Understanding Your Bill
You need to understand what you will (and will not) be charged for when using WASD. This is essential as you look to include things such as capacity planning and volumetrics. You need to design with billing in mind, otherwise you run the risk of deploying a solution that costs way more than you anticipated.
Those are the eight strategies I have seen users fail to include when moving to WASD. Any one of them alone may be enough of a pain point for someone to decide not to consider WASD. Combine a few of them together and it is easy to understand why WASD may not even be on the table as a viable option for some projects.
If you take the time to address each of those eight strategies then I believe you will find yourself set up for a chance at success when using WASD.
If you want to take a tour of Azure you can do so with one of the many free virtual labs available at MSDN.
As promised, here are the slides:
Enjoy!
6 thoughts on “Windows Azure SQL Database Strategies”