SQL System Administration Final Examination



SQL System Administration Final Examination

Date: Tuesday Dec 4, 2007

NAME:____________________________________

Instructions:

This is a take home exam.

You have 7 days to complete the test.

You submit your test via email by Monday Dec 10, 2007

Chapter 11, Lesson 1

1. Members of which of the following roles can execute the CREATE TABLE statement? (Choose all answers that are correct.)

a. The db_ddladmin role

b. The db_owner role

c. The sysadmin role

d. The db_securityadmin role

2. Members of which of the following roles can execute the GRANT statement? (Choose all answers that are correct.)

a. The db_ddladmin role

b. The db_owner role

c. The sysadmin role

d. The db_securityadmin role

3. Two users submit the identical query, SELECT * FROM ORDERS against the Accounting database. However, the results returned are not identical. What are likely reasons for this? (Choose all answers that are correct.)

a. The column-level permissions are different for each user.

b. Each user is querying a different table.

c. The database is corrupt.

d. The queries were submitted at different times, and the underlying data changed.

4. If you change the ownership of an object using the sp_changeobjectowner system stored procedure, the permissions associated with the object are not retained. (True or False?)

5. In general, database objects should be owned by the ___________.

6. Members of the db_datawriter database role have which of the following object permissions? (Choose all answers that are correct.)

a. SELECT

b. INSERT

c. UPDATE

d. DELETE

e. EXECUTE

f. REFERENCES

7. Members of the db_datareader database role have which of the following object permissions? (Choose all answers that are correct.)

a. SELECT

b. INSERT

c. UPDATE

d. DELETE

e. EXECUTE

f. REFERENCES

8. Which statement below is not true with respect to viewing permissions on database objects?

a. You can view an object to determine users and groups with permissions on the object.

b. You can view a login to determine the objects on which they have permissions.

c. Viewing an object or a login displays permissions inherited through membership in a server role or fixed database role.

d. Viewing an object displays permissions inherited through membership in a user-defined database role.

Chapter 11, Lesson 2

9. You cannot add users to database application roles. (True or False?)

10. Which statement below about applications roles is not true?

a. Application roles must be activated using a password before they can be used.

b. Users connecting through an application role can access data only in the database in which the application role exists.

c. A user who has been denied access to Microsoft SQL Server can connect to SQL Server through an application role.

d. You assign permissions to application roles in the same manner as you assign permissions to a user-defined database role.

Chapter 11, Lesson 3

11. Upon installation, the _____________________ group is automatically a member of the sysadmin server role.

12. After adding a Microsoft Windows user to a server role, you must create a login for the Windows group in order for members of the Windows group to log on to Microsoft SQL Server. (True or False?)

13. Which of the following statements are not true? (Choose all answers that are correct.)

a. You must create a login for a Microsoft Windows user or group before you grant the user or group database access using SQL Server Enterprise Manager.

b. Only Microsoft SQL Server login accounts can be added to user-defined database roles.

c. You can assign permissions directly to Windows groups.

d. Restricting user access on a column basis has a smaller performance impact than using views or stored procedures.

Chapter 12, Lesson 1

14. After installation of Microsoft SQL Server 2000 on any Microsoft Windows 2000 server edition, which of the following Windows 2000 optimization settings is configured?

a. Data throughput is maximized for file sharing.

b. Data throughput is maximized for network applications.

c. Data throughput is balanced between file sharing and network applications.

d. Data throughput is optimized to minimize memory used.

15. Placing a paging file on the same physical disk as the data files generally improves database performance. (True or False?)

16. When configuring properties for the Microsoft SQL Server service, a value of zero for a SQL Server property means that _____________.

17. In which of the following environments might you modify the default memory settings?

a. On a dedicated Microsoft SQL Server computer

b. On a computer running both SQL Server and Microsoft Exchange Server

c. On a SQL Server computer running the Full-Text Search feature

d. All of the above

18. To register a Microsoft SQL Server instance with Active Directory directory services, the sysadmin must be a member of the local Administrators group. (True or False?)

19. The Microsoft SQL Server Agent domain user account must be a member of the local Administrators group to perform which of the following actions. (Choose all answers that are correct.)

a. Run jobs when the processor is idle

b. Start the SQL Server service if it stops unexpectedly

c. Send mail messages in response to jobs and alerts

d. Create operating system and ActiveX Script jobs not belonging to members of the sysadmin role

20. Which of the following registration properties can be configured when registering a Microsoft SQL Server instance using the Register SQL Server wizard. (Choose all answers that are correct.)

a. Start SQL Server if not already running

b. Authentication mode

c. Hide system tables

d. Logical group

Chapter 12, Lesson 2

21. SQL Mail is the mail service of the ____________ service.

22. You can configure Microsoft SQL Server to return result sets via e-mail to Transact-SQL queries submitted via e-mail. (True or False?)

23. To enable SQL Mail and SQLAgentMail to send mail, you must install a ____________ and configure a ____________________________.

24. When configuring a linked server, which of the following is not a valid mapping option?

a. No security context required.

b. Use current security context for all users.

c. Use a common security context for all users.

d. Create a custom security context for specified users.

e. Use current security context for specified users.

f. All of the above are valid mapping options.

25. Data source names are used with _________ clients.

26. To enable Microsoft SQL Server to respond to XML queries, you must perform which of the following steps. (Choose all answers that are correct.)

a. Specify a database on a SQL Server instance.

b. Enable the database for XML access.

c. Configure a virtual directory in IIS.

d. Specify an authentication method.

e. Define the type of XML query access.

Chapter 12, Lesson 3

27. Microsoft SQL Server automatically updates index distribution statistics. (True or False?)

28. Specifying the proper __________________ and periodically ______________ indexes can help minimize page splits caused by insertion of new data.

29. Full-text indexes can be automatically maintained using which of the following methods. (Choose all answers that are correct.).

a. Complete rebuild on a specified schedule.

b. Incremental rebuild using time stamps on a specified schedule.

c. Incremental rebuild using time stamps when the processor is idle.

d. Incremental rebuild using a change track list as a background task.

e. Incremental rebuild using a change track list as a background task.

30. You can limit the amount of server resources used by the Microsoft Search Service to maintain current indexes. (True or False?)

Chapter 13, Lesson 1

31. The _____________ notification method is not dependent on the underlying e-mail system.

32. A Microsoft Windows 98 user cannot be notified via network pop-up. (True or False?)

33. You can configure a fail-safe operator for notification if the designated operator does not respond. (True or False?)

34. While you are creating a new operator, which of the following tasks cannot be performed? (Choose all answers that are correct.)

a. Specify alerts to be received by the new operator.

b. Specify jobs with respect to which the new operator will receive notifications.

c. Notify the new operator of alerts for which the operator will be receiving notification.

d. All of the above tasks can be performed.

35. You cannot delete a fail-safe operator until you designate a different fail-safe operator. (True or False?)

Chapter 13, Lesson 2

36. Which of the following is not a valid job step type?

a. VB script

b. ActiveX script

c. Operating system command

d. Transact-SQL script

37. Permission to execute a Microsoft SQL Server job is determined by the user executing the job. (True or False?)

38. By default, Transact-SQL job steps in jobs that are owned by users who are not members of the sysadmin server role run in which one of the following security contexts?

a. They cannot be run by default.

b. The user account of the owner of the job.

c. Designated proxy user account.

d. SQLAgentCmdExec user account.

39. A job step can complete successfully and notify an operator that the job failed. (True or False?)

40. Which of the following statements is true with respect to job scheduling?

a. You can create a job schedule that runs a job hourly during the week and once every six hours on the weekend.

b. Disabling a job schedule prevents a job from running.

c. When specifying that a job run when the processor is idle, you can use the default idle condition of 10% utilization for 60 seconds.

d. You can create jobs that run without any defined schedule.

41. When running a multiple-step job, you can start the job at a specified step other than the first step. (True or False?)

42. When viewing the job history log, click the ______________________ check box to view additional information about the execution of the job.

Chapter 13, Lesson 3

43. You can configure alerts to respond to which of the following types of events. (Choose all answers that are correct.)

a. Microsoft SQL Server errors with a severity of 19 or greater

b. Any Microsoft SQL Server error regardless of the severity

c. A user-defined error raised by an application

d. A Stop event in the Microsoft Windows application log

44. You can configure an alert to send a notification to an operator and to execute a job that notifies an operator about the success or failure of that job. (True or False?)

45. For a failed login event to trigger an alert, ___________________ must also be enabled.

46. You can review the execution history of an alert by viewing the alert history log. (True or False?)

Chapter 13, Lesson 4

47. You can use the Database Maintenance Plan wizard to create a single maintenance plan for all system and user databases on a Microsoft SQL Server instance. (True or False?)

48. Which of the following tasks is not configurable using the Database Maintenance Plan wizard?

a. Autoshrinking a database

b. Autogrowing a database

c. Defining a maintenance plan for multiple servers

d. Removing out-of-date backup files

e. Saving execution history to a central reporting server

49. Information about database maintenance plan jobs can be found in the _______________ and ___________ containers in SQL Server Enterprise Manager.

50. A single maintenance plan generally consists of multiple jobs, each with a separate schedule. (True or False?)

Chapter 13, Lesson 5

51. Each participating server in a master/target server group must be running in _____________ Mode authentication.

52. You must register a Microsoft SQL Server instance before you can enlist the SQL Server instance as a target server. (True or False?)

53. By default, target servers pull jobs and instructions from the master server. (True or False?)

54. A master server can perform which of the following tasks? (Choose all answers that are correct.)

a. Read the job history log of each target server.

b. Synchronize the clock of all target servers.

c. Force an immediate poll by each target server.

d. Defect a target server.

e. Synchronize jobs between servers.

Chapter 14, Lesson 1

55. Inadequate response time to a user query is most likely the result of which of the following?

a. A poorly written query.

b. A poorly designed database.

c. Lack of proper indexes.

d. Network congestion.

e. Inadequate resources on the Microsoft SQL Server computer.

f. It is impossible to determine the most likely cause without additional monitoring.

56. Solving one performance bottleneck frequently reveals another performance bottleneck. (True or False?)

57. How can you determine whether a value received when monitoring a Microsoft SQL Server instance is unusually high or low?

58. 4. List three types of factors to monitor for Microsoft SQL Server performance problems.

59. Improving the total server throughput generally improves the response time as perceived by the user. (True or False?)

Chapter 14, Lesson 2

60. The two primary tools for monitoring SQL Server performance are ______________ and _______________?

61. Windows 2000 System Monitor and Task Manager allow you to record data regarding database performance at different points in time to determine levels of activity at various times of the day, week, or month. (True or False)?

62. Which of the following tools cannot be used to determine which users are connecting to a particular table? (Choose all answers that are correct.)

a. SQL Server Enterprise Manager, Current Activity window

b. System Monitor

c. Task Manager

d. Transact-SQL

e. SQL Profiler

63. Which of the following tools can be used to kill a process within Microsoft SQL Server? (Choose all answers that are correct)

a. SQL Server Enterprise Manager, Current Activity window

b. System Monitor

c. Task Manager

d. SQL Profiler

e. SNMP

64. SQL Profiler includes a preconfigured trace template that records sufficient information to allow the captured trace to be replayed step by step. (True or False?)

65. A global counter always begins with which two symbols?

66. Trace flags are an unsupported feature of Microsoft SQL Server 2000. (True or False?)

67. The quickest way to determine the amount of memory, I/O, and processor time used by two competing resources on a computer is to use _______________________.

Chapter 14, Lesson 3

68. You can use the Freeze Display button in Task Manager to freeze counter values at a particular point for later analysis. (True or False?)

69. A high value for the Memory \ Pages/sec counter generally indicates a memory shortage. Which of the following symptoms would this cause? (Choose all answers that are correct.)

a. Excessive processor activity

b. Hard disk paging

c. Network congestion

d. Poor performance for other applications on the computer

70. When creating a trace definition, which type of event is not captured by default?

a. SQL Profiler events.

b. SQL events referencing system objects.

c. A SQL event related to the Microsoft SQL Server service account.

d. All of the above event types are excluded by default.

71. When using the Current Activity window in SQL Server Enterprise Manager, you can view the most recent Transact-SQL command issued by a process holding a blocking lock. (True or False?

Chapter 15, Lesson 1

72. Microsoft SQL Server replication can be used to reduce network traffic across a congested network link. (True or False?)

73. Which of the following is not a valid replication agent?

a. Snapshot Agent

b. Transactional Agent

c. Merge Agent

d. Distribution Agent

e. Queue Reader Agent

74. _________________ replication is for subscribers who need access to data in close to real time.

75. Which of the following is not a valid Subscriber updating option?

a. Immediate Updating

b. Queued Updating

c. Immediate Updating with failover to Queued Updating

d. Queued Updating with failover to Immediate Updating

76. ____________________ replication makes the most use of the distribution database.

Chapter 15, Lesson 2

77. A member of the db_owner fixed database role in a database has permission to enable that database for replication, configure publications, and create push subscriptions. (True or False?)

78. To configure replication using a remote Distributor, the Publisher must be using ________________________ Mode authentication.

79. Which of the following statements is not true with respect to filtering data?

a. Horizontal filtering can negatively affect performance.

b. Dynamic snapshots can improve performance when applying the initial snapshot.

c. You can create different static filters for different types of Subscribers of a single publication.

d. If you filter data using transformable subscriptions, you cannot permit updatable subscriptions.

80. Which of the following statements is not true with respect to the initial snapshot used with each type of replication?

a. The initial snapshot files can be stored on the distributor or on any network share, or copied to a compact disc for installation on a Subscriber.

b. Snapshot files can be compressed to save space using the CAB file format only if they are stored in an alternate location.

c. Snapshot files are always deleted after all Subscribers have applied the initial snapshot.

d. Compressing the initial snapshot files speeds network transmission times.

81. By default, the only login contained on the publication access list (PAL) when a publication is created is the sysadmin account. (True or False?)

Chapter 15, Lesson 3

82. By default, all registered servers on the Distributor computer are enabled to use the Distributor as a Publisher. (True or False?)

83. By default, all registered servers on the Publisher computer are enabled to subscribe to publications on the Publisher. (True or False?)

84. Replication agents can use either the _____________________ account or a _____________ account.

85. With respect to subscriptions, which of the following statements is not true?

a. When creating a push subscription from a Microsoft Windows 2000 Publisher/Distributor to a Windows Me Subscriber, the replication agent can run on either the Subscriber or the Publisher/Distributor.

b. When creating a pull subscription on a Windows Me computer to Windows 2000 Publisher/Distributor, the Subscriber can be anonymous.

c. Merge Agents can run continuously, either on a specified schedule or on demand.

d. When creating a pull subscription for a merge publication, the Subscriber can be a Windows CE device.

86. When configuring a publication, _______________, _____________, and ______________ are the three types of objects that can be replicated.

Chapter 15, Lesson 4

87. Replication Monitor is used to view the properties of existing publications. (True or False?)

88. The ______________________________ is used by members of the sysadmin role to view and resolve merge replication conflicts that were initially resolved automatically.

89. You can enable interactive conflict resolution on a per-subscription basis. (True or False?)

90. The profile of a replication agent cannot be modified in which one of the following ways.

a. Increase the amount of logging

b. Change the location of where the agent runs

c. Continue in spite of consistency errors

d. Use a small batch size

e. Increase the login timeout value

91. Which of the following tasks cannot be performed using Replication Monitor?

a. View the history of any replication agent.

b. Force the validation and resynchronization of all subscriptions to a publication.

c. Change location of any replication agent.

d. View replication conflicts.

Chapter 16, Lesson 1

92. A Microsoft SQL Server instance can function as a standby server for multiple SQL Server instances. (True or False?)

93. Which of the following statements is not true with respect to promoting a standby server?

a. The Microsoft SQL Server Setup program must be run after promoting a standby server to rename the SQL Server installation.

b. Log shipping automatically ensures that all database logins have server access.

c. You must have exclusive access to the database copy on the standby server to complete the promotion.

d. The edition of SQL Server installed for the primary and standby servers must be the same in order to promote the standby server.

94. Log shipping is available with the _____________, ________________, and ______________ editions of Microsoft SQL Server 2000.

95. The SQL Server Agent service account on the standby server must have Full Control access to the transaction log backup share on the primary server. (True or False?)

96. After a standby server is promoted and replaces a primary server, which transactions from the original primary server are not available on the new primary server?

Chapter 16, Lesson 2

97. When Microsoft SQL Server is installed as a virtual server across two or more nodes in a server cluster, the SQL Server Setup program automatically copies the executable program files to ________________and the data files to

98. When installing Microsoft SQL Server in a server cluster, you must install SQL Server from the node that will be the active node for the virtual server. (True or False?)

100. Which of the following statements is not true with respect to installing a virtual server on a server cluster?

a. You can install multiple virtual servers on a server cluster.

b. The failover node for one virtual server can be the active node for another virtual server.

c. The Setup program automatically detects a server cluster.

d. You can designate multiple IP addresses to a single virtual server.

101. Which of the following statements is not true with respect to disaster recovery on a server cluster?

a. Failover is automatic.

b. Removing a failed node does not require Microsoft SQL Server to be stopped.

c. Adding a rebuilt node requires the SQL Server instance on the active node to be restarted.

d. All incomplete transactions are lost when failover occurs.

102. . you want to use MS DTC in a clustered environment, you must run the Cluster wizard on each node before you install Microsoft SQL Server 2000. (True or False?)

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download