Previous posts discussed what CLR is, how we can import 3rd party DLLs and how we can use CLR to invoke REST APIs directly from Azure SQL MI. Today, we will touch upon another pain point that we’ve observed – transferring CLR assemblies from on-prem. to cloud; and we will do that by creating a brand-new MI link.
Let’s start with a quick reminder on what MI Link is.
What is Azure SQL MI Link?
Azure SQL Managed Instance link is a new feature enabling you to create a distributed availability group between your SQL Server and Azure SQL Managed Instance. It makes it super simple to connect on-prem. and cloud, providing near real-time replication speeds. Benefits are many and if you aren’t familiar, I’d strongly suggest you read our official blog post about it.
One great benefit for our use-case is that MI Link takes care of transferring the CLR assemblies for you! You can import your assemblies on your SQL Server instance, using all too familiar syntax (i.e. CREATE ASSEMBLY FROM ‘C:\path\to\assembly.dll’), and MI link will ensure that those same assemblies get transferred to cloud. Easy peasy and no need to deal with hex literals anymore.
Working example
If you haven’t set up the Azure SQL MI Link yet, follow our tutorials on Azure docs - using the link feature for Managed Instance.
For simplicity’s sake, I will use the code that we’ve introduced in the 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;
}
/// <summary>
/// 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.
/// </summary>
/// <remarks>
/// For example, for GetCurrencyParities("EUR"), the response would be:
/// { "USD": 1.2, "CAD": 1.46, "CHF": 0.96 }
/// </remarks>
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 just like the last time, please keep in mind that this code is not optimized for production readiness but rather for a showcase purposes.
One really nice thing about SQL MI Link is the fact that you don’t need to use hex literals anymore. Instead, you can just use the regular CREATE ASSEMBLY FROM ‘C:\path\to\assembly.dll’. Obviously, you can use hex literals as well if you want to, but that’s up to your preference.
After compiling the above code to DLL, execute 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 that this goes through, you should be able to execute the following on your on-prem. and on your SQL MI instance both:
Congrats! You’ve just used MI link to have CLR assemblies transferred from your on-prem. instance to Azure SQL MI. Great job!
What about existing assemblies?
All the assemblies and UDFs that are part of the database being replicated are going to be transferred to Azure SQL MI instance as well!
One thing you need to keep in mind though is that only the data from user databases is transferred to Azure SQL MI. This means that if you have any assemblies in your master database, those are NOT being transferred. Same is true for trusted assemblies (i.e. the ones added with sp_add_trusted_assembly).
Everything that is part of master database will have to be manually transferred to Azure SQL MI instance. Alternatively, you might consider creating a dedicated database solely for storing and transferring the assemblies.
Further reading
Here are some of the additional resources you might find useful:
- Embed C# in the heart of the SQL Query Engine
- Importing .NET FX and 3rd party DLLs into Azure SQL MI
- Importing .NET FX and 3rd party DLLs into Azure SQL MI (YouTube video)
- Invoking REST APIs with SQLCLR and Newtonsoft's Json.NET
We’d love to hear your feedback! If you’ve enjoyed this article, or think there might be some improvements to be made, please leave your comment below. Thanks for reading!
Microsoft
