Press "Enter" to skip to content

Author: Kevin Feasel

How SQL Server Stores the Money Data Type

Published 2020-05-29 by Kevin Feasel

Randolph West wants to talk to us about how we use our MONEY:

While MONEY and DECIMAL(19,4) are functionally the same, they are stored differently on disk, and this is where it gets interesting. Let’s use a random amount of $ 4,513.19. Since it’s small enough to fit in both MONEY and SMALLMONEY, we can do a simple experiment. When we ask SQL Server to store this value in a MONEY data type, it will store it (byte-reversed) as 0x7CA8B00200000000. The SMALLMONEY version of this amount would look almost identical, stored as 0x7CA8B002 (without the leading zeroes). A quick look at this byte-reversed value (0x02B0A87C) in a hex calculator gives us the amount of 45,131,900. After moving the decimal point four places to the left, we get our starting value of 4513.1900.

But read on to see how that compares to other data types.

Comments closed

Stopping an ADF SSIS Integration Runtime

Published 2020-05-29 by Kevin Feasel

Andy Leonard shows how to shut off the Azure Data Factory SSIS integration runtime when you don’t need to use it:

I have blogged about stopping an Azure-SSIS IR in the past (Start Azure-SSIS, Execute Package, Stop Azure-SSIS). The pattern described in the Start Azure-SSIS, Execute Package, Stop Azure-SSIS post is sound and awesome. My only issue is the pipeline will report failure if the Azure-SSIS IR is not running when the Web Activity executes the Stop command. Mind you, the operation succeeds. But it reports failure.

Reporting failure when the operation succeeds seriously messes with my CDO (that’s OCD with the letters in the proper order).

Read on to see how to do this the best way.

Comments closed

Feeding Databricks Output to Azure SQL Database

Published 2020-05-29 by Kevin Feasel

Arun Sirpal takes us through the process of moving data from Databricks into Azure SQL Database:

Recently I got to a stage where I leveraged Databricks to the best of my ability to join couple of CSV files together, play around some aggregations and then output it back to a different mount point ( based on Azure Storage) as a parquet file, I decided that I actually wanted to move this data into Azure SQL DB, which you may want to do one day.

This isn’t just dropping files into Blob Storage and picking them up, but rather a direct integration.

Comments closed

TDE and Managed Instances

Published 2020-05-28 by Kevin Feasel

Tim Radney notes a change to the way new Azure SQL Database Managed Instance databases are configured:

Another recent change is that all newly created databases have “Encryption Enabled” set to True. This enables Transparent Data Encryption. By default, if you haven’t created your own key, it will use the service-managed key. A bug I’ve found in SSMS is that even though you set “Encryption Enabled” to false, it will still enable TDE. This is not the behavior when using T-SQL.

Read on to understand the ramifications of this change.

Comments closed

SQL Agent Job Control Based on AG/Mirroring Status

Published 2020-05-28 by Kevin Feasel

Eitan Blumin has an interesting solution for us:

Lo and behold, my new and improved scripts, which implement the following logic:

Create a single Master Control Job with an hourly schedule, and also set it to run when SQL Agent is started up. The Master Control Job will do the following:

Automatically detect which jobs have steps that run on databases that are involved in an HADR solution.

For each such job, automatically detect whether there’s at least one step run on a database which currently has the Primary/Principle role.

If so, make sure it’s enabled. Otherwise, make sure it’s disabled.

Create an alert for a role/state change event, if such doesn’t exist yet, and set it to run the Master Control Job when triggered.

Click through for more details and links to scripts for Availability Group and database mirroring scenarios.

Comments closed

Blob Storage Enhancements

Published 2020-05-28 by Kevin Feasel

James Serra is keeping on top of things around Azure Blob Storage:

Account failover: Customer-initiated storage account failover is now generally available, allowing you to determine when to initiate a failover instead of waiting for Microsoft to do so.  When you perform a failover, the secondary replica of the storage account becomes the new primary. The DNS records for all storage service endpoints—blob, file, queue, and table—are updated to point to this new primary. Once the failover is complete, clients will automatically begin reading from and writing to data to the storage account in the new primary region, with no code changes. Customer initiated failover is available for GRS, RA-GRS, GZRS and RA-GZRS accounts.  To learn more, see Disaster recovery and account failover

Read on for several more improvements.

Comments closed

Setting Default Parameters with Powershell

Published 2020-05-28 by Kevin Feasel

Jess Pomfret takes us through the main idea behind PSDefaultParameterValues:

I first heard about PSDefaultParameterValues from a PSPowerHour session by Chrissy LeMaire in 2018. After rewatching this recently, I realised she even mentioned this exact scenario. However, it took until I recently rediscovered this handy preference variable that it all clicked together.

PSDefaultParameterValues does exactly what the name suggests- it lets you specify default values for parameters. PSDefaultParameterValues can be set as a hash table of parameter names and values that will be used in your session for any function that can use it.  A simple example is the verbose parameter. If you wanted to turn on the -Verbose switch for every function you run you could add -Verbose to each function call, or you could set PSDefaultParameterValues.

Click through for a few examples of how you can use this.

Comments closed

Comparing Recurrent and Convolutional Neural Networks

Published 2020-05-27 by Kevin Feasel

Sameer Nigam explains the differences between convolutional and recurrent neural networks:

CNN and RNN are amongst most important algorithm of Neural Network family, also they differ in their network process and solving problems.

So talking about their differences:

CNN are used to solve classification and regression problems and RNN are used to solve sequence information.

Read on to see what Sameer means by this.

Comments closed

Exchange Demand Partitioning and Parallel Queries

Published 2020-05-27 by Kevin Feasel

Joe Obbish takes us through a fun concurrency problem:

Very little has been written about exchange operators with a partitioning type of demand, so I forgive you for not hearing of it before today. There is a brief explanation available here, an example of using demand partitioning to improve some query plans involving partitioned tables, and a Stack Exchange answer for someone comparing round robin and demand partitioning. You have the honor of reading perhaps the fourth blog post about the subject.

Read on for an in-depth look at the problem.

Comments closed

Personalizing Power BI Page Navigation

Published 2020-05-27 by Kevin Feasel

Marc Lelijveld shows how you can create custom tooltips per user in Power BI:

From an end user perspective, it is very user friendly to see that the report is adjusted to your personal preferences. Similar is the case for page navigation. If I am the country manager for Germany, I would prefer the report to be adjusted to my area.

With row level security you can filter the report down to only the specified area you want. But in this case, we want clarity over all countries. So, we do not want to filter the dataset itself, but only customize the end user interaction. For this setup, we will still use row level security, but in a different setup.

Click through to see how to do this.

Comments closed