Microsoft-PRO Designing, Optimizing, and Maintaining a Database Administrative Solution Using Microsoft SQL Server 2008 Set 3

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.)

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.)

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?

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.)

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.)

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.

You administer SQL Server 2008 instances at three sites. All sites share a mission-critical database. The business requirements specify that users at each site must be able to perform the following tasks: Access and modify data on all sites with minimal latency. Minimize data loss in the event of a server failure. You need to provide a high-availability solution that meets the business requirements. Which solution should you implement?

You administer a SQL Server 2008 infrastructure. An instance hosts a business-critical database that must be continuously available to the users without data loss. The database includes Filestream data. You need to implement a high-availability solution for the site. Which solution should you use?

You administer four SQL Server 2008 instances. Each instance hosts a single database application. You plan to migrate all four instances to a new SQL Server failover cluster. The four instances are configured as shown in the following table. Instance Name Configuration: Instance 1 8 processors that have 16-GB RAM Instance 2 4 processors that have 8-GB RAM Instance 3 2 processors that have 16-GB RAM Instance 4 4 Processors that have 8-GB RAM The four instances are fully optimized and have no spare CPU cycles or extra memory. The new cluster will host all four databases on a single virtual cluster IP address. You need to ensure that the new cluster is configured to handle the workload of all the database applications by using the minimum amount of hardware resources. Which cluster configuration should you use?

You administer a SQL Server 2008 instance. You plan to design a high-availability solution by using database mirroring. The current application includes programming logic that will retry transactions if a failover occurs. You need to configure a strategy that will provide the minimum amount of latency for committed transactions. What should you do?