You work as DBA at Company.com. You administer two Windows Server 2003 computers named
CompanyA and CompanyB. You install SQL Server 2005 on both CompanyA and CompanyB to
host a new company database. CompanyA hosts a read-write copy of the company database in
which all changes are made. CompanyB subscribes to a publication on CompanyA and is only
used for reporting . A Windows domain administrator provides you with a domain user account
named SQLSRV to use as the security context for the SQL Server services. A password policy of
42 days exists in the Default Domain Group Policy object (GPO). You install the database on
CompanyA and CompanyB and configure replication. Everything works fine for six weeks, but
then all SQL Server services fail. You need to correct the problem. What should you do?
A. Ask the Windows domain administrator to grant the Log on as service right to the SQLSRV
domain user account.
B. Configure the SQLSRV domain user account with a new strong password. Configure the new
password in the properties of each SQL Server service that failed.
C. Create a local user account on CompanyA named CompanyA and a local user account on
CompanyB named CompanyB. Configure CompanyA and CompanyB to run under the context
of the appropriate local user account.
D. Ask the Windows domain administrator to grant the SQLSRV domain user account
membership in the Domain Admins group.
Answer: B
Question: 2
You are a database administrator of two SQL Server 2005 computers named CompanyA and
CompanyB. You have a Microsoft .NET application that has been modified so that it now
accesses a database on CompanyB in addition to CompanyA. You do not want the user
application to connect directly to CompanyB. You need to enable the data retrieval from
CompanyB while maintaining the ability to assign different permissions to different users who use
the .NET application. What should you do?
A. Change the .NET application to define a new server connection to CompanyB.
B. Configure a linked server on CompanyA to point to CompanyB.
C. Change the stored procedures called by the .NET application to include the OPENXML
command.
D. Configure a linked server on CompanyB to point to CompanyA.
Answer: B
Question: 3
You are a database administrator for Company.com. Your company uses a different company's
application that is based on SQL Server 2005 Standard Edition. The application executes a query
that uses an index query hint. The index query hint is not suitable for your environment, but you
cannot modify the query. You need to force the application to use a different query execution
plan. What should you do?
A. Create a plan guide for the query.
B. Clear the procedure cache.
C. Create a new covering index on the columns that the query uses.
D. Update the statistics for all of the indexes that the query uses.
Answer: A
Question: 4
one user database that holds sales transaction information. Users report that the queries and
stored procedures that they use every day are taking progressively longer to execute. You also
notice that the amount of free disk space on the SQL Server computer is decreasing. You need to
create a maintenance plan to correct the performance and storage problems. What are two
possible ways to achieve this goal? (Each correct answer presents a complete solution. Choose
two.)
A. In the SQL Server Maintenance Plan Wizard, use the Check Database Integrity option.
B. In the SQL Server Maintenance Plan Wizard, use the Reorganize Index option.
C. In the SQL Server Maintenance Plan Wizard, use the Shrink Database option.
D. In the SQL Server Maintenance Plan Wizard, use the Clean Up History option.
E. In the SQL Server Maintenance Plan Wizard, use the Execute SQL Server Agent Job option.
You are a database administrator for Company.com. Your SQL Server 2005 computer containsAnswer: B, C
Question: 5
You are a database administrator for Company.com. You are responsible for a SQL Server 2005
database that has several indexes. You need to write a stored procedure that checks the indexes
for fragmentation. Which Transact-SQL statement should you use?
A. DBCC INDEXDEFRAG
B. SELECT * FROM sys.dm_db_index_physical_stats
C. SELECT * FROM sys.indexes
D. DBCC DBREINDEX
Answer: B
Question: 6
You work as DBA at Company.com. You administer two SQL Server 2005 computers named
CompanyA and CompanyB. CompanyA and CompanyB contain a copy of a database named
Sales. The database is replicated between CompanyA and CompanyB by using transactional
replication. A full backup of each database is performed every night. Transaction log backups are
performed every hour. Replication latency is typically less than two minutes. One afternoon, the
Sales database on CompanyA becomes corrupted. You are unable to repair the database. The
Sales database on CompanyB is unaffected. You need to return the Sales database on
CompanyA to normal operation as quickly as possible. You must ensure a minimum loss of data
and minimal impact to users of either server. What should you do?
A. Perform a full database backup on CompanyB. Restore the backup to CompanyA.
B. Restore the most recent full database backup and all transaction logs made since the full
backup was made.
C. Restore only the most recent transaction log backup.
D. Detach the Sales database on CompanyB. Copy the database file to CompanyA, and attach
the database on both servers.
Answer: A
Question: 7
You are a database administrator for Company.com. The company runs a popular databasedriven
Web site against a SQL Server 2005 computer named CompanyB. You need to ensure a
quick response time and appropriate audit trail in the event that CompanyB experiences
excessive traffic due to denial-of-service (DoS) attacks. Which two actions should you perform?
(Each correct answer presents part of the solution. Choose two.)
B. Create a new performance alert to monitor the Current Bandwidth counter.
C. Configure the new performance alert to start a Network Monitor capture.
D. Create a new performance alert to monitor the Bytes Total/sec counter.
A. Configure the new performance alert to start a SQL Server Profiler trace.Answer: C, D
Question: 8
You are a database administrator for Company.com. One of the databases on a SQL Server
2005 computer contains a stored procedure. Users run this stored procedure to import data into a
table. The stored procedure needs to use the TRUNCATE TABLE command before importing
new data into the table. However, the users who run the stored procedure do not have permission
to truncate the table. You need to provide a way for the stored procedure to truncate the table
before it imports new data. What should you do?
A. Configure the stored procedure to use the EXECUTE AS command.
B. Configure the stored procedure to be owned by the same database user as the table.
C. Assign the users DELETE permission in the table.
D. Add the users to the db_datawriter fixed database role.
Answer: A
Question: 9
You are a database administrator for Company.com. You have separate SQL Server 2005
development and production environments. You use the Business Intelligence Development
Studio to create a SQL Server Integration Services (SSIS) package in your development
environment. Then, you use the SSIS package to import data into your development environment
from one of your company's trading partners. You need to deploy the SSIS package to your
production environment. Your production environment uses different table names than your
development environment. What should you do?
A. Save the SQL Server Integration Services (SSIS) package to a file. Copy the file to the
production server. Configure the SSIS package on the production server to use the new file.
B. Back up the master database and restore it to the production server. Rename the appropriate
tables inside the master database.
C. Create a SQL Server Integration Services (SSIS) package configuration. Build a deployment
utility. Copy the deployment folder for your SSIS project to your production server. Execute the
manifest file.
D. Back up the msdb database and restore it to the production server. Rename the appropriate
tables inside the msdb database.
Answer: C
Question: 10
You are a database administrator for Company.com. You notice that one of the data files on a
SQL Server 2005 computer is corrupted. You need to restore the database from the most recent
set of backups. You want to perform this task as quickly as possible, with a minimum loss of data.
What should you do first?
A. Restore the most recent transaction log backup.
B. Restore the most recent full database backup.
C. Perform a full database backup.
D. Perform a transaction log backup.
Answer: DQuestion: 11
You are a database administrator for Company.com. A SQL Server 2005 computer named
CompanyA has a database named Inventory. CompanyA is responsible for aggregating
manufacturing part numbers from your company's trading partners. The manufacturing part
numbers are stored in the Product table in the Inventory database. Every night, data is sent as
text files from each trading partner to CompanyA. You need to import data and ensure that no
duplicate manufacturing part numbers exist in the data imported from the text files that are stored
in the Inventory database on CompanyA. You want to achieve this goal with the minimum amount
of impact on your company's trading partners and your IT department. What should you do?
A. Ensure that each of your company's trading partners uses unique key values for manufacturing
part numbers. Then, import the text files into the Product table.
B. Ensure that each text file is stored in an Extensible Markup Language (XML) file. Use
Extensible Stylesheet Language Transformations (XSLT) to automatically remove duplicates
before data is imported into the Product table.
C. Import the data from each text file into a staging table. Write a query to include a HAVING
clause to remove duplicate values before inserting results into the Product table.
D. Place a unique index on the PartNum column.
Answer: C
Question: 12
You are a database administrator for Company.com. The company has a SQL Server 2005
computer named CompanyA. A database on CompanyA stores sales history data for the
company's Web-based order system. Twenty of the business analysts in the company create ad
hoc queries against the database. The performance of CompanyA is routinely slow. You need to
find out which business analyst is causing the problem. What are two possible ways to achieve
this goal? (Each correct answer presents a complete solution. Choose two.)
A. sp_who system stored procedure.
B. the Activity Monitor to view the activity of specific business analysts.
C. SQL Trace system stored procedures to gather database activity.
D. Create a trace that uses a predefined template by using SQL Server Profiler.
Answer: C, D
Question: 13
You are a database administrator for Company.com. Your company owns a chain of 10 retail
stores. Each retail store maintains point-of-sale transactions on its own SQL Server 2005
computer, in a database table named Sales. The Sales table also contains sales data from other
stores to enable customer returns to any of the 10 retail stores. The sales data is refreshed from
the main office to each retail store hourly. A trigger named trg_Coupon on the Sales table is used
to generate sales coupons based on customer sales and buying patterns. You need to configure
replication between the server in each retail store and a central server in the main office by using
the fewest number of steps. Replication does not have to be in real time. What should you do?
A. Use transactional replication between the server in each retail store and the central server in
the main office.
B. Set up multiple merge publications, one at each retail store and one on the central server in
the main office.
use the NOT FOR REPLICATION option.
D. Use merge replication. Configure the trg_Coupon trigger on the server in each retail store to
use the NOT FOR REPLICATION option.
C. Use snapshot replication. Configure the trg_Coupon trigger on the server in each retail store toAnswer: D
Question: 14
You are a database administrator for Company.com. The company hosts SQL Server 2005
databases for subscription-based customers. New customer databases are created frequently.
Databases are removed when customers subscriptions end. Company managers want to track
who creates each database and when each database is created. To store this information, you
create a SQL Server database named Logging. You need to ensure that the appropriate
information is written to the Logging database. What should you do?
A. Use the SQL Trace stored procedures to log database creation activity.
B. Create a stored procedure. Configure the stored procedure to write the appropriate information
to the Logging database.
C. Create a DDL trigger that runs when a new customer database is created. Configure the DDL
trigger to write the appropriate information to the Logging database.
D. Configure Service Broker to run a stored procedure that writes the appropriate information to
the Logging database.
Answer: C
Question: 15
You are a database administrator for Company.com. You are responsible for managing 10 SQL
Server 2005 computers that run Microsoft Windows Server 2003, Enterprise Edition. The
company's Microsoft Active Directory administrators handle all Group Policy object (GPO)
deployments. The Active Directory administrators have deployed a security template named
SQL05_Security.inf that has the appropriate settings to meet the company's security policy. You
need to identify the existing configuration of each SQL Server 2005 computer to ensure that it
meets the company's security policy. What should you do?
A. Use Network Monitor on each SQL Server 2005 computer to capture a detailed report of the
types of network traffic on the local network adapter.
B. Use the Performance Logs and Alerts snap-in to create counter logs for the Network Interface
performance object.
C. Use the Security Template snap-in to analyze the SQL05_Security.inf security template.
D. Use the Security Configuration and Analysis tool on each SQL Server 2005 computer to
identify discrepancies between system settings and database settings.
Answer: D
Question: 16
You are a database administrator for Company.com. A software developer in the company is
running the following query against a SQL Server 2005 database. SELECT Surname FROM
Employees WHERE UPPER(Surname) LIKE 'COR%' The software developer reports that the
query runs quickly on a test database that has a small number of rows. However, the query runs
very slowly on the production database that has millions of rows. The Surname column stores
data in mixed case, by using case-sensitive collation. But the query needs to perform a caseinsensitive
search. You need to improve the performance of this query. However, you do not have
permission to change the collation orders. And you cannot modify the application code that adds
part of the solution. Choose three.)
A. Create an index on the Surname column.
B. Modify the query to include the following Transact-SQL statement. SELECT Surname FROM
Employees WHERE Surname LIKE UPPER('cor%')
C. Execute the following Transact-SQL statement. ALTER TABLE Employees ADD
UpperSurname AS UPPER(Surname)
D. Execute the following Transact-SQL statement. ALTER TABLE Employees ADD
UpperSurname AS CAST(Surname AS nvarchar)
E. Create an index on the UpperSurname column.
F. Modify the query to include the following Transact-SQL statement. SELECT Surname FROM
Employees WHERE UpperSurname LIKE 'COR%'
rows to the database. Which three actions should you perform? (Each correct answer presentsAnswer: C, E, F
Question: 17
You are a database administrator for Company.com. Your SQL Server 2005 database contains a
table named Customer with a column named PostalCode in it. New PostalCode values are added
regularly. You need to ensure that values contained in the PostalCode column are verified. You
need to retrieve, from the Postal Service in each country in which you have customers, the
domain of values for the PostalCode column. Rapid response time for data access is of primary
concern. What should you do?
A. Implement a database trigger that looks up the postal code data from a server maintained by
the Postal Service.
B. Import the Postal Service data on a nightly basis into a PostalCode table. Then, configure a
foreign key on the Customer table to the PostalCode table.
C. Configure a CHECK constraint on the PostalCode field of the Customer table to allow only
valid values.
D. Implement a CLR trigger that looks up the postal code data by using a Web service offered by
the Postal Service.
E. Configure a new PostalCode data type and ensure that it conforms to the Postal Service
specifications for the format for each country.
Answer: B
Question: 18
You are a database administrator for Company.com. A user named Company belongs to the
Windows SalesManagers group. Company needs a Reporting Services report to display annual
sales information by territory. A user named Joe reports to Company and belongs to the Windows
Sales group. He needs a report that is limited to his sales territory, but still displays the same
detailed sales data that is on Company's report. You need to design a secure solution that meets
the reporting needs of the users and that consumes the minimal amount of server resources.
What should you do?
A. Design a report for Company that includes two data regions. One data region is used for
summary sales data. The other data region is used for the detailed sales data for each sales
representative. Then, design a report for Joe that displays the detailed sales data.
B. Design two reports for Company. One report includes the territory information, and one report
includes the detailed sales data. Then, design a report for Joe that displays the detailed sales
data for his territories.
Joe that includes detailed sales data for his territories. Finally, configure Companys report so
that it includes the data in Joes report.
D. Design a parameterized report for Company that includes all territory information. Then,
design a linked report for Joe that links to Companys report.
C. Design a report for Company that includes the territory information. Then, design a report forAnswer: D
Question: 19
You are a database administrator for Company.com. You receive alerts reporting that several
transactions on your SQL Server 2005 database have terminated due to a deadlock error. You
need to find out the causes of the deadlocks. What should you do?
A. Use System Monitor to trace the Application instance of the Number of Deadlocks/sec counter
in the SQL Locks object.
B. Use the sys.dm_tran_locks dynamic management view (DMV).
C. Run the Database Engine Tuning Advisor (DTA) and implement the recommendations.
D. Run SQL Server Profiler and create a trace with the Deadlock graph event group, and extract
deadlock events.
Answer: D
Question: 20
You are a database administrator for Company.com. You are configuring a new SQL Server 2005
computer named CompanyA. CompanyA will run Reporting Services. It will also be configured to
automatically perform database backups and other maintenance tasks. There are no other SQL
Server computers in the network environment. All access to CompanyA will be made by using
CompanyAs DNS name. You need to disable any unnecessary services on CompanyA. Which
service or services should you disable? (Choose all that apply.)
A. Microsoft Distributed Transaction Coordinator
B. Internet Information Services
C. SQL Server Agent
D. SQL Server Browser
Answer: A, D
Question: 21
You work as DBA at Company.com. You administer a SQL Server 2005 computer named
CompanyA. CompanyA is a member of a Microsoft Active Directory domain. You do not have any
rights or privileges to perform domain administration. However, you have been granted
membership in the local Administrators group on CompanyA. You perform most of the
management of CompanyA from your administrative workstation. However, for security reasons,
you want to track all attempts for interactive logons and network connections to CompanyA. What
should you do?
A. Configure the SQL Server service on CompanyA to audit all successful and failed logon
attempts.
B. Run the SQL Server Profiler and use a standard default template.
C. Edit the local security policy of CompanyA. Then, configure success and failure auditing on the
Audit logon events setting.
D. Create a Group Policy object (GPO) that is configured for success and failure auditing of the
Audit account logon events setting. Ask the domain administrator to link the GPO to the object
containing CompanyA.
Question: 22You work as DBA at Company.com. You administer a SQL Server 2005 computer named
CompanyA. CompanyA is configured to automatically perform transaction log backups, database
integrity checks, and other maintenance tasks on a regular basis. Another administrator uses the
SQL Server Surface Area Configuration tool to reconfigure CompanyA. You notice that
CompanyA no longer performs the automated maintenance tasks. You need to ensure that the
maintenance tasks on CompanyA are completed automatically. What should you do?
A. Manually perform a full backup of the msdb database.
B. Reconfigure the server so that the SQL Server Agent service starts automatically.
C. Reconfigure CompanyA to use Windows Integrated authentication.
D. Reconfigure the SQL Server service to log on by using an administrative user account.
Answer: B
Question: 23
You work as DBA at Contoso.com. You administer a SQL Server 2005 computer named SQL1.
SQL1 replicates with other SQL Server computers and manages multiserver automation jobs. It
also exports data to staging databases for export to a data warehouse. After a few months
without incident, SQL1 fails to start after a reboot. You review the security log, which returns the
results that the Security Log Results exhibit shows.
The Default Domain Group Policy object (GPO) is configured with a Password Policy and
Account Lockout Policy as shown in the following table.
You need to ensure that SQL1 runs properly. What should you do?
A. Unlock the contoso\sqlsrvc account. Then, configure the account with a strong password.
Configure the SQL Server services to use the new account password.
B. Delete the current contoso\sqlsrvc account. Then, create a new contoso\sqlsrvc account and
ensure a strong password. Finally, configure the account so that users cannot change the
password. Configure the SQL Server services to use the new account password.
You then review an individual audit entry, which the Individual Audit Entry exhibit shows.SQL1. Configure the SQL Server services to use the new account password.
D. Enable the contoso\sqlsrvc account. Then, change the password to a strong one. Finally,
configure the account so that users cannot change the password. Configure the SQL Server
services to use the new account password.
C. Configure the contoso\sqlsrvc account as a member of the local administrators group onAnswer: A
Question: 24
You are a database administrator for Company.com. The SQL 2005 Server computer has been in
operation for more than one month. This past week, query performance problems have led you to
investigate locking contention. The sys.dm_os_wait_stats dynamic management view (DMV) is
showing a high value in the max_wait_time_ms column. You need to find out if this value is a
factor in the current performance problems. You also must minimize the impact on database
users. What should you do?
A. Reset the statistics in the dynamic management view (DMV).
B. Restart the SQL Server computer.
C. Execute the UPDATE STATISTICS command.
D. Restart the SQL Server Service.
Answer: A
Question: 25
You are a database administrator for Company.com. You manage a database named Invoicing.
The Invoicing database is backed up with a full backup nightly and transaction log backups once
every two hours from 08:00 to 17:00. You implement database snapshots for the Invoicing
database. A database snapshot is created each day at 07:00. At 09:30 today, a user inadvertently
deleted all of the invoices that were entered into the CurrentInvoice table yesterday. None of
these invoices had been changed today prior to the deletion. It is now 11:45 and many other
changes have occurred in the database. You need to recover the lost rows with as few
administrative steps as possible while minimizing data loss. What should you do?
A. Use the SELECT subquery in the INSERT statement to move the deleted rows from yesterday
mornings database snapshot to the CurrentInvoice table.
B. Restore last nights Full Backup and all transaction log backups until the 10:00 backup. Use the
STOP AT statement on all restores to stop the restores at 09:29.
C. Use the SELECT subquery in the INSERT statement to move the deleted rows from this
mornings database snapshot to the CurrentInvoice table.
D. Restore last nights Full Backup and all transaction log backups until the 10:00 backup. Use the
STOP AT statement on the last restore to stop the restore at 09:29.
Answer: C
Question: 26
You work as DBA at Company.com. You administer a Microsoft Windows Server 2003 computer
named Server1. SQL Server 2005 is installed on Server1 to host a database named MedDB. The
MedDB database serves as the backend database for a new client/server application named
MedDATA. Installation of the MedDATA application creates the database schema, application
roles, and administrative accounts. It also sets all of the default permissions. You need to
establish a permissions baseline for all objects in the MedDB database. What should you do?
A. Use the SQL Server 2005 Management Studio to review the permissions of the default
database objects. 
No comments:
Post a Comment