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.

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.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

technology leadership

Why the World Needs More Technology Leadership

As a fact, technology has touched every single aspect of our lives. And there are some technology giants in today’s world which have been frequently opined to have a strong influence on recent overall technological influence. Moreover, those tech giants have popular technology leaders leading the companies toward achieving greatness.

iOS app development

The Future of iOS App Development: Trends to Watch

When it launched in 2008, the Apple App Store only had 500 apps available. By the first quarter of 2022, the store had about 2.18 million iOS-exclusive apps. Average monthly app releases for the platform reached 34,000 in the first half of 2022, indicating rapid growth in iOS app development.

microsoft careers

Top Careers at Microsoft

Microsoft has gained its position as one of the top companies in the world, and Microsoft careers are flourishing. This multinational company is efficiently developing popular software and computers with other consumer electronics. It is a dream come true for so many people to acquire a high paid, high-prestige job