Real Latest 70-450 Exam Questions Updated By Official Microsoft Exam Center! Braindump2go Offers 70-450 Dumps sample questions for free download now! You also can visit our website, download our premium Microsoft 70-450 Exam Real Answers, 100% Exam Pass Guaranteed!
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 51
You are configuring a new SQL Server installation.
The SQL Server instance will be used for both online analytical processing (OLAP) and relational data. The data in the server is accessed frequently.
You can configure the services, startup type, and accounts used by the services that run on the server according to the following table:
You want to raise the level of security on the server.
You need to minimize the surface area for potential attacks on the server.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Change the Startup type property for all the services to Manual.
B. Change the Startup type property for all the services to Disabled.
C. Create a single non-administrative local user account and assign it to each of the services.
D. Create three different non-administrative local user accounts and assign them to each of the
services.
Answer: D
Explanation:
Answer is D. FromMicrosoft’sbestpracticespoint of view every service should have it’s own service account.
C. Is a correct answer you can select this option due the installation of an SQL 2008 instance
but is not recommended as bestpractice.
Official site: http://msdn.microsoft.com/en-us/library/cc281953.aspx
You can assign the same login account to all SQL Server services, or you can configure each service account individually.
You can also specify whether services start automatically, are started manually, or are disabled.
Microsoft recommends that you configure service accounts individually to provide least privileges for each service, where SQL Server services are granted the minimum permissions they need to complete their tasks. For more information
QUESTION 52
You administer a SQL Server 2008 instance that will host a new database application.
You plan to design the security requirements for the application.
Each application user has a unique login to the SQL Server 2008 server.
The application database contains stored procedures to execute stored procedures in the MSDB database.
The stored procedures in the MSDB database schedule SQLAgent jobs.
You need to ensure that the stored procedures in the MSDB database are executed by using the security context of the application user.
What should you do?
A. Add each user to the public role in the MSDB database.
B. Add each user to the db_dtsltduser database role in the MSDB database.
C. Configure the new database to use the TRUSTWORTHY option, and then add each user
to the MSDB database.
D. Configure the MSDB database to use the TRUSTWORTHY option, and then add each user
to the MSDB database.
Answer: C
Explanation:
If this ever actually comes up, you may want to re-think your application design.
Presumably, the application users are logging in to the app database. Since this database is trusted, and the users exist in MSDB, the users will be able to schedule jobs via the mechanism
in the question… which probably is not a good idea.
QUESTION 53
Your SQL Server 2008 database contains two tables named Customer and Order inside the Sales schema.
The Sales schema is defined as follows:
– It has a stored procedure named SetCustomerStatus that reads from Order and modifies Customer.
– It is owned by a user named SalesUser.
Another user named marketingUser wants to be able to call SetCustomerStatus and change the status of Customer.
You need to assign the least permission necessary to MarketingUser.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Set only Execute on SetCustomerStatus.
B. Set only Take Ownership on ustomer and Order.
C. Perform the following tasks:
– Set Select on Customer.
– Set Update on Order.
D. Perform the following tasks:
– Set Execute on SetCustomerStatus.
– Set Select on Customer.
– Set Update on Order.
Answer: A
Explanation:
A and B could be correct but Execute gives the least amount of rights to MarketingUser
QUESTION 54
Your SQL Server 2008 database contains two tables named Customer and Order inside the
Sales schema.
The Sales schema is defined as follows:
– It has a stored procedure named SetCustomerStatus that reads from
Order and modifies Customer.
– It is owned by a user named SalesUser.
Another user named MarketingUser wants to be able to call SetCustomerStatus and change
the status of Customer.
You need to assign the least permissions necessary to MarketingUser.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Set only Execute on SetCustomerStatus.
B. Set only Take Ownership on Customer and Order.
C. Perform the following tasks: Set Select on Customer.
D. Perform the following tasks: Set Execute on SetCustomerStatus.
Answer: A
QUESTION 55
You administer a SQL Server 2008 instance.
You plan to deploy multiple databases to the instance.
You design the security policy for the instance.
A stored procedure verifies the partition compression settings on each database deployed to the instance.
You plan to use a policy to execute the stored procedure.
You need to ensure that the policy is enforced by using Policy-Based Management Framework. What should you do?
A. Enable the nested triggers server configuration option.
B. Grant EXECUTE permissions on the stored procedure to the SQLAgent service account.
C. Grant EXECUTE permissions on the stored procedure to the
##MS_PolicyTsqlExecutionLogin## login.
D. Grant EXECUTE permissions on the stored procedure to the
##MS_PolicyEventProcessingLogin## login.
Answer: C
Explanation:
The correct answer should be A or C because the two options are necessary to the policy be enforced.
But as the question specifies the security policy context, the right answer would be C.
http://technet.microsoft.com/en-us/library/bb510667.aspx
“If the nested triggers server configuration option is disabled, On change: prevent will not work correctly. Policy-Based Management relies on DDL triggers to detect and roll back DDL operations that do not comply with policies that use this evaluation mode. Removing the Policy-Based Management DDL triggers or disabling nest triggers, will cause this evaluation mode to fail or perform unexpectedly.”
http://blogs.msdn.com/b/sqlpbm/archive/2008/07/03/executesql.aspx
“The proxy account is called ##MS_PolicyTsqlExecutionLogin##.
You’ll find this login under the “Logins” folder in Management Studio and master.sys.syslogins.
By default the login has very limited permissions.
You will likely need to extend the privileges of this user to accommodate the SQL you need to run under policy.
Note: Every permission you grant to ##MS_PolicyTsqlExecutionLogin## you are effectively granting that permission to all users in the Policy Administrator role.”
Other sources:
http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/da35c7fc-af9d-4f87-b5e7-8438a6c6ca24
QUESTION 56
Your organization has many servers that run SQL Server 2008.
You define a rule to ensure that all database servers have Database Mail disabled.
You need to be able to identify and resolve any violation of the rule by using the minimum
amount of administrative effort.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Define a scheduled task on each server.
B. Define a SQL job on each database server.
C. Define a maintenance plan on each database server.
D. Define a SQL Server policy and import it on each database server.
Answer: D
Explanation:
http://msdn.microsoft.com/en-us/library/bb510667.aspx
“Policy-Based Management would be helpful in resolving the issues presented in the following scenarios:
A company policy prohibits enabling Database Mail or SQL Mail.
A policy is created to check the server state of those two features.
An administrator compares the server state to the policy.
If the server state is out of compliance, the administrator chooses the Configure mode and the policy brings the server state into compliance.
QUESTION 57
You are designing a database solution by using SQL Server 2008.
The database has a large number of tables that have columns that contain sensitive data.
The sensitive data must be protected.
You need to choose an encryption strategy that has the minimum effect on the performance
of the server.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Use Bitlocker Encryption.
B. Use Cell-Level Encryption.
C. Use Encrypting File System.
D. Use Transparent Data Encryption (TDE).
Answer: D
Explanation:
http://msdn.microsoft.com/en-us/library/bb934049.aspx
QUESTION 58
You administer SQL Server 2008 servers on a single site.
The site contains the following two SQL Server 2008 instances:
– An Enterprise Edition server on a server that has a redundant array
of independent disks (RAID) 10 disk system
– A Standard Edition server on a server that has a RAID 5 disk system
Each instance hosts a single application.
You need to recommend a high-availability solution for the site to meet the following business requirements:
– The solution can be implemented on the existing systems.
– The database is available with minimal downtime.
– Data loss is minimal.
– There is minimal effect on the existing system.
What are two possible ways to achieve this goal? (Each correct answer presents a complete solution.
A. Replication
B. Log shipping
C. Failover clustering
D. Database snapshot
Answer: AB
Explanation:
Snapshots are not a high availability solution no matter how many times they are included in questions about high availability.
Failover Clustering would require the addition of a shared storage array.
That leaves log shipping and replication. Log shipping is the weakest of high availability
options in SQL Server 2008.
Peer-to-peer replication is viable.
QUESTION 59
You are a professional level SQL Sever 2008 Database Administrator.
An instance runs on a computer, and the computer has eight quad-core processors and 128-GB RAM.
The instance is utilized by four different applications. Not only a low number of CXPACKET waits but also a large number of lazy writer waits are experienced by the instance.
The performance of the instance should be optimized.
Which action will you perform to accomplish the goal?
A. To accomplish the goal, software non-uniform memory access (soft-NUMA) should be
configured.
B. To accomplish the goal, the Resource Governor should be configured.
C. To accomplish the goal, the Windows System Resource Manager should be configured.
D. To accomplish the goal, an increase should be configured in the maximum degree of
parallelism option.
Answer: A
Explanation:
The instance is utilized by four different applications. Not only a low number of CXPACKET
waits but also a large number of lazy writer waits are experienced by the instance with
software non-uniform memory access, we could set multiple threads to ifo see example below:
Consider the following example.
A computer with eight CPUs does not have hardware NUMA. Three soft-NUMA nodes are configured. Database Engine instance A is configured to use CPUs 1 through 4. A second instance of the Database Engine is installed and configured to use CPUs 5 through 8. The example can be visually represented as:
Claus 1 2 3 4 5 6 7 8
Soft:–NUMA <–N0—-><–N1–>< ——– N2 ——– >
SQL Server <insurance A ><instance B>
Instance A, which experiences significant IIO, now has two UO threads and two lazy writer threads, while instance B, which performs processor-intensive operations, has only one IIO thread and one lazy writer thread. Differing amounts of memory can be assigned to the instances, but unlike hardware NUMA, they both receive memory from the same operating system memory block and there is no memory-to-processor affinity.
QUESTION 60
You are a professional level SQL Sever 2008 Database Administrator.
A database is included by the instance, and a table named EmployeeData is contained by the database.
There is a column named Profile in the EmployeeData table.
Microsoft Office Word 2003 documents are stored in the column.
The space utilized by the Profile column should be cut to the least.
In addition, a reasonable performance should be maintained to retrieve the content of the column.
Which action 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 NTFS file system
compression should 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
Explanation:
Much of the data that is created every day is unstructured data, such as text documents, images, and videos. This unstructured data is often stored outside the database, separate from its structured data. This separation can cause data management complexities. Or, if the data is associated with structured storage, the file streaming capabilities and performance can be limited.
FILESTREAM integrates the SQL Server Database Engine with an NTFS file system by storing varbinary(max) binary large object (BLOB) data as files on the file system. Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data. Win32 file system interfaces provide streaming access to the data.
FILESTREAM uses the NT system cache for caching file data. This helps reduce any effect that FILESTREAM data might have on Database Engine performance. The SQL Server buffer pool is not used; therefore, this memory is available for query processing.
Note: FILESTREAM data is not encrypted even when transparent data encryption is enabled.
Want to be 70-450 certified? Using Braindump2go New Released 70-450 Exam Dumps Now! We Promise you a 100% Success Passing Exam 70-450 Or We will return your money back instantly!
