By: Tim Smith | Updated: 2017-03-16 | Comments | Related: > Azure
Problem
Recently a friend and I had a few ideas that we wanted to quickly build, test, and get feedback from a market and we used Azure App Services and AzureSQL for our testing. In this tip, we look at some of our lessons learned, as well as some of the advantages and disadvantages to using the cloud to quickly build an idea before committing to it.
Solution
Suppose that you spent a thousand dollars on building an idea only to discover that it wouldn't function or run the way you imagined. Maybe you spent the money only to discover that no one really wanted the product. I've been on numerous SQL Server projects that involved significant costs just to test a Proof of Concept (POC), which resulted in significant costs in addition to involving work to terminate. One use for the cloud, even in environments which prefer not to use it, is a POC environment - whether you're testing some functionality, a POC, or something else.
The below are some lessons I've learned while using the cloud as a test platform only before making a decision to either commit to the idea in the cloud, or migrate it elsewhere.
Design each cloud service that match the needed traffic for all involved services.
One technical reminder is that a web service and a database service will only interact on the level of the maximum capacity of one. For an example, if a web service can only handle a small amount of traffic, even if the database is scaled up in AzureSQL, it won't matter. This is a reminder that both must be compatible for the traffic because one can quickly limit the other. This may require some experimenting, as I've been surprised at how much traffic a Basic tiered AzureSQL database can handle with some applications; you might not need as much scale as you think. In general, I suggest starting small, using the metrics to ascertain how many resources are being used, then scaling as appropriate. All down time can either be scaled down, or terminated.
While there are differences between AzureSQL and SQL Server, AzureSQL can make a great POC sandbox for a later SQL Server ETL process.
In a situation where I want to add a feature from a lot of data for clients as a POC (like beta clients), AzureSQL makes a great tool for this without committing to it in the long run. While there are some differences between AzureSQL and SQL Server, in many use cases, you can import an AzureSQL database into SQL Server, point the connection strings there, and switch use - and I've done this multiple times. This allows me to see if clients will value these data before committing to making the data regular for all other clients. I've seen many environments spend significant resources for tests and rationalize it later as, "We may use these created resources later for something else" when they could have tested these data stored in the cloud for one-hundredth (or less) of the cost and easily terminated the resources if their clients didn't need or value the data or feature. If your company has talent with experience in SQL Server and AzureSQL, I would highly recommend this on these type of POC projects.
In some cases, AzureSQL makes a useful traffic throttle for some reports, even outside of testing.
Provided schema and data compatibility exists, consider that you can migrate some SQL Server data into AzureSQL where some of your reports may point. Whether you migrate the data on a schedule using bulk loads, or whether you set an AzureSQL database as a subscriber for replication, these options can assist with some heavy read reports. This also allows you to horizontally scale reporting clients; imagine one of the more active clients being scaled at S0, while the less active clients are all scaled at Basic.
Scale makes large tests easier and much less expensive.
When we were testing bulk ETL loads to AzureSQL, we started on the basic level and I was impressed how many records it could receive from a .NET application within a minute. When scaling it up, the speed increased and it allowed me to test the functionality of a massive load where data were horizontally scaled and routed. Once the test was done, I could keep the database in an export file, scale it down to Basic, store it in Azure storage, or remove it completely if I didn't need it anymore. Still, the increased scale allowed a heavy test for about an hour before I reverted the database back to Basic. One important note here about using the cloud to test functionality: make sure that the functionality will work outside the cloud; for instance, avoid using an AzureSQL design that you couldn't import into SQL Server - or use SQL Server on an Azure VM, if the final destination is SQL Server outside the cloud. Cloud makes cheap testing for matching functionality.
Cloud testing validates the idea, not necessarily the design - if the design will be outside of the cloud and differs from the cloud arrangement.
The cloud makes a great environment for testing functionality and proving a POC, and I highly recommend delineating this before using it. If someone wants to test an application which they've committed to that will be outside the cloud, they should build it and test it outside the cloud. If someone wants to see if an application can be built or if they can try something to see if they can attract a market, and it will be outside the cloud, it would be appropriate for using it as a "test" - the key difference here is that there is no guaranteed commitment. The exception to this rule that I may follow with SQL Server architecture is that I first design in SQL Server, then migrate to AzureSQL, and have the application built with both connection strings; it depends on the project in question. Finally, if a design might be in the cloud later and it can be built and maintained very cheap, it makes sense to additionally build there.
Next Steps
- For environments that do multiple POCs in a quarter or year, consider if and where Azure may be helpful.
- Some examples of where a cloud platform, like Azure, would be useful: (ETL) how can we combine APIs into a meaningful report, (ETL) will our clients value or use these data, (multiple scenarios) how do we want to handle [x] event in our application if the need arises, etc.
- As a final point, even if it's slightly expensive to test a POC in the cloud, the speed at which it can be done and unwound will save resources if the POC is invalidated.
About the author
Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2017-03-16
