SQL 7 from a Database Administrator’s Perspective

SQL 7 from a Database Administrator’s Perspective

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.

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.

Share the Post:
Heading photo, Metadata.

What is Metadata?

What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular

XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several