aws clr rds sql server visual studio

How To Use SQL CLR in Amazon AWS RDS

Recently, I went over how to setup SQL Server on Amazon RDS from within Visual Studio. In that article, I noted that there are differences between SQL Azure and SQL Server on Amazon RDS and that we needed more work to thoroughly investigate any limitations of SQL Server on RDS. I specifically called out the ability to run .NET assemblies (otherwise known as SQLCLR) as a possible deal breaker for some. In Amazon’s SQL Server Feature List, they specifically mention “Safe CLR” as a feature. So here’s how to get that working.

Enable CLR

By default, SQL Server does not enable CLR execution. A brand new instance on RDS has the same limitation. We want CLR, so we need to enable it. Normally, on a local SQL Server, you would enable it by executing the following command:

[code] sp_configure ‘clr enabled’, 1 go reconfigure go[/code]

If you try to execute that against a SQL instance on RDS, however, you’ll get a nasty error messages like:

[code] Msg 15247, Level 16, State 1, Procedure sp_configure, Line 94 User does not have permission to perform this action. Msg 5812, Level 14, State 1, Line 1 You do not have permission to run the RECONFIGURE statement. [/code]

So, that’s a no go. Instead, Amazon provides something called “DB Parameter Groups” that allow you to change instance properties as needed. Looking at the RDS section of the AWS Management Console, the left column has a link for the aforementioned DB Parameter Groups. You can easily create a new DB parameter group by clicking “Create DB Parameter Group” (what a perfectly labeled button). In here, select one of the DB Parameter Group Families for SQL Server, depending on which type of instance you need. I’m going to pick sqlserver-ex-10.5 as I want a SQL Server Express instance.

[caption id=“attachment_827” align=“alignnone” width=“300” caption=“Create a New DB Parameter Group”]AWS Create DB Parameter Group[/caption]

[caption id=“attachment_828” align=“alignnone” width=“300” caption=“Set which default parameter group to use as a template.”][/caption]

This will create a new DB Parameter Group with all the default properties of the family chosen. Scrolling down in the parameters list, we’ll find ‘clr enabled’ with a value of ‘0’. This is expected, however, we need to change the value to ‘1’ to get CLR working.

[caption id=“attachment_829” align=“alignnone” width=“300” caption=”'clr enabled' defaults to 0”][/caption]

Modify DB Parameter Group

Unfortunately, Amazon does not offer a way to modify a DB parameter group via the AWS Console. Instead we can either use the Command Line Interface (CLI) or use an API call. I’m going to show how to use the CLI method. Believe me, this is the worst part.

To use the CLI Tools, follow the instructions provided by Amazon. I didn’t want to install Java on my computer, so I spun up an Azure instance and did everything via Remote Desktop. You could do this on a virtual machine or wherever you prefer. The instructions are fairly simple and worked for me without trouble. Just make sure to set all the PATH variables correctly.

Amazon also has a document that describes how to work with DB Parameter Groups. In the section labeled “Modifying a DB Parameter Group”, they list the CLI command for modifying a db parameter group property as:

[code]PROMPT>rds-modify-db-parameter-group mydbparametergroup –parameters "name=max_connections,value=250,method=immediate" –parameters "name=max_allowed_packet,value=1024,method=immediate"[/code]

So let’s replace their sample values with some real values.

[code]rds-modify-db-parameter-group clrenabled-sqlserver-ex-105 –parameters "name=‘clr enabled’,value=1,method=immediate"[/code]

Note that I changed ‘mydbparametergroup’ to the name of the db parameter group we previously created – ‘clrenabled-sqlserver-ex-105’. The only property to change is named ’clr enabled’. Note that because our property has a space in it, we need to enclose it in single quotes. Set the value to 1. Run that on your machine with the CLI tools installed and hopefully you’ll see something that looks like this:

[caption id=“attachment_830” align=“alignnone” width=“300” caption=“If you see this, it should have worked.”][/caption]

To confirm that worked, go back to the AWS Management Console and look at the parameters on the new DB Parameter Group. ‘clr enabled’ should now show a value of ‘1’. If it still says ‘0’, then something didn’t work. Ask in the comments and I’ll see if I can help.

[caption id=“attachment_831” align=“alignnone” width=“300” caption=“clr enabled is now set to 1”][/caption]

Create Database Instance and SQL Database

Assuming it worked, it’s time to create a new DB instance and database. Using the web console, click “Launch DB Instance”. This process is similar to creating any other database. The only difference is that in the “Additional Configuration” step, we need to choose the new DB parameter group we created. Beyond that, it’s all the same.

Once the instance is created and shows a status of ‘available’, go back to Visual Studio to create the database using the AWS Explorer add-in for Visual Studio. Right-click on the new instance and click “Create SQL Server Database”. After giving the database a name, the AWS Explorer will automatically add it to the Server Explorer.

[caption id=“attachment_833” align=“alignnone” width=“300” caption=“Right-click on instance to create a new database.”][/caption]

Deploy the SQLCLR Assembly

As a demonstration, I created a simple SQLCLR assembly with a user defined function (UDF). This UDF takes in two ints and returns the sum of those two numbers.

[code language=“csharp”] using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions { [Microsoft.SqlServer.Server.SqlFunction] public static SqlInt32 AddNumbers(SqlInt32 x, SqlInt32 y) { return new SqlInt32(x.Value + y.Value); } }; [/code]

To easily deploy this to the new database, right-click on the project name and click “Deploy”. If this doesn’t work, view the project properties and make sure to set the target database to our AWS database.

[caption id=“attachment_834” align=“alignnone” width=“300” caption=“Make sure the SQLCLR project references the correct database.”][/caption]

Once you see that the deployment succeeded, expand the ‘Assemblies’ subfolder in the server explorer under the new database. If you see the name of your assembly, then the deployment worked and you can now use it. Note that if you skipped the clr enable step above, the deployment will still work but you won’t be able to execute anything in the assembly.

To confirm that the assembly is working correctly, I opened a new T-SQL Editor window and tried it out:

As you can see in the screenshot, I asked the UDF to add 8 and 7 and it correctly gave me 15. Next step, world domination.

That’s it! This sample proves that we can deploy .NET assemblies to SQL Server on Amazon RDS giving it a leg up in the cloud SQL Server competition. While this is a trivial example, the same steps should apply if you have something more complicated to deploy.

If you’ve got any questions or comments, please let me know in the comments section below.

Liked this article? Be sure to follow me on Twitter @kenstone.