The CLR in the eyes of the DBA

After SQL Server 2005 introduced the CLR, the developers enthusiastically accepted it.
The CLR is a powerful tool that the developer can use in the repository to adjust the functions of other faces supervising to language.

From the perspective of the DBA, the introduction of the CLR has played down the distinction between the encoded code and the repository code. It introduces the role of the developers and DBAs in deploying, managing CLR codes, and security. In a way, it also lightens the area between the logically and the number of layers in a multi-structure system.

For many organizations, the CLR needs to decide on the process and the roles they are involved in. If you are ready to perform the CLR, you need a lot of non-technical details to print these decisions.

Finally, I'll point out how the CLR is going to discuss the pros and cons of CLR code, and the impact that CLR technology has on SQL Server management and security. I will also describe which situations are appropriate for the CLR and which may not be suitable.

creating and deploying the CLR

The CLR is a program model that allows developers to write code using a face supervising to a language, and then use it like a T-SQL save process, a function, or a trigger. The general creation process is as follows:
1, create vs Project, code can be used c#/vb. NET editing;

2. After the code is translated, it is deployed directly from VS to the target repository. After editing a DLL file, a DLL is a executable file that contains the encoded code. After deployment, the response assembly information will be inserted in the Sys.assemblies video and other related images.

3. By using the external name option for the assembly, one or more T-SQL pairs will be created in the numbers. These are called prototypes, and a prototype can be defined as the stored process, the function, the user-defined aggregation function, or the user-defined type. The prototype does not contain a code, it is simply a simple entry that can be adjusted by other repository codes, and the code in the DLL will run.

Usually, all of the above steps are executed when you start from VS and deploy, but you can also perform each step by hand.

The face supervising of the present generation have some very difficult or even impossible functions for T-SQL. The CLR provides a way to use these languages in a repository. " For calculations or iterative operations, the CLR typically provides better performance than T-SQL. The CLR code and SQL Server are running in the same internal context, so it works very efficiently.

The CLR code is the managed code, which means that it will not be able to make the service unstable when it runs in the "CLR Run Time" code. ". It is good to do this, but any form of virtualization will have a certain program of marketing, the CLR if it does not still affect performance. As long as the code behind the translated code runs efficiently in the SQL storage space, T-SQL is still the best choice for heavy work, and if you want to visit and return a large number of numbers, the CLR is not a good choice.

In one sentence, T-SQL handles collection operations more efficiently, and the CLR is more adept at iterating, passing, and computing. Of course there will be exceptions.

managing the CLR

Sometimes the first thing a DBA has to do with the CLR is someone's suggestion to open the CLR on the server. While it is simple to use the CLR to activate the CLR configuration in the service's options, you need to consider a lot of questions before you can activate it.

The deployment of the foot to the library is the responsibility of the DBA, and the DBA also has to guarantee that the foot works properly and does not affect performance or other issues on the server. However, CLR code is typically deployed directly from the VS deployment to the Repository, and DBAs generally do not use VS, which seems to imply that developers are more comfortable deploying CLR codes than DBAs.

Developers can edit the code after the code in SQL Server, like SQL, DBA may feel uncomfortable, but the DBA can still control who can load the CLR assembly, and at what security level to run the CLR functionality. "."

CLR Security

Implicitly, only the user of the sysadmin, db_ower, and ddl_admin roles has permission to run the DDL language associated with the assembly. This limitation can be given to other roles and user. In addition, the following three sets of permissions allow you to set the three security levels of the assembly itself:

SAFE. This level of security allows only local statistics and internal calculations. Visits to system resources such as documents, networks, environmental changes, or registration forms are prohibited. Any user with Creative Assembly rights can create a safe class of assemblies. If the CREATE assembly language does not specify a security level, safe will be the level of the default.

EXTERNAL ACCESS. This security level allows assemblies to visit external sources. A user with external access rights can create this security-level set of information.

UNSAFE. Think twice before you set the assembly to unsafe, and this level of assembly has no covenant permissions outside of SQL Server. The code in the unsafe assembly may also be an unsupported password, which means it has the potential to make the server unstable. Only the sysadmins user can log in to the unsafe assembly.

Implicitly, the CLR code is running in the security context of the SQL Server service account, and if you're going to visit resources outside of SQL Server, that's a problem, and the best thing to do is not to exceed the rights of the service account. But the CLR code can be used to simulate other Windows accounts. Unsafe and external access security class assemblies have this ability. The person who can create this assembly must be trusted by the company.

understanding CLR meta-numbers

In an environment where the CLR option is initiated, the DBA should be familiar with the following 5 system images that contain the metadata information:
Sys.assembiles. This is a single assembly per line of the video. It contains some of the assembly's dependencies, such as name, security level, and creation date.
sys.assembly_files. For each assembly in the repository, this image contains a line of information stating its source file, DLL file.
sys.assembly_modules. An assembly may contain multiple types, such as stored processes and functions. The classes in the assembly may contain multiple code modules that are functionally different. This image associates the respective code module ID with the reference to their repository ID. ".". You can find out the types and methods of each CLR reference assembly as follows:

SELECT OBJECT_NAME(m.object_id) AS db_object      ,a.name AS assembly      ,m.assembly_class      ,m.assembly_methodFROM sys.assembly_modules mINNER JOIN sys.assemblies a ON a.assembly_id = m.assembly_id

sys.assembly_references. The mutual reference between assemblies is dependent on the relationship.
sys.module_assembly_usages The ID of the assembly with the reference to their repository ID.

SELECT OBJECT_NAME(object_id) AS db_object      ,a.name AS assemblyFROM sys.module_assembly_usages u INNER JOIN sys.assemblies a ON a.assembly_id = u.assembly_id

To remove a CLR assembly

VS makes it easy to create and deploy CLR assemblies into SQL Server, but there is no such handy tool to unload them. You have to move them in sequence.
Before removing an assembly, you can view dependencies to first move those T-SQL images that reference this assembly, either sp_depends or right-click in the image Resource manager.

better change.

The CLR is the example of a simple T-SQL repository that is being transformed. It's a bit of a rejection, but it's the change that makes the DBA career interesting. The CLR really breaks the limits of T-SQL in some ways. The blending of the two modes is a powerful tool that both DBAs and developers should master.

Original link

Common Enquiry

--Assembly Query SELECT *  from Sys.assembly_modules; SELECT *  from sys.assemblies; SELECT *  from Sys.assembly_files;--CLR Object in Database SELECT *  from sys.objects WHERE type_desc  like ' clr% ';--which system objects reference the CLR assembly SELECT object_name (object_id)  as [Object_name],b.name  as Assembly_namefrom sys.module_assembly_usages aINNER JOIN sys.assemblies b  on a.assembly_id = b.assembly_id;

The CLR in the eyes of the DBA