DKIM - What is that?

This week, one of our client has asked to sign their emails which goes out from our software with DKIM. That's the first time I came across that term. So I got started with Google.

Found good explanation of what is DKIM in this article by CloudFlare.

DKIM stand for Domain Key Identified Mail. It is a mechanism use to reduce spam emails.

DKIM uses digital signature to identify the email is actually coming from the said domain. It has two parts:

• DNS record
• DKIM header included in the email

Email servers use DNS record with conjunction with DKIM header in the email to authenticate the email.

DNS record contains the public key for the signature.

DKIM header is the email signed by the email provider with the private key.

I will update this article when I get the chance to implement in our software.

Here is how it setup - Part 2

SQL Server Memory Grants - My Notes No 01

In order to run queries, SQL server need memory to store data. There fore when query is getting estimated, memory grant (amount of Memory SQL Server going to request) also get estimated.

Memory grant estimates are based on following (as per Erik Darlings this article):

• Number of rows -> How many rows query will read/write (again this is also an estimate at this stage)
• Size of the row -> how much memory one raw required
• Number of concurrent memory consuming operators -> how many operators will run at a time when query get executed (not all operators run same time, some need finishing other to start)

Variable Size Column Effect

Size of the row calculation get interesting with variable size (varchar/nvarchar like) columns. SQL server use half of the size of the variable size column. 

For example if column in varhchar(50) -> SQL server will assume column is half full and take 25 characters.

Memory Grant Info

If you run a query, which require a memory grant (some queries doesn't require it as SQL server can output the data as it reads), Memory grant information is shown on the execution plan.

To see this right click the Select operator and select properties:

Description of these properties can be found in this article by Pinal Dave.

Scan vs Seek in SQL Server

Recently I was able to go through Brent Ozar's "How to think like the SQL Server" free fundamental course and have to say content is very good.

Actually I have listen to the same course on YouTube several times. But this is the first time I have gone through it properly with Pen and Paper on my hand.

Learned few interesting things and one of the thing struck me most is on ever popular topic on scan and seek on SQL Server.

As per Brent, 

SCAN -> is where SQL Server start reading object (table) from one end. This can be from top/beginning or bottom/end.

SEEK -> is where SQL Server start reading the object (table) from specific point.

Generally when we say table scan, we think SQL server reads the whole table, but in reality it can be different. For example query like below:

SELECT TOP 10 * FROM dbo.Users

This will only read top 10 rows from the table, but it is a scan.

On the other hand seek can read the entire table. For example if we give SQL server to seek for a value which is not in the table, it will seek from the start or end and will go through entire table to find it.

Amazing isn't it.

More details from Brent's site -> https://www.brentozar.com/archive/2019/10/how-to-think-like-the-sql-server-engine-so-index-seeks-are-great-right/

Passing dynamic parameter to Base Constructor

 While working on a C# program this week, came across interesting scenario where I needed to pass parameter to base constructor of a class. Unfortunately, base constructor is developed by a third party, so we don't really couldn't change it.

Early days when we use this base class, we use to hard code the parameter to it in inherited class constructor. This week we had a new requirement, where that parameter required to be dynamic or read from configuration file.

To demonstrate it with example, let's assume my base class is ISOCar (where it build ISO standard car). I inherit this class and create my own modified car - let's call it MyBrandCar. Let's also assume base class has one constructor which accept engine capacity. So my code will be some like below:

public class MyBrandCar : ISOCar

{

    public MyBrandCar() : base(1.5)

    {

    }

}

My new requirement is, I need to read the engine capacity from a config file or calculate it from some other values from a config file.

So I was wondering what is the best way to approach this. Because constructor is called first and base constructor is called even before inherited class constructor, there was no way to fetch the value from the config file.

Little bit of Google suggested me to use static method. So I changed my code to following:

public class MyBrandCar : ISOCar

{

    public static decimal GetEngineCapacity()

    {

        // read the config and return the value

    }

    public MyBrandCar() : base(GetEngineCapacity())

    {

    }

}

Since static methods are executed even before constructors, this has worked.

Simple, but elegant way,

Downloading RDL files from SSRS Report Server

In work, I got a task to backup SSRS reports folder of a report server. Taking a different approach, I choose to depend on AI tools to help me out. I choose Bing Chat powered by ChatGPT.

Following is the PowerShell code generated by Bing Chat.

# Specify the URL of the report server

$reportServerUrl = "http://myreportserver01//reportserver"

# Specify the path of the report on the report server

$reportPath = "/My Report Folder 01"

# Specify the path where to save the RDL file (Note: double forward slashes)

$savePath = "D:\\BackupFolder\\Transfer\\ReportRDL\\"

# Create a new proxy to the report server (Note: we are using ReportingService2010 name space

$proxy = New-WebServiceProxy -Uri "$reportServerUrl/ReportService2010.asmx?wsdl" -Namespace SSRS.ReportingService2010 -UseDefaultCredential

# Get all reports in the folder

$reports = $proxy.ListChildren($reportPath, $false) | Where-Object { $_.TypeName -eq "Report" }

# Download each report

foreach ($report in $reports)

{

    # Get the report definition

    $reportDefinition = $proxy.GetItemDefinition($report.Path)

    # Save the report definition to disk

    [System.IO.File]::WriteAllBytes($savePath + $report.Name + ".rdl", $reportDefinition)

}

I also come across another away to download RDL files using "RS.exe" tool. It can be found in following url -> https://asgb1.freshdesk.com/support/solutions/articles/11000114924-download-ssrs-reports-rdl-files-using-rs-exe-utility

My Halloween Problem

2023 Halloween was just past, I came across this SQL Server related Halloween Problem accidently on an article I read.

Usually update operation has two separate cursors, one doing read and one doing the update.

If update operation caused row to updated in a way it cause index key also need updated and hence it's position changed, then there can be a occasions where read cursor might read it again and update again.

What is basically happening is when row is updated it change the position to match the index key and hence read by the read cursor again and updated again. 

Though it sound like it could happen very frequently, issue doesn't occur regularly on SQL server because it need to update index key and also phase separation happens while update operation doesn't allow it. Phase separation, make sure all matching rows are read first before updates are occur. However, for performance reasons, SQL server might decide some time to write some of the updated data before it read the next batch of data to be need to updated.

Above is a very high level and very simplified explanation (so that I can remember it). There are great articles on internet regarding this: 

https://www.sqlshack.com/the-halloween-problem-in-sql-server-and-suggested-solutions/

https://en.wikipedia.org/wiki/Halloween_Problem

SARGability in SQL Server

When I heard first about SARGability, it was a funny sound word to me and never could remember the meaning of it.

So I decided to search on it and study a bit and write a note about it, so I can remember it.

In simple terms SARGability means, ability use search predicates (i.e. what is in where clause) to seek through index or a table. High SARGability means, your where clause can be directly use to seek through index or table.

Non-SARGability means, opposite of that, of course.  I.e. these predicate will be hard for SQL server to use in direct seek operation.

Erik Darling, has goo article on this -> https://erikdarling.com/sargability-week-what-is-it-anyway/

Migrating SSRS Report Subscriptions

 SSRS has nice little feature where you can automate emailing of an report on a pre-defined schedule. Last week, one of our customer was moving their SQL server to brand new one and wanted to move all SQL related feature to it. 

So I have moved everything and was left with few dozens of report subscriptions. I wondering whether I should re-create them manually on new server or is there a tool that I can use.

When asked from AI search tool, it came up with several tools. Out of them following two were my finalists.

1. Report Sync Tool -> https://code.google.com/archive/p/reportsync/downloads

2. ReportingServicesTools written in PowerShell

I decided to go with PowerShell, because of my love with PowerShell.

ReportingServicesTools is a PowerShell cmdlet is a tool that we can use to do various tasks related to SSRS. It is hosted on GitHub -> ReportingServices

Documentation contains how to install it and basic use of it. However, I couldn't find enough documentation regarding how to do a report subscription migration.

There were some information in here, but nothing much.

So I decided to dig little deep into the script and see what is it capable of. I was working with version 0.0.8.0.

There are about 70 odd functions in that cmdlet, but I was interested on functions relate to subscriptions.

• Get-RsSubscription
• Set-RsSubscription
• Copy-RsSubscription
• Export-RsSubscriptionXml
• Remove-RsSubscription
• New-RsSubscription
• Import-RsSubscriptionXml

In order to fetch subscriptions under a reporting folder I used following:

Get-RsSubscription -ReportServerUri 'http://remote-machine:8080/reportserver_sql16' -RsItem '/path/to/my/report'

In -RsItem parameter you can specify a folder or a report it self. If you specify a folder, it will look for all subscriptions under that folder. If you specify a report, it will look for all subscription belong to that report only.

Above assume current windows user credentials when connecting to report server. But if you want to connect to report server using different credentials use the usual methods to specify credentials for -Credential parameter.

$password = ConvertTo-SecureString "MyPlainTextPassword" -AsPlainText -Force

$Cred = New-Object System.Management.Automation.PSCredential ("username", $password)

Now I got subscriptions, I want to write them to a file, so I can transfer them to the new server (if the new server is accessible from where you are you can directly use the Copy-RsSubscription function).

To export to a file, I have used Export-RsSubscriptionXml function.

Get-RsSubscription <<parameters as per above>> | Export-RsSubscriptionXml C:\Test\MySubscriptions.xml

You can pipe the output of the Get-RsSubscription function to Export function.

Then on the new server, I have copied above xml file to a directory and used Import-RsSubscription to import them to new Reporting server.

Import-RsSubscriptionXml C:\Test\\MySubscriptions.xml -ReportServerUri 'http://remote-machine:8080/reportserver_sql16'

This will just create Powershell objects from the xml file, you need to output this to a Copy-RsSubscription or Set-RsSubscription function.

Different between Copy and Set functions are, Copy create new subscriptions, where set updates already existing subscription.

If you want just to see, what is in a xml file you can use command like below:

            Import-RsSubscriptionXml .\MySubscriptions.xml | 

            Out-GridView -PassThru |

            Copy-RsSubscription -RsItem /Example/Report

You can learn more about these functions, from source code help available on the GitHub -> CatalogItems.

When I SET STATISTICS IO ON

SET statements in TSQL allows user to get more information or change behavior of the query execution in SSMS.

One of the most common SET statement we use is SET STATISTICS ON/OFF.

This shows statistical information about the query information. This is what we get when we turn on STATISTICS IO ON.

Statistic IO Result:

---------------------------

Table '<<Table name>>'. -> Name of the table we read

Scan count 1, -> Number of scans or seeks

logical reads 160,  -> Reads from RAM

physical reads 0, -> Reads from Disk

read-ahead reads 0, -> Number of pages placed into the cache for the query

lob logical reads 0, -> Large objects read from RAM

lob physical reads 0, -> Large objects read from Disk

lob read-ahead reads 0. -> Number of pages for LOB placed into the cache for the query

LOB stands for Large objects, such as ntext, nvarchar, varchar, etc.

LOB data is stored not on the row, but away from the row.

• If size is < 8000 bytes, stored on row overflow pages
• If size is > 8000 bytes, stored on LOB data pages

Moving away from old Azure AD Connect ...

One of the client we work for had hybrid Active Directory, which means they have their on premise domain with two on premise domain controllers which synchronised with Azure Active Directory (now called Microsoft Entra Id).

They used Azure AD Connect utility to sychronise users and credentials between on premise domain controllers and Azure AD (Entra Id). This utility was installed on one of the domain controllers.

They had version 1.x. Last week they suddenly got a email from Microsoft saying AD Connect 1.x versions will be stopped working from 1st of October 2023. Company was not ware of this until then. When searched about this on the web, AD Connect 1.x versions was actually retired on 31/08/2022.

So they needed to act fast.

There were two options:

• Install newest version of Azure AD Connect (which is version 2.x)

or

• Install new utility Microsoft recommend to synchronise on premise AD with Microsoft Entra Id -> which is called Cloud Sync

Reading about Cloud Sync, it is the future proof utility to syncrhonised identities between on premise and Microsoft Entra Id. Program run primarily on cloud (un-like AD Connect) and have an agent running on the on premise server for synchronisation.

If you want to know more about Cloud Sync -> https://learn.microsoft.com/azure/active-directory/hybrid/cloud-sync/what-is-cloud-sync

After considering lot, we have decided to install new version of the AD Connect instead of Cloud Sync. This is mainly because, this organisation is schedule to remove all on premise present of network very soon. There fore it was not worthy to install whole new architecture of sync on servers.

When we tried to install, there were another issue. Azure AD Connect was not supporting Windows version below 2016. But they had domain controllers on Windows 2012 R2. There fore we had to upgrade them before starting to install new version.

Fortunately, domain controller upgrade was straight forward, didn't face much issues and we were able to upgrade to Windows 2019 and also increased the Domain Functional level to 2016 (which is the highest available at that time).

Only issue we faced while upgrading was preparing AD (because they were domain controllers)

All you have to do is run adprep. Adprep can be found in the installation DVD of the windows 2019 (or any other version).

For AD Connect upgrade we used swing migration -> https://learn.microsoft.com/en-us/azure/active-directory/hybrid/connect/how-to-upgrade-previous-version#swing-migration

In this method, we installed AD Connect new version on the other domain controller and then kept it staging mode (not synching). Then we stopped old connect and un-installed it. Then we have activated the new version.

Using NOEXPAND hint

 NOEXPAND is a TSQL query hint, which we use with indexed views in certain scenarios.

NOEXPAND

Indexed views (or materialized views) are the ones which store underlying data in the database, where normal views generate data for the underlying query on run time.

However, even though indexed view store actual data for the view, there is a change SQL server still use the underlying query directly to generate the data when view is used in a query.

Under some circumstances, this can cause poor performances. So to improve performances, you can force SQL server to use underlying data on the query by using this query hint.