Client-Server Application Development Using Microsoft Access 2.0

The oldest article that I've been able to find that's related to my early Access client/server development projects is the "Client-Server Application Development Using Microsoft Access 2.0" presentation from Tech•Ed 95 by Microsoft's Christopher Payne. (Chris Payne is now Corporate Vice President, MSN Search.)

The article—last modified on February 26, 1995—had been available as a Web page from http://www.microsoft.com/Accessdev/ARTICLES/AC302.HTM. Details are preserved here because the Web version is no longer available from the Microsoft site, and it appears to have disappeared from the Google cache. Figures were missing from the cached source copy. Microsoft Knowledge Base article Q129613 has a link to a downloadable Microsoft Word version of the article.

My Access 2.0 applications that inspired this article were NSC_ASK.mdb and MSA_OLTP.mdb. NSC_ASK.mdb was a client/server decision-support application for National Semiconductor Corp., which is described in my "An (Almost) Lifetime of BASIC" archive post. I wrote MSA_OLTP.mdb as a contract project for Greg Nelson, an early Access marketer.

National Semiconductor's Margaret Lam and Greg Nelson demonstrated the two applications at Tech•Ed 1995's AC302 session—about a year and a half after Microsoft released Access 1.0 in November 1993. Special Edition Using Access 95 and subsequent editions have used MSA_OLTP.mdb to test backward compatibility and compare client/server data retrieval and update performance with unbound controls.

Remarkably, much of the content of this article still applies to client/server projects with current Access versions. MSA_OLTP.mdb was designed for heads-down telephone order entry by customer service representatives. All order lookup, new order entry, and new customer addition operations use keyboard shortcuts; a mouse isn't required.

The following screen captures of MSA_OLTP.mdb—running in Access 2007 Beta 2—illustrate basic customer and order search, and new order-entry operations. Click the images to display full-size versions in a new window.

Customer lookup (above) starts by typing the first character or two of the company name into the textbox and pressing Enter to open a list box of matching company names if more than one customer meets the search criterion. Selecting the customer in the listbox and pressing Enter opens another listbox of the customer's orders in reverse date sequence. Selecting the order to review and pressing enter displays the line items for the order in another Access list box. Pressing Alt+N or clicking the New Order button activates the order entry phase. Marking the Same as Bill To check box populates the shipping address fields with data from billing address. The capability to select products by numeric ID or the first one or two characters of the product name expedites line item entries.

The following description of the AC302 presentation is excerpted from the introduction to "Client-Server Application Development Using Microsoft Access 2.0."

---

Client-Server Application Development Using Microsoft Access 2.0 Presented by: Christopher Payne Christopher Payne has been with Microsoft for 4 1/2 years and has been focusing on research and planning future versions of Microsoft Access for the past 2 1/2 years. In addition to working on Microsoft Access, Chris was the project lead of the Microsoft Access Upsizing Tools released in October of 1994.

This presentation was originally created by Greg Nelson and Roger Jennings. Greg Nelson is a Senior Access Product Marketing Manager for Microsoft. Before coming to Access Marketing, Greg spent eight years as a developer and technical manager in Microsoft’s MIS department. He has 12 years’ experience in the software industry. Roger Jennings is the author of two books about Microsoft Access and is a consultant in the field of Windows database applications.

Session Goal The goal of this session is to provide Microsoft® Access developers with an understanding of the issues involved in developing efficient, optimized Microsoft Access version 2.0 applications for the client/server environment.

What You Will Learn

1. How Microsoft Access architecture operates and how it implements client/server computing.

2. How to optimize Microsoft Access to take advantage of its client/server capabilities.

3. How to use transaction processing system design issues as references for structural ideas and actual code segments optimizing client/server applications.

Overview

This course breaks down into two distinct sections:

1. The first section is an architectural overview of client/server application development technologies using Microsoft Access. This section provides essential background on each component, explains how the components are used, and how to optimize your application for your environment.

2. The next section is an overview of a transaction processing system. This application is the Northwind Traders sample database that ships with Access version 2.0.

For the purpose of this class, it has been migrated to a client/server environment giving us an opportunity to discuss the issues and solutions involved in migrating a transaction-processing file server application to client/server with a database many of us are familiar with. The applications will be available on your Tech*Ed compact disk. The application is for your personal use to learn from and reuse code segments. They cannot be redistributed for production use.

The reader is assumed to be familiar with the design of Microsoft Access version 2.0 applications and conversant with Access Basic programming techniques. No prior experience with using the Structured Query Language (SQL) database data sources is assumed for this session. This session is about optimizing database server usage in Microsoft Access.

---

Technorati Tags: Microsoft Access, Access 2.0, Client/Server, Access Basic, ODBC, TechEd, TechEd 95, TechEd 1995, AC302, Databases

An (Almost) Lifetime of BASIC

From Roger Jennings: "An (Almost) Lifetime of BASIC", an entry in Apress's 2001 VB @ 10 Years Project as previously cached by Google.com.

The five-year-old content is preserved here because the original verion is no longer available from the Apress site or from the Google cache. Visual Basic celebrates it's 15th anniversary on May 20, 2006.

The "Happy 15th to Visual Basic" post from Paul Vick's web site contains the text of Microsoft's May 20, 1991 press release, "Microsoft Announces Visual Basic at Windows World '91: General-Purpose, High-Productivity Programming System for Microsoft Windows."

---

My introduction to BASIC arrived in the mid '70s with the delivery of one of the first Wang 2200 "desktop" computers. My company at the time—Fluidyne Instrumentation—had been producing data acquisition and control (DAC) systems based on the earlier Wang 720 calculators which stored assembly-level programs on cassette tapes. The advent of interpreted BASIC reduced programming cost by a factor of twenty or more and led to a worldwide market for the product line.

We ultimately replaced the Wang 2200s with lower-cost Commodore CBMs that had BASIC in 20 KB of ROM, 32 KB of RAM, and an IEEE-488 interface for 5-1/4-inch floppy drives, our DAC boxes, and, ultimately, 5-MB+ fixed disk drives. One of our highest-volume products of the early 1980s was an IEEE-488-to-Centronics converter to drive a variety of impact printers.

Ten years and a couple of companies later, I decided to give up traveling sixty percent of my life and started writing commercial database programs in dBASE III+ and Clipper. I got hooked on relational databases—a strange addiction for someone who spent the late '50s at U.C. Berkeley—and have never been able to get the database monkey off my back.

Windows 3.0 arrived while I was consulting for the Sixth International Conference on AIDS in San Francisco, and Word for Windows 1.0 introduced me to WordBASIC. I finally gave in and bought a mouse. My first venture into serious WordBASIC programming was an imperfect Word Perfect to Word for Windows macro converter for my wife, Alix.

The macro converter led to my first book for Que, Using Visual Basic. My theory was that knowing nothing whatsoever about VB was a plus when it came to writing for beginning Windows programmers. I joined the "Rawhide" beta program for the Professional Extensions to VB 1.0 and became acquainted with Michael Risse and Nevet Basker. (The last time I checked, Mike was general manager of .NET Enterprise Services and Nevet was business development manager for ISA Server 2000.)

For better or worse, the VB title got lost in an organizational reshuffle and never appeared in print, but I ended up with a contract from Que to write Using Access for Windows. The Access 1.0 title was successful; between it and its eight subsequent editions, the title has close to a million English copies in print. Access 1.1 Developer's Guide and the 2.0 edition for SAMS, which emphasized Access Basic (originally Embedded Basic) programming, also sold well.

Access's conquest of the Windows desktop database market is in no small part the result of Tod Neilsen's efforts. Charles Cooper called Tod "one of the most likeable suits at Microsoft," and I agree wholeheartedly.

[Update 4/25/2005: The latest (10th) edition of Using Access for Windows is Using Microsoft Office Access 2003 and Tod Neilsen is now president and CEO of Borland.]

Between books, I developed commercial Access apps, including an Access 1.0/1.1/2.0 client/server app for National Semiconductor Corp. that Microsoft's Greg Nelson and National's Margaret Lam demonstrated at Tech*Ed '95. Early versions of the app used a pass-through query DLL and Information Builders' EDA ODBC middleware to connect to DB2 running on a Hitachi mainframe. The 1.0/1.1 versions were a kludge, but they ran like a champ on hundreds of networked desktops and modem-connected laptops.

It wasn't until VB3 arrived with support for the Jet database engine that VB became a viable contender to Access for database front-end development. I climbed on the bandwagon, added VB consulting to OakLeaf Systems' bag of tricks, and began writing "Database Design" columns for Jim Fawcette's Visual Basic Programmer's Journal. (Jim's insistence that VBPJ emphasize code, not abstract technology overviews, is undoubtedly responsible for much of the magazine's success.) My first large VB3 project was a semiconductor cycle-time reporting app that connected via TCP/IP to Sybase running on an IBM AIX box.

Database Developer's Guide with Visual Basic 3.0 was late to market because it took me a few months to convince SAMS management that a VB database book would sell in what was a generally over-published market for general VB titles. It did. VB4's adoption of VBA, the addition of the Remote Data Object (RDO), and the capability to produce 16- and 32-bit OLE 2.1 mini-servers and 32-bit OLE DLLs were interesting features, but none of my clients showed any interest at all in upgrading their VB3 apps or in using VB4 for new projects.

The same proved true for Access 95; Access 2.0 was fast, robust, and ran under Windows 3.1x - which was critical for laptop users. From my standpoint, both upgrades were non-events, but I wrote DDG with VB4 anyway. VB5 and 6 extended the language's reach to enterprise-scale systems, and I've yet to find a data-intensive project that VB6 can't handle with aplomb.

Despite the trend to browser-based front ends, conventional VB forms remain today's best choice for heads-down data entry and, in many cases, decision-support apps. Thus I'm glad—and somewhat surprised—that VB.NET accommodates both WebForms and WinForms. I expect migrating to VB.NET and gaining an XML-centric Weltanschauung to be a traumatic experience for many VB developers (including me). XML is here to stay, but my take is that XML Web services will be ready for prime time when 50% or more of U.S. households have broadband Internet connections. Sending and receiving SOAP messages via HTTPS through an analog modem isn't my idea of fun.

I'm an old dog, so I've seen plenty of new tricks follow the WebVan route to oblivion. (Does anyone remember VB for DOS, a.k.a Escher?) I'm waiting for Microsoft to supply a better definition of Office.NET and, especially, Access.NET before I make a total commitment to the .NET Framework. If Microsoft is willing to bet its Office revenue stream on .NET, I'll join the parade. In the meantime, VB6, MSXML3+, and SQL Server 2000 handle my data-related XML requirements quite nicely, albeit at a more leisurely pace than "legacy" ADO. Happy 10th Anniversary, VB. Here's hoping you're around ten years from now (and still recognizable to the pioneers).

--rj

July 15, 2001 Oakland, California

Roger Jennings, a principal of OakLeaf Systems, is a book author and contributing editor for Visual Studio Magazine (formerly Visual Basic Programmer's Journal). His primary programming interests are client/server and XML/XSLT database applications, and ADSI-database integration. Roger's latest books include Special Edition Using Access 2002 (Que), Special Edition Using Windows 2000 Server (Que), Admin911: Windows 2000 Group Policy (Osborne-McGraw Hill), and Database Developer's Guide with Visual Basic 6 (SAMS). His books for Que and SAMS have sold more than one million copies in English and have been translated into 20+ languages.

---

Technorati Tags: Visual Basic, Visual Basic 6, VB 6, VB6, Wang+BASIC, Access Basic, Embedded Basic, Word Basic, WordBASIC, VBA

Microsoft Belatedly Releases Books Online (April 2006) for SQL Server 2005 SP1

Although the April CTP release of SQL Server 2005 SP1 included an updated SQL Server 2005 Books Online (March 2005 CTP) version, a final SP1 BOL for was missing from the SP1 releases on April 18, 2006. Microsoft posted the missing file on Saturday, April 22, 2006. You can download SQL Server 2005 Books Online (April 2006) from here.

Technorati: Databases SQL Server 2005 SQL Server SQLServer MSSQL SQL Server 2005 Service Pack 1 SQL Server 2005 SP1 SQL Server Express SQL Express SQL Server Express Service Pack 1 SQL Express Service Pack 1 SQL Server Express SP1 SQL Express SP1

Microsoft Releases SQL Server 2005 SP-1

As Microsoft Senior Vice President of Server Applications Paul Flessner promised in his April 6, 2006 SQL Server 2005 Update, SQL Server 2005 SP-1 became available for download from MSDN on April 18, 2006. Following are links to new SQL Server 2005 SP-1 download pages on MSDN: • SQL Server 2005 SP-1 for Developer, Workgroup, Standard and Enterprise Editions running on x86, x64, or ia64 platforms • SQL Express with SP-1 for x86, x64, or EMT64 systems • SQL Server Management Studio Express (SSMSE) for x86, x64, or EMT64 systems • Microsoft Core XML Services (MSXML) 6.0 for SSMSE • SQL Server 2005 Express Edition with Advanced Services (includes SSMSE, MSXML 6.0, Reporting Services (SSRS), and full-text search) • SQL Server 2005 Express Edition Toolkit (includes SSMSE and Business Intelligence Development Studio (BIDS) for Reporting Services projects) • Download SQL Server 2005 Books Online (December 2005) for SQL Server Express • Read the Managing SQL Server Express with SQL Server 2005 Management Studio Express Edition white paper Installer dialogs are very similar to those for SQL Server 2005 SP-1 March CTP, as described in my "SQL Server 2005 SP1 CTP Available for Download" post of March 16, 2006. Technorati: Databases SQL Server 2005 SQL Server SQLServer MSSQL SQL Server 2005 Service Pack 1 SQL Server 2005 SP1 SQL Server Express SQL Express SQL Server Express Service Pack 1 SQL Express Service Pack 1 SQL Server Express SP1 SQL Express SP1

Running the Query Notifications Sample Project

QueryNotifications.sln is a Visual Studio 2005 project that demonstrates Visual Basic 2005 and SQL Server 2005 T-SQL Windows Form code to implement Query Notifications in standard (SqlDependency) or custom (SqlQueryNotification) mode. It also provides an example of programming Database Mail or SMTP messages in response to Query Notifications. This project is one of three Service Broker examples for my "Program SQL Server 2005's Service Broker" article for Visual Studio Magazine's June 2006 issue. Expert One-on-One Visual Basic 2005 Database Programming's Chapter 10, "Upgrading from SQL Server 2000 to 2005" includes an earilier version of the project and brief instructions on how to use the app's features. The project's VSM version installs in C:\ServiceBrokerQueryNotifications and the book's version installs in C:\VB2005DB\Chapter10\QueryNotifications. Note: The downloadable code from the online version of tne VSM article will be available shortly. This post will be updated when the article goes live. Prerequisites: These instruction assume that you're at least familiar with SQL Server 2005's Service Broker feature as the result of reading the VSM article, the Service Broker content of the book's Chapter 10, or SQL Server Books Online's Service Broker topics. Familiarity with Visual Studio 2005's Query Notifications help topics is also helpful.

Following are the requirements to run the QueryNotifications.sln Visual Basci 2005 project:

• Visual Studio 2005 Standard Edition or Visual Basic Express
• SQL Server 2005 Express or higher (SQL Server 2005 Developer Edition or higher is recommended to enable viewing Service Broker database objects with SQL Server Management Studio's Object Explorer)
• SQL Server 2005 Service Pack 1 (SP 1) Community Technical Preview (CTP) or release version is recommended for SQL Server 2005 and SQL Express
• Northwind sample database installed
• Northiwind database compatibility level = 90 and Service Broker enabled (see below)
• A login with sysadmin privileges (preferably the owner of the Northwind database)

Enable Service Broker and Query Notifications for Northwind: Run the following T-SQL script (EnableQueryNotifications.sql in the project folder): USE Northwind; -- Set compatibility level to SQL Server 2005 EXECUTE sp_dbcmptlevel 'Northwind',90; -- Enable Service Broker and set TRUSTWORTHY IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'Northwind' AND is_broker_enabled = 1) BEGIN ALTER DATABASE Northwind SET ENABLE_BROKER WITH ROLLBACK AFTER 5 SECONDS; ALTER DATABASE Northwind SET TRUSTWORTHY ON; END; GO -- Add Database Master Key for Service Broker IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101) CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'23987hxJ#KL95234nl0zBe'; GO -- Settings required for indexed views SET ANSI_NULLS ON; SET ANSI_PADDING ON; SET ANSI_WARNINGS ON; SET CONCAT_NULL_YIELDS_NULL ON; SET QUOTED_IDENTIFIER ON; SET NUMERIC_ROUNDABORT OFF; SET ARITHABORT ON; GO Change the Connection String to suit your SQL Server 2005 instance: The default connection string, stored in My.Settings, is: Data Source=localhost;Initial Catalog=Northwind;Integrated Security=True If you need to change the connection string, open QueryNotifications.sln in Visual Studio, right-click the QueryNotifications project node, choose Properties to open the property pages, click the Settings link, select the Value column of the NorthwindConnection row, and click the builder (...) button to open the Connection Properties dialog. Set the new connection properties, as shown here to substitute an SQL Server Express with the Northwind database installed: Click Test to verify Northwind database connectivity, and click OK to save the new connection string. Execute the project: Open QueryNotifications.sln in Visual Studio and press F5 to complile and run the project. Alternatively, run QueryNotifications.exe from the ...\QueryNotifications\bin\Debug folder. The main form opens with data imported from a local XML data store, as shown here:

Test Service Broker and SqlDependency Operation

Mark the Enable Query Notifications check box and click the QN Subscriptions button to verify that the subscription was created with this message box:

Click No to dismiss the message.

Select the Invalid Query Test option to verify that the client receives and dequeues SqlDependency messages by opening the following message box:

Click OK to dismiss the message and select the All Products Updates option.

Note: The test query has two elements that conflict with the requiremets for indexed view queries: A TOP 10 option and a missing SCHEMA (dbo) prefix for the Products table. If this message box doesn't appear, messages aren't being received by the client. Check Event Viewer's Applicaton log for SQL Server errors, such as insufficient privileges. You can usually solve these problems by detaching and reattaching the Northwind database with your Windows administrative login as the owner.

Verify an SqlDependency Query Notification

With the All Products Updates option selected, choose a product in the DataGridView, change a UnitsInStock, or UnitsOnOrder value, and click Save Changes to open a message box similar to the following:

Click OK to dismiss the message box, and click Refresh Data. If the change you made results in UnitsInStock + UnitsOnOrder <= ReorderLevel, clicking Refresh Data moves the current row to the selected product, and highlights the ProductName and UnitsInStock cells.

Add an SqlNotificationRequest Subscription and Reorder Message

Using the SqlNotificationRequest class requires substituting custom Service Broker QUEUE and SERVICE objects for the corresponding default objects employed by the SqlDependency class. If you don't provide an activation stored procedure and specify the procedure name when creating the QUEUE object, you must poll the target queue to test for messages in the form of XML documents. This example uses polling.

To add an SqlNotificationRequest for the Reorder Required query and specify a automated reorder message to the product's supplier, select the Reorder Required option, mark the Send Reorder Messages check box, and click Add SqlNotification Objects to open the following message box:

A ROUTE object (ProductsQnRoute) is optional for this example.

Note: SQL Express doesn't support Database Mail, so don't mark the Send by Database Mail check box. If you haven't provided valid service URL, user ID, and password values in the SendReordersBySMTP procedure, the default SMTP mail delivery function will fail.

Click OK to dismiss the message and click QN Subscriptions to verify the additional subscription, as shown here:

Click No to preserve the subscriptions, navigate to ProductID 30 (Nord-Ost Matjeshering), change the UnitsOnOrder value from 10 to 0, and click Save Changes. Click OK to dismiss the SqlDependency message, and click Poll Notifications to display the following message:

Click OK to dismiss the preceding mesage, and click Refresh Data to open this message:

If you've configured Database Mail for the SQL Server 2005 instance or SMTP for SQL SErver 2005 or SQL Express, click Yes to send the message to the address you specified as the sender. Here's an example sent to the oakleaf test POP3 server:

Note: Please don't send messages to the above account requesting help with this project. The account is for testing only and all incoming messages are erased periodically.

Otherwise, click No to display this message box with the text of the unsent message:

Troubleshooting

If you encounter problems running the project, check SQL Server Books Online's "Troubleshooting Service Broker" topic. Check Event Viewer's Application Log for SQL Server/SQL Express errors. Search on "Service Broker" and the Error ID or Event ID value. Good sources of troubleshooting assistance are the SQL Service Broker forum and the microsoft.public.sqlserver newsgroups.

Technorati: Databases SQL Server 2005 SQL Server SQLServer MSSQL SQL Server 2005 Service Broker SQL Server 2005 Query Notifications SQL Server Express SQL Express Query Notifications SqlDependency SqlQueryNotification ASP.NET Cache Invalidation

Very Large Databases: Bricks, BitVault and BigTable

Microsoft Watch's Mary Jo Foley mentioned computing/data storage "bricks" in her 4/4/2006 "Microsoft Readies 'BitVault' Self-Healing Data Store" article.

This piece, which was based on a December 2005 Microsoft Research Technical Report (MSR-TR-2005-179), "BitVault: a Highly Reliable Distributed Data Retention Platform," and unnamed "sources close to the company," speculated that Microsoft "has moved BitVault into the product groups, intending to commercialize the technology" and "[t]he Clusters, File Systems and Storage team is now spearheading the BitVault project. ... Microsoft is hoping to be able to field a first-generation product based on BitVault within two years."

Update 4/2/2008: BitVault doesn't appear to have gotten off the ground "within two years" (almost to the day), but "smart bricks" have evolved to ISO intermodal containers as the unit of deployment of servers to data centers according to Mary Jo's Microsoft builds out its first containerized datacenter post of April 2, 2008. [Links to early Fawcette magazine articles and figures no longer are operational.]

The Technical Report, authored by Zheng Zhang, Qiao Lian, Shiding Lin, Wei Chen, Yu Chen and Chao Jin of Microsoft Research Asia, describes a peer-to-peer (P2P) architecture for very large content-addressable databases that store seldom-updated reference data. The BitVault is constructed from one to tens of thousands of "smart brick" building blocks. The paper defines a "smart brick" as a commodity "trimmed down PC with large disk(s)" that enables BitVault to be self-managing, self-organizing, and self-healing.

Jeremy Reimer's Ars Technica post of April 5, 2006, "Microsoft leverages P2P technology to create BitVault," briefly explains how BitVault employs P2P and distributed hashtable (DHT) technology. Structured P2P systems based on DHTs (P2P DHT) are a popular research topic. Click here for DHT links and here for P2P DHT links.

Related Papers from Microsoft Research Asia: "P2P Resource Pool and Its Application to Optimize Wide-Area Application Level Multicasting" (August 2004) describes the combination of P2P with DHT and a self-organzied metadata overlay (SOMO) to create the illusion of a single, large, dynamic resource pool. "SOMO: Self-Organized Metadata Overlay for Resource Management in P2P DHT" (February 2003) illustrates implementing arbitrary data structure in a structured P2P DHT with SOMO for resource management. "XRing: Achieving High-Performance Routing Adaptively in Structured P2P" (MSR-TR-2004-93, Septebmber 2004) and "Z-Ring: Fast Prefix Routing via a Low Maintenance Membership Protocol" discuss optimization of P2P routing for resource pools. "WiDS: an Integrated Toolkit for Distributed System Development" (June 2005) describes the development and test environment for BitVault.

Evolution of Microsoft Proposals for [Smart] Bricks

I first encountered the "brick" concept when covering the Paul Flessner/Pat Helland keynote at Tech*Ed 2002. Here's Pat's description of a service center (SC):

[A service center] is a unit of deployment. This is a thing that I would go and put a collection of Web services and their databases and all of the things it takes to support the Web services on the Internet and put that into the service center so it can be self-managing as much as possible and implement and support the Web services.

Pat went on to describe "bricks" as SC building blocks:

Now, a service center is implemented using bricks. A brick is just a system. It's got lots of cheap memory, lots of cheap disk, cheap and fast CPU, lots of cheap but fast networking. These bricks are going to plug into the service center. So you're going to plug it in and it hooks up and it finds what subnet it's on. It says, "Oh, here I am," and then the service center says, "Oh, you're here. I know what I can do with you. You've got all that storage. I'm going to make some more mirrored copies of my data onto you. I'm going some computation to you." All the human did was go whoomp and shove the darn thing in.

Research for my brief "Microsoft Adopts Medieval Web Services Architecture" article turned up several early references by Microsoft Research's Jim Gray to "bricks" for implementing very large database (VLDB) systems. The first reference to storage bricks that I found was on slide 31, "Everyone scales out. What's the Brick?," in Jim Gray's September 2000 "Building Multi-Petabyte Online Databases" presentation to NASA Goddard. A previous talk at the UC San Diego Supercomputer Center in October 1999 mentioned "CyberBricks" but not in the context of storage bricks.

Gray's "Two Commodity Scaleable Servers:a Billion Transactions per Day and the Terra-Server" white paper and "Scalable Terminology: Farms, Clones, Partitions, and Packs: RACS and RAPS" technical report (December 1999) describe CyberBricks as the "fundamental building block" of large Web sites. His "Storage Bricks Have Arrived" presentation at the 2002 File and Storage Technologies (FAST) conference cemented storage bricks as the implementation of choice for scaleable SQL Server databases.

Note: Jim Gray is well known for his contributions to VLDB research. Many of his 129 citations in the Database (DBLP) Bibliography Server target the design and performance of VLDBs. About half the citations (64 so far) have occurred during his tenure as a Microsoft Research Senior Researcher (from 1995) and Distinguished Engineer (from 2000).

About a year after the Flessner/Helland Tech*Ed 2002 and Gray FAST 2002 presentations, David Campbell, who was then Microsoft's product unit manager for the SQL Server Dabase Engine delivered "Database of the Future: A Preview of Yukon and Other Technical Advancements" as a keynote address for VSLive! San Francisco 2003. My "Build Data Service Centers With Bricks" article for Fawcette Technical Publication's Windows Server System Magazine (May 2003 Tech*Ed issue) summarized Campbell's presentation.

Campbell outlined Microsoft's database architecture du jour as multiple data service centers (SCs) created from groups of autonomous computing cells (ACCs) built from commodity server bricks as shown here:

Campbell envisioned each brick as an independent, replaceable computing unit running an instance of SQL Server 2005 with built-in data storage. Plugging a new brick into an ACC causes the SC to allocate bricks to services dynamically. Management ACCs and SCs communicate with SOAP Web service or Service Broker messages, instead of conventional remote procedure call (RPC) protocols. As noted in my article, Campbell asserted that:

The key to resiliency and scalability is moving reference data, such as product catalogs and customer information, to ACCs that have databases partitioned out to bricks. Product catalog data and customer information is relatively nonvolatile, and the temporary outage of a single brick affects only a percentage (one divided by the total number of bricks) of site visitors. Adding a brick to mirror each partition solves potential outage problems.

The article went on describe how the SC/ACC might improve system resiliency and scalability:

Microsoft's example of a service center is a high-traffic e-commerce site in which relatively static reference data (product and customer information) and shopping-cart state is delivered by partitioned databases in individual autonomous computing cells (ACCs). The Order Processing System is a conventional, shared nothing database cluster, not an ACC. Brown text identifies the elements that David Campbell's original PowerPoint slide didn't include.

Mary Jo Foley also covered the keynote in her 2/13/2003 "Yukon, Ho!" Microsoft Watch article. According to Mary Jo, Campbell said "Microsoft is building Yukon around these [SC, ACC, and brick] concepts."

A later Microsoft Research Technical Report MSR-TR-2004-107 by Jim Gray, et al. (October 2004), "TerraServer Bricks – A High Availability Cluster Alternative," describes the replacement of an active/passive cluster connected to an 18 terabyte Storage Area Network (SAN) with a duplexed set of "white-box" PCs containing arrays of large, low-cost, Serial ATA (SATA) disks. Gray calls the replacement system "TerraServer Bricks." Web Bricks have two 2.4-GHz Xeon hyperthreaded CPUs, 2GB RAM and two 80-GB SATA drives, and have a hardware cost of $2,100. Storage Bricks, which have a hardware cost of $10,300 and run SQL Server 2000, have the same CPUs, 4 GB RAM and 16 250-GB SATA drives.

Note: Microsoft Research's "Empirical Measurements of Disk Failure Rate and Error Rates" (MSR-TR-2005-166, December 2005) study indicates that SATA "uncorrectable read errors are not yet a dominant system-fault source—they happen but are rare compared with other problems. "

VLDB Implementation/Deployment Issues

Dare Obasanjo's "Greg Linden on SQL Databases and Internet-Scale Applications" post quotes Greg Linden:

What I want is a robust, high performance virtual relational database that runs transparently over a cluster, nodes dropping in an out of service at will, read-write replication and data migration all done automatically. I want to be able to install a database on a server cloud and use it like it was all running on one machine.

Adam Bosworth, who managed the intial development of Microsoft Access, went on to found CrossGain (together with Tod Neilsen, Access marketing honcho), sold CrossGain to BEA and became BEA's chief architect and senior vice president, and now is VP Engineering at Google, lists these three features that database users want but database vendors don't suppply: Dynamic schema, dynamic partitioning of data across large dynamic numbers of machines, and modern [Googlesque] indexing. Adam wants the the Open Source community to "[g]ive us systems that scale linearly, are flexible and dynamically reconfigurable and load balanced and easy to use." Adam does mean give, not sell.

It seems to me that BitVault's smart bricks with appropriate deployment and management applications would fulfill Greg's and all but Adam's economic desires for reference data, which now constitutes more than half of the data stored by North American firms.

It's been more than six years since Jim Gray proposed CyberBricks and storage bricks for VLDBs but to the best of my knowledge, only Microsoft Resarch appears to be scaling out production databases (TerraServer) with bricks today. Microsoft still hasn't solved one of the major economic issues of storage bricks: licensing costs of Windows Server 2003 SP-1 R2 for each CPU compared with a no-cost Unix or Linux distribution. If Microsoft intends to productize BitVault successfully, a stripped-down, it seems to me that a lower-cost version of Windows Storage Server 2003 R2 is needed.

Grid data storage is a competing architecture that's gaining popularity in Europe but is just getting off the ground in North America. The EU DataGrid project's "objective is to build the next generation computing infrastructure providing intensive computation and analysis of shared large-scale databases, from hundreds of TeraBytes to PetaBytes, across widely distributed scientific communities." The EU DataGrid project was merged into the EU EGEE (Enabling Grids for E-sciencE) in 2004. EGEE is now in its second development phase. Oracle and IBM both promote grid architecture for their databases.

BitVault's P2P architecture seems to me to be a viable challenger to grid data storage for read-mostly (archival) databases. I haven't seen an official grid-computing or grid-storage story from Microsoft.

Note: The Microsoft Research eScience Workshop 2005 (October 2005) presented sessions by "early adopters using Microsoft Windows, Microsoft .NET, Microsoft SQL Server, and Web services" for scientific computing. Many of the sessions dealt with .NET and grid computing/data storage.

Really Big Smart Bricks and Databases

The ultimate brick is a Google data center in a shipping container, as postulated by PBS's Robert X. Cringely (Mark Stephens) in his 11/17/2005 column, "Google-Mart: Sam Walton Taught Google More About How to Dominate the Internet Than Microsoft Ever Did." According to Stephens, whose bona fides were questioned several years ago:

We're talking about 5,000 Opteron processors and 3.5 petabytes of disk storage that can be dropped-off overnight by a tractor trailer rig [...] at Internet peering points. ... With the advent of widespread GoogleBase (again a bit-schlepping app that can be used in a thousand ways -- most of them not even envisioned by Google) there's suddenly a new kind of marketplace for data with everything a transaction in the most literal sense as Google takes over the role of trusted third-party info-escrow agent for all world business.

As Cringely mentioned in his subsequent (11/24/2005) column, The Internet Archive proposed a similar—but considerably smaller—implementation called the "petabox" which contained 800 low-cost PCs running Linux and 1 PB of data storage in a configuration that could be deployed in an 8- by 8- by 20-foot shipping container. Capricorn Technologies' commercial TB-80 PetaBox implemerntation stores 80 TB in a 19-inch rack, so 12 TB-80s combine to create an actual petabox. In mid-2004, the Internet Archive had a 80-TB rack running in San Francisco and a 100-TB rack operational in Amsterdam.

Clearly, large distributed file systems and databases must run on very big smart bricks or hardware of similar scale. Google probably probably runs the world's largest distributed file system. Google's "The Google File System" paper by Sanjay Ghemawat, Howard Gobioff, and Shun-Tak Leung (October 2003) describes the then-largest GFS cluster as having more than 1,000 nodes and 300+ TB of storage. A GFS cluster has a single master and multiple chunkservers, and runs on commodity Linux boxes. A 64-bit GUID identifies each fixed-size (64-MB) file chunk. According to the paper, "For reliability, each chunk is replicated on multiple chunkservers. By default, we store three replicas, though users can designate different replication levels for different regions of the file namespace." The master contains system metadata and manages system-wide operations.

According to an abstract from a October 2005 presentation by Google's Jeff Dean at the University of Washington, "BigTable is a system for storing and managing very large amounts of structured data. The system is designed to manage several petabytes of data distributed across thousands of machines, with very high update and read request rates coming from thousands of simultaneous clients." A brief summary of Jeff's talk by Andrew Hitchcock is available here. (The Google Operating System Site is a good source of information on the latest Google Web apps.)

BigTable runs under the GFS. It's not clear if Google Base is a BigTable app but, in my opinion, the timing of Jeff's presentation and the start of the Google Base beta program is too close to be coincidental.

Don't Expect BitVault to RTM in the Near Future

On April 6, 2006, Paul Flessner presented a broad-brush overview of SQL Server's future to an audience of database users and computer press reporters in San Francisco. The primary item of (limited) interest was the rechirstening of SQL Server Mobile Edition as SQL Server Everywhere Edition, which Microsoft intends to RTM by the end of 2006. Mary Jo Foley reported in her "Microsoft Outlines (Vaguely) Its Database Road Ahead" article of the same date that Flessner "did acknowledge that Microsoft is looking at how to support 'content-addressable storage,' and that it had a project named BitVault that falls into this category. But he declined to say more."

Let's hope that BitVault and smart bricks get more attention from Microsoft executives and program/project management than first-generation data bricks received.

Technorati: Databases Content-Addressable Databases BitVault Distributed Hash Table DHT Bricks Smart Bricks Google File System GFS BigTable Google BigTable Microsoft Research Microsoft Research Asia SQL Server 2005

Other links in process (primarily related to autonomous and autonomic computing):

Gartner: "Microsoft's DSI Strategy: Clearer Vision, but End State Is Years Away From Fulfillment"

Pat Helland's "Autonomous Computing" presentation at the University of Washington

"Hurry up and wait" by Tom Yeager "IBM Manifesto on Autonomic Computing"

Microsoft Readies Autonomic Computing Plan" by Mary Jo Foley: Press Release: "Q&A: Microsoft to Unveil Vision for End-to-End Enterprise Management Solutions"

Microsoft white paper: "Automated Deployment Services Technical Overview"