ChannelLife New Zealand logo
Industry insider news for New Zealand's technology resellers
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
Tech job moves
Tech job moves - Bitdefender, Cohesity, Fortinet & MODIFI
We round up all job appointments from June 27-30, 2022, in one place to keep you updated with the latest from across the tech industries.
Story image
Financial results
Margins & revenues up at New Zealand arm of Acer Computer
We look at the local financial statements of Taiwanese manufacturer Acer Computer Inc.
Story image
Robotics
Evonik relies on Getac F110 tablet to control autonomous robot
The aim of the project is to evaluate the practicality of an automated robotic maintenance and inspection solution in the chemical industry.
Story image
Design
Hands-on review: Phillips Hue starter kit
The set-up was one of the simplest I have ever had to do. It is also where the products shine. I was able to connect the bridge, the three lightbulbs and the strip within 10 min.
Story image
Infrastructure
Oracle Cloud Infrastructure expands distributed cloud services
“Distributed cloud is the next evolution of cloud computing, and provides customers with more flexibility and control in how they deploy cloud resources."
Story image
Electrical
Up to $2.4 million shortfall in the collapse of IndeServe
We delve into the liquidators first report on long-standing networking service provider IndeServes collapse.
Story image
Sustainability
Honeywell launches new carbon energy management software for buildings
The new Carbon & Energy Management service allows building owners to track and optimise energy performance against carbon reduction goals, down to a device or asset level.
Story image
Cybersecurity
FIDO Alliance releases guidelines for optimising UX with FIDO Security Keys
The new guidelines aim to accelerate multi-factor authentication deployment and adoption with FIDO security keys.
Story image
Cybersecurity
Unknown connections: How safe is public WiFi in Aotearoa?
If it's not your own household WiFi, then who has control of your data and is your connection actually safe?
Story image
New Relic
New Relic looks to observability market with new partner programme
New Relic has announced the availability of New Relic Partner Stack, an enhanced programme expanding New Relic’s commitment to partners.
Story image
Revenue
Datacom announces revenue of $1.45 billion, fall in profit
Growing market pressures and border closures saw Datacom place increased focus on talent development initiatives for both existing and future employees.
Story image
BitTitan
How to achieve your monthly recurring revenue goals
Monthly recurring revenue (MRR) is the ultimate goal, the most important issue on which anyone in the IT channel should focus.
Story image
Microsoft
SAS wins Microsoft ISV 2022 Partner of the Year award
"We formed the SAS and Microsoft strategic partnership with a shared goal of making it easier for customers to drive better decisions in the cloud."
Story image
Salesforce
Data crucial to capture shoppers' wallets post-COVID
First-party data strategies key to driving personalisation, customer satisfaction, and long-lasting relationships according to a new report.
Story image
Review
Hands-on review – CleanMyMac X utility for macOS
We get hands-on with a useful utility that macOS users never thought they needed but probably do.
Story image
Public Cloud
Public cloud services revenues top $400 billion in 2021
"For the next several years, leading cloud providers will play a critical role in helping enterprises navigate the current storms of disruption."
Story image
Cyber Criminal
Identity and access: the fight is on
Blue team defenders are used to protecting our data, applications, and users with access controls and other security mechanisms, which is why attacks like this are especially challenging when they target identity and access control systems.
Story image
Internet of Things
ManageEngine wins big in IDC MarketScape assessment
ManageEngine's Endpoint Central service has been recognised as a leader by IDC MarketScape in several categories including Internet of Things device deployments and UEM software for SMEs.
Story image
Data
MYOB improves data visibility and user access with Snowflake
"Solutions such as Snowflake allow us to better understand our customers and make evidence-based decisions on what features work best for them."
Story image
Microsoft
ASI Solutions named finalist of Microsoft Surface Partner of the Year
"ASI Solutions has a strong Microsoft focus, building value by helping customers maximise investment in modern workplace solutions."
Story image
Dicker Data
EXCLUSIVE: Why women in IT makes good business sense - Dicker Data
The Federal government wants to bolster female participation in the tech industry to at least 40% by 2030. Here's how one homegrown Australian company has already reached that goal.
Story image
Hybrid Cloud
HPE GreenLake advances hybrid cloud experience with new services
"The innovations unveiled today further build on our vision to provide the market with an unmatched platform to spur innovation and drive transformation.”
Story image
Oracle Cloud
Commvault, Oracle to deliver Metallic Data Management as a Service
"We are excited to partner with Commvault and enable our customers to restore and recover their most mission-critical cloud data."
Story image
Entelar
How TruSens air purifiers can create healthier workspaces
The pandemic has heightened our awareness of our own and others’ health, and made us all much more conscious of the environments we work in.
Story image
Cybersecurity
Zero trust security adoption rises 27% in just two years
A survey of WAN managers has revealed that multi-factor authentication and single sign-on are the top zero trust features implemented.
Story image
Hybrid workforce
Why hybrid working is here to stay and how to ace it
Citrix's new report reveals hybrid workers are more productive and engaged at work than their office and completely remote counterparts.
Story image
Finance
Airwallex launches global payment services in New Zealand
The launch will enable businesses in New Zealand to tap into Airwallex's global payments services, offering an alternative to traditional banks.
Story image
Mergers and Acquisitions
SAS acquires Kamakura to propel risk technology innovation
Underscoring SAS growth in the domain-specific solutions space, the acquisition will enable SAS to greatly enhance the breadth of its risk solutions portfolio. 
Story image
Gaming
Hands-on review: 16GB PNY XLR8 Gaming EPIC-X 3600MHz DDR4
PNY sent over its 16GB XLR8 Gaming EPIC-X 3600MHz CL16 DDR4 module kit for testing. The kit contains two 8GB dual-channel modules with RGB lighting.
Story image
Ransomware
Secureworks reveals new information on BRONZE STARLIGHT threat group
New research from Secureworks has uncovered new information on the Chinese threat group BRONZE STARLIGHT and how they are using targeted ransomware to initiate complicated attacks.
Story image
WatchGuard Technologies
Ransomware volume doubled 2021 total by end of Q1 2022
Ransomware detections in the first quarter of this year doubled the total volume reported for 2021, according to a new report. 
Story image
Microsoft
Digital innovation could shape the future of NZ - Microsoft
With cloud technologies available to more people around the world than ever before, it is not only businesses who will benefit from using them.
Story image
Citrix
The best ways to attract young talent during labour shortages
New research from Citrix reveals hybrid working and ventures into the metaverse are top of mind for Gen Z workers.
Story image
Cybersecurity
Vulnerable APIs costing businesses billions every year
Large companies are particularly vulnerable to the security risks associated with exposed or unprotected APIs as they accelerate digital transformation.  
Story image
Samsung
Monitors are an excellent incentive for getting employees back
The pandemic has taught us that hybrid working is a lot easier than we would’ve thought, so how can the office be made to feel as comfortable as home? The answer could be staring you in the face right now.
Story image
Web Development
Whitecliffe fosters careers for the future of tech
Do you want a career in Information Technology, Networking, Web Development, Software Development, or are you looking to upskill?
Story image
Hybrid Cloud
Advent One acquires Layer 8 Networks, complements hybrid cloud offering
The acquisition comes at a time of surging demand in hybrid cloud, network virtualisation and network security.
Story image
Awards
Microsoft names A/NZ Partner of the Year award winners
The awards recognise partners across the globe for their innovative use of Microsoft technologies to help customers succeed.
Story image
HP Inc
Firmware attacks significant threat in age of hybrid work
Changing workforce dynamics are creating new challenges for IT teams around firmware security, according to new research.
Story image
MSP
Video: 10 Minute IT Jams - An update from CyberArk
Olly Stimpson joins us today to discuss the importance of MSP programmes and how MSP partners are experiencing success with CyberArk.
Story image
Dark web
Cybercrime in Aotearoa: How does New Zealand law define it?
‘Cybercrime’ is a term we hear all the time, but what exactly is it, and how does New Zealand define it in legal terms?
Story image
Great Resignation
New SAP study uncovers impact of 'the great resignation'
Coined in 2021, the phrase 'the great resignation' refers to millions of employees globally leaving their jobs. The phenomenon is real and impacting SMEs.
Story image
Cybersecurity
Why is NZ lagging behind the world in cybersecurity?
A recent report by TUANZ has revealed that we are ranked 56th in the world when it comes to cybersecurity - a look into why we're so behind and what needs to be done.
Story image
Cloudian
Cloudian, Vertica to deliver on-premise data warehouse platform
"We’re enabling our customers to capitalise on a leading object storage platform and maximise the value of their digital assets.”