微软mcitp认证70-450考试：列几个样题和答案解析21-25
QUESTIo n 21
You are a professio nal level SQL Sever 2008 Database Administrator in an internatio nal corporatio n named Wiikigo.
You are experienced in managing databases in an enterprise-level organizatio n, optimizing and sustaining the database life cycle.
In the company, your job is to implement solutio ns o n security, troubleshooting, deployment and optimizatio n. A SQL Server 2008 instance is managed by you.
A database is hosted by the instance and the database is co nfigured by utilizing high-safety mirroring operatio n mode alo ng with a witness server.
A memory failure is experienced by the witness server; therefore the server will be offline for five hours.
The mirroring strategy should be reset to cut the risk of database unavailability to the least level.
Which actio n should you perform to finish the task?
A. To finish the task, database mirroring should be paused.
B. To finish the task, the witness server should be moved from the mirroring sessio n.
C. To finish the task, asynchro nous operating mode should be utilized.
D. To finish the task, database mirroring should be removed.
QUESTIo n 21
Answer: B
Explanatio n/Reference:
Removing the witness will keep the mirroring sessio n but without automatic failover.
You can remove the witness from a sessio n if you are co nnected to the current principal server.
To remove the witness
1. Co nnect to the principal server instance and, in the Object Explorer pane, click the server name to expand the server tree.
2. Expand Databases, and select the database whose witness you want to remove.
3. Right-click the database, select Tasks, and then click Mirror. This opens the Mirroring page of the Database Properties dialog box.
4. To remove the witness, delete its server network address from the Witness field.
Note If you switch from high-safety mode with automatic failover to high-performance mode, the Witness field is automatically cleared.
1. Principal 与Mirror 以及 Witness 三台实例都安装为相同版本，目前是到SQL Server 2008 SP1。
2. 需要有一个域帐户分别添加到 三台数据库实例所在的Server 的 Administrators组、以及SQL Server 相关的组中； 由于具体的组的功能不清楚，因此建议所有组都添加该域帐户，确保权限运行正常。
3. 将第二步中的域帐户设置为SQL Server 服务，SQL Server Agent 的启动帐户，并重启服务。
4. 确保Principal 与Miroor 两台实例上的数据数据及日志相同。备份模式为完全备份，恢复模式为完全，向Mirror实例上恢复数据库时必须选择为NoRecovery模式。
QUESTIo n 22
You are a professio nal level SQL Sever 2008 Database Administrator in an internatio nal corporatio n named Wiikigo.
You are experienced in managing databases in an enterprise-level organizatio n, optimizing and sustaining the database life cycle. In the company, your job is to implement solutio ns o n security, troubleshooting, deployment and optimizatio n.
A SQL Server 2008 instance is managed by you. And a large financial database is hosted by the instance.
The backup strategies listed below are co ntained by the database.
First, the performance of a full database backup is implemented o nce a week.
Seco ndly, the performance of a differential backup is implemented every day.
Thirdly, the performance of a transactio n log backup is implemented every hour.
An end-of-year batch process should be implemented, and the process takes two hours to run. About five percent of data within the database will be changed by the batch process.
Since you are the technical support, you are required to make sure that the batch operatio n can be rolled back in the shortest time, if the batch process is not approved by the Finance department. What should you do first before start the batch process?
A. You should perform a differential backup.
B. You should create a marked transactio n. And then you should perform a transactio n log backup.
C. You should create a database snapshot.
D. You should record the time before the batch operatio n. And then you should perform a transactio n log
backup.
QUESTIo n 22
Answer: C
Explanatio n/Reference:
Reaso ns to take database snapshots include:
Maintaining historical data for report generatio n.
Because a database snapshot provides a static view of a database, a snapshot can extend access to data from a particular point in time. For example, you can create a database snapshot at the end of a given time period(such as a financial quarter) for later reporting. You can then run end-of-period reports o n the snapshot. If disk space permits, you can also maintain end-of-period snapshots indefinitely, allowing queries against the results from these periods; for example, to investigate organizatio nal performance.
Using a mirror database that you are maintaining for availability purposes to offload reporting.
Using database snapshots with database mirroring permits you to make the data o n the mirror server accessible for reporting. Additio nally, running queries o n the mirror database can free up resources o n the principal. For more informatio n, see Database Mirroring and Database Snapshots.
Safeguarding data against administrative error.
Before doing major updates, such as a bulk update or a schema change, create a database snapshot o n the database protects data. If you make a mistake, you can use the snapshot to recover by reverting the database to the snapshot. Reverting is potentially much faster for this purpose than restoring from a backup; however, you cannot roll forward afterward.
QUESTIo n 23
You are a professio nal level SQL Sever 2008 Database Administrator in an internatio nal corporatio n named Wiikigo.
You are experienced in managing databases in an enterprise-level organizatio n, optimizing and sustaining the database life cycle. In the company, your job is to implement solutio ns o n security, troubleshooting, deployment and optimizatio n.
Four SQL Server 2008 instances are managed by you. And they are respectively named Ins01, Ins02, Ins03 and Ins04. A single database applicatio n is hosted by each instance.
All four instances should be removed to a new SQL Server failover cluster. You can see the co nfiguratio n of the four instances from the table listed below.
All the four instances are sufficiently optimized, and there are no spare CPU cycles or extra memory in the instances.
All four databases will be hosted by the new cluster o n a single virtual cluster IP address.
Since you are the technical support, you are required to set the new cluster to handle the workload of all the database applicatio ns with the least amount of hardware resources.
From the four cluster co nfiguratio n, which o ne should be utilized?
A. Four-node active/active/active/active cluster should be utilized and a minimum of 9 processors and a
25-GB memory are co ntained by each node.
B. Two-node active/passive cluster should be utilized and a minimum of 18 processors and a 50-GB memory are co ntained by each node. 双机热备
C. Two-node active/active cluster should be utilized and a minimum of 9 processors and a 25-GB memory are co ntained by each node.
D. Four-node active/active/active/passive cluster should be utilized and a minimum of 18 processors and a
50-GB memory are co ntained by each node.
QUESTIo n 23
Answer: B
Explanatio n/Reference:
To use the least amount of hardware resources, we will create a single two nodes Active-Passive cluster.
As all the instances should be able to run without impact, we will sum the number of CPU; here it is 18 CPU for each node
Memory needed= 16 GB + 8 GB + 16 GB + 8 GB = 48 GB + 2 GB for the OS = 50 GB
QUESTIo n 24
You are a professio nal level SQL Sever 2008 Database Administrator in an internatio nal corporatio n named Wiikigo.
You are experienced in managing databases in an enterprise-level organizatio n, optimizing and sustaining the database life cycle. In the company, your job is to implement solutio ns o n security, troubleshooting, deployment and optimizatio n.
A SQL Server 2008 infrastructure is managed by you. An instance runs o n a computer, and the computer has eight quad-core processors and 128-GB RAM.
The instance is utilized by four different applicatio ns. Not o nly a low number of CXPACKET waits but also a large number of lazy writer waits are experienced by the instance.
The performance of the instance should be optimized.
Which actio n will you perform to accomplish the goal?
A. To accomplish the goal, software no n-uniform memory access (soft-NUMA) should be co nfigured.
B. To accomplish the goal, the Resource Governor should be co nfigured.
C. To accomplish the goal, the Windows System Resource Manager should be co nfigured.
D. To accomplish the goal, an increase should be co nfigured in the maximum degree of parallelism optio n.
QUESTIo n 24
Answer: A
Explanatio n/Reference:
The instance is utilized by four different applicatio ns. Not o nly a low number of CXPACKET waits but also a large number of lazy writer waits are experienced by the instance.
with software no n-uniform memory access, we could set multiple threads to i/o see example below:
Co nsider the following example.
A computer with eight CPUs does not have hardware NUMA. Three soft-NUMA nodes are co nfigured. Database Engine instance A is co nfigured to use CPUs 1 through 4. A seco nd instance of the Database Engine is installed and co nfigured to use CPUs 5 through 8.
The example can be visually represented as:
CPUs 1 2 3 4 5 6 7 8
Soft-NUMA <-N0--><-N1-><----N2---->
SQL Server <instance A ><instance B>
Instance A, which experiences significant I/O, now has two I/O threads and two lazy writer threads, while instance B, which performs processor-intensive operatio ns, has o nly o ne I/O thread and o ne lazy writer thread. Differing amounts of memory can be assigned to the instances, but unlike hardware NUMA, they both receive memory from the same operating system memory block and there is no memory-to-processor affinity.
QUESTIo n 25
You are a professio nal level SQL Sever 2008 Database Administrator in an internatio nal corporatio n named Wiikigo.
You are experienced in managing databases in an enterprise-level organizatio n, optimizing and sustaining the database life cycle. In the company, your job is to implement solutio ns o n security, troubleshooting, deployment and optimizatio n.
A SQL Server 2008 instance is managed by you. A database is hosted by the instance, and sensitive data is included in the database.
A database backup strategy will be implemented for the database. You need to have all backups of the database encrypted. The backups should not be accessed by unauthorized users.
Which actio n will you perform?
A. Transparent database encryptio n should be utilized.
B. Windows BitLocker Drive Encryptio n technology should be utilized.
C. The BACKUP statement should be utilized alo ng with the PASSWORD optio n.
D. The BACKUP statement should be utilized alo ng with the MEDIAPASSWORD optio n.
QUESTIo n 25
Answer: A
Explanatio n/Reference:
You can take several precautio ns to help secure the database such as designing a secure system, encrypting co nfidential assets, and building a firewall around the database servers.
However, in a scenario where the physical media (such as drives or backup tapes) are stolen, a malicious party can just restore or attach the database and browse the data. o ne solutio n is to encrypt the sensitive data in the database and protect the keys that are used to encrypt the data with a certificate. This prevents anyo ne without the keys from using the data, but this kind of protectio n must be planned in advance.
Transparent data encryptio n (TDE) performs real-time I/O encryptio n and decryptio n of the data and log files. The encryptio n uses a database encryptio n key (DEK), which is stored in the database boot record for availability during recovery. The DEK is a symmetric key secured by using a certificate stored in the master database of the server or an asymmetric key protected by an EKM module. TDE protects data "at rest", meaning the data and log files. It provides the ability to comply with many laws, regulatio ns, and guidelines established in various industries. This enables software developers to encrypt data by using AES and 3DES encryptio n algorithms without changing existing applicatio ns.