The 70-450 Exam Practice Questions and Answers are ideal for the aspring candiates to grab exceptional grades in Microsoft 70-450 Exam! The 70-450 Questions and Answers are developed using the latest updated course content and all the answers are verified to ensure phenoment preparation for the actual 70-450 Exam!
Vendor: Microsoft
Exam Code: 70-450
Exam Name: PRO: Designing, Optimizing and Maintaining a Database Administrative Solution Using Microsoft SQL Server 2008
Keywords: 70-450 Exam Dumps,70-450 Practice Tests,70-450 Practice Exams,70-450 Exam Questions,70-450 PDF,70-450 VCE Free,70-450 Book,70-450 E-Book,70-450 Study Guide,70-450 Braindump,70-450 Prep Guide
Microsoft 70-450 Dumps VCE and 70-450 Dumps PDF Download: http://www.braindump2go.com/70-450.html
QUESTION 71
You are a professional level SQL Sever 2008 Database Administrator.
The security requirements should be designed for a new database application.
A code segment is utilized by the application, and the code segment includes the following components:
– A method that the registry is accessed on the SQL Server
– A method that the file system is accessed on a network file server
– A class definition that public static fields are utilized
– SQL CLR integration is utilized by the code segment
– The code segment is implemented as a single assembly.
Since you are the technical support, you are required to make sure that the application should be
successfully deployed to the instance.
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 then the
assembly should be registered by utilizing the regasm.exe utility before deployment.
D. All public static fields should be replaced with public static read-only fields. And then the EXTERNAL_ACCESS code access security should be utilized for the assembly.
Answer: D
Explanation:
Creates a managed application module that contains class metadata and managed code as an object in an instance of SOL Server. By referencing this module, common language runtime (CLR) functions, stored procedures, triggers, user-defined aggregates, and user-defined types can be created in the database.
PERMISSION_SET { SAFE | EXTERNAL_ACCESS I UNSAFE}
Specifies a set of code access permissions that are granted to the assembly when it is accessed by SOL Server. If not specified, SAFE is applied as the default.
We recommend using SAFE. SAFE is the most restrictive permission set. Code executed by an assembly with SAFE permissions cannot access external system resources such as files, the network, environment variables, or the registry.
EXTERNAL_ACCESS enables assemblies to access certain external system resources such as files, networks, environmental variables, and the registry.
QUESTION 72
You are a professional level SQL Sever 2008 Database Administrator.
The security requirements should be designed for a new database application, and the application will be deployed to the instance.
A table is consisted by the new database, and the table is created by utilizing the following code segment:
TABLE EmpBonusPlan
Since you are the technical support, you should utilize the maximum secure method to protect the BonusPlan column from the access of unauthorized users.
Which action will you perform 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 of the
database files should 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 utilized on all
columns in the database.
Answer: B
Explanation:
The column BonusFIn is of type FILESTFIEAM this means that the data of this column is saved outside of the database on the filesystem. So, you have to protect those files by the NTFS security.
QUESTION 73
You are a professional level SQL Sever 2008 Database Administrator.
A database is included by an instance.
And a large table named OrderDetails is included by the database.
Only DML statements on the last three months data are executed by the application queries. Administrative audits are managed monthly on data which is longer than four months.
The performance problems listed below are found by you in the database .
The performance of the application queries against the OrderDetail table is poor. It takes a long time to perform the maintenance tasks against the database, index defragmentation is contained.
The performance problems should be solved with on impact on the server performance.
Which action will you perform?
A. A database snapshot should be created for the OrderDetails table every four months.
And then, the queries should be changed to utilize the present snapshot.
B. An additional table named OrderDetailsHistory should be created for data older than four
months.
And then, the OrderDetails and OrderDetailsHistory tables should be partitioned in two parts
by utilizing the OrderDate column.
At last, a SQL Server Agent job that runs every month should be created and the ALTER
TABLE…SWITCH Transact-SQL statement should be utilized to remove data that is longer
than four months to the OrderDetailsHistory table.
C. An additional table named OrderDetailsHistory should be created for data longer than four
months.
And then, a SQL Server Agent job that runs the following Transact-SQL statement every
month should be created.
INSERT INTO OrderDetailsHistory SELECT * FROM
OrderDetailsWHERE DATEDIFF (m, OrderDate,GETDATE())>4
D. An additional table named OrderDetailsHistory should be created for data older than four
months.
And then, the following Transact-SQL statement should be utilized.
CREATE TRIGGER trgMoveDataON
OrderDetailsAFTER INSERT ASINSERT INTO OrderDetailsHistory SELECT * FROM
OrderDetailsWHERE DATEDIFF(m,OrderDate,GETDATE())>4
Answer: B
Explanation:
The easiest way to deal with history tables is to use partitioned tables and the switch command should be use to attach the old data to the history table.
QUESTION 74
You are a professional level SQL Sever 2008 Database Administrator.
A mission-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 on all sites with minimal latency.
In addition, data loss should be minimized if a server fails.
A high-availability solution should 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 data should be included.
Answer: C
Explanation:
As required from the business, the only way to deal with this request is the Peer-to-Peer replication as by default the databases are synchronized continuously on each site.
QUESTION 75
You are a professional level SQL Sever 2008 Database Administrator in an international corporation named Wiikigo.
You are experienced in managing databases in an enterprise-level organization,optimizing and sustaining the database life cycle.
In the company, your job is to implement solutions on security, troubleshooting, deployment and optimization.
A SQL Server 2008 infrastructure is managed by you.
A corporate backup and recovery strategy that has to be validated is designed.
You are required to make sure that any single database can be restored successfully from a severe failure, and during the restoration, a backup data center should not be required in a different location.
Which three actions should be 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 be documented.
Answer: CDE
Explanation:
There isn’t enough information here to make any specific recommendations.
It’s just a best practices question.
One of the dumps suggests the answer is ADE, but I don’t think a cluster would help in this scenario. It’s hard to tell because the type of failure isn’t explained. Since a restore operation is needed, it seems like a disk failure.
QUESTION 76
You are a professional level SQL Sever 2008 Database Administrator.
A new database should be deployed, and the capacity requirements listed below should be satisfied. There should be 275 GB for the database data file, while there should be 50 GB for the transaction log file.
There are six 100-GB disk drives which are accessible for the database in the storage array.
The six disks are attached to a redundant array of independent disks (RAID) controller, and the controller supports RAID levels 0, 1, 5, and 10.
Since you are the technical support, you are required to maximize the write performance of the transaction log.
In addition, you should protect the database and transaction log files, when a drive fails.
The storage system should be designed.
From the following four storage 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
Explanation:
You have to
– “maximize the write performance of the transaction log.”
– “you should protect the database and transaction log files, when a drive fails”
To maximize the per1 of the transaction log and have it protected, for this, you should use the raid 1, because it is a disk mirror and the size is 50GB, the half of the 100GB, but you need two disks for this. Then it will only remains four disks, but you can’t have RAID 1 for the data files as the data file is 275GB. So, RAID 5 is the only solution.
QUESTION 77
You are a professional level SQL Sever 2008 Database Administrator.
Two SQL Server 2008 instances are contained by the site. One is an Enterprise Edition server, 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 server that has a RAID 5 disk system.
A single application is hosted by each instance.
A high-availability solution should be recommended 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: AC
Explanation:
Failover clustering is not possible, because the two system are not on the same array of disk and furthermore, you have to implement it on the current system.
Database snapshot is not one HA solution, because it is stored on the same server.
Replication and Log shipping are both answering to the four requirements.
QUESTION 78
You are a professional level SQL Sever 2008 Database Administrator in an international corporation named Wiikigo.
There is a team of database administrators in your company.
SQL Server 2008 Integration Services (SSIS) packages are created on the test server in a shared project by a team of application developers.
A fixed cache file is required by one of the packages.
The company will deploy the packages to the production server on completion of development. The production server is available only to the database administrators.
Since you are the technical support, you need to confirm that the project can be deployed successfully to the production server by the application developers.
Which action will you perform to achieve the goal?
A. To achieve the goal, the Import and Export Wizard should be utilized to save packages.
B. To achieve the goal, a direct package configuration should be created for each package.
C. To achieve the goal, a deployment utility should be created for the SSIS project.
D. To achieve the goal, an indirect package configuration should be created for all packages.
Answer: C
Explanation:
The first step in deploying packages is to create a deployment utility for an Integration Services project. The deployment utility is a folder that contains the files you need to deploy the packages in an Integration Services project on a different server. The deployment utility is created on the computer on which the Integration Services project is stored. You create a package deployment utility for an Integration Services project by first configuring the build process to create a deployment utility, and then building the project. When you build the project, all packages and package configurations in the project are automatically included. To deploy additional files such as a Fteadme file with the project, place the files in the Miscellaneous folder of the Integration Services project. When the project is built, these files are also automatically included.
You can configure each project deployment differently.
Before you build the project and create the package deployment utility, you can set the properties on the deployment utility to customize the way the packages in the project will be deployed.
For example, you can specify whether package configurations can be updated when the project is deployed. To access the properties of an Integration Services project, right-click the project and click Properties.
The following table lists the deployment utility properties.
When you build an Integration Services project, a manifest file, qproject name>.SS|SDep|oymentlv1anifest.xmI, is created and added, together with copies of the project packages and package dependencies, to the bintlileployment folder in the project, or to the location specified in the DepIoymentOutputPath property. The manifest file lists the packages, the package configurations, and any miscellaneous files in the project.
The content of the deployment folder is refreshed every time that you build the project.
This means that any file saved to this folder that is not copied to the folder again by the build process will be deleted. For example, package configuration files saved to the deployment folders will be deleted.
QUESTION 79
You are a professional level SQL Sever 2008 Database Administrator in an international corporation named Wiikigo.
A 32-bit version is utilized by the instance on a Windows Server 2008 64-bit server.
You activate the awe enabled option.
A predictable increase in query activity will be experienced by the instance.
You are required to figure out the most proper and beneficial time to have the databases migrated to a 64-bit SQL Server 2008 server on the same hardware.
A data collector type that proper information is provided should be identified.
What kind of collector should you utilize?
A. You should utilize SQL Trace collector
B. You should utilize Performance Counters collector
C. You should utilize T-SQL Query collector
D. You should utilize Query Activity collector
Answer: B
Explanation:
Performance Counters collector is used as it contains information about the CPU, Memory and Disk usage of the SQL Server instance. As we need to know if it will o benefit to migrate to 64 bit version of SQL Server.
QUESTION 80
You are a professional level SQL Sever 2008 Database Administrator.
A database is hosted by the instance and the database is configured by utilizing high-safety mirroring operation mode along with a witness server. A memory failure is experienced by the witness server; therefore the server will be offline for five hours.
The mirroring strategy should be reset to cut the risk of database unavailability to the least level.
Which action should you perform to finish the task?
A. To finish the task, database mirroring should be paused.
B. To finish the task, the witness server should be moved from the mirroring session.
C. To finish the task, asynchronous operating mode should be utilized.
D. To finish the task, database mirroring should be removed.
Answer: B
Explanation:
Removing the witness will keep the mirroring session, but without automatic failover
You can remove the witness from a session if you are connected to the current principal server. To remove the witness:
1. Connect to the principal server instance and, in the Object Explorer pane, click the server name to expand the server tree.
2. Expand Databases, and select the database whose witness you want to remove.
3. Flight-click the database, select Tasks, and then click Mirror.
This opens the Mirroring page of the Database Properties dialog box.
4. To remove the witness, delete its server network address from the Witness field.
Note lf you switch from high-safety mode with automatic failover to high-performance mode, the Witness field is automatically cleared.
Braindump2go is one of the Leading 70-450 Exam Preparation Material Providers Around the World! We Offer 100% Money Back Guarantee on All Products! Feel Free In Downloading Our New Released 70-450 Real Exam Questions!
