IT Brief New Zealand logo
Technology news for New Zealand'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
Artificial Intelligence
How to ensure ethical deployment of AI implentations
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
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
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
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
Microsoft
Microsoft backing Māori and Pacific wāhine in tech industry
A new initiative focused on getting Māori and Pacific wāhine into the tech industry and backed by Microsoft, NZTech and the government is calling for tech companies to get involved.
Story image
Apple
Apple previews new features for users with disabilities
Apple says new software features that offer users with disabilities new tools for navigation, health and communication, are set to come out later this year.
Story image
Remote Working
IT teams deploy powerful technologies to enable remote work
"We found that IT teams mastered the challenges of remote work last year in large part by employing powerful yet easy-to-use technologies."
Story image
Public Cloud
Cloud adoption still a work in progress, NetApp finds
NetApp has announced the results of the annual Cloud Infrastructure Report based on a survey of public cloud business and IT decision makers.
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
Remote Working
How zero trust and SD-WANs can support productive remote working
The way people connect with applications and data has changed, users are remotely accessing resources that could be stored anywhere from a corporate data center to the cloud.
Story image
SAS
New SAS service overcomes subscription fatigue for media companies
SAS has launched SAS 360 Match which helps media companies move towards a AVOD model to generate revenue as subscribers cancel.
Story image
Wireless
SmartCIC, BICS partner to expand wireless service options
SmarCIC has partnered with BICS to increase choice for organisations using fixed wireless services, expanding existing carrier relationships for its CELLSMART division.
Story image
Artificial Intelligence
SAS unveils AI experience to improve kids' batting abilities
SAS has created The Batting Lab, an interactive experience using AI, computer vision and IoT analytics to help kids improve their baseball and softball swings.
Story image
Phishing
Google reveals new safety and security measures for users
Google's new measures include automatic two step verification, virtual cards and making it easier to remove contact information on Google Search results.
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
Booster
Booster Innovation Fund. A fund of Kiwi ingenuity – for Kiwi investors.
Link image
Story image
Ivanti
Ivanti and Lookout bring zero trust security to hybrid work
Ivanti and Lookout have joined forces to help organisations accelerate cloud adoption and mature their zero trust security posture in the everywhere workplace.
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
Mobility
Hands-on review: STM laptop bags
The advent of hybrid working has meant we need laptop bags. We got our hands on two of the most popular laptop bags from STM.
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
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
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.
Exabeam
Find out how a behavioural analytics-driven approach can transform security operations with the new Exabeam commissioned Forrester study.
Link image
Story image
Microsoft
SAS Viya on Microsoft Azure to deliver 204% return - study
The Forrester Total Economic Impact study finds SAS Viya on Microsoft Azure brings a 204% return on investment over three years.
Story image
Artificial Intelligence
Clear Dynamics closes $35M funding round, invests in global growth
The funding is a major milestone and speaks to Clear Dynamics’ vision for AI-enabled ‘composable’ enterprise software, the company states.
Story image
Talend
Talend introduces new data health solutions for businesses
Talend has announced its latest version of Talend Data Fabric, with the release of Talend Trust Score enabling data teams to establish a foundation for data health.
Story image
PaaS
New digital traffic light system to tackle construction defects
Smarter Defects Management launches its PaaS digital system and says it will revolutionise managing defects in the construction industry.
Story image
Artificial Intelligence
SAS launches human-focused responsible innovation initiative
SAS has launched a responsible innovation initiative, furthering its commitment to equity and putting people first.
Story image
Commerce Commission
ComCom welcomes new marketing codes for the telecom industry
The Commerce Commission is welcoming the creation of new marketing codes for the telecommunications industry.
Story image
Cybersecurity
The 'A-B-C' of effective application security
Software applications have been a key tool for businesses for decades, but the way they are designed and operated has changed during the past few years.
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
Sustainability
Siemens showcases new automated solutions for data centers
Siemens has implemented new automated solutions and AI in the Baltic region's largest data center, providing insight into the future of data center management.
Story image
Oracle
Prophecy International migrates VMware environment with Oracle Cloud Solution
The Adelaide-based global provider is using the solution to eliminate the need to re-write applications, therefore allowing the company to enhance its business operations.
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
SaaS
Absolute Software expands Secure Access product offering
Absolute Software is enhancing its Secure Access product portfolio, enabling minimised risk exposure and optimised user experiences in the hybrid working environment.
Story image
SaaS
Cloudflare launches instant serverless database for dev teams
"Today we’re announcing our first serverless database which we expect will quickly become one of the largest databases in the world."
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
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
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
Fonterra
Fonterra to use automated guided vehicles by Dematic
New Zealand's dairy giant Fonterra is upgrading its manufacturing facility in Edendale with new automated guided vehicles (AGVs) from Dematic.
Story image
Commerce Commission
ComCom appeals $2.25 million fine in Vodafone FibreX case
The Commerce Commission has filed an appeal in the High Court against a $2.25 million fine imposed on Vodafone NZ for its offending under the Fair Trading Act.
Story image
Collaboration
Meadows exits Ingram Micro New Zealand on a good note
We look at the resignation of Danny Meadows from Ingram Micro's New Zealand Unified Communications & Collaboration business unit.
Story image
Tech job moves
Tech job moves - Datacom, Micro Focus, SnapLogic and VMware
We round up all job appointments from May 6-12, 2022, in one place to keep you updated with the latest from across the tech industries.
Story image
Power / Energy
Keysight Technologies introduces new next-gen DPT solution
Keysight Technologies has announced its new next-generation Double-Pulse Tester (DPT) with the PD1550A Advanced Dynamic Power Device Analyser.