You work as a database administrator at ABC.com. ABC.com is using SQL Server 2008.
ABC.com has laboratory for analyzing new products. The results of the analysis are stored on a
SQL Server 2008 server named ABC_LAB that is in the laboratory. You have received instructions
ensure thatdata on ABC_LAB is replicated to the production SQL Server 2008 servers outside the
laboratoryso that the data can be used for reporting.
How would you accomplish this task? (Each correct answer forms part of the solution. Choose
TWO.)

You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008
instance. The SQL Server 2008 instance contains a database named ABC_DB.You add a stored
procedure named RecTable to the database using the following code (NOTE: Line numbers are
included for reference only):
01 CREATE PROCEDURE Prods.RecTable
02
03 AS
04
05 IF OBJECT_ID(‘Prods.ManuTeam’) IS NOT NULL
06 DROP TABLE Prods.ManuTeam;
07
08 CREATE TABLE Prods.ManuTeam(
09ManuIdint PRIMARY KEY CLUSTERED,
10TeamSizesmallint,
11 TeamLeadernvarchar(150);
12 );
13
14 GO
15
However, when a ABC.com usernamed Mia Hamm tests the RecTable stored procedure she
receive an error stating CREATE TABLE permission denied in ABC_DB.
How would you rectify this problem without giving Mia Hamm more permissions than she
requires?

You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008 R2
instance that contains a database named ABC_DB1.
The ABC.com management would like the database security to be configured in such a way that
the log files exclusively contain data regarding successful and unsuccessful logon attempts.
You have decided to make use of the sp_configureTransact-SQL statement.
Which of the following actions should you take?

You work as a SQL Server 2008 database administrator at ABC.com. ABC.com is using a SQL
Server 2008 Analysis Services (SSAS) instance.
You have received instruction to deploy a data mining structure that has the Clustering mining
model. You have been instructed to make sure that cluster membership is assigned by minimizing
the differences among items in a cluster while maximizing the distance between clusters. Your
solution should also allow for the entire data set to be loaded in one clustering pass.
Which of the following actions should you take?

You work as a database administrator at ABC.com. ABC.com makes use of SQL Server 2008 R2.
ABC.com has a database that contains a table, named ABC_Item, which has numerous columns
including Name, ItemLine, ListPrice, Class, and Style. There is a standard query for the ABC_Item
table that searches for information based on these columns. There is also another standard query
specifying the Name column for the ORDER BY clause.
While in the process of reducing storage for the indexes, you are informed that the most common
searched to ABC_Item should be retained. To achieve this, you want to configure an index for the
Name column.
Which of the following actions should you take?

You work as a SQL Server 2008 database administrator at ABC.com. ABC.com has an instance
with an On-Line Analytical Processing (OLAP) database and a dimension table named ABC_TST.
ABC_TST is updated continuously. You want to reduce storage space used by the ABC_TST
table by compressing any redundant data in the table.
How would you accomplish this?

You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008
R2instance that contains a database named ABC_DB1.
ABC_DB1 contains a table named ABC_Prod that has a clustered primary key called
ABC_ProdID. ABC_DB1 also contains a solitary XML column, called ABC_ProdXml, which stores
XML data. ABC_ProdXmlis configured with an XML index.
After creating a new column called ABC_ProdNamein the Production table, you are instructed to
make sure that ABC_ProdNameis added to the primary key.
To achieve this, you have to alter the primary key.
Which of the following actions should you take prior to altering the primary key?

You work as a database administrator at ABC.com.ABC.com is using a SQL Server 2008 R2
instance that contains a database named ABC_DB1.
The ABC_DB1 database contains a SalesHistory table that has two table partitions. All information
in the SalesHistory table is located in the one partition, while the other partition has no information.

You then create a process to transfer information from the partition, which includes information for
the oldest month of the Sales table, to the proper partition of the SalesHistory table.
You have received instructions to ensure that the process is capable of running at double capacity
at the end of each month.
Which of the following actions should you take?

You work as a database administrator at ABC.com. ABC.com has a database named ABC_DB.
ABC_DB has a table named ABC_Prod that has over 100,000 rows. Several indexes that have
been created on the ABC_Prod tableare fragmented.
How would you deframent the indexes without causing too much unavailability of the table?

You work as a database administrator at ABC.com. You want to know how many users are
connected to a SQL Server 2008 instance.
What steps should you take?