Home Data Microsoft SQL Server 2019 set to ease ETL pains with data virtualisation
Microsoft SQL Server 2019 set to ease ETL pains with data virtualisation Featured

Global software house Microsoft is making big data the focus of SQL Server 2019, set for release later this year. A key part is data virtualisation, eliminating complex ETL processes. 

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.

Data virtualisation helps you create a single “virtual” layer of data from these disparate sources providing unified data services to support multiple applications and users.

ads external table wizard summary

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.

ads external table csv preview

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

FREE SEMINAR

Site24x7 Seminars

Deliver Better User Experience in Today's Era of Digital Transformation

Some IT problems are better solved from the cloud

Join us as we discuss how DevOps in combination with AIOps can assure a seamless user experience, and assist you in monitoring all your individual IT components—including your websites, services, network infrastructure, and private or public clouds—from a single, cloud-based dashboard.

Sydney 7th May 2019

Melbourne 09 May 2019

Don’t miss out! Register Today!

REGISTER HERE!

LEARN HOW TO REDUCE YOUR RISK OF A CYBER ATTACK

Australia is a cyber espionage hot spot.

As we automate, script and move to the cloud, more and more businesses are reliant on infrastructure that has the high potential to be exposed to risk.

It only takes one awry email to expose an accounts’ payable process, and for cyber attackers to cost a business thousands of dollars.

In the free white paper ‘6 Steps to Improve your Business Cyber Security’ you’ll learn some simple steps you should be taking to prevent devastating and malicious cyber attacks from destroying your business.

Cyber security can no longer be ignored, in this white paper you’ll learn:

· How does business security get breached?
· What can it cost to get it wrong?
· 6 actionable tips

DOWNLOAD NOW!

David M Williams

David has been computing since 1984 where he instantly gravitated to the family Commodore 64. He completed a Bachelor of Computer Science degree from 1990 to 1992, commencing full-time employment as a systems analyst at the end of that year. David subsequently worked as a UNIX Systems Manager, Asia-Pacific technical specialist for an international software company, Business Analyst, IT Manager, and other roles. David has been the Chief Information Officer for national public companies since 2007, delivering IT knowledge and business acumen, seeking to transform the industries within which he works. David is also involved in the user group community, the Australian Computer Society technical advisory boards, and education.

 

Popular News

 

Telecommunications

 

Guest Opinion

 

Sponsored News

 

 

 

 

Connect