SQL Server 2005 - Exam
.JPG "SQL Server 2005 - Exam - Quiz")
Please note that you have to give answers before going to next question and you can not go back. There are 25 Questions. Read Questions carefully before giving answers.
- 1.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 identify the most likely cause of the connection issues. What should you do first?
- A.
Ensure that port 443 is open in your firewall.
- B.
Ensure that port 1443 is open in your firewall.
- C.
Ensure that client computers connect by using Shared Memory protocol.
- D.
Ensure that the server is not paused.
- 2..You are preparing for a new installation of SQL Server 2005. You need to select the protocols that client computers might use to connect to the server. Which two protocols can you use to achieve this goal? Choose two.)
- A.
Named Pipes
- B.
TCP IP
- C.
Shared Memory
- D.
Multiprotocol
- 3.You are moving a SQL Server 2005 database named Timesheet to a new database server. The Timesheet database is used by the TimeEntry application. You need to minimize the amount of time that the TimeEntry application is unavailable while you move the database. What should you do?
- A.
Move the database to the new server by using the SQL Management Object method in the Copy Database Wizard.
- B.
Detach the current database. Copy the data files to the new server. Attach the files.
- C.
Back up the database. Copy the backup file to the new server. Restore the database.
- D.
Move the data files. Specify the new location by using ALTER DATABASE.
- 4.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 of 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 of other data in the database. What should you do?
- A.
Back up the current transaction log. Restore the database with a different name and stop at the point just before the data loss. Copy the table back into the original database.
- B.
Back up the current transaction log. Restore the database to the point just before the data loss.
- C.
Restore the database from the existing backup files to a time just before the data loss.
- D.
Restore the database to the point of the last full backup.
- 5.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.
Create a job schedule that runs every Sunday. Assign weekly tasks to this schedule. Create a second schedule that runs on the first day of every month. Assign monthly tasks to this schedule.
- B.
Create a job for each task that runs once a day. Use a TransactSQL statement to check the date and day of the week. If the day is either a Sunday or the first day of the month, execute the code.
- C.
Create a job schedule that runs once a day. Assign jobs to this job schedule. If the day is either a Sunday or the first day of the month, execute the jobs.
- D.
Create a job for each task that runs once a week on Sunday. Add a second job schedule that runs the job on the first of the month.
- 6.Company.com has multiple servers in a distributed environment. You work with two SQL Server 2005 computers named SQL1 and SQL2. Each server uses SQL Server Authentication and they use different logins. You need to write a distributed query that joins the data on SQL1 with the data on SQL2. What should you do?
- A.
Ensure that both SQL1 and SQL2 use the same login name as the security context for each server.
- B.
Configure SQL2 as a remote server. Write the query on SQL1.
- C.
Configure SQL2 as a linked server to impersonate the remote login.
- D.
Configure SQL2 as a distributed server. Use pass-through authentication.
- 7.A power failure occurs on the storage area network (SAN) where your SQL Server 2005 database server is located. You need to check the allocation as well as the structural and logical integrity of all databases, including their system catalogs. What should you do?
- A.
Execute DBCC CHECKFILEGROUP for each filegroup.
- B.
Execute DBCC CHECKCATALOG.
- C.
Execute DBCC CHECKDB.
- D.
Execute DBCC CHECKTABLE for each table.
- 8.9. You use a SQL Server 2005 database named DB1, which is located on a server named SQL1. DB1 is in use 24 hours a day, 7 days a week. A recent copy of DB1 exists on a second server named SQLtest that also runs SQL Server 2005. You detect a high number of full scans on SQL1 and conclude that additional indexes in DB1 are needed. A workload file that is suitable for Database Engine Tuning Advisor (DTA) already exists. You need to analyze the workload file by using DTA. You must ensure maximum performance on SQL1 during analysis. You must also ensure availability during the implementation of any recommendations suggested by the DTA. What should you do?
- A.
Store the workload file on SQL1. Start DTA on SQLtest and connect to SQL1. Specify all workload and tuning options as necessary. In the Advanced Tuning Options dialog box, select the Generate only online recommendations check box.
- B.
Store the workload file on SQLtest. Start DTA on SQLtest and connect to SQLtest. Specify all workload and tuning options as necessary. In the Advanced Tuning Options dialog box, select the Generate only online recommendations check box.
- C.
Store the workload file on SQL1. Start DTA on SQL1 and connect to SQL1. Specify all workload and tuning options as necessary. In the Advanced Tuning Options dialog box, select the All recommendations are offline check box.
- D.
Store the workload file on SQLtest. Start DTA on SQLtest and connect to SQLtest. Specify all workload and tuning options as necessary. In the Advanced Tuning Options dialog box, select the All recommendations are offline check box
- 9.A new employee needs access to a SQL Server 2005 database that is located on a server named SQL1. You create a login named ajones by using the following Transact-SQL statement. CREATE LOGIN ajones WITH PASSWORD = 'SQLServer$1' The new employee reports that when he logs in, he receives the following error message: "Login failed. The user is not associated with a trusted SQL Server connection." You need to resolve the error and allow the new employee to gain access to SQL1. What should you do?
- A.
Change the SQL Server security mode from Windows Authentication mode to SQL Server and Windows Authentication mode.
- B.
Change the SQL Server security mode from SQL Server and Windows Authentication mode to Windows Authentication mode.
- C.
Ensure that the login name is created with square brackets ([]).
- D.
Give the login access to a specific database by using the CREATE USER Transact-SQL statement.
- 10.You manage a SQL Server 2005 computer that was installed using default settings. After a power failure, the SQL Server (MSSQLSERVER) service on your database server does not start. You need to find out the cause of the problem. Which three actions should you perform? (Each correct answer presents part of the solution. Choose three.)
- A.
In Event Viewer, view the system log.
- B.
In Event Viewer, view the application log.
- C.
In Notepad, view the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ErrorLog.1 file.
- D.
In Notepad, view the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ErrorLog file.
- E.
In Notepad, view the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLAgent.out file.
- 11.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?
- A.
Disable the clustered index and all nonclustered indexes on the target table.
- B.
Set the database recovery model to full.
- C.
Set the database recovery model to bulk-logged.
- D.
Use the BULK INSERT statement to load the data.
- 12.Company.com has a server named SQL1 that is dedicated to SQL Server 2005. SQL1 is configured for SQL Server and Windows Authentication mode. Using Windows Authentication, you have sysadmin privileges on SQL1. All users who work with SQL1 report that all applications that use SQL1 stop responding. Your supervisor asks you to investigate the problem. In Task Manager, you notice that CPU usage is at 100 percent. You try to connect to SQL1 by using SQL Server Management Studio. SQL1 does not respond. You need to connect to SQL1 to diagnose the problem. What are two possible ways to achieve this goal? (Each correct answer presents a complete solution. Choose two.)
- A.
Connect to SQL1 by using the osql command-line utility.
- B.
Connect to SQL1 by using the sqlcmd command-line utility using a dedicated administrator connection.
- C.
Connect to SQL1 by using SQL Server Management Studio. Open a query window and execute the following command.DBCC TRACEON 1224
- D.
In SQL Server Management Studio, on the toolbar, click Database Engine Query to open the Connect to Database dialog box. In the Server name text box, type ADMIN:SQL1. Select SQL Server Authentication and provide your sa credentials. Click the Connect button.
- 13.During the design process for a new production installation of SQL Server 2005, you determine that you need to partition the database. Which edition supports data partitioning?
- A.
SQL Server Express Edition
- B.
SQL Server Enterprise Edition
- C.
SQL Server Workgroup Edition
- D.
SQL Server Standard Edition
- 14.As a database developer, you need to create an application that will be downloaded from the Internet. The application requires a database to store application data. Which SQL Server edition can you use for your application that does not require application users to purchase a license for SQL Server 2005?
- A.
SQL Server Express Edition
- B.
SQL Server Workgroup Edition
- C.
SQL Server Developer Edition
- D.
SQL Server Standard Edition
- 15.Which authentication mode lets you use both SQL Server logins and Windows logins?
- A.
Kerebos authentication
- B.
Windows
- C.
Mixed Mode
- D.
Network Service account
- 16.Which SQL Server 2005 services require that you install them with their own account? (Choose all that apply.)
- A.
SQL Server Agent
- B.
Log Reader Agent
- C.
SQL Server
- D.
.Replication Agent
- 17.Which upgrade data-movement method requires that users not be accessing the database you want to upgrade?
- A.
Copy Database Wizard
- B.
Backup/restore
- C.
Manual scripting
- D.
None of the above
- 18.Which of the following is a prerequisite for Database Mail?
- A.
Service Broker
- B.
Database Mirroring
- C.
Extended MAPI Profile
- D.
Microsoft Exchange Server
- 19.Which of the following sentences is true for authentication mechanisms when the SMTP server is being accessed?
- A.
Database Mail accesses the SMTP server using the SQL Server Agent service credentials by default.
- B.
Database Mail accesses the SMTP server using the SQL Browser service credentials by default.
- C.
Database Mail accesses the SMTP server using the database engine service credentials by default.
- D.
Database Mail accesses the SMTP server using the SQL Server Active Directory Helper service credentials by default.
- 20.Which of the following sentences is true for recovery models?
- A.
In the Full recovery model, most transactions are minimally logged
- B.
In the Simple recovery model, most transactions are minimally logged.
- C.
In the Bulk-Logged recovery model, all transactions are logged.
- D.
In the Simple recovery model, all transactions are logged.
- 21.Which of the following restore operations are NOT allowed in the Simple recovery model? (Choose two.)
- A.
Differential
- B.
Point-in-Time Restore
- C.
Full
- D.
Page Restore
- 22.When do you need to specify an external data source by using a linked server?
- A.
When you need to access a different database.
- B.
When you need to access a different instance.
- C.
When you need to access a different database schema
- D.
When you need to access objects of a different user owner.
- 23.You work as the database administrator at Hi-tech Company. The Hi-tech Company network contains a SQL Server 2005 database server named Server-DB01 that runs on a Windows Server 2003 computer. Server-DB01 hosts a database named DB_Orders. Several data capturers enter data into the DB_Orders database every day. The Simple Recovery Model is implemented for the DB_Orders database. A Full backup of the DB_Orders database is performed every Saturday at 2:00 P.M. You are concerned that data loss may occur should Server-DB01 suffer a hard disk failure. You decide to implement additional backups of the DB_Orders database on a daily basis. You want to minimize the amount of time required for the backups as well as the time required restoring the database. What should you do?
- A.
Perform differential backups of the DB_Orders database on every week day.
- B.
Switch the DB_Orders database to the Full Recovery Model.
- C.
Perform a Full backup of the DB_Orders database on every week day.
- D.
Perform incremental backups of the DB_Orders database on every week day.
- 24.How many default instances can you install on a single SQL Server server?
- A.
1
- B.
2
- C.
3
- D.
5
- 25.
10. You are working as a DBA at the Cape Town office of Company.com. Company.com use a SQL Server 2005 database that does not contain any views. You use Database Engine Tuning Advisor (DTA) to tune this database. A workload file that is suitable for DTA already exists. You are required to locate only missing nonclustered indexes. During this process, you need to insure that existing structures remain intact, and that newly recommend structures msut not be partitioned . You want to accomplish this goal by configuring the tuning options in DTA. Which tuning options should you use?(Select one answer in each section) Total answers will be three.- A.
Indexed Views
- B.
Indexes
- C.
Non culstered Indexes
- D.
Partitioning
- E.
No Partitioning
- F.
Do not keep all Existing PDS
- G.
Keep all Existing PDS
- H.
Keep Indexes only
Featured Quizzes
Popular Topics
Back to top