Using MI Link to transfer CLR binaries from SQL Server to Azure SQL Managed Instance

Previously discussed posts What is CLRHow can we do it Import 3^{road name} Party DLL And how can I use CLR? Calling REST APIs directly from Azure SQL MI. Today we’re going to cover another problem we’ve observed, which is transferring CLR assemblies from on-premises to the cloud. And for that, we’re going to create a completely new MI link.

First, let’s take a quick look at what MI Link is.

What is Azure SQL MI Link?

Azure SQL Managed Instance Link A new feature that allows you to create distributed availability groups between SQL Server and Azure SQL Managed Instances. It provides near real-time replication speeds, connecting on-premises and the cloud with incredible simplicity. The benefits are many, and if you are not familiar with it, I highly recommend reading it. Official blog post about this.

The biggest benefits for our use case are: MI Link delivers the CLR assembly on your behalf.! You can import assemblies from your SQL Server instance using the all-too-familiar syntax (e.g. CREATE ASSEMBLY FROM ‘C:\path\to\assembly.dll’) and MI Link ensures that the same assembly is transferred to the cloud. It’s that easy and you don’t have to deal with hexadecimal literals anymore.

Working example

If you haven’t set up Azure SQL MI Link yet, follow the tutorial in the Azure documentation. Using the Link Feature for Managed Instances.

For simplicity, we will use the code we introduced. Previous article:

using System;
using System.Data;
using System.Data.SqlTypes;
using System.IO;
using System.Net;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Microsoft.SqlServer.Server;

public class CurrencyConverter
{
    [SqlFunction(DataAccess = DataAccessKind.Read)]
    public static SqlDouble Convert(SqlDouble amount, SqlString fromCurrency, SqlString toCurrency)
    {
        // Output contains list of currency parities
        string jsonResponse = GetCurrencyParities(fromCurrency.ToString());

        JObject parities = JObject.Parse(jsonResponse);
        SqlDouble parity = SqlDouble.Parse(parities[toCurrency].ToString());
        return amount * parity;
    }

    /// 

    /// Returns parities for specified currency.
    /// Invokes a fictional Currency API that takes currency name as an input
    /// and returns dictionary where keys represent target currencies, and
    /// values represent the parities to source Currency.
    /// 
    /// 
    /// For example, for GetCurrencyParities("EUR"), the response would be:
    /// { "USD": 1.2, "CAD": 1.46, "CHF": 0.96 }
    /// 
    private static string GetCurrencyParities(string fromCurrency)
    {
        string url = String.Format("https://example-api.com/currency/{0}.json", fromCurrency);
        HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
        HttpWebResponse response = (HttpWebResponse)request.GetResponse();
        StreamReader reader = new StreamReader(response.GetResponseStream());
        string responseData = reader.ReadToEnd();
        return responseData;
    }
}

And then just like this Last timePlease keep the following in mind: This code is not optimized for production readiness. Rather, it is for showcase purposes.

One of the really nice things about SQL MI Link is that you no longer have to use hex literals. Instead, you can just use a plain CREATE ASSEMBLY FROM ‘C:\path\to\assembly.dll’. Of course, you can use hex literals if you want, but that’s a matter of taste.

After compiling the above code into a DLL, run the following T-SQL on your SQL Server instance.

USE [NameOfDatabaseThatIsPartOfMILink];

CREATE ASSEMBLY [CurrencyConverter] FROM 'C:\path\to\assembly.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS;

CREATE FUNCTION ConvertCurrency ( 
  @amount FLOAT, 
  @fromCurrency NVARCHAR(3), 
  @toCurrency NVARCHAR(3) 
) 
RETURNS FLOAT AS EXTERNAL NAME [CurrencyConverter].[CurrencyConverter].[Convert];

Assuming this works, you should be able to run the following both on-premises and on your SQL MI instance:

Congratulations! You just used MI Link to transfer a CLR assembly from an on-premises instance to Azure SQL MI. Great job!

What about existing assemblies?

All assemblies and UDFs contained in the replicated database will also be transferred to the Azure SQL MI instance!

But one thing you need to keep in mind is this: Only data from your user database is transferred to Azure SQL MI.. This means: If an assembly exists in the master database, that assembly will not be transferred.The same applies to trusted assemblies (i.e., assemblies added with sp_add_trusted_assembly).

Any items contained in the master database must be manually transferred to the Azure SQL MI instance.

Further reading

Additional resources that you may find useful include:

I’d love to hear your feedback! If you liked this article or have any suggestions for improvement, please leave a comment below. Thanks for reading!