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.
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:
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.
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 firstname.lastname@example.org' 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.
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:
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.
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.
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:
A current trend that I can't say that I love is constant churn within software applications. I have no interest to go back to the days where we got a new version of SQL Server or Power BI, etc. every few years.
In fact, I thrive on change. However, I've now become really concerned about how anyone:
Learns to use a complex application
Remembers how to use a complex application when they don't use it daily
I first really struck this issue with Azure. If I was teaching a class that used Azure, I could check every single lab on Sunday night, then Monday morning, the students would find it had all changed. That's OK for an experienced person, but not OK for a learner.
I love the rate of change for Power BI. We're endlessly getting wonderful new things. But I have to say that every class that I teach on this is like a new experience. I've got another one this coming Tuesday. I used to look forward to them but now I have a major hesitation every time, as I wonder what parts of the labs will have broken.
This is now an ongoing challenge for all this type of software though. I helped create some labs for VSTS very recently, and when I look at the product now, it barely resembles the one that I built the labs on.
Is it better? Probably yes.
But even though it might have been a few months ago, it feels like just the other week, and yet, not only has the UI changed, entire concepts have been added or removed, and the order that things are done in has changed substantially.
I don't know the answer to this but the current rate of churn is a substantial issue.
I gather the plan with the DevOps guys is to put a set of labs on GitHub, and let people who are doing the labs point out the issues day by day as they strike them. Again, for experienced users that might work. But for newcomers, I really wonder if that's what they'll think.
Will they realize the app must have changed, and it's all different, or will they just think the product is too hard to use. Either way, they'll be very frustrated.
And while initial learning the product is one thing, I'm worried about it longer-term. A product like VSTS lets you set up automation and you hope you won't need to change it constantly. But if every time you go to make a change, you're struggling to use it like you're a newbie again, that's a problem.
Finally, I'm really concerned about ongoing support.
The vast majority of support of software applications today happens from community resources like blogs, webcasts, etc.
Will they continue to be created at the same pace if the authors know they'll be irrelevant or wrong within a very short time? How will end-users learn to do things when none of the online examples they find still work?
If you have ever tried to write scripts to configure operating systems, you'd realize just how hard that is to get correct.
For example, if you need to ensure that your virtual machine has two network adapters, and they need to have a specific set of IP addresses, how do you do that?
The traditional approach for PowerShell (and similar tools) was to try to write a step-by-step script to configure the network adapters the way you want. But where do you start? Do you write a script to check for any existing adapters and loop through them to try to remove them? Do you try to add the ones you want, and then remove the others?
You'll quickly realize that you get into very, very complex coding because you might not be able to be sure what your starting point is. Everything has to stay on the path that you prescribed.
And worse, what happens if you run this script more than once?
That's the problem with imperative code. You are telling the operating system the steps required for configuration.
We don't want to be doing this.
With a declarative approach, we tell the system how we want it to end up ie: the desired state, and let it worry about how to get configured that way. This is what we want to be doing instead of writing imperative code.
PowerShell offers DSC (desired state configuration) where you describe in a JSON file, the way you want the configuration to end up. A bonus in this approach is that it's idempotent ie: no matter how many times you run it, you end up with the same outcome.
It's important wherever possible to be doing declarative configuration not imperative configuration. In later posts, I'll talk more about how.
I’ve worked with T-SQL for a very long time, and over the years I’ve lost count of how many tools I’ve found the need to create to help me in my work.
They have been in a variety of script folders, etc. and whenever I go to use them now, I often have to decide which is the best version of a particular tool, as they’ve also been refined over time.
So I decided to get them into a clean clear shape and SDU Tools was born. I’ve grabbed a whole bunch of tools for a start, made sure they are pretty consistent in the way they do things, and published them within a single script. I figured I might as well also share them with others. They are free (I get you to register so I know what usage they are getting).
For each of the tools, I’ve also created a YouTube video that shows how to use the tool. I’m also planning to create blog posts for each tool so I have the opportunity to show things that won’t fit in a short video and ways of using them that might be less obvious.
I’ve got a big backlog of tools that I’d like to add so I’m intending to add in whichever ones I get reworked during each month. Likely areas in the upcoming months are further functions and procedures related to scripting objects, and also to code generation.
The tools ship as a single T-SQL script, don’t require SQLCLR or anything to clever, and are targeted at SQL Server 2008 and above. They are shipped as a single schema that you can place in whichever database (or databases) suits you best.
Of course there’s the usual rider that you use them at your own risk. If they don’t do what they should, I’ll be sad and then fix it, but that’s all
I hope you find them useful.
You can find out more about our free SDU Tools here:
There’s an interesting scenario that’s come up in creating some new samples for SQL Server 2016.
I know that for many, many years people have asked for a way to limit developer edition to only use standard edition features, or at least to have a way to flag them. I’ve often thought that I’d like a way to set a “target environment” and have the colorization change anything that I can’t use in standard to an obviously different color.
However, previously, if you used developer edition to create a database, as long as you didn’t use any enterprise features, you could then backup and restore that database to a standard edition server.
That’s no longer the case with SQL Server 2016.
If you create a temporal table in a database on developer edition, it automatically chooses PAGE compression for the history file. There is also no DDL to avoid this. The database can no longer be restored on a standard edition server. To work around it, you would need to rebuild the table without compression after it’s created.
I see this as quite a change in behavior. I don’t think that features that are only available in enterprise (or other perhaps Azure) editions should be “auto-injected”.
Clearly, if you script out the database and create a new database on standard edition using the script, it will create the history table without compression. But I suspect that many people create DBs as starter DBs using developer edition, and then deploy them to other editions that don’t support compression.
I’d be interested in thoughts on how common this practice currently is.