Using CLR Assemblies with SQL Server Stored Procedures

1
Presentation Title
Using CLR Assemblies with SQL Server Stored
Procedures
Steve Simon State Street Corporation
2
During this hour we shall..

• Look at CLR Assemblies with a view to
  implementing them with SQL Server 2005 Stored
  Procedures.
• Learn when it is appropriate to utilize CLR
  Assemblies.
• Learn when NOT to utilize CLR Assemblies.

3
During this hour we shall..

• Examine a few practical hands-on examples where
  CLR Assemblies are utilized with Stored
  Procedures.

4
When is it appropriate to utilize T-SQL ?

• DML and DDL related activities
• The CLR Assembly performance is inferior to
  T-SQL.
• When External Access and UNSAFE Access are
  impossible.

5
When is it appropriate to utilize CLR Assemblies ?

• When output is required in html format.
• When access is required to the file operating
  system.
• Notification of DDL Events.
• Loading and unloading image / byte based data.

6
Assembly Permissions

• SAFE
• No access to external resources
• i.e. no unmanaged code
• (approved by Dr. Mom ? )
• EXTERNAL_ACCESS Permits external access to I/O
• and email

7
Assembly Permissions

• UNSAFE
• Allow all functionality of .NET including access
  to external resources and unsafe and unmanaged
  code.

8
Our practical examples

• SAFE
• EXTERNAL_ACCESS

9
Preparing your Visual Studio Project to handle
External Access
10
(No Transcript)
11
(No Transcript)
12
Creating the strong key

• Create a Strong key
• Sn k imagekeyFile.snk
• In VS2005 .NET and under project / properties in
  the signing page, click the Sign the Assembly
  check box and browse to find imagekeyFile.snk (as
  below)

13
(No Transcript)
14
The SQL Server side

• Create the target database.
• Create an asymmetric key from the compiled
  assembly using
• USE Master
• GO
• create asymmetric key imageskeyFile
• from executable file 'C\Pass
• 2007\Images\Images\bin\Debug\Images.dll'

15
The SQL Server side

• Create a login based on that asymmetric key using
  a statement like this.
• Use Master
• Go
• Create login ImageMaker from asymmetric
• key imageskeyFile

16
The SQL Server side

• Grant that login the right to create either or
  both an unsafe and / or external access assembly
  (as needed) using
• Use Master
• Go
• Grant EXTERNAL ACCESS assembly to
• ImageMaker

17
The SQL Server side

• Create a user in the database
• e.g. Pass2007
• Grant Create Assembly to ImageMaker

18
The SQL Server side

• Catalog the desired assembly using the now
  trusted asymmetric key with a statement
• SET QUOTED_IDENTIFIER OFF
• USE Pass2007
• GO
• CREATE ASSEMBLY Images
• AUTHORIZATION ImageMaker
• FROM "C\Pass 2007\Images\Images\bin\Debug\Images
  .dll"
• WITH PERMISSION_SET EXTERNAL_ACCESS
• GO

19
Demo 1

• Generating data in HTML format
• SAFE ACCESS

20
Highlights from this demo

• Flexibility of SQL Statement (Code is re-usable).
• Ability to loop efficiently effectively (re
  HTML).
• Assembly is SAFE

21
Demo 2

• Detecting File System Activities
• EXTERNAL ACCESS

22
Highlights from this demo

• Ability to view file system info from a Stored
  Procedure.
• Practical use of FileSystemInfo DirectoryInfo
  .NET classes.
• Assembly is EXTERNAL.
• Read Only (more safe?)

23
Demo 3

• Detecting File DDL Activities
• Using Triggers
• SAFE ACCESS

24
Highlights from this demo

• DBA can now detect DLL based activity via a
  trigger and have the details populated in a
  table.
• XML based data obtained via the EventData Class.
• Assembly is SAFE.

25
Demo 4

• Loading and unloading
• image data
• EXTERNAL ACCESS

26
Highlights from this demo

• CLR Assemblies are useful for loading byte based
  data.
• Use of the FileStream and BinaryWriter .NET
  classes.
• Once again looping helps with bulk loading,
  serialization deserializtion.

27
Highlights from this demo

• Use of a FileSystemWatcher class.
• Use of SQL Server Integration Services.
• Facility of use of CLR Assemblies.

28
During this hour we..

• Looked at CLR Assemblies with a view to
  implementing them with SQL Server 2005 Stored
  Procedures.
• Learnt when it is appropriate to utilize CLR
  Assemblies.
• Learnt when NOT to utilize CLR Assemblies.

29
During this hour we..

• Examined a few practical hands-on examples where
  CLR Assemblies were utilized with Stored
  Procedures.

30
The question really boils down to..Do we
really need CLR Assemblies to achieve our goals
or are they just cool tools?
31
The jury is still out ?
32
Thank you!

• Please remember to complete your evaluation form
  and submit it to the proctor ?
• Thank you for attending this session and the
• 2007 PASS Community Summit in Denver