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.
