Microsoft Enhances SQL Server 2000 Core Storage Engine
REDMOND, Wash -- Late last month, Microsoft Corp. said at the SQL Server 2000 Reviewer’s Workshop here that when its forthcoming database is released SQL Server 2000's core storage engine will be enhanced.
When Microsoft (www.microsoft.com) built SQL Server 7.0 -- its current SQL Server product and precursor to SQL Server 2000 -- the company didn’t talk about how much of the code it reworked because it didn’t want to scare customers away, says Peter Spiro, product unit manager of the SQL Server development team at Microsoft.
"Now that we’ve pulled it off and it works we can brag a little about reworking 75 percent of the storage engine," he says.
In the process of developing SQL Server 7.0 and overhauling the storage engine, Microsoft built architectural hooks for features to be added over the next several releases. Spiro says Microsoft treated SQL Server 7.0 as a massive release, and now SQL Server 2000, code-named Shiloh, is the first extension of the hooks, in which Microsoft is cleaning the code and updating some of the features. "This release is not about altering big things," he says. "But there are a number of delighters that we added."
In Shiloh, Microsoft is focusing on ease of use, particularly in the areas of recovery and backup and restore.
There are three recovery modes: simple, bulk logged, and normal. Simple recovery mode is equivalent to truncate log, so there is no media recovery. Nor is this mode a form a backup. Instead, there is enough of the transaction log kept online to ensure restart recovery in the event of a system failure.
In bulk logged recovery mode a set of extents touched by all bulk operations is recorded, and the transaction log backup includes the contents of all extents involved in the bulk logged operation. This avoids staging bulk data in the online log, which grows the online log tremendously.
Everything is fully loaded in normal recovery mode. This mode provides the most protection because the database is recoverable to any point in time. Spiro says Microsoft streamlined the process by enabling bulk operations to be logged as entire pages rather than one row at a time.
On the backup and restore side, Microsoft added fast differential backup, fast fail back from standby to primary, and snapshot backup and recovery.
With fast differential backup, each extent modified since the previous full backup is marked in a bitmap. Differential backup then consults the bitmap and backs up changes. The fast failback feature prevents a database from requiring a restore if the data and log files are not damaged, since the log files taken on secondary backup can be applied back to a primary backup. Spiro suggested customers would use this feature in planned failovers. The snapshot backup and restore functionality is equivalent to a full database of file or filegroup backups.
Moving forward, Microsoft aims to have no static configuration perimeters in the core storage engine, and to continually work on the code base so that future changes can be made rapidly. "The essential idea is to keep it simple but not simplistic," Spiro says.