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?

Your company has two SQL Server 2005 computers named SQL1 and SQL2. Both servers take part in

replication. SQL1 is both the Publisher and its own Distributor of a publication named Pub1. Pub1 is the only

publication on SQL1, and SQL2 is the only Subscriber.

Your supervisor requests a status report about the replication latencies.

Using Replication Monitor on SQL1, you need to find out the current latencies between the Publisher and

Distributor as well as between the Distributor and Subscriber.

What should you do?

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 work at the regional sales office. You are responsible for importing and exporting data in SQL Server

2005 databases. The main office asks you to send them a text file that contains updated contact information for the

customers in your region.

The database administrator in the main office asks that the data be sorted by the StateProvince, Surname, and

FirstName columns.

You need to satisfy these requirements by using the least amount of effort.

What should you do?

You are responsible for importing data into SQL Server 2005 databases. Your department is starting to receive

text files that contain sales transactions from stores across the country.

Columns in the data are separated by semicolons.

You need to import the files into the sales database.

What should you do?

You are responsible for managing a SQL Server 2005 database. This database is updated with data that is

exported from another site. On a monthly basis, data is loaded into the database from a flat file by using the bcp

utility.

This operation should be minimally logged. However, users report that they receive error messages indicating that

the transaction log is full. You investigate and discover that the errors occur when the data is being imported.

You need to prevent this problem from occurring.

What should you do?

You use Service Broker to manage data requests between SQL Server 2005 databases.

You need to make changes to objects for an existing service. You want to ensure that no messages can be received

by the service until the changes are complete.

What should you do?

You are creating a Web-based application to manage data aggregation for reports. The application connects to

a SQL Server 2005 database named DataManager.

One page in the application has controls that execute stored procedures in a database named ReportingDatabase.

There is an existing Service Broker connection between the DataManager database and ReportingDatabase.

You want to add two new message types to the existing service. In each database, you create message types

named ProcessReport and SendResult.

You need to add the two new message types to the existing service.

What should you do first?

You work for a company that sells books. You are creating a report for a SQL Server 2005 database. The

report will list sales representatives and their total sales for the current month. The report must include only those

sales representatives who met their sales quota for the current month. The monthly sales quota is $2,000. The date

parameters are passed in variables named @FromDate and @ToDate.

You need to create the report so that it meets these requirements.

Which SQL query should you use?

A full backup of your database named DB1 is created automatically at midnight every day.

Differential backups of DB1 occur twice each day at 10:00 and at 16:00. A database snapshot is created every day

at noon.

A developer reports that he accidentally dropped the Pricelist table in DB1 at 12:30. The last update to Pricelist

occurred one week ago.

You need to recover the Pricelist table. You want to achieve this goal by using the minimum amount of

administrative effort. You must also minimize the amount of data that is lost.

What should you do?