subhashini
(Moderator):
Good evening and hello everybody :-) subhashini
(Moderator):
Welcome to today's chat on ' Overview of SQL Server 2005 CLR Integration" subhashini (Moderator):
We have Anjana with us today subhashini
(Moderator):
Anjana is currently Working as Sr. Software Engineer for Satyam Computer
Services subhashini (Moderator):
She is Microsoft Community Star subhashini
(Moderator):
and is also a core member of Microsoft User Group Hyderabad (MUGH)
and also one of the managers of SQLCON subhashini
(Moderator):
You can catch up with her blog at http://weblogs.asp.net/anjanaram subhashini (Moderator):
Now before we start few chat rules subhashini
(Moderator):
Please refrain from sending any private messages to the expert subhashini (Moderator):
Anjana because that leads to her logging off from the chat abruptly subhashini (Moderator):
feel free to ask your questions using the submit a question radial
button subhashini (Moderator):
Chat Rules: subhashini (Moderator):
Chat Procedures:
This chat will last for one hour. During this hour, our Experts will
respond to as many questions as they can. Please understand that there
may be some questions we cannot respond to due to lack of information
or because the information is not yet public. We encourage you to
submit questions for our Experts. We ask that you stay on topic for
the duration of the chat. This helps the Guests and Experts follow
the conversation more easily. We invite you to ask off topic questions
after this chat is over. subhashini (Moderator):
And last but not the least , I am Subhashini , moderator of this chat
subhashini (Moderator):
I work with Microsoft subhashini (Moderator):
Once again , please refrain from sending private messages subhashini
(Moderator):
Sorry friends, got logged off subhashini
(Moderator):
and that's because few people were sending private messages subhashini
(Moderator):
Please refrain from using private messages in today's chat subhashini
(Moderator):
So , lets get the chat rolling subhashini
(Moderator):
Hi Anjana Anjana (Expert):
Thanks Subhashini for the introduction :) Anjana
(Expert):
Hi All, Anjana (Expert):
Welcome to this chat session Anjana (Expert):
I am Anjana, working for Satyam Computers Services. I am a MS India
Community Star and one of the managers of SQLCon. Anjana
(Expert):
Today I would be taking a session on the Overview of CLR Integration
with SQLServer 2005. Anjana (Expert):
This is a 1 hour session. So let's have 30 Mins for the session and
30 Minutes for Q & A. Anjana (Expert):
I will answer the questions at the end of the session Anjana
(Expert):
Today I would be covering the overview of the following
1. Purpose and mechanism of SQLServer CLR Integration .
2. Development with managed code in SQLServer
3. When to/when not to use CLR Integration. Anjana
(Expert):
Let's start now.:) Anjana (Expert):
SQLServer CLR Integration is the integration of. NET CLR with the
SQLServer
Database engine. Anjana (Expert):
This synergy helps developers to use the benefits of .net runtime
and the base class libraries... While retaining the advantages of
the SQLServer database engine. Anjana
(Expert):
It is meant to take the advantage of both and get the best of both
the worlds. Anjana (Expert):
T-SQL is more useful for tasks like fetching, storing and managing
data Anjana (Expert):
But performing computation intensive tasks has not been very easy
with T-SQL !. subhashini (Moderator):
We are sorry Anjana (Expert):
Sorry guys ..got disconnected subhashini
(Moderator):
about Anjana getting disconnected subhashini
(Moderator):
Please bear with the inconvenience subhashini
(Moderator):
great Anjana (Expert):
This is because T-SQL is not a full fledged programming language. Anjana (Expert):
let's continue from where I stopped. Anjana
(Expert):
This is where the .NET CLR empowers SQLServer with it's object oriented
programming language and with it's rich base class libraries. Anjana (Expert):
Now we can write our computation logic in any CLR supported programming
language...like C# or VB.NET...and deploy it on the SQLServer. Anjana (Expert):
Earlier, These were also possible by using extended procedures, but
.NET CLR comes with lots of features like JIT, security, type safety
etc. which cannot be offered by Extended Procedures Anjana
(Expert):
So SQLServer CLR Integration comes with enhanced programming model,
enhanced security and safety mechanism and performance and scalability
improvements. Anjana (Expert):
So to meet the above purposes CLR has been integrated with SQLServer. Anjana (Expert):
Here we should remember that SQLServer acts as a host to CLR....in
other words SQLServer behaves like an OS to CLR. Anjana
(Expert):
Hence for all memory management, threading and synchronization CLR
makes a call to SQLServer
memory management/threading/synchronization model whenever required. subhashini (Moderator):
Please bear with this , Anjana will be back in a minute subhashini
(Moderator):
Please bear with this , Anjana will be back in a minute Anjana
(Expert):
Subhashini...can u confirm that ur getting my messages...sorry guys..
I
think we have some testers here who are testing this application by
sending private messages :D.. pls don't send private messages subhashini
(Moderator):
:-) yup, i have been asking the testers to stop testing that subhashini (Moderator):
doesn't seem to work Anjana (Expert):
ok subhashini (Moderator):
guys once again please stop sending private messages Anjana
(Expert):
This is required because CLR and SQLServer are two different runtimes
and hence it's very important that they completely run in-sysnc with
each other without competing with each other.
It's important that CLR Integration does not affect the integrity
and security of the host (SQLServer) Anjana
(Expert):
Please refer to the link for an article on this. http://www.extremeexperts.com/SQL/Yukon/ManagedExecution.aspx Anjana (Expert):
For this CLR has a security system called Code Access Security(CAS)
which assigns permissions to managed code and hence controls the operations
performed by managed code. Anjana (Expert):
It supports there permission sets namely: Safe, UnSafe and External-Access.
'Safe' does not allow external access and runs type safe code.
'External-Access' - runs type-safe code but allows external access
'UnSafe'- Can call unmanaged code as well. This permission is given
to administrators only. Anjana (Expert):
So now with SQLServer CLR Integration, we would have code based permissions
apart from having the SQLServer role based permissions. Anjana
(Expert):
To get more details on this refer to this link http://portal.acm.org/citation.cfm?id=1007669
This gives good info on how SQLServer loads CLR and how both the CLR
and SQLServer engine work together. Anjana
(Expert):
Now let's move to development in managed code with SQLServer . Anjana (Expert):
In SQLCLR, a single unit of deployment is called an Assembly. Anjana (Expert):
Typical Deployment Steps of managed code in SQLServer would consist
of following steps. 1. Creates an assembly .net DLL with different
classes and methods as required
2. Loads the assembly into SQLServer.
3. .NET routines are created which are associated with different entry
points inside the assembly.
Entry points meaning... each routines might be associated with a single
class or a method inside the assembly. Anjana
(Expert):
The syntax for creating an assembly is as below Anjana
(Expert):
CREATE ASSEMBLY [assembly_name]
FROM <assembly file path>
[WITH PERMISSION_SET={SAFE | EXTERNAL_ACCESS | UNSAFE} ] Anjana
(Expert):
here Permission_set are the different access settings discussed earlier(
SAFE/UNSAFE/EXTERNAL_ACESS etc). Anjana
(Expert):
For example. Say I have an assembly for all called ‘EmployeeTax’.
The purpose for the assembly is to for all tax calculation and updations
of employee.
This has methods that does tax computations and stores the details
in files for each employee in some file share. Anjana
(Expert):
CREATE ASSEMBLY EmployeeTax
FROM ‘C:\samples\payments\bin\ EmployeeTax.dll’
WITH PERMISSION_SET=EXTERNAL_ACCESS Anjana
(Expert):
Managed code that runs inside the SQLServer is called a .NET routine. Anjana (Expert):
With SQLServer 2005 we can create
1. User defined functions
2. User Defined procedures
3. User Defined Triggers
4. User Defined Types
5. User Defined Aggregates. Anjana (Expert):
With User Defined Functions (UDFs) users can write custom logic that
was possible earlier only in middle tier with managed code.
For example: For formatting the phone numbers. A user can write a
function by using the regular expressions in .net. Anjana
(Expert):
Similarly, User defined procedures and triggers are helpful where
ever the procedures need to use the .NET base class libraries Anjana (Expert):
Please use 'Submit a question' to post question. I would answer then
after some while Anjana (Expert):
User defined types are custom datatypes written by the user for his
specific use. For example , if a user wants to create a new that would
store the user’s gender info as ‘M’ or ‘F’. Anjana (Expert):
User defined aggregates helps users to extend the functionality of
existing aggregate functions like Max, Min etc and create there own
aggregates. For example calculating for 'Compound Interest' from accounts
table. Anjana (Expert):
Let’s see one example to create a .Net routine.. Let’s
take a User Defined Function.
Suppose we create an assembly ‘EmployeeTax’. Let’s
assume this assembly has a method ‘CalculateIncomeTax’
to calculate the Income tax by taking the employee id. Anjana
(Expert):
For this a corresponding we would create a routine as ... Anjana
(Expert):
CREATE FUNCTION getEmployeeTax AS
EXTERNAL NAME EmployeeTax. CalculateIncomeTax Anjana
(Expert):
Managed code communicates with database with a in-process managed
provided.
These are the functional extensions made to ADO.NET. Anjana
(Expert):
Earlier we had client based data provider for SQLServer or Oracle.
But as the managed code runs on the server, we do not need a client
based provider as the managed code runs on the server. Anjana
(Expert):
Hence there is new new in-proc data provided called SQLServer .Net
Data Provider which runs
Directly on the server. Anjana (Expert):
Check this blog for good FAQ’s on ADO.NET and refer to BOL for
more details. http://weblogs.asp.net/angelsb/archive/2004/09/07/226597.aspx http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=44762&DisplayTab=Article Anjana (Expert):
Now a developer has both the power of T-SQL and managed code to develop
his application.
But the question is do we need to replace all T-SQL with managed code
do we need to be judicious? Anjana (Expert):
We need to be very judicious here. Because both T-SQL and managed
code have there own set of advantages and disadvantages based on the
scenario. Anjana (Expert):
We should be using CLR in place of T-SQL only when managed code can
simplify a complex logic.
In a normal scenario where the simple SELECT or an INSERT statement
is used a T-SQL would be the best choice as .NET comes with an overhead
caused due to the extra code to access the database Anjana
(Expert):
CLR integration gives ample chances for developers to move there middle
tier code closer to data layer. Anjana
(Expert):
But this might add load to the CPU and hence should be done only in
scenarios where more data needs to be processed but less need to be
returned as resultset Anjana (Expert): Q: Anjana.. is there any possible for creating own protocol
using dotnet A: Creating protocols would require to leverage
Operating System level calls , so it is not possible under SQL Server
limitations with CLR Anjana (Expert):
As discussed CLR Integration is required when we have complex logic
to handle... Anjana (Expert):
and when we need to use the power of .net base class libraries Anjana (Expert):
Yes , you can. pls post ur questions Anjana
(Expert):
In continuation to my previous answer...For complex operations like
financial and scientific calculation which would need more powerful
methods and libraries, CLR would definitely add more power and flexibility
Anjana (Expert): Q:
What is a DTS package? A: this is out of scope of current
session. Anjana (Expert): Q: how CLR Integration is done with SQL Server? A:
CLR Host was implemented with in SQL Server 2005 to run .NET code
just like ASP.NET and Windows Shim (CLR Hosts) Anjana
(Expert): Q: 2. Does VS.NET required for
SQLServer 2005. A: No, it's not required Anjana
(Expert): Q: HOW SQLServer .Net Data Provider
works? A: This is an extension to the ADO.NET. the difference
being it allows server side cursors. Apart from this there are some
new methods that it offers.. Anjana (Expert): Q: how the security concept works with SQLServer 2005 A:
we have code based and user based security here.... Anjana
(Expert): Q: does this make the data retrieval
speedy compared to traditional way ...?? A: T-SQL has its
own credibilities when u r dealing with database objects and it is
faster anytime, but for all complex manipulations which doesn't require
database objects, .NET would give more performance Anjana
(Expert): Q: how the security concept works
with SQLServer 2005 A: in continuation...code based security
is used to to control the code access apart from the user based security
already available in SQLServer Anjana
(Expert): Q: Is the CLR is Integrated with SQL
Analysis Server too. A: Yes it is integrated subhashini
(Moderator):
we will continue the chat for another 15 minutes Anjana
(Expert): Q: Is there change in User Authentication
?? right not SQL Server and MIXED mode authentication is thr... A: No changes are made to SQL authentication model.. everything
works fine with Backward compatibility. Anjana
(Expert): Q: What i mean is can we mix .Net
syntax in SQL statements? A: no we cannot mix .NET syntax
in SQL code.. we can call the .net routines Anjana
(Expert): Q: Is there anything similar to SQL-J
in SQLServer 2005? A: Right now there is nothing similar
to SQLJ in SQL Server 2005, CLR pack, This is applicable to CLR with
in Oracle 10g and IBM DB2 also Anjana
(Expert): Q: can we host all the versions of
.NET Framework at a time in SQL SERVER 2005 A: no, right
now SQL Server can host CLR 2.0 only Anjana
(Expert): Q: Is there any spl classes available
from ADO.NET to support SQL Server 2005. efficiently? A:
Yes, with ADO.NET we have some special classes for ADO.NET.. For instance
SqlContext , Sqlpipe, Sqltrigegr context are the main one's Anjana
(Expert): Q: the SQL Profiler is available with
2005. Can it show the .net calls? A: well.. it doesn't.
it
can show the calls made by the .net routine but not the code it self.. if
I have understood ur question right Anjana
(Expert): Q: can v get the trail version of
SQLServer 2005 ?? A: goto http://www.microsoft.com/sql/express/
u can get trial version if you are a MSDN subscriber Anjana
(Expert): Q: hw significant is the writing
.NET code in SQL server and hw the programmers get benefit from this?
Cud u give us an example.. A: Programmers will get more
flexibility of using functionality rich & powerful BCL and leverage
.NET code's security. for example to validate a simple email in T-SQL
we need to write length code.. but we can directly use regular expressions
here Anjana (Expert): Q: Well when T-SQL is much faster what scenario will I have
to use this layer? I mean how will it add the performance of the application? A: sorry raghu for the delay...This will add performance when
there is a complex logic to write...a length T-SQL query might take
more time than a simple. NET cod Anjana
(Expert): Q: can i use visual Studio.NET 2003
with SQL Server 2005 ?? A: yes you can. Anjana
(Expert):
you can crate your DLL's in VS 2003 as well Anjana
(Expert): Q: Is supports distribution?.. any
improvement in that area? A: I could not get your question. Anjana (Expert):
All .NET code will be compiled when you create a DLL, so it is 100%
MSIL, and will be stored in DB Anjana
(Expert): Q: Any good links about Yukon? A:
You can follow the links that I had pasted during the chat session.
they have the explanation of the concept Anjana
(Expert): Q: Can you elaborate on the flow,
what will actually happen internally when a call is made to .NET function
from SQL Server. Means which step will take place on calling a .net
function? A: To be brief, SQL server routes the call to
CLR for that particular .NET Code execution and CLR returns the result
back to SQL Server after execution. Anjana
(Expert): Q: How SQL Server comes to know that
the function called is actually to be processed by CLR? A:
It happens as similar as ASP.NET runtime uses CLR as it's execution
engine. The data will be marshalled to and fro according to the CLR
standard data marshalling Anjana (Expert): Q: The T-SQL code is compiled and stored inside the DB. Are
we now looking at a scenario where the same editor can write .NET
code with access to framework classes and then the compiled assembly
(DLL) is stored like that inside the DB? A: Visual Studio
2005 enables you to write both T-SQL and .NET code in a common IDE.
We would have a new project type for SQLServer Anjana
(Expert): Q: if some C# procedure written in
SQLServer that call some object across application domain (means in
case of remoting or web services...) hosted on some other machine,
then how request will pass .... A: U can use web services
with in CLR hosted on SQL Server Anjana
(Expert): Q: Ok, Just to confirm the CLR executes
in SQL Server or it is a separate process to which SQL Server interacts? A: that process will be hosted with in SQL Server memory Anjana (Expert):
SQLSTAR: Can u pls elaborate further Anjana
(Expert): Q: Yes, then request to remote object
will first pass to SQL Server and SQL Server will further send it
..... Is this way it works?? A: yes, exactly subhashini
(Moderator):
Time up ! :-) subhashini (Moderator):
Lets conclude the chat and you can email anjana subhashini
(Moderator):
\for any further queries\ Anjana (Expert):
Ok, guys.. as we are running as we are running short of time and also
because of connectivity issues.. we would stop here for the day.. For
unanswered queries pls mail to anjana.ram@gmail.com. Anjana
(Expert):
thanks a lot to all of u Anjana (Expert):
definitely!:) Anjana (Expert):
You can also post your questions at http://mught.net/sql
as well.. This is an exclusive SQLServer User Group subhashini
(Moderator):
thanks to all of you subhashini (Moderator):
have a lovely evening subhashini (Moderator):
:-) subhashini (Moderator):
thanks anjana
