You administer a SQL Server 2008 instance.
The instance is one of three servers in a peer-to-peer transactional replication topology. The publication
contains a table named Orders. The Orders table contains 200 GB of data across multiple partitions.
A batch process bulk loads 10 GB of data to the Orders table periodically.
You need to design a replication strategy to minimize latency.
What should you do?

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?

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 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 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. The largest table in a database is partitioned into four equal-sized parts.
The first two partitions are seldom queried, while the third partition is regularly queried by several reports.
Meanwhile, the fourth partition is heavily queried and regularly modified.
The disk space of the table should be cut to the least and the query performance should be with the least impact.
What will you do? (Choose more than one.)

You administer a SQL Server 2008 infrastructure.
You plan to design an infrastructure for a new application.
The application has the following requirements:
Users can connect to an instance named SQLSERVER1.
SQLSERVER1 is linked to a server named SQLSERVER2.
SQLSERVER1 and SQLSERVER2 run on different computers.
The SQL Server instances use only Windows authentication.
You need to configure the infrastructure to ensure that the distributed queries are executed in the Windows
security context of the login.
Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)

You administer a SQL Server 2008 infrastructure.
You partition the largest table in a database into four equal-sized parts. The first two partitions are rarely queried. The third partition is frequently queried by several reports. The last partition is heavily queried and frequently modified.
You need to minimize the disk space of the table and ensure that this has minimum effect on the query performance.
Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)

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 administer a SQL Server 2008 infrastructure.
You discover that an instance experiences performance degradation for the following reasons:
Excessive CPU usage
Server processes paging
Deadlocks
You need to design a monitoring solution that can provide data, including detailed deadlock information, to
monitor and troubleshoot performance issues. You want to achieve this goal by using the minimum amount
of administrative effort.
What tool should you use?

You administer a SQL Server 2008 instance. The instance is using a 32-bit version on a Windows Server
2008 64-bit server. The awe enabled option is enabled.
The instance will experience a predictable increase in query activity.
You plan to ascertain the appropriate time when the migration of the databases to a 64-bit SQL Server
2008 server on the same hardware is beneficial.
You need to identify a data collector type that provides the appropriate information.
Which collector should you use?