skip to main | skip to sidebar
Showing posts with label SQL Express. Show all posts
Showing posts with label SQL Express. Show all posts

Friday, June 27, 2008

Suggestions for Scott Hanselman’s Updated Northwind Sample Database

Scott’s not a fan of Northwind, but when he suggested a completely new replacement for Northwind in his Community Call to Action: NOT Northwind post of May 29, 2008, many developers, including me, pushed back.

I left the following comment to Scott’s NotNorthwind - Update #1 - All Your Northwind Are Belong To Us post on June 27, 2008:

Scott,

As a long-time and widespread Northwind user, I'd prefer Southwind (the maker of aircraft cabin heaters that have kept me warm on many cold nights at 9,500 ft.)

I use Northwind rather than AdventureWorks because AW is overly normalized and the added joins confuse readers by diverting their attention to database structure from the issue at hand.

I'd also suggest some additions:

  1. Add an item number (tinyint) and shipped (bit) field for backorders below.
  2. Invoices from shipped order with backorders. An sproc to issue an invoice with and without backordered items would be nice.
  3. A relation table for a one:many relationship between sales orders and invoices to support the above.
  4. Inventory transactions table to provide on-demand calculated data for related field in Products table
  5. Change Products table to many:many relationship with suppliers and add multiple suppliers for some products
  6. A Purchase orders table with line items as in #1 with received field working with inventory table.
  7. Sproc to automatically add or remove timestamp field for each table
  8. Sproc to automatically add or remove usual audit fields for each table
  9. Sproc to automatically add or remove Sync framework fields for each table
  10. Sproc to substitute surrogate for natural PKs or vice-versa
  11. Product reviews (by customers, suggested by someone else) would be great

Deletions: Remove CustomerDemographics, Territories, Region
Thanks for considering the above,

--rj

If you have any suggestions for improving Northwind, add a comment to Scott’s Update#1 or my blog. I’ll send posts here to Scott.

Tuesday, February 27, 2007

New LINQ to SQL Video from the LINQ Farm

Charlie Calvert has added a 7.5-minute video for LINQ to SQL beginners to the LINQ Farm. The video covers all the essential steps in creating a C# LINQ to SQL console application from the Northwind sample database's Customers and Orders tables using the DLinq Designer.

The video complements Charlie's earlier LINQ Farm: Connecting to a Database with LINQ to SQL post. My LINQ to SQL C# Primer post has links to Charlie's previous LINQ Farm posts and my additions to his Connecting to Northwind article.

The popularity of console apps to demo LINQ to SQL features is undoubtedly due to the KISS (Keep It Simple, Stupid) mandate, but adding a BindingNavigator and two BindingSource and DataGridView pairs to the project involves only a few mouse clicks. It's a good bet that most data-intensive Visual Studio projects that are candidates for LINQ to SQL use at least a few bound controls.

As an example, here's the UI for an early VB .NET LINQ to SQL (then DLinq) demo project with Customers, Orders, and Order Details grids that lets you update or delete rows and then apply the DataContext.SubmitChanges method to update the underlying tables. (Code is required to insert rows.)

(Click here for full-size image.)

In my books and articles, I generally take the time to provide a WinForm with bound DataGridView, text boxes, or code to prove that the LINQ objects I'm demonstrating support CRUD operations. It might take readers a few minutes more to complete the exercise but they also learn the capabilities (and limitations) of the collections they create.

LINQ Farm: One to Many and Joins with LINQ to SQL and Orcas March CTP

Update 2/28/2007: This primer on relationships and joins in the Northwind sample database is intended for relational database and SQL newbies. It just appeared on 2/28/2007 and must have been hibernating since its 2/20/2007 pub date. The source code for Charlie's last three LINQ to SQL posts requires the Orcas February 2007 CTP, "which should be out very soon."

Microsoft released on the morning of February 28, 2007 the Microsoft Pre-release Software Visual Studio Code Name "Orcas" - March Community Technology Preview (CTP). The "Orcas March CTP Available from MSDN" post has details for downloading a self-extracting install or VirtualPC image (VHD).

Saturday, February 17, 2007

LINQ to SQL C# Primer

The seventh in Charlie Calvert's LINQ Farm series, "Connecting to a Database with LINQ to SQL ," shows beginning LINQ programmers how to write a simple C# console application that uses LINQ to SQL for connecting to and querying the Northwind Customers table.

Charlie also posted a "Connecting to Northwind " article to aid users new to database applications in downloading and installing the Northwind sample database in an SQL Server or SQL Server Express instance. My "Tips for Using Northwind with Visual Studio Express " extends Charlie's article with a few suggested additions and modifications, especially for Vista users.

Following are links to all seven members of Charlie's LINQ Farm series:

  1. LINQ for Beginners
  2. Query Expressions
  3. Query Operators
  4. Using Distinct and Avoiding Lambdas
  5. Focus on Grouping
  6. LINQ Sets : Union, Intersect and Except
  7. Connecting to a Database with LINQ to SQL

I first covered LINQ Farm in an "Orcas December 2006 CTP Coming with New LINQ/EDM Bits " post of November 25, 2006. However, my "Orcas February 2007 CTP Delayed to March " post notes that "we LINQ and ADO.NET 3.0 early adopters need a new Il Duce to make the Orcas trains run on time ."

Wednesday, November 15, 2006

Microsoft Offers SQL Server 2005 SP2 CTP

Microsoft announced on November 7, 2006 the availability of a Communtity Technical Preview (CTP) of the forthcoming SQL Server 2005 Service Pack 2 for all editions except Express, and special SP2 CTPs for the Express edition (SQLX) and Express with Advanced Features. SP2 will be required to run any version of SQL Server under Windows Vista and "Longhorn" Server. The "What's New in SQL Server 2005 SP2" page summarizes upgrades to:

  • Analysis Services
  • Database Engine
  • Integration Services
  • Reporting Services and
  • SharedTools
for editions that incorporate the features. Download the appropriate SP2 CTP bits from the SQL Server Community Technology Preview Program page. Microsoft senior vice president of Data and Storage Platforms Paul Flessner expanded on the preceding list during his keynote speech at the 2006 Professional Association for SQL Server (PASS) Community Summit in Seattle on November 15, 2006. Added features include:
  • Data compression
  • Increased business intelligence functionality
  • Security updates relating to Common Criteria
  • Manageability enhancements
  • Support for Windows Vista and
  • Optimization for use in the 2007 Microsoft Office system environment
Microsoft is pursuing Common Criteria security certification for SQL Server 2005 Enterprise Edition SP1 and SP2. Many governments require Common Criteria certification and it's a preference or requirement for many industries. In the meantime, Microsoft has made available Enterprise Strategy Group (ESG) reports on SQL Server 2005 security—Microsoft SQL Server Runs the Security Table and The Security Development Lifecycle (SDL). Advantage, Microsoft. According to ESG, "Microsoft SQL Server 2005 is particularly worth examining as it is the first major product release that Microsoft has put through the SDL." Flessner also announced that Visual Studio Team Edition for Database Professionals (formerly code-named "DataDude") is set to RTM on November 30, 2006.

Updated: 11/16/2006 with additional security data.

Technorati tags: , , , , , , ,

Sunday, July 23, 2006

Red vs. Blue JET Database API Confusion

It's undoubtedly safe to say that the majority of Microsoft Exchange administrators, developers, ISVs, and possibly even users are disappointed that the forthcoming Exchange Server 2007 didn't migrate its message store from the Blue JET Extensible Storage Engine (ESE) to SQL Server 2005. After all, SQL Server 2005 stole the show from Microsoft's rather dismal fourth quarter earnings report as the "Server and Tools [segment] delivered 18% revenue growth for the quarter, fueled by an increase of over 35% revenue growth for SQL Server."

One of the motivators for Exchange's adoption of SQL Server for its message store is the belief of many IT executives and even Exchange administrators that the ESE uses the same technology as Access's Jet database engine. For example, Peter Galli's July 20, 2006 eWeek article, "Exchange Data Store Change Still in the Cards," starts with a lead that refers to generic Jet:

While Exchange 2007, the upcoming e-mail, calendaring and messaging server from Microsoft, is still based on the Extensible Storage Engine, a derivative of the Jet database store, the company says it remains committed to unifying this with the SQL Server database store going forward.

They just don't know exactly when yet.

Officials such as Terry Myerson, the general manager of the Exchange Server product group, argue that there is ultimately more value for customers by staying on the Jet engine in Exchange 2007, the second, public beta of which is expected to ship as early as late July, with the final product likely in late 2006 or early 2007.

Wikipedia's entry for "Microsoft Jet Database Engine" merges the Red and Blue JET flavors (Purple JET?) into a single, generic Jet database:

JET stands for Joint Engine Technology, sometimes being referred to as Microsoft JET Engine or simply Jet. Microsoft Access, Microsoft Exchange Server and Visual Basic use or have used Jet as their underlying database engine. It has since been superseded, however, first by Microsoft Desktop Engine (MSDE), then later by SQL Server 2005 Express Edition and no longer exists as a component of Microsoft Data Access Components (MDAC).

Note: The Wikipedia entry correctly assigns Jet 4.0 as the version used by Access 2000, 2002, and 2003, but incorrectly says that Exchange 5.5 used it also. No wonder folks are confused. Remember Microsoft's convulsive architectural and product name-game of the early 21st century: Changes from Windows Distributed interNet Architecture (Windows DNA) and Digital Nervous System to the Next Generation Windows Services (NGWS), .NET Products and Services, Windows Server .NET, Visual Studio .NET, Visual Basic .NET and My Services .NET (Hailstorm), and finally to WhateverSKU 200x? The confusion between Red, Blue, and generic JET/Jet pales in comparison. JET Detectives It's not easy to find an authoritative history of Microsoft's Joint Engine Technology (JET) API. During the late 1990's, I was told by a Microsoft representative that JET (then being downsized to "Jet") was not an abbreviation or acryonym for anything. The "Notes" section of Microsoft's Extensible Storage Engine Reference , which is likely to be as close to authoritative as one can get, states:
ESE was formerly known as Joint Engine Technology (JET) Blue, and so frequently the term "JET Blue" or "JET" is used interchangeably with the term ESE outside this documentation. However, there are in fact two completely separate implementations of the JET API, called JET Blue and JET Red. The term "JET" is frequently also used to refer to JET Red, which is the database engine that is used with Microsoft Office Access. The two JET implementations are completely different, are separately maintained, have a vastly different feature set, and are not interchangeable. Within the ESE documentation, "JET" refers to the ESE or the JET API as ESE implements it. Any references to the JET Red will always explicitly be labeled "JET Red".
The ESE folks apparently didn't have the clout to establish "JET Red" as the brand for garden-variety Jet, the oft-maligned and oldest of Microsoft's freely-distributable datbase triumerverate—Jet 4.0, SQL Server 2005 Express Edition (SSX), and the forthcoming SQL Server 2005 Mobile Edition (SSM). It's clear that generic Jet is Red JET. Or is it JET Red? Confusion about JET API colors has abounded for years. Mary Jo Foley, who's well-known as a technical straight-shooter, got the Jet versions right and then wrong in this excerpt from her June 15, 2005 "Microsoft Shares More Clues About Longhorn Server" Windows Watcher article:
When asked by a participant whether Microsoft was planning to revisit Active Directory "resilience" by reworking Active Directory and other "server-intrinsic databases in Longhorn" so they don't require the Microsoft Jet database engine, Muglia noted that there are two versions of Jet: "Jet Red," which is what Microsoft Access uses, and "Jet Blue," which is "used widely within the Windows Server, including in Active Directory and Exchange Server. "The Jet Blue used by Access is transactional and in general, has had good robustness characteristics," Muglia told participants. "We will always look at where we should go with this and someday, we'll host it on SQL (Server). But that is a ways away — certainly not Longhorn."
It's not clear if Bob Muglia misspoke or Mary Jo simply transcribed his response incorrectly. Regardless, the last paragraph directly contradicts the first. Access 2007 Migrates to a New Red JET Erik Rucker's October 13, 2005 "Access 12's new data engine " post in MSDN's A discussion of what's new in Access 2007 (formerly "Access 12") blog made the first public disclosure that Microsoft Office Access 2007 (then 12) would use a special, Access-only version of Jet 4.0's .mdb file structure and database engine. The primary change to the new .accdb format was to accommodate SharePoint with Attachment and Multi-Valued Field data types. However, .accdb files don't support user-level security (with workgroup files), replication, and a few data import/export features of earlier Access versions. In a January 4, 2006 "JET History" comment to Erik's post, Microsoft's Ian Jose (ianjo) aimed to reduce readers' confusion about which JET API version was in play by providing more detail of the divergence of Red and Blue JET:

Everywhere this article refers to Jet, it is refering to JET Red. JET Red was an ISAM originally developed by Microsoft for BC7, compiled basic. JET Blue was originally developed by Microsoft as a prospective upgrade for Access, but it was never used in this role. JET Red and JET Blue began sharing the common JET moniker in the Spring of 1990 when a query technology, QJET, was developed that would host on both Red and Blue.

As others have noted, JET Blue went on to be used by Microsoft Exchange, Active Directory and many many other Windows services. JET Blue was a private API for many years, but became a published API in April of 2005 when three middle ware applications not covered by the Windows EULA chose it as their data store. Now anyone can use JET Blue.

JET Red is a file sharing techonlogy with page level locking and best effort crash recovery. JET Blue on the other hand is designed to be embedded in a server application, does not share files, has write ahead logging for guaranteed crash recovery and has record level locking. JET Blue does not ship with a query engine but instead relies on applications to write their own queries as C++ ISAM code.

Note: Red JET has had record-level locking since Access 2000's Jet 4.0 but is prone to crashes and file corruption.

In the early 1990s while busy writing Access user and developer books, I learned a bit more about Blue JET and then lost interest in the hierarchical version as Access 1.1+ and Visual Basic 3.0+ began to dominate the desktop relational database management system (RDBMS) space and book market.

Jet is Dead or Just Moribund?

Cries of "Jet is dead" accompanied the release of Access 2000, MSDE 1.0, and Access Data Projects, which abandoned the .mdb file format entirely. Access book sales fell off precipitously; migration from Access 97 to 2000 was at a trickle to be generous. Microsoft's Bill Demos wrote in a June 1999 whitepaper, "Microsoft SQL Server: Microsoft Access 2000 Data Engine Options":

MSDE is the new data engine for Microsoft and is our strategic direction. MSDE is completely compatible with the SQL Server version 7.0 code base, enabling customers to write one application that scales from a PC running the Windows 95 operating system to multiprocessor clusters running Windows NT Server, Enterprise Edition.

Obviously the rumors of Red JET's impending death in 1999 and 2000 were "greatly exaggerated." In fact, today the Access team is attempting to convince developers not only to migrate data storage from strategic SQL Server to Windows SharePoint Services (WSS) 3.0 as a data store or use non-strategic Access .accdb databases derived from Red JET. Plus (gasp!) they recommend moving from ActiveX Data Objects (ADO) and OLE DB to (double-gasp!) retro Data Access Objects (DAO) to enable programmatic access to the new data types and other Access 2007 enhancements. Not to be outdone by the storage engine group, Access 2007's programmability team is pushing formerly abandoned macrostoreplace VBA. Microsoft put Access macros out to pasture when they released Access 95 with "real" VBA instead of Access (or Embedded) Basic. The idea is that macros will run in secure environments where unsigned VBA code won't. I bet almost all Office 2007 users will end up with entire drives designated as trusted locations (a.k.a "safe for scripting.") Maybe I'm getting too old for this foolishness or—as my Montenegran friend Drago Vely would call it—crazinesses. It looks to me as if the next version of Visual Studio (codenamed "Orcas") alone will introduce many more cool data-related features than all Microsoft Office 2007 members together. Technorati tags: , , , , , , , , , , , , , , , ,

Subscribe to: Comments (Atom)
 

AltStyle によって変換されたページ (->オリジナル) /