Preview of the SQL Server

Preview Of The SQL Server .NET File System Jonathan Morrison SDE SQL Server Microsoft Corporation 2-224 ™ SQL Server .NET FS Overview      Motivation and goals Architecture File system features SQL features Using the file system Motivation  The Internet is evolving quickly   Initially mostly static Web pages Now dynamic content is common Unstructured data (pages, images, templates, etc.) Structured data (stored in databases)  Internet data is now generally of two types:     With the emergence of XML, these datasets are merging We need new technology to assist in building future Web applications and services Goals      Extend SQL Server to support files (unstructured data) Support existing file-based apps including development tools File system streaming performance Integrated administration of all data Powerful SQL queries Goals (Scenarios)  Existing mixed-mode Web applications   Easy to migrate Easy to enhance Easy to build and maintain Benefit from integrated structured and unstructured support Benefit from the power of a true database (transactions, etc.) Add support for semi-structured data  Web applications/services     Traditional database applications  What Is SQLFS      File system built on SQL Server Hierarchy and properties stored in SQL tables File streams stored as BLOBs Win32® access through a file system driver New views/functions expose data to SQL clients Dual Mode Data Access File-based App Accesses Files via Win32 MyAppData X: C: Winnt Docs Utils Foo.txt Data Other SQLFS Tables File01.txt File02.txt BigPitch.ppt Proposal.doc Report97.doc MyApp.exe Custom.cmd Hierarchy info kept here MyDatabase.mdf DB-aware App Accesses Data via T-SQL & OLE DB Application-specific Table Key Col1 UserfileCol FILEID reference Col3 SQLFS Architecture DBMS application queries Win32 application I/O requests SQLFS User Mode SQL Server User Mode Kernel Mode Win32 API DLL I/O Manager Database NTFS Disk Driver TCP/IP SQLFS Tables SQLFS Kernel Driver SMB Srvr SMB requests (Transmit File, etc.) File System Features    Just another file system device Support for network shares, IIS, etc. Designed to leverage the Windows NT® cache manager/VMM   Support cached and memory mapped I/O For cached I/O, driver only sees cache faults, lazy writes More Features       NTFS style security Support for short names (8.3) Byte range locking Oplocks Directory notifications TransmitFile support Locks, Transactions, Etc.  File system applications behave very differently from traditional database apps:     File locks can be held for a very long time (weeks) Fail on lock contention (no wait) Long delays between write and flush Shared write support More On Locks  SQLFS implements long term locks   Lock is held on contents Properties and directory structure available for query  Copy on write strategy implemented for cached I/O (commit on flush)  Ensures consistent backup, etc.  Shared write supported  Multiple Win32 apps can read and write the same file simultaneously FS Versus DBMS Style Locks DBMS-aware Application A Write to blob for file DBMS-aware Application B Read from blob for file Win32 Application C Open file write-shared Win32 Application D Open file read-shared OLE DB DBMS write locks always exclusive FS allows nonexclusive write shared locks Win32 Subsystem OS Kernel SQLFS Kernel Mode Driver Separate Win32 clients combined into a single DBMS client, sharing locks and transactions Request Sequence Axxx Bxxx Cxxx DABC DACB DBxx DCxx Database SQL Server SQLFS User Mode Lock contention between Win32 clients resolved in kernel driver Access result for: A B C D SQLFS Tables = Access granted = Request blocked = Access denied Demo Basic File System Access SQL Features  File system exposed as a set of tables   Row represents a file Each file has a unique id     One or more file systems per database Files can be queried and modified using standard SQL All file system management functions are available through SQL User data tables refer to files by id SQL File System Views fileitems Column Name Data Type id uniqueidentifier name nvarchar(260) created datetime last_write datetime accessed datetime size bigint owner nvarchar(128) is_folder bit needs_archive bit is_hidden bit is_read_only bit is_system bit    filecontents Column Name id format contents Data Type uniqueidentifier nvarchar(128) image fileitemlinks Column Name parent_id child_id name Data Type uniqueidentifier uniqueidentifier nvarchar(260) fileitems is a flat view of the file system fileitemlinks stores the hierarchy information contents is full-text indexed to allow powerful queries Sample T-SQL DDL CREATE TABLE Articles ( ArticleID int NOT NULL, Title nvarchar(100) NOT NULL, Date datetime NOT NULL, Aurthor nvarchar(40) NOT NULL, Source nvarchar(40) NOT NULL, Article uniqueidentifier as FILEID) (syntax is still under development and may change) Sample T-SQL Queries  Find all files created this week:  SELECT * FROM FileItems WHERE created >= „07-09-00‟  Get the contents of directory „\PDC‟:  SELECT * FROM FileItemLinks WHERE parent_id = FN_GETFILEID(„\PDC‟)  Get ID and full path for all articles written by Joe Smith:  SELECT ArticleID, FN_FILEPATH(Article) + FN_FILENAME(Article) FROM Articles WHERE Author=„Joe Smith‟ (syntax is still under development and may change) Demo More SQL Features   Managing related relational data and files is a common problem for mixedmode applications SQLFS provides referential integrity constraints   Files can't be deleted if referenced Cascade delete allows for automatic maintenance Transactional Updates    Updating file data and relational data in a single transaction is difficult today With SQLFS, file data and properties can be updated in a SQL transaction Common Scenarios:   Workflow, Document Management Web site updates Applications  Previous features make it very easy to migrate simple mixed-mode apps:    Copy files into the database Optionally add RI and transactions All existing Win32 code (http: access) still works  Why add file support to traditional relational apps?    A lot of data is still stored in files Support for file based applications Organizing data in hierarchies SQLFS And The Web  Works with existing server apps  E.g., just create a VROOT that points to a SQLFS volume to publish via IIS     Can be used in conjunction with the XML support (storing templates, XML files, fragments, etc) Easy to manage Transacted updates More technology coming Single Point Of Admin  Through SQL you can:     Manage files Update files and relational data in a single transaction Backup and restore all the data together Additional features just work (replication, bulk load, warm standby, etc.)  SQL Server scalability and reliability Demo Web Applications SQLFS Availability   The SQL Server .NET File System will ship with the next release (after SQL Server 2000) Working on a plan to provide a “developer preview” release of this technology in the next 6-9 months Summary     SQL is committed to the Internet and this FS is just a small part of a solution that includes improved XML support, http: support, etc. Great for Web scenarios: powerful query support, integrated administration, reliability, scalability Easy migration of mixed-mode Web applications Full support for Win32 File API based apps including development tools Related Sessions And References  Other PDC sessions recommended:   2-322: SQL Server 2000 and XML 2-336: SQL Server and .NET Framework Integration