A couple of years ago, I was at a client site working to fix some SQL Server performance problems. Every three months, the organization received data in a large file and loaded it into a SQL Server database. They then ran queries against this data to provide reports for their customers. My client’s advantage over others in their industry was that they were the only ones who took the time to normalize the data, and, therefore, they could offer a wider variety of insights for their customers.
I was there on-site because my client was experiencing several performance-related problems that were only getting worse as size of the data grew bigger than a terabyte. Both loading the data and querying it took much longer than expected, and it had become next to impossible to delete old data.
By tuning some queries and indexes the queries improved to acceptable levels. I also recommended that they implement table partitioning to assist with loading and deleting data, but, at that time, to do that meant that they would have to upgrade to SQL Server Enterprise Edition. (Table partitioning is now available on Standard Edition beginning with SQL Server 2016 SP1.) They nodded gravely, but the cost was going to be a problem for them.
A few months later, I heard from the client again. This time, they were very excited to tell me that they had moved the database completely off SQL Server and into Windows Azure Tables, a NoSQL key-value pair storage service. Instead of querying the data using SQL, they were using C# to do the aggregations. This solution solved all their performance issues.
This NoSQL solution made perfect sense for them, and I congratulated them on the idea and their success. I then had a twinge of doubt. Why hadn’t I suggested something similar? After all, their application didn’t do any of the things that required the power of a relational database. I then realized that I needed to expand my horizons and learn more about solutions outside the relational world.
Microsoft has made several non-relational solutions available on Azure. As well as Azure Tables, there is the newly released Azure Cosmos DB, a highly scalable distributed service based on DocumentDB. It already has LINQ, a MongoDB query language and API, a Table API, and a Graph API. You might choose a more specialized alternative solution such as Hadoop or Neo4j, both available in Azure.
My client had painted themselves into a corner by initially choosing the wrong database solution for their application; the simpler solution worked much better. I don’t think that relational databases are going away anytime soon, but even SQL Server professionals like me don’t think they are the correct solution for every application, either. It pays to look at the benefits and drawbacks of each and determine the best technology for your project. Let us know if you have had success – or failure – by moving to non-relational database solutions.