IT Brief Australia logo
Technology news for Australia's largest enterprises
Story image

No ETL? No worries, with SQL Server 2019's new data virtualisation

By Contributor
Fri 5 Apr 2019
FYI, this story is more than a year old

Article by IT journalist David Williams

Big data is big business, and global software titan Microsoft is making big data its business to streamline businesses with the next release of SQL Server 2019 late this year.

Microsoft states SQL Server 2019’s focus is to make managing a big data environment a lot easier.
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.

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 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 a company’s database.

All complex ETL processes are gone.

There are numerous situations where this delivers tremendous time and functionality savings.

For instance, if a user makes a dashboard showing their company’s revenues by period, office and client out of their 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 the 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 they’re using SQL Server 2019, the user merely saves the budget figures as a CSV file on the Finance team’s file share and add this as a virtual table in their 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 someone is using SQL Server 2019, they 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 (HTAP)
  • intelligent query processing features that improve scaling of queries
  • security enhancements including Always Encrypted secure enclaves, and Data Discovery and Classification labelling for GDPR
Related stories
Top stories
Story image
Employment
Tech job moves - Forcepoint, Malwarebytes, SolarWinds & VMware
We round up all job appointments from May 13-20, 2022, in one place to keep you updated with the latest from across the tech industries.
Story image
Migration
Let’s clear the cloud visibility haze with app awareness
Increasingly, organisations are heading for the cloud, initiating new born-in-the-cloud architectures and migrating existing applications via ‘lift and shift’ or refactoring.
Story image
Remote Working
Successful digital transformation in the hybrid work era is about embracing shifting goalposts
As organisations embraced remote working, many discovered they lacked the infrastructure needed to support history’s first global load test of remote work capabilities.
Story image
Vectra AI
Understanding the weight on security leader’s shoulders, and how to shift it
Millions of dollars of government funding and internal budgets are being funnelled into cybersecurity to build resilience against sophisticated threats, indicating how serious this issue has become.
Story image
Artificial Intelligence
AI-based email security platform Abnormal Security valued at $4B
"A new breed of cybersecurity solutions that leverage AI is required to change the game and stop the rising threat of sophisticated and targeted email attacks."
Story image
Lightspeed
Lightspeed launches all-in-one marketing platform in A/NZ
ECommerce provider, Lightspeed has launched a new all-in-one marketing solution, Lightspeed Marketing & Loyalty in Australia and New Zealand.
Story image
Customer experience
Gartner recognises Okta for abilities in Access Management
Okta has announced it has been recognised as a Customers' Choice for the fourth time in a row in the Gartner Peer Insights "Voice of the Customer" report.
Story image
SaaS
Rubrik Security Cloud marks 'next frontier' in cybersecurity
"The next frontier in cybersecurity pairs the investments in infrastructure security with data security giving companies security from the point of data."
Story image
Fintech
Airwallex launches new bank feed integration with NetSuite
Airwallex has launched a new bank feed integration with NetSuite, developed in partnership with NetSuite solution partner, Onlineone.
Story image
Ransomware
Cybersecurity starts with education
In 2021, 80% of Australian organisations responding to the Sophos State of Ransomware study reported being hit by ransomware. 
Story image
Malware
New vulnerabilities found in Nuspire’s Q1 2022 Threat Report
“Threat actors are quickly adjusting their tactics and these exploits tend to get industry attention, but the threat posed by older and attacks still persists."
Story image
Digital Transformation
How to modernise legacy apps without compromising security
At a time when digital transformation has become central to business, even the most important applications come with a ‘use-by’ date.
Story image
Application Security
What are the DDoS attack trend predictions for 2022?
Mitigation and recovery are vital to ensuring brand reputation remains solid in the face of a Distributed Denial of Service (DDoS) attack and that business growth and innovation can continue.
Story image
Sustainability
Power at the edge: the role of data centers in sustainability
The Singaporean moratorium on new data center projects was recently lifted, with one of the conditions being an increased focus on power efficiency and sustainability.
Story image
Supply chain
Jetstack promotes better security with supply chain toolkit
The web-based resource is designed to help organisations evaluate and plan the crucial steps they need to establish effective software supply chain security.
Story image
Data Protection
Barracuda launches new capabilities for API Protection
"Every business needs this type of critical protection against API vulnerabilities and automated bot attacks," Barracuda says.
Darktrace
Threat actors are exploiting weaknesses in interconnected IT/OT ecosystems. Darktrace illuminates your entire business and takes targeted action to stop emerging attacks.
Link image
Exabeam
Find out how a behavioural analytics-driven approach can transform security operations with the new Exabeam commissioned Forrester study.
Link image
Story image
Broadband
Telstra enters into new RSP agreement with Opticomm
Telstra has entered into an RSP agreement with Opticomm (A Uniti Group Limited subsidiary) to provide network fibre services to customers.
Story image
Ransomware
A third of companies paying ransom don’t recover data - report
Veeam's report finds 76% of businesses who are victims of cyberattacks paid the ransom to recover data, but a third were still unable to get their information back.
Story image
Artificial Intelligence
How to ensure ethical deployment of AI implementations
The increase in automation and machine technology such as AI and machine learning has unlocked a whole new level of scale and service to organisations. 
Story image
Digital Transformation
Trading up: It's time to swap core systems for flexible digital applications
This year will see more oranisations planning and commencing high tech renovations that will shake up the way they operate.
Story image
Customer experience
Research unveils precarious customer loyalty for retailers
New research has found customers are reassessing established brand loyalties as their priorities and behaviours shift.
Story image
ChildFund
ChildFund launches new campaign to protect children online
ChildFund says WEB Safe & Wise aims to protect children from sexual exploitation and abuse online while also empowering them to become digitally savvy. 
Story image
Cloud Security
Aqua Security createa unified scanner for cloud native security
“By integrating more cloud native scanning targets into Trivy, such as Kubernetes, we are simplifying cloud native security."
Story image
Qualys
Qualys updates Cloud Platform solution with rapid remediation
The new update is designed to enable organisations to fix asset misconfigurations, patch OS and third-party applications, and deploy custom software.
Story image
Microsoft
New Relic enters multi-year partnership with Microsoft Azure
New Relic has announced a strategic partnership with Microsoft to help enterprises accelerate cloud migration and multi-cloud initiatives. 
Story image
Sift
Sift shares crucial advice for preventing serious ATO breaches
Are you or your business struggling with Account Takeover Fraud (ATO)? One of the latest ebooks from Sift can provide readers with the tools and expertise to help launch them into the new era of account security.
Softiron
For every 10PB of storage run on HyperDrive vs. comparable alternatives, an estimated 6,656 tonnes of CO₂ are saved by reduced energy consumption alone over its lifespan. That’s the equivalent of taking nearly 1,500 cars off the road for a year.
Link image
Story image
Artificial Intelligence
CFOs using digital workers and AI to prevent unnecessary loss
New technology is now allowing CFOs to use digital workers to automate their accounting processes, making it easier for them to avoid unnecessary losses.
Story image
Apricorn
Data backup plans inadequate, data still at risk - study
The Apricorn 2022 Global IT Security Survey revealed that while the majority organisations have data backup plans in place, data for many are at risk.
Story image
Data Center
Preventing downtime costs and damage with Distributed Infrastructure Management
Distributed Infrastructure Management (DIM) can often be a lifeline for many enterprises that work with highly critical ICT infrastructure and power sources.
Story image
Data solutions
South Australia state satellite makes significant progress
South Australia’s first state satellite has successfully completed the Critical Design Review (CDR), moving it closer to providing tangible data solutions.
Story image
Cybersecurity
Infoblox's State of Security Report spotlights Australian remote work hazards
Attackers exploit weak WiFi, remote endpoints, and the cloud, costing 50% of organisations over $1.3 million in breach damages.
Story image
Attain
Revenue operations is taking centre stage
As the business world continues to evolve, new demands need to be met to keep up with the ever-changing landscape. 
Story image
Artificial Intelligence
SAS announces new products amid cloud portfolio success
Analytics and AI company SAS is deepening its broad industry portfolio with offerings that support life sciences, energy, and martech.
Story image
CRM
Zendesk announces new conversational CRM solutions
“The last few years have made it obvious that digital is the front door, convenience is paramount and relationships are anchored in conversations."
Story image
Cybersecurity
More than 40% of banks worried about cloud security - report
Publicis Sapient's new report finds security and the lack of cloud skills and internal understanding of business benefits are big obstacles for banks moving to the cloud.
Story image
Digital Transformation
The Huawei APAC conference kicks off with digital transformation
More than 1500 people from across APAC have gathered for the Huawei APAC Digital Innovation Congress to explore the future of digital innovation.
Story image
Manufacturing
HINDSITE wins Aerospace Xelerated Pitch Challenge with solution to support Boeing
Brisbane-based startup HINDSITE was the winner of the first ever Pitch Challenge organised by Aerospace Xelerated in partnership with Queensland XR Hub. 
Story image
Cybersecurity
A10 Networks finds over 15 million DDoS weapons in 2021
A10 Networks notes that in the 2H 2021 reporting period, its security research team tracked more than 15.4 million Distributed Denial-of-Service (DDoS) weapons.
Story image
Excel
Could your Excel practices be harming your business?
While Excel has been the de-facto standard for budgeting, planning, and forecasting, is it alone, enough to support organisations in the global marketplace that’s facing rapid changes due to digital transformation?
Story image
Sustainability
Grasping the opportunity to rethink the metrics of a sustainable data centre
A data centre traditionally has two distinct operations teams: the Facility Operations team, and the IT Operations team. Collaboration between them is the key to defining, measuring, and delivering long-term efficiency and sustainability improvements.
Story image
trust
9/10 Aussies to stop spending if personal data compromised
"Based on the patterns we are seeing among Australian consumers, it is evident that trust in a brand is exceptionally important."