Press "Enter" to skip to content

Month: November 2020

Migrating SSIS to Azure Data Factory

Published 2020-11-04 by Kevin Feasel

Koen Verbeeck has some articles for us:

For quite some time now, there’s been the possibility to lift-and-shift your on-premises SSIS project to Azure Data Factory. There, they run in an Integration Runtime, a cluster of virtual machines that will execute your SSIS packages. In the beginning, you only had the option to use the project deployment model and host your SSIS catalog in either an Azure SQL DB, or in a SQL Server Managed Instance.

But over time, features were added and now the package deployment model has been supported for quite some time as well. Even more, the “legacy SSIS package store” is also supported. For those who still remember this, it’s the SSIS service where you can log into with SSMS and see which packages are stored in the service (either the file system or the MSDB database) and which are currently running.

Read on for much more detail on the topic.

Comments closed

External Tables vs T-SQL Views in Synapse

Published 2020-11-04 by Kevin Feasel

James Serra explains the differences between external tables and T-SQL views in Azure Synapse Analytics when querying from Data Lake Storage:

A question that I have been hearing recently from customers using Azure Synapse Analytics (the public preview version) is what is the difference between using an external table versus a T-SQL view on a file in a data lake?

Note that a T-SQL view and an external table pointing to a file in a data lake can be created in both a SQL Provisioned pool as well as a SQL On-demand pool.

Here are the differences that I have found:

Click through for the differences.

Comments closed

Date and Time Functions in Cosmos DB

Published 2020-11-04 by Kevin Feasel

Hasan Savran walks us through date and time functions in Azure Cosmos DB:

Json does not have datetime data type, you need to keep the datetime information in string. This can be a problem for database engines specially if user needs to search by date or sort by date. Cosmos DB team introduced bunch of datetime functions to the Azure Cosmos DB database engine this month. You can read my older post about DateTime in CosmosDB if you like to know how Azure CosmosDB saves the datetime in documents. I will cover the new datetime functions in this post. Here is the list of the functions 

Click through for those functions and how you can use them.

Comments closed

Geospatial Analysis with Azure Data Explorer

Published 2020-11-04 by Kevin Feasel

Chris Webb continues along a theme:

Since last week’s blog post about dynamic M parameters generated so much interest, this week I thought I’d give you another example of something cool you can do with them when you’re using Azure Data Explorer (ADX) as a DirectQuery source in Power BI: geospatial analysis.

Let’s say you work for a chain of supermarkets and want to use Power BI see what other competing stores are close to one of your stores.

Read on for the rest of the story.

Comments closed

Understanding How GPS Works

Published 2020-11-03 by Kevin Feasel

Holger von Jouanne-Diedrich walks us through the basics of global positioning:

Last week, I showed you a method of how to find the fastest path from A to B: Finding the Shortest Path with Dijkstra’s Algorithm. To make use of that, we need a method to determine our position at any point in time.

For that matter, many devices use the so-called Global Positioning System (GPS). If you want to understand how it works and do some simple calculations in R, read on!

Do read the whole thing; the explanation is laid out really well.

Comments closed

Adding Libraries in Databricks

Published 2020-11-03 by Kevin Feasel

Arun Sirpal has some third-party libraries to add:

It is a really common requirement to add specific libraries to databricks. Libraries can be written in Python, Java, Scala, and R. You can upload Java, Scala, and Python libraries and point to external packages in PyPI, Maven, and CRAN repositories.

Libraries can be added in 3 scopes. Workspace, Notebook-scoped and cluster. I want to show you have easy it is to add (and search) for a library that you can add to the cluster, so that all notebooks attached to the cluster can leverage the library.

I’m hoping that loading libraries in Azure Synapse Analytics will, at some point, be this convenient.

Comments closed

Connecting to Postgres with PolyBase

Published 2020-11-03 by Kevin Feasel

I clear one blog post off my backlog:

Now that we have some data, let’s go back to SQL Server. I assume you’ve already installed and configured PolyBase—if not, check out my presentation on PolyBase. Note that this requires SQL Server 2019 or later, as that’s the first version which supports PolyBase to ODBC. Here’s a script which assumes a database named Scratch and a master key <<SomeSecureKey>>.

Click through for step-by-step instructions to get started, though I will freely admit that I don’t have the Postgres knowledge to give you a full listing of sharp edges.

Comments closed

Backing Up the Service Master Key

Published 2020-11-03 by Kevin Feasel

William Assaf takes us through backing up important keys in SQL Server:

You should consider complimentary backup solutions that backup/snapshot the entire server (or VM) for SQL Server, but sometimes these technologies are limited or have too much of an impact on the server. A whole VM snapshot for example that is reliant on VSS could incur an unacceptable long IO stun duration when it occurs. 

Regardless, in all cases, SQL Server backups of each database should be taken regularly. This is a conversation for another blog post but a typical pattern is weekly full backups, nightly differential backups, and in the case of databases not in SIMPLE recovery model, 15 minute transaction log backups.

Read the whole thing.

Comments closed

SSMS 18.7.1 Released

Published 2020-11-03 by Kevin Feasel

Glenn Berry takes us through the latest edition of SQL Server Management Studio:

One big change with SSMS 18.7 is described by Microsoft this way:

“Beginning with SQL Server Management Studio (SSMS) 18.7, Azure Data Studio is automatically installed alongside SSMS. Users of SQL Server Management Studio are now able to benefit from the innovations and features in Azure Data Studio. Azure Data Studio is a cross-platform and open-source desktop tool for your environments, whether in the cloud, on-premises, or hybrid.“

So far, this has been a pretty controversial change. Erik Darling created a User Voice suggestion on October 20th that has already gotten over 234 votes, and many comments.

I’m not going to weigh in too much here, though I would prefer this to be an optional installation. Do watch out for an annoyance, though, if you have Azure Data Studio installed as a User instead of System.

Comments closed