微软mcitp认证70-450考试：列几个样题和答案解析25-30
QUESTIo n 26
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 database is hosted by the instance.
The company utilizes the following backup strategy for the database.
First, the performance of a full database backup is implemented at 02:00 hr every Mo nday.
Seco ndly, the performance of a differential backup is implemented at 23:00 hr every day.
Thirdly, the performance of a transactio n log backup is implemented o n an hourly basis.
A reboot is caused o n the SQL Server 2008 server by a power failure at 09:15 hr, Thursday.
Fifteen minutes after the reboot of the server, it is reported that certain queries that access customer data cannot be executed. You find that the customer data remains unvaried after the power failure.
When the DBCC CHECKDB command is executed o n the database, the following error message is received.
Object ID 2814307832,
index ID 2,
partitio n ID 83127819437122157,
alloc unit ID 82134587923221126 (type In-row data): Page (3421:169) could not be processed.
See other errors for details.
Table error:
Object ID 2814307832,
index ID 2,
partitio n ID 83127819437122157,
alloc unit ID 82134587923221126 (type In-row data),
page (3421:169).
Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed.
Values are 16928422 and -8.CHECKDB found 0 allocatio n errors and 2 co nsistency errors in table 'tbl_Customer' (object ID 2814307832).
When the sp_help 'tbl_customer' stored procedure is executed, the following result set is received:
index_name index_descriptio n
--------------- ----------------------------------
PK clustered located o n PRIMARY
NCIX no nclustered located o n PRIMARY
Since you are the technical support, you are required to make sure that the data should be accessible in the shortest time. And the impact o n users should be cut to the least.
Which actio n will you perform to finish the task?
A. To finish the task, the PK index should be dropped and recreated.
B. To finish the task, the latest full database backup should be recovered. And all transactio n log backups
should be recovered from the latest full database backup.
C. To finish the task, the latest full database backup should be recovered. And then, the latest differential
backup should be recovered. At last, all transactio n log backups should be recovered
from the latest differential backup.
D. To finish the task, the NCIX index should be dropped and recreated.
QUESTIo n 26
Answer: D
Explanatio n/Reference:
As you can see in the DBCC CHECKDB, the failed object is the index with the ID=2.
Or, the index with the ID=2 is the index NCIX, o ne no nclustered index. So, this index is build upo n the Cluster index and all the leaf pages co ntains the leaf page address of the clustered index.
To rebuild this index in the fatsest way, the solutio n is to drop it and to re-create it after.
QUESTIo n 27
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.
A new database will be deployed to the instance.
Regular inserts and updates should be subjected by the database.
There will be multiple schemas o n the database.
A great number of read-o nly reference data should be co ntained by o ne of the schemas.
The physical database structure should be designed for optimal backup performance.
Which actio n should you perform?
A. You should utilize a single log file and multiple filegroups to create the database.
B. You should utilize a single data file and a single log file to create the database.
C. You should utilize a single data file and multiple log files to create the database.
D. You should utilize a single log file and a filegroup that has multiple data files to create the database.
QUESTIo n 27
Answer: A
Explanatio n/Reference:
In order to be able to backup filegroups with different schedules, the easiest way is to create multiple filegroups and o nly o ne log file.
QUESTIo n 28
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 instance is o ne of three servers, and the three servers are in a peer-to-peer transactio nal replicatio n topology.
A table named Orders is included by the publicatio n. 200 GB of data across multiple partitio ns are co ntained by the Orders table. 10 GB of data is loaded to the Orders table periodically by a batch process bulk.
A replicatio n strategy should be designed to have latency minimized.
Which actio n will you perform?
A. The BULK INSERT command should be minimized in multiple staging tables. And then partitio ns should be switched into the Orders table.
B. The Distributor agent should be set to duplicate transactio ns co ntinually.
C. The Distributor agent should be set to modify the commit batch threshold.
D. The Distributor agent should be switched. And then the BULK INSERT command should be utilized in the Orders table. At last, the publicatio n should be reinitialized.
QUESTIo n 28
Answer: A
Explanatio n/Reference:
QUESTIo n 29
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 database is hosted by the instance.
High volumes of queries and transactio ns that are processor-intensive are experienced by the database.
An incremental backup strategy should be designed for the database. In additio n, backup compressio n will be utilized.
Since you are the technical support of the company, you are required to reduce the impact from the backup jobs o n the performance of the server to the least.
Which actio n will you perform to achieve the goal?
A. To achieve the goal, database snapshots should be utilized.
B. To achieve the goal, the Affinity I/O Mask co nfiguratio n optio n should be reco nfigured.
C. To achieve the goal, the database backup should be spread across multiple backup devices.
D. To achieve the goal, the Resource Governor should be utilized.
QUESTIo n 29
Answer: D
Explanatio n/Reference:
As we have the following statement "An incremental backup strategy should be designed for the database. In additio n, backup compressio n will be utilized." and "you are required to reduce the impact from the backup jobs o n the performance of the server to the least"
We can remove answer C from the choices because we will use the backup compressio n and this use a lot of ressource
Ressource gouvernor will help us to achieve thi by creating assignment functio n and ressource pool. We can manage the backups and the queries in parallel.
QUESTIo n 30
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 Reporting Services (SSRS) instance is managed by you. An authenticatio n method should be designed for SSRS reports that SQL Server data co nnectio ns are utilized.
Since you are the technical support of the company, you are required to make sure that all SSRS reports utilize the Kerberos authenticatio n protocol to communicate to the SQL Server instance.
Which actio n should you perform?
A. The default Web site should be set o n the instance to utilize the Integrated Security type.
B. The applicatio n pool utilized by the default Web site should be set to utilize a domain account.
C. A service principal name (SPN) should be registered in the Active Directory directory service for the SSRS
instance.
D. The default Web site o n the instance should be set to utilize the Secure Sockets Layer (SSL) protocol.
QUESTIo n 30
Answer: C
Explanatio n/Reference:
A service principal name (SPN) is the name by which a client uniquely identifies an instance of a service. The Kerberos authenticatio n service can use an SPN to authenticate a service. When a client wants to co nnect to a service, it locates an instance of the service, composes an SPN for that instance, co nnects to the service, and presents the SPN for the service to authenticate.
Permissio ns
To register the SPN, the Database Engine must be running under a built-in account, such as Local System or NETWORK SERVICE, or an account that has permissio n to register an SPN, such as a domain administrator account. If SQL Server is not running under o ne of these accounts, the SPN is not registered at startup and the domain administrator must register the SPN manually.
more in BOL