multi-language extensibility in ms sql server
Post on 01-Feb-2016
48 Views
Preview:
DESCRIPTION
TRANSCRIPT
Multi-Language Multi-Language Extensibility in MS Extensibility in MS
SQL ServerSQL Server
James HamiltonJames HamiltonJamesRH@microsoft.comJamesRH@microsoft.com
Microsoft SQL ServerMicrosoft SQL Server
2002.06.252002.06.25
22
AgendaAgenda Common Language Runtime (CLR) Integration Common Language Runtime (CLR) Integration
Overview:Overview: Basic infrastructureBasic infrastructure Design philosophyDesign philosophy
4 S’s: Safety, Security, Scalability, & Speed4 S’s: Safety, Security, Scalability, & Speed SQL Features Enabled:SQL Features Enabled:
CLR assembliesCLR assemblies Scalar functionsScalar functions Relational functionsRelational functions Aggregate functionsAggregate functions Stored ProceduresStored Procedures TriggersTriggers Types and MethodsTypes and Methods SQL Types PackageSQL Types Package Access methodsAccess methods
Systems & Integration issuesSystems & Integration issues
33
Basic InfrastructureBasic Infrastructure CLR Hosted inside SQL Server:CLR Hosted inside SQL Server:
4S’s: safety, security, scalability, & speed4S’s: safety, security, scalability, & speed Run verified, type-safe code in processRun verified, type-safe code in process .NET Framework languages (VB, C#, Java, …).NET Framework languages (VB, C#, Java, …)
Competitive offerings:Competitive offerings: Oracle & DB2: in-proc Java & JDBCOracle & DB2: in-proc Java & JDBC
Data access in process:Data access in process: Based on ADO.NETBased on ADO.NET Same programming model as middle-tierSame programming model as middle-tier
SQLTypes support:SQLTypes support: SQL type semantics in managed code on client & server SQL type semantics in managed code on client & server
tierstiers
44
Philosophy: Safety & SecurityPhilosophy: Safety & Security Safety:Safety:
User code does not compromise integrity User code does not compromise integrity of server processof server process Verifiable code Verifiable code Leverage CLR’s code access security Leverage CLR’s code access security
User code cannot call UI, create threads, User code cannot call UI, create threads, synchronization, or call unmanaged codesynchronization, or call unmanaged code
Security:Security: Access to SQL data from user code via SQL Access to SQL data from user code via SQL
authorization modelauthorization model Access to system resources from user code Access to system resources from user code
via .NET Framework code permissionsvia .NET Framework code permissions Administrators control permissions given to Administrators control permissions given to
assembliesassemblies
55
Philosophy: Security modelPhilosophy: Security model Resources Resources protected by permissionsprotected by permissions
threads, files, unmanaged code access etc.threads, files, unmanaged code access etc.
API that exposes a resource API that exposes a resource introduces a introduces a demanddemand for that permission for that permission
Stack-walk based permission check: Stack-walk based permission check: every assembly in call-stack has permissionevery assembly in call-stack has permission
Permissions granted to assemblies Permissions granted to assemblies determined by machine and user-level determined by machine and user-level security policysecurity policy
Allows host to add another policy layer to Allows host to add another policy layer to further restrict permissionsfurther restrict permissions
66
Philosophy: 3 Permission SetsPhilosophy: 3 Permission Sets
SAFESQL SAFESQL Internal computation plus data access Internal computation plus data access No access to resources outside SQL Server No access to resources outside SQL Server No unmanaged callsNo unmanaged calls Must be verifiableMust be verifiable
EXTERNAL ACCESS EXTERNAL ACCESS SAFESQL + access to external resourcesSAFESQL + access to external resources Requires EXTERNAL ACCESS permission to create Requires EXTERNAL ACCESS permission to create SQL Server will impersonate the callerSQL Server will impersonate the caller Must be verifiableMust be verifiable
UNRESTRICTED UNRESTRICTED No controls: can call unmanaged, & un-verifiableNo controls: can call unmanaged, & un-verifiable Only Sysadmin can createOnly Sysadmin can create
77
Philosophy: Scalability & SpeedPhilosophy: Scalability & Speed
Scalability:Scalability: As many concurrent users as TSQLAs many concurrent users as TSQL
Integrated SQL and runtime threadsIntegrated SQL and runtime threads Collaboration between SQL and GCCollaboration between SQL and GC
Speed:Speed: Efficient data access in processEfficient data access in process Compiled user code, not interpreted as TSQLCompiled user code, not interpreted as TSQL Fast transitions in/out of runtimeFast transitions in/out of runtime
88
Philosophy: Functions speedPhilosophy: Functions speed
..NET functions approximating speed of TSQL inline NET functions approximating speed of TSQL inline expressionsexpressions.NET Framework functions much faster than TSQL .NET Framework functions much faster than TSQL functions for complex expressionsfunctions for complex expressions
.NET function vs. TSQL inline
0
200
400
600
800
1 3 5 7 9 11 13 15 17 19 21 23 25 27 29
Integer ops per func call
Que
ry ti
me
(ms)
.NET func TSQL inline
.NET function vs. TSQL function
1
10
100
1000
10000
100000
1000000
100 1000 10000
Integer ops per call
Qu
ery
tim
e (m
s)
.NET func TSQL func
99
AgendaAgenda CLR Integration Overview:CLR Integration Overview:
Basic infrastructureBasic infrastructure Design philosophyDesign philosophy
4 S’s: Safety, Security, Scalability, & Speed4 S’s: Safety, Security, Scalability, & Speed SQL Features Enabled:SQL Features Enabled:
CLR assembliesCLR assemblies Scalar functionsScalar functions Relational functionsRelational functions Aggregate functionsAggregate functions Stored ProceduresStored Procedures TriggersTriggers Types and MethodsTypes and Methods Access methodsAccess methods SQL Types PackageSQL Types Package
Systems & Integration issuesSystems & Integration issues
1010
Development StepsDevelopment Steps
VS .NET VS .NET ProjectProject
Assembly: “TaxLib.dll”
VB, C#, …VB, C#, … BuildBuild
SQL ServerSQL Server
SQL Data Definition: SQL Data Definition: create create assembly … assembly … create function … create function … create create procedure … procedure … create trigger … create trigger … create create type …type …
SQL Queries: SQL Queries: select select sum(sum(tax(sal,state)tax(sal,state) ) ) from Emp where county from Emp where county = ‘King’= ‘King’
Runtime hosted inside
SQL
1111
Assembly RegistrationAssembly RegistrationCREATE ASSEMBLY lib_geom FROM ‘\\m1\types\
geometry.dll’ WITH PERMISSION SET SAFE WITH AUTOREGISTER
DROP ASSEMBLY lib_geom
Assemblies stored in DBAssemblies stored in DB Backup, restore, etc. with dataBackup, restore, etc. with data
Code permissions assigned per assemblyCode permissions assigned per assembly SafeSQL, external access, & unrestrictedSafeSQL, external access, & unrestricted
Autoregister functionsAutoregister functions Using .NET custom attributesUsing .NET custom attributes
Assembly benefitsAssembly benefits Self-describing metadata: types & file dependenciesSelf-describing metadata: types & file dependencies Unit of deployment: permissions & versioningUnit of deployment: permissions & versioning
1212
ALTER AssemblyALTER Assembly
Schema bound:Schema bound: Cannot invalidate persistent data or Cannot invalidate persistent data or
indexesindexes No tables with columns of UDT from this No tables with columns of UDT from this
assemblyassembly No indexes on functions of that No indexes on functions of that
assemblyassembly
Packaging considerationsPackaging considerations Place routines and types in different Place routines and types in different
assembliesassemblies
1313
Register A FunctionRegister A FunctionCREATE FUNCTION distance (
@x1 int, @y1 int, @x2 int, @y2 int ) RETURNS float EXTERNAL NAME ‘lib_geom:CPoint.Distance’ DETERMINISTIC RETURNS NULL ON NULL INPUT
DROP FUNCTION distance
Functions called from queries:Functions called from queries: Static class functionsStatic class functions Deterministic functions:Deterministic functions:
No SQL updates or access to global stateNo SQL updates or access to global state Can be scalar or table-valuedCan be scalar or table-valued Using a function in a query:Using a function in a query:
SELECT s.name FROM Supplier sWHERE dbo.distance( s.x, s.y, @x, @y ) < 3
1414
Call FunctionCall Function
SELECT name FROM Employee SELECT name FROM Employee WHERE dbo.tax(salary) >= 50000WHERE dbo.tax(salary) >= 50000
Common Language RuntimeCommon Language Runtime
Managed codeManaged codeUnmanaged codeUnmanaged code
SQLSERVR.EXESQLSERVR.EXE
FilterFilter
ProjectProject
EmployeeEmployee
ScanScan
double tax( double sal ) { double tax( double sal ) { if (sal < 50000.0) return sal * 0.15; if (sal < 50000.0) return sal * 0.15; if (sal >= if (sal >= 50000.0 && sal <= 90000.0) 50000.0 && sal <= 90000.0)
return sal * 0.23; return sal * 0.23; else else return return sal * 0.35; sal * 0.35; } }
1515
Register A ProcedureRegister A Procedure
CREATE PROCEDURE check_inventory
EXTERNAL NAME ‘lib_events:CInventory.check_level’
DROP PROCEDURE check_inventory
Procedures called directlyProcedures called directly Can contain SQL queries, updates, or DDLCan contain SQL queries, updates, or DDL Can return results directly to clientCan return results directly to client Not directly callable (as functions are) from Not directly callable (as functions are) from
queriesqueries
1616
Register A TriggerRegister A Trigger
CREATE TRIGGER supplier_event ON supplier
AFTER INSERT, UPDATE
EXTERNAL NAME ‘lib_events:CNotif.Supp_Event’
DROP TRIGGER supplier_event
Similar to proceduresSimilar to procedures with access to inserted & deleted tableswith access to inserted & deleted tables
1717
UDT: IntroductionUDT: Introduction
Extends SQL Server type systemExtends SQL Server type system Complex structure & associated behaviorComplex structure & associated behavior Managed classes in any CLR languageManaged classes in any CLR language Functionally subsume SQL-99 distinct typesFunctionally subsume SQL-99 distinct types
equivalent to structured typesequivalent to structured types
Other products: Oracle Cartridges, Informix Other products: Oracle Cartridges, Informix DataBlades, & IBM DB ExtendersDataBlades, & IBM DB Extenders
1818
UDT: Ordering valuesUDT: Ordering values Two kinds of ordering supportedTwo kinds of ordering supported Binary ordering indicated via CLR property:Binary ordering indicated via CLR property:
public const bool IsByteOrdered;public const bool IsByteOrdered;
Operator-based orderingOperator-based ordering Overloaded comparison operators:Overloaded comparison operators:
public static SQLBool operator public static SQLBool operator
== (<type>, <type>);== (<type>, <type>);
1919
UDT: CreatingUDT: Creating
Registered as a type from an already Registered as a type from an already registered assemblyregistered assembly
CREATE ASSEMBLY MapLibCREATE ASSEMBLY MapLib
FROM ‘\\mysrv\share\MapLib.dll’FROM ‘\\mysrv\share\MapLib.dll’
CREATE TYPE PointCREATE TYPE Point
EXTERNAL NAME ‘MapLib:Basetypes’EXTERNAL NAME ‘MapLib:Basetypes’
2020
UDT: InstantiatingUDT: Instantiating
Can be declared as column type:Can be declared as column type:
Create table Cities(Create table Cities(
Name varchar(20), Name varchar(20),
State varchar(20), State varchar(20),
Location Point DEFAULT new Point(0,0))Location Point DEFAULT new Point(0,0))
Variables & params of T-SQL & .NET Variables & params of T-SQL & .NET routines can UDTs routines can UDTs
2121
UDT: Read operationsUDT: Read operations
Registered methods, properties, & public Registered methods, properties, & public data members can be use in SQL queriesdata members can be use in SQL queries
Methods assumed to be non-mutators unless Methods assumed to be non-mutators unless marked using custom attributemarked using custom attribute
Only non-mutators allowed in SELECTsOnly non-mutators allowed in SELECTs
Declare @p Declare @p
Set @p = new point(32, 23)Set @p = new point(32, 23)
Select Location::Distance(@p)Select Location::Distance(@p)
From CitiesFrom Cities
2222
UDT: Ordering OperationsUDT: Ordering Operations
Indexing, UNIQUE, & PRIMARY KEY Indexing, UNIQUE, & PRIMARY KEY constraints:constraints: Requires binary ordering supportRequires binary ordering support Inconsistent operator based ordering can cause Inconsistent operator based ordering can cause
corrupt indices, incorrect query resultscorrupt indices, incorrect query results
ORDER BY, GROUP BY, & comparison ORDER BY, GROUP BY, & comparison operators:operators: If UDT supports binary ordering, always use If UDT supports binary ordering, always use
binary orderingbinary ordering Else use overloaded operators; rely on Else use overloaded operators; rely on
consistency and correctness of UDT consistency and correctness of UDT implementationimplementation
2323
UDT: UDT Write OperationsUDT: UDT Write OperationsConstructor can be called using new Constructor can be called using new
operatoroperator
UPDATE CitiesUPDATE Cities
SET Location = new Point(12.3, 46.2)SET Location = new Point(12.3, 46.2)
Properties and public data members Properties and public data members can be modified through assignmentcan be modified through assignment
UPDATE CitiesUPDATE Cities
SET Location::X = 23.5, SET Location::X = 23.5,
Location::Y = 23.5Location::Y = 23.5
WHERE Name = ‘Anchorage’WHERE Name = ‘Anchorage’
2424
UDT: Write OperationsUDT: Write Operations
Methods marked mutators callable in Methods marked mutators callable in UPDATEsUPDATEs
UPDATE CitiesUPDATE Cities
SET Location::SetXY(23.5, 23.5)SET Location::SetXY(23.5, 23.5)
WHERE Name = ‘Anchorage’WHERE Name = ‘Anchorage’
INSERTs get values from string, binary INSERTs get values from string, binary form or from constructor invocationform or from constructor invocation
INSERT Cities(‘Anchorage’, ‘Alaska’, ’19.3:2.3’)INSERT Cities(‘Anchorage’, ‘Alaska’, ’19.3:2.3’)
2525
UDAggs Required MethodsUDAggs Required Methods
Implement QP interface to aggregate Implement QP interface to aggregate values over a groupvalues over a group
Interface needed:Interface needed: Initialize a groupInitialize a group Pass values in the group to accumulate Pass values in the group to accumulate
the aggregationthe aggregation Merge multiple groups (for parallel plans)Merge multiple groups (for parallel plans) Finalize computation and retrieve resultFinalize computation and retrieve result
2626
Creating UDAggsCreating UDAggs
Aggregates implemented as set of Aggregates implemented as set of methods packaged in a .NET classmethods packaged in a .NET class
UDAgg is bound to a class in existing UDAgg is bound to a class in existing assemblyassembly
CREATE AGGREGATE Concat(nvarchar)CREATE AGGREGATE Concat(nvarchar)
RETURNS nvarcharRETURNS nvarchar
EXTERNAL ‘MyLib:Concat’EXTERNAL ‘MyLib:Concat’
2727
UDAgg class definition e.g.UDAgg class definition e.g.
Public class Concat {Public class Concat {//Private store for accumulating results.//Private store for accumulating results.private SQLString agg;private SQLString agg;
//Optimizer properties//Optimizer propertiespublic static bool IsNullOnNullsSet() {return true;}public static bool IsNullOnNullsSet() {return true;}public static bool IsNullOnEmptySet() {return false;}public static bool IsNullOnEmptySet() {return false;}
//Aggregation interface//Aggregation interfacepublic void Init();public void Init();public void Accum(SQLString str);public void Accum(SQLString str);public void Merge(Concat otheragg);public void Merge(Concat otheragg);public SQLString Term();public SQLString Term();
}}
2828
Final Stage: Access MethodsFinal Stage: Access Methods Complete extensibility solution requires Complete extensibility solution requires
user defined access methods:user defined access methods: Informix supports adding access methods:Informix supports adding access methods:
Expose locking, concurrency control, recovery, Expose locking, concurrency control, recovery, etc.etc.
Hard to make work in general … few engineers Hard to make work in general … few engineers able to write internal storage engine code wellable to write internal storage engine code well
Many will try and they will have bugs … hard on Many will try and they will have bugs … hard on product quality imageproduct quality image
Solution: Tailor existing access methodsSolution: Tailor existing access methods E.g. map spatial queries to queries over 2 B-E.g. map spatial queries to queries over 2 B-
trees or single Z-transformtrees or single Z-transform Query rewrite language such that system can Query rewrite language such that system can
rewrite a function as an appropriate set of rewrite a function as an appropriate set of operations over 1 or more tables/indexesoperations over 1 or more tables/indexes
2929
CLR SQL Types PackageCLR SQL Types Package Defines C#, VB, & Java types Defines C#, VB, & Java types
corresponding SQL Server typescorresponding SQL Server types Reduce impedance mismatch between Reduce impedance mismatch between
programming language & dataprogramming language & data Consistent expression evaluation in Consistent expression evaluation in
mid- & server-tier programmingmid- & server-tier programming SQL Types library SQL Types library
Managed classes: system.Data.SQLTypesManaged classes: system.Data.SQLTypes Provide SQL semanticsProvide SQL semantics
Nullability, three-valued logicNullability, three-valued logic Precision & scale in operationsPrecision & scale in operations
3030
SQL Types ExampleSQL Types Example
Tax function implemented with SQL types:Tax function implemented with SQL types:
using System; using System; using System.Data.SQLTypes; using System.Data.SQLTypes;
public class myFinances public class myFinances { { public static SQLDouble tax( SQLDouble sal ) public static SQLDouble tax( SQLDouble sal ) { { if ( sal < 50000.0 ) return sal * 0.15; if ( sal < 50000.0 ) return sal * 0.15; if ( sal >= 50000.0 && sal <= 90000.0 ) return sal if ( sal >= 50000.0 && sal <= 90000.0 ) return sal * 0.23 * 0.23 else return sal * 0.35; else return sal * 0.35; }}
}}
3131
AgendaAgenda CLR Integration Overview:CLR Integration Overview:
Basic infrastructureBasic infrastructure Design philosophyDesign philosophy
4 S’s: Safety, Security, Scalability, & Speed4 S’s: Safety, Security, Scalability, & Speed SQL Features Enabled:SQL Features Enabled:
CLR assembliesCLR assemblies Scalar functionsScalar functions Relational functionsRelational functions Aggregate functionsAggregate functions Stored ProceduresStored Procedures TriggersTriggers Types and MethodsTypes and Methods Access methods Access methods SQL Types PackageSQL Types Package
Systems & Integration issuesSystems & Integration issues
3232
Integration: In-Proc Data AccessIntegration: In-Proc Data Access
Goals:Goals: Symmetric modelSymmetric model
Client, server, & mid-tierClient, server, & mid-tier Fully supported by dev toolsFully supported by dev tools
VS dev environment including debugVS dev environment including debug
In same address space as server:In same address space as server: Don’t marshal to TDS (tabular data stream)Don’t marshal to TDS (tabular data stream) Don’t loop-back through network interfaceDon’t loop-back through network interface Avoid unnecessary copiesAvoid unnecessary copies Avoid unnecessary transitions across Avoid unnecessary transitions across
managed/unmanaged (into VM) interfacemanaged/unmanaged (into VM) interface
3333
Integration: Process ModelIntegration: Process Model Thread integration:Thread integration:
SQL Server: non-preemptive user-level thread schedulingSQL Server: non-preemptive user-level thread scheduling Fibers multiplexed on O/S thread with thread migrationFibers multiplexed on O/S thread with thread migration Implication: TLS storage used by VM won’t work in SQL Implication: TLS storage used by VM won’t work in SQL
execution environmentexecution environment Lazy preemptive:Lazy preemptive:
When SQL calls potentially blocking code it must “go When SQL calls potentially blocking code it must “go preemptive” (allocate a thread)preemptive” (allocate a thread)
So thread required for each VM call – expensiveSo thread required for each VM call – expensive Lazy pre-emptive: assume preemption not required and Lazy pre-emptive: assume preemption not required and
set timer to catch rare cases when thread allocation is set timer to catch rare cases when thread allocation is neededneeded
Garbage collection blocks VM threads:Garbage collection blocks VM threads: Hard on multi-user DB throughputHard on multi-user DB throughput GC doesn’t directly suspend – calls DB to suspend and GC doesn’t directly suspend – calls DB to suspend and
DB schedules other non-VM hosted workDB schedules other non-VM hosted work
3434
Integration: Memory ManagementIntegration: Memory Management
Memory allocation from DB system Memory allocation from DB system (rather than via O/S):(rather than via O/S): Allows memory resources to be used for Allows memory resources to be used for
different purposes over timedifferent purposes over time Garbage collection very general but DB Garbage collection very general but DB
systems often have more info:systems often have more info: e.g. free memory pool at end of e.g. free memory pool at end of
statementstatement Goal:Goal:
DB memory costing able to request DB memory costing able to request memory from VM when needed memory from VM when needed
Per user memory consumption Per user memory consumption tracking & quotastracking & quotas
3535
Integration: Remaining IssuesIntegration: Remaining Issues Assemblies (code) kept in database:Assemblies (code) kept in database:
Dispatched to VM through buffersDispatched to VM through buffers Stream interface would be better yetStream interface would be better yet
Support attention propagation (user Support attention propagation (user interrupt) into VMinterrupt) into VM
Track all VM resourcesTrack all VM resources O/S portability layer tailored to DB execution O/S portability layer tailored to DB execution
environmentenvironment Error containment/minimize multi-user Error containment/minimize multi-user
impact of failures:impact of failures: Out-of-proc executionOut-of-proc execution Multiple VMsMultiple VMs
3636
Integration: Query optimizationIntegration: Query optimization
Gather function cost statisticsGather function cost statistics Value histograms, execution costValue histograms, execution cost
Reorder of predicate evaluationReorder of predicate evaluation Based on cost of evaluationBased on cost of evaluation
Function indexesFunction indexes Speed up expensive functionsSpeed up expensive functions Extends computed column indexes and Extends computed column indexes and
indexed (materialized) viewsindexed (materialized) views
3737
CLR Integration SummaryCLR Integration Summary
DB extensibility without product DB extensibility without product stability riskstability risk
Ability to add types and behavior w/o Ability to add types and behavior w/o access to engine source codeaccess to engine source code Scalable, safe, secure & fastScalable, safe, secure & fast
Rich server programming modelRich server programming model Any CLR languageAny CLR language Symmetric mid- & server-tier modelSymmetric mid- & server-tier model
Better development environmentBetter development environment Integrates SQL tools with Visual StudioIntegrates SQL tools with Visual Studio
top related