Term
You are a SQL Server administrator. You have a server with a database named Commerce that will be used to store sales transactions. The database must be available at all times and must be as fast as possible. Configure your server according to the table below. Where should you place your database and transaction logs for maximum speed and fault tolerance?
SCSI RAID Adapter Raid-5 Disk Array SCSI Adapter Physical Disk 0
400 GB Physical Disk 1 TB
400 GB Physical Disk
400 GB Physical Disk
400 GB Physical Disk
400 GB Physical Disk
a. Place the transaction log on physical disk 0 and the data file on the RAID-5 disk array.
b. Place the transaction log on the RAID-5 disk array and the data file on physical disk 0.
c. Place the transaction log and the data file on physical disk 0.
d. Placce the transaction log and the data file on the RAID-5 disk array. |
|
Definition
| d. Both the data and log files should be placed on the RAID-5 array because it is the only array that offers fault tolerance. Physical disk 0 is just a disk with no fault tolerance whatsoever. |
|
|
Term
You have just installed two new 400 GB hard disks in your server that you are going to use to hold a database named Inventory. You need to add, update, and delete data as fast as possible. How should you configure these hard disks? (Choose two.)
a. Configure the hard disks as a RAID-1 array.
b. Configure the hard disks as a RAID-0 array.
c. Configure the hard disks as a RAID-5 array.
d. Configure the hard disks as two independent drives.
e. Place the data files and log files on the same volume.
f. Place the data file on the first volume and the log file on the second volume. |
|
Definition
| d, f. Because you want to perform write operations as fast as possible, a mirror is not going to work for you because the write speed is very slow. And because you do not have enough disks, you cannot create a RAID-5 array (it requires three at least). RAID 0 is out of the question because of the complete lack of fault tolerance, so the best option is to create two independent disks and put the log and data files on separate disks. That way, if you lose one, you can still recover data from the other. |
|
|
Term
You are about to bring a new server online, and you want the most efficient disk configuration possible for your new system. Select the proper RAID array on which to place your files for optimum performance and fault tolerance. Choose from RAID 1, RAID 2, RAID 0, and RAID 5: OS/Binaries, data files, transaction log.
a. RAID 0 ____________________
b. RAID 1 ____________________
c. RAID 2 ____________________
d. RAID 5 ____________________ |
|
Definition
OS and Binaries should be on a mirror. Transaction logs should be on a mirror because they need the sequential write speed that a mirror provides. Data files should be on a stripe set with parity, for the read speed it provides.
RAID 1: OS/Binaries and Transaction Log
RAID 5: Data Files |
|
|
Term
You are creating an Inventory database that requires 60 GB of data space and 20GB of log space. Your servers' hard disks are configured as shown in the table below. Your OS files are on the C drive. You want to maximize performance. What should you do?
Physical Disk 0 Physical Disk 1
Drive C: 45 GB Drive E: 45 GB
Drive D: 45 GB Drive F: 45 GB
a. Add a 10 GB log to drives C and D, and a 30 GB data file to drives E and F.
b. Add a 10 GB log to drives E and F, and a 30 GB data file to drives E and F.
c. Add a 20 GB log to drive D, and a 30 GB data file to drives E and F.
d. Add a 20 GB log to drive F, and a 30 GB data file to drives D and E. |
|
Definition
| c. The best strategy is to place the log file on drive D and the data files on E and F. That way, all of them can grow if necessary, the transaction log and data files are separate, and the OS is on its own partition. |
|
|
Term
You are the administrator of a SQL Server server that contains a development database. Your developers are not concerned with recovering any of the data in the database in the event of an emergency, and they want to keep the transaction log from accidentally filling up. Which recovery model should you use?
a. Simple.
b. Bulk-logged.
c. Full. |
|
Definition
| a. Simple will allow you to recover the database up to the last full backup; any data after that will be lost. This is the best model to use for development databases because the developers do not need to recover the data in the database, and they need to keep the transaction logs from filling up. |
|
|
Term
You need to configure your system for optimum access to a 1.5 TB database. Approximately half of the tables are used primarily for writing; the rest are used primarily for reading and generating reports. How can you optimize this database for the fastest access?
a. Place the log file and data file on the same disk so the system has to work from only one disk.
b. Create two log files and place each on a separate disk while leaving the disk file on a single disk array.
c. Place the files that are used for reading in one filegroup and the files that are used primarily for writing in a second filegroup on another disk array.
d. Limit the number of users who can access the database at once. |
|
Definition
| c. To specify which disk you want to place an object on, you must create a filegroup and then specify which filegroup to place the object in at the time it is created. |
|
|
Term
Which statement about placing tables and indexes in filegroups is true?
a. Tables and their corresponding indexes must be placed in the same filegroup.
b. Tables and their corresponding indexes must be place in different file groups.
c. Tables and indexes that are place in separate filegroups must be back up together.
d. Tables and indexes that are placed in separate filegroups cannot be backed up together. |
|
Definition
| c. Tables and indexes can be placed in separate filegroups; but if you do that, they must be backed up as a unit. |
|
|
Term
Your company has just installed a new storage area network, and you have been asked for the best RAID model to use for your database. You need optimum speed and reliability. How should you configure these hard disks?
a. Configure the hard disks as a RAID-1 array for data and a RAID-5 array for logs.
b. Configure the hard disks as a RAID-0 array for data and a RAID-5 array for logs.
c. Configure the hard disks as a RAID-5 array for both data and logs.
d. Configure the hard disks as two RAID-10 arrays for both data and logs. |
|
Definition
| d. RAID 10 gives you optimum speed and reliability. If it is available, you should use it. |
|
|
Term
You need to import a large amount of data into a table in one of your production databases using a BULK INSERT statement. Put these steps in the correct order for optimum speed and reliability.
a. Set the database to use the Full recovery model.
b. Set the database to use the Bulk-logged recover model.
c. Set the database to use the Simple recovery model.
d. Back up the database.
e. Run the BULK INSERT statement. |
|
Definition
| b, e, a, d. Before you insert the data, you should set the database to use the Bulk-logged recovery model because it is fastest for inserting bulk data. Next, since this is a production database, it is safe to assume that it was using the Full recovery model, so don’t forget to set it back to Full. Finally, after running the statement, you need to back up the database because you will lose your bulk-imported data if the database crashes before your next scheduled backup. |
|
|
Term
Your servers' hard disks fill to capactiy and your database runs out of space. You do not have money in the budget for more disk space right now, but you do have plenty of disk space on one of your file servers. Can SQL Server use this disk space for database files?
a. Yes, just create secondary data files on the remote server using the UNC filename convention (\\server\share\filename.ext); no other configuration is necessary.
b. Yes, just turn on trace flag 1807, and then create secondary data files on the remote server using the UNC filename convention.
c. Yes, jsut turn on trace flag 3205, and then create the secondary data files on the remote server using the UNC filename convention.
d. No, SQL Server cannot use remote drives for database file storage. |
|
Definition
| b. Yes, you can use the remote drives by enabling trace flag 1807. Trace flag 3205 does exist, but it is for disabling hardware compression for tape drives. |
|
|
Term
You have just created a database with a 500 MB data file. How big will the transaction log be by default?
a. 130 MB
b. 120 MB
c. 125 MB
d 225 MB |
|
Definition
| c. The default transaction log size is 25 percent of the data file size. |
|
|
Term
SQL Server uses which of these page types to store information about changes to the database since the last BACKUP DATABASE statement was executed?
a. Index Allocation Map page
b. Global Allocation Map page
c. Differential Changed Map page
d. Data page
e. Page Free Space page
f. Index page
g. Bulk Channel Map page
h. Text/image page |
|
Definition
| c. The Differential Changed Map page stores information about changes to the data since the last BACKUP DATABASE statement was executed. |
|
|
