The Journey to SQL Server 2019 – where does your own journey start?

[read this post on Mr. Fox SQL blog]

A couple of weeks back I presented the keynote at SQL Server Saturday Sydney, which was an absolute blast. It was my first keynote, so honestly I wasn’t quite sure what to talk about. Now I’ve been to many seminars, conferences, etc and seen many, many key notes – and so in the end I decided to have a bit of “light-hearted” fun and simply tell a story!

So I decided to talk about SQL Server – yes, I know its exciting – but moreso about how it is that we all got to be there in that very room that morning, listening to some guy present a keynote on SQL. And most importantly how everyone else in that room had their own related personal journey with SQL Server.

And so – this journey starts all the way back in 1989; 3 great friends got together one summer and built something AMAZING; SQL Server 1.0. And it ends some 30 years and 13 versions later with SQL Server 2019more amazing than anyone could have imagined when they cut that code all those years before.

So sit back, reminisce, and have fun!

Continue reading

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

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