Your company has a server named SQL1 that runs SQL Server 2005 Enterpilse Edition. SQL1 has 2 GB of RAM, 1.6 GB of which are used by the default SQL Server database engine instance. The average data growth of all databases combined is 100 MB a month. Users state that report execution times are increasing. You want to assess whether more RAM is needed. You need to use System Monitor to create a counter log that will help you decide whether to add RAM. Which performance object should you add to the counter log?

The sales department and the finance department are located in different buildings. The two departments are connected by a 2 megabit network. Both departments use SQL Server 2005. The sales department records all sales activities in a database named DB1. DB1 contains 380 tables and has a size of 10 GB. The finance department has read-only access to four tables in DB1 for reporting purposes. The finance department runs queries throughout the day and needs the data to be as up to date as possible. Users in the finance department tell you that report execution is slow. To optimize network capacity, your department decides to provide copies of these four tables to the finance department. You need to provide copies of these tables. You want to achieve this goal by using the copy method that uses the least amount of network bandwidth. What should you do?

You are creating an HTTP endpoint that will be used to provide customer data to external applications. Your SQL Server 2005 computer is named SQL1 . You create a stored procedure named dbo.usp_GetPersonData to retrieve the data in the AdventureWorks database. You create the endpoint by using the following code.

CREATE ENDPOINT SQLEP_AWPersons
AS HTTP
(PATH = ‘/AWpersons’,
AUTHENTICATION = (INTEGRATED),
PORTS = (CLEAR),
SITE = ‘SQL1’)
FOR SOAP
(WEBMETHOD ‘PersonData’
(NAME=’AdventureWorks .dbo .usp_GetPersonData’),
BATCHES = DISABLED,
WSDL = DEFAULT,
DATABASE = ‘AdventureWorks’,
NAMESPACE = ‘http://Adventure-Works/Persons’)

The first users to connect to the endpoint tell you that they do not get any data. You connect to the endpoint and discover that it is not responding.

You need to modify the endpoint so that data is returned as expected. What should you do?

Your company uses a mission-critical database named DB1, which is located on a server named SOL1. You want to mirror DB1 on a second server named SQL2. Before you can establish database mirroring, you need to prepare DB1 and the servers for mirroring. Which three actions should you perform? (Each correct answer presents part of the solution. Choose three.)

Your company uses SQL Server 2005. You are implementing a series or views that are used in ad hoc queries. The views are used to enrorce your companys security policy or abstracting data. Some or these views perform slowly. You create indexes on those views to increase performance, while still maintaining the companys security policy. One or the views returns the current date as one or the columns. The view returns the current date by using the GETDATE() function. This view does not allow you to create an index. You need to create an index on the view. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)

Your company uses a SQL Server 2005 database. This database contains a trigger named trg_lnsertOrders, which fires when order data is inserted into the Orders table. The trigger is responsible for ensuring that a customer exists in the Customers table before data is inserted into the Orders table. You need to configure the trigger to prevent it from firing during the data import process. You must accomplish this goal while using the least amount of administrative effort. Which two Transact-SQL statements can you use to achieve this goal? (Each correct answer presents a complete solution. Choose two.)

You are responsible for implementing maintenance jobs on a SQL Server 2005 database server. Certain jobs run every Sunday and other jobs run at the beginning of every month. You need to schedule the jobs in the way that uses the least amount of administrative effort. What should you do?

A support engineer reports that inserting new sales transactions in a SQL Server 2005 database results in an error. You investigate the error. You discover that in one or the databases, a developer has accidentally deleted some data in a table that is critical for transaction processing. The database uses the full recovery model. You need to restore the table. You need to achieve this goal without affecting the availability or other data in the database. What should you do?

You configure a new SQL Server 2005 computer to use TCP/IP with all default settings. Your corporate policy requires that each server use a firewall. You find that you can connect to the SQL Server instance from the local computer. However, client computers cannot connect to the SQL Server instance. You need to identity the most likely cause of the connection issues. What should you do first?

Your company has two SQL Server 2005 computers named SQL1 and SQL2. A user in your company named Eric writes many ad hoc queries against the company databases. Eric has access to the Customer database on SQL1. He does not have access to the Sales database on SQL2. You need to ensure that Eric can write queries that join information from both servers. What should you do first?