Microsoft SQL Server, although very robust for a wide variety of business applications, was rarely considered as a business intelligence (BI) and analytics tool because it wasn’t deemed to have the robust capabilities that real-time BI and data analytics require.
All of that changed, however, with the release of SQL Server 2014, and with the introduction this year of SQL 2016. For the first time, Microsoft’s database platform could be considered for complex BI and analytics functionality due to features such as in-memory data processing and advanced analytics built into the database. Suddenly, running BI queries on SQL Server was not only possible but in many ways preferable. It meant that companies could run their BI and analytics on a database platform that didn’t come with a commensurately big price tag.
Recently, my team was invited to conduct a proof of concept (POC) for a global medical manufacturing company to see if they could effectively run their BI and analytics using SQL Server 2014, and what kind of performance they could anticipate. Like a lot of enterprises, this company uses SAP for their operational data, which fed into an SAP Business Warehouse (BW).
The company is very happy with the SAP systems’ performance, but had become increasingly concerned that their legacy SQL Server 2008R2 database wasn’t up to the task of analyzing that data. In particular, the company wanted to run real-time reports from their SAP BW data and couldn’t, due to SQL Server performance issues.
They’d heard that SQL Server 2014 was an order of magnitude faster and wanted to check it out before making the move to another database platform. This was a company that was already running more than 80 different SQL databases in their organization, so they had a lot of time and talent invested in the SQL solution.
Since this was a POC, we looked at a subset of their data: accounts receivable, inventory and sales. We started by obtaining a baseline of current performance using SQL 2008R2. Then, using a replica of their environment with a SQL 2014 Database Server, we measured SQL 2014’s query performance out of the box, optimized it, and re-measured the results to look for improvements.
Without any optimization, we noted an improvement of 64-90% in query performance just through the in-memory database capabilities. Using several different optimization methods—flattening the cubes, increasing the memory size, applying the latest cumulative updates—we were able to improve that performance even more, sometimes significantly so.
In the end, the company was convinced that a simple upgrade to SQL Server 2014 would support their requirement to perform real-time analysis, and would offer performance and functionality improvements that will support their anticipated growth.
For this customer, the benefits of SQL 2014 and the promise of SQL 2016’s added features—including up to 30X faster transactional processing and new cloud capabilities—offer the best approach to meeting their business demands now and in the future.
Clearly, Microsoft has big plans for SQL Server moving forward, and enterprises that are already running SQL Server in their business would do well to take a second look at Microsoft’s new SQL story.
If you’d like to see what SQL Server can do for your business today, contact your local Rolta AdvizeX team and ask about our proof of concept capabilities. ▪