Microsoft says its flagship database product, SQL Server 2019, will make it a lot easier to manage a big data environment. In fact, the product supports a rich variety of environments like Hadoop Distributed File System (HDFS), Spark, analytics tools, and even Kubernetes-managed Linux containers.
Previously, Microsoft allowed T-SQL queries inside SQL Server 2016 to pull data from Hadoop and return it in a structured way without moving or copying the data. In the 2019 release, this concept of data virtualisation is widely expanded to data sources including Oracle, MongoDB, even CSV files.
Data virtualisation means database administrators can virtualise external data in a SQL Server instance, regardless of source, location, and format, so it can be queried like any other set of tables within your SQL Server instance.
This sounds like a data lake, but unlike a typical data lake there is no need to move data from where it lives when using data virtualisation, and this is a huge advantage over traditional ETL (extract, transform, load) processes that by necessity require delays, extra storage, additional security, and an amount of engineering to setup and maintain.
For database administrators, software developers, data scientists and others this new capability is transformative and powerful in its simplicity.
Quite literally, Microsoft has enabled your SQL Server 2019 databases to hold virtual tables which come from some other place on the Internet - whether it is a SQL Server database or otherwise, whether it’s local or far - and query and join and work with these tables just as if they were native to your database. All complex ETL processes are gone.
In this writer’s own experience, there are numerous situations where this delivers tremendous time and functionality savings.
For instance, you make a dashboard showing your company’s revenues by period, office and client out of your main line-of-business app’s database. Management then asks for budgeted sales figures to be included so the dashboards contrast actuals against the budget. However, these aren’t included in your main app but instead held in an Excel spreadsheet. Traditionally, the solution is to manually or periodically upload the spreadsheet into a new table created for this purpose, which your dashboard then queries against.
Or, if you’re using SQL Server 2019, you merely save the budget figures as a CSV file on the Finance team’s file share and add this as a virtual table in your app’s database. The budget figures appear, and can be queried against, and reported on as if they were always in the database.
Another case: the company needs sales figures to move from its main application into the back-office ERP platform. Typically, this is achieved by exporting and importing the data between the disparate products.
Or, if you’re using SQL Server 2019 you include relevant tables from the ERP system as virtual tables in the main application’s database, and directly move data via a scheduled stored procedure. The ERP doesn’t even have to be an SQL Server database, so long as it is accessible.
SQL Server 2019’s data virtualisation supports Cosmos DB, SQL Server, Azure SQL, Oracle, HDFS and DB2.
Other SQL Server 2019 features include
- transformational insights over structured and unstructured data supporting Hadoop and Spark;
- scalable compute and storage clusters;
- a complete AI platform to train and operationalise R and Python models in SQL Server Machine Learning Services or Spark ML using Azure Data Studio notebooks;
- the choice of programming language and platforms like .NET, PHP, Node.JS, Java, Python, Ruby and more, and deploy the application on Windows, Linux or containers both on-premise and in the cloud;
- real-time analytics on operational data using Hybrid Transactional and Analytical Processing; and
- intelligent query processing features that improve scaling of queries- security enhancements including Always Encrypted secure enclaves, and Data Discovery and Classification labelling for GDPR.
SQL Server 2019 is in a community tech preview stage, and the final release is anticipated in the second half of 2019. Interested parties can sign up for early adoption now.