Managed Code Inside of SQL Server

What roles do you perform as the DBA for your organization?  Many times we find ourselves doing data related tasks that are thrust upon us because of software acquisitions, executive level business requirements, or a myriad of other reason.  Whichever the case, we end up working on some common repetitive tasks and some very specific unique ones.  One of those specific tasks that you may find yourself needing to implement is using managed code in your SQL environment. 
 

This article is not for everyone, that is to say managed code integration is not a common task like backup or index management, rather it is for administrators who find themselves in one of the following two situations; a) you have applications in your environment that were developed and supported in house, and/or b) you work closely with those who develop code to enhance or modify 3rd party applications.
 

In a typical n-tier application, we have front end code, business logic, and the data repository.  In fact we could have a data repository that supports many different front/middle tiers.  In such a case, we could develop stored procedures, triggers, or functions, on the data layer for shared use at the application layer.  We have heard the benefits of centralized administration of networks from Microsoft for years, we can think of the aforementioned approach as centralized development.
 

One problem that could arise from this approach is that an application may (and likely will) already have code written in it for its own use.  Therefore developing it again at the data layer, is time consuming, costly, and may have scaled down functionality due to the limitations of T-SQL.  SQL Server 2008 has the ability to create, what I will term as function “proxies” that are created inside of the SQL Server environment, but are defined in managed code.  Now, if your development team has developed some function that their application needs and placed it into a DLL, you as a SQL professional can centralize that function by simply calling a SQL Server function that calls out to that DLL for its implementation. 
 

Whether the reason for using this feature, is applying centralized coding, or combating the limitation of the data developer not knowing the same business logic specifics that the application developer knows, or that managed code is more capable of handling the business logic tasks than T-SQL, or leveraging the skills of each area of your business to minimize the development cycle – accessing managed code is a great option to explore.
 

Assume your business line is the building of bridges and you frequently need to calculate the density of steel at various elevations.  You may have this function defined in one of your applications on the middle tier, and you may find that you now need to access that function from different applications and possibly inside SQL Server.  Without managed code integration, you would have to recreate the body of the function inside of SQL server or inside of the other application.  You now have the ability to create the function inside of the SQL environment, point the body of the SQL Server function to the DLL function, and simply consume the SQL function, as you normally would.  Of course, there is a little bit of “wire-up” code that you need to provide, as well as some re-architecting from the application standpoint.  You may find the benefit of the code existing in one place makes implementing managed code worth it.  However, a cost analysis to determine if this change would be of benefit would have to be conducted.  However, designing code in the first place to be able to benefit from this capability is a great way to move forward without the cost of retooling any current code.
 

The specifics of how you chose to implement this feature are as varied as the infrastructure of your company, but the benefits of now being able to reuse .NET developed code inside of SQL server is a great way to reduce total cost of ownership in a measurable way.

- Ed Dressler, SQL Server Expert; CTT+, MCDBA, and MCITP LinkIn with Ed