Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jul 2, 1998

SQL 7 Preview from TechEd '98 - Page 2

By Sharon Dooley

The 7.0 Enterprise Manager is a "snap-in" for the Microsoft Management Console. You can also manage SQL Server 6.5 servers from the MMC but not with the same tool. They are simply building the 6.5 Enterprise Manager into the console.

There are many new wizards including a much more powerful maintenance plan wizard and import and export wizards for Data Transformation Services.

Web-based administration will be supported, initially for monitoring through HTML pages shared with the MMC.

DMO is substantially changed. All administration functions are supported including DML and DDL. There is an integration interface for ISV's. DMO can be hosted in COM products (VC ++, VB, Visual J++, MTS, Active Server pages) and Automation products (thru Idispatch) such as Excel, VB Script, JavaScript, etc.

Here is the new object model:

Existing DMO applications will not work against the 7.0 DMO. However, Microsoft plans to have an upgrader for 6.5 DMO. Don't ask me how or what.


Backup and Recovery

The way databases are backed up when users are on line has been changed. In 6.5, the backup is of a consistent set of pages. Transactions are delayed until affected pages are written. This affects transaction throughput. The disk is accesses in non-optimal order because of this. The backup process copies data between buffers.

The 7.0 backup uses a "fuzzy backup" and includes the transaction log to make it consistent. There is minimal impact on running transactions (figures given were that backup with live users on system had about a 10% impact on users and 10-20% impact on the backup). There is no out-of sequence list. The pages being backed up are not cached so it is fast.

SQL Server 7.0 will have an incremental backup – everything changed since the last full backup. This is used mainly to reduce recovery time. You would restore the last full, the last incremental and the log backup instead of several log backups. It may also reduce the time to backup.

Files and file groups can be backed up individually. This is to support VLDB's with tight backup window. You will need to still consider Referential Integrity, etc., if you are backing up files and file groups. You must back up the transaction log separately since it is in a file of its own and won't be automatically done as part of file group backup.

You can restore files and file groups as well. This is useful for recovery from isolated media failure. There are multiple restore plans possible. For example, suppose you have a full backup on Sunday, an incremental on Tuesday and Thursday and log backups for Tues, Weds and Thurs. If you crash later on Thursday you could

restore from Sunday's full, Thursday's incremental and Thursday's log

restore from Sundays full, Tuesday's incremental and Tues – Thursday's logs.

This helps with the "My tape broke" problem.

There is an assisted restore that builds a restore plan based on available backups: either a full database restore or only damaged files. The assisted restore allows a choice of alternate plans. It performs the restore automatically.

Additional features of backup and restore:

Database is created automatically on restore

Files can be relocated

Backup supports Microsoft Tape Format (MTF)

Allows sharing of media among backup packages such as NT Backup

You can resume interrupted operation (backup and restore) near the point of failure

Verify a backup set without actually restoring it.

There is no table-level backup and restore in 7.0.

As to performance, they say that the backup/restore runs at media speed. There is minimal impact on the server (90%+ transaction throughput while maintaining 80+ backup speed). Scales up well as backup devices are added. Fast rollforward is 2-10 times (typically 4 times) faster than 6.5. Third party backup products can achieve the same performance.

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM