insidetests 70-450 exam - ms sql serv 08

Microsoft 70-450PRO:MS SQL Serv 08,Design,Optimize, and Maintain DB Admin Solu

10 Q&A

Version 3.0

http://www.insidetests.com/70-450.htm



Leading the way in IT testing and certification tools, www.InsideTests.com

- 2 -

Important Note, Please Read Carefully

Other 70-450 productsA) Offline Testing engineUse the offline Testing engine product topractice the questions in an exam environment.

Build a foundation of knowledge which will be useful also after passing the exam.

Latest VersionWe are constantly reviewing our products. New material is added and old material isrevised. Free updates are available for 90 days after the purchase. You should check yourmember zone at 70-450 and update 3-4 days before the scheduled exam date.

Here is the procedure to get the latest version:

1.Go towww.70-450.com2.Click on Log in3.The latest versions of all purchased products are downloadable from here. Just click thelinks.For most updates,it is enough just to print the new questions at the end of the newversion, not the whole document.

FeedbackIf you spot a possible improvement then please let us know. We always interested inimproving product quality.Feedback should be send to [email protected]. You should include the following:Exam number, version, page number, question number, and your login Email.

Our experts will answer your mail promptly.

CopyrightEach iPAD file is a green exe file. if we find out that a particular iPAD Viewer file isbeing distributed by you, 70-450 reserves the right to take legal action against youaccording to the International Copyright Laws.

ExplanationsThis product does not include explanations at the moment. If you are interested inproviding explanations for this exam, please contact [email protected].


- 3 -

www.70-450.com Q: 1 QUESTION 1You are a professional level SQL Sever 2008 Database Administrator in aninternational corporationnamed Wiikigo. You are experienced in managing databases in an enterprise-levelorganization,optimizing and sustaining the database life cycle. In the company, your job is toimplement solutions onsecurity,troubleshooting, deployment and optimization. A SQL Server 2008infrastructure is managed byyou. A database is included by the instance, and a table named EmployeeData iscontained by thedatabase. There is a column named Profile in the EmployeeData table. MicrosoftOffice Word 2003documents are stored in the column. The space utilized by the Profile column shouldbe cut to the least. Inaddition, a reasonable performance should be maintained to retrieve the content ofthe column. Whichaction will you perform?

A. ROW compression should be utilized for the EmployeeData table.

B. The Profile column should be stored by utilizing filestream storage, and the NTFSfile systemcompressionshould be utilized.

C. PAGE LEVEL compression should be utilized for the EmployeeData table.

D. The Profile column should be implemented as a CLR user-defined type.

Answer: B

www.70-450.com Q: 2 QUESTION 10You are a professional level SQL Sever 2008 Database Administrator in aninternational corporation


- 4 -

named Wiikigo. You are experienced in managing databases in an enterprise-levelorganization,optimizing and sustaining the database life cycle. In the company, your job is toimplement solutions onsecurity, troubleshooting, deployment and optimization. A SQL Server 2008instance is managed by you. anew database application is hosted by the instance. The security requirementsshould be designed for theapplication. A unique login to the SQL Server 2008 server is assigned to eachapplication user. Storedprocedures are included by the application database to execute stored procedures inthe MSDB database.SQLAgent jobs are scheduled by the stored procedures in the MSDB database.Since you are thetechnical support, you are required to confirm that the stored procedures in theMSDB database should beexecuted by utilizing the security context of the application user. Which actionshould 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 option, and theneach user shouldbeadded to the MSDB database.

D. The new database should be set to utilize the TRUSTWORTHY option, and theneach user should beaddedto the MSDB database.

Answer: D



- 5 -

named Wiikigo. You are experienced in managing databases in an enterprise-levelorganization,optimizing and sustaining the database life cycle. In the company, your job is toimplement solutions onsecurity, troubleshooting, deployment and optimization. A SQL Server 2008infrastructure is managed byyou. A maintenance strategy should be designed for a mission-critical database, anda large table namedOrders is contained by the database. Index maintenance operations are contained inthe design plan.When you design the strategy, the facts listed below should be taken intoconsideration. First, the userscontinuously access to the Orders table in the database.Secondly, a column of the xml data type is contained by Orders table. Thirdly, thenew rows are regularlyadded to the Orders table. Fourthly, the average fragmentation for the clusteredindex of the Orders tableis no more than 2 percent. A strategy should be designed to have the performance ofthe queries on thetable optimized. Which action 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 once a month.

C. The clustered index of the Orders table should be excluded from scheduledreorganizing or rebuildingoperations.

D. The clustered index of the Orders table should be reorganized by reducing the fillfactor.

Answer: C

www.70-450.com Q: 4 QUESTION 12You are a professional level SQL Sever 2008 Database Administrator in aninternational corporationnamed Wiikigo. You are experienced in managing databases in an enterprise-levelorganization,


- 6 -

optimizing and sustaining the database life cycle. In the company, your job is toimplement solutions onsecurity, troubleshooting, deployment and optimization. A SQL Server 2008instance ismanaged by you. The security requirements should be designed for a new databaseapplication. A codesegment is utilized by the application, and the code segment includes the followingcomponents: A methodthat the registry is accessed on the SQL Server, a method that the file system isaccessed on a networkfile server, and a class definition that public static fields are utilized. SQL CLRintegration is utilized by thecode segment, and the code segment is implemented as a single assembly. Since youare the technicalsupport, you are required to make sure that the application should be successfullydeployed to theinstance. Which action 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-only fields. And thenthe assemblyshouldbe registered by utilizing the regasm.exe utility before deployment.

D. All public static fields should be replaced with public static read-only fields. Andthen theEXTERNAL_ACCESS code access security should be utilized for the assembly.

Answer: D

www.70-450.com Q: 5 QUESTION 13You are a professional level SQL Sever 2008 Database Administrator in aninternational corporationnamed Wiikigo. You are experienced in managing databases in an enterprise-levelorganization,


- 7 -

optimizing and sustaining the database life cycle. In the company, your job is toimplement solutions onsecurity, troubleshooting, deployment and optimization. A SQL Server 2008instance ismanaged by you. The security requirements should be designed for a new databaseapplication, and theapplication will be deployed to the instance.A table is consisted by the new database, and the table is created by utilizing thefollowing code segment:TABLE EmpBonusPlanSince you are the technical support, you should utilize the maximum secure methodto protect theBonusPlan column from the access of unauthorized users. Which action will youperform to finish the task?

A. To finish the task, Transparent data encryption should be utilized.

B. To finish the task, the NTFS file system security should be utilized and the access ofthe database filesshould be limited to the SQL Server 2008 Service account.

C. To finish the task, the Trustworthy option for the database should be utilized.

D. To finish the task, the Advanced Encryption Standard encryption should be utilizedon all columns inthedatabase.

Answer: B

www.70-450.com Q: 6 QUESTION 14


- 8 -

You are a professional level SQL Sever 2008 Database Administrator in aninternational corporationnamed Wiikigo. You are experienced in managing databases in an enterprise-levelorganization,optimizing and sustaining the database life cycle. In the company, your job is toimplement solutions onsecurity, troubleshooting, deployment and optimization. A SQL Server 2008infrastructure is managed byyou. A database is included by an instance. And a large table named OrderDetails isincluded by thedatabase. Only DML statements on the last three months data are executed by theapplication queries.Administrative audits are managed monthly on data which is longer than fourmonths. The performanceproblems listed below are found by you in the database. The performance of theapplication queriesagainst the OrderDetail table is poor. It takes a long time to perform themaintenance tasks against thedatabase, index defragmentation is contained.The performance problems should be solved with on impact on the serverperformance. Which action willyou perform?

A. A database snapshot should be created for the OrderDetails table every four months.And then, thequeriesshould be changed to utilize the present snapshot.

B. An additional table named OrderDetailsHistory should be created for data older thanfour months. Andthen, the OrderDetails and OrderDetailsHistory tables should be partitioned in twoparts by utilizing the OrderDate column. At last, a SQL Server Agent job that runs everymonth shouldbecreated and the ALTER TABLE...SWITCH Transact-SQL statement should be utilizedto remove data that is longer than four months to the OrderDetailsHistory table.

C. An additional table named OrderDetailsHistory should be created for data longerthan four months. Andthen, a SQL Server Agent job that runs the following Transact-SQL statement every


- 9 -

month should be created. INSERT INTO OrderDetailsHistory SELECT * FROMOrderDetailsWHEREDATEDIFF(m,OrderDate,GETDATE())>4

D. An additional table named OrderDetailsHistory should be created for data older thanfour months. Andthen, the following Transact-SQL statement should be utilized. CREATE TRIGGERtrgMoveDataON OrderDetailsAFTER INSERT ASINSERT INTO OrderDetailsHistorySELECT * FROMOrderDetailsWHERE DATEDIFF(m,OrderDate,GETDATE())>4

Answer: B

www.70-450.com Q: 7 QUESTION 15You are a professional level SQL Sever 2008 Database Administrator in aninternational corporationnamed Wiikigo. You are experienced in managing databases in an enterprise-levelorganization,optimizing and sustaining the database life cycle. In the company, your job is toimplement solutions onsecurity, troubleshooting, deployment and optimization. SQL Server 2008 instancesat fivesites are managed by you. A mission-critical database is shared by the five sites.According to thebusiness requirements, the users at each site should be enabled to access and changedata on all siteswith minimal latency. In addition, data loss should be minimized if a server fails. Ahigh-availability solutionshould be designed, and the business requirements should be satisfied.Which action should be included in your solution?

A. Failover clustering should be included.

B. Asynchronous database mirroring without a witness server should be included.

C. Peer-to-Peer replication should be included.

D. Log shipping to servers at two of the sites to offer read-only replications of datashould be included.


- 10 -

Answer: C

www.70-450.com Q: 8 QUESTION 16You are a professional level SQL Sever 2008 Database Administrator in aninternational corporationnamed Wiikigo. You are experienced in managing databases in an enterprise-levelorganization,optimizing and sustaining the database life cycle. In the company, your job is toimplement solutions onsecurity, troubleshooting, deployment and optimization. A SQL Server 2008infrastructure is managed byyou. A corporate backup and recovery strategy that has to be validated is designed.You are required tomake sure that any single database can be restored successfully from a severefailure, and during therestoration, a backup data center should not be required in a differentlocation.Which three actions shouldbe contained by your solution? (Choose more than one)

A. All SQL Server instances should be installed on a failover cluster.

B. One list of all Windows logins and passwords should be sustained.

C. All backup media offsite should be stored.

D. SQL login accounts and credentials should be scripted.

E. The administrative processes and application access requirements should bedocumented.

Answer: A, D, E



- 11 -

named Wiikigo. You are experienced in managing databases in an enterprise-levelorganization,optimizing and sustaining the database life cycle. In the company, your job is toimplement solutions onsecurity, troubleshooting, deployment and optimization. A SQL Server 2008instance ismanaged by you. A new database should be deployed, and the capacityrequirements listed below shouldbe satisfied. There should be 275 GB for the database data file, while there shouldbe 50 GB for thetransaction log file There are six 100-GB disk drives which are accessible for thedatabase in the storagearray. The six disks are attached to a redundant array of independent disks (RAID)controller, and thecontroller supports RAID levels 0, 1, 5, and 10. Since you are the technical support,you are required tomaximize the write performance of the transaction log. In addition, you shouldprotect the database andtransaction log files, when a drive fails. The storage system should be designed.From the following fourstorage configuration, which one should you utilize?

A. A single RAID 5 volume should be utilized.

B. A RAID 1 volume and a RAID 5 volume should be utilized.

C. A single RAID 10 volume should be utilized.

D. A RAID 0 volume and a RAID 5 volume should be utilized.

Answer: B

www.70-450.com Q: 10 QUESTION 18You are a professional level SQL Sever 2008 Database Administrator in aninternational corporationnamed Wiikigo. You are experienced in managing databases in an enterprise-levelorganization,optimizing and sustaining the database life cycle. In the company, your job is toimplement solutions on


- 12 -

security, troubleshooting, deployment and optimization. SQL Server 2008 serverson a single site aremanaged by you. Two SQL Server 2008 instances are contained by the site. One isan Enterprise Editionserver, and it is on a server that has a redundant array of independent disks(RAID) 10 disk system The other is a Standard Edition server, and it is on a serverthat has a RAID 5 disksystem. A single application is hosted by each instance. A high-availability solutionshould berecommended for the site to satisfy the business requirements listed below.First, the solution can be implemented on the current systems.Secondly, the database is available with least downtime.Thirdly, Data loss should be cut to the least.Fourthly, the existing system should be with least impact.Which solutions should be utilized to finish the task? (Choose more than one)

A. To finish the task, Log shipping should be utilized.

B. To finish the task, Failover clustering should be utilized.

C. To finish the task, Replication should be utilized.

D. To finish the task, Database snapshot should be utilized.

Answer: A, C

insidetests 70-450 exam - ms sql serv 08

Documents