| Author |
Topic |
|
tfountain
Constraint Violating Yak Guru
491 Posts |
 Posted - 2009-02-12 : 08:53:56
|
| The time has come for us to implement CLR in our databases (yea, about time). This will be utilized by several groups within our organization for different purposes. Being the one that is ultimately responsible for our database I do have several concerns that I would like to try and resolve. If anyone has any thoughts, suggestions, comments or references your input will be appreciated.I'll put this basic question out there (since I do not know how CLR is implemented or deployed yet) - as a DBA, what are the major concerns and/or considerations around physical security, database security, resource security and CPU utilization (on the SQL box)?My own opinion is I would like to restrict all CLR assemblies to the SAFE permission set but is this viable in terms of enforcing? I would like to avoid having a developer or anyone else begin writing intensive business processes on the database and affecting performance. We have web and application servers for those workloads. I also see no reason why any business process needs to access physical file information on the database server. Perhaps some DBA routines but that is the only exception I can think of.Also, am I just being overly concerned and should just enable the option and move on? I actually have a lot more questions but I figure let me just put these basics out here and see where this leads. |
|
|
revdnrdy
Posting Yak Master
220 Posts |
Posted - 2009-02-12 : 12:04:36
|
| Hello;This link "building Database Objects with CLR" may help get you started in how to implement CLR in a .NET framework. [url]http://msdn.microsoft.com/en-us/library/ms131046.aspx[/url]It may not address all of your concerns but should aid in pointing you in the right direction. A big portion of you concerns will also center around the business needs of your organization (ie.. security & permissions). As I am sure you know your business needs will translate into business logic. That logic in turn will then be enforced by code.I think it is wise for you not to simply 'turn the feature on' without a good understanding of what is happening. Ask all the questions you need to & read up on the technology.hope that helps you!r&r |
 |
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2009-02-12 : 12:38:13
|
| I have done some additional research and here is what I have found. First, I must say, I am impressed with the job MS did the implementation of CLR.Anyway, as far as the permission set, it seems I can indirectly control this by keeping the database attribute TRUSTWORTHY set to NO. Seems good enough for me. |
|
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2009-02-13 : 09:04:33
|
| Snag! First error attempting to execute CREATE ASSEMBLY with a basic "Hello World" example. This is the error I ran across:Failed to initialize the Common Language Runtime (CLR) v2.0.50727 due to memory pressure. Please restart SQL server in Address Windowing Extensions (AWE) mode to use CLR integration features.This particular instance is a development box. This box is a Windows 2003 Standard SP2 (x32) server with 4 GB memory. Unfortunately this is a multi purpose server and runs other processes on the OS so we have SQL Server configured with min memory of 512 MB, max memory of 1024 MB with AWE enabled. This instance does not the -g option specified so the amount of reserved memory for extended stored procedures, CLR, OLEDB providers, etc is the 256 MB. We do make extensive use of linked servers to SQL Server, Oracle and dBase files on the OS (IOW - OLEDB providers).I'm thinking I should set the -g option to 1 GB on this dev box but are there other things I should consider? That will leave 2 GB for SQL Server and 2 GB for the remainder of the operating system. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2009-02-13 : 09:36:19
|
quote:
Originally posted by tfountain
The time has come for us to implement CLR in our databases (yea, about time).
Apparently I missed that date on my calendar. Please let me know when this new database holiday occurred, so that next year on the anniversary we can celebrate by starting to clean up all the mess that implementing CLR is surely going to cause.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2009-02-13 : 10:13:27
|
quote:
Originally posted by blindman
quote:
Originally posted by tfountain
The time has come for us to implement CLR in our databases (yea, about time).
Apparently I missed that date on my calendar. Please let me know when this new database holiday occurred, so that next year on the anniversary we can celebrate by starting to clean up all the mess that implementing CLR is surely going to cause.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________
I really do not see how your opinion helps me out. Thanks for stopping by! |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2009-02-13 : 10:22:22
|
| That would be your problem.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2009-02-13 : 10:42:17
|
quote:
Originally posted by blindman
That would be your problem.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________
All I have in response is - WOW. I would like to request that you refrain from posting in this thread so the constructive conversations can continue. And possibly go back to bed and get up on the right side of it. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2009-02-13 : 12:26:58
|
| Request granted.Oops.....________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
revdnrdy
Posting Yak Master
220 Posts |
Posted - 2009-02-13 : 12:56:52
|
quote:
Originally posted by tfountain
Snag! First error attempting to execute CREATE ASSEMBLY with a basic "Hello World" example. This is the error I ran across:Failed to initialize the Common Language Runtime (CLR) v2.0.50727 due to memory pressure. Please restart SQL server in Address Windowing Extensions (AWE) mode to use CLR integration features.This particular instance is a development box. This box is a Windows 2003 Standard SP2 (x32) server with 4 GB memory. Unfortunately this is a multi purpose server and runs other processes on the OS so we have SQL Server configured with min memory of 512 MB, max memory of 1024 MB with AWE enabled. This instance does not the -g option specified so the amount of reserved memory for extended stored procedures, CLR, OLEDB providers, etc is the 256 MB. We do make extensive use of linked servers to SQL Server, Oracle and dBase files on the OS (IOW - OLEDB providers).I'm thinking I should set the -g option to 1 GB on this dev box but are there other things I should consider? That will leave 2 GB for SQL Server and 2 GB for the remainder of the operating system.
May I suggest to read up AWE.[url]http://msdn.microsoft.com/en-us/library/aa366527(VS.85).aspx[/url]Here is an excerpt which seems related to your issue.Address Windowing Extensions (AWE) is a set of extensions that allows an application to quickly manipulate physical memory greater than 4GB. Certain data-intensive applications, such as database management systems and scientific and engineering software, need access to very large caches of data. In the case of very large data sets, restricting the cache to fit within an application's 2GB of user address space is a severe restriction. In these situations, the cache is too small to properly support the application.r&r |
|
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2009-02-13 : 13:40:44
|
| Yea, I've read up on that and I don't glean anything out of it that will help my situation. I've even changed it to reserve 1 GB of memory by setting the -g startup option to 1024, restarted the SQL Server service and attempted to run the CREATE ASSEMBLY command and I still receive the same error. Since I restarted the database services, I don't see how this reserved space could be fragmented at that point. Is there a minimum memory requirement to run CLR? |
|
|
|
revdnrdy
Posting Yak Master
220 Posts |
Posted - 2009-02-13 : 15:35:36
|
What happens when you run this code.. (what is the output in SSMS?)1. Is CLR initializing properly? run this query to find out..select * from sys.dm_clr_properties --output is easy to understand2. How much memory is CLR using? To check this run:select * from sys.dm_os_memory_clerks To explain the columns of that query a bit....The field single_pages_kb is for memory allocated in the SQL Buffer Pool, multi_pages_kb is for memory allocated by the SQL CLR Host that is outside the SQL Buffer pool, and virtual_memory_committed_kb is the amount of memory allocated by the CLR directly through bulk allocation interface (instead of heap allocation) through SQL server.3. How much memory is SQL CLR allowed to use? run this query..select single_pages_kb + multi_pages_kb + virtual_memory_committed_kb from sys.dm_os_memory_clerks where type = 'MEMORYCLERK_SQLCLR' This may help determine if the issue is memory related. You may also wish to see if AWE is enabled (you hadn't mentioned whether it was or not).r&r |
|
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2009-02-16 : 09:52:05
|
quote:
1. Is CLR initializing properly? run this query to find out..select * from sys.dm_clr_properties
Three rows returned, but only the row with the name attribute = state has a value:directory version state CLR initialization permanently failedquote:
2. How much memory is CLR using? To check this run:select * from sys.dm_os_memory_clerks
Should I be filtering for name = 'MEMORYCLERK_SQLCLR' or 'MEMORYCLERK_SQLCLRASSEMBLY' here? If so, I get the following results:memory_clerk_address type name memory_node_id single_pages_kb multi_pages_kb virtual_memory_reserved_kb virtual_memory_committed_kb awe_allocated_kb shared_memory_reserved_kb shared_memory_committed_kb page_size_bytes page_allocator_address host_address0x0088B6D8 MEMORYCLERK_SQLCLR Default 0 216 120 576 40 0 0 0 8192 0x0088B6F8 0x000000000x3449D6D8 MEMORYCLERK_SQLCLR Default 0 0 0 0 0 0 0 0 8192 0x3449D6F8 0x000000000x3449D930 MEMORYCLERK_SQLCLRASSEMBLY Default 0 0 0 0 0 0 0 0 8192 0x3449D950 0x000000000x0088B930 MEMORYCLERK_SQLCLRASSEMBLY Default 0 0 0 0 0 0 0 0 8192 0x0088B950 0x00000000 Note the last 3 records do not have any values.quote:
3. How much memory is SQL CLR allowed to use? run this query..select single_pages_kb + multi_pages_kb + virtual_memory_committed_kb from sys.dm_os_memory_clerks where type = 'MEMORYCLERK_SQLCLR'
This returns 2 records, one with a value of 376, the other with a value of zero. |
|
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2009-02-16 : 09:55:24
|
| After researching the state message in sys.dm_clr_properties, I found this in the BOL:The CLR initialization permanently failed state indicates that hosted CLR initialization failed. Memory pressure is a likely cause, or it could also be the result of a failure in the hosting handshake between SQL Server and the CLR. Error message 6512 or 6513 will be thrown in such a case.In my case I am generating a 6513 error with the CREATE ASSEMBLY statement. Is this really just a permission issue with creating the CLR?EDIT:After following suggestions on other threads, I restarted the SQL services and queried the state before anything else and the state is now 'locked CLR version with mscoree'. I'll research this one further but any suggestions will be welcome. |
|
|
|
revdnrdy
Posting Yak Master
220 Posts |
Posted - 2009-02-16 : 10:38:26
|
| This is straight from BOL.The Locked CLR version with mscoree state may be seen where the hosted CLR is not being used and, thus, it has not yet been initialized. The hosted CLR is initialized the first time a DDL statement (such as CREATE ASSEMBLY (Transact-SQL)) or a managed database object is executed.When you restarted the server did you check the startup logs for any error messages? I didn't ask earlier but is the CLR option enabled?You can check by looking at exec sp_configure to see if clr enabled is set to 1. (Again just double checking).As a final note I am wondering if you have SP2 installed for your .NET framework. I have heard through the grapevine that this can create an issue. The fix for that was to re-install .NETr&r |
|
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2009-02-16 : 11:22:16
|
| I think the problem is resolved. I moved the dll file to the database server and was able to run the CREATE ASSEMBLY command just fine.The tip into the dm_clr_properties led me to to try a few things out differently. However, I do not want to grant physical access to my server so I will need to eventually resolve creating the assembly from a remote file.Also, follow up info:- CLR is enabled- AWE is enabled |
|
|
|
|
CLR concerns