Saturday, June 13, 2009

SQL Server as a Runtime Host Part 3

There are three categories of access security for managed code. These are SAFE, EXTERNAL_ACCESS, and UNSAFE, which we mentioned previously with respect to class loading. This allows the DBA to determine if an assembly should be permitted certain privileges while knowing the risks.

These categories equate to SQL Server–specific permission sets using code access security concepts. Having stated this, there is no specific enhancement to the CLR past the normal stack walk that intercepts all privileged operations as defined by the CLR permissions model and enforces user permissions. For ensuring the integrity of userpermissions defined in the database, we depend on the principal execution context of the stored procedure or user-defined function in combination with database roles.

I have spoken of AppDomains quite a bit in previous articles. It’s time to describe exactly what they are and how SQL Server uses them. In .NET, processes can be subdivided into pieces known as application domains, or AppDomains. Loading the runtime loads a default AppDomain; user or system code can create other AppDomains.

AppDomains are like lightweight processes themselves with respect to code isolation and marshaling. This means that object instances in one AppDomain are not directly available to other AppDomains by means of memory references; the parameters must be “marshaled up” and shipped across. In .NET, the default is marshal-by-value; a copy of the instance data is made and shipped to the caller.

Another choice is marshal-by-reference, in which the caller gets alocator or “logical pointer” to the data in he callee’s AppDomain, and subsequent use of that instance involves a cross AppDomain trip. This isolates one AppDomain’s state from others. Each process that loads the .NET Framework creates a default AppDomain.

From this AppDomain, you can create additional AppDomains programmatically, like this:

public static int Main(string[] argv)
        // Create domain
        AppDomain child = AppDomain.CreateDomain("dom2");

        // Execute yourapp.exe
        int entryPoint = child.ExecuteAssembly("yourapp.exe", null, argv);

        // Unload domain

        return entryPoint;

Although there may be many AppDomains in a process, AppDomains cannot share class instances without marshaling. SQL Server does not use the default AppDomain for database processing, although it is used to load the runtime. Exactly how AppDomains are allocated in SQL Server 2005 is opaque to and not controllable by the user or DBA; however, by observation, in the beta version of SQL Server 2005, it can be determined that a separate AppDomain will be created for each database for running that database’s code.

Executing the system function master.sys.fn_appdomains() shows the AppDomains in the SQL Server process when more than one combination is in use. In the beta 1 version, the AppDomains were named “databasename.number”-for example, “AdventureWorks.2.” This effectively isolates each database’s user code from the others, albeit at the cost of more virtual memory. In beta 2, AppDomains may be allocated based on the identity of the user owning the assembly, possibly resulting in more AppDomains, but isolating each assembly owner’s code.

This effectively prevents using reflection to circumvent SQL Server permissions without the overhead of intercepting each call. The runtime-hosting APIs also support the concept of domain-neutral code. Domain-neutral code means that one copy of the Just-In-Time compiled code is shared across multiple AppDomains.
Although this reduces the working set of the process because only one copy of the code and supported structures exists in memory, it is a bit slower to access static variables, because each AppDomain must have its own copy of static variables and this requires the runtime to add a level of indirection.

There are four domain-neutral code settings:

 1. No assemblies are domain neutral.
 2. All assemblies are domain neutral.
 3. Only strongly named assemblies are domain neutral.
 4. The host can specify a list of assemblies that are domain neutral.

SQL Server 2005 uses the fourth option it will only share a set of Framework assemblies. It doesn’t share strongly named user assemblies, because it means user assemblies that happen to be strongly named can never be unloaded. AppDomains do not have a concept of thread affinity; that is, all AppDomains share the common CLR thread pool.

This means that although object instances must be marshaled across AppDomains, the marshaling is more lightweight than COM marshaling, for example, because not every marshal requires a thread switch. This also means it is possible to delegate the management of all threads to SQL Server while retaining the existing marshaling behavior with respect to threads.

Hire Me

Follow me on Facebook

Follow me

Do you find this Blog helpful?

Follow by Email

About Me

My Photo
Expert Senior Software Developer

Microsoft Business Card