微软mcitp认证70-450考试：列几个样题和答案解析6-10
QUESTIo n 6
Answer: C
Explanatio n/Reference:
Introducing SQL Server Extended Events
SQL Server Extended Events(Extended Events) is a general event-handling system for server systems. The Extended Events infrastructure supports the correlatio n of data from SQL Server, and under certain co nditio ns, the correlatio n of data from the operating system and database applicatio ns. In the latter case, Extended Events output must be directed to Event Tracing for Windows (ETW) in order to correlate the event data with operating system or applicatio n event data.
All applicatio ns have executio n points that are useful both inside and outside an applicatio n. Inside the applicatio n, asynchro nous processing may be enqueued using informatio n that is gathered during the initial executio n of a task. Outside the applicatio n, executio n points provide mo nitoring utilities with informatio n about the behavioral and performance characteristics of the mo nitored applicatio n.
Extended Events supports using event data outside a process. This data is typically used by:
Tracing tools, such as SQL Trace and System Mo nitor.
Logging tools, such as the Windows event log or the SQL Server error log.
Users administering a product or developing applicatio ns o n a product.
独特型，扩展事件是新的trace功能
QUESTIo n 7
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 are 30 branch offices in DoubleSecurity Insurance双保险, and in the branch offices, customer data are stored in SQL Server 2008 databases.
Customer data should be security compliant if it is stored through multiple database instances. You intend to utilize the Policy-Based Management feature to design a strategy for custom policies. And the format of custom policies is XML format. The requirements listed below should be satisfied.
The company distributes custom policies to all instances. In additio n, the company enforces the policies o n all instances.
A strategy should be thought out and the minimum amount of administrative effort should be utilized.
Which actio n should you perform to finish the task?
A. To finish the task, the Distributed File System Replicatio n service should be utilized.
B. To finish the task, a co nfiguratio n server should be utilized.
C. To finish the task, the policies should be distributed by utilizing Group Policy Objects.
D. To finish the task, the policies should be distributed by utilizing the Active Directory directory service.
QUESTIo n 7
Answer: B
Explanatio n/Reference:
Co nfiguratio n Server or Central Management Server
In SQL Server 2008, you can designate an instance of SQL Server as a Central Management Server. Central Management Servers store a list of instances of SQL Server that is organized into o ne or more Central Management Server groups. Actio ns that are taken by using a Central Management Server group will act o n all servers in the server group. This includes co nnecting to servers by using Object Explorer and executing Transact-SQL statements and Policy-Based Management policies o n multiple servers at the same time. All Central Management Servers and subordinate servers must be registered by using Windows Authenticatio n. Versio ns of SQL Server that are earlier than SQL Server 2008 cannot be designated as a Central Management Server.
独特型，中央管理服务器
QUESTIo n 8
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 database solutio n is managed by you.
All data changes are implemented through stored procedures, and o nly the INSERT, UPDATE, or DELETE statements are utilized by the procedures. A backup strategy should be implemented.
The business requirements listed below should be satisfied. Point-in-time recovery for failure is supported by the backup strategy at any time of day.
In additio n, the least amount of disk space should be utilized by the transactio n log.
Which actio n should you perform to finish the task?
A. To finish the task, hourly database snapshots should be utilized.
B. To finish the task, the full-recovery model alo ng with transactio n log backups should be utilized.
C. To finish the task, the full-recovery model alo ng with differential backups should be utilized.
D. To finish the task, the simple-recovery model alo ng with differential backups should be utilized.
QUESTIo n 8
Answer: B
Explanatio n/Reference:
Full-recovery Model
Descriptio n
Requires log backups.
No work is lost due to a lost or damaged data file.
Can recover to an arbitrary point in time (for example, prior to applicatio n or user error).
Work loss exposure
Normally no ne.
If the tail of the log is damaged, changes since the most recent log backup must be redo ne.
Recover to point in time?
Can recover to a specific point in time, assuming that your backups are complete up to that point in time.
要想实现full-recovery model，必须transactio n log。
QUESTIo n 9
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 utilized by the instance, and the database is utilized by a Web-based applicatio n. 15,000 transactio ns are processed by the applicatio n every minute. A column is co ntained by a table in the database, and the column is utilized o nly by the applicatio n. Sensitive data is stored in this column.
The sensitive data should be stored with the highest security level. In additio n, the least amount of memory space and processor time should be utilized.
From the following four encryptio n types, which o ne should you utilize?
A. Asymmetric key encryptio n should be utilized.
B. Certificate-based encryptio n should be utilized.
C. Symmetric key encryptio n should be utilized.
D. Transparent data encryptio n should be utilized.
QUESTIo n 9
Answer: C
Explanatio n/Reference:
Cryptographic Key Hierarchy
At the root of encryptio n tree is the Windows Data Protectio n API (DPAPI), which secures the key hierarchy at the machine level and is used to protect the service master key (SMK) for the database server instance. The SMK protects the database master key (DMK), which is stored at the user database level and which in turn protects certificates and asymmetric keys. These in turn protect symmetric keys, which protect the data. TDE uses a similar hierarchy down to the certificate. The primary difference is that when you use TDE, the DMK and certificate must be stored in the master database rather than in the user database. A new key, used o nly for TDE and referred to as the database encryptio n key (DEK), is created and stored in the user database.
This hierarchy enables the server to automatically open keys and decrypt data in both cell-level and database-level encryptio n. The important distinctio n is that when cell-level encryptio n is used, all keys from the DMK down can be protected by a password instead of by another key. This breaks the decryptio n chain and forces the user to input a password to access data. In TDE, the entire chain from DPAPI down to the DEK must be maintained so that the server can automatically provide access to files protected by TDE. In both cell-level encryptio n and TDE, encryptio n and decryptio n through these keys is provided by the Windows Cryptographic API (CAPI).
Symmetric keys use the same password to encrypt and decrypt data, so it is the less space co nsuming, because o ne asymetric key will use o ne private and o ne public key.
独特型，对称密钥加密
QUESTIo n 10
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 new database applicatio n is hosted by the instance.
The security requirements should be designed for the applicatio n. A unique login to the SQL Server 2008 server is assigned to each applicatio n user.
Stored procedures are included by the applicatio n database to execute stored procedures in the MSDB database. SQL Agent jobs are scheduled by the stored procedures in the MSDB database.
Since you are the technical support, you are required to co nfirm that the stored procedures in the MSDB database should be executed by utilizing the security co ntext of the applicatio n user.
Which actio n should you perform ?
A. Each user should be added to the public role in the MSDB database.
B. Each user should be added to the db_dtsltduser database role in the MSDB database.
C. The MSDB database should be set to utilize the TRUSTWORTHY optio n, and then each user should be
added to the MSDB database.
D. The new database should be set to utilize the TRUSTWORTHY optio n, and then each user should be added
to the MSDB database.
QUESTIo n 10
Answer: D
Explanatio n/Reference:
The TRUSTWORTHY database property is used to indicate whether the instance of SQL Server trusts the database and the co ntents within it. By default, this setting is OFF, but can be set to o n by using the ALTER DATABASE statement. For example, ALTER DATABASE AdventureWorks2008R2 SET TRUSTWORTHY o n;
By default msdb has the optio n TRUSTWORTHY set to True.
易混淆型，新DB要配为值得信赖。