Microsoft Ignite US 2017 – Major Azure Announcements

Microsoft Ignite is probably the biggest technical event that Microsoft host yearly with many major announcements across the entire solutions portfolio suiteand this year 2017 was certainly no exception to that!

This year it was held in Orlando, FL over 5 days (25 – 29 Sep) and was attended by more than 30,000 people across the two major events of Ignite and Envision.  The event covers all areas of Microsoft solutions including Azure, Office, Power BI, SQL Server, Windows, Dynamics, etc, etc and is a world of technical goodness!

The announcements across the Azure Cloud space in particular are significant and very exciting, and provide a strong lead as to the direction Microsoft are taking their technologies today – and in the very near future.

I have prepared a summary deck of what I think are the major announcements specifically across the Azure Infrastructure and Data space which are important to be aware of.  There are of course even more announcements that this across other solutions areas I mentioned above that I haven’t covered in this deck.

You can download the Azure Data & Infra announcements deck from [MY PRESENTATIONS] page here on my blog site Ignite US 2017 Announcements – https://mrfoxsql.files.wordpress.com/2017/10/azure_igniteus2017_whatsnew_v0-21.pdf

 

In addition to all the technical goodnessSatya also released a new book called “Hit Refresh” which outlines the inside story of Microsoft’s own digital transformation.

Hit Refresh is about individual change, about the transformation happening inside of Microsoft and the technology that will soon impact all of our lives—the arrival of the most exciting and disruptive wave of technology humankind has experienced: artificial intelligence, mixed reality, and quantum computing.

You can read about it here – and also grab a copy if interested to learn more – https://news.microsoft.com/hitrefresh/

 

Happy reading!

…AND of course, as I always say, please review and validate this yourself as your required outcomes may vary!


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

Advertisements

Making Phone Calls from Azure Event Hub Messages

Recently I did a presentation at our local SQL Server User Group (SSUG) on Managing Streaming Data Pipelines Using Azure Data Services and as such wanted to build a compelling Azure demo that worked with simple streaming data which under certain event conditions would trigger an outbound phone call.

If interested the presentation deck is here – SSUG Melbourne – Building Streaming Data Pipelines Using Azure Cloud Services

The solution had several key components and stages outlined in the architecture below.

 

  1. A mobile phone app which generates JSON events with the X, Y, Z location of the device and G (g-force) detected in the device during movement.
  2. An Azure IoT Hub (AIH) which accepts the JSON events posted from the mobile device
  3. An Azure Stream Analytics (ASA) job that queries the Event Hub and routes the event data to several outputs, including…
    • Azure Blob Storage (for archive of all event data)
    • Power BI (for a live dashboard of all event data)
    • Azure SQL Database (ASDB) (for tabular storage of all event data)
    • Azure Event Hub + Azure Function (AF) (for queuing events which have a G Force reading greater than 3 and then triggering a phone call back to the original device from which the event originated)

The entire demo solution is actually really interesting (tsk, of course!) – and I will blog about other separate parts of this presentation at some point later.  However the part of the demo that received the most interest was the external phone call integration with Azure Functions.  

To be clear up front – Azure itself does not have native phone capability – so to make outbound phone calls I leverage an external “Twilio” API from within an Azure Function, and “Twilio” connects the outbound call.

And so, lets see the Twilio phone setup and c# Function code in action!

Continue reading

What Exactly is the “Data Platform” Nowadays?

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

Azure Cognitive Services Text Analytics – An API Calling Application

Just last week we had the fantastic opportunity to present at Microsoft Ignite 2017 in the Gold Coast on Azure Cognitive Services – and we had an absolute blast of a time!

I co-presented with Kristina Rumpff who works at Microsoft in the Data Platform team as a Solution Architect.  I focused on an overview of the suite of Azure Cognitive Services along with a deep dive into the Text Analytics service, and Krissy focused on the LUIS service coupled together with Azure Bots.

 

Fast Start to Azure Text Analytics Cognitive API’s

Leading up to, and since, the session I had a few people ask if there is anything pre-canned application wise which can call the Text Analytics API’s which they can just use.

The answer to that is kind of yes

However apart from that I didn’t find anything else out there which people can quickly leverage to do this for them… so I wrote one!

UPDATE:  I have since found this app handy myself when I need to quickly process a stack of once-off random text that someone sent to me for some other downstream reporting.  (…AND this was a good chance to learn more c# coding!)

And so, lets see the application code in action!

Continue reading

Azure Cognitive Services API’s with SQL Server 2016 CLR

Azure Cognitive Services is relatively new functionality within Azure that exposes some truly amazing APIs that have the ability to do some truly amazing things.

Before I dive into SQL and DLL code to make use of Cognitive Services, lets take a second to understand what I am talking about – imagine this;

  • A customer walks to an electronic kiosk in a shopping centre and says “Hi Cortana, I want to book a holiday, I really need a break from this bad weather.  Any ideas on where I should go?
  • The kiosk is run by a “bot” capable of conversing in 30 languages and which has been trained on understanding context and intent.
  • It recognises you as a 35 yo male who looks and sounds unhappy, and because it recognises your face and voice it knows it has talked to you before in another shopping centre last week when you asked for directions to a Surf Shop clothing store.
  • It also recognises a beach image on your T-Shirt, making note of the link between your previously asked directions and your clothing.
  • As you spoke in English, it replies in English – “Good to see you again.  Now, would you consider a beach holiday to Bali or Thailand?” – Why beach? Well the recommendations engine has determined that is where 35yo male surfer types go when the local weather is bad!
  • You negotiate a package using natural language, and close out the conversation.
  • The “bot” visualises your increased sentiment from the initial baseline and says “I’m glad I could make your day better! Enjoy your flight next week!

It may sound futuristic – but this is exactly what Cognitive Services (API’s) can do right now – and in my example I have only used 6 out of the 21 Azure Cognitive Services!  Microsoft Research has built these powerful Azure ML Models and wrapped them up into a single, simple, consumable publicly available API.

Some other amazing deployments for Cognitive Services

  • At a trade show, or even a window display at a shopping centre, a company could use emotion detection to see how people are reacting to their products.
  • Facial recognition could be used to find missing children quickly at an amusement park.
  • The APIs can determine the male:female ratio and ages of patrons at a nightclub, and identify VIPs or banned guests.
  • The object recognition capabilities can enable a blind person to read a menu in a restaurant or have their surroundings described to them

For those not familiar with Azure Cognitve Services APIs, check out this link which has online demos you can try – https://www.microsoft.com/cognitive-services/en-us/apis

For those not familiar with the Azure Bot Framework, check out this link – https://dev.botframework.com/

Anyway – despite all this, for this post today we’ll just focus on something pretty simple – making usage of the Text Analytics API right within SQL Server 2016.

And so, lets get to scoring some sentiment!

Continue reading

Azure SQL IaaS – TempDB Performance on SSD vs Premium Disk

When it comes to deploying SQL Server onto an Azure IaaS VM, it is sometimes difficult to know the best option to deploy the SQL Server tempdb database for your workload.

In many of the SQL templates on the marketplace it is often deployed to the C:\ by default after which you should redeploy the database to either D:\ (local SSD) or to an attached premium disk (P10, P20, P30).  The Microsoft SQL on IaaS Performance Best Practice article states both are possibilities under certain circumstances, however it does not provide empirical workload evidence as which to use when.

For those who have not seen the article – read here – https://azure.microsoft.com/en-us/documentation/articles/virtual-machines-windows-sql-performance/

The key comment of interest is this…

For D-series, Dv2-series, and G-series VMs, the temporary drive on these VMs is SSD-based. If your workload makes heavy use of TempDB (e.g. for temporary objects or complex joins), storing TempDB on the D drive could result in higher TempDB throughput and lower TempDB latency.

…and this…

So I thought lets test a OLTP type SQL workload!

AND SO – lets do some testing to validate this puppy!

Continue reading

Azure DocumentDB – Preparing, Loading and Querying Data

 

Recently I have been using Azure DocumentDB (aka CosmosDB) to validate several business use cases for a variety of application purposes.

For those SQL DBA’s and others who are new to Azure CosmosDB, its a recent entrant to the NoSQL document database world, and as its a PaaS document database cloud service it has the agility, scalability and availability of the Azure Cloud.

Being a schema-less Azure PaaS “document database” for my use case I wanted to verify…

  • basic costing and performance levels
  • methods to create valid JSON documents from SQL Server
  • methods to load JSON documents into Azure CosmosDB
  • performing basic like-for-like document query comparisons with SQL Server

Some homework reading for those interested…

 

22 May 2017 [EDIT]

As hinted above, Microsoft have just recently added significant new functionality and also formally renamed Azure DocumentDB as Azure CosmosDBa major evolution of the NoSQL database engine.  The details on the renamed service is here – https://azure.microsoft.com/en-au/blog/dear-documentdb-customers-welcome-to-azure-cosmos-db/

 

And so… let get into the belly of Azure DocumentDB CosmosDB!

Continue reading

Compression Performance with Low Key Selectivity

When it comes to enabling data compression in SQL Server I suspect most people don’t do a lot of testing as to the impacts of either enabling compression and its ongoing maintenance.  I assume most are interested in its ongoing performance for queries, and you know that’s damn fair enough too!

If you read the Microsoft Data Compression Whitepaper (and I mean whos got time to do that!) then it has some interesting technical tidbits burried deep in there which affect the impacts of applying and managing data compression.

https://technet.microsoft.com/en-us/library/dd894051(v=sql.100).aspx

Here are some of the ones of interest…

SQL Server uses statistics on the leading column to distribute work amongst multiple CPUs, thus multiple CPUs are not beneficial when creating, rebuilding, or compressing an index where the leading column of the index has relatively few unique values or when the data is heavily skewed to just a small number of leading key values – only limited effective parallelism will be achieved in this case.


And this…

Compressing or rebuilding a heap with ONLINE set to ON uses a single CPU for compression or rebuild. However, SQL Server first needs to scan the table—the scan is parallelized, and after the table scan is complete, the rest of the compression processing of the heap is single-threaded.


And this…

When a heap is compressed, if there are any nonclustered indexes on the heap, they are rebuilt as follows:
(a) With ONLINE set to OFF, the nonclustered indexes are rebuilt one by one.
(b) With ONLINE set to ON, all the nonclustered indexes are rebuilt simultaneously.
You must account for the workspace required to rebuild the nonclustered indexes, because the space for the uncompressed heap is not released until the rebuild of the nonclustered indexes is complete.

 

That first one is a cracker – it hit me once when compressing a SQL Server table (600M+ rows) on a 64 core Enterprise SQL Server.  After benchmarking several other data compression activities I thought I had a basic “rule of thumb” (based on GB data size and number of rows)… of which just happened to be coincidence!

This also begs the question of why would you use low selectivity indexes?  Well I can think of a few cases – but the one which stands out the most is the identification of a small number of rows within a greater collection – such as an Index on TYPE columns (ie; [ProcessingStatusFlag] CHAR(1) = [P]rocessed, [U]nprocessed, [W]orking, [F]ailed, etc)

… AND SO – lets do some testing to validate this puppy!

Continue reading

A “Business Intelligence” (BI) Definition

Over the years I have presented many times to various clients describing Business Intelligence (BI) solutions using the Microsoft BI solutions stack.

In all of my sessions, regardless of the specific focus, each time I always start with the same 2 content slides.

  • The first is always the positioning graphic from the Gartner Magic Quadrant for Business Intelligence & Analytics.  This shows all top BI vendors and how they are positioned against each other, and is republished yearly (typically in Feb).  This is often a great introduction as to why Microsoft BI is such a great play. If interested an introduction to the paper is here, but you can also find the full report on various web sites (https://www.gartner.com/doc/2989518/magic-quadrant-business-intelligence-analytics)
  • The second is always a definition of exactly what Business Intelligence (BI) actually is, and this is the purpose of this short blog.

And so, lets dig down into a business definition for Business Intelligence!

Continue reading

Calculating Table Partition Sizes in Advance

Continuing on with my Partitioning post series, this is part 7.

The partitioning includes several major components of work (and can be linked below);

  1. partitioning large existing non-partitioned tables
  2. measuring performance impacts of partitioned aligned indexes
  3. measuring performance impacts of DML triggers for enforcing partitioned unique indexes
  4. rebuilding tables that are already partitioned (ie. apply a new partitioning scheme)
  5. implementing partial backups and restores (via leveraging partitions)
  6. implementing partition aware index optimisation procedures
  7. Calculating table partition sizes in advance

This blog post deals with calculating partitioning sizes in advance.

Sometimes (just sometimes) you need to calculate the size your table partitions upfront before you actually go to the pain and effort of partitioning (or repartition) a table.  Doing this helps with pre-sizing the database files in advance instead of having them auto-grow many many times over in small increments as you cut data over into the partitions.

As a quick aside…

  • The negative performance impacts of auto-shrink are universally well known (er, for DBA’s that is!), however I rarely hear people talk about the less universally well known negative performance impacts of auto-grow quite so much.
  • Auto-Growing your database files in small increments can cause physical fragmentation in the database files on the storage subsystem and cause reduced IO performance.  If you are interested you can read about this here https://support.microsoft.com/en-us/kb/315512

Now – back to what I was saying about pre-sizing table partitions…!

I prepared a SQL script which given some parameters can review an existing table and its indexes (whether they are already partitioned or not) and tell you what your partition sizing breakdown would be should that table be partitioned with a given partition function.

I wrote it just for what I needed but it could be expanded more if you are feeling energetic.  The script is at the end of this post.

And so, lets get into the nitty gritty of this estimation script!

Continue reading