Administration, Amazon AWS, Azure, Azure SQL Database, Editorials

Using Throw-Away Databases, Tables

We’ve been doing some work with a few different database systems that are supported on the different cloud platforms – work where we’re using the databases as either a pre-processing or holding-zone for data that isn’t quite yet ready to be fed into the overall scheme of things for the customers.

It was an odd realization that with many of these systems, when it comes to cleaning things out, many times it’s easier (and less expensive processing wise and perhaps bill-wise) to simply delete the resource.  It’s strange to think of removing a table just to recreate it, or even dropping a database as a quick way to reset it.  I have this “angel on my shoulder” voice screaming that there simply must be a better way, that this is somehow wasteful.

Indeed on some platforms, it makes a lot of sense.  On others, it can incur billing milestones.  Like creating a table is a billable event.  Deleting stuff from that table, while billable, may be a far lower billing amount per transaction.  It’ll depend on the resource you’re using to do the transaction.

Of course the ultimate is firing up an instance to store things, automating either the database service or the overall server image as you need it, then shutting it down and throwing it away when you’re done.  It strikes me that all of this ad-hoc database use opens up so many different approaches to managing things.

Back in the day, it used to be that [one of those really big my…. social media sites of the early days] could only attain more capacity by building in servers.  A whole system had to be created for slicing and dicing information, and it had to take into account the age of the information, the number of people going after it, things like that.  It was a really big deal.

If your systems are still architected where scale-out and scale-up is painful, be mindful of the fact that there are many different options that are available.  From adding instances on-demand based on processing needs, to storage types that ebb and flow with your workload to extending your database to the cloud seamlessly from SQL Server.

One of the things we’ve been having great success with though, is the use of the pre-processor systems – gather and work with the data a bit so SQL Server can really run with it when it’s handed off for analysis.  This might be MySQL playing the role, or DynamoDB or other very inexpensive solutions that can step in, gather information from specific sources and do a bit with it.  It can enhance your processing as much as a bigger instance or more storage or more services in that second analysis phase.

It may take some architecture changes for your applications, but many times the changes are as simple as changing the destination where information is stored as it’s collected.  From there, you can do the automation bits and pieces as you need to, and bring it in to the specific tools you need, all set to go.