 |
SQL # |
Enabling more powerful and more functional SQL |
| Home Features Benefits F.A.Q. Download Contact Us | ||
|
|
||
|
-- F. A. Q. --
CLR stands for Common-Language Runtime. This is what makes .Net function. When code is written in any .Net language (e.g. C#, VB.Net, Visual J#, or Visual C++), it gets compiled down to a common, intermediate language called p-code. The .Net CLR runs this p-code on the machine. This is why you need to have the .Net run-time on a machine in order to .Net programs. It is also why all of those languages metioned can make equal use of the .Net framework and resources. What does the new .Net CLR ability for Microsoft SQL Server 2005 do? Does it replace T-SQL? Microsoft included the .Net CLR (.Net 2.0) inside of SQL Server 2005. This allows for Stored Procedures and User-Defined Functions to be able to make use of the .Net framework via any of the .Net languages (C#, VB.Net, Visual J#, and Visual C++). No, this does NOT replace T-SQL. T-SQL is still the ONLY way to manipulate and retrieve data in SQL Server 2005. Even if you create a .Net CLR Stored Procedure or User-Defined Function, you will still have to connect to the database inside of it and issue T-SQL commands. Granted, the connection to the database inside of a .Net CLR Procedure or Function is much more efficient than an outside call from the application since the connection already exists (as you are already in the database) and you just use it. However, you would not want to use the CLR for anything that does not have complex logic or is not procedural in nature as the most efficient mechanism for data retrieval and manipulation is still standard T-SQL via a Stored Procedure or User-Defined Function. So, the true benefits of the CLR are: a) the ability to more efficiently / quickly perform procedural logic, and b) the ability to make use of the .Net framework functionality that could never be duplicated in T-SQL anyway. THIS is where SQL# comes to the rescue. SQL# brings the useful aspects of .Net via the CLR into the T-SQL world so that now T-SQL can do so much more! Does SQL# (SQLsharp) work with Microsoft SQL Server 2000 (or before)? Unfortunately, no. SQL Server 2005 is the first version to incorporate the .Net CLR inside the database engine so the previous versions would have no way to make use of .Net code. If you are stuck using SQL Server 2000 (or before) for a while and need to do complicated, procedural logic, the only way is to create a COM DLL and register it as an Extended Stored Procedure (xp). See Books Online (BOL) for more detailed information on creating and registering xp DLLs in SQL Server 2000. What about security? Is SQL# safe? SQL# is as safe as you want it to be. Regardless of the security level set for the SQL# Assembly, .Net CLR code has the same permissions as any other T-SQL code based on either the user running the code, or the user specified in the EXECUTE AS clause of the Stored Procedure or User-Defined Function. All SQL# Procedures and Functions are created with EXECUTE AS CALLER so that anyone who runs any SQL# Procedures or Functions will only be able to do what the DBA has already allowed them to do. The CLR canNOT be used to circumvent database security; it works WITHIN the confines of the database's security. That being said, the SQL# Assembly is loaded with a default setting of SAFE. You will see this when you get the install SQL script as it creates the Assembly. Updates to the SQL# Assembly done via the SQLsharp_Update Stored Procedure will NOT change the security setting of the SQLsharp Assembly prior to the update. There are some SQL# User-Defined Functions and Stored Procedures that require EXTERNAL_ACCESS to get to the InterNet. If you want to use any of these InterNet or FileSystem functions, you will need to change the security setting via the SQLsharp_SetSecurity Stored Procedure. If you use that Procedure to set the security level of the SQL# Assembly to either EXTERNAL_ACCESS or UNSAFE, it will also set the database property of is_trustworthy_on to TRUE (who are we kidding, it's 1 in SQL Server ;-). You can read more about security in relation to the CLR here: Security in the CLR World Inside SQL Server (http://www.devx.com/codemag/Article/31193) Note: If the database property of is_trustworthy_on was previously FALSE before setting the security to 2 (for EXTERNAL_ACCESS), it will be set to TRUE by the act of setting level 2 but will NOT be set back to FALSE when setting security level 1 (for SAFE) as there is no way to determine who set is_trustworthy_on to TRUE or why or when. If you need to set is_trustworthy_on back to FALSE, you can do that via the T-SQL command: ALTER DATABASE {DBName} SET TRUSTWORTHY OFF What happens when I backup and restore the database? Do I also need to keep a copy of SQL# (SQLsharp) on the filesystem? In SQL Server 2000, COM DLLs registered as Extended Stored Procedures (xp) always point to the DLL file on the filesystem. This means that you have to keep a copy of those DLLs around so that code in the database that references those Extended Stored Procedures continue to function. In contrast, .Net Assemblies in SQL Server 2005 are loaded directly into the database and do not point to any location outside of the database. This has the benefit of ensuring that through backups and restores, code in the database that points to these .Net Assemblies continues to function. How do I know what version I have installed? That's easy. Just run the following Stored Procedure: EXEC SQLsharp_Version How do I know when upgrades are available? There are two ways to check for updates:
How do I get these upgrades? There are two ways to get the updates:
I have an idea for a new SQL# function. Will you include it? I will consider most anything. Send any suggestions to: I don't want SQL# installed anymore. How do I get rid of it? Easily (though you shouldn't because it's so great!). Just run the following Stored Procedure: EXEC SQLsharp_Uninstall This will remove ALL Stored Procedures and User-Defined Functions linked with the SQL# Assembly and then remove the SQL# Assembly itself. How do I check my current security settings? Easily. Just run the following Stored Procedure: EXEC SQLsharp_SetSecurity 0 This will show the current setting for both the database and the SQLsharp Assembly. I don't want to set EXTERNAL_ACCESS but I still would like to be able to update via SQLsharp_Update. Is this possible? Sure. Just run the following sequence of Stored Procedures: EXEC SQLsharp_SetSecurity 2 -- open up InterNet access (2 = EXTERNAL_ACCESS) GO EXEC SQLsharp_Update -- get the update now that we temporarily opened up InterNet access GO EXEC SQLsharp_SetSecurity 1 -- close any type of external resource access (1 = SAFE) GO Note: If the database property of is_trustworthy_on was previously FALSE before setting the security to 2 (for EXTERNAL_ACCESS), it will be set to TRUE by the act of setting level 2 but will NOT be set back to FALSE when setting security level 1 (for SAFE) as there is no way to determine who set is_trustworthy_on to TRUE or why or when. If you need to set is_trustworthy_on back to FALSE, you can do that via the T-SQL command: ALTER DATABASE {DBName} SET TRUSTWORTHY OFF When I update via the InterNet using SQLsharp_Update, is any information about my computer or database transmitted to your server? NO. The only piece of information transmitted to SQLsharp.com is the current version number of the installed SQL# Assembly being updated. That's it! |
||
|
Copyright © 2006 - 2008 Solomon Rutzky / The Phoenix Project. All rights reserved. |
||