PrepAway - Latest Free Exam Questions & Answers

Category: 70-431

Exam 70-431: TS: Microsoft SQL Server 2005 – Implementation and Maintenance

What should you do to identify any missing nonclustered indexes. You want the existing structures remain intac

You are employed as a database administrator at Domain.com. Your duties at Domain.com include administrating a SQL Server 2005 database computer named Certkiller -DB04. None of the databases on Certkiller -DB04 contain any views. You want to tune a database named CK_Stats that is hosted on Certkiller -DB04. You to accomplish this task using the Database Engine Tuning Advisor (DTA). Certkiller -DB04 contains a workload file that is appropriate for DT

How can you achieve this goal to connect to Certkiller -DB01 to detect the problem?

You are employed as a database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. Your duties include administrating a SQL Server 2005 database server named Certkiller -DB01. Certkiller -DB01 is configured for SQL Server and Windows Authentication mode.
You make use of Windows Authentication; therefore you have sysadmin privileges on Certkiller -DB01. You have received several reports from clients who work with Certkiller -DB01 stating that all applications that use Certkiller -DB01 stopped responding. You have received instruction from the CIO to investigate this error.
You check in Task Manager and detects that the CPU usage is at 100 percent. You attempt to connect to Certkiller -DB01 by using SQL Server Management Studio. However, Certkiller -DB01 does not respond. You are required to connect to Certkiller -DB01 to detect the problem.
How can you achieve this goal? (Choose all that apply)

How can you retrieve data concerning a user who is currently logged in on the system?

Domain.com has employed you as a database administrator. Your duties at Domain.com encompass administrating a SQL Server computer named Certkiller -DB03.
You have received instruction from the CIO to retrieve data concerning a user who is currently logged in on the system. You create a function that returns scalar information about the activity time for a particular user. How can you accomplish this goal? (Choose all that apply)

What should you do next on Certkiller -SR02 to configure Certkiller -DB01 and Certkiller -DB02 as mirrored ser

You work as the database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. All servers on the Domain.com network run Windows Server2003. The Domain.com network contains a single SQL Server 2005 database server named Certkiller -DB01. Certkiller -DB01 hosts a database named CK_Sales that stores sales data for the company. The CK_Sales database is configured to use the full recovery model.
You want to provide zero down time and high availability for the CK_Sales database. You decide to add a new database server which will be named Certkiller -DB02 on the Domain.com network. You want to configure Certkiller -DB01 and Certkiller -DB02 as mirrored servers. You perform a full backup of the CK_Sales database on Certkiller -DB01.
What should you do next on Certkiller -SR02?

Which two article properties should you change to change the article properties so that all indexes of Certkil

Exhibit:

Domain.com has two SQL Server 2005 computers named SQL1 and SQL2. A database named DB1 is located on SQL1. DB1 contains a table named Certkiller 4. Certkiller 4 is replicated to a database named DB1Repl, which is located on SQL2. Full-Text Search is not being used. Users report that the queries they run against Certkiller 4 in DB1Repl are very slow. You investigate and discover that only the clustered index of Certkiller 4 is replicated. All other indexes in DB1Repl are missing. You examine the Certkiller 4 article properties. The current Certkiller 4 article properties are shown in the exhibit. You need to change the article properties so that all indexes of Certkiller 4 in DB1 are replicated when the subscription is reinitialized. Which two article properties should you change? (Each correct answer presents part of the solution. Choose two.)

Which two actions should you perform to modify the Transact-SQL statement?

Exhibit:

You use a SQL Server 2005 database that contains a table named Customers. The Customers table contains the columns displayed in the exhibit. You create a view named v_Customer by using the following Transact-SQL statement. CREATE VIEW v_CustomerASSELECT CustomerID, CompanyName, FirstName, LastName, PictureFROM Customers You want to create an index on the v_Customer view. Prior to creating the index, you need to modify the Transact-SQL statement. Which two actions should you perform? (Each correct answer presents part of the solution.Choose two.)

Which Transact-SQL batch should you use to ensure that the database is always in a consistent state?

Your data warehouse includes three separate SQL Server 2005 tables for storing customer data. Each table is used to horizontally partition data that is migrated from a SQL Server 2000 installation to SQL Server 2005. Data is partitioned as shown in the following table. Table nameStores customer surnamebeginning with letterCustomer1A C HCustomer2I C PCustomer3Q C Z The database does not allow customers to opt-out of e-mail marketing offers.

You need to add a new OptOutFlag column to each of the customer tables. You must ensure that this column is added to either all three customer tables or none of them. You must also ensure that the database is always in a consistent state.

exhibit Which Transact-SQL batch should you use?

Which Transact-SQL batch should you use to ensure that the database is always in a consistent state?

Exhibit:

Domain.com is storing its customer data in a data warehouse.It includes three separate SQL Server 2005 tables for storing customer data. Each table is used to horizontally partition data that is migrated from a SQL Server 2000 installation to SQL Server 2005. Data is partitioned as shown in the table displayed in the exhibit. The database does not allow customers to opt-out of e-mail marketing offers. You need to add a new OptOutFlag column to each of the customer tables. You must ensure that this column is added to either all three customer tables or none of them. You must also ensure that the database is always in a consistent state.
Which Transact-SQL batch should you use?

Which Transact-SQL batch should you use to ensure the following?

You work as a database administrator at Domain.com. Domain.com stores client information in a data warehouse. The data warehouse contains three separate SQL Server 2005 tables for storing client information.
The information stored in three separate tables named CK_Staff1, CK_Staff 2 and CK_Staff 3. The tables are used to horizontally partition information that is migrated from a SQL Server 2000 installation to a SQL Server 2005. The information is partitioned as shown in the table displayed below:
Table
Stores employee surnames
beginning in letters

CK_Staff1 A – J

CK_Staff2 K – P

CK_Staff3 R – Z
The database is configured in order that Domain.com employees are unable to opt-out of e-mail notifications to their personal email addresses. You have received instruction from the CIO to add a new OptOutFlag column to every CK_Staff table.
You need to ensure the following:
1. The column is added to either all three CK_Staff tables or none of them.
2. The database is always in a consistent state.

exhibit Which Transact-SQL batch should you use?

What should you do to meet the following criteria?

You work as the database administrator for an IT training company named Domain.com. All servers on the Domain.com network run Windows Server 2003 and all database servers run SQL Server 2005. The Domain.com network contains a database server named Certkiller -DB01 B01. Certkiller -DB01 hosts a database named CK_Sales that stores sales data for the company. The tables in the CK_Sales database are shown in the following database diagram.

Domain.com decides to switch to commission-based salaries for its Sales staff. You need to generate information that Rory Allen, the manager of the Sales department, will use to calculate the commission for each sales person. Your solution must meet the following criteria:
1. You must be able to use multiple SELECT statements to produce the result set.
2. The result set must be returned as a table for which the PRIMARY KEY constraint is defined.
3. Rory Allen must be able to reference the result set in the FROM clause of a SELECT statement.

exhibit What should you do?


Page 1 of 5012345...102030...Last »