微软mcitp认证70-450考试：列几个样题和答案解析11-15
QUESTIo n 11
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 maintenance strategy should be designed for a missio n-critical database, and a large table named Orders is co ntained by the database.
Index maintenance operatio ns are co ntained in the design plan. When you design the strategy, the facts listed below should be taken into co nsideratio n.
First, the users co ntinuously access to the Orders table in the database.
Seco ndly, a column of the xml data type is co ntained by Orders table.
Thirdly, the new rows are regularly added to the Orders table.
Fourthly, the average fragmentatio n for the clustered index of the Orders table is no more than 2 percent.
A strategy should be designed to have the performance of the queries o n the table optimized.
Which actio n will you perform?
A. The clustered index of the Orders table should be dropped.
B. The clustered index of the Orders table offline should be rebuilt o nce a mo nth.
C. The clustered index of the Orders table should be excluded from scheduled reorganizing or rebuilding operatio ns.
D. The clustered index of the Orders table should be reorganized by reducing the fill factor.
QUESTIo n 11
Answer: C
Explanatio n/Reference:
As the users will co ntinously access the database and there is o ne cluster index, the cluster index could not be unavaillable because the leaf pages of the clustered index co ntains the table data.
Furthermore, the cluster index has never o ne fragmentatio n of more than 2%, this means it doesn't need to be reordered.
With this, you can be sure that answer A,B and D are wro ng.
易混淆型，题目要性能，所以重做把集群索引排除。
QUESTIo n 12
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.
The security requirements should be designed for a new database applicatio n.
A code segment is utilized by the applicatio n, and the code segment includes the following compo nents:
A method that the registry is accessed o n the SQL Server, a method that the file system is accessed o n a network file server, and a class definitio n that public static fields are utilized.
SQL CLR integratio n is utilized by the code segment, and the code segment is implemented as a single assembly.
Since you are the technical support, you are required to make sure that the applicatio n should be successfully deployed to the instance.
Which actio n will you perform?
A. The SAFE code access security should be utilized for the assembly.
B. All public static fields should be replaced with public fields.
C. All public static fields should be utilized with public static read-o nly fields. And then the assembly shouldbe registered by utilizing the regasm.exe utility before deployment.
D. All public static fields should be replaced with public static read-o nly fields. And then the EXTERNAL_ACCESS code access security should be utilized for the assembly.
QUESTIo n 12
Answer: D
Explanatio n/Reference:
Creates a managed applicatio n module that co ntains class metadata and managed code as an object in an instance of SQL Server. By referencing this module, commo n language runtime (CLR) functio ns, stored procedures, triggers, user-defined aggregates, and user-defined types can be created in the database.
PERMISSIo n_SET { SAFE | EXTERNAL_ACCESS | UNSAFE }
Specifies a set of code access permissio ns that are granted to the assembly when it is accessed by SQL Server. If not specified, SAFE is applied as the default.
We recommend using SAFE. SAFE is the most restrictive permissio n set. Code executed by an assembly with SAFE permissio ns cannot access external system resources such as files, the network, enviro nment variables, or the registry.
EXTERNAL_ACCESS enables assemblies to access certain external system resources such as files, networks, enviro nmental variables, and the registry.
独特型，记外部存取。
QUESTIo n 13
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. The security requirements should be designed for a new database applicatio n, and the applicatio n will be deployed to the instance.
A table is co nsisted by the new database, and the table is created by utilizing the following code segment:
CREATE TABLE CK_DISCOUNTS
(
ProductID INT NOT NULL IDENTITY(1,1)
Discount VARBINARY (MAX) FILESTREAM
)
Since you are the technical support, you should utilize the maximum secure method to protect the Bo nusPlan column from the access of unauthorized users.
Which actio n will you perform to finish the task?
A. To finish the task, Transparent data encryptio n should be utilized.
B. To finish the task, the NTFS file system security should be utilized and the access of the database files should be limited to the SQL Server 2008 Service account.
C. To finish the task, the Trustworthy optio n for the database should be utilized.
D. To finish the task, the Advanced Encryptio n Standard encryptio n should be utilized o n all columns in the database.
QUESTIo n 13
Answer: B
Explanatio n/Reference:
The column Bo nusPln is of type FILESTREAM this means that the data of this column is saved outside of the database o n the filesystem. So, you have to protect those files by the NTFS security.
QUESTIo n 14
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 an instance. And a large table named OrderDetails is included by the database.
o nly DML statements o n the last three mo nths data are executed by the applicatio n queries. Administrative audits are managed mo nthly o n data which is lo nger than four mo nths.
The performance problems listed below are found by you in the database.
The performance of the applicatio n queries against the OrderDetail table is poor.
It takes a lo ng time to perform the maintenance tasks against the database, index defragmentatio n is co ntained.
The performance problems should be solved with o n impact o n the server performance. Which actio n will you perform?
A. A database snapshot should be created for the OrderDetails table every four mo nths. And then, the queries should be changed to utilize the present snapshot.
B. An additio nal table named OrderDetailsHistory should be created for data older than four mo nths.
And then, the OrderDetails and OrderDetailsHistory tables should be partitio ned in two parts by utilizing the OrderDate column.
At last, a SQL Server Agent job that runs every mo nth should be created and the ALTER TABLE...SWITCH Transact-SQL statement should be utilized to remove data that is lo nger than four mo nths to the OrderDetailsHistory table.
C. An additio nal table named OrderDetailsHistory should be created for data lo nger than four mo nths.
And then, a SQL Server Agent job that runs the following Transact-SQL statement every mo nth should be created.
INSERT INTO OrderDetailsHistory SELECT * FROM OrderDetails WHERE DATEDIFF(m,OrderDate,GETDATE())>4
D. An additio nal table named OrderDetailsHistory should be created for data older than four mo nths.
And then, the following Transact-SQL statement should be utilized.
CREATE TRIGGER trgMoveData o n OrderDetails
AFTER INSERT
AS INSERT INTO OrderDetailsHistory SELECT * FROM
OrderDetailsWHERE DATEDIFF(m,OrderDate,GETDATE())>4
QUESTIo n 14
Answer: B
Explanatio n/Reference:
The easiest way to deal with history tables is to use partitio nned tables and the switch command should be use to attach the old data to the history table.
QUESTIo n 15
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. SQL Server 2008 instances at five sites are managed by you.
A missio n-critical database is shared by the five sites. According to the business requirements, the users at each site should be enabled to access and change data o n all sites with minimal latency.
In additio n, data loss should be minimized if a server fails.
A high-availability solutio n should be designed, and the business requirements should be satisfied.
Which actio n should be included in your solutio n?
A. Failover clustering should be included.
B. Asynchro nous database mirroring without a witness server should be included.
C. Peer-to-Peer replicatio n should be included.
D. Log shipping to servers at two of the sites to offer read-o nly replicatio ns of data should be included.
QUESTIo n 15
Answer: C
Explanatio n/Reference:
As required from the business, the o nly way to deal with this request is the Peer-to-Peer replicatio n as by default the databases are synchro nized co ntinuously o n each site.