Hello friends in this post we are going to discuss about SQL Server L1 Multiple choice question | SQL Server L1 Objective type questions | SQL Server L1 Multiple choice questions | SQL Server MCQ Question answers | SQL Server TrendNXT Myskillz Wipro Question answers
Question: 1
UCF: SQL 2.1
Topic: ARCHITECTURE_17
__ number of CPUs are supported by SQL Server 2005 Standard Edition
A) 1
B) 2
C) 4
D) Max Supported by OS
Answer: C) 4
Question: 2
UCF: SQL 2.1
Topic: ARCHITECTURE_17
All system tables are allocated in the _.
A) Secondary filegroup
B) Primary filegroup
C) User-defined filegroups
D) None of the Options
Answer: B) Primary filegroup
Question: 3
UCF: SQL 2.1
Topic: ARCHITECTURE_17
Automatic recovery in the pages is available in
A) 2008
B) 2005
C) 6.5
D) 7
Answer: A) 2008
Question: 4
UCF: SQL 2.1
Topic: ARCHITECTURE_17
By default files not specifically assigned to any file group reside in __
A) Primary file group
B) Secondary file group
C) User-defined file group
D) Log file group
Answer: A) Primary file group
Question: 5
UCF: SQL 2.1
Topic: ARCHITECTURE_17
Collation setting does not include which of the following?
A) character set
B) sort order
C) locale-specific setting
D) None of the above
Answer: D) None of the above
Question: 6
UCF: SQL 2.1
Topic: ARCHITECTURE_17
Collation setting in SQL Server can not be set at the ___ level.
A) Server
B) Database
C) Table
D) Column
Answer: C) Table
Question: 7
UCF: SQL 2.1
Topic: ARCHITECTURE_17
Default port for SQL Server
A) Port 1432
B) Port 1433
C) Port 1434
D) Port 1435
Answer: B) Port 1433
Question: 8
UCF: SQL 2.1
Topic: ARCHITECTURE_17
Default value of Max Worker Thread in SQL Server
A) 255
B) 256
C) 0
D) 200
Answer: C) 0
Question: 9
UCF: SQL 2.1
Topic: ARCHITECTURE_17
Distributed partitioned views is supported by which of the following editions of SQL Server. I. SQL Server Personal Edition II. SQL Server Standard Edition III. SQL Server Enterprise Edition
A) I II
B) II III
C) III
D) I, II III
Answer: C) III
Question: 10
UCF: SQL 2.1
Topic: ARCHITECTURE_17
DMV stands for?
A) Data Management View
B) Department of Motor Vehicle
C) Dynamic Management View
D) Database Monitoring View
Answer: C) Dynamic Management View
Question: 11
UCF: SQL 2.1
Topic: ARCHITECTURE_17
FILLFACTOR in an Index is applied at the _ level
A) Intermediate
B) root
C) leaf
D) none
Answer: C) leaf
Question: 12
UCF: SQL 2.1
Topic: ARCHITECTURE_17
For an in-place upgrade to SQL Server 2005, which version(s) of SQL Server can used? I. SQL Server 2005 RTM or later II. Only SQL Server 2000 SP 4 or later III. Only SQL Server 2000 SP 3 or later IV. SQL Server 7.0 SP 4 or later V. SQL Server 6.5 SP 4 or later
A) I, II, III and IV
B) I, III IV
C) I, II IV
D) II, III, IV V
Answer: B) I, III IV
Question: 13
UCF: SQL 2.1
Topic: ARCHITECTURE_17
How can we configure SQL to allow remote connections
A) SAC
B) DAC
C) sp_configure
D) Both 1 3
Answer: D) Both 1 3
Question: 14
UCF: SQL 2.1
Topic: ARCHITECTURE_17
how do you check the tcp port in sql 2000
A) client network utility
B) server network utility
C) sql configuration manager
D) all of the above
Answer: B) server network utility
Question: 15
UCF: SQL 2.1
Topic: ARCHITECTURE_17
How many cluster index we can create in the table
A) 249
B) 1
C) 0
D) 146
Answer: B) 1
Question: 16
UCF: SQL 2.1
Topic: ARCHITECTURE_17
How many data and log file will be created by default if we use CREATE DATABASE command
A) 1 mdf file, 1 ndf file and 1 ldf file
B) 1 mdf file and 1 ldf file
C) 1 mdf file and 2 ldf file
D) 1 ndf file and 1 ldf file
Answer: C) 1 mdf file and 1 ldf file
Question: 17
UCF: SQL 2.1
Topic: ARCHITECTURE_17
How many databases can be created on each instance of SQL Server?
A) 32767
B) 32766
C) 32747
D) 32787
Answer: A) 32767
Question: 18
UCF: SQL 2.1
Topic: LOG_SHIPPING_18
For the backup job to be successful, the SQL Server service account on the primary server instance in a logshipping setup and the proxy account of the backup job must have __ permissions to the backup directory.
A) read
B) write
C) read/write
D) None of the above
Answer: C) read/write
Question: 19
UCF: SQL 2.1
Topic: LOG_SHIPPING_18
A company has several local stores. Central office needs data twice a month. What kind of replication will you use?
A) Snapshot
B) Merge
C) Transaction
D) Transaction replication with immediate-updating subscribers.
Answer: A) Snapshot
Question: 20
UCF: SQL 2.1
Topic: LOG_SHIPPING_18
Automatic failover is not supported in
A) Mirroring
B) Logshipping
C) Clustering
D) All
Answer: B) Logshipping
Question: 21
UCF: SQL 2.1
Topic: LOG_SHIPPING_18
Can we compress T-Log backup in Log Shipping
A) Possible only in SQL 2008
B) Possible only in SQL 2005
C) Possible only in SQL 2000
D) Possible in All Versions
Answer: A) Possible only in SQL 2008
Question: 22
UCF: SQL 2.1
Topic: LOG_SHIPPING_18
Can we configure Log Shipping using T-SQL?
A) Yes
B) No
C) Only in 2005 version
D) Only in 2008 version
Answer: B) No
Question: 23
UCF: SQL 2.1
Topic: LOG_SHIPPING_18
Can we configure Mirroring on DB if it is already configured for Log Shipping
A) Yes
B) No
C) Possible in SQL 2005
D) Possible in SQL 2008
Answer: A) Yes
Question: 24
UCF: SQL 2.1
Topic: LOG_SHIPPING_18
Can we run any DML or DDL statement on Secondary Database
A) Can run only DML Statement
B) Can run only DDL statement
C) Yes
D) No
Answer: D) No
Question: 25
UCF: SQL 2.1
Topic: LOG_SHIPPING_18
Database Snapshot
A) is the backup and update format of the database
B) is the read only static views of the database
C) is the temporary database
D) is system database
Answer: B) is the read only static views of the database
Question: 26
UCF: SQL 2.1
Topic: LOG_SHIPPING_18
Database state on secondary server if it is configured for logshipping
A) Restoring or Read Only
B) Online
C) No Recovery
D) Multi User
Answer: A) Restoring or Read Only
Question: 27
UCF: SQL 2.1
Topic: LOG_SHIPPING_18
For the copy job, in a log shipping setup, to be successful, the proxy account of the copy job must have ___ access to the copy directory
A) read
B) write
C) delete
D) None of the above
Answer: B) write
Question: 28
UCF: SQL 2.1
Topic: LOG_SHIPPING_18
For the copy job, in a logshipping setup, to be successful, the proxy account of the copy job must have __ permissions to the backup directory
A) read
B) write
C) delete
D) None of the above
Answer: A) read
Question: 29
UCF: SQL 2.1
Topic: LOG_SHIPPING_18
For the restore job to be successful, the SQL Server service account on the secondary server instance in a logshipping setup and the proxy account of the restore job must have _ access to the copy directory
A) read
B) write
C) read/write
D) None of the above.
Answer: C) read/write
Question: 30
UCF: SQL 2.1
Topic: LOG_SHIPPING_18
How many jobs will be created while configuring the logshipping
A) 3
B) 4
C) 5
D) 6
Answer: C) 5
Question: 31
UCF: SQL 2.1
Topic: LOG_SHIPPING_18
In logshipping the recovery model of Primary database should not be in
A) Full
B) Bulk logged
C) Simple
D) None
Answer: C) Simple
Question: 32
UCF: SQL 2.1
Topic: LOG_SHIPPING_18
In order to setup logshipping a user should be a member of _ server role on each instance involved in Log shipping
A) serveradmin
B) sysadmin
C) setupadmin
D) backupadmin
Answer: B) sysadmin
Question: 33
UCF: SQL 2.1
Topic: LOG_SHIPPING_18
In replication, what is each set of the source data that is replicated from the source server called?
A) Publisher
B) Article
C) Data Source
D) Magazine
Answer: B) Article
Question: 34
UCF: SQL 2.1
Topic: LOG_SHIPPING_18
In Replication: you have finance db there are many publications on it. accounting group subscribes to one of the publications, but they want to subscribe only to one article. How can it be done with min admin steps?
A) Filter on the publication so that they can subscribe to one article and use pull subs.
B) Filter on the publication same as above but with push subs.
C) Create a new publication with the above article.
Answer: C) Create a new publication with the above article.
Question: 35
UCF: SQL 2.1
Topic: LOG_SHIPPING_18
In SQL Server 2005, how are the primary and secondary systems in log shipping coupled?
A) tightly
B) loosely
C) No coupling
D) noosed
Answer: B) loosely
Question: 36
UCF: SQL 2.1
Topic: TOOLS_UTIL_10
What factors are necessary to ensure minimal logging? I. Table not replicated II. TABLOCK hint used III. Recovery model set to Bulk-Logged IV. Logshipping disabled
A) I, II III only
B) I II only
C) II IV only
D) I III only
Answer: A) I, II III only
Question: 37
UCF: SQL 2.1
Topic: TOOLS_UTIL_10
Which counter is helpful to find the number of connection in the SQL Server
A) System:user connections
B) Processor:user connections
C) SQL Server: General Statistics: User Connections
D) Database:user connections
Answer: C) SQL Server: General Statistics: User Connections
Question: 38
UCF: SQL 2.1
Topic: TOOLS_UTIL_10
_ is most appropriate to implement the lowest level of granular locking in SQL Server
A) Database
B) Table
C) Record
D) Key
Answer: C) Record
Question: 39
UCF: SQL 2.1
Topic: TOOLS_UTIL_10
_ troubleshoots problems in SQL Server by capturing events on a production system and replaying them on a test system.
A) SQL Interface
B) SQL Audit Events
C) SQL Profiler
D) SQL Audit Utility
Answer: C) SQL Profiler
Question: 40
UCF: SQL 2.1
Topic: TOOLS_UTIL_10
_ in SQL Server 2005 is a replacement of DTS feature in SQL Server 2000
A) SSIS
B) SSAS
C) SSRS
D) Configuration Manager
Answer: A) SSIS
Question: 41
UCF: SQL 2.1
Topic: TOOLS_UTIL_10
_ debugs Transact-SQL statements and stored procedures.
A) SQL Audit Event
B) SQL Interface
C) SQL Profiler
D) Transact-SQL debugger
Answer: D) Transact-SQL debugger
Question: 42
UCF: SQL 2.1
Topic: TOOLS_UTIL_10
A database originally created at size of 500M, now grows to 1G, the data is 200M, to reclaim 600M for OS:
A) DBCC SHRINKDATABSE (database, 50)
B) DBCC SHRINKDATABSE (database, 400)
C) DBCC SHRINKFILE (database, 400)
D) DBCC SHRINKFILE (database, 50)
Answer: A) DBCC SHRINKDATABSE (database, 50)
Question: 43
UCF: SQL 2.1
Topic: TOOLS_UTIL_10
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?
A) Restore the most recent backup into a new database named DB1bak. Apply the most recent differential backup. Copy the Pricelist table from DB1bak to DB1.
B) Delete all database snapshots except the most recent one. Restore DB1 from the most recent database snapshot.
C) Recover DB1 from the most recent backup. Apply the most recent differential backup.
D) Copy the Pricelist table from the most recent database snapshot into DB1.
Answer: D) Copy the Pricelist table from the most recent database snapshot into DB1.
Question: 44
UCF: SQL 2.1
Topic: TOOLS_UTIL_10
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 faile 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.
Answer: A) Change the SQL Server security mode from Windows Authentication mode to SQL Server and Windows Authentication mode.
Question: 45
UCF: SQL 2.1
Topic: TOOLS_UTIL_10
A query which loads data on a Production Server runs. Transaction Log is approaching 500MB, less than 50% done. 3GB HD was free on the drive. DB growth was auto. The query blocks out other users. How could you give the general user quick access to the database for use.
A) Kill update process
B) Let the update process run until it finishes
C) Kill the blocked processes
D) Stop and restart the SQL server
Answer: B) Let the update process run until it finishes
Port number for database mirroring
5022
5023
5024
1434
Ans: 5022
Which of these is only used for DB design?
Toad
DB artisian
Erwin
None
Ans:
SQL job are stored in which DB?
TempDB
Master
Msdb
Model
Ans: msdb
How do we know % of back up completed
Inputbuffer (pid)
Outbuffer (pid)
Is used to comment a single line
%
$
^
Ans: —
select 5+null from dual
5
0
Null
Error
Ans null
ANSI SQL STANDARDS SQL 86, 89, 92,99,2003,2006,2008,2011
You may get question on this
How many sub queries can be there in a nested query
Ans 32
Which of these is true
1.Select can be used with stored procedure and function
2.Select can be used with stored procedure , but not with function
3.Select cannot be used with stored procedure but can be using with function
4.Select cannot be used with stored procedure neither with function
Ans:
Start sql server in single user mode
Ans : Net start sqlserver –m
Start sql server in minimal config mode
Ans: net start sqlserver –f
Question: 46
UCF: SQL 2.1
Topic: WINDOWS_OS_5
/3GB Switch needs to be added in
A) win.ini
B) boot.ini
C) windows.ini
D) sql.ini
Answer: B) boot.ini
Question: 47
UCF: SQL 2.1
Topic: WINDOWS_OS_5
_ are the key identifiers to for accounts in Windows
A) SPID
B) SID
C) UID
D) GUID
Answer: B) SID
Question: 48
UCF: SQL 2.1
Topic: WINDOWS_OS_5
AWE is in 64 bit
A) Not Available
B) Automatically enabled
C) Manually
D) both
Answer: B) Automatically enabled
Question: 49
UCF: SQL 2.1
Topic: WINDOWS_OS_5
Built-in local group cannot be created or deleted. It can only be modified.
A) TRUE
B) FALSE
Answer: A) TRUE
Question: 50
UCF: SQL 2.1
Topic: WINDOWS_OS_5
How much memory is supported by Windows 2003 Datacenter Edition when /PAE switch is enabled
A) 16 GB
B) 32 GB
C) 24 GB
D) 64 GB
Answer: D) 64 GB
What is the recommended page file size in windows
Ans 1.5 x RAM
SQL 2.2
Question: 1
UCF: SQL 2.2
Topic: DBA_17
Create a predefined list of data sources that users can select at run time. When a user requests a report, the report provides a list of data sources. The user must select which data source to use prior to running the report. To add a data source selection list to a report, you use an expression What do you call this type of connection in SSRS?
A) Dynamic data source connection
B) Temporary data source connection
C) Linked data source connection
D) Remote data source connection
Answer: A) Dynamic data source connection
Question: 2
UCF: SQL 2.2
Topic: DBA_17
James works as a Database Administrator for Miracle Infocom. The company has a SQL server 2005 computer named bwSQL1 with the default collation settings. He installs SQL server 2005 on another computer named bwSQL2 with different collation settings. What will he do to configure bwSQL2 with the same collation settings as bwSQL1?
A) Reinstall SQL server 2005 on bwSQL2 with the same collation settings as bwSQL1.
B) Use the fn_get_sql function to change the collation settings.
C) Use the SQL Server properties dialog box in SSMS to change the collation settings.
D) Use the fn_helpcollation function to change the collation settings.
Answer: A) Reinstall SQL server 2005 on bwSQL2 with the same collation settings as bwSQL1.
Question: 3
UCF: SQL 2.2
Topic: DBA_17
What is the configuration setting, Execution Account used for in the Reporting Services Configuration Tool?
A) Used for connecting to the Report Server DB
B) Used for connecting and executing against ReportServer DB
C) used for remote connections during scheduled operations or when user credentials are not available
D) Used for connecting and executing against Reportservertempdb database
Answer: C) used for remote connections during scheduled operations or when user credentials are not available
Question: 4
UCF: SQL 2.2
Topic: DBA_17
What is the difference between Single user and Restricted User modes?
A) Only one user can connect to the SQL Server in Single User mode All the members of db_owner , dbcreator , sysadmin groups can connect in Restricted User mode
B) Only db_owners can connect to the SQL Server in Single User mode All the members of db_owner , dbcreator , sysadmin groups can connect in Restricted User mode
C) Choice A B
D) Only one user can connect to the SQL Server in Restricted User mode All the members of db_owner , dbcreator , sysadmin groups can connect in Single User mode
Answer: A) Only one user can connect to the SQL Server in Single User mode All the members of db_owner , dbcreator , sysadmin groups can connect in Restricted User mode
Question: 5
UCF: SQL 2.2
Topic: DBA_17
What type of Operating Mode that the Database Mirroring is working which has Safety set to Full having witness Server and Synchronous data transfer happens?
A) A. High Protection
B) High Availability
C) High Performance
D) High Scalability
Answer: B) High Availability
Question: 6
UCF: SQL 2.2
Topic: DBA_17
Which of the following is true when using SSIS Package Migration Wizard to migrate DTS packages to SSIS packages in sql server 2005?
A) does not migrate transaction settings
B) Migrates ActiveX scripts
C) Migrates complex DTS tasks
D) Migrates Dynamic Properties Tasks
Answer: A) does not migrate transaction settings
Question: 7
UCF: SQL 2.2
Topic: DBA_17
Andrew works as a Database Administrator for Miracle Infocom. The company has a SQL server 2005 computer. The computer has a database named Purchase. Andrew creates a table named products. He writes some queries that are used to insert and update data in the table and delete data from the table. He now wants to check the performance of the queries. Which of the following will he use to accomplish the task?
A) SP_UPDATESTATS
B) SP_AUTOSTATS
C) SYS.DM_EXEC_QUERY_STATS
D) SYS.DM_DB_INDEX_PHYSICAL_STATS
Answer: C) SYS.DM_EXEC_QUERY_STATS
Question: 8
UCF: SQL 2.2
Topic: DBA_17
Andrew works as a Database Administrator for Miracle Infocom. The company has a SQL server 2005 computer. The computer has a database named Orders. Andrew wants to maintain two copies of the database such that if insert, update, and delete operations are performed on the principal database, it should also apply on the other copy of the database. Which of the following processes will be applied by Andrew to accomplish the task?
A) Failover clustering
B) RAID installation
C) Replication
D) Database mirroring
Answer: D) Database mirroring
Question: 9
UCF: SQL 2.2
Topic: DBA_17
________ plays an important role in the performance of query
A) Database Engine
B) Query Parser
C) Query Optimizer
D) File Manager
Answer: C) Query Optimizer
Question: 10
UCF: SQL 2.2
Topic: DBA_17
A full backup of your database named sales is created automatically at 23:00 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 orders table in sales at 12:30. The last update to orders occurred one week ago. You need to recover the orders 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?
A) Restore the most recent backup into a new database named salesnew. Apply the most recent differential backup. Copy the orders table from salesnew to sales.
B) Delete all database snapshots except the most recent one. Restore sales from the most recent database snapshot.
C) Recover sales from the most recent backup. Apply the most recent differential backup.
D) Copy the orders table from the most recent database snapshot into sales.
Answer: D) Copy the orders table from the most recent database snapshot into sales.
Question: 11
UCF: SQL 2.2
Topic: DBA_17
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.
Answer: C) Execute DBCC CHECKDB.
Question: 12
UCF: SQL 2.2
Topic: DBA_17
Andrew works as a Database Administrator for Miracle Infocom. The company has a SQL server 2005 computer. The computer has a database named Purchase. Andrew creates a table named products. He writes some queries that are used to insert and update data in the table and delete data from the table. He now wants to check the performance of the queries. Which of the following will he use to accomplish the task?
A) A.SP_UPDATESTATS
B) B.SP_AUTOSTATS
C) SYS.DM_EXEC_QUERY_STATS
D) D.SYS.DM_DB_INDEX_PHYSICAL_STATS
Answer: C) SYS.DM_EXEC_QUERY_STATS
Question: 13
UCF: SQL 2.2
Topic: DBA_17
Andrew works as a Database Administrator for Miracle Infocom. The company has a SQL server 2005 computer. The computer has a database named Sales. The company has its office in an area that is prone to power failure. Andrew has to take a backup that includes all the filegroups and database objects. Which of the following types of backups will he take to accomplish the task?
A) A.Transaction Logbackup
B) B.Partial backup
ams)C) Full database backup
D) D.File backup
Answer: ams)C) Full database backup
Question: 14
UCF: SQL 2.2
Topic: DBA_17
Andrew works as a Database Administrator for Miracle Infocom. The company has a SQL server 2005 computer. The computer has a database named Sales. The company has several sales outlets, each having a SQL server 2005 computer. Andrew has to replicate data between the main office and its sales outlets. The database contains several agent profiles to monitor the replication. He wants to modify one of the agent profiles. Which of the following stored procedures will he use to accomplish the task?
A) A.sp_alter_agent_profile
B) sp_help_agent_profile
C) C.sp_add_agent_profile
D) D.sp_modify_agent_profile
Answer: B) sp_help_agent_profile
Question: 15
UCF: SQL 2.2
Topic: DBA_17
As the database owner, you grant Franz permissions to create views and create stored procedures in the Finance database. France creates a stored procedure that does an update on the dbo.prices table. He creates a view that selects price information for a report. He grants Suzanne SELECT permissions on the view and EXECUTE permissions on the stored procedure. What must be done so that Suzann e can obtain the results by using the view and stored procedure?
A) A.You must grant Suzanne SELECT permissions on the view and the stored procedure.
B) B.Franz must grant Suzanne SELECT permissions on the Prices table.
C) You must grant Suzanne SELECT and UPDATE permissions on the Prices table.
D) D.Franz must grant Suzanne EXECUTE permissions on the stored procedure and SELECT permissions on the Prices table.
Answer: C) You must grant Suzanne SELECT and UPDATE permissions on the Prices table.
Question: 16
UCF: SQL 2.2
Topic: DBA_17
Can you run a differential backup on a database in the Simple recovery mode?
A) Yes
B) No
Answer: A) Yes
Question: 17
UCF: SQL 2.2
Topic: DBA_17
Collation setting does not include which of the following?
A) character set
B) sort order
C) locale-specific setting
D) None of the above
Answer: D) None of the above
Question: 18
UCF: SQL 2.2
Topic: MT_15
What are the databases that will be created when you install SQL Server Reporting Services
A) Tempdb, reportserver
B) Reportserver, Reportservertempdb
C) Reportservertempdb, Reportserver, Tempdb
D) None
Answer: B) Reportserver, Reportservertempdb
Question: 19
UCF: SQL 2.2
Topic: MT_15
What does the waittype column, RESOURCE_QUEUE indicates
A) You may notice this waittype during the long-running I/O-bound operations such as creating, expanding, or dropping a database file
B) This waittype indicates that a batch has been received from a client application but that there are no worker threads that are available to service the request
C) These waittypes are all involved in parallel query execution
D) This is an ordinary idle state for background threads in SQL Server
Answer: D) This is an ordinary idle state for background threads in SQL Server
Question: 20
UCF: SQL 2.2
Topic: MT_15
What is the command used to update query optimization statistics?
A) UPDATE STATISTICS
B) STATS_UPDATE
C) DBCC UPDATE_STATISTICS
D) SP_UPDATESTATISTICS
Answer: A) UPDATE STATISTICS
Question: 21
UCF: SQL 2.2
Topic: MT_15
Cannot generate SSPI context error message, when connect to local SQL Server outside domain
A) Connection configuration causes network library to choose TCP/IP provider and The hosting machine of SQL Server is connected to a network, including home network or dialup connection, but it is disconnected from its domain
B) Invalid Service pack installation.
C) Port number is not configuered
D) SQL Server has too many connections
Answer: A) Connection configuration causes network library to choose TCP/IP provider and The hosting machine of SQL Server is connected to a network, including home network or dialup connection, but it is disconnected from its domain
Question: 22
UCF: SQL 2.2
Topic: MT_15
You are the administrator of a SQL Server 2000 computer. Each night you need to send a copy of the server s msdb database in an e-mail message to the administrator of another SQL Server computer. You create a job that contains the following steps: Detach the msdb database Send the database to the administrator in an e-mail message Reattach the msdb database You test the job, and it fails on the first step. You need to ensure that the msdb database is mailed to the administrator every night. What should you do?
A) A.Ensure that the SQLServerAgent service is running under a user account that has database owner access to the msdb database
B) B.Delete the first and last steps in the job
C) Configure the job to back up the msdb database to a temporary file Send the file to the administrator in an e-mail message
D) D.Insert a new job step before the first step Configure the new step to terminate all processes that use the Msdb database
Answer: C) Configure the job to back up the msdb database to a temporary file Send the file to the administrator in an e-mail message
Question: 23
UCF: SQL 2.2
Topic: MT_15
A Company uses a SQL Server 2005 database. A full backup of the database is made each night. Transaction log backups are made each morning and each afternoon. You notice that the database transaction log file grows very large throughout the day. You need to minimize the size of the transaction log file. You also need to minimize data loss in the event of a failure. What should you do?
A) Decrease the amount of time between transaction log backups.
B) Configure the database to use the simple recovery model.
C) Configure the RECOVERY INTERVAL option to be a smaller value.
D) Run DBCC SHRINKFILE on the transaction log file.
Answer: A) Decrease the amount of time between transaction log backups.
Question: 24
UCF: SQL 2.2
Topic: MT_15
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?
A) Restore the most recent backup into new database and apply the most recent differential backup
B) Recover DB1 from most recent backup and apply the most recent Differential backup
C) Delete the database snapshots except the most recent one and restore DB1 from most recent database snapshot
D) Copy the pricelist table from the most recent database snapshot into DB1
Answer: D) Copy the pricelist table from the most recent database snapshot into DB1
Question: 25
UCF: SQL 2.2
Topic: MT_15
All database users belong to which role?
A) everyone
B) db_public
C) public
D) db_datareader
Answer: C) public
Question: 26
UCF: SQL 2.2
Topic: MT_15
Andrew works as a Database Administrator for Miracle Infocom. The company has a SQL server 2005 computer. The computer has a database named Sales. The company has several sales outlets, each having a SQL server 2005 computer. Andrew has to replicate data between the main office and its sales outlets. The database contains several agent profiles to monitor the replication. He wants to modify one of the agent profiles. Which of the following stored procedures will he use to accomplish the task?
A) sp_alter_agent_profile
B) sp_help_agent_profile
C) sp_add_agent_profile
D) sp_modify_agent_profile
Answer: B) sp_help_agent_profile
Question: 27
UCF: SQL 2.2
Topic: MT_15
As SQL Server 2000 DBA, you should grant select permission to the Products table for all members of the Management department. There are Admin, Management, Tech and Sales database roles. Ravi has recently been transferred to the Management department from the Tech department and added to the Management role. The Tech role has been denied access to the Products table. All employees from the Management department have select permission to the Products table, but Ravi does not have. You should grant select permission to the Products table for Ravi. What should you do?
A) GRANT SELECT ON Products TO Ravi
B) sp_addrolemember db_owner , Ravi
C) sp_droprolemember Tech , Ravi
D) sp_addrolemember Admin , Ravi
Answer: C) sp_droprolemember Tech , Ravi
Question: 28
UCF: SQL 2.2
Topic: MT_15
Backup strategy involves making full backup on every Saturday night, differential backup every day and transaction log backup every hour. The database crashes. How do you restore?
A) Restore the latest full backup
B) Restore all differential backups in sequence
C) Restore all differential backups after the full backup in sequence
D) Restore all transaction log backups
Answer: A) Restore the latest full backup
Question: 29
UCF: SQL 2.2
Topic: MT_15
Company s financial forecasting application uses a SQL Server 2005 database that enables business users to run both ad hoc and predefined queries. You suspect that some queries consume an excessive amount of server resources. You need to identify which queries consume the most resources. You want to achieve this goal as quickly as possible. What should you do?
A) Use the DBCC INPUTBUFFER command.
B) Use the sys.dm_exec_query_stats dynamic management view (DMV).
C) Use the sqldiag utility.
D) Use the SHOWPLAN session option.
Answer: B) Use the sys.dm_exec_query_stats dynamic management view (DMV).
Question: 30
UCF: SQL 2.2
Topic: MT_15
Create job that performs several maintenance tasks on the server s databases. You want the job to run whenever the server s processor utilization falls below 5 percent.You create a new schedule for the job and specify the Start whenever the CPU(s) become idle option. After several days, you notice that the job has never executed although the server s processor utilization has fallen below 5 percent several times. What should you do?
A) Modify SQL Server Agent properties and specify a smaller idle time.
B) Modify SQL Server Agent properties and specify a larger idle time.
C) Write a stored procedure that executes the job whenever the @@IDLE system variable is less than 5.
D) Write a stored procedure that executes the job whenever the @@IDLE system variable is greater than 1.
Answer: A) Modify SQL Server Agent properties and specify a smaller idle time.
Question: 31
UCF: SQL 2.2
Topic: MT_15
What SQL Server edition is require for setting up a server as log shipping monitor
Enterprise
Developer
Standard
Any of these
Answer: Any of these
Question: 32
UCF: SQL 2.2
Topic: MT_15
Which database recovery model does not allow condifuring Log Shipping
Simple
Full
Bulk-Logged
all of them
Answer: Simple
Question: 33
UCF: SQL 2.2
Topic: NETWORKING_5
__ Protocol is preferred for remote connections as it opens fewer ports in the firewall
A) Named Pipes
B) Shared Memory
C) TCP/IP
D) None of the above
Answer: C) TCP/IP
Question: 34
UCF: SQL 2.2
Topic: NETWORKING_5
Microsoft SQL Server cannot service several protocols at the same time
A) TRUE
B) False
Answer: B) False
Question: 35
UCF: SQL 2.2
Topic: NETWORKING_5
If your company s Web site is linked to a SQL Server 2005 server which performance counter would help you capture the sudden increase in Network Interface
A) Network Interface: Bytes Total/sec counter
B) Network Interface: Output Queue Length
C) SQL Server: Buffer Manager: Buffer Cache Hit Ratio
D) None of the above
Answer: A) Network Interface: Bytes Total/sec counter
Question: 36
UCF: SQL 2.2
Topic: NETWORKING_5
A unique, 12-digit (48-bit), hexadecimal number that the network interface card (NIC) manufacturer burns into a computer s network interface card is called
A) http address
B) IP address
C) MAC Address
D) FTP address
Answer: C) MAC Address
Question: 37
UCF: SQL 2.2
Topic: NETWORKING_5
Before any data can be transmitted through the TCP protocol, a reliable connection must be established. What is the process that TCP uses to establish this connection called
A) network handshake
B) windows handshake
C) two way handshake
D) three way handshake
Answer: D) three way handshake
Question: 38
UCF: SQL 2.2
Topic: REPLICATION_13
In SQL Server 2005, log_reuse_wait_desc column in sys.databases system table is showing as Replication and you are getting alerts that your Tlog is full, how do you resolve this?
A) Update the column to Nothing and take Tlog backup
B) Temporarily stop/disable the Replication, increase the Log file size
C) Restart SQL Service
D) Take Tlog backup
Answer: B) Temporarily stop/disable the Replication, increase the Log file size
Question: 39
UCF: SQL 2.2
Topic: REPLICATION_13
Which type of replication allows for reads and modifications to be performed on any of the databases participating in replication?
A) Transactional Replication
B) Snapshot Replication
C) None
D) Peer to Peer replication
Answer: D) Peer to Peer replication
Question: 40
UCF: SQL 2.2
Topic: REPLICATION_13
A distributed database can use which of the following strategies?
A) Totally centralized at one location and accessed by many sites
B) Partially or totally replicated single location sites
C) Partitioned into segments into single site
D) none
Answer: A) Totally centralized at one location and accessed by many sites
Question: 41
UCF: SQL 2.2
Topic: REPLICATION_13
A distributed database has which of the following advantages over a centralized database
A) Software cost
B) Software complexity
C) Slow Response
D) Modular growth
Answer: D) Modular growth
Question: 42
UCF: SQL 2.2
Topic: REPLICATION_13
A homogenous distributed database is which of the following?
A) The same DBMS is used at each location and data are not distributed across all nodes.
B) The same DBMS is used at each location and data are distributed across all nodes
C) A different DBMS is used at each location and data are not distributed across all nodes
D) A different DBMS is used at each location and data are distributed across all nodes
Answer: B) The same DBMS is used at each location and data are distributed across all nodes
Question: 43
UCF: SQL 2.2
Topic: REPLICATION_13
Error Message: Can not update column as it s used in replication ? What could be the probable cause?
A) column not found in the replication DB
B) Column does not have insert permission
C) Replication Agent is not working
D) If this table is no longer involved in replication, there is a stored procedure to update the object s metadata and it is called sp_MSunmarkreplinfo (it takes a tablename as a parameter). Running sp_removedbreplication can be used to remove all traces of replication in the subscriber database, but obviously must only be done if this database is no longer needed as a publisher or subscriber.
Answer: D) If this table is no longer involved in replication, there is a stored procedure to update the object s metadata and it is called sp_MSunmarkreplinfo (it takes a tablename as a parameter). Running sp_removedbreplication can be used to remove all traces of replication in the subscriber database, but obviously must only be done if this database is no longer needed as a publisher or subscriber.
Question: 44
UCF: SQL 2.2
Topic: REPLICATION_13
From where does the replication queue reader agent get its data?
A) The publisher transaction log
B) The distributor transaction log
C) From the distribution database
D) From a message queue
Answer: D) From a message queue
Question: 45
UCF: SQL 2.2
Topic: REPLICATION_13
George works as a Database administrator for Miracle Infocom. The company has its main office in Los Angels and 20 branch offices at other locations. The main office has a SQL server 2005 computer, which is a Publisher. Each branch office also has a SQL server 2005 computer, which is either Distributor or Subscriber. He wants to monitor the transaction logs of those databases that are configured for transactional replication. Which of the following replication agents will he use to accomplish the task?
A) Log Reader
B) B.Distribution
C) C.Merge
D) D.Snapshot
Answer: A) Log Reader
Question: 46
UCF: SQL 2.2
Topic: REPLICATION_13
How are schema changes(add columns) replicated in sql 2005?
A) They are replicated by default
B) It should be activated
C) They wont be replicated
D) Reinitialize the subscription
Answer: A) They are replicated by default
Question: 47
UCF: SQL 2.2
Topic: REPLICATION_13
How can I find numerical info about the transactions remaining to be sent?
A) Subscription status
B) publisher status
C) sp_replmonitorsubscriptionpendingcmds
D) You can not view
Answer: C) sp_replmonitorsubscriptionpendingcmds
Question: 48
UCF: SQL 2.2
Topic: REPLICATION_13
How can Replication Alerts be written to Event Viewer ?
A) Open up the replication alerts folder, double click on the alert you are interested, click on the browse
B) No Function available
C) It will directly go to event Viewer
D) None
Answer: A) Open up the replication alerts folder, double click on the alert you are interested, click on the browse
Question: 49
UCF: SQL 2.2
Topic: REPLICATION_13
how to add column to a replicated article:
A) sp_repaddcolumn
B) sp_replsaddcolumn
C) sp_repladdcolmn
D) sp_repladdcolumn
Answer: D) sp_repladdcolumn
Question: 50
UCF: SQL 2.2
Topic: REPLICATION_13
If you want to establish a replication model that will allow three branch offices to update data at the corporate site and propagate the data back to each branch office. Which replication model should you use?
A) Transactional replication
B) Merge replication
C) Remote replication
D) Snapshot replication
Answer: B) Merge replication
SQL 3.1
Question: 1
UCF: SQL 3.1
Topic: INST_UPG_10
Which of the following statement is not correct about Clustering
High reliability
Offers hardware, OS, and software fault tolerance
Low availabilty
Automatic failover
Answer: Low availabilty
Question: 2
UCF: SQL 3.1
Topic: INST_UPG_10
How many Virtual IP address is needed for installing 4 instances of sql server in sql 2005 cluster
1
2
3
4
Answer: 4
Question: 3
UCF: SQL 3.1
Topic: INST_UPG_10
Recommended minimum size for Quorum is
1024 MB
500 MB
256 MB
128 MB
Answer: 500 MB
Question: 4
UCF: SQL 3.1
Topic: INST_UPG_10
Is it necessary to configure MSDTC as a clustered resource for SQL Server clustering to work?
yes it is required always for SQL Server clustering to work?
no, it is not required
it is required only if you need the ability to perform distributed transactions on your cluster
none
Answer: it is required only if you need the ability to perform distributed transactions on your cluster
Question: 5
UCF: SQL 3.1
Topic: INST_UPG_10
Which of the following option is not correct about Clustering
The cluster service and SQL Server service accounts need to be a member of the Local Administrators group of each node
The cluster service and SQL Server service accounts need to be domain accounts and members of the domain user group
An sql instance must be installed fom the active node in the cluster.
An SQL instances can be uninstalled from Add\Remove program in control panel
Answer: An SQL instances can be uninstalled from Add\Remove program in control panel
Question: 6
UCF: SQL 3.1
Topic: INST_UPG_10
Can SQL Server 2000 be installed on one node of a cluster and SQL Server 2005 be installed on the other node?
Yes
no
it depends on requirement
none
Answer: no
Question: 7
UCF: SQL 3.1
Topic: INST_UPG_10
What cluster resources should not belong to cluster group
Cluster IP Address
Cluster Name
Quorum drive
Physical Disk F:
Answer: Physical Disk F:
Question: 8
UCF: SQL 3.1
Topic: INST_UPG_10
If you take any sql server resource in a cluster offline, what will happen?
SQL Server Resources will be failed over to another node
SQL Server dependent resources will be failed over to another node
Only SQL Server resources will be offline on same node
SQL Server and its dependent resources will be offline on same node
Answer: SQL Server and its dependent resources will be offline on same node
Question: 9
UCF: SQL 3.1
Topic: INST_UPG_10
Upto how many instances can be supported in sql 2005 Enterprise Edition Clustering
25
16
50
32
Answer: 25
Question: 10
UCF: SQL 3.1
Topic: INST_UPG_10
Upto how many instances can be supported in sql 2005 Standard Edition Clustering
25
16
50
32
Answer: 16
Question: 11
UCF: SQL 3.1
Topic: MON_TR_15
Which DBCC command is used to check the fragmentation of a Database?
DBREINDX
SHOWCONTIG
DBCATALOG
None of the Aove
Answer: SHOWCONTIG
Question: 12
UCF: SQL 3.1
Topic: MON_TR_15
Which DBCC command performs both DBCC CHECKTABLE and CHECKALLOC?
CHECKDB
SHOWCONTIG
DBCHECKCATALOG
None of the Aove
Answer: CHECKDB
Question: 13
UCF: SQL 3.1
Topic: MON_TR_15
What does the ERROR_PROCEDURE() function return?
Description of the error
Name of the function
Name of the stored procedure that caused the error
All of the Above
Answer: Name of the stored procedure that caused the error
Question: 14
UCF: SQL 3.1
Topic: MON_TR_15
What does @@options return?
Current set options for the connection
Current set options
Current set options for the user
None of the Above
Answer: Current set options for the connection
Question: 15
UCF: SQL 3.1
Topic: MON_TR_15
Where do you think the users names and passwords will be stored in sql server?
sysusers
sysxlogins
sysobjects
sysproperties
Answer: sysxlogins
Question: 16
UCF: SQL 3.1
Topic: MON_TR_15
Which DBCC command is used to reduce the size of the file?
SHRINKDB
SHRINKFILE
SHRINKFILES
None of the Above
Answer: SHRINKFILE
Question: 17
UCF: SQL 3.1
Topic: MON_TR_15
Which trace flag can be used to trace the occurrence of Deadlocks?
-1204
205
206
305
Answer: -1204
Question: 18
UCF: SQL 3.1
Topic: MON_TR_15
Which DBCC command provides statistics about the use of T-log space in all databases?
SQLPERF (LOGSPACES)
SQLPERF (LOGS)
SQLPERF
SQLPERF (LOGSPACE)
Answer: SQLPERF (LOGSPACE)
Question: 19
UCF: SQL 3.1
Topic: MON_TR_15
Which stored procedure should be used to list the fixed Server roles in the current database?
sp_helpsrvroles
sp_helpsvrole
sp_helpsrvrole
None of the Above
Answer: sp_helpsrvrole
Question: 20
UCF: SQL 3.1
Topic: MON_TR_15
Which database is used as a template for all other databases?
model
master
msdb
tempdb
Answer: model
Question: 21
UCF: SQL 3.1
Topic: MON_TR_15
How many SQL Server agent service runs per SQL Server Installation?
2
1
3
4
Answer: 1
Question: 22
UCF: SQL 3.1
Topic: MON_TR_15
Which stored procedure should you use to estimate the amount of space on disk required by a backup?
sp_spaceused
sp_spacesused
sp_space
None of the Above
Answer: sp_spaceused
Question: 23
UCF: SQL 3.1
Topic: MON_TR_15
Which DBCC command is used to display the status of trace flags?
TRACESTATUS
TRACEON
TRACEOFF
TRACEONS
Answer: TRACESTATUS
Question: 24
UCF: SQL 3.1
Topic: MON_TR_15
Which DBCC command, displays the syntax for any specified dbcc command?
HELPS
HELPSORTS
HELPSORT
HELP
Answer: HELP
Question: 25
UCF: SQL 3.1
Topic: MON_TR_15
Which DBCC command is used to reduce the size of the database?
SHRINKDB
SHRINKDBS
SHRINKDATABASE
None of the Above
Answer: SHRINKDATABASE
Question: 26
UCF: SQL 3.1
Topic: PERF_TUN_15
Is it possible to create a Clustered index without a Primary Key?
Yes
No
It is only possible with a Partner Key
None of the above
Answer: Yes
Question: 27
UCF: SQL 3.1
Topic: PERF_TUN_15
Which SP can be used to perform same actions for all databases?
sp_mseachdb
sp_msforeachdb
sp_msforeachdatabase
All of the above
Answer: sp_msforeachdb
Question: 28
UCF: SQL 3.1
Topic: PERF_TUN_15
Which is the default Isolation level in SQL Server 2000?
Read Uncommitted
Serializable
Read Committed
None of the above
Answer: Read Committed
Question: 29
UCF: SQL 3.1
Topic: PERF_TUN_15
Which is the most restrictive Isolation level?
Read Uncommitted
Read Committed
None of the above
serializable
Answer: serializable
Question: 30
UCF: SQL 3.1
Topic: PERF_TUN_15
How many pages makes an Extent?
8
16
32
64
Answer: 8
Question: 31
UCF: SQL 3.1
Topic: PERF_TUN_15
Which one of them is not a TABLE HINT?
NOLOCK
HOLDLOCK
TABLOCK
DBDLOCK
Answer: DBDLOCK
Question: 32
UCF: SQL 3.1
Topic: PERF_TUN_15
A table exists without a Primary key, however the user needs to create a clustered index on it. Is it possible?
No
Yes
Only a Non-Clustered index can be created
None of the above
Answer: Yes
Question: 33
UCF: SQL 3.1
Topic: PERF_TUN_15
What is a table called, if it does have neither Cluster nor Non-cluster Index?
Heap
Extent
Page
None of the above
Answer: Heap
Question: 34
UCF: SQL 3.1
Topic: PERF_TUN_15
Which component of SQL Server 2005 handles messaging between the Sender and Receiver?
CLR
Service Broker
Data Encryption
HTTP EndPoints
Answer: Service Broker
Question: 35
UCF: SQL 3.1
Topic: PERF_TUN_15
When shall we ignore running UPDATE STATISTICS command?
Deletions of Data
Modifications of Data
Deletion of Database
None of the above
Answer: Deletion of Database
Question: 36
UCF: SQL 3.1
Topic: PERF_TUN_15
How can we view the current collation Settings?
sp_sorts
sp_helpsorts
sp_sort
sp_helpsort
Answer: sp_helpsort
Question: 37
UCF: SQL 3.1
Topic: PERF_TUN_15
Which is the term referrred for periodic flushing of all dirty buffers to stable media?
CHECKPOINT
Deadlocks
Dirty Reads
None of the above
Answer: CHECKPOINT
Question: 38
UCF: SQL 3.1
Topic: PERF_TUN_15
Which Isolation level does not issue shared locks while reading data?
Read Committed
Read Uncommitted
Serializable
None of the above
Answer: Read Uncommitted
Question: 39
UCF: SQL 3.1
Topic: PERF_TUN_15
Which stored procedure can be used to change the object owner?
sp_objowner
sp_changedobjectowner
sp_changeobjectowner
None of the above
Answer: sp_changeobjectowner
Question: 40
UCF: SQL 3.1
Topic: PERF_TUN_15
Which one of them is not an advanced option under SQL Server 2000 query analyzer for troubleshooting purpose?
SET SHOWPLAN_ALL ON
SET SHOWPLAN_TEXT ON
SET STATISTICS IO ON
SET SHOWPLAN IO OFF
Answer: SET SHOWPLAN IO OFF
Question: 41
UCF: SQL 3.1
Topic: STORAGE_10
For which of the following Microsoft applications is DPM v2 designed to provide the best backup and most reliable restore?
Microsoft SQL Server/ Windows file services
Microsoft Exchange Server
SharePoint Portal Server
All of the above
Answer: All of the above
Question: 42
UCF: SQL 3.1
Topic: STORAGE_10
DPM version 2 was specifically built to protect and recover SQL Server using the best aspects of
Continuous data protection (CDP)
Traditional tape backup
Continuous data protection (CDP) with traditional tape backup
Protecting changed files to secondary disk
Answer: Continuous data protection (CDP) with traditional tape backup
Question: 43
UCF: SQL 3.1
Topic: STORAGE_10
How does DPM V2 complete data protection and recovery solution for a core Microsoft SQL server?
Continuously sends the server workloads to a DPM server, which then provides disk-based recovery and tape-based archival
Directly takes Online snapshots and offline tape backups.
Protects changed files in a secondary disk
None of the above
Answer: Continuously sends the server workloads to a DPM server, which then provides disk-based recovery and tape-based archival
Question: 44
UCF: SQL 3.1
Topic: STORAGE_10
DPM fails when protecting SQL Server clusters,SQL Servers with Log Shipping or Simple Recovery Model configurations and mirrored SQL 2005 servers.
True
False
None of the above
Answer: False
Question: 45
UCF: SQL 3.1
Topic: STORAGE_10
How do we restore Microsoft SQL Server with Microsoft Data Protection Manager (DPM)?
Restore a SQL database directly back to the original server/ recovery database on the original server
Copy database files to an alternate server
Copy the database file to tape
All of the above
Answer: All of the above
Question: 46
UCF: SQL 3.1
Topic: STORAGE_10
Unique to DPM v2 is lossless recovery of SQL Server data
TRUE
False
patially true
None of the above
Answer: TRUE
Question: 47
UCF: SQL 3.1
Topic: STORAGE_10
RAID stands for
Redundant Array of Inexpensive Disks
Redundant Array of Inexpensive Disks
Redundant Array of Inexpensive/ Independent Disks
Redundant Array of Important Disks
Answer: Redundant Array of Inexpensive/ Independent Disks
Question: 48
UCF: SQL 3.1
Topic: STORAGE_10
What were the Motivating factors for RAID to be conceptualized?
An attempt to bridge the widening gap between Performance of Processor v/s Performance of disks
To support faster data access required by video and multi-media applications – warranted new storage architecture
When disks were small and expensive, an attempt to create a big virtual disk in order to provide a large amount of data.
All of the above
Answer: All of the above
Question: 49
UCF: SQL 3.1
Topic: STORAGE_10
RAID can recover data during which of the following scenarios?
System crashes while I/Os are in progress on RAID or a single disk in RAID configuration fails
System crashes and is followed by a disk failure or a double disk failure occurs
Disk fails followed by uncorrectable biterror during reconstruction.
All of the above
Answer: System crashes while I/Os are in progress on RAID or a single disk in RAID configuration fails
Question: 50
UCF: SQL 3.1
Topic: STORAGE_10
Which of the following scenarios does RAID lose data?
System crashes while I/Os are in progress on RAID
A single disk in RAID configuration fails
System crashes and is followed by a disk failure or a double disk failure occurs
None of the above
Answer: System crashes and is followed by a disk failure or a double disk failure occurs
SQL 3.2
Question: 1
UCF: SQL 3.2
Topic: CONCEPT_12
Which components are part of Report Server
Report Processor
Scheduling and Delivery Processor
Report Manager
Report Processor, Scheduling and Delivery Processor Report Server Database
Answer: Report Processor, Scheduling and Delivery Processor Report Server Database
Question: 2
UCF: SQL 3.2
Topic: CONCEPT_12
The Report Processor gets the report definition from
Report Definition File
Report Database
Rendering extension
None of these
Answer: Report Database
Question: 3
UCF: SQL 3.2
Topic: CONCEPT_12
PollingInterval configuration parameter of RSReportServer.config file interacts with
Report Designer
Scheduling and Delivery Processor
Data extension
Report Processor
Answer: Scheduling and Delivery Processor
Question: 4
UCF: SQL 3.2
Topic: CONCEPT_12
Subscription and schedule definitions for a report is stored in
Report Definition File
ReportServerTempDb Database
ReportServer Database
RSReportServer.config
Answer: ReportServer Database
Question: 5
UCF: SQL 3.2
Topic: CONCEPT_12
Reporting Services is implemented as
SQL Server Service
Web Service
SQL Server Integration Services
Microsoft Windows service and as a Web service
Answer: Microsoft Windows service and as a Web service
Question: 6
UCF: SQL 3.2
Topic: CONCEPT_12
Reporting Services requires this to connect to local and remote Report Server
SQL Server Agent
Web Service
SQL Server
Windows Management Instrumentation
Answer: Windows Management Instrumentation
Question: 7
UCF: SQL 3.2
Topic: CONCEPT_12
Report definition in Report Definition file (.rdl) file is written in
VB.net
C#
XML
JavaScript
Answer: XML
Question: 8
UCF: SQL 3.2
Topic: CONCEPT_12
Request for a cached report or snapshot is handled by
Report Manager
Report Processor
Report Builder
Scheduling Delivery Processor
Answer: Report Processor
Question: 9
UCF: SQL 3.2
Topic: CONCEPT_12
Pushing of reports to E-mail is done by this component
SQL Server Database Mail
SMTP Server
Scheduling and Delivery Processor
All of these
Answer: Scheduling and Delivery Processor
Question: 10
UCF: SQL 3.2
Topic: CONCEPT_12
RSReportServer.config file is found in this folder
90\shared
\Reporting Services\ReportServer
Instid\Reporting Services\RSTempfiles
Instid\Reporting Services\reportManager
Answer: \Reporting Services\ReportServer
Question: 11
UCF: SQL 3.2
Topic: CONCEPT_12
Report server and Report Manager are _ applications
VB.net
C#
ASP.NET
None of these
Answer: ASP.NET
Question: 12
UCF: SQL 3.2
Topic: CONCEPT_12
How many peformance counter objects installed as part of Reporting Services
Two
One
Three
Five
Answer: Two
Question: 13
UCF: SQL 3.2
Topic: MIRROR_10
Sql 2005 Standard editions installed in a server without applying servicepacks .To enable mirroring we need run the trace flag
1400
1401
1402
1403
Answer: 1400
Question: 14
UCF: SQL 3.2
Topic: MIRROR_10
Default port for creating Mirroring is
5021
5022
5023
5024
Answer: 5022
Question: 15
UCF: SQL 3.2
Topic: MIRROR_10
Operating modes in mirroring has been classified into
1
2
3
4
Answer: 2
Question: 16
UCF: SQL 3.2
Topic: MIRROR_10
Roles used in EndPoints are
1
2
3
4
Answer: 4
Question: 17
UCF: SQL 3.2
Topic: MIRROR_10
Endpoints informations can be reterived from
tcp_endpoint
endpoints_tcp
endpoints_tcpdetail
tcp_endpoints
Answer: tcp_endpoints
Question: 18
UCF: SQL 3.2
Topic: MIRROR_10
Automatic failover duration can be increased in mirroring by the paramter
partner duration
partner timeout
partner time
partner =50
Answer: partner timeout
Question: 19
UCF: SQL 3.2
Topic: MIRROR_10
when principal database was not able to failover mirror database then mirror status will be
Pending_failover
suspended
unsynchronized
expozed
Answer: Pending_failover
Question: 20
UCF: SQL 3.2
Topic: MIRROR_10
when principal database was not able to communicate with mirror and withness then the mirror state will be
suspended
unsynchronized
expozed
exposed
Answer: exposed
Question: 21
UCF: SQL 3.2
Topic: MIRROR_10
Mirroring state can be viewed by using the table
Mirroring_view
database_Mirroring_state_change
Mirroring_state_change
Mirroring_change_state
Answer: database_Mirroring_state_change
Question: 22
UCF: SQL 3.2
Topic: MIRROR_10
In ADO.Net To specify the mirror parameter we have to use the parameter
Failover partner
Partner failover
mirror server
Mirror Partner
Answer: Failover partner
Question: 23
UCF: SQL 3.2
Topic: REPORT_13
which is the method by which SSRS CANNOT be deployed
Using Report Builder
TSQL
Visual Studio 2008
rs.exe
Answer: TSQL
Question: 24
UCF: SQL 3.2
Topic: REPORT_13
While deploying reports using BIDS, which is the property that is NOT available for configuring
Deploy
Debug
DebugLocal
Release
Answer: Deploy
Question: 25
UCF: SQL 3.2
Topic: REPORT_13
Which utility uses script files written in VB .NET to access the Web service and allows us to call any of the published methods.
spsrvs.exe
msrs.exe
ssrs.exe
rs.exe
Answer: rs.exe
Question: 26
UCF: SQL 3.2
Topic: REPORT_13
What does the following command do rs -i Reports.rss -s https://iag.wipro.com/whalecom59fde2e7b008764732/whalecom0/reportserver
Deploy Report
Checks the existence of report in the given URL
Publish Report
Checks whether the report is error free
Answer: Publish Report
Question: 27
UCF: SQL 3.2
Topic: REPORT_13
You must be logged in as a user who is _ to add new schedules.
Member of the SSRS Deploy Administrator role
DBO permission on the ReportingServices DB
Sysadmin
Member of the SSRS System Administrator role
Answer: Member of the SSRS System Administrator role
Question: 28
UCF: SQL 3.2
Topic: REPORT_13
when a job, such as a subscription or an execution snapshot, is scheduled to run within SSRS, a SQL Server job is created using the SQL Server Agent. Jobs can be monitored through
Report Builder
Report Manager
Query the ReportingServices DB
Report Server
Answer: Report Manager
Question: 29
UCF: SQL 3.2
Topic: REPORT_13
which is NOT true with respect to a cached report
Caching a report sometimes result in less performance as multiple copies of the report needs to be stored
The subsequent user must access the report within the time interval before the cached report is set to expire.
If the cached report has parameters that change during subsequent executions, and each user receives a new report based on that parameter, each report that s generated becomes a cached copy specific to the parameter value
The report s data source isn t set to Windows authentication or to prompt the user for login credentials.
Answer: Caching a report sometimes result in less performance as multiple copies of the report needs to be stored
Question: 30
UCF: SQL 3.2
Topic: REPORT_13
which is not ture with respect to the Snap Shot of a report
point in time
generated as execution snapshots
can contain multiple copies of a report at given points in time.
can be updated using methods from .Net Frame work.
Answer: can be updated using methods from .Net Frame work.
Question: 31
UCF: SQL 3.2
Topic: REPORT_13
Which CANNOT be considered as a management role in SSRS
Content management
Performance
Report Authoring
Report execution
Answer: Report Authoring
Question: 32
UCF: SQL 3.2
Topic: REPORT_13
Using Report Server Web service, you can create custom applications that control all aspects of the server and cover the entire reporting life cycle. Which among the list cannot be controlled?
Folder and resource management
Report rendering
Report history
Report Authoring
Answer: Report Authoring
Question: 33
UCF: SQL 3.2
Topic: REPORT_13
Which is NOT an SSRS 2008 Class for Use with WMI
MSReportServer_ ConfigurationSetting
MSReportServer_ ReportSecuritySetting
MSReportServerReportManager_ ConfigurationSetting
All the above
Answer: MSReportServer_ ReportSecuritySetting
Question: 34
UCF: SQL 3.2
Topic: REPORT_13
Which among the list is not part of SSRS security model
Data encryption
Report audits
Authentication and user access
Report Delivery
Answer: Report Delivery
Question: 35
UCF: SQL 3.2
Topic: REPORT_13
SSRS natively supports encrypting the data it stores in the
ReportServer DB
ReportServer$SQL2008TempDB
In configuration files located on IIS server
In configuration files located on DB server
Answer: ReportServer DB
Question: 36
UCF: SQL 3.2
Topic: TU_15
Litespeed can be converted to SQL native file by using the command
Extractor
Split
Convert
Utility
Answer: Extractor
Question: 37
UCF: SQL 3.2
Topic: TU_15
While Converting Litespeed to Native parameter N denotes
to encrypt
Number of files to generate
path of Native backupfile
None of the above
Answer: Number of files to generate
Question: 38
UCF: SQL 3.2
Topic: TU_15
Default Encryption Level in Litespeed is
56 bit RC2
40 bit RC2
80 bit RC2
60 bit RC2
Answer: 40 bit RC2
Question: 39
UCF: SQL 3.2
Topic: TU_15
File Corrupted can be verified by using the command
checksumonly
checksumonly_check
xp_checksum
checksumutility
Answer: xp_checksum
Question: 40
UCF: SQL 3.2
Topic: TU_15
In Litespeed audit log can be viewed by the SP
view_sls_auditlog
xp_sls_auditlog
xp_sls_view_auditlog
xp_view_sls_auditlog
Answer: xp_view_sls_auditlog
Question: 41
UCF: SQL 3.2
Topic: TU_15
Average Compression ration in Litespeed can be
70-75
75-80
80-85
90-95
Answer: 75-80
Question: 42
UCF: SQL 3.2
Topic: TU_15
Percentage of Back up Speed higher in Litespeed when compare to Native backup
60
25
50
75
Answer: 50
Question: 43
UCF: SQL 3.2
Topic: TU_15
Failed to login into server in Litespeed can be resolved by removing
SQl service accounts
Administrators
BUILTIN\Administrators
None of the above
Answer: BUILTIN\Administrators
Question: 44
UCF: SQL 3.2
Topic: TU_15
Query timeout occurs when you run extended stored procedure in Litespeed can be resolved by checking permissions in
checking sql server
checking registry
checking settings in database
None of the above
Answer: checking registry
Question: 45
UCF: SQL 3.2
Topic: TU_15
Error opening registry key for SQLLiteSpeed can be resolved by
User and SQL service account should be sync in privlieges
user should have sysadmin
User should be a dbowner
None of the above
Answer: User and SQL service account should be sync in privlieges
Question: 46
UCF: SQL 3.2
Topic: TU_15
Restoring File backup may arise issues due to
File is not valid
File is corrupted one
File is not an litespeed format
File is already in use
Answer: File is not an litespeed format
Question: 47
UCF: SQL 3.2
Topic: TU_15
File header can be checked by the command
xp_restore_headeronly
xp_restore_header
xp_restore_headerlist
restore_headeronly
Answer: xp_restore_headeronly
Question: 48
UCF: SQL 3.2
Topic: TU_15
No of editions in litespeed are
2
3
4
5
Answer: 4
Question: 49
UCF: SQL 3.2
Topic: TU_15
Progress of current sql activity can be viewed by using the command
xp_ReadProgress
xp_slsReadProgress
xp_slsRead
xp_slsReaProgress
Answer: xp_slsReadProgress
Question: 50
UCF: SQL 3.2
Topic: TU_15
Table restore can be done by using the command
xp_obj_recover
xp_obj_recovery
object_recovery
xp_object_recovery
Answer: xp_object_recovery
SQL 4.1
Question: 1
UCF: SQL 4.1
Topic: MT_15
Which of the below Reporting tools provides complex reporting but requires in-depth understanding of the data
Report Builder
Report Designer
Report Manager
Report Server
Answer: Report Designer
Question: 2
UCF: SQL 4.1
Topic: MT_15
In SQL Server2005, The purpose of making This a Shared Data Source checkbox whilh creating a report through Report Builder is
to make it available to the future reporting apps
to share the data across networkt
Answer: to make it available to the future reporting apps
Question: 3
UCF: SQL 4.1
Topic: MT_15
While modifying a report, what is not possible?
You can change the available data or the sort order for the report by modifying the query on the Data tab.
You can resize or rearrange controls on the Layout tab.
You can use the Properties window to change properties of individual controls including their font, alignment, colors, and so on.
You can include more tables ot join columns
Answer: You can include more tables ot join columns
Question: 4
UCF: SQL 4.1
Topic: MT_15
You are the local administrator of a windows server which hosts a SQL Server Databse Engine and SSRS. You created a report in the Report Designer and now you want to publish it to the Reporting server. You recive an error that zour login has insufficient privileges. In which security role your login need to be added in SSRS?
Add your account to the sysadmin fixedserver role of the SQL Server
Add the Windows user ASPNET to the Administrator group of the local server.
Grant your login db_owner access to ReportServerTempdb database
Answer: Add the Windows user ASPNET to the Administrator group of the local server.
Question: 5
UCF: SQL 4.1
Topic: MT_15
To publish a report,In Configuration Manager, Active Soultion Configuration must be selected to ————
Debug
Debuglocal
Production
Any of theabove
Answer: Production
Question: 6
UCF: SQL 4.1
Topic: MT_15
While publishing a report, ————– should be selected from the configuration Manager
Build only
Deploy only
Both Build and Deploy
No need to select explicitily. It will automatically pick up
Answer: Both Build and Deploy
Question: 7
UCF: SQL 4.1
Topic: MT_15
Which of the below Reporting tools is intended for Business Analysts and non-developers
Report Builder
Reort Manager
Report Designer
None of the above
Answer: Report Builder
Question: 8
UCF: SQL 4.1
Topic: MT_15
What does a Data model contai? (Choose all that apply)
Data Sources connect the data model to actual data.
Data Source Views draw data from data sources.
Report Models contain entities that end users can use on reports.
All of the above
Answer: All of the above
Question: 9
UCF: SQL 4.1
Topic: MT_15
Which is not a rule in the Data Model generation?
Create entities for all the tables
Create indexed views
Create numeric aggregates
Create roles
Answer: Create indexed views
Question: 10
UCF: SQL 4.1
Topic: MT_15
In addition to viewing data, what are the other tasks performed bz Report Manager
Enables view historical snapshots
Enables Subscription of reports to the end users
Only A)
Both a and b
Answer: Both a and b
Question: 11
UCF: SQL 4.1
Topic: MT_15
Installing SSRS creates two new virtual directories under the default website on IIS. They are
Reports and ReportServer
ReportTemp and ReportServer
Report and ReportserverTemp
None of the above
Answer: Reports and ReportServer
Question: 12
UCF: SQL 4.1
Topic: MT_15
Which is true about Reports site
hosts a web service for running and managing reports
allows you upload reports from a browser and run reports over the web.
Both A) and B)
None of the above
Answer: allows you upload reports from a browser and run reports over the web.
Question: 13
UCF: SQL 4.1
Topic: MT_15
It is possible to add new parameters, maz or maz not be in the query in the Lazout of Report Designer
Yes
No
Cant Say
Answer: Yes
Question: 14
UCF: SQL 4.1
Topic: MT_15
Which is not a SSIS object
Event handler
Data and Control flows
Tasks
Connections
Answer: Tasks
Question: 15
UCF: SQL 4.1
Topic: MT_15
What is true about connection manager in SSIS?
Controls the data flow
Integrate different data sources into packages.
Handles event
None of the above
Answer: Integrate different data sources into packages.
Question: 16
UCF: SQL 4.1
Topic: PT_15
What factor is a scale to measure the performance of a query
Operators
Query Cost
Indexes
Statistics
Answer: Query Cost
Question: 17
UCF: SQL 4.1
Topic: PT_15
What tool would you use to analyze a query and its cost
Estimate Query Plan
Profiler
Index tuning Wizard
all of the above
Answer: Estimate Query Plan
Question: 18
UCF: SQL 4.1
Topic: PT_15
To identify a slow performing query what data would you look in profiler
text
blocked by
duration
hostname
Answer: duration
Question: 19
UCF: SQL 4.1
Topic: PT_15
Currently running CPU intensive batches/procedures can be found using
dm_exec_query_plan
Edison
dm_exec_query_stats
dm_exec_cached_plans
Answer: dm_exec_query_stats
Question: 18
UCF: SQL 4.1
Topic: PT_15
To identify a slow performing query what data would you look in profiler
text
blocked by
duration
hostname
Answer: duration
Question: 21
UCF: SQL 4.1
Topic: PT_15
On a case sensitive database, out of the two queries which one would be faster A) SELECT column_name FROM table_name WHERE LOWER(column_name) = name B) SELECT column_name FROM table_name WHERE column_name = NAME or column_name = name
a
b
Both are same
none
Answer: b
Question: 22
UCF: SQL 4.1
Topic: PT_15
What is the order of execution in a SELECT query using WHERE, GROUP BY, HAVING
GROUP BY, HAVING, WHERE
WHERE, HAVING, GROUP BY
WHERE, GROUP BY, HAVING
HAVING, WHERE, GROUP BY
Answer: WHERE, GROUP BY, HAVING
Question: 23
UCF: SQL 4.1
Topic: PT_15
While comparing data in two tables which of the following function is a top performer
Not EXISTS
NOT IN
LEFT Join
none
Answer: Not EXISTS
Question: 24
UCF: SQL 4.1
Topic: PT_15
Sorting often occurs when any of the following Transact-SQL statements are executed:
ORDER BY
UNION
SELECT DISTINCT
all of the above
Answer: all of the above
Question: 25
UCF: SQL 4.1
Topic: PT_15
Which of the below query peforms better A) SELECT customer_number, customer_name FROM customer WHERE customer_number in (1000, 1001, 1002, 1003, 1004) B) SELECT customer_number, customer_name FROM customer WHERE customer_number BETWEEN 1000 and 1004 C) SELECT customer_number, customer_name FROM customer WHERE customer_number =1000 or customer_number = 1001 or customer_number = 1002 or customer_number = 1003 or customer_number = 1004
a
b
c
b and c
Answer: b
Question: 26
UCF: SQL 4.1
Topic: PT_15
How would you find out when an index/statistics was last updated
stats_upd_date()
update_stats()
stats_date()
sp_updatestats
Answer: stats_date()
Question: 27
UCF: SQL 4.1
Topic: PT_15
In a Query plan Index Seak is used when
it is highly selective queries
non clustered indexes present
it is a heap
no statistics is present
Answer: it is highly selective queries
Question: 28
UCF: SQL 4.1
Topic: PT_15
SYS.DM_DB_INDEX_OPERATIONAL_STATS is helpful in
identifying when a table/index is last used since start of service
identify lock IO contention for a table/inded
identifying how many times a table/index is used since start of service
Identify the Fragmentation level
Answer: identify lock IO contention for a table/inded
Question: 29
UCF: SQL 4.1
Topic: PT_15
How would you find the Fragmentation level of an Index/Table?
sys.dm_db_index_usage_stats
sys.dm_db_index_operational_stats
sys.dm_db_index_fragment_stats
sys.dm_db_index_physical_stats
Answer: sys.dm_db_index_physical_stats
Question: 30
UCF: SQL 4.1
Topic: PT_15
What is covering index?
an index which includes all the columns of the table
index which includes first and last columns of the table
Index which includes all Char columns of the table
when all the referenced columns in SELECT, WHERE are part of INDEXKEY
Answer: when all the referenced columns in SELECT, WHERE are part of INDEXKEY
Question: 31
UCF: SQL 4.1
Topic: SOL_5
Which of the statement is True about DB Back-up s having different SQL Version
Backups created on later versions of SQL Server cannot be restored to earlier versions of SQL Server
Back-up is possible if the SQL Server DB have no Consistency errors in later
DB Back-up in later version occupy more disk space
None of these
Answer: Backups created on later versions of SQL Server cannot be restored to earlier versions of SQL Server
Question: 32
UCF: SQL 4.1
Topic: SOL_5
Database can be Restored with
Having Differential Tlogs Back-up s
You have all the transactions stored in .ldf you will be able to restore the database completely without .mdf and .ndf files
Full Back-up of DB is Mandatory to Restore the DB
All of these
Answer: Full Back-up of DB is Mandatory to Restore the DB
Question: 33
UCF: SQL 4.1
Topic: SOL_5
Which RECOVERY model best suites the Point-in time Recovery of data
Simple
Full
both Simple and Full
All of These
Answer: Full
Question: 34
UCF: SQL 4.1
Topic: SOL_5
Transaction logs are required for
Data Consistency
Avoid Data loss
Check Point for Transactions
All of the these
Answer: All of the these
Question: 35
UCF: SQL 4.1
Topic: SOL_5
Can we restore a SQL Server 2000 database backup to a SQL Server 7.0 server?
yes, DBRestore is possible if the SQL Server DB have no Consistency errors
No. The only way to move a SQL Server 2000 database to a SQL Server 7.0 server is by transferring the data using a method such as DTS, bcp, or use of a query between linked servers.
yes ..Restore DB is possible if .ndf and .ldf files are present though .mdf files are missing
Yes ..Database is in Full Recovery mode can be Restored
Answer: No. The only way to move a SQL Server 2000 database to a SQL Server 7.0 server is by transferring the data using a method such as DTS, bcp, or use of a query between linked servers.
Question: 36
UCF: SQL 4.1
Topic: SSIS_5
SSIS is a version upgrade of DTS from SQL 2000?
True
Entirely New product
False
An upgrade of MSDTC
Answer: Entirely New product
Question: 37
UCF: SQL 4.1
Topic: SSIS_5
You cannot edit DTS packages in Business Intelligence Development Studio You can edit SSIS packages only in BI Development Studio?
True
False
Partially True
None of the above
Answer: True
Question: 38
UCF: SQL 4.1
Topic: SSIS_5
To view DTS packages in Management Studio connect to an instance of SQL Server and look in the Object Browser under?
from Management to legacy to Database maintenance plans
from Management to legacy to SQL Mail
None of the Above
from Management to legacy to Data Transformation Services
Answer: from Management to legacy to Data Transformation Services
Question: 39
UCF: SQL 4.1
Topic: SSIS_5
You can install DTS and SSIS on the same computer and run them?
You can install but cant run
cant install both
Can install and run
cant install both
Answer: Can install and run
Question: 40
UCF: SQL 4.1
Topic: SSIS_5
If you are a previous or current user of Microsoft SQL Server 2000 Data Transformation Services (DTS) you have the following options when you install Microsoft SQL Server 2005 Integration Services (SSIS)?
Installing Support for SQL Server 2000 Data Transformation Services Packages
Upgrading an earlier version of Data Transformation Services to SSIS
Migrating existing Data Transformation Services packages to SSIS
All of the above
Answer: All of the above
Question: 41
UCF: SQL 4.1
Topic: STO_10
Which one of the following is not an important area which needs attention while doing capacity planning for your sql servers
Disk storage
Memory
Processor efficiency
OS Version
Answer: OS Version
Question: 42
UCF: SQL 4.1
Topic: STO_10
What are the most important factors you look for while purchasing CPUs for your Server
CPU Speed, CPU Made
CPU Speed, L2 Cache
CPU Made, L2 Cache
CPU Speed only
Answer: CPU Speed, L2 Cache
Question: 43
UCF: SQL 4.1
Topic: STO_10
While ordering for a RAID 5 Array, which of the following gives better read/write performance
4 – 36GB drives
6 – 17GB drives
7 – 18GB drives
8 – 19 GB drives
Answer: 7 – 18GB drives
Question: 44
UCF: SQL 4.1
Topic: STO_10
Which is the best RAID option available for SQL Server databases, considering you want optimum I/O performance and your databases are more write intensive.
RAID 1
RAID 2
RAID 5
RAID 10
Answer: RAID 10
Question: 45
UCF: SQL 4.1
Topic: STO_10
Ideally the OS, OS Swap file and other SQL Server executables should be located on which RAID Levels
RAID 1
RAID 2
RAID 10
RAID 5
Answer: RAID 1
Question: 46
UCF: SQL 4.1
Topic: STO_10
Tempdb data files are avoided on this RAID level
RAID 2
RAID 5
RAID 10
RAID 1
Answer: RAID 5
Question: 47
UCF: SQL 4.1
Topic: STO_10
You have already planned for your Tempdb size requirements but you want to make sure the size doesn t reach its limit and follow some best practices, which one of the following is not a best practice
Set the recovery model of tempdb to simple
Preallocate space for all tempdb files by setting the file size to a larger value
Always place the tempdb files on the same drive as your database log files reside
Create as many tempdb files as needed to maximize disk bandwidth
Answer: Always place the tempdb files on the same drive as your database log files reside
Question: 48
UCF: SQL 4.1
Topic: STO_10
Which of the following is not one of the factors that help you in estimating your database size
Determine the no. of rows
Determine the size of each row
calculate the no. of data pages required
determine the no. of tables
Answer: determine the no. of tables
Question: 49
UCF: SQL 4.1
Topic: STO_10
How to get the actual storage space for your table once you calculated the no. of data pages required to store your table
mulitply the number by 8096
multiply the number by 1024
multiply the number by 2048
multiply the number by 4096
Answer: mulitply the number by 8096
Question: 50
UCF: SQL 4.1
Topic: STO_10
What is the maximum no. of cluster nodes supported by Microsoft Windows server 2003
2
4
8
12
Answer: 8
SQL 1.1
Question: 1
UCF: SQL 1.1
Topic: BASIC ADMIN_8
Which of the following backup model is used, if we do not need transaction log backups?
A) Simple
B) Bulk-Logged
C) Full
D) Simple and Full
Answer: A) Simple
Question: 2
UCF: SQL 1.1
Topic: BASIC ADMIN_8
__ role has the capability to back up the database.
A) db_backuplogger
B) db_backup
C) db_backupoperator
D) db_backadmin
Answer: C) db_backupoperator
Question: 3
UCF: SQL 1.1
Topic: BASIC ADMIN_8
To recover a database to the point of failure which recovery model can be used?
A) Simple
B) Bulk-Logged
C) Full
D) Mirror
Answer: C) Full
Question: 4
UCF: SQL 1.1
Topic: BASIC ADMIN_8
Which of the following database consists of system tables that keep track of the server installation as a whole and all other databases that are subsequently created
A) tempdb database
B) master database
C) model database
D) pubs database
Answer: B) master database
Question: 5
UCF: SQL 1.1
Topic: BASIC ADMIN_8
CTE stands for
A) Correct Table Extract
B) Constant Table Expressions
C) Common Table Expressions
D) none of the above
Answer: C) Common Table Expressions
Question: 6
UCF: SQL 1.1
Topic: BASIC ADMIN_8
SQL stands for
A) Strong Question Language
B) Structured Question Language
C) Structured Query Language
D) none of the above
Answer: C) Structured Query Language
Question: 7
UCF: SQL 1.1
Topic: BASIC ADMIN_8
Which SQL keyword is used to retrieve a maximum value?
A) TOP
B) MOST
C) UPPER
D) MAX
Answer: D) MAX
Question: 8
UCF: SQL 1.1
Topic: BASIC ADMIN_8
We refer to a join as self-join when
A) We are joining more than 2 tables
B) We are joining table to itself
C) We are using left and right join together
D) None of the above
Answer: B) We are joining table to itself
Question: 9
UCF: SQL 1.1
Topic: MON TRBL_5
Aamir was recently moved to the ETL group in a company. He should be able to modify data on the all the databases available on the SQL Server. Which of the following database role should he be a member o
A) db_datawriter
B) db_denydatareader
C) db_datareader
D) all of above
Answer: A) db_datawriter
Question: 10
UCF: SQL 1.1
Topic: MON TRBL_5
Sirisha changes the definition of a view by issuing a ALTER VIEW statement. Which of the following are changed? 1. stored procedure 2. Triggers 3. permissions on the view
A) 1 2
B) 23
C) 13
D) 1,23
Answer: D) 1,23
Question: 11
UCF: SQL 1.1
Topic: MON TRBL_5
Ajay wants to check whether the latest service pack has been installed on the SQL Server. Which command will give Ajay the required output?
A) @@servername
B) @@server
C) @@servicepack
D) @@version
Answer: D) @@version
Question: 12
UCF: SQL 1.1
Topic: MON TRBL_5
Which stored procedure do you run to identify locks on SQL Server?
A) sp_identifylocks
B) sp_livelocks
C) sp_who2
D) sp_deadlocks
Answer: C) sp_who2
Question: 13
UCF: SQL 1.1
Topic: MON TRBL_5
Which stored procedure do you use to set the compatibility of a database to a previous version of SQL Server?
A) sp_dblevel
B) sp_dbcmptlevel
C) sp_cmptlevel
D) sp_level
Answer: B) sp_dbcmptlevel
Question: 14
UCF: SQL 1.1
Topic: NETWORKING_2
Which utility in windows 2003 Enterprise edition is used to copy big files across different network
A) Xcopy
B) Copy
C) robocopy
D) networkcopy
Answer: C) robocopy
Question: 15
UCF: SQL 1.1
Topic: NETWORKING_2
Before any data can be transmitted through the TCP protocol, a reliable connection must be established. What is the process that TCP uses to establish this connection called
A) network handshake
B) windows handshake
C) two way handshake
D) three way handshake
Answer: D) three way handshake
Question: 16
UCF: SQL 1.1
Topic: SRV BASICS_5
With SQL, how can you return the number of records in the “Employee” table?
A) SELECT COUNT() FROM Employee B) SELECT COLUMNS() FROM Persons
C) SELECT COLUMNS() FROM Persons
Answer: A) SELECT COUNT(*) FROM Employee
Question: 17
UCF: SQL 1.1
Topic: SRV BASICS_5
Which SQL statement is used to return only distinct values?
A) SELECT ALL
B) SELECT UNIQUE
C) SELECT DISTINCT
D) SELECT DIFFERENT
Answer: C) SELECT DISTINCT
Question: 18
UCF: SQL 1.1
Topic: SRV BASICS_5
With SQL, how do you select a column named “FirstName” from a table named “Employee”?
A) EXTRACT FirstName FROM Employee
B) SELECT Employee.FirstName
C) OPEN Employee.FirstName
D) SELECT FirstName FROM Employee
Answer: D) SELECT FirstName FROM Employee
Question: 19
UCF: SQL 1.1
Topic: SRV BASICS_5
which of the following fall under Constraints?
A) NOT NULL CHECK
B) UNIQUE
C) PRIMARY KEY, FOREIGN KEY
D) All the above
Answer: D) All the above
Question: 20
UCF: SQL 1.1
Topic: SRV BASICS_5
How to know which index a table is using?
A) SELECT table_name,index_name FROM user_constraints
B) SELECT index_name FROM indexes
C) SELECT table_name,index_name FROM user
D) SELECT table_name FROM indexes_tables
Answer: A) SELECT table_name,index_name FROM user_constraints
Question: 21
UCF: SQL 1.1
Topic: TOOLS UTIL_5
what is the order for database restore in simple recovery
A) 1- Recent full backup 2- recent diff backup
B) 1- recent full backup 2- recent diff backup 3- all the Tran backups from the latest diff backup
C) 1- recent full backup 2- all the diff backups taken since latest full backup
D) 1- recent full backup 2- all the diff backups taken since latest full backup 3- all the Tran backups from the latest diff backup
Answer: A) 1- Recent full backup 2- recent diff backup
Question: 22
UCF: SQL 1.1
Topic: TOOLS UTIL_5
what immediate actions need to be taken for log file full issue for db in simple recovery?
A) Add another log file in another spacious drive
B) Increase the drive space where logfile resides
C) take a logbackup and shrink the file
D) All the above
Answer: A) Add another log file in another spacious drive
Question: 23
UCF: SQL 1.1
Topic: TOOLS UTIL_5
what immediate action you take if db is switched from simple to full
A) take full backup before switching from simple to full
B) take full backup after switching from simple to full
C) take log backup after switching from simple to full
D) Both 2 3
Answer: B) take full backup after switching from simple to full
Question: 24
UCF: SQL 1.1
Topic: TOOLS UTIL_5
which command does not log the deletion of rows
A) truncate
B) delete
C) drop
D) None
Answer: A) truncate
Question: 25
UCF: SQL 1.1
Topic: TOOLS UTIL_5
which command is used to check the database space
A) sp_spaceused
B) sp_space
C) dbcc sqlperf(space)
D) dbcc sqlperf(logspace)
Answer: A) sp_spaceused
SQL 2.1
Question: 1
UCF: SQL 2.1
Topic: ARCHITECTURE_17
SQL Server 2005 Developer Edition has the same features as _______
A) SQL Server 2005 Enterprise Edition
B) SQL Server 2005 Workgroup Edition
C) SQL Server 2005 Express Edition
D) SQL Server 2005 Standard Edition
Answer: A) SQL Server 2005 Enterprise Edition
Question: 2
UCF: SQL 2.1
Topic: ARCHITECTURE_17
syslogins table is the part of
A) MSDB Database
B) TempDB Database
C) Master Database
D) Model Database
Answer: C) Master Database
Question: 3
UCF: SQL 2.1
Topic: ARCHITECTURE_17
The HashBytes() in SQL Server 2005
A) is used to transform data passed into the function, but you cannot recover the original value
B) is used to display the cluomunvalue
C) is used to store the hash value
D) is used in the database creation
Answer: A) is used to transform data passed into the function, but you cannot recover the original value
Question: 4
UCF: SQL 2.1
Topic: ARCHITECTURE_17
The maximum number of Secondary data files in a database
A) 32767
B) 32676
C) 32776
D) 32766
Answer: D) 32766
Question: 5
UCF: SQL 2.1
Topic: ARCHITECTURE_17
There are triggers for
A) DDL statements
B) DML Statements
C) DDL and DML statements
D) None of the above
Answer: C) DDL and DML statements
Question: 6
UCF: SQL 2.1
Topic: ARCHITECTURE_17
what actions should be taken if sql server is not connecting remotely?
A) check the firewall settings and add sql port an exception. Then add the alias in the client utility.
B) check the firewall settings and add the sql port as an exception. Then add the alias in the server utility.
C) Both 1 2
D) none
Answer: A) check the firewall settings and add sql port an exception. Then add the alias in the client utility.
Question: 7
UCF: SQL 2.1
Topic: ARCHITECTURE_17
What are the advantages of having multiple instances of SQL Server?I.Can apply different service packs for different instances II. Can increase availability of the database III. Flexibility to allocate one instance per application IV. Improves the performance of the server
A) All the above
B) I II only
C) I III only
D) II III only
Answer: C) I III only
Question: 8
UCF: SQL 2.1
Topic: ARCHITECTURE_17
What are the different kinds of files that can be created in a database? I. Primary data File II. Secondary data file III. Secondary Log file IV. Log File
A) I II only
B) I, II III only
C) I, II IV only
D) All the above
Answer: C) I, II IV only
Question: 9
UCF: SQL 2.1
Topic: ARCHITECTURE_17
what are the order of three phases in database recovery?
A) 1- analysis 2- redo 3- undo
B) 1- redo 2- undo 3- analysis
C) 1- undo 2- redo 3-analysis
D) 1- undo 2- redo 3- analysis
Answer: A) 1- analysis 2- redo 3- undo
Question: 10
UCF: SQL 2.1
Topic: ARCHITECTURE_17
What do ACID properties stand for?
A) Atomicity, Consistency, Intermediate, Dependent
B) Attainable, Consistency, Isolation, Dependent
C) Atomicity, Consistency, Isolation, Durability
D) Attainable, Consistency, Intermediate, Durability
Answer: C) Atomicity, Consistency, Isolation, Durability
Question: 11
UCF: SQL 2.1
Topic: ARCHITECTURE_17
What does DML stand for
A) Data Mode Lane
B) Data Manipulation Language
C) Different Mode Level
D) Data Model Language
Answer: B) Data Manipulation Language
Question: 12
UCF: SQL 2.1
Topic: ARCHITECTURE_17
What happens to a deferred transaction if the filegroup that it is holding it up becomes defunct?
A) The transaction is unaffected. The deferred status remains because it was not properly resolved.
B) The transaction is moved out of the deferred state.
C) The database becomes suspect.
D) The filegroup cannot be moved into the defunct state until the transaction is resolved.
Answer: B) The transaction is moved out of the deferred state.
Question: 13
UCF: SQL 2.1
Topic: ARCHITECTURE_17
What is a bookmark lookup?
A) When the query processor uses a non-clustered index to match a SARG.
B) When the query processor uses a non-clustered index to lookup the data in the clustered index.
C) When the query processor uses a clustered index to satisfy a query
D) When the query processor updates an index based on an insert.
Answer: B) When the query processor uses a non-clustered index to lookup the data in the clustered index.
Question: 14
UCF: SQL 2.1
Topic: ARCHITECTURE_17
What is a defunct filegroup?
A) Any filegroup that is offline from the database.
B) Any filegroup that has been emptied of data and other objects.
C) A defunct filegroup is a filegroup that has been removed and can never be restored, but its meta data remains.
D) There is no such thing as a defunct filegroup
Answer: C) A defunct filegroup is a filegroup that has been removed and can never be restored, but its meta data remains.
Question: 15
UCF: SQL 2.1
Topic: ARCHITECTURE_17
What is basic unit of SQL Server
A) Page
B) row
C) Table
D) Database
Answer: A) Page
Question: 16
UCF: SQL 2.1
Topic: ARCHITECTURE_17
what is meant by extents in SQL Server 2005
A) 16 pages
B) 24 pages
C) 8 pages
D) 32 pages
Answer: C) 8 pages
Question: 17
UCF: SQL 2.1
Topic: ARCHITECTURE_17
what is the default isolation level for sql?
A) Read uncommitted
B) Serializable
C) Read committed
D) Repeatable Read
Answer: C) Read committed
Question: 18
UCF: SQL 2.1
Topic: LOG_SHIPPING_18
Which system database can be configured for logshipping
A) Master
B) Model
C) MSDB
D) None
Answer: D) None
Question: 19
UCF: SQL 2.1
Topic: LOG_SHIPPING_18
Which system stored procedure refreshes the remote monitor tables with the latest information from a given primary or secondary server for the specified log shipping agent?
A) sp_refresh_ls_monitor
B) sp_refresh_log_shipping_monitor
C) sp_refresh_log_shipping_monitor_tables
D) sp_update_log_shipping_monitor
Answer: B) sp_refresh_log_shipping_monitor
Question: 20
UCF: SQL 2.1
Topic: LOG_SHIPPING_18
Which system stored procedure removes a secondary database name from a primary database?
A) sp_cleanup_log_shipping_primary_secondary
B) sp_delete_log_shipping_primary_secondary
C) sp_remove_log_shipping_primary_secondary
D) sp_truncate_log_shipping_primary_secondary
Answer: B) sp_delete_log_shipping_primary_secondary
Question: 21
UCF: SQL 2.1
Topic: LOG_SHIPPING_18
Which system stored procedure removes log shipping of primary database including backup job as well as local and remote history?
A) sp_cleanup_log_shipping_primary_database
B) sp_delete_log_shipping_primary_database
C) sp_cleanup_log_shipping_primary_db
D) sp_delete_log_shipping_primary_db
Answer: B) sp_delete_log_shipping_primary_database
Question: 22
UCF: SQL 2.1
Topic: LOG_SHIPPING_18
Which system stored procedure returns information regarding a secondary database from the monitor tables?
A) sp_get_log_shipping_monitor_secondary
B) sp_help_log_shipping_monitor_secondaries
C) sp_help_log_shipping_monitor_secondary
D) sp_help_log_shipping_secondar
Answer: C) sp_help_log_shipping_monitor_secondary
Question: 23
UCF: SQL 2.1
Topic: LOG_SHIPPING_18
Which system stored procedure returns the job ID of the alert job in a logshipping setup?
A) sp_help_log_shipping_alert_job
B) sp_get_log_shipping_alert_job
C) sp_help_log_shipping_job
D) sp_get_log_shipping_job
Answer: A) sp_help_log_shipping_alert_job
Question: 24
UCF: SQL 2.1
Topic: LOG_SHIPPING_18
Which table we can check for log shipping history in SQL 2005 onwards?
A) log_shipping_monitor_history_detail
B) log_shipping_plan_history
C) loghipping_plan_history
D) None
Answer: A) log_shipping_monitor_history_detail
Question: 25
UCF: SQL 2.1
Topic: LOG_SHIPPING_18
Which table stores one record for each primary database in each log shipping configuration?
A) log_shipping_primary_databases
B) log_shipping_monitor_primary
C) log_shipping_primary_dbs
D) log_shipping_monitor_primary_dbs
Answer: A) log_shipping_primary_databases
Question: 26
UCF: SQL 2.1
Topic: LOG_SHIPPING_18
Which table stores one record for each secondary database in each log shipping configuration?
A) log_shipping_secondary_dbs
B) log_shipping_monitor_secondary
C) log_shipping_monitor_secondary_dbs
D) log_shipping_secondary_databases
Answer: D) log_shipping_secondary_databases
Question: 27
UCF: SQL 2.1
Topic: LOG_SHIPPING_18
Which table will provides you the status of Log Shipping in SQL Server 2005
A) log_shipping_monitor_primary
B) log_shipping_plans
C) log_shipping_monitor
D) log_shipping_monitor_secondary
Answer: D) log_shipping_monitor_secondary
Question: 28
UCF: SQL 2.1
Topic: LOG_SHIPPING_18
Will my SQL Server logins be transferred as a part of log shipping?
A) Yes
B) No
C) Only in 2005 version
D) Only in 2008 version
Answer: B) No
Question: 29
UCF: SQL 2.1
Topic: LOG_SHIPPING_18
You are a database developer for an insurance company. The company has one main office and 18 regional offices. Each office has one SQL Server 2000 database. The regional offices are connected to the main office by a high-speed network. The main office database is used to consolidate information from the regional office databases. The tables in the main office database are partitioned horizontally. The regional office location is used as part of the primary key for the main office database. You are designing the physical replication model. What should you do?
A) Configure the main office as a publishing Subscriber.
B) Configure the main office as a Publisher with a remote Distributor.
C) Configure the main office as a central Publisher and the regional offices as Subscribers.
D) Configure the regional offices as Publishers and the main office as a central Subscriber.
Answer: D) Configure the regional offices as Publishers and the main office as a central Subscriber.
Question: 30
UCF: SQL 2.1
Topic: LOG_SHIPPING_18
You are implementing transaction log shipping for a database named DB1 from a server named SQL1 to a server named SQL2. Because DB1 is 100 GB in size, it is too big to transfer over the network in a reasonable amount of time. You need to minimize the impact on the network while you initialize the secondary database. What action should you perform?
A) A. Specify the simple recovery model for DB1.
B) : Specify either the full or the bulk-logged recovery model for DB1.
C) C. Perform a complete backup of DB1 to portable media. Restore the secondary database from that backup; specify the RECOVERY option.
D) Before you activate transaction log shipping to the secondary database, execute the following statement on the primary server. BACKUP LOG DB1 WITH TRUNCATE_ONLY
Answer: D) Before you activate transaction log shipping to the secondary database, execute the following statement on the primary server. BACKUP LOG DB1 WITH TRUNCATE_ONLY
Question: 31
UCF: SQL 2.1
Topic: LOG_SHIPPING_18
You can reduce the load on your primary server by using a secondary server for read-only query processing. This can be achieved by putting the database in __ mode?
A) NORECOVERY
B) RECOVERY
C) STANDBY
D) You cannot use secondary database.
Answer: C) STANDBY
Question: 32
UCF: SQL 2.1
Topic: LOG_SHIPPING_18
You cannot setup Log Shipping on a database that is in _ recovery model
A) Full
B) Simple
C) Bulk Logged
D) None of the above
Answer: B) Simple
Question: 33
UCF: SQL 2.1
Topic: LOG_SHIPPING_18
You have a corporate SQL box which has the master price list for shoes which is updated constantly in the corp. office. You want all your outlets to receive the updated prices as quickly as possible from the corp. office. Which sort of replication is best here?
A) Transactional
B) Snapshot
C) Merge
Answer: A) Transactional
Question: 34
UCF: SQL 2.1
Topic: LOG_SHIPPING_18
You must be a _____ on each server instance to configure log shipping
A) serveradmin
B) sysadmin
C) setupadmin
D) backupadmin
Answer: B) sysadmin
Question: 35
UCF: SQL 2.1
Topic: LOG_SHIPPING_18
You will not be able run queries on the secondary database if the secondary database is in _ mode.
A) NORECOVERY
B) RECOVERY
C) STANDBY
D) NOSTANDBY
Answer: A) NORECOVERY
Question: 36
UCF: SQL 2.1
Topic: TOOLS_UTIL_10
How can you change “Hansen” into “Nilsen” in the “LastName” column in the Persons table?
A) UPDATE Persons SET LastName=’Nilsen’ WHERE LastName=’Hansen’
B) UPDATE Persons SET LastName=’Hansen’ INTO LastName=’Nilsen’
C) MODIFY Persons SET LastName=’Hansen’ INTO LastName=’Nilsen
D) MODIFY Persons SET LastName=’Nilsen’ WHERE LastName=’Hansen’
Answer: A) UPDATE Persons SET LastName=’Nilsen’ WHERE LastName=’Hansen’
Question: 37
UCF: SQL 2.1
Topic: TOOLS_UTIL_10
How does SQL Server checks the statistics?
A) You can configure sql to use only sample rows
B) You can configure sql to use all rows
C) Both 1 2
D) None
Answer: C) Both 1 2
Question: 38
UCF: SQL 2.1
Topic: TOOLS_UTIL_10
How to change database owner for database DBTest as ‘Albert’
A) ALTER DATABASE DBTest CHANGE OWNER ‘Albert’
B) ALTER DATBASE MODIFY OWNER ‘Albert’
C) EXEC sp_changedatabaseowner ‘Albert’
D) EXEC sp_changedbowner ‘Albert’
Answer: D) EXEC sp_changedbowner ‘Albert’
Question: 39
UCF: SQL 2.1
Topic: TOOLS_UTIL_10
How to check Database Consistency
A) DBCC CHECKCATALOG
B) DBCC CHECKDATABASE
C) DBCC CHECKTABLE
D) DBCC CHECKDB
Answer: D) DBCC CHECKDB
Question: 40
UCF: SQL 2.1
Topic: TOOLS_UTIL_10
How to check depended objects on a table
A) sp_depends
B) sp_depend
C) sp_dependenc
D) None
Answer: A) sp_depends
Question: 41
UCF: SQL 2.1
Topic: TOOLS_UTIL_10
How to check index information for a table
A) sp_help
B) sp_helpindex
C) sp_helpdb
D) sp_helptext
Answer: B) sp_helpindex
Question: 42
UCF: SQL 2.1
Topic: TOOLS_UTIL_10
How to check SQL Server Version using T-SQL
A) SELECT @Version
B) SELECT @@Version
C) SELECT @@SQLVersion
D) ALL
Answer: B) SELECT @@Version
Question: 43
UCF: SQL 2.1
Topic: TOOLS_UTIL_10
how to check the percentage of backup completed in sql 2000:
A) sp_spaceused dbname
B) dbcc inputbuffer(spiD)
C) dbcc outputbuffer(spiD)
D) none
Answer: C) dbcc outputbuffer(spiD)
Question: 44
UCF: SQL 2.1
Topic: TOOLS_UTIL_10
How to connect to SQL server using DAC
A) sqlcmd A d master
B) Admin:serverinstance
C) Both
D) None
Answer: C) Both
Question: 45
UCF: SQL 2.1
Topic: TOOLS_UTIL_10
How to map an existing database user to a SQL Server login
A) sp_synclogins
B) sp_change_users_login
C) sp_change_user_login
D) sp_change_login_users
Answer: B) sp_change_users_login
Question: 46
UCF: SQL 2.1
Topic: WINDOWS_OS_5
How to start the sql server in single user mode from command prompt?
A) net start mssqlserver -m
B) net start mssqlserver -c
C) net start mssqlserver -f
D) net start mssqlserver /t
Answer: A) net start mssqlserver -m
Question: 47
UCF: SQL 2.1
Topic: WINDOWS_OS_5
In order to secure SQL Server 2005 installation, which privileges should be granted to Service Accounts?
A) Run SQL Server services with the lowest possible privileges.
B) Run SQL Server services with the highest possible privileges.
C) Grant Full Control permission to Service Account
D) SQL Server setup automatically grants the service account required permissions.
Answer: A) Run SQL Server services with the lowest possible privileges.
Question: 48
UCF: SQL 2.1
Topic: WINDOWS_OS_5
On an 8 processor 32 bit sql server machine, how many MAX WORKER THREADS will be there?
A) 128
B) 256
C) 288
D) 98
Answer: C) 288
Question: 49
UCF: SQL 2.1
Topic: WINDOWS_OS_5
Page file setting is the part of
A) Operating System
B) SQL Database Engine
C) SQL Server
D) SSIS
Answer: A) Operating System
Question: 50
UCF: SQL 2.1
Topic: WINDOWS_OS_5
Security policy can be configured using from Command Prompt
A) secpol
B) secpol.msc
C) secpol.msi
D) None
Answer: B) secpol.msc
SQL 2.2
Question: 1
UCF: SQL 2.2
Topic: DBA_17
You work as a Database Developer for Miracle Infocom. The company uses SQL server 2000 database to maintain data for the Human Resource department. You design a month salary sheet view, which joins several tables and calculates aggregate information. You want to provide a parameterized query to access the data contained in the salary sheet view. The output of this query will be used in other SELECT lists. What will you do?
A) Create an inline user-defined function that accepts the parameter as input.
B) B.Create a stored procedure that accepts the parameter as input and returns a rowset with the result set.
C) C.Create a scalar user-defined function that accepts the parameter as input.
D) D.Use an ALTER VIEW statement to add the parameter value to the view definition.
Answer: A) Create an inline user-defined function that accepts the parameter as input.
Question: 2
UCF: SQL 2.2
Topic: DBA_17
You work as a network administrator at Miracle Info.com. The Miracle Info.com network consists o f a single Windows 2000 domain named Miracle Info.com. All servers on the Miracle Info.com network run Windows 2000 Server. The Miracle Info.com network contains a member server named Miracle Info -SR10. The physical hardware on Miracle Info -SR10 is shown in the following table. You n eed to ensure that SQL Server 2000 can be installed on Miracle Info – SR10. What should you do?
A) A.Install more RAM on Miracle Info – SR10.
B) Upgrade the CPU on Miracle Info -SR10.
C) C.Install a second hard drive on Miracle Info -SR10.
D) D.Do nothing; SQL Server 2000 can be installed on Miracle Info -SR10 .
Answer: B) Upgrade the CPU on Miracle Info -SR10.
Question: 3
UCF: SQL 2.2
Topic: DBA_17
You work as the database administrator at Miracle Info.com. The Miracle Info.com network consists of a single Active Directo ry domain named Miracle Info.com. All servers on the Miracle Info.co m network run Windows 2000 Server. The Miracle Info.com network contains a SQL Server 2000 database server named Miracle Info -DB01 that runs on Windows 2000 Server. Miracle Info -DB01 contains a database named CK_Sales. Miracle Info.com intends migrating Miracle Info -DB01 to SQL Server 2005 in the near future. To prepare for the migration, you decide to install SQL Server 2005 Express on a server named Miracle Info -SR3 1. You verify that Miracle Info -SR 31 Exceeds the minimum recommended hardware requirements for SQL Server 2005. What should you do next?
A) A.Upgrade Miracle Info -SR31 to Windows Sever 2003 and install Service Pack
B) B.Install and run the SQL Server Upgrade Advisor on Miracle Info -SR 31.
C) C.Install SQL Server 2000 on Miracle Info -SR31.
D) Download and install the .NET Framework 2.0.
Answer: D) Download and install the .NET Framework 2.0.
Question: 4
UCF: SQL 2.2
Topic: DBA_17
You work as the database administrator at Miracle Info.com. The Miracle Info.com network consists of a single Active Directo ry domain named Miracle Info.com. All servers on the Miracle Info.com network run Windows 2000 Server. The Miracle Info.com network contains a SQL Server 7.0 database server named Miracle Info -DB01 that runs on Windows 2000 Server. Service Pack 4 for Windows 2000 Server id installed on Miracle Info -DB01. Miracle Info.com wants to migrate Miracle Info -DB01 to SQL Server 2005. You decide to perform an in-place upgrade to SQL Server 2005. You verify that Miracle Info -DB01 exceeds the recommended hardware requirements for SQL Server 2005. What should you do next?
A) A.Up grade Miracle Info -DB01 to Windows Sever 2003.
B) Install Service Pack 4 for SQL Server 7.0 on Miracle Info -DB01.
C) C.Upgrade SQL Server 7.0 on Miracle Info -DB01 to SQL Server 2000.
D) D.Run the SQL Server 2005 Setup utility on Miracle Info -DB01 and upgrade directly to SQL Server 2005.
Answer: B) Install Service Pack 4 for SQL Server 7.0 on Miracle Info -DB01.
Question: 5
UCF: SQL 2.2
Topic: DBA_17
You work as the database administrator at Miracle Info.com. The Miracle Info.com network consists of a single Active Directory domain named Miracle Info.com. All servers on the Miracle Info.com network run Windows 2000 Server. The Miracle Info.com network contains a SQL Server 2000 database server named Miracle Info -DB01 that runs on Windows 2000 Server Service Pack 4. Miracle Info – DB01 contains a database n amed CK_Trading.Miracle Info.com wants to migrate Miracle Info -DB01 to SQL Server 2005 but wants to retain the CK_Trading database with the same instance name. You decide to perform an in-place upgrade to SQL Server 2005. You verify that Miracle Info -DB01 exceeds the minimum recommended hardware requirements for SQL Server 2005. What should you do next?
A) A.Upgrade Miracle Info -DB01 to Windows Sever 2003 Service Pack
B) Run the SQL Server Upgrade Advisor on Miracle Info -DB01.
C) C.Detach th e CK_Trading database on Miracle Info –
D) D.B01. D.Run the SQL Server 2005 Setup utility.
Answer: B) Run the SQL Server Upgrade Advisor on Miracle Info -DB01.
Question: 6
UCF: SQL 2.2
Topic: DBA_17
You work as the database administrator at Miracle Info.com. The Miracle Info.com network consists of a single Windows 2000 domain named Miracle Info.com. All servers on the Miracle Info.com network run on Windows 2000 Server while the client computers run Windows 2000 Professional, Windows 98, and Novell NetWare. All computers are members of the Miracle Info.com domain. The Miracle Info.co m network contains a SQL Server 2000 database server named Miracle Info -DB01 that hosts a default instance of SQL Server 2000. Miracle Info.com wants to implement another SQL Server 2000 instance for a database named Sales_DB. Sales_DB will store sales data for the company. All Miracle Info.com users will require access to the Sales_DB database ase. You have been instructed to create the SQL Server 2000 instance for the Sales_DB database. You need to ensure that all users will be able to connect to the Sales_DB database. You cannot make configuration changes to the client computers. Your solution must optimize network traffic. What should you do?
A) Install the instance as a named instance and configure it to support Mixed Mode Authentication. Then enable the NWLink IPX/SPX Net-Library for the instance.
B) B.Install the instance as a default instance and configure it to support Windows Authentication. Then enable the TCP/IP Sockets Net-Library for the instance.
C) C.Install the instance as a Named Instance and configure it to support SQL Server Authentication. Then enable the IPX/SPX for the instance.
D) D.Install the instance as a default instance and configure it to support Windows Authentication. Then enable the TCP/IP Sockets Net-Library for the instance.
Answer: A) Install the instance as a named instance and configure it to support Mixed Mode Authentication. Then enable the NWLink IPX/SPX Net-Library for the instance.
Question: 7
UCF: SQL 2.2
Topic: DBA_17
You work as the database administrator at Miracle Info.com. The Miracle Info.com network consists of a single Windows 2000 domain named Miracle Info.com. All servers on the Miracle Info.com network run Windows 2000 Advanced Server. The Miracle Info.com network contains a SQL Server 2000 database server named Miracle Info -DB01. Miracle Info -DB01 is a production server and hosts a database named CK_Sales which stores sales transactions for the company. Miracle Info.com has database application developer named Rory Allen who uses a Windows 2000 Professional client computer named Miracle Info -WS228. Ro ry Allen needs to test a database application on Miracle Info -DB01. You install a separate a SQL Server 2000 instance named RA_Test on Miracle Info -DB01. You need to ensure that Rory Allen can connect to the RA_Test instance. What should you do?
A) Configure the RA_Test instance to use the Named Pipes Net-Library.
B) B.Configure the RA_Test instance to use the Multiprotocol Net-Library.
C) C.Configure the RA_Test instance to use the NWLink IPX/SPX Net-Library.
D) D.Configu re the RA_Test instance to use the Shared Memory Net-Library.
Answer: A) Configure the RA_Test instance to use the Named Pipes Net-Library.
Question: 8
UCF: SQL 2.2
Topic: DBA_17
You work as the database administrator at Miracle Info.com. The Miracle Info.com network consists of a single Windows 2000 domain named Miracle Info.com. The Miracle Info.com network contains a SQL Server 7.0 database server named Miracle Info -DB01 that runs on Windows NT Server 4.0. Miracle Info -DB01 is a production server and hosts several databases for the company. You install SQL Server 2000 as the default instance on a new database server named Miracle Info -DB02. Miracle Info -DB02 runs Windows 2000 Server. Miracle Info.com users in the Accounts department must have d direct access to the databases on both Miracle Info -DB01 and Miracle Info -DB02. The users in the Accounts department use client computers that run Windows 2000 Professional. You need to encrypt the network communication between the Accounts department users’ client computers and the database servers. What should you do?
A) A.Configure the client computers to use the Named Pipes Net-Library.
B) Configure the client computers to use the Multipro tocol Net-Library.
C) C.Configure the client computers to use the TCP/IP Sockets Net-Library.
D) D.Configu re the client computer to use the Shared Memo ry Net-Library.
Answer: B) Configure the client computers to use the Multipro tocol Net-Library.
Question: 9
UCF: SQL 2.2
Topic: DBA_17
You work as the database administrator at MiracleInfocom .com. All servers on the MiracleInfocom .com network run Windows Server 2003 and all database servers run SQL Server 2005. The MiracleInfocom .com network contains two database servers named MiracleInfocom -DB01 and MiracleInfocom -B02. MiracleInfocom -DB01 hosts a database named MI_Sales that stores sales data for the company. The MI_Sales database is also used for online transaction processing (OLTP). MiracleInfocom .com develops a new Web application that you deploy on MiracleInfocom -DB01. MiracleInfocom .com’s Web customers start complaining about excessive delays and intermittent timeout errors. You monitor the systems resources on MiracleInfocom -DB01 on Windows System Monitor and are satisfied that system resource usage is well within expected parameters. You request that the application developers test the Web application and identify the cause of the performance problems. The application developers report that the problem is related to the time required to retrieve data from MI_Sales. You need to create a SQL Server Profiler trace to identify the poorly performing queries in the Web application. What should you do?
A) Capture events related to the Sessions and Stored Procedure event classes. Group data by EventClass.
B) Capture events related to the Stored Procedure and TSQL event classes. Group data by Duration.
C) Capture events related to the ExistingConnection event classes. Group data by EventClass.
D) Capture events related to the ExistingConnection and Sessions event classes. Group data by ClassProcessID
Answer: B) Capture events related to the Stored Procedure and TSQL event classes. Group data by Duration.
Question: 10
UCF: SQL 2.2
Topic: DBA_17
You work as the database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom .com. All servers on the MiracleInfocom .com network run Windows Server 2003 and all client computers run Windows XP Professional. The MiracleInfocom .com network contains a SQL Server 2005 database server named MiracleInfocom -DB01. MiracleInfocom -DB01 hosts a database named MI_Sales that stores sales data for the company. MiracleInfocom -DB01 also hosts two financial applications. Several MiracleInfocom .com users in the Sales department complain that queries against the MI_Sales database perform very poorly at times. You use System Monitor to monitor the performance of by using MiracleInfocom -DB01 and discover that SQL Server 2005 does not have sufficient memory to run efficiently. You need to ensure that SQL Server 2005 always has sufficient memory to run efficiently. What should you do?
A) Decrease the min memory per query server option.
B) Increase the min server memory server option.
C) Increase the max worker threads server option.
D) Decrease the max server memory server option.
Answer: B) Increase the min server memory server option.
Question: 11
UCF: SQL 2.2
Topic: DBA_17
You work as the database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom .com. All servers on the MiracleInfocom .com network run Windows Server 2003 and all client computers run Windows XP Professional. You use a client computer named MiracleInfocom -WS444. The MiracleInfocom .com network contains a SQL Server 2005 database server named MiracleInfocom -DB01. MiracleInfocom -DB01 hosts a database named MI_Products. The recent increase in database usage at the company has resulted in several MiracleInfocom .com users complaining of poor query performance and query timeouts try to retrieve data from the MI_Products database. You need to determine the cause of the poor performance. You need to minimize the effect of your monitoring strategy on system performance. What should you do?
A) Run the SQL Server Profiler from MiracleInfocom -WS444 and create a trace that monitors MiracleInfocom -DB01. Save the trace file to a table.
B) Run the Performance tool on MiracleInfocom -DB01 to create a log that monitors MiracleInfocom -DB01. Save the log file to a folder on MiracleInfocom -DB01.
C) Run the SQL Server Profiler on MiracleInfocom -DB01 and create a trace that monitors MiracleInfocom -DB01. Save the trace file to a table
D) Run the Performance tool on MiracleInfocom -WS444 to create a log that monitors MiracleInfocom -DB01. Save the log file to a folder on MiracleInfocom -WS444.
Answer: D) Run the Performance tool on MiracleInfocom -WS444 to create a log that monitors MiracleInfocom -DB01. Save the log file to a folder on MiracleInfocom -WS444.
Question: 12
UCF: SQL 2.2
Topic: DBA_17
You work as the database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom .com. All servers on the MiracleInfocom .com network run Windows Server 2003 and all client computers run Windows XP Professional. The MiracleInfocom .com network contains a SQL Server 2005 database server named MiracleInfocom -DB01. MiracleInfocom -DB01 is running a default SQL Server instance and a named instance named MI_Sales. MiracleInfocom .com users complain that performance for both instances has deteriorated over the last six months. You want to determine the memory and processor resource requirements of the two instances as well as any resource conflicts between the two instances. What should you do?
A) Use SQL Server Profiler to create a trace and capture typical activity for each instance with both instances running.
B) Use Windows Performance to create counter log that captures performance counters for each instance with both instances running.
C) Use Windows Performance to create counter log that captures performance counters for each instance separately with each instance running on its own.
D) Use SQL Server Profiler to create a trace and capture typical activity for each instance separately with each instance running on its own.
Answer: B) Use Windows Performance to create counter log that captures performance counters for each instance with both instances running.
Question: 13
UCF: SQL 2.2
Topic: DBA_17
You work as the database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom .com. All servers on the MiracleInfocom .com network run Windows Server 2003 and all client computers run Windows XP Professional. The MiracleInfocom .com network contains a Microsoft SQL Server 2005 database server named MiracleInfocom -DB01 which you administer. You are in the process of collecting information that will be required for ongoing server management which includes hardware performance statistics. You are required to collect the statistics during average use and peak periods. You want to collect the hardware baseline performance statistics. What should you do?
A) A Windows counter log should be created.
B) A Windows Alerts should be created.
C) A Windows trace log should be created.
D) A SQL Server Profiler trace should be created.
Answer: A) A Windows counter log should be created.
Question: 14
UCF: SQL 2.2
Topic: DBA_17
You work as the network database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom .com. All servers on the MiracleInfocom .com network run Windows Server 2003 and all client computers run Windows XP Professional. The MiracleInfocom .com network contains a Microsoft SQL Server 2005 database server named MiracleInfocom -DB01 which you administer. Recently the MiracleInfocom .com network users started complaining that server response has degraded rapidly. You decided to monitor the following counters: 1. Processor: % Processor Time You discover that the performance counter is consistently over 90 percent. The Baseline values for this counter are typically in the 40 to 60 percent range. During the course of the day you determine that the SQL Server process accounts for less than 50% of the processor time. You are required to identify and correct the problem as quickly as possible. What should you do?
A) The server should be scanned for missing updates.
B) The server should be rebooted.
C) The SQL Server service should be stopped.
D) The server should be scanned using anti-virus software.
Answer: D) The server should be scanned using anti-virus software.
Question: 15
UCF: SQL 2.2
Topic: DBA_17
Your company uses SQL Server 2005. Users report that report execution is slow. You investigate and discover that some queries do not use optimal execution plans. You also notice that some optimizer statistics are missing and others are out of date. You need to correct the problem so that reports execute more quickly. Which two Transact-SQL statements should you use?
A) DBCC CHECKTABLE
B) ALTER INDEX REORGANIZE
C) UPDATE STATISTICS
D) DBCC SHOW_STATISTICS
Answer: C) UPDATE STATISTICS
Question: 16
UCF: SQL 2.2
Topic: DBA_17
Your SQL Server is experiencing internal memory pressure, how do you resolve this?
A) Run DBCC Memorystatus, see how the memory is distributed and then take action
B) B. Run DBCC inputbuffer, see how the memory is distributed and then take action
C) C. Run sys.dm_exec_cached_plans, see how the memory is distributed and then take action
D) Run DBCC Outputbuffer, see how the memory is distributed and then take action
Answer: A) Run DBCC Memorystatus, see how the memory is distributed and then take action
Question: 17
UCF: SQL 2.2
Topic: DBA_17
Your work as a network administrator at Miracle Info.com. The Miracle Info.com Net work consists of a single Windows 2000 forest. The Miracle Info.com forest has a single root domain named Miracle Info.com. All servers on the Miracle Info.com network run Windows 2000 Server. The Miracle Info.com network contains a member server named Miracle Info -SR12. You install SQL Server 2000 on Miracle Info -SR12 and configure the MS SQL Server service to run under a domain u ser account n amed SQLAdmin in the Miracle Info.com root domain. You add the SQLAdmin account to the Enterprise Admins group. You start Miracle Info -SR12 but the MSSQLServer service does not start. You need to ensure that the MS SQL Server service starts.What should you do?
A) Configure the MS SQL Server service to run under the local System account on Miracle Info -SR12.
B) B.Add the SQL Admin user account to the Do main Admins group in the Miracle Info.com root domain.
C) C.Add the Enterprise Admins group to the Domain Admins group in the Miracle Info.com root domain.
D) D.Configure the MSSQLServer service and the MSSQLServerAgent service to run under the same user account.
Answer: A) Configure the MS SQL Server service to run under the local System account on Miracle Info -SR12.
Question: 18
UCF: SQL 2.2
Topic: MT_15
You work as the network database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom .com. All servers on the MiracleInfocom .com network run Microsoft Windows Server 2003 and the database server runs Microsoft SQL Server 2005. The MiracleInfocom .com network users use a database called Billventory. You need to replicate the Billventory database across all the servers and you are concerned with the distribution database growing very large. You are required to minimize the size of the distribution database and you want o increase the frequency that delivered transactions from the distribution database are deleted. You must accomplish your goal using the least amount of administrative effort. What should you do?
A) A new job should be created that automatically delete the replicated Agent history
B) The Replication agents checkup job should be configured to run more frequently
C) The Expired subscriptions cleanup job must be configured to run twice a day
D) The frequency that the Agent History clean up job runs should be increased
Answer: D) The frequency that the Agent History clean up job runs should be increased
Question: 19
UCF: SQL 2.2
Topic: MT_15
You work as the network database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom .com. All servers on the MiracleInfocom .com network run Windows Server 2003 and all client computers run Windows XP Professional. The MiracleInfocom .com network contains three Microsoft SQL Server 2005 servers which you administer. The MiracleInfocom .com network includes a mix of databases configured for Full Recovery model and Simple Recover Model. A new MiracleInfocom .com security policy specifies that database servers: 1. All database server should be secured against logon and local access 2. Database servers must be backed up on a predefined schedule 3. Any database should be recoverable to no more than six hours lost 4. The backups must be protected against unauthorized access 5. A copy of the data no more than a week old must be maintained offsite The MiracleInfocom .com network servers are locked in a room with file servers used as the backup destination for all database servers. The Full backups run monthly and the differential backups run nightly. You are required to ensure that all security policy guidelines are met. What should you do?
A) A second file server should be deployed in the locked room and run mirror backups.
B) Transaction log backups must be run every six hours.
C) Mirrored backups should be run to another file server located in a different physical location in the network.
D) The differential backup frequency should be changed to every six hours.
Answer: D) The differential backup frequency should be changed to every six hours.
Question: 20
UCF: SQL 2.2
Topic: MT_15
You work as the network database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom .com. All servers on the MiracleInfocom .com network run Windows Server 2003 and all client computers run Windows XP Professional. The MiracleInfocom .com network contains ten Microsoft SQL Server 2005 servers which you administer. MiracleInfocom .com management has recently decided to tighten network security. A new MiracleInfocom .com security policy requires that only network users with security clearance can manage security principals and roles. You additionally need to audit the users who are currently making such changes on the database servers whilst your solution does not impact performance more than required. What should you do?
A) Enable object access event auditing in Audit Policy.
B) Enable auditing for account management events in Audit Policy.
C) C2 audit mode for the SQL Server instance must be enabled.
D) A SQL Server Profiler trace should be created.
Answer: D) A SQL Server Profiler trace should be created.
Question: 21
UCF: SQL 2.2
Topic: MT_15
You work as the network database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom .com. All servers on the MiracleInfocom .com network run Windows Server 2003 and all client computers run Windows XP Professional. The MiracleInfocom .com network contains a Microsoft SQL Server 2005 database server named MiracleInfocom -DB01 which you administer. MiracleInfocom -DB01 is configured for Windows Authentication. You are busy configuring user access and permissions. Most of the MiracleInfocom .com users have the same access requirements at the server and database level but there are some exceptions. The MiracleInfocom .com network users will access the database through a custom application, stored procedures and ad hoc queries. A new MiracleInfocom .com security policy requires that access to the MiracleInfocom Data must be limited to Active Directory user accounts only. User accounts who are not members of the Domain Admins group are not configured as database administrators. You are required to configure user access to minimize the effort required to assign and audit user access permissions. What should you do?
A) Access permissions should be assigned to Active Directory groups.
B) Logins and database users should be created based on Active Directory user accounts.
C) Access permissions should be assigned based on application roles.
D) Access permissions should be assigned based on user accounts.
Answer: A) Access permissions should be assigned to Active Directory groups.
Question: 22
UCF: SQL 2.2
Topic: MT_15
You work as the network database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom .com. All servers on the MiracleInfocom .com network run Windows Server 2003 and all client computers run Windows XP Professional. The MiracleInfocom .com network contains a Microsoft SQL Server 2005 database server named MiracleInfocom -DB01 which you administer. MiracleInfocom -DB01 is used to store confidential company information. You established a domain security policy which requires every action to change permission on the database server to be logged. MiracleInfocom -DB01 can only be running when logging is enabled. You are required to enforce the security policy on the database server. What should you do?
A) By using the sp_configure statement c2 audit mode should be enabled.
B) From the Security page of MiracleInfocom -DB01’s Properties windows auditing should be enabled.
C) Through a Group Policy Object (GPO) an audit policy must be enabled.
D) Through the Local Security Policy auditing must be enabled.
Answer: A) By using the sp_configure statement c2 audit mode should be enabled.
Question: 23
UCF: SQL 2.2
Topic: MT_15
You work as the network database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom .com. All servers on the MiracleInfocom .com network run Windows Server 2003 and all client computers run Windows XP Professional. The MiracleInfocom .com network contains a Microsoft SQL Server 2005 database server named MiracleInfocom -DB01 which you administer. You have received instruction from the CIO to install and configure an additional database server named MiracleInfocom -DB02 which will be maintained using scheduled Maintenance Plans. You configure the settings so that if a Maintenance Plan fails the server will send you an e-mail. You are required to configure the surface area of MiracleInfocom -DB02 to support the required functionality whilst keeping your solution as secure as possible. What should you do?
A) Service Broker endpoints should be enabled.
B) The SQL Server Browser service must be configured to start automatically.
C) SQL Mail should be enabled.
D) The SQL Server Agent service must be configured to start automatically.
Answer: D) The SQL Server Agent service must be configured to start automatically.
Question: 24
UCF: SQL 2.2
Topic: MT_15
You work as the network database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom .com. All servers on the MiracleInfocom .com network run Windows Server 2003 and all client computers run Windows XP Professional. The MiracleInfocom .com network contains a Microsoft SQL Server 2005 database server named MiracleInfocom -DB01 which you administer. The MiracleInfocom .com network uses a database called Bill EmployeeData which is used to create monthly reports. All of the network employees have a SQL Server login to access the server. A new MiracleInfocom .com security policy requires that all the SQL logins use the same complexity and password expiration policies used by Microsoft Windows Server 2003. The networks Windows Server 2003 computers are configured to require complex passwords and periodic password changes. The security policy additionally states that all users must change their password the first time when login onto MiracleInfocom -DB01. MiracleInfocom .com has recently hired a new employee who requires access to the database server using the account named Rory A. You are required to create a SQL Server login account for the user that adheres to all the appropriate requirements. What should you do?
A) You should execute CREATE LOGIN RoryA WITH PASSWORD = Moles4M4 MUST_CHANGE CHECK_EXPIRATION = ON, CHECK_POLICY = ON
B) You should execute CREATE LOGIN RoryA WITH PASSWORD = Moles4M4 MUST CHANGE CHECK_POLICY = OFF
C) You should execute CREATE LOGIN RoryA WITH PASSWORD = Moles4M4 MUST Change CHECK_POLICY = ON
D) You should execute CREATE LOGIN RoryA WITH PASSWORD = Moles4M4 CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF
Answer: A) You should execute CREATE LOGIN RoryA WITH PASSWORD = Moles4M4 MUST_CHANGE CHECK_EXPIRATION = ON, CHECK_POLICY = ON
Question: 25
UCF: SQL 2.2
Topic: MT_15
You work as the network database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom .com. All servers on the MiracleInfocom .com network run Windows Server 2003 and all client computers run Windows XP Professional. The MiracleInfocom .com network contains a Microsoft SQL Server 2005 database server named MiracleInfocom -DB01 configured to support both Windows authentication and SQL Server authentication. You have received instruction from the CIO to audit the passwords for the SQL Server logins. You are required to determine which logins require strong passwords and which require password expiration. What should you do?
A) Query the sys.syslogins.
B) Query the sys.login_token.
C) Query the sys.server_principals.
D) Query the sys.sql_logins.
Answer: D) Query the sys.sql_logins.
Question: 26
UCF: SQL 2.2
Topic: MT_15
You work as the network database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom .com. All servers on the MiracleInfocom .com network run Windows Server 2003 and all client computers run Windows XP Professional. The MiracleInfocom .com network contains a Microsoft SQL Server 2005 database server named MiracleInfocom -DB01 configured to support Windows authentication and SQL Server authentication. You have received instruction from the CIO to audit permissions granted to several sensitive tables. You are required to write a stored procedure to retrieve information on the permissions granted the principal to which it was granted and the principal to who granted them. The stored procedure is required to support the server even if you upgrade the SQL server to a newer version. What should you do?
A) The sys.syspermissions should be queried.
B) The sys.fn_builtin_permissions should be queried.
C) The sys.server_permissions should be queried.
D) The sys.database_permissions should be queried.
Answer: D) The sys.database_permissions should be queried.
Question: 27
UCF: SQL 2.2
Topic: MT_15
You work as the network database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom .com. All servers on the MiracleInfocom .com network run Windows Server 2003 and all client computers run Windows XP Professional. The MiracleInfocom .com network contains a Microsoft SQL Server 2005 database server named MiracleInfocom -DB01 which you administer. MiracleInfocom-DB01 includes a default instance and two named instances of SQL Server 2005. The MiracleInfocom-DB01 will only be accessed by users in the Finance department. All computers in the Finance department are located in the same subnet. The MiracleInfocom .com network financial application uses instance names to connect to MiracleInfocom -DB01. The application is not configurable. During the course of the day you are informed about a worm attack that accesses a computer using port 1434. You are required to protect MiracleInfocom -DB01 from the worm. What should you do?
A) The HideInstance flag should be set on all instances of SQL Server.
B) The Windows Firewall on MiracleInfocom -DB01 should be configured to block port 1434.
C) The SQL Server Browser service should be disabled.
D) An Internet Protocol Security (IPSeC) policy should be enabled on the database server.
Answer: D) An Internet Protocol Security (IPSeC) policy should be enabled on the database server.
Question: 28
UCF: SQL 2.2
Topic: MT_15
You work as the network database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom .com. The MiracleInfocom .com network has its headquarters in Chicago and a branch office in Miami The MiracleInfocom .com network contains two Microsoft SQL Server servers named MiracleInfocom -SR01 in the main office running SQL Server 2005 configured as the Publisher and MiracleInfocom -SR02 at the branch office running SQL 2000 configured as the Distributor. The MiracleInfocom .com network uses transactional replication with queued updating subscribers to synchronize data between locations. You define the following publications on MiracleInfocom -SR01: 1. FinancialData 2. FinancialStatusData Since MiracleInfocom -SR02 acts as the distributor most subscriptions are implemented as push subscriptions. You monitor the replication and discover that changes are not always replicated to the stores because of connection timeouts and you need to resolve the problem. What should you do?
A) The QueryTimeout value should be increased in the Log Reader Agent profile
B) The PollingInterval should be decreased for each Distribution Agent
C) The PollingInterval value should be decreased in the Queue Reader Agent profile
D) The LoginTimeout value should be increased in the Distribution Agent profile
Answer: D) The LoginTimeout value should be increased in the Distribution Agent profile
Question: 29
UCF: SQL 2.2
Topic: MT_15
You work as the network database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom.com. All servers on the MiracleInfocom .com network run Windows Server 2003 and all client computers run Windows XP Professional. The MiracleInfocom .com network contains a Microsoft SQL Server 2005 database server named MiracleInfocom -DB01 which you administer. The MiracleInfocom .com network management has recently informed you that you should have a password policy enabled on MiracleInfocom -DB01. You enabled a password policy on MiracleInfocom -DB01. All passwords are required to meet complexity requirements. You created a SQL login using the following command: 1. CREATE LOGIN RoryA WITH PASSWORD = Rory3M4 MUST_CHANGE CHECK_EXPIRATION = ON, CHECK_POLICY = ON When Rory Allen attempts to log onto the SQL server the first time and attempts to change the password to RoryAll23$_a he receives an error message indicating that he cannot use the new password. You need to find out what is causing the problem. What should you do?
A) Part of the user name is in the password.
B) At least one invalid character is in the password.
C) There are no uppercase letters in the password.
D) The minimum number of characters is not used in the password.
Answer: A) Part of the user name is in the password.
Question: 30
UCF: SQL 2.2
Topic: MT_15
You work as the network SQL developer at MiracleInfocom .com. All servers on the MiracleInfocom .com network run Microsoft Windows Server 2003 and the database server runs Microsoft SQL Server 2005. You are currently busy configuring the MiracleInfocom.com database server named MiracleInfocom -DB01. You will be required to perform distributed queries against a database that is stored on a SQL server named MiracleInfocom -SR02 which hosts four databases each using a different collation. You have received instruction from the CIO to add MiracleInfocom -SR02 as a linked server on MiracleInfocom -SR01 and you need to make sure the distributed queries are evaluated correctly using the least amount of administrative effort. What should you do?
A) You should set the Use Remote Collation option to True
B) You should set the Use Remote Collation option to False
C) You should use OPENROWSET to retrieve data from MiracleInfocom -SR02
D) You should specify a collation name
Answer: A) You should set the Use Remote Collation option to True
Question: 31
UCF: SQL 2.2
Topic: MT_15
Your company has two SQL Server 2005 computers named SQL1 and SQL2. Transaction log shipping occurs from SQL1 to SQL2 by using default SQL Server Agent schedule settings. You need to reconfigure re transaction log shipping to provide minimum latency on SQL2. What should you do?
A) On SQL1, reschedule the transaction log backup job so that it occurs every minute. On SQL2, maintain default schedule settings for both the log shipping copy and the restore jobs.
B) On SQL1, change the schedule type for the transaction log backup to Start automatically when SQL Server Agent starts. On SQL2, change the schedule types for both the log shipping copy and the restore jobs to Start automatically when SQL Server Agent starts.
C) On SQL1, maintain default schedule settings for the transaction log backup job. On SQL2, change the schedule types for both the log shipping copy and the restore jobs to Start automatically w+E33hen SQL Server Agent starts.
D) On SQL1, reschedule the transaction log backup job so that it occurs every minute. On SQL2, reschedule both the log shipping copy and the restore jobs so that they occur every minute.
Answer: D) On SQL1, reschedule the transaction log backup job so that it occurs every minute. On SQL2, reschedule both the log shipping copy and the restore jobs so that they occur every minute.
Question: 32
UCF: SQL 2.2
Topic: MT_15
Your Orders table is defined as follow: CREATE TABLE Orders ( OrderID Int IDENTITY (1,1) NOT NULL, SalesPersonID Int NOT NULL, RegionID Int NOT NULL, OrderDate Datetime NOT NULL, OrderAmount Int NOT NULL) The table is becoming too large to manage. You must delete all sales that are more than three years old. Which query will accomplish the desired result?
A) DELETE FROM Orders WHERE OrderDate DATEADD(YY,-3,GETDATE())
B) DELETE FROM Orders WHERE OrderDate DATEADD(YY,3,GETDATE())
C) DELETE FROM Orders WHERE OrderDate GETDATE(), -3
D) DELETE FROM Orders WHERE OrderDate GETDATE(),+3
Answer: A) DELETE FROM Orders WHERE OrderDate DATEADD(YY,-3,GETDATE())
Question: 33
UCF: SQL 2.2
Topic: NETWORKING_5
How do you check the tcp port in SQL Server 2000
A) client network utility
B) server network utility
C) sql configuration manager
D) all of the above
Answer: B) server network utility
Question: 34
UCF: SQL 2.2
Topic: NETWORKING_5
How many Endpoints are created in SQL Server 2005 ?
A) 4
B) 5
C) 6
D) 8
Answer: C) 6
Question: 35
UCF: SQL 2.2
Topic: NETWORKING_5
How to check the connectivity of the remote server from the local desktop?
A) ping local servername
B) ping ,remote servername
C) Ping local, remote serevrname
D) None of the above
Answer: B) ping ,remote servername
Question: 36
UCF: SQL 2.2
Topic: NETWORKING_5
How to enable Remote connection in SQL Server 2005
A) Surface Area Configuration Tool
B) Configuration Manager
C) SQL Configuration
D) None of the above
Answer: A) Surface Area Configuration Tool
Question: 37
UCF: SQL 2.2
Topic: NETWORKING_5
Named Pipes protocol is used with Large Networks as it supports support routing and gateways
A) TRUE
B) False
Na
NA
Answer: B) False
Question: 38
UCF: SQL 2.2
Topic: REPLICATION_13
You want to find the latency in transmitting data between the distribution and the subscription database in transactional replication. The time difference between the distributor_commit column in Mstracer_tokens table and the subscriber_commit column in Mstracer_history table will give you the latency for the Subscriber server. In which database can you find these two system tables?
A) distribution database
B) publication database
C) subscription database
D) msdb database
Answer: A) distribution database
Question: 39
UCF: SQL 2.2
Topic: REPLICATION_13
You work as a database administrator for MiracleInfocom .com. MiracleInfocom .com is a book distributor and its network consists of a single Active Directory domain named MiracleInfocom .com. All servers on the MiracleInfocom .com network run Microsoft Windows Server 2003 and the database servers run Microsoft SQL Server 2005. Your job description at MiracleInfocom .com includes the administration of the database servers named MiracleInfocom -DB01 and MiracleInfocom -DB02 respectively. MiracleInfocom -DB01 is on the internal network and MiracleInfocom -DB02 is on the perimeter network. Both these servers are configured to make use of transactional replication with immediate -updating subscribers to replicate the BookTitle table and the InStock table of the Inventory database. You have a suspicion that the replicas are non-convergent. Now you need to check which of the rows are different. What should you do?
A) You should run sp_publication_validation utility.
B) You need to run tablediff utility.
C) You need to enable the replication: Subscriber has failed data validation alert.
D) You need to enable the Validate Subscriptions option on the publication.
Answer: B) You need to run tablediff utility.
Question: 40
UCF: SQL 2.2
Topic: REPLICATION_13
You work as the network database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom .com. The MiracleInfocom .com network has its headquarters in Chicago and branch offices in Miami and Dallas. The MiracleInfocom .com network uses a database called Billventory. The network branch offices are connected to the main office via a high speed Wide Area Network (WAN) connection. The MiracleInfocom .com network contains a Microsoft SQL Server 2005 server in each branch office. The changes on each database in the branch offices should be replicated to the database in the main office and all the tables used in replication have a unique primary key. During the course of the day you implement transactional replication between the SQL servers in the branch offices and the main office. You are required to be notified when the amount of time for a transaction to be committed as the publisher and committed at the Subscriber exceeds a timeout specified in the design document that has been provided. What should you do?
A) A replication alert must be configured to generate an e-mail message
B) A warning should be enabled on the Publisher an specify the appropriate subscription expiration threshold based on the design document value
C) The Replication: agent success predefined replication alert should be enabled on the Publisher
D) A warning should be enabled on the Publisher and set the appropriate threshold for synchronization time based on the design document value
Answer: A) A replication alert must be configured to generate an e-mail message
Question: 41
UCF: SQL 2.2
Topic: REPLICATION_13
You work as the network database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom .com. The MiracleInfocom .com network has its headquarters in Chicago and branch offices in Miami, Dallas and Detroit. The MiracleInfocom .com network contains a Microsoft SQL Server 2005 server in each office. The database server in the main office is the Publisher and Distributor for merge replication. The network database server in the branch offices are configured as Subscribers and the Subscriptions are configured as pull subscriptions. During the course of the day you configure merge replication because the connection between the offices is intermittent and the data at the Subscribers rarely changes. You want to resolve conflicts manually when they occur. You are required to ensure that the SQL Server does not automatically resolve conflicts whilst you identify how you would resolve the conflicts that occur. What should you do?
A) The Interactive Resolver should be used to resolve replication conflicts
B) The conflict tracking level should be configured for row level
C) The Conflict Viewer should be used to resolve replication conflicts
D) All subscriptions should be configures as push subscriptions
Answer: A) The Interactive Resolver should be used to resolve replication conflicts
Question: 42
UCF: SQL 2.2
Topic: REPLICATION_13
You work as the network database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom .com. The MiracleInfocom .com network has its headquarters in Chicago and branch offices in five cities. The MiracleInfocom .com network contains a Microsoft SQL Server 2005 server at the main office and a SQL Server at each of the cities. The main office database server will be configured as the Publisher and Distributor whilst the remaining servers are configured as Subscribers who are rarely updated. You ensure that when updates of the subscribers conflicts with the Publisher the Publisher always takes precedence. The connections between the Publisher/Distributor and the Subscribers are periodic and are configured to occur after business hours only. You are required to design a replication configuration that meets the update and conflicts resolution requirements whist you use the least amount of administrative effort. The Publisher and Subscribers are required to remain synchronized with identical data. What should you do?
A) Transactional replication must be configured with queued-updating Subscribers
B) Merge replication must be configured with a custom managed code resolver
C) Merge replication should be configured for manual resolution
D) The Transactional replication should be configured with immediate-updating Subscribers
Answer: A) Transactional replication must be configured with queued-updating Subscribers
Question: 43
UCF: SQL 2.2
Topic: REPLICATION_13
You work as the network database administrator at MiracleInfocom .com. The MiracleInfocom .com network consists of a single Active Directory domain named MiracleInfocom .com. The MiracleInfocom .com network has its headquarters in Chicago and four branch offices. The MiracleInfocom .com network contains a Microsoft SQL Server 2005 server in the main office and one in each branch office. The main office SQL server is configured to act as the Publisher and Distributor for transactional replication. The network branch offices are all configured as subscribers and all subscriptions are configured as push subscriptions. The Data is treated as read-only at the remote offices. You are required to validate connections between the Distributor and the Subscribers and measure the replication latency whilst you collect statistics about latency that is accurate as possible. What should you do?
A) The Replication Monitor must be used to insert tracer tokens and gather token statistics from the Publisher/Distributor and all subscribers
B) The Replication Monitor should be used to set a latency threshold and generate an alert if latency exceeds the threshold
C) The Replication Monitor should be used to view the Queue Reader agent history statistics
D) The Replication Monitor should be used to view Current Average Performance and Current Worst Performance statistics
Answer: A) The Replication Monitor must be used to insert tracer tokens and gather token statistics from the Publisher/Distributor and all subscribers
Question: 44
UCF: SQL 2.2
Topic: REPLICATION_13
You work as the network SQL developer at MiracleInfocom .com. All servers on the MiracleInfocom .com network run Microsoft Windows Server 2003 and the database servers run Microsoft SQL Server 2005. The MiracleInfocom .com network contains two servers which hosts the FinancialData database. The second server runs Oracle Relational Database Management System (RDBMS) and hosts the MiracleInfocom Inventory database. The Inventory database is updated frequently by members of the Finance department. The MiracleInfocom .com network users in the Marketing department need to be able to create daily reports that combine information from both the FinancialData and MiracleInfocom Inventory databases to be run hourly. You have received instruction from the CIO to have your solution minimize the effort required for the database developers to run ad hoc queries no data on both SQL servers to test development requirements. The MiracleInfocom .com network users are required to create reports on the SQL server and you want to minimize the disk space used on the SQL Server whilst using the least administrative effort. What should you do?
A) Replication must be configured between the servers and replicate the data from the Oracle to the SQL server
B) A job must be created that performs the restore function and back up the Oracle server logged changes and restore the database changes from the Oracle to the SQL server every hour
C) A SQL Server Integration Services (SSIS) should be created to import the data from the Oracle server and schedule the package to be delivered every hour
D) The Oracle server should be configured as a linked server and join the information in the databases using a view
Answer: D) The Oracle server should be configured as a linked server and join the information in the databases using a view
Question: 45
UCF: SQL 2.2
Topic: REPLICATION_13
Your company has regional sales offices in Hyderabad, Noida and Chennai. You need to implement the replication scenario which will support autonomous changes the replicated data on the Publisher and on the Subscriber with the priority-based conflict resolution. Which type of SQL Server replication should you implement?
A) Transaction replication
B) Merge replication
C) Transaction replication with immediate-updating subscribers
D) Snapshot replication
Answer: B) Merge replication
Question: 46
UCF: SQL 2.2
Topic: REPLICATION_13
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?
A) Select the Subscription Watch List tab for SQL1. View the Latency column for the SQL2 subscription.
B) Select the All Subscriptions tab for the Pub1 publication. View the Latency column for the SQL2
C) Select the Tracer Tokens tab for the Pub1 publication. Select the Insert Tracer option and wait for the results to get the latency information.
D) Select the Subscription Watch List tab for SQL1. Double-click the SQL2 subscription. View the duration
Answer: C) Select the Tracer Tokens tab for the Pub1 publication. Select the Insert Tracer option and wait for the results to get the latency information.
Question: 47
UCF: SQL 2.2
Topic: REPLICATION_13
You setup Queue Reader Agent by exection __ on the distribution database
sp_qreader_agent_start
sp_addqreader_agent
sp_qreaderadd_agent
xp_qreaderadd_agent
Answer: sp_addqreader_agent
Question: 48
UCF: SQL 2.2
Topic: REPLICATION_13
Which flavour or TRANSACTIONAL Replication can be used in a ring topology with all the participants doing updates on different sets of data
Peer to Peer
Immediate Updates
Queued Updates
Cannot be implemented
Answer: Peer to Peer
Question: 49
UCF: SQL 2.2
Topic: REPLICATION_13
With _, you can use the HTTPS protocol to replicate data to mobile users over the Internet or to remote databases across the corporate firewall.
Peer to Peer Replication
Snapshot Replication
Transactional Replication
Web Synchronization
Answer: Web Synchronization
Question: 50
UCF: SQL 2.2
Topic: REPLICATION_13
SQL Server 2005 introduces a new program called __ which is used to provide information about SQL Server instances installed on the system
Alias
MDAC
SQL Server Browser
Service Broker
Answer: SQL Server Browser
SQL 3.1
Question: 1
UCF: SQL 3.1
Topic: INST_UPG_10
SQL Server Browser does not contribute to which of the action below?
A) Browsing a list of available servers
B) Connecting to the correct server instance
C) Connecting to dedicated administrator connection (DAC) endpoints
D) None of the above
Answer: D) None of the above
Question: 2
UCF: SQL 3.1
Topic: INST_UPG_10
Which of the following should be configured before installing SQL Server on a cluster?
A) MS DTC
B) MS TDC
C) MS TSC
D) MS RTC
Answer: A) MS DTC
Question: 3
UCF: SQL 3.1
Topic: INST_UPG_10
In SQL Server 2005, how do you perform a backup using Named Pipes?
A) Normal backups to local disks all use named pipes
B) In the TO part of the backup command, specify the pipe as “TO PIPE = “\sql\pipe”
C) SQL Server 2005 does not support backup to named pipes
D) In the OPTIONS part of backup command, specify the pipe as OPTIONS PIPE
Answer: C) SQL Server 2005 does not support backup to named pipes
Question: 4
UCF: SQL 3.1
Topic: INST_UPG_10
You have created a new assembly and want to test it on SQL Server 2005. You install the developer edition with defaults on your workstation and create the assembly as a sysadmin. You then write a function to use this assembly, but it does not work. What could be the problem?
A) .NET assemblies cannot be run on developer edition. You need to install Standard or Enterprise.
B) The CLR environment needs to be enabled.
C) You do not have the correct permissions to run a .NET assembly.
D) The assembly was not created with an explicit permission set assigned. You need to drop it and recreate it with SAFE, UNSAFE, or EXTERNAL_ACCESS explicitly set
Answer: B) The CLR environment needs to be enabled.
Question: 5
UCF: SQL 3.1
Topic: INST_UPG_10
Which of the following component gets installed with SQL-Server 2000
A) Internet Explorer
B) IIS
C) MDAC
D) SQL Lite Speed
Answer: C) MDAC
Question: 6
UCF: SQL 3.1
Topic: INST_UPG_10
You are installing a new SQL Server 2005 instance on a machine in a small peer-to-peer network. You will not be performing replication, so SQL Server will not need to communicate with other servers over the network. You need to be able to change the service accounts password every six months per company policy. Which service account type should you use?
A) Built-in system account
B) Local system account
C) Domain account
D) DAC account
Answer: B) Local system account
Question: 7
UCF: SQL 3.1
Topic: INST_UPG_10
Your installation of SQL Server 2005 has failed. Where is the first place you should look to find clues about the cause?
A) The System log in the Event Viewer
B) The Summary.txt file
C) The SQLSetupxxxx_ComputerName_Core.log file
D) The Application log in the Event Viewer
Answer: D) The Application log in the Event Viewer
Question: 8
UCF: SQL 3.1
Topic: INST_UPG_10
which version of .NET framework does SQL server 2005 need
A) .Net framework 2.1
B) .Net framework 2.0
C) .Net framework 1.1
D) .Net framework 1.0
Answer: B) .Net framework 2.0
Question: 9
UCF: SQL 3.1
Topic: INST_UPG_10
Which optional SQL Server component must you install to ensure that you can create and execute maintenance plans?
A) SQL Writer
B) SQL Server Browser
C) SQL Server Integration Services
D) SQL Server Maintenance Pack
Answer: C) SQL Server Integration Services
Question: 10
UCF: SQL 3.1
Topic: INST_UPG_10
What is the recommended way to send mail from SQLAgent in SQL Server 2005?
A) Database Mail
B) SQL Server Agent Mail
C) SQLMail
D) Extended MAPI
Answer: A) Database Mail
Question: 11
UCF: SQL 3.1
Topic: MON_TR_15
In SQL Server2005, The purpose of making This a Shared Data Source checkbox whilh creating a report through Report Builder is
to make it available to the future reporting apps
o share the data across networkt
Na
Na
Answer: to make it available to the future reporting apps
Question: 12
UCF: SQL 3.1
Topic: MON_TR_15
While modifying a report, what is not possible?
You can change the available data or the sort order for the report by modifying the query on the Data tab.
You can resize or rearrange controls on the Layout tab.
You can use the Properties window to change properties of individual controls including their font, alignment, colors, and so on.
You can include more tables ot join columns
Answer: You can include more tables ot join columns
Question: 13
UCF: SQL 3.1
Topic: MON_TR_15
You are the local administrator of a windows server which hosts a SQL Server Databse Engine and SSRS. You created a report in the Report Designer and now you want to publish it to the Reporting server. You recive an error that zour login has insufficient privileges. In which security role your login need to be added in SSRS?
Add your account to the sysadmin fixedserver role of the SQL Server
Add the Windows user ASPNET to the Administrator group of the local server.
Grant your login db_owner access to ReportServerTempdb database
NA
Answer: Add the Windows user ASPNET to the Administrator group of the local server.
Question: 14
UCF: SQL 3.1
Topic: MON_TR_15
To publish a report,In Configuration Manager, Active Soultion Configuration must be selected to ————
Debug
Debuglocal
Production
Any of theabove
Answer: Production
Question: 15
UCF: SQL 3.1
Topic: MON_TR_15
While publishing a report, ————– should be selected from the configuration Manager
Build only
Deploy only
Both Build and Deploy
No need to select explicitily. It will automatically pick up
Answer: Both Build and Deploy
Question: 16
UCF: SQL 3.1
Topic: MON_TR_15
Which of the below Reporting tools is intended for Business Analysts and non-developers
Report Builder
Reort Manager
Report Designer
None of the above
Answer: Report Builder
Question: 17
UCF: SQL 3.1
Topic: MON_TR_15
What does a Data model contai? (Choose all that apply)
Data Sources connect the data model to actual data.
Data Source Views draw data from data sources.
Report Models contain entities that end users can use on reports.
All of the above
Answer: All of the above
Question: 18
UCF: SQL 3.1
Topic: MON_TR_15
Which is not a rule in the Data Model generation?
Create entities for all the tables
Create indexed views
Create numeric aggregates
Create roles
Answer: Create indexed views
Question: 19
UCF: SQL 3.1
Topic: MON_TR_15
In addition to viewing data, what are the other tasks performed bz Report Manager
Enables view historical snapshots
Enables Subscription of reports to the end users
Only A)
Both a and b
Answer: Both a and b
Question: 20
UCF: SQL 3.1
Topic: MON_TR_15
Installing SSRS creates two new virtual directories under the default website on IIS. They are
Reports and ReportServer
ReportTemp and ReportServer
Report and ReportserverTemp
None of the above
Answer: Reports and ReportServer
Question: 21
UCF: SQL 3.1
Topic: MON_TR_15
Which is true about Reports site
hosts a web service for running and managing reports
allows you upload reports from a browser and run reports over the web.
Both A) and B)
None of the above
Answer: allows you upload reports from a browser and run reports over the web.
Question: 22
UCF: SQL 3.1
Topic: MON_TR_15
It is possible to add new parameters, maz or maz not be in the query in the Lazout of Report Designer
Yes
No
Cant Say
na
Answer: Yes
Question: 23
UCF: SQL 3.1
Topic: MON_TR_15
Which is not a SSIS object
Event handler
Data and Control flows
Tasks
Connections
Answer: Tasks
Question: 24
UCF: SQL 3.1
Topic: MON_TR_15
What is true about connection manager in SSIS?
Controls the data flow
Integrate different data sources into packages.
Handles event
None of the above
Answer: Integrate different data sources into packages.
Question: 25
UCF: SQL 3.1
Topic: MON_TR_15
Which is not a SSIS maintenance plan task_
Notify operator
History Cleanup
Execute T-SQL statement
Shrink File
Answer: Shrink File
Question: 26
UCF: SQL 3.1
Topic: PERF_TUN_15
Eliminate Columns not dependent on a key is
1NF
3NF
2NF
BCNF
Answer: 3NF
Question: 27
UCF: SQL 3.1
Topic: PERF_TUN_15
Eliminate Redundant Data is
1NF
3NF
2NF
4NF
Answer: 2NF
Question: 28
UCF: SQL 3.1
Topic: PERF_TUN_15
Large datawarehouses should have more indexes
True
False
maybe
maybe not
Answer: True
Question: 29
UCF: SQL 3.1
Topic: PERF_TUN_15
A primary key attribute due to itsrelationwith another key attribute creates multiple tuples withinan entity cappled
multipled dependency
combined dependency
Multivlued Dependency
associated dependency
Answer: Multivlued Dependency
Question: 30
UCF: SQL 3.1
Topic: PERF_TUN_15
SQL Server Lock information can be obtained using
sys.dm_os_sys_info
sys.dm_os_performance_counters
both
neither
Answer: sys.dm_os_performance_counters
Question: 31
UCF: SQL 3.1
Topic: PERF_TUN_15
/3GB switch improves performance in a 64 bit server
True
False
maybe
maybe not
Answer: False
Question: 32
UCF: SQL 3.1
Topic: PERF_TUN_15
Size of Pagefile is inversely proportional to the RAM
True
False
maybe
maybe not
Answer: True
Question: 33
UCF: SQL 3.1
Topic: PERF_TUN_15
Buffer Cache hit ratio should always be above
0.3
0.4
0.5
0.9
Answer: 0.9
Question: 34
UCF: SQL 3.1
Topic: PERF_TUN_15
AWE option should be set explicitly on 64 bit
False
True
maybe
maybe not
Answer: False
Question: 35
UCF: SQL 3.1
Topic: PERF_TUN_15
Windows 2003 server Datacenter edition supports
32 GB
64 GB
128GB
256 GB
Answer: 64 GB
Question: 36
UCF: SQL 3.1
Topic: PERF_TUN_15
Windows 2003 server SP1 Datacenter edition supports
16 GB
32 GB
64 GB
128 GB
Answer: 128 GB
Question: 37
UCF: SQL 3.1
Topic: PERF_TUN_15
A constant decrease in available memory is indicative of
i/o bottleneck
Memory Leak
wrong max memory setting
wrong cpu affinity setting
Answer: Memory Leak
Question: 38
UCF: SQL 3.1
Topic: PERF_TUN_15
How would you supress direct trigger recursion
Set RECURSIVE_TRIGGERS database option to OFF
set RECURSIVE_TRIGGER server option to 0
set NESTED TRIGGER server option to 0
Set NESTED_TRIGGERS database option to OFF
Answer: Set RECURSIVE_TRIGGERS database option to OFF
Question: 39
UCF: SQL 3.1
Topic: PERF_TUN_15
On performance standpoint what is the best way to store rows of data temporarily during an execution of SP?
#tables
Table variables
Permanent Tables
None of the above
Answer: Table variables
Question: 40
UCF: SQL 3.1
Topic: PERF_TUN_15
How would you improve the performance of a cursor
try to define READONLY Cursor
Try to limit the number of rows defined in the cursor
Select only the required columns in the Cursor
All of the above
Answer: All of the above
Question: 41
UCF: SQL 3.1
Topic: STORAGE_10
All successful SQL Server backup entries can be prevented from writing to the SQL Server Error Log by a single trace flag
3226
3116
3410
none
Answer: 3226
Question: 42
UCF: SQL 3.1
Topic: STORAGE_10
How do you backup Analysis Services databases?
Backup Database command
Backup service command
Create the XML statement to backup the Analysis Services databases
none
Answer: Create the XML statement to backup the Analysis Services databases
Question: 43
UCF: SQL 3.1
Topic: STORAGE_10
Which RAID level is known as Disk Striping?
A) RAID 0
B) RAID 1
C) RAID 2
D) RAID 10
Answer: A) RAID 0
Question: 44
UCF: SQL 3.1
Topic: STORAGE_10
Which RAID level provides striping with parity?
A) RAID 0
B) RAID 1
C) RAID 5
D) RAID 10
Answer: C) RAID 5
Question: 45
UCF: SQL 3.1
Topic: STORAGE_10
Combine all separate disks into a single storage volume called as.
A) striping
B) mirroring
C) intended
none
Answer: A) striping
Question: 46
UCF: SQL 3.1
Topic: STORAGE_10
Which leve RAID level has stripes data at byte level across multiple disks
A) RAID 0
B) RAID 1
C) RAID 2
D) RAID 3
Answer: D) RAID 3
Question: 47
UCF: SQL 3.1
Topic: STORAGE_10
Which leve RAID level has stripes data at the block level across multiple disks
A) RAID 2
B) RAID 3
C) RAID 4
D) RAID 5
Answer: C) RAID 4
Question: 48
UCF: SQL 3.1
Topic: STORAGE_10
Fault tolerance supported in
RAID 0
RAID 1
RAID 2
RAID 5
Answer: RAID 5
Question: 49
UCF: SQL 3.1
Topic: STORAGE_10
parity and Hot spare option supported in
RAID 0
RAID 1
RAID 2
RAID 5
Answer: RAID 5
Question: 50
UCF: SQL 3.1
Topic: STORAGE_10
Because of parity, information all data are available in case one of the disks fails in
RAID 0
RAID 1
RAID 2
RAID 5
Answer: RAID 5
SQL 3.2
Question: 1
UCF: SQL 3.2
Topic: CONCEPT_12
The _ system DMV can be used to check the source of memory pressure (external/internal)
sys.dm_os_memory_cache_clock_hands
sys.dm_os_latch_stats
sys.dm_os_loaded_modules
sys.dm_os_memory_cache_counters
Answer: sys.dm_os_memory_cache_clock_hands
Question: 2
UCF: SQL 3.2
Topic: CONCEPT_12
A perfectly configured SQL Server starts experiencing OOM Errors. This could be due to
SQL Server Buffer Pool is unable to commit the Virtual Address Space reserved
Memory leak in the System
Both of the above
None of the above
Answer: Both of the above
Question: 3
UCF: SQL 3.2
Topic: CONCEPT_12
In order to use locked pages on 64 bit systems, it is __ to turn ON AWE.
required
not required
NA
NA
Answer: not required
Question: 4
UCF: SQL 3.2
Topic: CONCEPT_12
A SQL Server installation is found to be using only 4 GB of memory inspite of configuring the Max Server memory to 8GB. What could be the reason?
SQL Server cannot ustilze more than 4 GB
Maximum amount of allowed memory is only 2 GB
The AWE option needs to be enabled on 32 bit systems
None of the above
Answer: The AWE option needs to be enabled on 32 bit systems
Question: 5
UCF: SQL 3.2
Topic: CONCEPT_12
A memory node component in SQL Server consists of
page allocators
virtual allocator leveraging Windows VirtualAlloc APIs
shared memory allocator
All of the above
Answer: All of the above
Question: 6
UCF: SQL 3.2
Topic: CONCEPT_12
SQL OS’s memory manager consists of several components such as memory objects and _
memory nodes
memory caches
memory clerks
All of the above
Answer: All of the above
Question: 7
UCF: SQL 3.2
Topic: CONCEPT_12
The following output is from the DBCC MEMORYSTATUS Command Buffer Distribution Buffers —————————— ———– Stolen 241 Free 95 Procedures 89 Inram 0 Dirty 16 Kept 0 I/O 0 Latched 18 Other 880 (9 row(s) affecteD) Pages for pinned tables that cannot be removed from memory are indicated by
Dirty
Inram
Kept
Other
Answer: Inram
Question: 8
UCF: SQL 3.2
Topic: CONCEPT_12
The output of the DBCC MEMORYSTATUS command contains information on relative distribution of stolen buffers among major component groups in the server in addition to
distribution of 8 KB buffers in the buffer pool
overall size and the makeup of the buffer pool
makeup of the procedure cache
All of the above
Answer: All of the above
Question: 9
UCF: SQL 3.2
Topic: CONCEPT_12
The __ DMV can be used to find out how much memory SQL Server has allocated through AWE mechanism
sys.dm_os_awe_memory_used
sys.dm_os_workers
sys.dm_os_memory_clerks
None of the above
Answer: sys.dm_os_memory_clerks
Question: 10
UCF: SQL 3.2
Topic: CONCEPT_12
The following SQL Component cannot utilize the AWE mapped memory
SQL Server Analysis Services
SQL Server Reporting Services
SQL Server Integration Services
SQL Server Database Engine
Answer: SQL Server Analysis Services
Question: 11
UCF: SQL 3.2
Topic: CONCEPT_12
On systems having more than 16 GB of RAM, the amount of user mode address space available to applications like SQL Server is
1 GB
2 GB
4 GB
As configured by Max Server Memory
Answer: 2 GB
Question: 12
UCF: SQL 3.2
Topic: CONCEPT_12
With MARS enabled, the amount of memory utilized by a User Connection is
Approximately (3 * network_packet_size + 94 KB)
Approximately (2 * network_packet_size + 94 KB)
approximately (3 + 3 * num_logical_connections) * network_packet_size + 94 KB
approximately (3 + 2 * num_logical_connections) * network_packet_size + 94 KB
Answer: approximately (3 + 3 * num_logical_connections) * network_packet_size + 94 KB
Question: 13
UCF: SQL 3.2
Topic: MIRROR_10
In the following statements which is the right command to Specify the partner from the principal server
Modify DATABASE [DB name] SET PARTNER = N’TCP://B.corp.mycompany.com:5022′
ALTER DATABASE [DB name] SET PARTNER = N’TCP://B.corp.mycompany.com:5022′
Set DATABASE [DB name] SET PARTNER = N’TCP://B.corp.mycompany.com:5022′
All of the above
Answer: ALTER DATABASE [DB name] SET PARTNER = N’TCP://B.corp.mycompany.com:5022′
Question: 14
UCF: SQL 3.2
Topic: MIRROR_10
cannot serve the database – When a principal server does not allow any user connections to the database and any transactions to be processed
True
False
NA
NA
Answer: True
Question: 15
UCF: SQL 3.2
Topic: MIRROR_10
Database mirroring does not use the partner servers as resource managers in a distributed transaction
True
False
NA
NA
Answer: True
Question: 16
UCF: SQL 3.2
Topic: MIRROR_10
Can I have more than one mirror in SQL 2005 ?
Yes
No In SQL Server 2005, each principal database can have only one mirrored database.
Partial
Unknown
Answer: No In SQL Server 2005, each principal database can have only one mirrored database.
Question: 17
UCF: SQL 3.2
Topic: MIRROR_10
Can I use Database Mirroring and Clustering?
Unknown
No
Yes
Manager needs to approve
Answer: Yes
Question: 18
UCF: SQL 3.2
Topic: MIRROR_10
Database Mirroring Went out of Sync due to existing Endpints.due to the error message : the endpoint is not started? how do you start the End points?
ALTER ENDPOINT [Mirroring] STATE = Enabled
ALTER ENDPOINT [Mirroring] STATE = STARTED
Create ENDPOINT [Mirroring] STATE = STARTED
NA
Answer: ALTER ENDPOINT [Mirroring] STATE = STARTED
Question: 19
UCF: SQL 3.2
Topic: MIRROR_10
Mirroring failed due to endpoints, how, you ensure thatport currently associated with database mirroring endpoint of a server instance
Using update statement against sys.tcp_endpoints table
Using SELECT statement against sys.tcp_endpoints table
Using recreate statement against sys.tcp_endpoints table
None of the above
Answer: Using SELECT statement against sys.tcp_endpoints table
Question: 20
UCF: SQL 3.2
Topic: MIRROR_10
To verify the results of the failover on the new mirror, the following query will give you the current value of mirroring_role_desc is now Mirror SELECT name, mirroring_role_desc GO FROM sys.databases WHERE name = N’AdventureWorks’;
False
True
NA
NA
Answer: True
Question: 21
UCF: SQL 3.2
Topic: MIRROR_10
To learn the role and state of the database mirroring endpoint of a server instance, on that instance, use the following Transact-SQL statement: SELECT role_desc, state_desc FROM sys.database_mirroring_endpoints
True
False
Na
Na
Answer: True
Question: 22
UCF: SQL 3.2
Topic: MIRROR_10
choose the following statement is true or false? A SET PARTNER or SET WITNESS command can complete successfully when entered, but fail later
False
True
Na
NA
Answer: True
Question: 23
UCF: SQL 3.2
Topic: REPORT_13
To enable the incoming request which port is opened in Microsoft Windows Firewall.
Port 1443
Port 80
Port 443
Port 1433
Answer: Port 80
Question: 24
UCF: SQL 3.2
Topic: REPORT_13
Which command is used to find the status of the Remote administration
netsh.exe
RemoteStat.exe
inetinfo.exe
Adminstat.exe
Answer: netsh.exe
Question: 25
UCF: SQL 3.2
Topic: REPORT_13
On the instance of SQL Server that is hosting the report server database.SQL Server Agent must be running before users define subscriptions and other scheduled operations.What is used to enable the SQL Server Agent.
SQL Server Surface Area Configuration tool
SQL Server Service configuration tool
Report Builder
Report Designer tool
Answer: SQL Server Surface Area Configuration tool
Question: 26
UCF: SQL 3.2
Topic: REPORT_13
If you are performing an upgrade from an earlier version of SQL Server Reporting Services to SQL Server 2005 Reporting Services, what value you will provide to SKUUPGRADE argument if the following conditions are true: a.You use an instance of SQL Server Express Edition to host the report server database. b.You are upgrading from the previously installed edition of Reporting Services and from the previously installed instance of SQL Server Express Edition.
0
1
2
3
Answer: 1
Question: 27
UCF: SQL 3.2
Topic: REPORT_13
Reporting server makes a request to whom to initially obtain report metadata.
report catalog
Reporting Data
Both
None
Answer: Both
Question: 28
UCF: SQL 3.2
Topic: REPORT_13
If the report server database is on a remote computer and you do not have permission to update that database, Setup prompts you to provide credentials to update to a remote report server database. what permissions we should provide.
SecurityAdmin
Setupadmin
Serveradmin
sysadmin
Answer: sysadmin
Question: 29
UCF: SQL 3.2
Topic: REPORT_13
If IIS is not running, you may receive which error during the SSRS setup.
29556
1603
1306
29956
Answer: 1603
Question: 30
UCF: SQL 3.2
Topic: REPORT_13
Which file Stores the code access security policies for the server extensions.
RSSrvpolicy.config
RSMgrPolicy.config
RSPreviewPolicy.config
RSReportServer.config
Answer: RSSrvpolicy.config
Question: 31
UCF: SQL 3.2
Topic: REPORT_13
which file is located at \Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies folder
RSReportServerServices.exe.config
RSReportDesigner.config
RSReportServer.config
RSPrivateAssemblies.config
Answer: RSReportDesigner.config
Question: 32
UCF: SQL 3.2
Topic: REPORT_13
While creating a new report, you are prompted to create a data source definition. To connect to data source you required three information.Data source type, connect information and _
Port number
Permission
Version
Database name
Answer: Permission
Question: 33
UCF: SQL 3.2
Topic: REPORT_13
Which table in reportserver database that stores the execution results of each report
ExecutionLog
ExecutionResult
ExecutionTrace
Executionstat
Answer: ExecutionLog
Question: 34
UCF: SQL 3.2
Topic: REPORT_13
Where does the .RDL stored
ReportServer Database
Report Folder
Windows Directory
All
Answer: ReportServer Database
Question: 35
UCF: SQL 3.2
Topic: REPORT_13
Which report is used in Visual Studio for client side reporting
RDL
RDLC
Both
None
Answer: RDLC
Question: 36
UCF: SQL 3.2
Topic: TU_15
What does the following command indicates when you are restoring your database using sql server Litespeed tool. exec master.dbo.xp_restore_database @database = ‘dbname’, @filename = ‘path to backup file’ , @file number =2
Restoring single backup from a Backup set (Multiple backups written to same file)
Restoring backup but only the second file
Restoring backup but only the second filegroup
Restoring backup but only the log file
Answer: Restoring single backup from a Backup set (Multiple backups written to same file)
Question: 37
UCF: SQL 3.2
Topic: TU_15
when you try to restore a database you got an error something like this: Server: Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure ‘master.dbo.xp_restore_database’. What does this indicate and how to correct this issue?
This indicates that there is a problem with the backup file, verify your backup and if corrupted get a new backup and restore the db
This indicates that you lost some of your system stored procedures, you need to restore Resource database with a backup you have you get all the updated system objects
This indicates that litespeed is not installed and hence you cannot use this backup command, instead use sql server native backup command, backup database to restore your database
This could be that you are executing this command in master database instead of MSDB, execute the same statement in Msdb database and you would be able to restore you database
Answer: This indicates that litespeed is not installed and hence you cannot use this backup command, instead use sql server native backup command, backup database to restore your database
Question: 38
UCF: SQL 3.2
Topic: TU_15
how do you take a databases backup other then SQL native Backup?
Lite Speed
Veritas
Tivoli
All of the above
Answer: All of the above
Question: 39
UCF: SQL 3.2
Topic: TU_15
LiteSpeed improves the performance of log shipping by reducing the size of the logs, thereby increasing the speed of the log transfer
True
False
NA
NA
Answer: True
Question: 40
UCF: SQL 3.2
Topic: TU_15
LiteSpeed allows you to restore only the individual objects (i.e., tables, stored procedures and views) you need, preventing disruption while you access the database.
True
False
NA
NA
Answer: True
Question: 41
UCF: SQL 3.2
Topic: TU_15
The technology of LiteSpeed is specifically designed to identify additional compression points within the database
False
True
NA
NA
Answer: True
Question: 42
UCF: SQL 3.2
Topic: TU_15
LiteSpeed allows you to write a single backup to multiple locations simultaneously, eliminating the need to make copies individually.
False
True
NA
NA
Answer: True
Question: 43
UCF: SQL 3.2
Topic: TU_15
LiteSpeed allows you to restore data down to the row level by incorporating SELECT statements into the recovery process, returning only the data you need from your backup file.
True
False
NA
NA
Answer: True
Question: 44
UCF: SQL 3.2
Topic: TU_15
The Backup Analyzer lets you test different backup parameters such as compression level, ncryption, backup locations and tuning capabilities(SQL Server 5.0.2 includes)
False
True
NA
NA
Answer: True
Question: 45
UCF: SQL 3.2
Topic: TU_15
LiteSpeed Maintenance Plans provide the ability to create maintenance plans with all the options of the native tool as well LiteSpeed’s power, regardless of plan complexity(SQL Server 5.0.2 includes)
True
False
NA
NA
Answer: True
Question: 46
UCF: SQL 3.2
Topic: TU_15
Log Reader makes online transaction logs and online backup logs (native and LiteSpeeD) visible. It also features the power to redo, undo, search and view transaction details(SQL Server 5.0.2 includes)
False
True
NA
NA
Answer: True
Question: 47
UCF: SQL 3.2
Topic: TU_15
WE can not manage all backup and recovery operations from a central user interface.
True
False
NA
NA
Answer: False
Question: 48
UCF: SQL 3.2
Topic: TU_15
LiteSpeed must be installed under a Local Administrator account with SA rights on SQL Server to allow installation of the registry keys, files and extended stored procedures necessary
True
False
NA
NA
Answer: True
Question: 49
UCF: SQL 3.2
Topic: TU_15
user account that will run backups/restores may be a Power User account.
False
True
NA
NA
Answer: True
Question: 50
UCF: SQL 3.2
Topic: TU_15
while using lightspeed backup you scheduled a backup job. Later some time you noticed that the backup file is not compressing expected. Where do you verify compress level?
At SQL job Level
At lightspeed parameters
At SQL default values
None of the above
Answer: At lightspeed parameters
SQL 4.1
Question: 1
UCF: SQL 4.1
Topic: MT_15
The public and private networks that we setup in cluster should have
dynamic IP addresses
static IP addresses
either static or dynamic IP addresses
virtual IP addresses
Answer: static IP addresses
Question: 2
UCF: SQL 4.1
Topic: MT_15
which of the following is not true in case of Qurom disks in sql server failover clusters
quorum disk contains a master copy of the server cluster configuration
used as a tie-breaker if all network communication fails between cluster nodes
the quorum resource share the same disk with other resources
quorum disk should be a physical disk on the shared cluster disk array
Answer: quorum disk should be a physical disk on the shared cluster disk array
Question: 3
UCF: SQL 4.1
Topic: MT_15
In a Clustering environment, this signal, is very lightweight and does not perform anything more than a simple check to see if the SQL Server 2005 instance is up and running, not if the instance can execute any operations, what is this signal called
IsAlive
IsWorking
LooksAlive
heartbeat
Answer: LooksAlive
Question: 4
UCF: SQL 4.1
Topic: MT_15
This is a private network set up between the nodes of the cluster that checks to see whether a server is up and running. This occurs at regular intervals known as time slices. If this is not functioning, a failover is initiated, and another node in the cluster will take over the services, what is this called?
IsAlive
LooksAlive
Heartbeat
IsWorking
Answer: Heartbeat
Question: 5
UCF: SQL 4.1
Topic: MT_15
This type of disk array is a setup in which only one node can own a given resource at any given moment. All other nodes are denied access until they own the resource. This protects the data from being overwritten data when two computers have access to the same drives concurrently, what is this disk array called?
Shared Nothing
Shared one
Shared array
FibreChannel
Answer: Shared Nothing
Question: 6
UCF: SQL 4.1
Topic: MT_15
Does SQL Server 2000 clustering support load balancing?
SQL Server 2000 clustering does not provide load balancing; it provides failover support. To achieve load balancing, you need software that balances the load between clusters, not between servers within a cluster
SQL Server 2000 clustering does not support load balancing
ALL the above
NA
Answer: ALL the above
Question: 7
UCF: SQL 4.1
Topic: MT_15
Does SQL Server 2000 Full-Text Search support clustering?
Yes
No
Required additional Hot fix
None
Answer: Yes
Question: 8
UCF: SQL 4.1
Topic: MT_15
Is it necessary to configure MSDTC as a clustered resource for SQL Server clustering to work?
No. MSDTC is required only if you need the ability to perform distributed transactions on your cluster
We can configure for Data Load
Yes
None
Answer: No. MSDTC is required only if you need the ability to perform distributed transactions on your cluster
Question: 9
UCF: SQL 4.1
Topic: MT_15
Can SQL Server 6.5 or 7.0 be installed on one node of a cluster and SQL Server 2000 be installed on the other node?
We can install ony with SQL 200 and SQL 2005
Possible only in Multinode Cluster
Using third-party Software
No. For two SQL Server servers to participate in a cluster, they must be the same version.
Answer: No. For two SQL Server servers to participate in a cluster, they must be the same version.
Question: 10
UCF: SQL 4.1
Topic: MT_15
How do I shut down SQL Server from the command line without the Cluster Service interpreting the shutdown as a failure?
It is not possible from Command Prompt
Cluster /offline
cluster [cluster name] RESOURCE [resource name] /option
Cluster /SQL resourse Online
Answer: cluster [cluster name] RESOURCE [resource name] /option
Question: 11
UCF: SQL 4.1
Topic: MT_15
Can I make the VMM2008 server highly available by configuring it as a clustered application on a failover cluster of physical servers?
We can not make
It is possible only when Single Node Cluster
No. Instead, you can run VMM 2008 on a highly available virtual machine.
None
Answer: No. Instead, you can run VMM 2008 on a highly available virtual machine.
Question: 12
UCF: SQL 4.1
Topic: MT_15
Is the use of software fault-tolerant disk sets for cluster storage supported?
No
Yes
None
using SAN manager Utility
Answer: No
Question: 13
UCF: SQL 4.1
Topic: MT_15
Where is the ” Cluster.Log” Location?
C:|winnt
C:\Program files\SQL Server
C:\Cluster
None
Answer: C:|winnt
Question: 14
UCF: SQL 4.1
Topic: MT_15
How does SQL Cluster Setup process detect that it is going to operate in a cluster environment?
It checks Cluster Service running
it Checks the cluster DLL files
it checks the MSCS installed
None
Answer: It checks Cluster Service running
Question: 15
UCF: SQL 4.1
Topic: MT_15
How many IP addres would require to build a Single Node Cluster?
9
8
6
4
Answer: 6
Question: 16
UCF: SQL 4.1
Topic: PT_15
You are mastering the company database, Users complaints that the SQL Server 2008 application is running slowly. You should check the information of the CPU utilization, disk utilization, and memory utilization. The network should be considered. the detailed information should be checked for the proper example of SQL Server2008.Which is the correct answer.
You should check the proper dynamic management views (DMVs).
In the databases, you should check the distribution of the statistics which are hosted
You should check a statistics update command’s the output
You should check the recovery model of the master database
Answer: You should check the proper dynamic management views (DMVs).
Question: 17
UCF: SQL 4.1
Topic: PT_15
You are managing a SQL Server 2008 instance which includes databases for a shopping application and a manufacturing application for the Company.com. You set the Resource Governor to cut the maximum CPU search time permitted through the applications. The administrator of the manufacturing application tells you that that some reports could not run successfully. You should make sure that you change the Resource Governor settings to permit the manufacturing application to cost more CPU time. Which is the correct answer?
The workload group that clarifies the shopping application
The classifier function that is used by default
The workload group that is utilized by default
The workload group which is utilized through the manufacturing application
Answer: The workload group which is utilized through the manufacturing application
Question: 18
UCF: SQL 4.1
Topic: PT_15
You are managing a SQL Server 2008 instance for Company.com. There are databases in the server for some applications. You decide to utilize the Resource Governor to cut the result of searches which are run through Microsoft SQL Server Management Studio. The requirements listed below are met: By SQL Server Management Studio, Searches initiated do not excel 40 percent of CPU utilization.By the applications, Searches initiated are permitted to cost 200 percent of CPU utilization when needed. Which is the correct answer?
You should develop a default resource pool and configure the MAX_CPU_PERCENT selection to 60.and give this resource pool to the workload group used.
You should develop a default resource pool and configure the MAX_CPU_PERCENT selection to 40.and give this resource pool to the workload group used.
You should develop a new resource pool and configure the MAX_CPU_PERCENT selection to 40.and give his resource pool to the workload group used through.
You should develop a new resource pool and configure the MAX_CPU_PERCENT selection to 60.and give this resource pool to the workload group used through
Answer: You should develop a new resource pool and configure the MAX_CPU_PERCENT selection to 40.and give his resource pool to the workload group used through.
Question: 19
UCF: SQL 4.1
Topic: PT_15
You are a database administrator for Company.com. You are responsible for monitoring the performance of your SQL Server 2005 computers. You have a SQL Server Integration Services (SSIS) package that usually takes 10 minutes to run. Today, the package is taking more than one hour to run. You suspect memory is too low. You need to identify the problem. What should you do?
Redesign the SQL Server Integration Services (SSIS) package so that it runs only when no exclusive table locks are in use
Check the time-out values in the SQL Server Integration Services (SSIS) package
Use the System Monitor to monitor the SQL Server Integration Services (SSIS) pipeline counters
Use SQL Server Profiler to monitor the Scans event category counters
Answer: Use the System Monitor to monitor the SQL Server Integration Services (SSIS) pipeline counters
Question: 20
UCF: SQL 4.1
Topic: PT_15
You are a database administrator for Company.com. Users report that a SQL Server 2005 application is performing slowly. You check the SQL Server error log and discover that the Server frequently runs out of memory to manage locks. The application designer informs you that dirty reads are acceptable. You need to resolve the problem indicated by the error log. What should you do?
Implement the Read Uncommitted isolation level
Execute the sp_locks stored procedure
Increase the maximum number of worker threads.
Configure the SQL Server max Server memory setting to be a lower value
Answer: Implement the Read Uncommitted isolation level
Question: 21
UCF: SQL 4.1
Topic: PT_15
You work as DBA at Company.com. You administer a SQL Server 2005 computer named Company A. Users report that all client applications that connect to Company A are responding slowly. You discover that Company As CPU and memory utilization is low and that disk activity is low. You need to identify the cause of the performance problem. What should you do?
Use System Monitor to examine Company As cache hit ratio
Examine the sys.dm_db_index_physical_stats dynamic management function (DMF).
Use Task Manager to examine utilization of Company As network adapter
Examine the sys.dm_db_partition_stats dynamic management view (DMV).
Answer: Use Task Manager to examine utilization of Company As network adapter
Question: 22
UCF: SQL 4.1
Topic: PT_15
You are a database administrator for Company.com. Users report that the performance of a SQL Server 2005 application seems slow. You verify that the SQL Server computers CPU utilization,disk utilization, and memory utilization are normal. You also verify that the network is operating normally. You need to examine more detailed performance information for the appropriate instance of SQL Server 2005. What should you do?
Examine the distribution of the statistics in the databases that are hosted by the instance
Examine the recovery model of the temp db database
Examine the appropriate dynamic management views (DMVs).
Examine the output of a statistics update command
Answer: Examine the appropriate dynamic management views (DMVs).
Question: 23
UCF: SQL 4.1
Topic: PT_15
You are a database administrator for Company.com. Users report that a SQL Server 2005 application stops responding for several seconds at a time during the day. This problem occurs randomly throughout the day and affects many users at once. When the problem occurs, you notice that CPU utilization and disk tilization on the SQL Server 2005 computer are low, but that the network is operating normally. You need to examine additional information relevant to the problem.What should you do?
Examine the sys.dm_tran_locks dynamic management view (DMV).
Examine the total number of lock requests on the SQL Server 2005 computer
Examine the output of the DBCC SHOWCONTIG command
Examine the output of the DBCC SQLPERF(IOSTATS) command.
Answer: Examine the sys.dm_tran_locks dynamic management view (DMV).
Question: 24
UCF: SQL 4.1
Topic: PT_15
You are a database developer for TestKing’s SQL Server 2000 database. The database is installed on a Microsoft Windows 2003 Server computer. The database is in the default configuration. All tables in the database have at least one index. SQL Server is the only application running on the server.The database activity peaks during the day, whensalesrepresentatives enter and update salestransactions. Batch reporting is performed after business hours. hesalesrepresentatives report slow updates and inserts.What should you do
Run system monitor on the SQL Server:Access Methods counter during the day. Use the output from System Monitor to identify which tables need indexes
Use the sp_configure system stored procedure to increase the number of locks that can be used by SQL
Increase the value of the min server memory option
Run SQL Profiler during the day. Select the SQL:BatchCompleted and RPC:Completed events and the EventClass and TextData data columns. Use the output from SQL profiler as input to the Index Tuning Wizard
Answer: Run SQL Profiler during the day. Select the SQL:BatchCompleted and RPC:Completed events and the EventClass and TextData data columns. Use the output from SQL profiler as input to the Index Tuning Wizard
Question: 25
UCF: SQL 4.1
Topic: PT_15
You are the administrator of a SQL Server 2005 computer. You create a job that performs several maintenance tasks on the server’s databases. You want the job to run whenever the server’s processor utilization falls below 5 percent. You create a new schedule for the job and specify the Start whenever the CPU(s) become idle option. After several days, you notice that the job has never executed although the server’s processor utilization has fallen below 5 percent several times. What should you do
Modify SQL Server Agent properties and specify a smaller idle time
Modify SQL Server Agent properties and specify a larger idle time
Write a stored procedure that executes the job whenever the @@IDLE system variable is less than 5
Write a stored procedure that executes the job whenever the @@IDLE system variable is grater than 5
Answer: Modify SQL Server Agent properties and specify a smaller idle time
Question: 26
UCF: SQL 4.1
Topic: PT_15
You are the administrator of a SQL Server 2000 computer that contains a database. Users report that the database responds slowly when they insert data. You need to modify the server to improve its performance. What should you do?
Install a larger hard disk
Increase the amount of RAM
Increase the size of the virtual memory paging file
Add an additional processor
Answer: Increase the amount of RAM
Question: 27
UCF: SQL 4.1
Topic: PT_15
You are the administrator of a SQL Server 2000 computer. The server contains your company’s investment-tracking database. Each day, 100 operators make approximately 5,000 changes to customer investments. In addition, daily and monthly reports are created from the investment data.Developers at your company need to optimize the database application. They need a sample of database query activity to discover if they can speed up the transactions. The developers also want to replay the sample on another SQL Server computer You need to capture the sample, but you want to minimize any increase to the workload of the server. What should you do
Run SQL Profiler on a client computer Configure SQL Profiler to monitor database activity, and log data to a .trc file
Run SQL Profiler on the server Configure SQL Profiler to monitor database activity, and log data to a database table
Run SQL Profiler on the server Configure SQL Profiler to monitor database activity, and log data to a text file
Run SQL Profiler on the server Configure SQL Profiler to monitor database activity, and log data to a database table
Answer: Run SQL Profiler on a client computer Configure SQL Profiler to monitor database activity, and log data to a .trc file
Question: 28
UCF: SQL 4.1
Topic: PT_15
Your company has a server named SQL1 that runs SQL Server 2005 Enterprise Edition. SQL1 has 2 GB of RAM, 1.6GB 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
MSAS 2005:Memory
MSAS 2005:Proactive Caching
SQLServer:Buffer Manager
SQLServer:SQL Statistics
Answer: SQLServer:Buffer Manager
Question: 29
UCF: SQL 4.1
Topic: PT_15
You use an order application named App1, which connects to a SQL Server 2005 database named DB1. Because some actions take more than three seconds, users report slow performance when they use App1. You want to identify those actions by using the Tuning template in SQL Server Profiler to trace the application. You need to capture only those App1 actions in DB1 that have a response time of two seconds or more. What should you do
Add the ApplicationName column and create the following column filters.ColumnOperatorValueDurationGreater than or equal2000ApplicationNameLikeApp1DatabaseNameLikeDB1
Add the ApplicationName column and create the following column filters.ColumnOperatorValueDurationGreater than or equal to ApplicationName Like App1DatabaseName Like DB1
Add the CPU column and create the following column filters.ColumnOperatorValueCPUGreater than or equal2000TextDataLikeApp1DatabaseNameLikeDB1
Add the ApplicationName and CPU columns and create the following column filters.ColumnOperatorValueCPUGreater than or
Answer: Add the ApplicationName column and create the following column filters.ColumnOperatorValueDurationGreater than or equal2000ApplicationNameLikeApp1DatabaseNameLikeDB1
Question: 30
UCF: SQL 4.1
Topic: PT_15
You are managing a SQL Server 2008 instance which includes databases for a shopping application and a manufacturing application for the Company.com. You set the Resource Governor to cut the maximum CPU search time permitted through the applications. The administrator of the manufacturing application tells you that that some reports could not run successfully. You should make sure that you change the Resource Governor settings to permit the manufacturing pplication to cost more CPU time. Which is the correct answer?
The workload group that clarifies the shopping application
The classifier function that is used by default
The workload group that is utilized by default
The workload group which is utilized through the manufacturing application
Answer: The workload group which is utilized through the manufacturing application
Question: 31
UCF: SQL 4.1
Topic: SOL_5
Can we restore a SQL Server 2000 database backup to a SQL Server 7.0 server?
yes, DBRestore is possible if the SQL Server DB have no Consistency errors
No. The only way to move a SQL Server 2000 database to a SQL Server 7.0 server is by transferring the data using a method such as DTS, bcp, or use of a query between linked servers.
yes ..Restore DB is possible if .ndf and .ldf files are present though .mdf files are missing
Yes ..Database is in Full Recovery mode can be Restored
Answer: No. The only way to move a SQL Server 2000 database to a SQL Server 7.0 server is by transferring the data using a method such as DTS, bcp, or use of a query between linked servers.
Question: 32
UCF: SQL 4.1
Topic: SOL_5
Running DBCC Commands with Allow Dataloss are best suited when run
DBOnline
DB Single User mode
DB Restoring
DB MultiUser mode
Answer: DB Single User mode
Question: 33
UCF: SQL 4.1
Topic: SOL_5
MasterDB supports which type of Back-up
Full DB Back-up
Differential Back-up
Transaction Log
File and Filegroup Back-up
Answer: Full DB Back-up
Question: 34
UCF: SQL 4.1
Topic: SOL_5
ONLINE RESTORE in SQL Server 2005
allows to restore the DB Online and access the data being restored
allows to restore the DB Online and only the data being restored is unavailable
allows to access the filegroup data and shrink DB
Allows slowness in DB Transactions
Answer: allows to restore the DB Online and only the data being restored is unavailable
Question: 35
UCF: SQL 4.1
Topic: SOL_5
How can I grant to user the RESTORE DATABASE permission using TSQL ?
sp_changesrvrolemember
sp_addrolemember
sp_addsrvrolemember
sp_add_dbadmin
Answer: sp_addsrvrolemember
Question: 36
UCF: SQL 4.1
Topic: SSIS_5
You can install DTS and SSIS on the same computer and run them?
You can install but cant run
cant install both
Can install and run
cant install both
Answer: Can install and run
Question: 37
UCF: SQL 4.1
Topic: SSIS_5
If you are a previous or current user of Microsoft SQL Server 2000 Data Transformation Services (DTS) you have the following options when you install Microsoft SQL Server 2005 Integration Services (SSIS)?
Installing Support for SQL Server 2000 Data Transformation Services Packages
Upgrading an earlier version of Data Transformation Services to SSIS
Migrating existing Data Transformation Services packages to SSIS
All of the above
Answer: All of the above
Question: 38
UCF: SQL 4.1
Topic: SSIS_5
SQL Server 2005 provides what for migrating SQL Server 2000 DTS packages to the Integration Services package format?
DTS migration wizard
Package Migration wizard
ssis migration wizard
None of the above
Answer: Package Migration wizard
Question: 39
UCF: SQL 4.1
Topic: SSIS_5
IIS 1.0 or later is required for Microsoft SQL Server 2005 Reporting Services (SSRS) installations?
5.0 or later
2.0 or later
3.0 or later
4.0 or later
Answer: 5.0 or later
Question: 40
UCF: SQL 4.1
Topic: SSIS_5
To ensure a complete installation of Integration Services, you must select what on the Components to Install page?
Install all components
Database Engine
The Business Intelligence Development Studio
Integration Services
Answer: Integration Services
Question: 41
UCF: SQL 4.1
Topic: STO_10
Which of the following is not a best practice on TEMPDB
keeping the recovery model to simple
keeping the database owner to SA
drop and recreate tempdb by restarting sql server service atleast once a day
Do not change collation from the SQL Server instance collation
Answer: drop and recreate tempdb by restarting sql server service atleast once a day
Question: 42
UCF: SQL 4.1
Topic: STO_10
What are version stores
is a collection of log pages of tempdb
is a collection of data pages that hold the data rows that are required to support the features that use row versioning
is a collection of all data pages in tempdb
is a collection of all data and log pages in tempdb
Answer: is a collection of data pages that hold the data rows that are required to support the features that use row versioning
Question: 43
UCF: SQL 4.1
Topic: STO_10
Which DMV is used to monitor disk space used by tempdb files
sys.dm_db_file_space_usage
sys.dm_io_virtual_file_stats
sys.dm_tran_version_store
sys.dm_os_latch_stats
Answer: sys.dm_db_file_space_usage
Question: 44
UCF: SQL 4.1
Topic: STO_10
what’s the difference in the results of dbcc sqlperf(logspace) and dbcc showfilestats
both give information about data files and there is no difference
both give information about data files, the first one gives in the form of MBs while the latter gives the results in Extents
both give information about data files, the first one gives in Extents while the latter gives information in MBs
The first one gives information about data files while the latter gives information about column statistics
Answer: both give information about data files, the first one gives in the form of MBs while the latter gives the results in Extents
Question: 45
UCF: SQL 4.1
Topic: STO_10
which of the following is not one of the requirements that you would look at before doing capacity planning for your databases
expected number of users
usage profile of the system
max CPU usage
max memory usage
Answer: max memory usage
Question: 46
UCF: SQL 4.1
Topic: STO_10
What should be the value of Priority Boost in case of sql server 2005 failover clusters
0
1
2
you should not use this setting
Answer: you should not use this setting
Question: 47
UCF: SQL 4.1
Topic: STO_10
What is a LUN
This is the identification number assigned to a volume when created on a SAN device
are collections of storage devices and fibre switches connected together along with the servers that access the storage on the device
another way to refer to the physical disk drives that make up the RAID array
A general term that refers to the disks on the server
Answer: This is the identification number assigned to a volume when created on a SAN device
Question: 48
UCF: SQL 4.1
Topic: STO_10
there is a performance impact while writing data to the array because a parity bit must be calculated for each write operation performed, which RAID level are we talking about here?
RAID 0
RAID 1
RAID 5
RAID 10
Answer: RAID 5
Question: 49
UCF: SQL 4.1
Topic: STO_10
It is recommended that for larger systems, each file group of the database should
have between .25 to one physical file per physical CPU core in the server
have between .5 to one physical file per physical CPU core in the server
have between .75 to one physical file per physical CPU core in the server
have between 1 to 2 physical files per physical CPU core in the server
Answer: have between .25 to one physical file per physical CPU core in the server
Question: 50
UCF: SQL 4.1
Topic: STO_10
What is the best RAID level suited for storing Transaction log files
RAID 0
RAID 1
RAID 5
RAID 0+1
Answer: RAID 0+1