Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

SQL 7 from a Database Administrator's Perspective

The changes that Microsoft has made to SQL Server version 7 are great enough that how you will function as a database administrator could change significantly. Here are some areas that DBAs might want to investigate.


advertisement
he changes that Microsoft has made to SQL Server version 7 are great enough that how you will function as a database administrator could change significantly. Here are some areas that DBAs might want to investigate.

Creating a Database
One of the most basic functions of a DBA is the responsibility to create the databases. In version 6.5, this required two steps. First, one created a device. The database was then created by allocating space from the device. The concept of a device was an unnecessary layer between the file system and the database. Version 7 has done away with devices. One now creates the database in a one-step process that specifies the files for the database in the CREATE DATABASE statement.

Changing from devices to files has enabled Microsoft to add features, including allowing the database to expand automatically and moving a database easily from one machine to another.



Meta Data
A DBA must be able to get around the internal tables that SQL Server uses to store information about itself. There is always something that can't be done through the GUI or that must be scripted.

The system tables have changed to support the architectural differences between the versions, although some of the previous tables are still supported as views. In addition, in compliance with ANSI SQL, a set of virtual tables has been added under an owner titled INFORMATION_SCHEMA.

However, under version 7, it was difficult to obtain a list of all database files associated with the server. The SYSFILES table is local to each database.

Backing Up Your Database
Microsoft has added two options to back up up your database. Instead of backing up your transaction logs regularly, you can create a differential backup. This backup represents all the changes between the last full backup and now.

For very large databases, you can also choose to back up up just part of the database each time by backing up one of the files the database uses. However, you still need transaction dumps in this case. As a DBA, you will have to decide what makes the most sense for your system.

Security
Microsoft has changed the security aspects of SQL Server, and Standard Security mode no longer exists. You can choose only between integrated or mixed.

In version 6.5, managing security based on NT groups was awkward. For example, in NT, a user can be part of more than one group, while in 6.5, a user was restricted to one—a setup that frequently created conflict. However, under version 7, managing users based on their NT groups is easy. Version 7 even supports the "deny" command that already exists in the NT security model.

In fact, groups have disappeared in version 7, and a much more flexible model of "roles" has replaced them. Users of Oracle will be already familiar with this concept, in which a user can be a member of more than one role.

I make no claims to being familiar with all aspects of the new version. It's quite possible that equally significant changes to SQL Server will cause dramatic changes to the daily routine of a DBA that I haven't covered. I welcome any comments and suggestions.



   
Joe Lax has spent more than 10 years working in various database environments and has been a practicing DBA on all versions of SQL Server from version 4.2 onward. He also is a MCSE and an MCT who recently has started to learn Oracle, which affords him no end of fun. He loves feedback and can be reached here.
Comment and Contribute

 

 

 

 

 


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

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date