Welcome to Certifiable, your exam prep headquarters. Here you'll find questions about some of the tricky areas that are fair game for the certification exams. Following the questions, you'll find the correct answers and explanatory text. We change the questions weekly.

Questions (March 2, 2001)
Answers (March 2, 2001)

As I read through last week's column, I realized that we don't list my certifications on either the Training & Certification Channel or in the Training & Certification UPDATE newsletter. For the record, I have the MCSD, MCSE+I, Microsoft Certified Trainer (MCT), Microsoft Certified Database Administrator (MCDBA), and Certified Technical Trainer (CTT) certifications. I mention my qualifications because I'm sure some of you have begun to wonder whether I'll ever cover anything other than the MCSE core exams.

As you can tell from the string of letters, I'm familiar with a wide range of exams. Starting with last week's column, I intend to spend time on each of the Microsoft certification topics with which I have experience. My transcript has 31 exams on it, and I plan to take at least six more exams this year, so we should have enough non-MCSE core material to last for quite a while.

If your primary interest is the MCSE core materials, don't be alarmed. Jon Bischke (2000Tutor.com, 2000Exams.com, MCSETutor.com, and MSCE Live!) will soon begin alternating with me as this column's author, and he'll cover the MCSE core topics!

This week's column is the second to cover topics relating to Exam 70-028, Administering Microsoft SQL Server 7.0.

Questions (March 2, 2001)

Question 1
A new payroll database has 65 tables with the following characteristics:

  • 25 tables contain the primary keys that foreign keys in the rest of the tables use.
  • 10 tables contain the master records for items such as payroll and employee information.
  • 30 tables contain the detail records that correspond to the master records (e.g., individual items for payroll deductions, employee work history).

The first group will grow by approximately 5 percent each quarter; the second group will grow by approximately 2000 records each week; and the last group will grow by approximately 10,000 records each week. Assume that the tables in group 2 have record sizes that average 200 bytes and the tables in group 3 have record sizes that average 100 bytes.

In addition, the database will contain digital images of each payroll check after it has cleared the bank. These images will be inserted into the database each month as part of the bank account reconciliation process.

When you implement the database's physical design, you must meet the following criteria:

  • Physical I/O for insertions and updates must be optimal.
  • You must be able to back up volatile data independently of rarely changing data.
  • Image data won't be used more than twice a year during semiannual audits; therefore, you can archive it until needed.
  • Physical I/O for index read operations should be optimal.

You use the following statement to create the database:

CREATE DATABASE Sales
ON PRIMARY ( NAME = PPri1_dat,  FILENAME = 'c:\mssql7\data\PPri1dat.mdf',
  SIZE = 10,  FILEGROWTH = 5% ),
      FILEGROUP PayrollGroup1 ( NAME = PGrp1Fi1_dat,  FILENAME =
'd:\mssql7\data\PG1Fi1dt.ndf',  SIZE = 25,
                                                   FILEGROWTH = 1),
      FILEGROUP PayrollGroup2 ( NAME = PGrp2Fi1_dat,  FILENAME =
 'e:\mssql7\data\PG2Fi1dt.ndf',  SIZE = 55,
                                                    FILEGROWTH = 5 ),
      LOG ON ( NAME = 'Payroll_log',  FILENAME = 'f:\mssql7\data\payrlog.ldf',
 SIZE = 5MB, FILEGROWTH = 5MB )

You also create group 1 tables in the Primary file group, group 2 tables in the PayrollGroup1 file group, and group 3 tables in the PayrollGroup2 file group.

Which of the criteria, if any, do you meet with this solution? (Choose all that apply.)

  1. Physical I/O for insert and delete operations is optimal.
  2. You can back up volatile data independently of rarely changing data.
  3. You can archive image data separately.
  4. Physical I/O for index read operations is optimal.

Question 2
Backups on Server A have the following schedule:

  • Complete backup of all data on Sunday
  • Backup of changes to the data since Sunday every 2 hours during business hours
  • Backup of the data that changed during the day each night, Monday through Saturday

Which type of backup occurs each night?

  1. Full database backup
  2. Differential backup
  3. Transaction log backup
  4. Incremental transaction log backup

Question 3
You need to provide 24 x 7 access to your SQL Server, but you can accommodate a short loss (less than 5 minutes) of service. The application that uses your server is written in such way that it's impossible to have incomplete or incorrect data in the database, even if the server crashes. If the server does crash, SQL Server can reconstruct any transaction that was in progress. Finally, the application uses ODBC entries in each client computer's registry to hold server name and login information.

You must meet the following criteria:

  • Users wait no more than 5 minutes for service to be restored.
  • You can't change anything in the application.
  • All operations must be intact or not applied at all.

You configure your environment in the following way:

  • You choose the TCP/IP network library as the default client network library.
  • You create a standby server for your primary server.
  • You make transaction log backups of the primary database every 30 minutes and restore them automatically to the standby server.
  • If your primary server crashes, you restore with the most recent transaction log backup with the Recovery option and change the primary server's IP address to the standby server's IP address.

Which criteria, if any, do you meet with this solution? (Choose all that apply.)

  1. Users wait less than 5 minutes for service to be restored.
  2. No changes need to be made to the application.
  3. Transactional integrity remains intact.
  4. You meet none of the criteria.

Answers (March 2, 2001)

Answer to Question 1
The correct answers are A—Physical I/O for insert and delete operations is optimal; and B—You can back up volatile data independently of rarely changing data. Answer A is correct because the CREATE DATABASE command creates a 10MB data file on drive C, a 25MB data file on drive D, a 55MB data file on drive E, and a 5MB log file on drive F. By splitting the database storage across multiple drives, SQL Server can take advantage of Windows 2000 and Windows NT's asynchronous read/write ability, which means a read/write operation on one drive won't block a read/write request for a different drive. Allocating each group of tables to a specific file group further improves physical I/O performance by isolating rapidly changing data, which can require insertions and deletions, from slowly changing data.

Answer to Question 2
The correct answer is B—Differential backup. Differential backups back up only pages that have changed since the last backup. When you combine a differential backup with a full database backup, you can restore the database by restoring the full database backup and then restoring the differential backups in the order they were made. The benefit to differential backups is that they generally take less time than full or incremental backups when the number of changed pages is small relative to the total size of the database.

Answer to Question 3
The correct answers are A—Users wait less than 5 minutes for service to be restored; B—No changes need to be made to the application; and C—Transactional integrity remains intact. The transaction log records the actual operations that change data in the database, and the log includes a special record that indicates when a set of operations completes successfully. If the server crashes before all operations in a transaction have completed, at the next startup, SQL Server automatically rolls back all the changes those operations made. Transaction log backups preserve the series of operations applied to the data and let an administrator undo any operations that were in progress when the system crashed.