A Fine Slice Of SQL Server
Keshav Kiran performs a migration:
One of our customers came up with a requirement where they wanted to Migrate On-prem Database to Azure SQL Managed instance. The databases had traditional column level encryption enabled.
He has restored the database on the SQL Managed instance by Backup/Restore approach. Now when he was trying to read the encrypted column on the destination database, It was showing NULL values after decryption.
Read on for the solution.
Comments closedGilbert Quevauvilliers is missing something:
I have enabled the new On-Object Interaction for the formatting pane in Power BI and while it is constantly improving there are times when I would like to have the good old formatting pane available.
I have also found that sometimes when you create a new visual there is no option to format it as shown below.
There’s a workaround to this, so check it out.
Comments closedAndy Yun has started a new series:
Recently, I had the pleasure of delivering a new presentation called How to Accelerate Your Database Backups for MSSQLTips.com. This blog series is intended to be a companion piece, particularly for those who prefer to read content instead of watching a video.
In this first post, Andy describes the threads and queues involved with taking a backup.
Comments closedChristian Lorentzen has released a new package:
Version 1.0.0 of the new Python package for model-diagnostics was just released on PyPI. If you use (machine learning or statistical or other) models to predict a mean, median, quantile or expectile, this library offers tools to assess the calibration of your models and to compare and decompose predictive model performance scores.
This looks like a really useful package, so check it out.
Comments closedSasa Popovic creates some clones:
Database copy and database move operations for Azure SQL Managed Instance are very convenient in various situations when you want to copy or move database from one managed instance to another in an online way. What does online mean in this context? It means that the database on destination managed instance will be identical to the source database at the moment when operation is explicitly completed by user action. Copying a database is a size of data operation, and you can expect copy will take some time, but what is important and convenient, unlike point in-time restore where database is in state from some point in time in the past, with database copy you get database in state as it was when the operation was completed.
Read on to see how you can set this up for an Azure SQL Managed Instance.
Comments closedRobert Cain tries out some window functions:
The Kusto Query Language includes a set of functions collectively known as Window Functions. These special functions allow you to take a row and put it in context of the entire dataset. For example, creating row numbers, getting a value from the previous row, or maybe the next row.
In order for Window Functions to work, the dataset must be serialized. In this post we’ll cover what serialization is and how to create serialized datasets. This is a foundational post, as we’ll be referring back to it in future posts that will cover some of the KQL Windowing Functions.
Read on to see how to serialize data, what the risks of serialization are, and then how to generate a row number in KQL.
Comments closedAngela Henry takes it out of the user’s hands:
A little background for those new to using Power BI and Data Gateways. If the data source for your Power BI dataset lives on-prem or behind a private endpoint, you will need a Data Gateway to access the data. If you want to keep your data fresh (either using Direct Query or Import mode), but don’t want to rely on a specific user’s credentials (because we all want to go on vacation at some point), you will need to use a service principal for authentication.
Read on for the step-by-step instructions on how to do this.
Comments closedPhil Factor maintains some tests:
When learning about relational databases, we all tend to use ‘toy’ databases such as
Pubs,AdventureWorks,NorthWind, orClassicModels. This is fine, but it is too easy to assume that one can then do real-world database development in the same way. You have your database full of data and just cut code that you then test. From a distance, it all seems so easy.In fact, rapid and effective database development usually requires a much more active approach to data. You need to work out how to test your work as you go, and to test continuously. For that, you need appropriate data with the right characteristics, in the suitable quantity. You also need to plan how to ensure that, when you make changes to the database, or even minor changes to its settings, all business processes continue to work correctly. In Agile terms you need a test-first methodology, fast feedback loop, and iterative development. You should never cut some SQL Code and only then think to yourself “I wonder how I’ll be able to test this?“.
This is something I’ve historically been pretty lazy about, to my detriment. Phil does an outstanding job of making the case for why generating and working with your own test data (versus live data) is important, as well as categorizing the purposes of this test data and the types of tests you’ll want to have.
Comments closedMarco Russo and Alberto Ferrari do some slicing and filtering:
Slicers with too many values might be inconvenient for users, as they must search for the desired selection among too many lines. In such cases, a common solution is to build a hierarchy and use slicers with multiple columns inside, or multiple slicers, each with one column. However, this solution works only in structures with a natural hierarchy, like continents and countries. Indeed, each country belongs to only one continent so the hierarchy can be easily created with a new column.
If the hierarchy is non-natural, the relationship between the parent and the children is many-to-many, requiring a specific type of relationship.
Click through to see what that relationship looks like and how you can build it.
Comments closedKendra Little gives an answer:
Have you ever received advice that was technically correct, but which was delivered in such a way that it was too hard to understand?
I think of this as “accidental bad advice,” because it leads to confusion. There’s a LOT of accidental bad advice out there on index maintenance for SQL Server and cloud versions like Azure SQL, even in the official documentation.
In this post I’m answering a common index maintenance question, and we’re going to keep it simple.
The answer is essentially the same as it would be on-premises: yes, but perform index maintenance when it is appropriate. Read on to learn what that means in this case.
Comments closed