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 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 server has only the SQL Server service running. All other SQL
Services are disabled. You have a user named DBReportsUser. You need to restrict
DBReportsUser to login only between 17:00 hours and 22:00 hours. You want to achieve
this goal by using the minimum amount of administrative effort and the least impact on the
Server security. What should you do? (More than one answer choice may achieve the goal.
Select the BEST answer.)

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

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

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

You administer a SQL Server 2008 infrastructure. You plan to upgrade a Microsoft SQL
Server 2005 instance to SQL Server 2008 by using an in-place upgrade. All user databases
use the simple recovery model and have the PAGE_VERIFY option turned off. You need to
ensure that the database engine can identify I/O path errors and memory corruptions for the
data in tables. Which two actions should you perform before the upgrade? (Each correct

answer presents part of the solution. Choose two.)

You deploy a new database application to a SQL Server 2008 instance on a server that has
4 processor cores and 64 GB of RAM. The application uses partitioned tables. During
testing, deadlocks occasionally occur on queries that are attempting to read data. You find
out that the deadlocks are related to the table partitions. You need to ensure that the
application executes without deadlocks for the read queries. You also need to ensure that
dirty reads do not occur. What should you do? (More than one answer choice may achieve
the goal. Select the BEST answer.)

You administer a SQL Server 2008 infrastructure. A financial application uses several
instances across multiple servers. The majority of queries require distributed transactions
across all servers. The application administrator reports that the application experiences
excessive deadlocks. You need to design a data collector type that provides the appropriate
information for remote servers. What collector should you use?

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?