Making a cloud transformation, not just a migration – free course

In the dying days of PASS, one of the attempts at keeping the organization afloat was the introduction of the PASS Pro membership. One benefit of that was access to a series of Learning Experiences. Since the demise of PASS, our friends at Red-Gate have purchased the assets, and that includes those courses.

I was pleased to hear from the Red-Gate team that the courses have now been made available for free, and without any type of paywall/loginwall. That's just awesome.

The course that I built for them was on making a cloud transformation, instead of just a migration. So many times, I see CIOs/CTOs going on and on about the cloud transformation they are embarking on, yet I don't see them transforming anything. The cloud vendors love it, as it's an apparently quick way "to the cloud" for an organization. But for the organization, it often leads to lousy outcomes.

You can now see that course here.

The course is about 3 1/2 hours in length.

You'll find the other courses in that series here.

And did I mention free ?

 

SQL: GREATEST and LEAST – awesome new additions to Azure SQL Database

I still get really excited when T-SQL has new language elements added. I'm not talking about new data definition or management statements. I'm talking about new functions and logic. So, as expected, I was really happy when I saw some posts saying that GREATEST and LEAST now seemed to be working in Azure SQL Database.

They've been in other database engines before but weren't part of T-SQL.

I tried them, and sure enough, they work as expected. NOTE: Intellisense hasn't caught up yet, so they're still flagged as syntax errors.

GREATEST and LEAST

GREATEST takes a list of values and returns the largest. LEAST takes a list of values and returns the smallest.

Note that both ignore NULL values, and unlike aggregates, don't produce any message about the NULL value being ignored either:

That's how you'd hope it would work.

This is really good news. It's so common to want to choose values on this basis. For example, if I want to offer a customer the lowest price i.e. either their own special price, a sale price, or the normal price:

The Old Way

Before these functions existed, doing the above either required a convoluted bunch of CASE statements or a potentially even less obvious OUTER APPLY statement:

And that would get messier and messier if you needed to do it for multiple columns.

This is really good news. Thank you SQL Server team. Hope it comes to an on-premises version soon.

SDU Podcast: Show 80 with guest Pedro Lopes is now available

I was really pleased to get to record a SQL Down Under podcast with an old friend Pedro Lopes recently. Pedro is a Principal Program Manager with Microsoft in the Azure Data SQL Server area.

A year or so back, I had Joe Sack on the show telling us where Intelligent Query processing was heading, and Pedro now fills us in on where it's got to.

We recorded the show earlier in this month but I couldn't release it until after the PASS summit where some of the features were announced.

I hope you enjoy it.

You'll find that show, and all previous shows here.

 

SQL: PASS Learning Experience on Making a Cloud Transformation

I've been in so many companies lately where new CTOs and CIOs claim to have a cloud focus, but all they want to do is migrate all their existing systems from on-premises VMs to cloud-based VMs. They talk about making a cloud transformation but they're not transforming anything.

I was pleased to get a chance to create a short educational series for the people at PASS to cover some of my thoughts on how to make a real transformation, not just a migration.

You'll find info on it here: https://www.youtube.com/watch?v=Hq-yYQY5eLo

 

SQL Down Under Podcast 79 with Guest Mark Brown

Hi Folks,

Just a heads-up that we've just released SQL Down Under podcast show 79 with Microsoft Principal Program Manager Mark Brown.

Mark is an old friend and has been around Microsoft a long time. He is a Principal Program Manager on the Cosmos DB team. Mark is feature PM for the replication and consistency features. That includes  its multi-master capabilities, and its management capabilities. He leads a team focused on customers and community relations.

In the show, Mark provides a background on Cosmos DB for those less familiar with it, and discusses the latest licensing changes, including the new free tier. Along the way, he gets into detail on the multi-mode nature of Cosmos DB, and its consistency, availability, and indexing stories.

You'll find the show and podcast subscription details if you want those,  here: http://podcast.sqldownunder.com

 

ADF: Changing or removing your Git connection from an Azure Data Factory

I've been working a lot with ADF (Azure Data Factory) again lately. If you've started to work with ADF and aren't using Git in conjunction with it, you need to change that now.

ADF objects are also represented as JSON objects and lend themselves nicely to being stored in Git.

Another key advantage is that if you don't have Git connected, when you're working with ADF, you don't have a Save button. If you're part way through making changes, you can either Publish them or Discard All. When you have Git in place, you get an option to Save.

NOTE: If I'm working at a site that doesn't have Git connected, if I need to temporarily stop working on something, what I can do is get the JSON code for the pipeline (or whatever I'm working on), save it away manually, then discard my changes. At least I can then put it back again later. But that's basically what the Git option does for you anyway, so just use it.

Changing or Removing the Git Connection

Today was the first time I'd needed to change the Git connection across to another repository. And it took me a while to work out how. You'll notice that in the main editing window, there's no option to change it:

Now I think it should be there. But if not, it should be on the Properties page for the ADF instance. But it's not there either:

Now I think it should be in one of those two locations.

So where is it?

In one of the least obvious bits of UI ever created, it's tucked up in the top right corner of the Dashboard. That's the last place I would have looked. That page is usually useless to me. It's great if you've just started, as it has lots of links to tutorials, etc. but otherwise, not so interesting. But that's precisely where the option is:

I hope that helps someone.

 

SQL: Fix – Login failed for user 'NT AUTHORITY\ANONYMOUS' Azure SQL Database Error 18456

At one of my customer sites, I recently started having an issue logging onto an Azure SQL Database. The customer had configured Azure Active Directory (AAD) and were using Multi-factor Authentication (MFA).

I had previously been using it OK.

I would connect to the server using SSMS, enter my username and password, and then be prompted for the MFA authorization. After I authorized the logon, I would then receive the error shown in the main image: Login failed for user 'NT AUTHORITY\ANONYMOUS' and Error 18456.

Now when I've seen this error before with on-premises SQL Server, it's usually a Kerberos problem or a double-hop problem. But this was direct to Azure SQL Database.

Same error occurred on 17.9.1 and 18.3 for SSMS, and for Azure Data Studio.

Turns out that one of the admins at the site had removed my database user (and others) from the master database, AND, that was my DEFAULT database. That then led to this error. Putting the user back into master fixed the issue.

Other Causes

While researching this error though, I came across some other potential causes. The main one was that if you need to connect to a specific database, not to master, you need to ensure that you've typed in (i.e. not tried to select) the appropriate database name on the Options tab when trying to log in. If you have no database, or have the wrong one there, you'll get the same error.

Now one other error was worth noting. There used to be a text box and checkbox on the Options tab that let you enter the domain suffix for your AAD credentials. What you need to do now, is to enter your full email address (AAD credential) as the Username after selecting to logon using MFA. If you don't do that, again all goes but then right at the end, you get this error:

The critical part is that it says that the 'User yourname@yourcompany.com' returned by service does not match user 'yourname' in the request. If you see this, it means that you didn't include the full address in the Username box.

DevOps: Are you centralizing your log files?

System configurations are becoming more complex all the time. Each and every server, container, and key application and service today has log files that tell you a wealth about what's going on under the covers. But how accessible are those log files in your organization?

If you aren't using a log management tool, you probably should be.

Here are a few easy ones to get started with:

Azure Monitor

One thing that I do find frustrating with Microsoft tooling at present is the constant churn of product names. A while back, we had Application Insights that could collect details of what was happening within an app. The data for that was stored in a tool called Log Analytics, and it could also collect operating system logs and more. Agents were provided for on-premises systems.

Originally, these tools had different query languages but eventually, the query language for Log Analytics was one that's used. It's awesome to be able to write a query to simply find and filter log details.

For my SQL Server buddies, there were SQL Insights which has now morphed into SQL Server Intelligent Insights along with Azure SQL Database Intelligent Insights. These allow you to capture bunches of info about your SQL Server instances and databases so very simply.

I constantly visit client sites where they have purchased tools for this, and those tools aren't even close to being as useful as these Azure ones. And they don't just work with Microsoft servers and services.

Anyway, these have now all been bundled up again under the name Azure Monitor.

Azure Monitor also offers built-in integration with popular DevOps, issue management, ITSM and SIEM tools. You can use packaged solutions for monitoring specialised workloads, or build your own custom integration using Azure Monitor REST APIs and webhooks.

Papertrail

Another interesting offering from our friends at SolarWinds, is Papertrail. Their claim is "Frustration-free log management. Get started in seconds.
Instantly manage logs from 2 servers… or 2,000". Papertrail seems to be gaining a stronghold in the Linux, MySQL, Ruby, Apache, Tomcat areas along with many others.

In the end, if you aren't using one of these types of tools, you probably should be.

 

SDU Tools: Create a linked server to Azure SQL Database from SQL Server

When I need to move small amounts of data between an on-premises SQL Server system and an Azure SQL Database, or run queries that involve both systems, the easiest option at present is to create a linked server to Azure SQL Database.

And the easiest way to do that (or if you can't remember the commands) is with one of our free SDU Tools for developers and DBAs, called CreateLinkedServerToAzureSQLDatabase. You can read it to check out the code, or use it like this:

The parameters are as follows:

@LinkedServerName sysname – name that will be assigned to the linked server
– defaults to AzureSQLDB
@AzureSQLServerName nvarchar(max) – name for the Azure SQL Server eg: myserver.database.windows.net
@AzureSQLServerTCPPort int – port number for the Azure SQL Server (defaults to 1433)
@AzureSQLDatabaseName sysname – name of the database (defaults to master)
@RemoteLoginName sysname – login name for the Azure database
@RemotePassword nvarchar(max) – password for the Azure database
@SetCollationCompatible bit – is the remote server collation compatible (default is true)
@SetRPCIn bit – should rpc (remote procedure calls = stored procedure calls) be allowed (default is true)
@SetRPCOut bit – should rpc output be allowed (default is true)

Once you've done that, you will see it in the list of Linked Servers as in the main image above. In that image, I've created one called AzurePublicData. It contains a database called PublicData, and the database contains a number of tables.

You can then query the tables using four part names:

You can see it in action here:

To become an SDU Insider and to get our free tools and eBooks, please just visit here:

http://sdutools.sqldownunder.com

Shortcut: Import and Export Settings in SQL Server Management Studio

Whenever I need to work on a new laptop or server, or whenever I change versions of SQL Server Management Studio, I kick myself for not remembering to export my settings, so I can import them again.

I spend quite a bit of effort getting SSMS configured the way I want, so it only makes sense to save the settings. Saving them isn't perfect but it's far better than not having done it.

From the Tools menu, choose Import and Export Settings:

As an example, let's export the settings. I'll choose Next:

Notice that it's not an all or nothing export. I can choose details of which settings or groups of settings to export.

In this case, I wanted all of them, so I just need to pick a name and a location:

And next time I change to a different machine or new version, I can just import them and pat myself on the back for remembering.