SQL Saturday 769 Melbourne & 771 Sydney

For those not aware there’s some excellent local SQL events coming up here in Melbourne and Sydney

  • SQL Saturday 769 (Sat 30 Jun 2018) Melbourne.  SQL Saturday 771 (Sat 07 Jul 2017) Sydney.  For those looking for some great free local SQL / Azure / BI / etc learning, you simply cannot go past a SQL Saturday anywhere in the world!  And this one right here in Melbourne will again be no exception.  There is a lineup of fantastic local speakers including Microsoft and MVP’s as well as international speakers too.
  • SQL Saturday Pre-Con Training (Fri 29 Jun 2018).  Melbourne.   Leading up to the main event are 3 pre-con training events which cover some very interesting topics around SQL Performance Analysis, Azure SQL Cloud Migrations and Data Science using Azure.

 

SQL Saturday 769 – Melbourne

SQL Saturday is an excellent free learning resource for all things SQL Server – all costs are covered by donations and sponsorships.  Some of the excellent sponsors this year are Microsoft, Wardy IT, SQLBI, and PASS.

Some of the session focus areas include SQL 2017/19 (many deep dives across almost all facets!), SQL DB/DW in Azure, CosmosDB, Azure Machine Learning, R, Data Lakes, BI, DAX, …and so much more!

The event is being held at Northcote Town Hall (189 High Street, Northcote, VIC 3070)

For those wanting to come along here are the links you need to know.  Please go to the website and register to attend.

 

SQL Saturday 769 Pre-Con Training Options

There’s also 3 pre-con training sessions held the day before on Fri 29 Jun 2018.  Definitely work a look in…

Session: Building Streaming Data Pipelines in Azure…

For those attending – I am presenting a pretty fun session on Building Streaming ETL Pipelines Using Azure Cloud Services.  

Session Details here – http://www.sqlsaturday.com/769/Sessions/Details.aspx?sid=78630

We’ll talk though the various different shape, speed and size of data sources available to modern business today, and discuss various PaaS streaming methods available to ingest data at scale all using the Azure Cloud Platform.  I also have a few pretty fun demos which will aim to show how all the Azure services can tie together to perform ETL/ELT!

Feel free to pop in to have a chat!

 

I hope to see you all in Melbourne at SQL Saturday!


Disclaimer: all content on Mr. Fox SQL blog is subject to the disclaimer found here

Advertisements

Using Elastic Query to Support SQL Spatial in Azure SQL DW

[read this post on Mr. Fox SQL blog]

Recently we had a requirement to perform SQL Spatial functions on data that was stored in Azure SQL DWSeems simple enough as spatial has been in SQL for many years, but unfortunately, SQL Spatial functions are not natively supported in Azure SQL DW (yet)!

If interested – this is the link to the Azure Feedback feature request to make this available in Azure SQL DW – https://feedback.azure.com/forums/307516-sql-data-warehouse/suggestions/10508991-support-for-spatial-data-type

AND SO — to use spatial data in Azure SQL DW we need to look at alternative methods.  Luckily a recent new feature in Azure SQL DB  in the form of Elastic Query to Azure SQL DW now gives us the ability to perform these SQL Spatial functions on data within Azure SQL DW via a very simple method!

So the purpose of this blog is to show how to perform native SQL Spatial functions on data within Azure SQL DW.

Continue reading

Azure Cognitive Services API’s with SQL Integration Services Packages (SSIS)

[read this post on Mr. Fox SQL blog]

I had a recent requirement to integrate multi-language support into a SQL DW via a SQL SSIS ETL solution.  Specifically the SQL DW platform currently only supported English translation data for all Dimension tables, but the business was expanding internationally so there was a need to include other language translations of the Dimensional attributes.

We wanted to do this without having to manually translate English text attributes that exist already, or new ones that are added or modified over time.  We wanted an automated method that simply “worked“.

Enter Azure Cognitive Services Translator Text API service!

So the purpose of this blog is to outline the code/pattern we used to integrate the Azure Cognitive Services API into SQL SSIS ETL packages.

Continue reading

Database Backup Options for SQL on Azure IaaS

[read this post on Mr. Fox SQL blog]

Recently I had a requirement to collate and briefly compare some of the various methods to perform SQL Server backup for databases deployed onto Azure IaaS machines.  The purpose was to provide a few options to cater for the different types (OLTP, DW, etc) and sizes (small to big) of databases that could be deployed there.

Up front, I am NOT saying that these are the ONLY options to perform standard SQL backups!  I am sure there are others – however – the below are both supported and well documented – which when it comes to something as critical as backups is pretty important.

So the purpose of this blog is to provide a quick and brief list of various SQL backup methods!

Continue reading

Query Azure CosmosDB from a SQL Server Linked Server

[read this post on Mr. Fox SQL blog]

Recently I had a requirement to combine data that I already had in SQL Server (2016) with JSON document data already stored in Azure CosmosDB.  Both databases were operational and continuously accepting data so I didn’t want to go to the trouble of doing the delta load thing between them, instead I just wanted to be able to query directly on demand.

And so – the purpose of this article is to outline the method to connect direct to Azure CosmosDB from SQL Server using a SQL Linked Server.

Finally … SQL & NoSQL … together at last!

For those interested to learn more about Azure CosmosDB, check out my previous blog post here – https://mrfoxsql.wordpress.com/2016/05/11/azure-documentdb-preparing-loading-querying-data/

Or the official documentation here – https://docs.microsoft.com/en-us/azure/cosmos-db/

And so right up front – this solution only works for SQL Server on VM/IaaS – and is not supported for Azure SQL DB (ASDB) – mainly as ASDB doesn’t support SQL Linked Servers! (Damn, they say!)

Continue reading

Streaming Reporting: SQL Change Data Capture (CDC) to Power BI

[read this post on Mr. Fox SQL blog]

Extending on my previous post about redirecting SQL CDC changes to Azure Event Hub, I have had a few people ask for details/options to stream SQL data into the Power BI API.

Specifically – they were looking for an easy method to leverage the ADD ROWS functionality of the Power BI API so they could push real-time data into a Power BI service dataset.

This method provides the ability to update the Power BI Dataset with new rows every few seconds, instead of a Power BI report having to either use Direct Connect or Scheduled data refresh capability which can be very limiting.

If interested in how the SQL CDC and Event Hubs work together, then read here from my previous post – https://mrfoxsql.wordpress.com/2017/07/12/streaming-etl-send-sql-change-data-capture-cdc-to-azure-event-hub/

The purpose of this post is to quickly show how to extend and explore pushing new SQL data rows via Azure Stream Analytics into Power BI.

And so, lets get into some CDC to Power BI streaming action!

Continue reading

Streaming ETL: SQL Change Data Capture (CDC) to Azure Event Hub

[read this post on Mr. Fox SQL blog]

I had a recent requirement to capture and stream real-time data changes on several SQL database tables from an on-prem SQL Server to Azure for downstream processing.

Specifically we needed to create a streaming ETL solution that …

  1. Captured intermediate DML operations on tables in an on-prem SQL database
  2. Transmit data securely and real-time into Azure
  3. Store the delta changes as TXT files in Azure Data Lake Store (ADLS)
  4. Visualise the real-time change telemetry on a Power BI dashboard (specifically the number of Inserts, Updates, Deletes over time).

The first part was easy; SQL has a feature called Change Data Capture (CDC) which does an amazing job of tracking DML changes to seperate system tables.  If you dont know about CDC then see here – https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server

The second part wasn’t easy, and after some searching I came across this blog post by Spyros Sakellariadis which gave me inspiration and starter code for my streaming ETL solution.  Excellent post.  See here – https://azure.microsoft.com/en-us/resources/samples/event-hubs-dotnet-import-from-sql/

And so, the final architecture looks something like this…

The solution picks up the SQL data changes from the CDC Change Tracking system tables, creates JSON messages from the change rows, and then posts the message to an Azure Event Hub.  Once landed in the Event Hub an Azure Stream Analytics (ASA) Job distributes the changes into the multiple outputs.

What I found pretty cool was that I could transmit SQL delta changes from source to target in as little as 5 seconds end to end!

And so, lets get into some CDC to Event Hub data streaming action!

Continue reading

What Exactly is the “Data Platform” Nowadays?

[read this post on Mr. Fox SQL blog]

A couple of months ago I was presenting at SQL Saturday Melbourne (582) on Azure Cognitive Services and got chatting with some of the other presenters about our sessions.

I co-presented with Krissy Rumpff from Microsoft Data Platform Team (https://www.linkedin.com/in/krumpff/) – and for those interested our session is here – http://www.sqlsaturday.com/582/Sessions/Details.aspx?sid=56483    …or…    you can look at the recording here – https://channel9.msdn.com/Events/Ignite/Australia-2017/DA321

Anyway, whats interesting is that some of the other presenters were asking why we were presenting on Cognitive Services, when in fact this was SQL Saturday?  And, you know, Cognitive is not the Data Platform?

This is actually an interesting point – and since then I have had a pretty good think about what this means – and so this is the purpose of this blog post!

Continue reading

A Collection of SQL Server Help Scripts

[read this post on Mr. Fox SQL blog]

Like probably every SQL DBA, consultant, architect etc etc out there that has ever worked on or used SQL Server they will likely have their own personal collection of SQL Server Help Scripts.

So not unsurprisingly I also have such a collectionand so this is the purpose of this post!

Continue reading

SQL Saturday 582 Melbourne (11 Feb 2017) and Ignite 2017 (14-17 Feb 2017)

For those not aware there are 2 cool local and national events coming to both Melbourne and Gold Coast

  • SQL Saturday 582 (Sat 11 Feb 2017).  Melbourne.  For those looking for some great free local SQL (and related!) learning, you simply cannot go past a SQL Saturday anywhere in the world!  And this one right here in Melbourne will again be no exception.  There is a lineup of fantastic local speakers including Microsoft and MVP’s as well as international speakers too.  http://www.sqlsaturday.com/582/EventHome.aspx
  • Ignite 2017 (14-17 Feb 2017).  Gold Coast.  For those wanting more than a day and across a wider variety of topics, then Ignite will also be another you simply cannot miss.  As above there is a fantastic lineup of both local Microsoft and local MVP/experts as well as an impressive list of international speakers.  This is a 4 day back-to-back set of technical sessions. https://msftignite.com.au/

UPDATE: Ignite 2017 has come and gone – and so here’s some links!

 

I want to focus this short blog post on SQL Saturday 582 as there are some great things happening right here locally in Melbourne… 

SQL Saturday 582 – Melbourne

SQL Saturday is an excellent free learning resource for all things SQL Server – all costs are covered by donations and sponsorships.  Some of the excellent sponsors this year are Microsoft, Wardy IT, Idera and PASS.

Some of the session focus areas include SQL 2016 (many deep dives across almost all facets!), SQL DB/DW in Azure, Azure Cognitive Services and Machine Learning, R, Agile Methods, Power BI, Powershell, BIML …and more!

Furthermore – Some of the top Microsoft product group (PG) speakers will also be attending:

  • Lindsey Allen
  • Sunil Agarwal
  • Matt Masson
  • Julie Koesmarno
  • Ajay Jagannathan

Sunil will also be helping to run a SQL Clinic on the day where you can talk directly to Microsoft about your biggest pain points or suggestions for the next versions of SQL Server.

For those wanting to come along here are the links you need to know.  Please go to the website and register to attend.

The event is being held at Monash University (Caulfield Campus, 888 Dandenong Road, Caulfield East, Victoria)

 

The Rise of the Machines…

For those attending – I am co-presenting a pretty cool session with the awesome Krissy Rumpff (from local Microsoft SQL Team) on Azure Cognitive Services.  

Session Details here – http://www.sqlsaturday.com/582/Sessions/Details.aspx?sid=56483

We’ll talk though what they are, why you should care and how to interact with them (via API’s).  We hope to show some pretty fun demos and draw some relevant use cases – and if time permits will look for some unwilling audience participation!

Feel free to pop in and introduce yourself!

 

I hope to see you all in Melbourne at SQL Saturday!


Disclaimer: all content on Mr. Fox SQL blog is subject to the disclaimer found here