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 Download: http://www.braindump2go.com/70-450.html
QUESTION 41
You are a professional level SQL Sever 2008 Database Administrator.
A thirdparty database application will be deployed to the instance.
Stored procedures are utilized by the application, and the procedures are developed by utilizing SQL CLR integration.
You should set the application to have the EXTERNAL_ACCESS code access security setting enabled.
Since you are the technical support of the company, you should deploy the application to the instance with no loss of functionality.
Which action will you perform first?
A. Readwrite non-static fields in the code should be replaced with static fields.
B. The peverify.exe PEVerify tool should be utilized to confirm whether the type-safety
requirements are satisfied by the code.
C. Read-only static fields in the code should be replaced with readwrite static fields.
D. The regasm.exe assembly registration tool should be utilized to have the assembly
registered on the server before deployment.
Answer: B
Explanation:
This is a vague question, but we can eliminate three answers just based on context.
PEVERIFY is used to determine if type-safety requirements are met.
From the first part of the guide, we know that read-only static fields are the only ones permitted, so that excludes A) and C) from being acceptable answers.
REGASM creates registry entries for COM functionality and isn’t related here.
———————————–
All EXTEFINAL_ACCESS assemblies must meet the following criteria:
– Static fields are not used to store information. Read-only static fields are allowed.
– PEVerify test is passed. The PE\.’erify tool {peverityexe), which checks that the MSIL code and associated metadata meet type safety requirements, is provided with the .NET Framework
– Synchronization, for example with the SynchronizationAttribute class, is not used.
– Finalizer methods are not used.
The following custom attributes are disallowed in EXTERNAL_ACCESS assemblies:
– System.ContextStaticAttribute
– System.l\llTAThreadAttribute
– System.Fiuntime.CompiIerServices.l\.-1ethodImplAttribute
– System.Runtime.CompiIerServicesCompilationHe|axationsAttribute
– System.Ftuntime.Flemoting.Contexts.ContextAttribute
– System.Runtime.Remotingflontexts.Synchronizationitiittribute
– System.Runtime.|nteropServices.DI||mportAttribute
– System.Security.Permissions.CodeAccessSecurityAttribute
– System.Security.SuppressUnmanagedCodeSecurityAttribute
– System.Security.Unverifiab|eCodeAttribute
– System.STAThreadAttribute
– System,ThreadStaticAttribute
QUESTION 42
You are designing the storage scheme for a new SQL Server 2008 database application
The application will primarily be used to query data.
The database will be initially populated with 4 terabytes of data stored in 20 tables.
The data is distributed evenly throughout all 20 tables.
Every night, between 100 GB and 300 GB of new data will be loaded into 4 database tables.
The remaining 16 tables will remain static. The average CPU utilization of the server where the application will reside is between 30 and 50 percent.
You need to ensure that the amount of disk space used is reduced.
You need to achieve this goal by ensuring minimum impact of performance on the server.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Use page-level data compression on the 4 tables that are loaded every night.
B. Use row-level data compression on the 4 tables that are loaded every night.
C. Use page-level data compression on the 16 tables that remain static.
D. Use row-level data compression on the 16 tables that remain static.
Answer: B
Explanation:
—Small Peter—(best answer logics)
I will go with answer b.
As the 4 tables get loaded every night will soon become the major factor of the storage capacity problem and page level compression will use a lot of CPU time. Thus row level compression every night for the 4 non-static tables would be a good compromise.
—D—- Answer
We should use row-level compression only on the 16 tables that remain static.
Row-level because it is the compression type that consumes less CPU (page-level compression consumes more CPU).
—A answer—
Full-recovery Model
Description
– 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 application or user error).
Work loss exposure
– Normally none.
– If the tail of the log is damaged, changes since the most recent log backup must be redone.
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.
QUESTION 43
You are the database administrator for a SQL Server that hosts an application defined as follows:
The database consists of 4 data files and one log file.
The physical server has 16 CPUs and 64 GB of memory.
Hardware non-uniform memory access (NUMA) is not available on this server platform.
Users of the application report that it is responding slowly.
You use SQL Server Profiler to find out that the I/O performance issue is related to the lazywriter process.
You need to improve the performance of the application by reducing the I/O performance issue.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Add additional RAM to the server.
B. Add additional data files to the database.
C. Configure four software non-uniform memory access (soft NUMA) nodes on the server.
D. Configure a CPU affinity mask on the server to limit the instance of SQL Server to four
CPUs
Answer: C
Explanation:
There are a number of terms in the question that don’t come up often.
The lazy writer is partially responsible for writing data from memory buffers to the disk.
It’s called ‘lazy’ because it only functions when the amount of free buffers becomes low.
Otherwise, it doesn’t do much.
NUMA organizes the way that CPUs access memory: The benefits of soft-NUMA include reducing I/O and lazy writer bottlenecks on computers with many CPUs and no hardware NUMA.
There is a single I/O thread and a single lazy writer thread for each NUMA node.
Depending on the usage of the database, these single threads may be a significant performance bottleneck. Configuring four soft-NUMA nodes provides four I/O threads and four lazy writer threads, which could increase performance.
CXPACKET waits occur due to problems with the timing of multiple processors.
It’s low here, so we know that’s not the issue.
QUESTION 44
You administer a SQL Server 2008 infrastructure.
An instance runs on a computer that has eight quad-core processors and 128-GB RAM.
Four different applications use the instance.
The instance experiences a low number of CXPACKET waits.
The instance also experiences a large number of lazy writer waits.
You need to optimize the performance of the instance.
What should you do?
A. Configure the Resource Governor.
B. Configure the Windows System Resource Manager.
C. Configure software non-uniform memory access (soft-NUMA).
D. Configure an increase in the maximum degree of parallelism option.
Answer: C
Explanation:
Failover clustering in SQL Server provides high–availability support for an entire SQL Server instance. For example, you can configure a SQL Server instance on one node of a failover cluster to fail over to any other node in the cluster during a hardware failure, operating system failure, or a planned upgrade.
A failover cluster is a combination of one or more nodes (servers) with two or more shared disks, known as a resource group. The combination of a resource group, along with its network name, and an internet protocol (IP) address that makes up the clustered application or server, is referred to as a failover cluster or a failover cluster instance. A SQL Server failover cluster appears on the network as if it were a single computer, but has functionality that provides failover from one node to another if the current node becomes unavailable. A failover cluster appears on the network as a normal application or single computer, but it has additional functionality that increases its availability.
Failover clustering has a new architecture and new work flow for all Setup scenarios in SQL Server 2008. The two options for installation are Integrated installation and AdvancedlEnterprise installation. Integrated installation creates and configures a single-node SQL Server failover cluster instance. Additional nodes are added using add node functionality in Setup. For example, for Integrated installation, you run Setup to create a single-node failover cluster. Then, you run Setup again for each node you want to add to the cluster. AdvancedlEnterprise installation consists of two steps. The Prepare step prepares all nodes of the failover cluster to be operational. Nodes are defined and prepared during this initial step. After you prepare the nodes, the Complete step is run on the active nods–the node that owns the shared disk–to complete the failover cluster instance and make it operational.
When to Use Failover Clustering
Use failover clustering to:
– Administer a failover cluster from any node in the clustered SQL Server configuration. For more information, see Installing a SQL Server 2008 Failover Cluster.
– Allow one failover cluster node to fail over to any other node in the failover cluster configuration.
For more information, see Installing a SQL Server 2008 Failover Cluster.
– Configure Analysis Services for failover clustering. For more information, see How to: install
Analysis Services on a failover cluster.
– Execute full-text queries by using the Microsoft Search segilce with failover clustering.
For more information, see Using SQL Server Tools with Failover stering.
QUESTION 45
You are designing a new SQL Server instance that will support a Web application.
The Web application is hosted on a Web farm that contains 32 nodes.
The server has 128 GB of memory and 16 processor cores. The application contains two databases and supports both online analytical processing (OLAP) and online transaction processing (OLTP) workloads.
When testing the application, you discover that some queries run extremely slow and some queries run very fast.
You need to ensure that the server processes database queries as fast as possible .
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Execute the following command on the server: exec_sp_configure ‘maximum degree of
parallelism’, 1
B. Execute the following command on the server: exec_sp_configure ‘maximum degree of
parallelism’, 8
C. Use SQL Profiler to identify queries that experience CXPACKET waits.
Add (OPTION MAXDOP 1) to each query
D. Use SQL Profiler to identify queries that experience CXPACKET waits.
Add (OPTION MAXDOP 8) to each query
Answer: C
Explanation:
The data collector is a component installed on a SOL Server server, running all the time or on a user-defined schedule, and collecting different sets of data. The data collector then stores the collected data in a relational database known as the management data warehouse.
The data collector is a core component of the data collection platform for SQL Server 2008 and the tools that are provided by SQL Server. The data collector provides one central point for data collection across your database servers and applications.
This collection point can obtain data from a variety of sources and is not limited solely to performance data, unlike SOL Trace.
The data collector enables you to adjust the scope ol data collection to suit your test and production environments. The data collector also uses a data warehouse, a relational database that enables you to manage the data you collect by setting different retention periods for your data. The data collector supports dynamic tuning for data collection and is extensible through its API.
QUESTION 46
You are the administrator of a Microsoft Windows Server 2003 computer.
Your company purchases a new enterprise sales application that runs SQL Server 2008.
The application uses stored procedures that include the EXECUTE AS clause and that use Microsoft Windows accounts.
The company’s written security policy states that all enterprise applications must run under the context of a service account that requires the minimum amount of privileges.
You need to configure the SQL Server service torun under the appropriate context for the installation of the new enterprise sales application.
Under which context should the SQL Server service run?
A. Under the context of the local system account
B. Under the context of the local service account
C. Under the context of the domain user account
D. Under the context of the local Administrator account
Answer: C
QUESTION 47
You work in a company which is named Wiikigo Corp.
The company uses SQL Server 2008.
You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
There is an Internet Information Services application.
This application will use anonymous access to access the SSAS instance.
You must make sure that the application can access the SSAS instance.
What should you do?
A. The Security\RequireClientAuthentication server configuration should be set to False
B. The Security\RequireClientAuthentication server configuration should be set to True.
C. The NTLM Security Support Provider Interface (SSPI) provider should be added to the Security\SecurityPackageList server configuration.
D. The Kerberos Security Support Provider Interface (SSPI) provider should be added to the Security\SecurityPackageList server configuration
Answer: A
QUESTION 48
You maintain a SQL Server 2008 instance.
You have an existing database maintenance plan that performs the following tasks:
You need to ensure that an e-mail message is sent to the operators when the maintenance plan fails to execute successfully.
What should you modify?
A. The instance.
B. The SQL Server service.
C. The SQL Server Agent job.
D. The database maintenance plan.
Answer: C
QUESTION 49
You administer two SQL Server 2008 instances named Instance1 and Instance2.
A database named Customers resides on Instance1.
You move the Customers database from Instance1 to Instance2.
A SQL Server login named User1 that has the password “111999” is used by a user to access the database on Instance1.
You create the same SQL Server login on Instance2.
The user attempts to access the Customers database on Instance2 by using the SQL Server login User1. However, the user receives an error message which indicates that the access to the Customers database is denied.
You need to ensure that User1 can access the Customers database.
Which Transact-SQL statements should you execute on Instance2?
A. USE Customers; ALTER USER User1 WITH LOGIN = User1;
B. USE Customers; ALTER USER User1 ENABLE;
C. USE Customers; ALTER LOGIN User1 WITH PASSWORD = ‘111999’ UNLOCK;
D. USE Customers; ALTER LOGIN User1 WITH DEFAULT_DATABASE = Customers;
Answer: A
QUESTION 50
You administer a SQL Server 2008 instance.
You configure a single Database Mail profile that is used to send reports to all the employees of your organization.
The sp_send_dbmail stored procedure is used to send a large volume of reports during business hours.
The reports to the manager take a long time to be delivered.
You need to ensure that the reports to the manager are sent as quickly as possible.
What should you do?
A. Modify the SMTP relay settings.
B. Create a separate Database Mail profile for the reports.
C. Configure the security settings of the Database Mail system.
D. Modify the parameters of the email that contains the reports.
Answer: B
Braindump2go New Released Premium 70-450 Exam Dumps Guarantee You a 100% Exam Success Or We Promise Full Money Back! Download Microsoft 70-450 Exam Dumps Full Version From Braindump2go Instantly!
