Tuesday, June 9, 2009

SQL Server as a Runtime Host Part 2

Since in SQL Server users are not allowed to run arbitrary programs for reliability reasons, code (an assembly) is loaded a little differently than in other runtime hosts. The user or DBA must preload the code into the database and define which portions are invocable from Transact-SQL.

Preloading and defining code uses ordinary SQL Server Data Definition Language (DDL). Loading code as a stream of bytes from the database rather than from the file system makes SQL Server’s class loader unique. The class libraries that make up the .NET Framework are treated differently from ordinary user code in that they are loaded from the global assembly cache and are not defined to SQL Server or stored in SQL Server.

Some portions of the base class libraries may have no usefulness in a SQL Server environment (for example, System.Windows.Forms); some may be dangerous to the health of the service process when used incorrectly (System.Threading) or may be a security risk (portions of System.Security). The architects of SQL Server 2005 have reviewed the class libraries that make up the .NET Framework, and only those deemed relevant will be enabled for loading.

This is accomplished by providing the CLR with a list of libraries that are OK to load. SQL Server will take the responsibility for validating all user libraries, to determine that they don’t contain non-read-only static variables, for example. SQL Server does not allow sharing state between user libraries and registers through the new CLR hosting APIs for notification of all interassembly calls.

In addition, user libraries are divided into three categories by degree of danger; assemblies can be assigned to a category and use only the appropriate libraries for that category.
Because code in SQL Server must be reliable, SQL Server will only load the exact version of the Framework class libraries it supports.

This is analogous to shipping a particular tested version of ADO with SQL Server. Multiple versions of your code will be able to run side by side (though this was not enabled in beta 1), but the assemblies must be defined with different SQL Server object names.

Source: https://www.nilebits.com/blog/2009/09/sql-server-as-a-runtime-host/

1 comment:

  1. In addition, user libraries are divided into three categories by degree of danger; assemblies can be assigned to a category and use only the appropriate libraries for that category

    Actually libraries are the same for all three caregories, the difference is the permissions available for your code to run, for every level you can grant some persmissions (i.e. IO in External set), but the libraries loaded are the same

    ReplyDelete