Press "Enter" to skip to content

Category: Administration

Getting Started with KQL

Published 2022-01-04 by Kevin Feasel

Steve Jones starts learning about the Kusto Query Language:

I saw an episode of Data Exposed with my good friend, Hamish Watson. He talked about KQL (Kusto Query Language) being the next query language you need to learn. I was skeptical of the title, but I decided to give this a try.

In the episode, Hamish points out a cheat sheet from Microsoft, which I thought was a good resource. However, while watching the video, I browsed over to the demo site Microsoft has at https://aka.ms/lademo. You need an Azure account to log in, but this is a demo site where you can query some Log Analytics data. The new query window below is what appears when you go here:

If you’re already familiar with the way Splunk’s filtering language works, KQL follows from it. It’s a worthwhile language for Azure-based administrators to know, as it’s the most powerful way to get data out of Log Analytics.

Comments closed

Cleaning SQL Express Databases

Published 2021-12-31 by Kevin Feasel

Kevin Hill knows the pain:

I was contacted by a lawyer that was using a 3rd party application to store emails, keep track of time, etc.

The backend of the application is SQL Server Express edition, which has a hard limit of 10GB for the data file.

One quick note for people with lots of LOB data, remember to reorganize with LOB_COMPACTION = ON as that’s the only way to be sure. Also, depending on how old the version of SQL Server is, there was a bug with LOB compaction which affected SQL Server 2014 and earlier. But, uh, hopefully you’re patched past that point…

Also, getting up to 2016 SP1 means that Express Edition gets data compression. It wouldn’t directly help in this case, but if you have a lot of non-LOB data on Express Edition, it can work wonders, for some definition of “wonders.” After all, if you’re using Express Edition, wonders are by definition pretty small.

Comments closed

Reference to Database Not Supported in this Version of SQL Server

Published 2021-12-29 by Kevin Feasel

Diego Nieto-Arroyo troubleshoots an issue:

In this article I will show how to resolve and overcome errors while attempting to query a system tables or any table for that matter, via Linked Servers. The error seen below is the result of the issue we encountered.

Msg 40515, Level 16, State 2, Line 1

Reference to database and/or server name in ‘xxxx.sys.sp_tables_info_90_rowset_64’ is not supported in this version of SQL Server.

Read on to see what causes this issue and how you can resolve it.

Comments closed

Operation Requires Server to be a Registered Server

Published 2021-12-23 by Kevin Feasel

Garland MacNeill finds one way to solve a problem:

Anyway, it’s been a while since I worked on this AG and I need to get the migration/upgrade done. As I was working on configuring jobs, I ran into a problem where the AG node (as a target node) wasn’t downloading jobs from the Master node, in fact, the last poll was in July.

When I tried to force a poll, I was met with an error message that the server wasn’t registered, never mind it was clearly listed as a target server. Google didn’t find anything useful, other than some questions from 2013. I did come across the syntax to forcefully eject the server as a target with SQL. 

Read on to see how and what to do in the aftermath.

Comments closed

SERVERPROPERTY() and DATABASEPROPERTYEX() Views

Published 2021-12-20 by Kevin Feasel

Andy Mallon provides a public service:

The thing I hate the most about these two functions is that you need to know the right magic spells to make them work. Let’s look at SERVERPROPERTY() first. The syntax for the function is SERVERPROPERTY( 'propertyname' ), which is easy enough syntax, but the list of values for propertyname isn’t discoverable from SQL Server metadata, DMVs, or even IntelliSense. Instead, I need to check the docs for the list of allowable values. These property names are essentially magic words, and I need to check my spell book to make sure I get it right.

Invalid values for propertyname just return NULL–which is easy enough to handle, but also means your code will compile and run, but might do unintended things if you get your magic spell wrong, due to a typo.

Click through for Andy’s solution to the problem.

Comments closed

Reviewing Azure Options for PostgreSQL and MySQL

Published 2021-12-17 by Kevin Feasel

Maria Zakourdaev has a pair of info sheets. First up is Azure Database for MySQL:

MySQL is an open-source relational database that is widely used for web applications, it’s easy to use, reliable, secure, and fast.

Recently Microsoft have announced a new deployment option, Flexible Server, that is now generally available.

If we have a quick look at the available options, we now have Single Server and Flexible server deployment options.

Then we have Azure Database for PostgreSQL:

PostgreSQL is an open-sourced, feature rich and extendable relational database that handles high concurrency workloads easily. It supports complex structures, many advanced data types, Search Tree indexes and also got highly sophisticated query optimizer.

Azure Database for PostgreSQL is an Azure managed services running PostgreSQL community edition. With Flexible Server announced recently, you now have 3 deployment options: Single Server, Flexible Server and Hyperscale/Citus.

Click through for a quick comparison of each available option.

Comments closed

Log Replay for Azure SQL Managed Instance

Published 2021-12-17 by Kevin Feasel

Joey D’Antoni has some quick notes on the Log Replay Service:

Recently, I’ve started on a project where we are migrating a customer to Azure SQL Managed Instance, which now supports a few different migration paths. You can simply backup and restore from a database backup, but you can’t apply a log or differential backup to that database. You can also use the Database Migration Service, but that requires a lot of infrastructure and Azure configuration. The log replay service, or LRS, is the functional equivalent of log shipping to your new managed instance database. While log shipping is a well known methodology for both database migrations or disaster recovery. However, the implementation is a little different–let’s talk about how it works.

Click through to see how it differs.

Comments closed

Copy Logins between SQL Server Instances

Published 2021-12-17 by Kevin Feasel

David Alcock wants to move a login:

Migrating SQL databases is fun, depending on your definition of fun that is. The process can involve having to move things such as login details that have been around for that long that nobody has a clue what they are anymore.

With domain accounts that’s pretty straightforward, the passwords are managed in Active Directory and not held in SQL Server and it’s just a case of recreating the account on the new instance.

SQL authentication is different and migrating an account as is means you also have to recreate the password as is which could be difficult if you didn’t know what the password should be. It’s worth saying at this point that the preference should always be to use domain accounts, they’re more secure and much more manageable and migrations are ideal opportunities to refactor things to be better but for the sake of this article let’s proceed with the scenario of recreating a SQL authentication login with an unknown password, and we’ll need to get creative.

And as you’d expect, dbatools makes a dramatic appearance.

Comments closed

Finding SQL Servers with the MAP Toolkit

Published 2021-12-14 by Kevin Feasel

Lee Markum goes to active sonar:

A full tutorial on using the MAP Toolkit is available here on Microsoft Learn. MAP Toolkit installation is fairly straight forward but is also included in the  tutorial from Microsoft that I linked to above. You can install it on your own desktop and then scan Active Directory for SQL Servers. Please be sure to notify your System Administrators BEFORE you run the scan as it will set off intrusion detection alarms.

Sure, it’s no nmap sweep, but any blog post which includes a warning like that is worth the read.

Comments closed