Reading...
Play button
Play button
Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
23 Cards in this Set
- Front
- Back
|
What are the 2 major considerations for determining how to configure service accounts?
|
1. Use separate accounts for SQL Server Service and SQL Server Agent, or use single account for both?
2. Use a built-in system account or a domain account? |
|
|
Use separate accounts for SQL Server Service and SQL Server Agent, or use single account for both?
|
Since the service account rarely needs to interact with other servers, while the Agent account does, it's generally better to create 2 separate accounts.
|
|
|
What are the 3 types of accounts you can choose from when selecting SQL Agent and Service accounts? Which is preferred?
|
1. Network Service Account
2. Local System Account 3. Dedicated Domain User Account It's generally considered best practice to use dedicated domain accounts because the other types grant too many privileges. |
|
|
What are SQL Server's 2 authentication methods? Which is preferred?
|
1. Windows Authentication
2. Mixed-mode. Windows Auth is generally preferred; mixed-mode is supported for backward compatibility with legacy apps. |
|
|
Which SQL Server 2005 services require that you install them with their own account?
|
SQL Server Agent and SQL Server Service
|
|
|
What are the 2 types of SQL Server operating system files?
|
1. Data Files (.mdf / .ndf)
2. Log Files (.ldf) |
|
|
What are the 2 types of SQL Server data files?
|
1. Primary (.mdf)
2. Secondary (.ndf) |
|
|
Describe the primary data file.
|
The primary data file is mandatory and contains startup information for the db catalog and points to other db files. It can also contain objects and user data but this is not recommended.
|
|
|
Describe the secondary data file(s).
|
Secondary data files are optional and user defined. A db can have a maximum of 32,766 secondary data files.
|
|
|
What is a best practice with regard to placing user data in the primary data file?
|
You shouldn't do it. All data and objects should be stored in secondary files; the primary file should only contain db catalog data in order to reduce disk access contention.
|
|
|
What is a filegroup?
|
A logical structure that lets DBAs group data files together and manage them as a logical unit. They can help improve performance by allowing DBAs to split objects across multiple FGs in order to leverage different disk subsystems.
|
|
|
What are the two types of filegroups supported by SQL Server?
|
1. Primary
2. User-defined |
|
|
Describe the primary filegroup.
|
The primary filegroup contains the primary data file and any secondary data files not stored in another filegroup. All system tables are also stored here.
|
|
|
Describe the user-defined filegroup.
|
The user-defined filegroup contains secondary data files and db objects as designated by the DBA. SQL Server supports up to 32,766.
|
|
|
How many log files can you have in each filegroup?
|
None. Filegroups only support data files.
|
|
|
Can the primary filegroup be designated read-only?
|
No. All other filegroups can however.
|
|
|
Suppose you have an access-intensive table called OrderDetail. How might you improve performance of this table using filegroups?
|
You could create several secondary data files and store these on different disk drives / subsystems, then group them together in a filegroup. You could then save the table into this filegroup. Queries against the table would now be spread across the several disks.
|
|
|
What are the most common RAID Arrays used with SQL Server?
|
RAID (Redundant Array of Inexpensive Disks) 0, 1, 5, 10
|
|
|
Describe RAID 0
|
Striping. Data is striped across all disks in a disk set. Fastest READ and WRITE performance. No fault tolerance. All data on stripe set is lost on failure of any disk.
|
|
|
Describe RAID 1
|
Mirroring. Provides a redundant copy of a selected disk. Fast READ performance, WRITE can be slower.
|
|
|
Describe RAID 5
|
Striping with parity. Like 0 but includes a parity stripe with redundant information for fault tolerance. Parity inf is distributed over all disks. READ and WRITE better than RAID 1; READ drops with disk loss.
|
|
|
Describe RAID 10
|
Really RAID 1 + 0. Striping w/o parity PLUS mirroring. Better availability and performance than RAID 5, but at much higher cost.
|
|
|
What is considered best practice with regard to the location of OS files, data files, log files, and tempDB?
|
Put OS files, data files, log files, and tempDB files on separate drives. Placing tempDB files separately allows SQL Server to perform tempDB functions in parallel with database operations.
|
