Add Trusted Assemblies to SQL Server 2022

Since SQL Server 2017, Microsoft introduced the clr strict security option. This option is enabled by default and requires that all assemblies loaded into SQL Server are signed by a certificate. This is a security feature that helps protect your database from malicious code.

When you migrate a database to SQL Server 2017 or later, you may need to load assemblies that are not signed by a certificate. In this case, you need to add the assemblies as trusted assemblies to SQL Server.

If you’ve used the data masking command in dbatools you’ve probably noticed that the PowerShell session becomes memory intensive when it has to handle larger tables with one or more unique indexes. The reason that happens is that during the data masking process the command looks for any unique indexes in the table. If it finds a unique index it will create a unique row for all the columns in the unique index. The command creates the values in memory. This means that you’ll have all the values that eventually get inserted into a table in memory. This can lead to a massive amount of memory being used when you have wider unique indexes with large data types. There was also another problem that I had to fix and that was that it would create those unique values for every unique index. This was also the case when there were overlapping columns when multiple unique indexes were being used. This was not efficient and I wanted to make something better for that too. I’ve been thinking about solutions for this problem because I think this command should be usable in almost every situation.

Consider the following scenario, you’re a database developer and your company has just decided that they want to implement DevOps for databases. You have multiple databases that need to be put under source control and each database needs its database project. The first thing you’ll need to do is decide whether or not you want to use migration-based or state-based deployments. This post is not going to discuss the pros and cons of these different methods, instead, we’re going to use state-based migrations using SQL Server Data Tools (SSDT) solutions.

This series has been a long time coming. I have been struggling with continuous integration and continuous development for a while and I want to share my process, techniques, and tips and tricks with you.

I will publish several blog posts about this process because we cannot go through all of it at once. It would lead to a TL;DR article that nobody will read.

• The first part will be setting up the project/solution for your database.
• The second part will be about creating unit tests for your database objects.
• The third part will be to put the project through a build server and eventually a deployment server

Why this series

I never had to do a lot of work with source control, visual studio, and all the related parts because I would get a script, and deploy it in a test. If it worked fine, if not I would have a backup and restore that.

You always think your environment is set up correctly and that you’re able to recover in case of a disaster.
You make backups, test your backups, set up DR solutions, and in the end test the DR plan (very important). But have you ever considered a situation where all your data is unusable?

If you get infected with ransomware, and the trojan gets a hand on your backups, all your precautions and preparations have been for nothing.