DBA 221 TSQL Versus NET Database Programming Dispelling
DBA 221 TSQL Versus. NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero Solid Quality Learning fernando@solidqualitylearning. com
TSQL vs. . NET… is this some kind of war? Is. NET the “silver bullet” for all our computing needs – what is it? Does “managed” code really handle everything? Is. NET limited to SQL Server access only? Is TSQL becoming obsolete? How does. NET impact existing applications? Portions of this session is based on a Tech. Ed 2003 USA presentation from Don Awalt and Brian Lawton, from RDA Corporation
Quick info about Fernando (2 milliseconds) MCSD, MCSE+Internet (W 2 K), MCDBA, MCT, SQL Server MVP CEO and principal mentor at Solid Quality Learning Writing for SQL Sever Magazine and SQL Server Professional Co-author of Microsoft SQL Server 2000 Programming by Example (ISBN : 0789724499) Author of the. NET Operations Guide and the. NET Deployment Guide for Microsoft Tech. Net
Solid Quality Learning (3 ms) An association of SQL Server experts from around the world Principal Mentors: Itzik Ben-Gan Kalen Delaney Fernando G. Guerrero Michael Hotek Brian Moran Ron Talmage Kimberly L. Tripp Helping you get the best out of your SQL Server: Training Consulting Mentoring Stay tuned to http: //www. Solid. Quality. Learning. com
Objective To present important concepts about. NET in order for DBAs to remain the stewards of their organization’s databases Addressing “common myths” about. NET of concern to DBAs Recommended Sessions: DAT 220: “SQL Server Yukon: . NET and the DBA” DAT 234: “SQL Server Yukon: . NET Programming Features” This is not a talk for. NET experts or application developers!
Agenda. NET and the. NET Framework. NET Interoperability with SQL Server Deployment of applications using the. NET Framework Data Access versus TSQL Using Visual Studio. NET to troubleshoot
Agenda. NET and the. NET Framework. NET Interoperability with SQL Server Deployment of applications using the. NET Framework Data Access versus TSQL Using Visual Studio. NET to troubleshoot
Terminology if (application == built with Web Services) Application = a. NET Application; else if (application == utilizes the. NET Framework) Application = a Windows application built using the. NET Framework; else Application = a Windows application;
While most people say a “. NET App” or “. NET”, what they really mean is “An application built using the. NET Framework” or “the. NET Framework”!
Benefits Of. NET Framework A common runtime engine used by all. NET-aware languages Optimize and enrich Resource and memory management Rich class library Shelters raw API and COM plumbing Standard data types Type-safety Consistent error handling Full interoperability with existing (COM) code
Benefits Of. NET Framework Truly simplified deployment Component version management (even the Framework itself!) Self-describing components No more binary registrations (COM) More secure execution model Platform independent (Windows 98) Goes beyond who is running the code Code -> Security Policy -> Permissions Verifiable code (itself a permission)
What Is An XML Web Service? Evolution of applications and Web sites Access – beyond just people Strategy for reuse Publish and/or consume Secure interactions across trust boundaries New integration methodology XML Open standards Interoperability efforts Can expose existing code as Web service
Why XML Web Services? Broad industry support Major standards authored by Microsoft and IBM Already 60+ implementations “Everyone should build on the XML/SOAP foundation” XML Web Services Foundation –Meta Group Publish, Find, Use Services: UDDI Service Descriptions: WSDL Service Interactions: SOAP Universal Data Format: Ubiquitous Communications: XML Internet
The. NET Framework Is A runtime – Common Language Runtime (CLR) The CLR locates, loads and manages data types – Common Type System (CTS) Standards have been defined to ensure common types – Common Language Specification (CLS) The base class library provides a simplified programming model
CLR Multi-language Support Object system is built-in, not bolted on Language of choice Cross-language inheritance and exceptions Supports more than 20 languages Consistent tools across all languages IDE Debugger
. NET Framework System. Windows. Forms System. Web Services Controls Web Forms ASP. NET Application Services Drawing Windows Application Services System Base Classes ADO. NET XML SQL Threading IO Net Security Service. Process Common Language Runtime Memory Mgmt Type System Lifetime
What Is An Assembly? A logical container for a set of modules/files Contents described by a manifest A managed unit of deployment Metadata references a specific version of an assembly Defines security boundaries
What Is MSIL? Microsoft Intermediate Language Instructions for an abstract computer All. NET languages compile to MSIL Instructions are verifiable or not Can compile "unsafe" languages like C into MSIL Tools ildasm (. NET Framework SDK) reflector (http: //www. aisto. com/roeder)
CLR Compilation and Execution Compilation Source Code Language Compiler Execution Native Code JIT Compiler Code MSIL Metadata Before installation or the first time each method is called
Agenda. NET and the. NET Framework. NET Interoperability with SQL Server Deployment of applications using the. NET Framework Data Access versus TSQL Using Visual Studio. NET to troubleshoot
. NET Interoperability. NET Framework (hence code managed by the CLR) is not used today by SQL Server! Issue: All native SQL Sever 2000 interfaces are COM based; How do we use them from the. NET Framework? Examples Data Transformation Services (DTS) Data Manipulation Objects (SQLDMO)
Calling COM Objects …from managed code COM: Unmanaged components Need to expose COM objects as. NET equivalents COM objects need reference counts Ideally, it would be transparent…
Runtime Callable Wrapper (RCW) Acts as a proxy for the COM object 1 per object (ref count) Generated with a tool (tlbimp. exe) Handles some COM plumbing interfaces itself (IClass. Factory, IDispatch, IError. Info)
Calling Managed Code …from COM objects. NET Assemblies can “fool” the COM runtime Registered in the system registry to be located Generate a COM type library Deploy the assembly where COM can find it (COM client folder or Global Assembly Cache) Typically this is less of an issue…
. NET Interoperability IUnknown Common Language Runtime COM Server Object IFoo Runtime Callable Wrapper Reference Counted Client
Agenda. NET and the. NET Framework. NET Interoperability with SQL Server Deployment of applications using the. NET Framework Data Access versus TSQL Using Visual Studio. NET to troubleshoot
How To Improve Deployment Eliminate “DLL Hell” Installation – registry registration impacts Moving applications, uninstalling applications, reinstalling applications Security – track more than just “who’s running the code”
Deployment Of Assemblies. NET Framework is required Available for free from Microsoft Included with Windows Server 2003 Support for multiple versions Frameworks –. NET 1. 0 and 1. 1 Side-by-side component execution No more DLL Hell! Install – file copy* Uninstall – file delete*
No Registration Required Code is self-describing Types scoped to referenced assemblies Version-aware Privately-deployed assemblies Shared assemblies (GAC) Strongly-named assemblies – “safe” Filename, version, culture, public key token No weakly-named assemblies in the GAC
demo Deploying…
Agenda. NET and the. NET Framework. NET Interoperability with SQL Server Deployment of applications using the. NET Framework Data Access versus TSQL Using Visual Studio. NET to troubleshoot
Data Access ADO. NET Integrated into the. NET Framework Type-safe standard data types XML integration XML is the native data format!! Designed for disconnected, n-Tier application architectures Supercedes OLEDB, ADO, and ODBC
ADO. NET …an evolution from ADO Strong demand for faster, more scalable applications XML proved to be lighter weight and provides greater flexibility over HTTP ADO 2. 5 introduced propriety support for XML – Advanced Tablegram (ADTG) format ADO does not handle disconnected recordsets and XML integration well Better XML support in MSXML 3. 0, but not optimal
ADO. NET …an evolution from ADO focuses on “relational” data XML focuses on “non-relational” data Well suited for hierarchies ADO. NET offers “next generation” data access by leveraging the best of both worlds
ADO. NET Concepts: Data classes Containers for data Know nothing about the database Key Objects Data. Table – Disconnected/ In-memory cache NOT related to a “database” table! Data. Set – Collection of Data. Tables, their relations, and constraints
ADO. NET Concepts: Database classes Interact with data sources Utilize managed providers Key Objects Data. Adapter – Connects a Data. Set to a database Data. Reader – Provides the Data. Adapter a “bindable” data stream (a. k. a. a forward-only, read-only, client-side cursor)
ADO. NET Architecture Presentation Tier Windows Forms My. App. Exe Data. Set Business Tier Internet Intranet Web Forms Data Object (Class) Data. Set IE Data. Set Business to Business (Biz. Talk, for example) Data Tier Data Adapter XML Data Adapter
ADO. NET Managed Providers Provide interaction with data sources Managed equivalent of an OLEDB layer Targeted and optimized for data source Standard Providers (in-the-box) System. Data. Ole. Db legacy support to any data source System. Data. Sql. Client – SQL Server Designed to access SQL Server 7. 0 or higher For older versions, use System. Data. Ole. Db Oracle, ODBC providers also available
ADO. NET SQL Server Interaction Virtually transparent to the DBA Impacts applications only! Requires MDAC 2. 6 or later Windows 2000: MDAC 2. 5! MDAC 2. 7 SP 1 is recommended When updating MDAC, also update server catalogs instcat. sql
SQL Server Interaction Connected versus Disconnected data access User interaction is required If “memory” data is required Distributed data Only supports optimistic locking Typical “Connected” data access is not supported! No server-side cursors If required, use a COM-based ADO
demo Using ADO. NET
TSQL Versus. NET Location of application logic Classic issue remains No one right answer! Consolidate in the database (TSQL) Typical 2 -tier/cient-server environment Potential to reduce maintenance costs Distribute in an application layer (. NET) Typical n-tier architecture Increases flexibility and scalability
TSQL Versus. NET Row versus Set-based Operations Classic issue remains Leverage their strengths TSQL – set-based operations on large datasets. NET – complex procedural logic and CPU intensive operations Be wary of pulling too much data! Data. Sets are NOT an in-memory relational database
Transactions Transaction Model. NET introduces no changes! All existing means of handling transactions, be it through middle-tier code or directly within TSQL remain
Agenda. NET and the. NET Framework. NET Interoperability with SQL Server Deployment of applications using the. NET Framework Data Access versus TSQL Using Visual Studio. NET to troubleshoot
Development Tools Visual Studio versions Visual Studio 2002 -. NET Framework 1. 0 Visual Studio 2003 -. NET Framework 1. 0/1. 1 Provides deep database integration Database projects Version control Integrated debugging Stepping stone for Yukon!
Visual Studio …an introduction Database Project Used to organize “database” objects Stored procedures, TSQL scripts, functions, etc… Data Connections Identifies the target server Enables access to database objects Version Control Full Source. Safe integration!
Visual Studio …an introduction Debugging Can be used independently or in conjunction with managed code To “step” through TSQL, you must set a breakpoint within the code Limited to code inspection only Triggers must be fired to be debugged
demo Debugging And Profiling ADO. NET
Summary Is. NET is the “silver bullet” for all our computing needs – what is it? Does “managed” code really handle everything? Is. NET limited to SQL Server only? Is TSQL becoming obsolete? How does. NET impact existing applications?
Appendix…. NET Framework Downloads. Net Frameworks 1. 0 and 1. 1 ODBC Managed Provider Oracle Managed Provider http: //msdn. microsoft. com/library/default. asp? url=/do wnloads/list/netdevframework. asp
Appendix Debugging Stored Procedures http: //support. microsoft. com/default. aspx? scid=kb; en -us; 316549 Data Access Technologies Downloads MDAC 2. 6 MDAC 2. 7 SP 1 http: //msdn. microsoft. com/library/default. asp? url=/nh p/Default. asp? contentid=28001860
Appendix Microsoft Patterns and Practices Best Practices Reference Architectures http: //msdn. microsoft. com/practices/ Data Access Application Block Re-usable subsystem design http: //msdn. microsoft. com/library/default. asp? url=/libr ary/en-us/dnbda/html/daab-rm. asp
Summary SQL Profiler as Management Tool Looking under the hood of database tools Defining effective Traces Scripting SQL Server Traces Do you love Profiler a bit more? ♥
Ask The Experts Get Your Questions Answered I’ll be at the Ask the Experts area: 2 July 2003: 12: 00 -14: 00 3 July 2003: 11: 00 -13: 00 4 July 2003: 12: 00 -14: 00 You’ll see me around some more times
Community Resources http: //www. microsoft. com/communities/default. mspx Most Valuable Professional (MVP) http: //www. mvp. support. microsoft. com/ Newsgroups Converse online with Microsoft Newsgroups, including Worldwide http: //www. microsoft. com/communities/newsgroups/default. mspx User Groups Meet and learn with your peers http: //www. microsoft. com/communities/usergroups/default. mspx
Suggested Reading And Resources The tools you need to put technology to work! TITLE Available Microsoft® SQL Server™ 2000 High Availability: 0 -7356 -1920 -4 7/9/03 Microsoft® SQL Server™ 2000 Administrator's Companion: 07356 -1051 -7 Today Microsoft Press books are 20% off at the Tech. Ed Bookstore Also buy any TWO Microsoft Press books and get a FREE T-Shirt
Thank you! Questions? Download the source code of this session from: http: //www. solidqualitylearning. com/conferences You can contact me at: fernando@solidqualitylearning. com
evaluations
© 2003 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.
- Slides: 61