微软mcitp认证70-450考试：列几个样题和答案解析1-5
QUESTIo n 1
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.
A database is included by the instance, and a table named EmployeeData is co ntained by the database.
There is a column named Profile in the EmployeeData table. Microsoft Office Word 2003 documents are stored in the column.
The space utilized by the Profile column should be cut to the least.
In additio n, a reaso nable performance should be maintained to retrieve the co ntent of the column.
Which actio n will you perform?
A. ROW compressio n should be utilized for the EmployeeData table.
B. The Profile column should be stored by utilizing filestream storage, and the NTFS file system compressio n should be utilized.
C. PAGE LEVEL compressio n should be utilized for the EmployeeData table.
D. The Profile column should be implemented as a CLR user-defined type.
QUESTIo n 1
Answer: B
Explanatio n/Reference:
Much of the data that is created every day is unstructured data, such as text documents, images, and videos. This unstructured data is often stored outside the database, separate from its structured data. This separatio n can cause data management complexities. Or, if the data is associated with structured storage, the file streaming capabilities and performance can be limited.
FILESTREAM integrates the SQL Server Database Engine with an NTFS file system by storing varbinary(max) binary large object (BLOB) data as files o n the file system. Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data. Win32 file system interfaces provide streaming access to the data.
FILESTREAM uses the NT system cache for caching file data. This helps reduce any effect that FILESTREAM data might have o n Database Engine performance. The SQL Server buffer pool is not used; therefore, this memory is available for query processing.
Note:
FILESTREAM data is not encrypted even when transparent data encryptio n is enabled.
QUESTIo n 2
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 should be managed.
It is reported by the customers that the server performance degraded due to a newly implemented process.
Dynamic Management Views is utilized to co nfirm that no lo ng running queries exist. The operating system performance data should be correlated with the actual query executio n trace, and the least administrative effort should be utilized.
Which actio n will you perform to finish the task?
A. To finish the task, Data Collector should be utilized.
B. To finish the task, the SQLdiag.exe utility should be utilized.
C. To finish the task, SQL Server Profiler and the tracerpt.exe utility should be utilized.
D. To finish the task, SQL Server Profiler and System Mo nitor should be utilized.
Sys Mo nitor OS Perform
QUESTIo n 2
Answer: D
Explanatio n/Reference:
SQL Server profiler displays data about a large number of SQL Server events. Whereas Windows System Mo nitor graphically displays informatio n about the server internals. You can merge the two sets of informatio n and walk through a scenario viewing both perspectives using SQL Server Profiler.
To set up the dual-perspective experience, you need to simultaneously capture server performance using both Performance Mo nitor’s Counter Logs and SQL Server Profiler. The steps to do this are listed below:
1. Co nfigure System Mo nitor with the exact counters you want to view later. Be sure to get the scale and everything just right. Set up the Counter Log to the exact same co nfiguratio n.
2. Co nfigure Profiler with the right set of trace events. They must include the start and end time data columns so that Profiler can integrate the two logs later. Save the profiler and close profiler.
3. Manually start the Counter Log. Open SQL Profiler trace code to start the server-side trace.
4. When the test is complete, stop both the counter Log and the server side trace. You need to stop the SQL Profiler which is a negative point in this.
5. Open profiler and open the saved trace file.
6. Use the File->Import Performance Data menu command to import the Counter Log. You have the optio n of selecting o nly the important counters from the performance mo nitor. There will be performance issues if you select too many counters.
QUESTIo n 3
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.
There is a database in the instance, and the day-to-day business of your company requires the database. When reports are executed, slow respo nse time will be experienced by Users.
A performance mo nitoring strategy will be implemented by you so as to have three aspects of data captured and stored:
Blocking and deadlock informatio n,
Executed Transact-SQL statements
Query activity and Counters for disk, CPU, and memory.
You are required to utilize the least amount of administrative effort to implement the mo nitoring process.
Which actio n will you perform to finish the task?
A. To finish the task, the client-side profiler trace should be utilized.
B. To finish the task, the dynamic management views should be utilized.
C. To finish the task, the data collector should be utilized.
D. To finish the task, the System Mo nitor counter log trace should be utilized.
QUESTIo n 3
Answer: C
Explanatio n/Reference:
SQL Server 2008 provides a data collector that you can use to obtain and save data that is gathered from several sources. The data collector enables you to use data collectio n co ntainers, which enable you to determine the scope and frequency of data collectio n o n a SQL Server server system.
The data collector provides predefined collector types that you can use for data collectio n. The collector types provide the actual mechanism for collecting data and uploading it to the management data warehouse. For this release of the data collector, the following collector type is provided.
In This Sectio n
Topic Descriptio n
T-SQL Query Collector Type Describes the structure and behavior of the T-SQL Query collector type.
SQL Trace Collector Type Describes the structure and behavior of the SQL Trace collector type.
Performance Counters Collector Type Describes the structure and behavior of the Performance Counters collector type.
Query Activity Collector Type Describes the structure and behavior of the Query Activity collector type.
QUESTIo n 4
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 should be managed by you.
The computer o n which the instance run has the following three features:
a 64-GB RAM, four quad-core processors, and several independent physical raid volumes
A transactio nal database will be implemented o n the instance. In additio n, the database should have a high volume of INSERT, UPDATE, and DELETE activities.
Creatio n of new tables is co ntained by the activities.
You need to maximize disk bandwidth and decrease the co ntentio n in the storage allocatio n structures so as to have the performance of the database optimized.
Which actio n will you perform to finish the task?
A. To finish the task, database and log files should be placed o n the same volume.
B. To finish the task, the affinity mask optio n should be co nfigured properly.
C. To finish the task, multiple data files should be create for the database.
D. To finish the task, the affinity I/O mask optio n should be co nfigured properly.
QUESTIo n 4
Answer: C
Explanatio n/Reference:
If your database is very large and very busy, multiple files can be used to increase performance. Here is o ne example of how you might use multiple files. Let's say you have a single table with 10 millio n rows that is heavily queried. If the table is in a single file, such as a single database file, then SQL Server would o nly use o ne thread to perform a read of the rows in the table. But if the table were divided into three physical files, then SQL Server would use three threads (o ne per physical file) to read the table, which potentially could be faster. In additio n, if each file were o n its own separate physical disk or disk array, the performance gain would even be greater.
Essentially, the more files that a large table is divided into, the greater the potential performance. Of course there is a point where the additio nal threads aren't of much use when you max out the server's I/O. But up until you do max out the I/O, additio nal threads (and files) should increase performance
QUESTIo n 5
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 should be managed.
Log shipping should be implemented for several databases o n three SQL Server instances. The logs are migrated to a fourth SQL Server instance. A manual failover will be implemented.
You need to ensure that the database applicatio ns utilize the seco ndary server after failover.
Since you are the technical support, you are required to co nfirm that the latest data should be available to users.
Which actio ns should you perform to achieve the goal? (Choose more than o ne)
A. To achieve the goal, you should utilize the WITH RECOVERY optio n o n the last log to apply any unapplied transactio n log backups in sequence to each seco ndary database.
B. To achieve the goal, you should redirect client computers to the seco ndary instance.
C. To achieve the goal, you should replicate all log shipping network shares to the seco ndary instance.
D. To achieve the goal, you should utilize the WITH NORECOVERY optio n to back up the tail of the transactio n log of primary databases.
E. To achieve the goal, you should back up all databases o n the seco ndary instance.
Log shipping原理是双机热备的基础
双机热备基于日志传送原理。全程由作业控制，主服务器定时向辅服务器发送日志，辅服务器处于STAND BY模式下，定时按日志进行差异恢复。如果主服务器停摆，监视服务器会将主服务器和辅服务器的角色互换。监视服务器推荐设置在第三台服务器上，如条件不具备，也可考虑设置在辅服务器上。
QUESTIo n 5
Answer: ABD
Explanatio n/Reference:
Log shipping co nsists of three operatio ns:
1. Back up the transactio n log at the primary server instance.
2. Copy the transactio n log file to the seco ndary server instance.
3. Restore the log backup o n the seco ndary server instance.
The log can be shipped to multiple seco ndary server instances. In such cases, operatio ns 2 and 3 are duplicated for each seco ndary server instance.
A log shipping co nfiguratio n does not automatically fail over from the primary server to the seco ndary server. If the primary database becomes unavailable, any of the seco ndary databases can be brought o nline manually.
To make the target as the new source database you have to
1. Backup the transactio n log tail in order to have the latest transactio ns and to put the database in recovery state
BACKUP LOG [AdventureWorks]
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\Adv.trn'
WITH NO_TRUNCATE ,
NOFORMAT,
NOINIT,
NAME = N'AdventureWorks-Transactio n Log Backup',
SKIP,
NOREWIND,
NOUNLOAD,
NORECOVERY ,
STATS = 10
2. After having copied all the transactio n log backup, restore them in order and, for the latest, use the WITH RECOVERY optio n
3. Redirect all the clients to the new source database