Reading...
Play button
Play button
Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
293 Cards in this Set
- Front
- Back
|
Chapter 1: New Features in SQL Server 2008
|
xx
|
|
|
1. You are setting up security for your new SQL Server 2008 installation. Management is concerned about security. What approach should you take to ensure security settings are optimal?
1. Use the Surface Area Configuration Tool to secure the installation 2. Use the new Security Analysis tool to secure the installation 3. Use SQL Server Configuration Manager to secure the installation 4. Use Windows Service Manager to enable and disable the appropriate services |
The correct answer is C. On the Start menu, point to All Programs | Microsoft SQL Server 2008 | Configuration Tools | SQL Server Configuration Manager.
Answer A is incorrect because the Surface Area Configuration Tool is no longer used to secure SQL Server 2008.Answer B is incorrect because there is no Security Analysis tool in SQL Server 2008.While Answer D may work, it’s not the best answer.Your answer should be the most “correct” using the new features, even though some of the other answers may technically work. |
|
|
2. You have been tasked with setting up standards for your SQL Server 2008 installation.You need to enforce a table naming convention.What is the best way to accomplish this in your SQL Server 2008 environment?
1. Use Windows Group Policy 2. Create DDL Triggers 3. CreateDMLTriggers 4. Create a Declarative Management Framework policy |
The correct answer is D.This is the most efficient way to accomplish the task of enforcing a table naming convention.Answer A is incorrect— Windows Group Policy does not provide a mechanism for enforcing table naming conventions. While Answer B could would, it’s not the best solution. Answer C is incorrect as DML triggers provide no means to control
DDL code. |
|
|
3. You have been asked to create a backup of your production database and restore it on a development server.Your production server is using the full recovery model. Full backups are taken Monday and Wednesday.Transaction log backups are taken every hour.Today is Friday.The backup needs to be created as quickly as possible.What’s the fastest way to get the latest database copy while minimizing impact to production?
1. Create a normal backup. Use that to restore to development. 2. Create a Copy Only Backup. Use that to restore to development. 3. Use the Wednesday backup. Restore the transaction log backups since Wednesday. 4. Copy the .mdf and log files and use SP_attach. |
The ideal situation would be to use B, the Copy Only Backup. Copy Only doesn’t affect the backup chain and will provide the quickest solution while minimizing interruptions. Answer A will affect the backup chain, so it’s somewhat disruptive. Answer C is not the fastest method, and there would be quite a few transaction logs to restore. Answer D would require stopping the SQL service on the production server, rendering it unusable during
the copy. |
|
|
4. You have a SQL Server 7.0 database and would like to move it to a new SQL Server 2008 instance.The database is part of an FDA-validated system and cannot be changed at all? How can this be accomplished?
1. Restore the 7.0 database on your new server and set the compatibility mode to 7. 2. You must upgrade the database to SQL 2005 or greater. 3. Restore the 7.0 database on your new server and set the compatibility mode to 6.5. 4. Copy the .mdf and log files and use SP_attach. |
The correct answer is B.Answers A and C are incorrect as SQL Server 2008 does not support 7 or 6 compatibility.Answer D is incorrect as the database must be upgraded and cannot just be attached.
|
|
|
5. You have an application that is being upgraded from SQL Server 2005 to SQL Server 2008.You notice that some stored procedures are not working correctly. An excerpt is as follows:
SELECT * FROM Territories, Region WHERE territories.regionid *= region.regionid What should you do to resolve the issue? A. There is no issue.The problem lies elsewhere. B. The join syntax is incorrect. Replace with left join. C. The select is incorrect.You need to enumerate the fields. D. The where clause should be = not ∗=. |
The answer is B.The join syntax is no longer supported.Answer A is incorrect, as the old join syntax (*= and =* ) will no longer work. Answer C is not cor- rect; although Select ∗ will work, it is not good practice. Answer D is incorrect as it will change the left join to an inner join, which would change the overall function of the select statement.
|
|
|
6. Your disk is almost full on the transaction log drive for a database server. How can you resolve this issue?
A. UseBACKUPLOGWITHTRUNCATE_ONLY B. Change the mode to simple and shrink the log C. Reinstall SQL D. Drop the database and restore from the last backup |
The correct answer is B.Answer A would be the answer with SQL Server 2005; however, this command is no longer available. Answer C will not solve the problem, and Answer D would result in data loss and could take a while.
|
|
|
7. You want to enforce a standard naming convention for stored procedures. What’s the best way to do this in SQL Server 2008?
A. Create a DDL trigger B. Use the performance data warehouse C. Create DML triggers D. Use the SQL Server 2008 Declarative Management Framework |
The correct answer is D.The DMF will allow you to perform the task at hand.Answer B has nothing to do with enforcing naming conventions.While Answer A could work, it’s not the best approach. Answer D will not allow you to perform the task of enforcing naming conventions.
|
|
|
8. You want to enforce a standard naming convention for tables and stored procedures.Your company has two SQL Server 2008 servers and 60 SQL Server 2005 servers.You need to use the same solution on all servers.What’s the best way to do this in SQL Server 2005 and SQL Server 2008?
A. Create a DDL trigger for all servers B. Use the performance data warehouse C. Create DML triggers D. Use the SQL Server 2008 Declarative Management Framework |
The correct answer is A.This will work on SQL Server 2005 and SQL Server 2008 servers.While Answer D is correct, the DMF will allow you to perform the task at hand only on SQL Server 2008 servers.Answer B has nothing to do with enforcing naming conventions. D will not allow you to perform the task of enforcing naming conventions.
|
|
|
9. You have a database table with a varchar(600) field in it. Most of the records in the table have a null value for this field. How can you save space?
A. Move the data into a second table B. Use sparse columns C. Install a third-party tool on the machine to compress the data D. Use the SQL Server 2008 Declarative Management Framework |
The correct answer is A. Sparse columns will take up less space for varchar columns. Sparse columns will take up less space for most columns, but cannot be used for text, ntext, image, timestamp, user-defined data type, geometry, or geography or varbinary (max) with the FileStream attribute can be sparse. Answer C is incorrect; this is not the best approach. Answer D is incorrect, as the Declarative Management Framework will not save
you space. |
|
|
10. You have a database table with a FileStream field in it. Most of the records in the table have a null value for this field.What’s the best way to save space?
A. Move the data into a second table B. Use sparse columns C. Use the SQL Server 2008 Declarative Management Framework D. None of the above |
The correct answer is D.Answer A is incorrect;this will not save space.Answer B is incorrect, as sparse columns will take up less space for most columns, but cannot be used for text, ntext, image, timestamp, user-defined data type, geometry, or geography or varbinary (max) with the FileStream attribute can be sparse.
|
|
|
11. You need to store images in for a Web site using SQL Server 2008. How can you accomplish this?
A. Use a FileStream data type, and the images will be stored on disk B. Use a varchar data type and store the images in that field C. Use an int data type and store the images in that field D. Use an nchar data type and store the images in that field |
The correct answer is A.The FileStream data type is meant for storing files on disk.You could also store the image in a varbinary field, but moving forward the recommended approach is to use the FileStream data type.
Answers B, C, and D all reference data types that cannot be used to store binary data like image files. One approach is to use a varchar field as a pointer to the disk, but this is not recommended after the introduction of the FileStream data type. |
|
|
12. You are responsible for a system that is used for both online transaction processing (OLTP) and reporting.When reports run on the server, the OLTP process slows way down. How can you allow reports to be run on the server and minimize impact to the OLTP processes?
A. Use the Resource Governor B. Use a DDL trigger C. Use a DML trigger D. Use processor affinity masks |
A is the correct answer.The Resource Governor allows you to assign workloads to different groups, then assign a priority to those groups.
Answers B and C are incorrect.Triggers cannot be used for this purpose. Answer C is incorrect. Processor affinity masks cannot be used for this purpose. |
|
|
13. You are creating an application to track crime in different locations throughout a large city.What data type could prove useful for storing location data (longitude and latitude)?
A. Varchar B. int C. Char D. Spatial |
The correct answer is D. Spatial data types are ideal for this type of data. Answer A could work, but is not ideal. Answers B and C are not ideal for storing this type of information.
|
|
|
14. You are running out of space on the drive used to store backups.All of the servers use the same network location.What can you do to save space with your backups while maintaining the same number of backups?
A. Use data compression B. Use compressed backups C. Use full backups D. Use a third-party tool |
B is the correct answer. Compressed backups take up less space. Answer A is incorrect; this will save space in the data files. Answer C is incorrect; this will not save space. Answer D will work, but it’s not the best solution.
|
|
|
15. You need to store sensitive data in your SQL Server database.The application has already been written and works fine.What’s the easiest way to do this without having to change your application?
A. Modify the stored procedures to use xp_encryptstring B. Use transparent data encryption C. Use a third-party tool D. Use a trigger |
The correct answer is B.Transparent data encryption enables data to be stored securely by encrypting the database files. Answer A is incorrect, as this is not the best approach. Answer C is incorrect; while there are third-party encryption tools, this is not the best approach. Answer D is incorrect; triggers by themselves are not a correct solution.
|
|
|
16. Within your application, you need to log all changes to one table. DDL and DML changes must be logged.What’s the best approach to solve this problem?
A. Use the built-in auditing capability B. Create a DDL trigger C. Create a DML trigger D. This cannot be accomplished |
A is the correct answer. SQL Server 2008 has built-in auditing capability. Answers B and C are incorrect as they are not a complete solution.Answer D is incorrect, as this can be accomplished with Answer A.
|
|
|
17. You have a server that supports Hot Add CPU.The current CPU utilization is 95 to 100 percent most of the time.The server is mission-critical and cannot be shut down. SQL Server 2008 Standard Edition is installed.What should you do?
A. Use the Hot Add CPU feature to add another CPU B. Use the Hot Add CPU feature to add two CPUs C. Add more memory to the server D. Schedule an outage and add another CPU to the server |
The correct answer is D. SQL Server 2008 Standard Edition does not support Hot Add CPU, which makes Answers A and B incorrect. Answer C is incorrect—the issue is CPU, so adding more memory will in all likelihood not solve the problem.
|
|
|
18. You are contemplating using data compression on a table.You would like to know how much space this will save. How can you determine the savings?
1. View the table properties 2. Enable compression, then check the table size 3. Use sp_estimate_data_compression_savings 4. Use sp_check_compression |
The correct answer is C. sp_estimate_data_compression_savings will help you determine if the table is worth compressing.
This procedure accepts five parameters. 1. @schema_name 2. @object_name 3. @index_id 4. @partition_number 5. @data_compression The two critical parameters are @object_name and @data_compression. Answer A is incorrect; compression information is not shown in the table properties.Answer B will not solve the problem before applying compression. Answer D is incorrect, as there is no stored procedure by that name. |
|
|
19. You have a server that supports Hot Add Memory. Performance is sluggish, and you believe adding more memory will help.The server is mission-critical and cannot be shut down. SQL Server 2008 Standard Edition is installed. What should you do?
A. Use the Hot Add CPU feature to add another CPU B. Use the Hot Add CPU feature to add two CPUs C. Add more memory to the server. D. Schedule an outage and add memory to the server. |
The correct answer is D. SQL Server 2008 Standard Edition does not support Hot Add Memory, which makes Answer C incorrect. Answers A and B are incorrect—the issue is memory, so adding more CPU will in all likelihood not solve the problem.
|
|
|
20. You have a SQL Server 2008 installation, and you want to create a high- availability solution.What are the ideal approach(es) to solve this problem?
A. Backup and restore B. Replication C. Mirroring D. Clustering |
The correct answers are C and D. C and D are automated high-availability solutions.Answer A is not necessarily a good solution as it is not automated. Answer B will create a copy of a database, but will require intervention in order for the replicated database to become the active database.
|
|
|
Chapter 2: Installing SQL Server 2008
|
xx
|
|
|
1. Which SQL Server 2008 edition supports IA64?
1. Web and Compact Editions 2. Standard and Workgroup Editions 3. Enterprise and Developer editions 4. All of the above |
Correct Answer & Explanation: C.The Enterprise and Developer Editions support IA64.
|
|
|
2. How many instances can be created in SQL Server 2008 Standard Edition? A. 1
B. 4 C. 16 D. Unlimited |
Correct Answer & Explanation: C. SQL Server 2008 Standard Edition supports 16 instances, as do the Workgroup,Web, and Express Editions.
The Enterprise Edition can have up to 50. |
|
|
3. How many SQL Server default instances can be installed on a single, nonclus- tered server?
A. 1 B. 4 C. 16 D. Unlimited |
Correct Answer & Explanation: A.There can only be one default instance of SQL Server on any SQL Server installation.
|
|
|
4. Which of the following editions has all the features and functionality but is not licensed for production?
A. Compact Edition B. Web Edition C. Developer Edition D. Workgroup Edition |
Correct Answer & Explanation: C.The Developer Edition has all the features and functionality of SQL Server 2008 Enterprise Edition, but it is not licensed for production use.
|
|
|
5. Which SQL Server 2008 edition is best suited for mobile applications? A. Compact Edition
B. Web Edition C. Developer Edition D. Workgroup Edition |
Correct Answer & Explanation: A.The Compact 3.5 Edition is best used for desktop and mobile applications because of its smaller footprint than the other versions.This is critical in mobile devices where disk space is at a premium.
|
|
|
6. Which SQL Server 2008 edition provides the most Analysis Services features? A. Enterprise Edition
B. Standard Edition C. Workgroup Edition D. Web Edition E. Express Edition |
Correct Answer & Explanation: A. Only the Enterprise and Developer Editions provide all the Analysis Services features.
|
|
|
7. Which tool should be on an existing SQL Server 2000 or 2005 database instance prior to upgrading to SQL Server 2008?
A. Integration Services B. Reporting Services C. Analysis Services D. UpgradeAdvisorTool |
Correct Answer & Explanation: D.The Upgrade Advisor Tool should be used on an existing SQL Server 2000 and 2005 instance to provide guidance prior to an upgrade to SQL Server 2008.
|
|
|
8. Which of the following is a command-line utility used to run PowerShell command and scripts?
Self Test Appendix • Appendix 11 12 Appendix • Self Test Appendix A. sqlcmd B. SQLdiag C. Ssms D. sqlps |
Correct Answer & Explanation: D. sqlps is used to run PowerShell commands and scripts. Sqlcmd is used to execute T-SQL command and pro- cedures. Ssms is used to start the SQL Server Management Studio, and SQLdiag is used to collect diagnostic information to supply to Microsoft Customer Service and Support.
|
|
|
9. Which of the following editions does not support any Reporting Services features?
A. Enterprise Edition B. Standard Edition C. Workgroup Edition D. Web Edition E. Express Edition |
Correct Answer & Explanation: E. SQL Server 2008 Express Edition does not provide any Reporting Services features. Enterprise Edition provides all the features, while Standard,Workgroup, and Web have limited features of Reporting Services.
|
|
|
10. Which of the following accounts has the least privilege when used as a service account?
A. Domain user account B. Local user account C. Local service account D. Administrator account E. Local system account |
Correct Answer & Explanation: B. Local user account has only local user privileges, which are not sufficient for a service account.
|
|
|
11. Which of the following accounts should be used to perform a SQL Server 2008 installation or upgrade?
A. Domain user account B. Local user account C. Local service account D. Administrator account E. Local system account |
Correct Answer & Explanation: D, Administrator account. An account with Administrator privileges is required to install SQL Server 2008.
|
|
|
12. In which of the following directories are the default instance database data and log files located?
1. <drive>:\Program Files\Microsoft SQL Server\\100\Setup Bootstrap\ LOG\Files\ 2. <drive>:\Program Files\Microsoft SQL Server\MSSQL10. MSSQLSERVER\Data 3. <drive>:\Program Files\Microsoft SQL Server\MSSQL9.DEFAULT\Data 4. <drive>:\Program Files\Microsoft SQL Server\MSAS10. MSSQLSERVER\Data 5. <drive>:\Program Files\Microsoft SQL Server\MSRS10. MSSQLSERVER\Data |
Correct Answer & Explanation: B. SQL Server 2008’s default directory is under the Program Files\Microsoft SQL Server.The name of the database directory is prefaced with the SQL Server Version ID of MSSQL10, plus a period and the name of the database.The default database name is always MSSQLSERVER.
|
|
|
13. What additional software is required for SQL Server’s Business Intelligence Developer’s Tool?
1. .NET Framework 2.0 2. Microsoft Installer 4.5 3. Microsoft Internet Explorer 6 SP1 or later 4. Windows PowerShell 2.0 5. Windows Vista Enterprise SP1 or greater |
Correct Answer & Explanation: C, Microsoft Internet Explorer 6 SP1 or later. ...NET Framework 3.5, Microsoft Installer 4.5, and Windows PowerShell 1.0 are required as part of the installation of SQL Server 2008.
|
|
|
14. Which of the following is the SQL Server component that provides the ser- vices for creating, processing, and managing OLAP and data mining?
A. Business Intelligence Developer Studio B. SQL Server Management Studio C. Windows PowerShell 2.0 D. SQL Server Database Engine E. Analysis Services |
Correct Answer & Explanation: E,Analysis Services.The SQL Server Database Engine contains the services to manage and process relational and XML data. Business Intelligence Developer Studio is used for managing all SQL Server 2008 components, including relational database and business intelligence (BI). SQL Server Management Studio is used for administering and managing all SQL Server 2008 components.Windows PowerShell 2.0 is a command-line shell and Microsoft scripting language.
|
|
|
15. Which of the following is a scalability feature in SQL Server Enterprise Edition?
A. Partitioning B. Data Mining Queries C. Binary and compressed XML transport |
Correct Answer & Explanation: A, Partitioning
|
|
|
16. In which directory are the installation log files found?
A. <drive>:\Program Files\Microsoft SQL Server\\100\Setup Bootstrap\LOG\Files\ B. <drive>:\Program Files\Microsoft SQL Server\\90/Data C. <drive>:\Program Files\Microsoft SQL Server\\Binn D. <drive>:\Program Files\Microsoft SQL Server\\logs |
Correct Answer & Explanation: A.The installation log files are located in the directory <drive>:\Program Files\Microsoft SQL Server\\100\Setup Bootstrap\LOG\ Files\
|
|
|
17. Which system database is hidden to SQL Server Management Studio and is used to persist a read-only version of system objects?
A. Master B. Resource C. Tempdb D. System |
Correct Answer & Explanation: B, Resource. Master and resource are system databases but do not perform the function to persist read-only versions of system objects.There is no system database named system.
|
|
|
18. Which is the compatibility version associated with SQL Server 2008?
A. 80 B. 90 C. 100 D. 200 |
Correct Answer & Explanation: C, 100. 80 is associated with SQL Server 2000, and 90 is associated with SQL Server 2005. 200 is an invalid compatibility version.
|
|
|
19. Which SQL Server tool is used to check that SQL Server component services
are running? 1. SQL Server Management Studio (SSMS) 2. Business Intelligence Development Studio (BIDS) 3. SQL Server Configuration Manager 4. SQL Server Profiler 5. Database Engine Tuning Advisor |
Correct Answer & Explanation: C, SQL Server Configuration Manager.
|
|
|
20. Which type of file contains data and database objects such as tables and indexes?
A. Data files B. Log files C. File groups D. Binary files E. Summary.txt |
Correct Answer & Explanation: A, data files.
|
|
|
Chapter 3: Configuring SQL Server 2008
|
xx
|
|
|
1. You’ve just started as a DBA at a company that has recently installed a dedicated server running both a SQL Server 2008 default instance and the database for the company’s ERP system.You want to implement a process to test the database with new SQL Server patches and service packs before they are applied to the live database.There is no budget to buy any new servers, but the existing hardware is under-utilized and you’re confident it will suffice for your short-term needs.Which of these options would meet your needs?
A. Restore a copy of the live database as test on to its own disk array.Apply patches and service packs to the new database and test them before applying them to the live database. B. Install a named instance for testing. Create a database snapshot on the named instance pointing to the live database on the default instance.Apply patches and service packs to the named instance and test before applying them to the default instance. C. Install another default instance for testing and restore a copy of the live database.Apply patches and service packs to the second instance and test before applying them to the live instance. D. Install a named instance for testing and restore a copy of the live database. Apply patches and service packs to the named instance and test before applying them to the default instance. |
Correct Answer & Explanation: D. SQL Server patches and service packs are applied at the instance level. Having a separate instance with a copy of the live database is the only way to achieve the requirements.
Incorrect Answers & Explanations: A, B, C. Answer A is incorrect because you cannot apply service packs and patches to individual databases.Answer B is incorrect because you cannot create a database snapshot between instances. You have to restore a copy of the database onto the instance.Answer C is incorrect because you can only have one default instance. |
|
|
2. Last month your company went through an exercise to standardize service accounts while you were on holiday.You’ve just restarted SQL Server after applying a service pack, and now the instance won’t start.You check the Windows Event Log and see lots of errors about not being able to open the error log.What is the most likely cause?
A. The SQL Server Service account was not changed using SQL Server Configuration Manager B. The service pack failed C. Data corruption D. You don’t have the right permissions to the error log |
Correct Answer & Explanation: A. If you don’t use the Configuration Manager tool to change the service account, it will not get explicit permissions to the SQL Server directory structure. SQL Server will not start without permissions to create an error log file.
Incorrect Answers & Explanations: B, C, D. Answer B is incorrect, although technically feasible; the service pack should have generated errors if there was a problem, and given the scenario it is not the most likely cause. Answer C is incorrect, although technically feasible; given the lack of any other errors and the scenario it is not the most likely cause.Answer D is incorrect because the issue is with the Service Account permissions, not your account.To install the service pack you would have needed full Administrator rights on the server anyway. |
|
|
3. You have a default instance and a named instance installed on your server.The developers are complaining that they can’t connect to the named instance from their workstations using Management Studio anymore.You log on to the server through Terminal Services and can connect to each instance without issue.What could the cause of this behavior be?
A. The developers haven’t created a SQL Server Alias to the named instance using the Shared Memory protocol. B. The SQL Browser Service has been started. C. The SQL Browser Service has been stopped. D. The IP addresses of the developers’ workstations have been added to the Exclude list in the TCP/IP properties of the named instance in Configuration Manager. |
Correct Answer & Explanation: C. By default, named instances used dynamic TCP port allocation, and clients depend on the SQL Browser Service to find the current port. Incorrect Answers & Explanations: A, B, D. Answer A is incorrect because you can’t create an alias that uses the Shared Memory protocol, and it can be used only by tools running on the server. Answer B is incorrect because the SQL Browser Service being started helps remote connections to named instances. Answer D is incorrect because you cannot exclude IP addresses from connecting to SQL Server using Configuration Manager.
|
|
|
4. Your security administrator has determined that all the SQL Servers in your organization need to listen on nondefault TCP ports, and she doesn’t want network users to see the SQL Servers in a browse list.Which two actions would achieve this goal?
A. Stop and disable the browser service on each SQL Server B. Use Configuration Manager on each server to change each instance to listen on port 50000 C. Use Configuration Manager on each server to change each instance to listen different ports D. Configure the SQL Browser Service to hide each instance E. Enable Show Advanced Options using sp_configure and use the F. Default TCP port option to configure the port for each instance |
Correct Answer & Explanation: A and C. Stopping the browser service will stop all instances on that server being browsable. Configuration Manager
is the only place you can change the TCP port for an instance. Each instance on a server must listen on a unique port. Answers & Incorrect Answers & Explanations: B, D, E. Answer B is incorrect because configuring each instance with the same port on the same server will prevent all but one instance from working.Answer D is incorrect because you cannot hide an instance using the SQL Browser Service.Answer E is incorrect because Configuration Manager is the only place you can change the TCP port for an instance. |
|
|
6. The company you work for has just merged with another company that uses Novell Netware as their network operating system. The new company’s developers are struggling to connect to your SQL Server. What could be the problem?
A. SQL Server 2008 doesn’t support the BanyanVines protocol B. You haven’t enabled the IPX/SPX protocol C. You haven’t enabled the Banyan Vines protocol D. SQL Server 2008 doesn’t support the IPX/SPX protocol |
Correct Answer & Explanation: D. Older versions of Novell NetWare used IPX/SPX as a communication protocol. SQL Server 2008 does not support it.
Incorrect Answers & Explanations: A, B, C.Answer A is incorrect.While the statement is true, Banyan Vines has nothing to do with the scenario. Answer B is incorrect because SQL Server 2008 doesn’t support IPX/SPX.Answer C is incorrect because BanyanVines has nothing to do with the scenario. |
|
|
6. You have just upgraded your databases from a SQL Server 2005 default instance to a SQL Server 2008 named instance called Phobos on a server named MARS. The application that uses the SQL Server sits on its own server, and you’ve just found out that you can’t change the connection string in the application to point to the new instance. How can you fix it?
A. Add a CNAME record on your DNS server for MARS that points to MARS\Phobos B. Install SQL Server Client Tools on the application server and create a SQL Server Alias called MARS that points to MARS\Phobos C. Install SQL Server Client Tools on the application server and create a SQL Server Alias called MARS\Default that points to MARS\Phobos D. Add a PTR record on your DNS server for MARS that points to MARS\ Phobos E. Correct Answer & Explanation: B. Connection requests from the application to MARS will be redirected to MARS\Phobos without you having to change the connection string. |
Incorrect Answers & Explanations: A, C, D. Answer A is incorrect because DNS resolves IP addresses, and you still need to connect to same server (MARS). DNS has no awareness of instances. Answer C is incorrect because you connect to a default instance using the servername, not servername\ Default. Answer D is incorrect because DNS resolves IP addresses, and you still need to connect to same server (MARS). DNS has no awareness of instances.
|
|
|
7. You install a default instance of SQL Server 2008 Developer Edition with default configuration options.The developers complain that they can’t connect to the server using SQL Server Management Studio.Which of these options is the most likely cause?
A. A firewall is blocking port 1433 B. The SQL Browser Service isn’t running C. The SQL Server tools on the developer workstations haven’t been service packed D. TCP/IP is disabled by default in Developer Edition |
Correct Answer & Explanation: D.TCP/IP is disabled by default in Developer Edition, which means that all remote connections will fail.
Incorrect Answers & Explanations: A, B, C. Answer A is incorrect. Although this would cause the same issue, all remote protocols are disabled and that is the most likely cause. Answer B is incorrect because the SQL Server Browser Service isn’t required to connect to a default instance using default port 1433. Answer C is incorrect. Technically, it may be possible for this to cause an issue, but it is not the most likely cause of the problem. |
|
|
8. A SQL Server recently suffered from all CPUs running at 100 percent, which you found to be caused by a missing index. You fixed the problem, but you want to stop any new occurrences from saturating all CPUs. Which of these options would achieve this?
A. Use sp_configure to set max degree of parallelism B. Use Configuration Manager to configure the SQL Server Service to use C. fewer CPUs D. Run the Database Tuning Advisor and apply any recommendations E. Use sp_configure to set cost threshold for parallelism |
Correct Answer & Explanation: A. Setting max degree of parallelism to less than the number of CPUs will prevent SQL Server running parallel executions across all CPUs. Incorrect Answers & Explanations: B, C, D. Answer B is incorrect; you cannot use Configuration Manager to configure SQL Server CPU usage. Answer C is incorrect because DTA recommendations will only reduce the amount of parallelism that occurs. Answer D is incorrect because cost threshold for parallelism controls how expensive queries must be before being considered for parallelism.
|
|
|
9. A developer has contacted you because he wants to enable AWE memory on his Developer Edition instance, but he says that he can’t see the option with sp_configure. What is the most likely cause?
A. AWE is not supported with Developer Edition B. The developer is connecting with a low-privilege user and doesn’t have enough permissions. C. He hasn’t enabled show advanced options with sp_configure. D. He hasn’t enabled /PAE in boot.ini. |
Correct Answer & Explanation: C. AWE is an advanced option and is only visible when show advanced options has been enabled.
Incorrect Answers & Explanations: A, B, D. Answer A is incorrect because AWE is supported with Developer Edition. Answer B is incorrect because low privileges won’t reduce what you see with sp_configure. Answer D is incorrect because /PAE won’t affect whether or not you can see the AWE option. |
|
|
10. You’re running SQL Server 64-bit and want to make sure that SQL Server doesn’t take too much memory.Which of these options would achieve that?
A. Set Maximum Server Memory (MB) using sp_configure B. Assign the SQL Service account the Lock Pages in Memory privilege C. Set Maximum Server Memory (MB) using SQL Server Management Studio D. Start SQL Server with the -g switch passing the maximum memory as a parameter |
Correct Answer and Explanation: A and C. Maximum Server Memory con- trols how much memory SQL Server will use and can be configured using Management Studio or with sp_configure. B is incorrect because this would make a 64-bit SQL Server use AWE to lock memory pages; it wouldn’t pre- vent it from taking too much memory. D is incorrect because the -g switch controls how much reserved memory SQL Server allocates; it won’t prevent it from taking too much.
|
|
|
11. One of your DBAs is setting up SQLMail and has asked you for a license key for Outlook. You suggest that he enable Database Mail instead. Which two of these options are features of Database Mail?
A. Received e-mails are stored in msdb. B. It doesn’t require a MAPI client. C. It is Windows failover-cluster aware. D. It is available in all SQL Server editions. |
Correct Answer & Explanation: B and C. SQLMail requires a MAPI client like Outlook to run. Database Mail uses SMTP and is failover-cluster aware.
Incorrect Answers & Explanations: A, D. Answer A is incorrect because you can’t receive e-mails with Database Mail. Answer D is incorrect because Database Mail is not available in SQL Server Express. |
|
|
12. A business user has just phoned to say that his data wasn’t updated overnight. You check SQL Server and see that a SQL Server Agent job that runs a data load failed last night. You want to be automatically notified via e-mail if it hap- pens again. Which of these options will provide the most efficient, supported solution?
A. Implement SQLMail B. Implement Database Mail C. Install a third-party SMTP mailer D. Implement Service Broker |
Correct Answer & Explanation: B. Database Mail is fast, efficient, reliable, and supported. It meets the criteria in the scenario.
Incorrect Answers & Explanations: A, C, D. Answer A is incorrect because SQLMail is less efficient and reliable than the Database Mail that supersedes it. Answer C is incorrect because a third-party SMTP mailer would be unsupported, and Database Mail is an SMTP mailer. Answer D is incorrect because Service Broker is the messaging engine used by Database Mail. It does not provide e-mail facilities on its own. |
|
|
13. You’ve just upgraded to SQL Server 2008 from SQL Server 2000, and you’ve dropped SQLMail in favor of Database Mail. The IT manager wants to know if Database Mail has redundancy if a mail server goes down. How would
you reply? A. Yes, you can configure multiple SMTP servers in a Database Mail Profile. B. Yes, you can configure SQLMail as a backup. C. No, you would have to reconfigure it to use a different mail server. D. Yes, Database Mail is supported on a failover cluster. |
Correct Answer & Explanation: A. If the first SMTP server isn’t contactable, Database Mail will try the next configured server.
Incorrect Answers & Explanations: B, C, D. Answer B is incorrect because SQLMail and Database Mail don’t work together. Answer C is incorrect because you can specify multiple SMTP servers. Answer D is incorrect because the question refers to a mail server going down, not Database Mail going down. |
|
|
14. You’ve been investigating deadlocks on your server using trace flag 1222, and you’d like to enable it permanently to capture extra information if deadlocks occur again, even if SQL Server gets restarted. What is the easiest way to
do this? A. Execute DBCC TRACEON (1222,-1) B. Create a start-up stored procedure containing DBCC TRACEON C. (1222,-1) and use sp_configure to enable scan for startup procs D. Use Configuration Manager to add –T1222 as a start-up parameter for the E. SQL Server service F. ExecuteDBCCTRACEON(1222) |
Correct Answer & Explanation: C. –T is a start-up switch that enables the specific trace flag for all connections.
Incorrect Answers & Explanations: A, B, D. Answer A is incorrect; although this would enable the trace flag on all connections (-1), it will not persist a ser- vice restart. Answer B is incorrect because it is not the easiest method for achieving the goal. Answer D is incorrect because the trace flag will not persist a service restart and would only enable it on the current connection. |
|
|
15. You’ve been told that you need to make your SQL Server compliant with EAL4+.What is the first step you need to do in this process?
A. Use sp_configure to enable c2 audit mode. B. Use sp_configure to enable common criteria compliance enabled. C. SQL Server 2008 is automatically EAL4+ compliant. D. SQL Server 2008 does not support EAL4+ compliance. |
Correct Answer & Explanation: B. EAL4+ is the most widely accepted level of Common Criteria Compliance.
Incorrect Answers & Explanations: A, C, D. Answer A is incorrect; C2 compliance is not related to EAL4+.Answer C is incorrect because you need to enable Common Criteria Compliance. Answer D is incorrect because SQL Server 2008 supports EAL4+ through Common Criteria Compliance. |
|
|
16. Some of your full-text indexes are consuming a lot of server resources. You investigate and discover that the index causing the problem is on a text-based field that is updated frequently. You’ve spoken to the business, and the updates don’t affect their search results. What could you do to reduce the overall impact on the server while keeping the index fairly up-to-date?
A. Set change tracking on the index to AUTO B. Move the index to a filegroup on its own drive C. Set change tracking on the index to MANUAL and schedule a nightly population update. D. Set change tracking on the index to OFF |
Correct Answer & Explanation: C. This will still track changes but switches off auto propagation to the index. A nightly update will process all the tracked changes.
Incorrect Answers & Explanations: A,B, D. Answer A is incorrect; change tracking is already at the default setting of AUTO, and that is what is causing the problem. Answer B is incorrect because you want to reduce overall impact, and this option will only directly affect disk i/o. Answer D is incorrect because there is no method specified to keep the index updated. |
|
|
17. You have installed a default and two named instances called Phobos and Deimos on a server called Mars. How would you connect to each instance?
A. Mars,Mars\Phobos,Mars\Deimos B. Mars, Mars1\Phobos, Mars2\Deimos C. Default,Phobos,Deimos D. Default,mars\phobos,mars\deimos |
Correct Answer & Explanation: A. The default instance accessed by using the servername and each instance is accessed by adding \<instancename> to the servername. Incorrect Answers & Explanations: B,C, D. Answer B is incorrect; Mars1 and Mars2 refer to different servers. Answer C is incorrect because Default is invalid for the default instance and the server is missing from the instance names. Answer D is incorrect because Default is invalid for the default instance.
|
|
|
18. Your developers complain that they can’t connect to the server using SQL Server Management Studio. You’ve used Terminal Services to log on to the server, and you’ve confirmed that TCP/IP is enabled. You now want to test it with a local connection. What should you do?
A. Nothing, just connect B. Disable Shared Memory in Configuration Manager C. Move TCP/IP above Shared Memory in the connection protocol order D. Disable Shared Memory using sp_configure ‘shared memory enabled’ |
Correct Answer & Explanation: B. Disabling Shared Memory will force SQL Server to use the next protocol to connect which is TCP/IP by default.
Incorrect Answers & Explanations: A,C, D. Answer A is incorrect; local connections will use the Shared Memory protocol by default. Answer C is incorrect because Shared Memory cannot be moved in the connection protocol order, only disabled. Answer D is incorrect because the sp_configure option is invalid. |
|
|
19. You are responsible for a large-capacity server that has a default instance of SQL Server 2008 installed and hosts databases for multiple applications belonging to several of your customers. A new database needs to be moved to the server, which has a different collation, and the collation of TempDB needs to match it. You can’t change the collation for your existing TempDB because it will affect all the applications. What could you do?
A. Create another TempDB with the right collation and configure the new database to use it B. Configure the database to use a different TempDB on a remote server C. Install a new instance for the database with a default collation the same as the database D. A separate server with a separate instance is the only option |
Correct Answer & Explanation: C. You can have only one TempDB database per instance, and it might be feasible to install a second instance on a large capacity server.
Incorrect Answers & Explanations: A, B, D. Answer A is incorrect; you can have only one TempDB per instance. Answer B is incorrect because you cannot configure to use a remote TempDB. Answer D is incorrect because it isn’t the only option. |
|
|
20. You’re implementing a database backup strategy, and you have full-text indexes that took 12 hours to build, so you need to make sure that they are backed up to avoid a long recovery time. What should you do?
A. Nothing extra, they’re backed up with the databases. B. Add WITH FULLTEXT to the database backup command. C. Switch on fulltext backup default enabled with sp_configure. D. Configure the full-text indexes for AUTO. |
Correct Answer & Explanation: A. Indexes are backed up as part of a database backup.
Incorrect Answers & Explanations: B, C, D. Answer B is incorrect because this option does not exist and is not required. Answer C is incorrect because the option does not exist. Answer D is incorrect, because AUTO affects change tracking and propagation, not backups. |
|
|
Chapter 4: Managing Security
|
xx
|
|
|
1. Which of the following is considered a Principal?
A. SQL Server B. SQL Server Integration Services C. SQL Server Login D. SQL Server Agent |
The correct answer is C.A SQL Server Login is considered a principal since it can request SQL Server resources.
Answers A, B, and D are incorrect because they are services and not considered to be principals. |
|
|
2. Which of the following is a securable object in a database schema?
A. Database User B. Database C. Service D. Procedure |
The correct answer is D.A procedure is a securable object within a database schema. Answers A and C are database-level securables. Answer B is a server- level securable.
|
|
|
3. Which of the following fixed server-level roles enables a member to add and remove linked servers?
A. Securityadmin B. Setupadmin C. Serveradmin D. Dbcreator |
The correct answer is B. Although the answers A, C, and D are also fixed server-level roles, they do not have the correct level of permissions to add or remove a linked server.
|
|
|
4. You have a team member who needs the ability to manage SQL Server logins, and you do not want him to have permissions to perform any task on the server. The team member’s responsibilities include resetting logins as well as granting, denying, and revoking database-level and server-level permissions. Which of the following server-level roles best fits the team member’s permissions requirement?
A. Serveradmin B. Sysadmin C. Securityadmin D. Processadmin |
The correct answer is C. The securityadmin role would best fit the team member’s permissions requirement. Answer B would give the user more permissions then he needs. Answers A and D would not provide the appropriate level of permissions needed to perform the required tasks.
|
|
|
5. Which of the following can be added to a server-level role?
A. Windows group B. Database User C. Database-level role D. Domain server account |
The correct answer is A. Windows groups can be added to a server-level role. Answers B, C, and D are incorrect since they cannot be added to
a server-level role. |
|
|
6. Which of the following database-level roles limits a user’s permissions to reading all data from all user tables?
A. db_owner B. db_datareader C. db_datawriter D. db_securityadmin |
The correct answer is B. The role of db_datareader will limit a user to being able to only read all data from all user tables. Answer A would provide the user with permissions greatly exceeding his needs. Answers C and D would not provide the user sufficient permissions to read the data.
|
|
|
7. You have a user who requires the permissions to drop a database and perform any configuration and maintenance tasks. Which of the following actions would violate “The Principle of Least Privilege”?
A. Adding the user to the db_datareader role B. Adding the user to the db_owner role C. Adding the user to the sysadmin role D. Giving the user permission to modify tables |
The correct answer is C. Adding the user to the sysadmin role would provide him with permissions greatly exceeding his needs, which violates “The Principle of Least Privilege.” Answers A and D are incorrect since the permissions for these roles are less then what is required. Answer B is the appropriate role with the best permissions fit. This answer is incorrect since it does not violate the principle.
|
|
|
8. Which of the following can be mapped to a database user?
A. Windows account B. Login C. Windows group D. Domain account |
The correct answer is B.A Login can be mapped to a database User. Answers
A, C, and D are incorrect since they cannot be mapped to a database user. |
|
|
9. Although T-SQL can be used to create database users, which of the following
SQL Server management tools can also be used? A. SQL Server Configuration Manager B. The Surface Area Configuration Fact C. Microsoft Visual Studio D. SQL Server Management Studio |
The correct answer is D. By right-clicking the Users folder in the SQL Server Management Studio and selecting new user from the menu, you can create a user. Answers A, B and C are incorrect since these applications do not provide the functionality to create database users.
|
|
|
10. Which of the following defines mixed mode authentication?
A. Connections can be made using only Windows accounts. B. Connections to SQL Server can be made with either Windows accounts or with SQL Server Logins. C. Connections can be made using only SQL Server Logins. D. Connections can be made using only database users. |
The correct answer is B. When you are using mixed mode authentication, connections to SQL Server can be made either with Windows accounts or SQL Server Logins. Answers A and C are incorrect since mixed mode authentication does not exclusively use Windows accounts or SQL Server logins. Answer D is incorrect since it is not a mode of authentication.
|
|
|
11. Which of the following is an advantage to using mixed mode authentication? A. Users can connect from unknown or untrusted domains.
B. The Kerberos security protocol. C. Windows password policies. D. Windows accounts are maintained outside of SQL Server. |
The correct answer is A. With using mixed mode authentication and SQL Server Logins, users can connect from unknown or untrusted domains. Answers B, C, and D are all advantages to using the Windows Authentication mode exclusively.
|
|
|
12. Which SQL Server tool is changed in the Authentication Mode? A. SQL Server Configuration Manager
B. Visual Studio C. SQL Server Management Studio D. The Surface Area Configuration Facet |
The correct answer is C. The Authentication Mode is changed by right- clicking on the server instance and changing the mode in the security section. Answers A, B, and D are incorrect since these tools do not provide access to change this configuration.
|
|
|
13. When you change the Authentication Mode, what action do you need to perform before the mode change takes effect?
A. Reboot the server operating system. B. Lock out all the database users. C. Restart SQL Server Agent. D. Restart the SQL Server Instance. |
The correct answer is D. You need to restart the SQL Server instance in order for an authentication mode change to take effect. Answer A is incorrect since, although rebooting the server operating system will shut down SQL Server and start it when the operating system cycles back up, it is not necessary to do this. Answers B and C are incorrect since taking either of these actions will not cause the authentication mode to change.
|
|
|
14. Jose has ownership of database tables in database A and database B, both of which reside in the same instance of SQL Server where cross database ownership chaining has been enabled. Jose uses a database view in database A to join both tables in order to retrieve the needed data. Jose grants Jack permission to use her database view. What other permissions need to be granted in order for Jack to retrieve data using Jose’s view?
A. Jack needs to be granted read permissions to the table in database B that is used by the view. B. Since cross database ownership chaining is enabled in this instance, no additional permissions need to be granted. C. The tables used by the view in database A and B need read permissions granted to Jack. D. Jack cannot use Jose’s view since she is the owner. Jack will need his own view and permissions. |
The correct answer is B. With cross database ownership chaining, credentials are only checked on the first object request. Since Jack was granted permission by the object’s owner, Jose’s credentials will be used to access the tables in database B. Answer A is incorrect since Jack was granted permission to the view by the object owner and cross database ownership chaining was enabled. Jack’s credentials would not be checked for accessing the table in database B. In addition, there is no need to permission the database B table to Jack.
Answer C is incorrect since with cross database ownership chaining it is not necessary for the object’s owner to grant permission on the view in database A to Jack. Answer D is incorrect since Jose granted the view to Jack. The view uses Jose’s credentials when it is run. |
|
|
15. Which of the following is an Execution Context?
A. Execute sp_srvrolepermission B. Execute sp_dbfixedrolepermission C. Execute sp_addrole D. Execute AS user_name |
The correct answer is D. Execute AS user_name is an Execution Context that can be used to execute an object such as a stored procedure under the permission of a particular user_name. Answers A, B, and C are not Execution Contexts.
|
|
|
16. Which of the following is captured when using CDC?
A. SELECT B. ALTER C. EXECUTE D. UPDATE |
The correct answer is D. UPDATE statements are captured when using CDC. Answers A, B, and C are incorrect since these activities are not captured when using CDC.
|
|
|
17. You want to prevent changes to tables in one of the databases in your SQL Server instance since changes to any of the tables can cause the associated client application to stop functioning. What can be implemented to prevent any tables from being changed?
A. A stored procedure B. A database-level DDL trigger C. A DML trigger D. A server-level DDL trigger |
The correct answer is B.A database-level DDL trigger can be used to prevent any modifications to tables. Answer A is incorrect since stored procedures
can only make DDL changes, not prevent them. Answer C is incorrect since DML triggers can only be used to fire on data INSERTS, UPDATES, and DELETES. Answer D is incorrect since monitoring changes to tables is outside the scope of a server-level DDL trigger. |
|
|
18. Which tool is used to enable SQL Server features not automatically enabled, such as Database Mail, when SQL Server is installed.
A. SQL Server Configuration Manager B. Visual Studio C. Surface Area Configuration Facet D. SQL Server Installation Center |
The correct answer is C. In the SQL Server Management Studio at the instance level, the Surface Area Configuration Facet can be accessed and features such as DatabaseMail and CLR integration can be enabled. Answers A, B, and D are incorrect since the Surface Area Configuration Facet cannot be accessed through these tools.
|
|
|
19. What is used to apply appropriate subsystem permissions to SQL Server Agent tasks?
A. Database-level roles B. Proxies C. Server-level roles D. Credentials |
The correct answer is B. Proxies can be set up to use with SQL Server to establish the appropriate permissions for the various tasks performed by SQL Server Agent job steps. Answers A and C are incorrect since permissions for SQL Server Agent tasks are not defined through either database-level or server-level roles. Answer D is incorrect since proxies are used by the SQL Server Agent. Credentials are necessary to establish the proxy but are not used directly.
|
|
|
20. Which SQL Server tool is use to manage SQL Server services such as the SQL Server Agent and SQL Server Integration services?
A. Surface Area Configuration Manager Facet B. SQL Server Management Studio C. Visual Studio D. SQL Server Configuration Manager |
The correct answer is D. SQL Server services should always be managed through the SQL Server Configuration Manager. Answers A, B, and C are incorrect since these services cannot be accessed through these tools.
|
|
|
Chapter 5: Managing Data Encryption
|
xx
|
|
|
1. What level of protection does transparent data encryption (TDE) provide?
A. Cell-level B. File-level C. Database-level D. Drive-level |
The correct answer is C. Answers A, B, and D are incorrect because TDE is designed to provide database-level specifically. Cell-level data encryption is a different method of data encryption provided by SQL Server 2008 and file-level (or EFS) and drive-level (bitLocker) are Windows features.
|
|
|
2. Which of the following best describes transparent data encryption (TDE)?
A. Data in specific columns is encrypted. B. Encrypts everything, such as indexes, stored procedures, functions, keys, etc, without sacrificing security or leaking information on the disk. C. Once data types have been changed to varbinary, everything will be encrypted. D. Once database schemas have been changed to accommodate data encryption, everything will be encrypted. |
The correct answer is B. Answer A refers to cell-level/column-level data encryption. Answers C and D are incorrect since database changes are not necessary in order to use TDE.
|
|
|
3. Which edition of SQL Server 2008 offers transparent data encryption (TDE)? A. SQL Server 2008 Express
B. SQL Server 2008 Standard C. SQL Server 2998 Web D. SQL Server 2008 Enterprise |
The correct answer is D. Answers A, B, and C are incorrect since TDE is only available in the Enterprise edition of SQL Server 2008.
|
|
|
4. Which SQL Server permissions are required to enable transparent data encryption (TDE)?
A. Permissions associated with creating the user database. B. Permissions associated with making server-level changes. C. Permissions associated with creating a database master key and certificate in the master database and control permissions on the user database. D. Permissions associated with the operating system administrator’s account. |
The correct answer is C. Answers A, B and D are incorrect since they do not supply the appropriate level of permissions needed in order to enable TDE.
|
|
|
5. The first step to enabling TDE on a database is to create a database master key (DMK).Which of the following is the correct syntax for creating a DMK?
A. CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘somepassword’; B. ALTER DATABASE tdedatabase SET ENCRYPTION ON; C. CREATE CERTIFICATE tdeCert WITH SUBJECT = ‘tdeCertificate’; D. CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE tdeCert; |
The correct answer is A. Answer B is incorrect because this is the syntax to start the encryption scan. Answer C is incorrect because this is the syntax used to create a certificate. Answer D is incorrect because this is the syntax used to create a database encryption key.
|
|
|
6. It is very important to back up the Certificate with the private key once it has been created and to copy it to a secure location other than on the server that it was created on. Which of the following is the correct syntax to back up a Certificate?
A. CREATE CERTIFICATE tdeCert WITH SUBJECT = ‘tdeCertificate’; B. BACKUP CERTIFICATE tdeCert TO FILE = ‘path_to_file’WITH PRIVATE KEY ( FILE = ‘path_to_private_key_file’, ENCRYPTION BY PASSWORD = ‘cert password’); C. CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE tdeCert; D. ALTER DATABASE tdedatabase SET ENCRYPTION ON; |
The correct answer is B. Answer A is incorrect because this syntax is used to create a certificate initially. Answer C is incorrect because this syntax is used to create a database encryption key. Answer D is incorrect because this syntax is used to start the encryption scan.
|
|
|
7. Which sys catalog view is used to monitor the progress of an encryption scan?
A. Sys.configurations B. Sys.certificates C. Sys.key_encryptions D. Sys.dm_database_encryption_keys |
The correct answer is D. Answers A, B, and C are incorrect because they will not display the progress of an encryption scan.
|
|
|
8. When transparent data encryption (TDE) is enabled, which of the following system catalog views will the database show as encrypted?
A. Sys.databases B. Sys.all_objects C. Sys.all_columns D. Sys.configurations |
The correct answer is A. Answers B, C, and D are incorrect because the “is_encrypted” column is not available in these system views.
|
|
|
9. When transparent data encryption (TDE) is enabled, database backups are encrypted. If you cannot locate the associated Certificate and private key files, what happens to the data if you need to restore the encrypted database?
A. Everything will be readable since the Certificate and private key can be recreated. B. The data will not be readable since the Certificate and private key files are lost. C. Only encrypted columns will not be readable. D. Everything will be readable once the database encryption has been turned off in the master database. |
The correct answer is B. Answers A, C, and D are incorrect since the original Certificate and private key files are required to decrypt the database. It is very important to store these files in a secure location other than with the database backup to mitigate the possibility of not being able to decrypt a database.
|
|
|
10. In which edition of SQL Server 2008 is cell-level encryption available? A. Only in the Enterprise edition
B. Only in the Web edition C. Only in the Workgroup edition D. All SQL Server 2008 editions |
The correct answer is D. Answers A, B, and C are incorrect because cell-level encryption is not limited to only one edition of SQL Server 2008.
|
|
|
11. When using cell-level encryption which data type is required in order to encrypt the data?
A. VARCHAR B. No special data type is necessary C. BINARY D. VARBINARY |
The correct answer is D. Using the VARBINARY is required when using cell-level encryption, making answers A, B, and C incorrect.
|
|
|
12. What is the query performance impact when using cell-level encryption?
A. There is a positive query performance impact since there are only specific columns that are encrypted. B. There is a negative query performance impact since data types have to be converted from varbinary to the correct data type and primary keys and indexes are not used, resulting in full table scans. C. There is a negative query performance impact unless TDE has been enabled. D. There is a positive query performance impact as long as TDE has been enabled. |
The correct answer is B. Answers A, C, and D are incorrect since these con- figurations would not have an impact on query performance.
|
|
|
13. Cell-level encryption is best used in which of the following?
A. Performance sensitive situations B. All situations C. Limited access control through the use of passwords situations D. Situations when a password is not needed to access the encrypted data |
The correct answer is C.Answer A is incorrect and is a consideration that would result in the use of TDE. Answer B is incorrect because cell-level encryption is not recommended in all situations because of the performance impact. Answer D is incorrect because passwords are required when using cell-level encryption.
|
|
|
14. Which of the following best describes the difference between symmetric and asymmetric keys?
A. An asymmetric key uses the same password to encrypt and decrypt the data and a symmetric key uses a public key to encrypt the data and a private key to decrypt the data. B. An asymmetric key uses the same password to encrypt and decrypt the data and a symmetric key uses a private key to encrypt and a public key to decrypt the data. C. A symmetric key uses the same password to encrypt and decrypt the data and an asymmetric key uses a public key to encrypt and a different password/private key to decrypt the data. D. A symmetric key uses the same password to encrypt and decrypt the data and an asymmetric key uses a private key to encrypt and a different password/public key to decrypt the data. |
The correct answer is C. Answers A, B, and D are incorrect because the combinations of passwords used for encrypting and decrypting do not accurately describe symmetric and asymmetric keys.
|
|
|
15. Which of the following best describes EFS encryption?
A. Encryption occurs at the file-level. B. Encryption occurs at the database-level. C. Encryption occurs at the cell-level. D. Encryption occurs at the server-level. |
The correct answer is A. Answers B, C, and D are incorrect because Encrypting File System (EFS) – encryption only occurs at the file-level.
|
|
|
16. When you are using Encrypting File Service (EFS) with SQL Server, which SQL server account must have access to file encryption keys encrypting any database files?
A. SQL Server agent account B. Database server, service account C. SA account D. SQL executive account |
The correct answer is B. Answers A, C, and D are incorrect since these accounts are not used to access database files.
|
|
|
17. In which of the following is EFS best used?
A. Web server B. Database server C. Workstation D. Application server |
The correct answer is C. EFS is best used when the database is primarily used by a small set of users so answers A, B, and D are incorrect.
|
|
|
18. Which of the following best describes SQL Server 2008 Extensible Key Management?
A. Enables third-party EKM/HSM vendors to register their modules in SQL Server B. Enables easy management of Encrypting File System (EFS) encryption C. Enables easy creation of an EKM key with another EKM key D. Enables easy back up of a database encryption key |
The correct answer is A. Answer B is incorrect because SQL Server 2008 extensible key management does not provide the functionality to manage EFS encryption. Answer C is incorrect because an EKM key cannot be created with another EKM key so this cannot be done using EKM. Answer D is incorrect because EKM does not provide the functionality to back up a database encryption key; this is done using T-SQL.
|
|
|
19. In which of the following editions of SQL Server 2008 is Extensible Key Management available?
A. Workgroup edition B. Web edition C. Enterprise edition D. Express edition |
The correct answer is C. SQL Server 2008 Extensible Key Management is available in the Enterprise, Developer, and Evaluation editions so Answers A, B, and D are incorrect.
|
|
|
20. Which system stored procedure is used to enable Extensible Key Management? A. Sys.dboption
B. Sys.sp_configure C. Sys.sp_helpdb D. Sys.sp_addextendedproc |
The correct answer is B. Only the sys.sp_configure stored procedure provides the ability to enable Extensible Key Management so answers A, C, and D are incorrect. These stored procedures do not provide the necessary functionality.
|
|
|
Chapter 6: Managing High Availability
|
xx
|
|
|
1. You are designing a new database server for your company. You wish to store the database’s MDF file and NDF files on the most cost efficient storage possible, while providing a redundant storage solution. Your database will be 95% reads, and 5% writes. What RAID level should you use for your MDF and NDF files?
A. RAID 0 B. RAID 1 C. RAID 5 D. RAID 10 |
Answer C is the correct answer. Although RAID 0 is more cost effective than RAID 5, RAID 0 has no redundancy built into it. RAID 1 and RAID 10 are less cost effective then RAID 5 because of the one-to-one copying that is done, which reduces the number of hard drives available in the RAID array by half.
|
|
|
2. You are configuring log shipping on your OLTP database from one data center to another. You need to ensure that the minimum amount of bandwidth is used when moving the logs from your primary site to the backup site. When config- uring the log backups, which option should you select to meet your goal?
A. Use the default server setting B. Compress backup C. Do not compress backup |
Answer B is the correct answer. If you are dealing with a slow WAN link you do not want to run the risk that someone disables the global compression settings and slows down the WAN by turning off the compression.
|
|
|
3. You are setting up log shipping for a database between two instances of SQL Server 2008.You want to ensure that your users have access to the database when the logs are not actively being restored so that they can read the updated data for reporting purposes. In what state should you leave the database?
A. RECOVERY B. NO RECOVERY C. STANDBY |
Answer C is the correct answer. If you leave the database in the STANDBY state the users can connect to the database, which is left in a read-only state, and query it. If the database is in a RECOVERY state then you will not be able to restore any more logs to it. If the database is in a NO RECOVERY state then the users will not be able to access it at all. If you do this, be sure to tell the log shipping to disconnect the users when restoring the backups.
|
|
|
4. Which of the following database mirroring modes requires the use of a witness instance?
A. High Performance with automatic failover B. High Performance C. High Safety D. High Safety with automatic failover |
The correct answer is D. Automatic failover is available only with High Safety, and only when a witness server is configured. High Safety can be used without a witness server if automatic failover is not required but the loss of any transactions is unacceptable.
|
|
|
5. You are setting up your deployment scripts to create your database mirroring endpoints on your production SQL Server. Each server has a single instance on it. Which script will correctly create the database mirroring endpoint?
A. CREATE ENDPOINT MyEndpoint STATE = STARTED AS HTTP (PATH=/sql/MyEndpoint, AUTHENTICATION=BASIC) FOR DATABASE_MIRRORING; B. CREATE ENDPOINT MyEndpoint STATE = STARTED AS TCP (LISTENER_PORT=5022) FOR DATABASE_MIRRORING; C. CREATE DATABASE_MIRRORING ENDPOINT MyEndpoint STATE=STARTED AS TCP (LISTENER PORT=5022); D. CREATE ENDPOINT MyEndpoint STATE = STARTED AS TCP (LISTENER PORT=1433) FOR DATABASE_MIRRORING; |
Answer B is the correct answer. Database mirroring endpoints must use unique TCP port numbers. Answer C uses an invalid command, because the DATABASE_MIRRORING switch goes after the protocol section. Answer D is incorrect because port 1433 will be used by the SQL Server engine.
|
|
|
6. You are designing your high-availability solution. Because of the importance of the database system you are required to have more than one independent backup server running at all times. Which high-availability solution fits these requirements?
A. Database mirroring B. Log shipping C. Clustering D. Replication |
Answers B and D are correct. Only log shipping and replication allow for multiple independent backup servers. Database Mirroring can have only a single backup server. Clustering does not allow for any independent backup servers because of the shared storage requirement.
|
|
|
7. You have installed SQL Server 2008 in a clustered environment. You are installing SSIS on the SQL Server. You have installed SQL Server 2008 as a named instance and need to tell SSIS that it needs to connect to the named instance. What file should you edit?
A. C:\Program Files\Microsoft SQL Server\100\DTS\Binn\msdtssrvr.exe. config B. C:\Program Files\Microsoft SQL Server\100\DTS\Binn\MsDtsConfig. ini.xml C. C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe. config D. C:\Program Files\Microsoft SQL Server\100\DTS\Binn\MsDtsConfig. ini.config |
The correct answer is B. This is the config file, which contains the settings that the Integration Services service uses to control to which instances it connects. The msdtssrvr.exe.config file contains the settings that control what tracing data is being collected. The DTExec.exe.config file holds some basic settings that are used when running SSIS packages. The MsDtsConfig.ini. config file does not exist.
|
|
|
8. You are selecting your high-availability solution for your production environ- ment. You have a single data center that houses all your servers. Which high- availability solution requires administrative overhead after the initial setup is complete?
A. Database mirroring B. Replication C. Clustering D. Log shipping |
The correct answer is C. With clustering, when you install SQL Server service packs and hot fixes the patch will be installed on all the nodes of the cluster. With the other three solutions the primary machine and backup machines need to be patched separately.
|
|
|
9. You are considering the licensing costs for your new high-availability solution. You will be using the Standard edition of Microsoft SQL Server. Which solution has the lowest licensing costs?
A. Database mirroring B. Replication C. Clustering D. Log shipping |
Answer C is correct. SQL Server clustering has the lowest licensing costs because you need to license only the active node. With the other three options the backup machines must also be licensed.
|
|
|
10. You need to upgrade the CPUs in your SQL Server. You want to add new CPUs without taking the system offline. Your hardware supports this feature. What operating system or systems support this feature?
A. Web Edition B. Standard Edition C. Enterprise Edition D. Datacenter Edition |
The correct answer is D. Only the Datacenter Edition of Windows supports adding CPUs without taking the system offline.
|
|
|
11. You need to upgrade the RAM in your SQL Server. You want to add more RAM without taking the system offline. Your hardware supports this feature. What operating system or systems support this feature?
A. Web Edition B. Standard Edition C. Enterprise Edition D. Datacenter Edition |
Answers C and D are correct. Both the Enterprise and Datacenter edition support adding RAM without taking the system offline.
|
|
|
12. When you are setting up your clients to connect to a clustered SQL Server database, to which hostname should they be connecting?
A. The hostname of the node that is actively running the SQL Service B. The hostname of the node that is not actively running the SQL Service C. The hostname that was used when setting up the Windows Cluster D. The hostname that was used when installing SQL Server |
The correct answer is D. You will want to have your clients connect to the hostname that was used when installing SQL Server. Although you can connect to the hostname that is running the SQL Service, there is no way to know which host this is. Although you can connect via the hostname that was set up when configuring the Windows cluster it will work only if both the Windows Cluster and the SQL Instance are currently on the same node. As they are in different resource groups it is entirely possible that they will be on different nodes.
|
|
|
13. You are using merge replication to make your user authentication database highly available. The database has a single table that is shown in Example A. The replication has been running for several months without issue. Your replication has begun failing, giving a unique index violation. What should you do to correct the problem? Your business processes require that each username remain unique within the system, and that users use their e-mail addresses for their usernames.
Example A: Merge Replication CREATE TABLE Authentication (UserId INT PRIMARY KEY IDENTITY(1,1), UserName NVARCHAR(255), Password NVARCHAR(100), Active BIT) GO CREATE UNIQUE INDEX IX_Authentication_UserName ON dbo.Authentication (UserName) INCLUDE (Password, UserId, Active) WITH (FILLFACTOR=60) GO A. Mark the subscriber for reinitialization, and run the snapshot agent. B. Identify the rows that are causing the violation and delete both rows from the servers. C. Identify the rows that are causing the violation, then identify which UserId is being used by the customer within the rest of your business processes, then modify the UserName value of the invalid row to a different value. D. Delete the subscriber and re-create the subscription. |
The correct answer is C. This solution will cause the least amount of downtime for the environment, and the least inconvenience to your customers. By using either solution A or D any new records that were created on the subscriber after the offending row was entered would be lost. Using solution B would cause the user to lose access to the system entirely. Solution C would allow the user to continue to use the system without causing other users to lose their data.
|
|
|
14. You are administrating a SQL Server cluster that is running three instances of Microsoft SQL Sever 2008. Each instance uses SQL Service Broker to send messages to other instances both on the cluster and on other machines in the Enterprise. After patching and rebooting the host machines you see that one of the instances is not getting its service broker messages. One of the instances is using TCP port 1433, and the other two are using dynamic port numbers. The server that is not receiving SQL Service broker messages is using a dynamic TCP port number. You examine the SQL Server ERRORLOG and find the following relevant messages:
2008–11–05 23:14:23.64 Server Server is listening on [ ‘any’ <ipv6> 49237]. 2008–11–05 23:14:23.64 Server Server is listening on [ ‘any’ <ipv4> 49237]. 2008–11–05 23:14:23.64 Server Server is listening on [ ::1 <ipv6> 49551]. 2008–11–05 23:14:23.65 Server Server is listening on [ 127.0.0.1 <ipv4> 49551]. You know that the SQL Service Broker Endpoint is configured on port 5200. How should you resolve this issue with the least impact to the users of the databases hosted by this instance? A. Move one or more instances to the other node, then restart this instance so that the endpoint can take the TCP port number it has been assigned. B. Change the port number of the ENDPOINT to a free TCP port number that is not configured for use by the other instances by using the ALTER ENDPOINT command, changing the state from stopped to started at the same time. Reconfigure the routes on the machines that send SQL Service Broker messages to this server to use the new TCP port number. C. Restart the problem instance hoping that it gets access to its port number. D. Use the Cluster Administrator to move the problem instance to its own node so that the service can gain access to the port number in question. |
The correct answer is B. Even though all four of these solutions will resolve the problem (answer C will solve the problem only if the application that has taken the TCP port number is not running when it restarts), the answer that will resolve the issue with the least impact to the users is option B. Although it is the answer that requires the most administrative overhead, it will impact the users the least, which is a requirement of the question.
|
|
|
15. You have installed SQL Server 2008 in an Active Passive environment running on the servers SQL01A and SQL01B.You have several SSIS packages within your database environment. You have installed SQL Server as the default instance using the default configuration on your cluster using the name SQL01V01 as the clustered resource name. When your SQL Server’s resource group is running on SQL01B the SSIS packages work without error. When your SQL Server’s resource group is running on SQL01A the SSIS packages fail to start. All the SSIS packages are run from SQL Agent Jobs. How should you correct this problem?
A. Fail the SQL Server back to SQL01B and always run the SQL Server on that node. B. Reconfigure the SQL Agent Jobs that run the SSIS jobs to use SQL01B as the SSIS repository instead of SQL01V01. C. Install the SQL Server Integration Services service on the SQL01A and apply the same patches and hot fixes that are in use by the SQL Server service. D. Configure another SQL Server to be the SSIS repository instead of SQL01V01. |
The correct answer is C. Only the SQL Server and Analysis Services are cluster aware services. This means that only these services are installed automatically on both nodes of the cluster. SSIS will be installed on only one node of the cluster by default and needs to be installed and patched manually on the second node of the cluster.
|
|
|
16. You are installing a new SQL Server that will be used to store the Web logs from your IIS Web farm. The database will be 99% write, and 1% read. Data will be read from the system only in the event of a system problem or legal investigation. You need to store the data on the most efficient solution possible, while providing redundancy, so that writes to the database are not slowed down. What RAID level should you use?
A. RAID 0 B. RAID 5 C. RAID 6 D. RAID 10 |
Answer D is correct. In this case you will want to choose RAID 10 for your RAID array. RAID 10 will give you the fastest write performance of all the available RAID levels because it does not perform a parity calculation like the RAID 5 and RAID 6 volumes do. RAID 0 is not a good solution because there is no redundancy in a RAID 0 array, and redundancy is a requirement of our design.
|
|
|
17. You are administrating a clustered SQL Server solution in a shared storage environment. You are seeing disk performance problems weekly on Saturday nights. YouVPN in on Saturday night in order to attempt to troubleshoot the problem. You see nothing out of the ordinary on the SQL Server except that the SQL Server is reporting that the hard drives are not responding quickly enough. You open performance monitor and see that the disk queues are less than five, and you are averaging 80 operations per second. When you look at the Avg Disk Sec/Read you see you are averaging 0.8 seconds with a max of 7 seconds. What is the most likely cause of this kind of performance problem?
A. The server is not sending the commands to the storage correctly to do weekly maintenance, and because the commands have to be sent over and over they are taking too long to be completed. B. Part of the SAN is powered down on weekends to save power as part of the company’s Green policies. C. Another server using the same spindles is performing some very heavy workload against the disks every week—something like a tape backup or a defrag. D. Your SAN is under-licensed and performs periodic workload slowdowns in order to bring this to the attention of the SAN administrator so that they will correct the SAN licensing. |
Answer C is the most feasible of the solutions. If you have a large file server that normally is not very busy sharing the same spindles as your SQL Server you will not see this performance problem during the week. However when the full backup of that server is taking place or when the disk is being defragged, both of which are very disk-intensive operations, you may see a performance slow down on the SQL Server as a result. Check with your SAN vendor and see if they have Quality of Service (QOS) modules that can be loaded on the SAN in order to ensure that the SQL Server has the required resources available.
Answer A is possible, but it would show itself always, and wouldn’t be caused by server maintenance. More than likely a slowdown that is causing commands to be stored more than once will be caused by a faulty fiber cable or host bus adapter (HBA). Answers B and D should pretty much never happen in a production environment. Although SANs are expensive to power, the loss of productivity usually offsets any benefit gained by powering down the part of the SAN. No SAN solution will ever slow itself down in order to get someone’s attention. If there is a problem with the SAN it will use its administrative contacts to send an email to the SAN administrator, or in some cases directly to the SAN vendor who will then schedule a service call. |
|
|
18. You are troubleshooting distributed transaction problems on your clustered SQL Server. Your SQL Server is on your internal network, and there are several servers in the DMZ that use MSDTC to use distributed transactions with the SQL Server. These machines are receiving sporadic messages about not being able to connect to the MSDTC service on the SQL Server cluster. You manually begin a distributed transaction and query a remote system and it works correctly most of the time, sometimes failing with a message saying that the local MSDTC service cannot be contacted. What action should you perform to correct this problem?
A. Restart the MSDTC service. B. Move the MSDTC service to the same node on which the SQL Service is running. C. Configure RPC to use more TCP ports. D. Reinstall MSDTC from the Programs and Features Control Panel Applet. |
The correct answer is C. You have configured RPC to use to few ports and you need to increase the number of ports that can be used. This will need to be done on both the SQL Server cluster as well as the client machines. All machines will need to be rebooted after this change is made. Reinstalling, moving, or restarting the MSDTC service will not permanently fix the problem. It may stop the problem in the short term, but since all the services are restarted and reconnect to the database the problem will return.
|
|
|
19. You are installing SQL Server 2008 on a new Windows 2008 cluster. You get to the disk configuration screen and are unable to configure the tempdb database to be placed in the C:\MSSQL\MSSQL\Data folder as per your IT policy. How should you resolve this issue?
A. Add the C drive as a clustered resource using the Windows cluster admin- istrator. Rerun the SQL Server 2008 installed, selecting the C drive as a disk resource. B. Install SQL Server with the TEMPDB database on a clustered drive. After installing SQL Server 2008, change the location of the TEMPDB database to the C drive and restart the SQL Server. C. Add a new drive to the cluster as a clustered resource. Use this drive to host the TEMPDB database. |
Answer C is the correct answer. You cannot host any SQL Server databases on nonclustered drives, including the tempdb database. This is because Windows cannot guarantee that the nonclustered drive will be available on all nodes of the cluster. Answer A will not work because you cannot assign drives that are not shared drives as clustered resources. If you use Answer B, the SQL Server will not allow you to move any databases to drives on which the SQL Server resource is not dependent.
|
|
|
20. You are designing your high-availability solution. You have decided to use SQL Server replication in order to move data from server to server. You need a bidirectional replication solution so that you can use either server as your active server. What replication topology should you use?
A. Snapshot B. Merge C. Transactional |
Answer B is the correct answer, because Merge replication easily supports bidirectional replication. Snapshot and Transactional replication are most typically unidirectional replication.
|
|
|
Chapter 7: Maintaining Your Database
|
xx
|
|
|
1. You have a large centralized line of business database used by hundreds of users all around the world. One of the key tables is the Orders table. This table is frequently updated and frequently searched. A nonclustered index named IX_Orders_CustomerID exists on the Orders table, CustomerID column. Recently your company has experienced record sales, and many new customers have been added.You have received several user reports of degraded performance when trying to search for orders by a specific customer. After running some diagnostics, you are convinced that the reason for the performance degradation is index fragmentation. You wish to resolve the problem for your users, but, due to the 24-hour nature of your business, you are not allowed to take the index offline. Additionally,you wish to be able to interrupt reindexing operations without losing progress, in case the maintenance operation affects performance severely while it is running. What is the best way to resolve the problem while meeting your objectives?
A. Run ALTER INDEX _Orders_CustomerID ON Orders.CustomerID REBUILD B. Run ALTER INDEX IX_Orders_CustomerID ON Orders.CustomerID REORGANIZE C. Run CREATE INDEX IX_Orders_CustomerID ON Orders. CustomerID WITH DROP EXISTING D. RunDBCCINDEXDEFRAG(LOBDatabase,“Orders”,IX_Orders_ CustomerID) |
Correct Answer & Explanation: B. Specifying the REORGANIZE option with the ALTER INDEX statement slowly rearranges the index in the back- ground without taking it offline. It also allows you to cancel the operation without losing the index and without losing any of the defragmentation that has already taken place.
Incorrect Answers & Explanations: A, C, and D. Answer A is incorrect because specifying the REBUILD option with the ALTER INDEX statement will force the index to be taken offline. Answer C is incorrect because dropping and re-creating the index will make the index unavailable to users until the CREATE statement has completed, which may take a long time. Answer D is incorrect because DBCC INDEXDEFRAG is for backwards compatibility only and should not be used in SQL Server 2008. |
|
|
2. You have recently removed a lot of historical data from your SQL Server 2008 database named HR by archiving it to a different server. However, you notice that the disk space consumed by the database file remains unchanged and the . MDF file is taking up the majority of space on the drive. How can you decrease the size of the database file?
A. Run DBCC CHECKDB (‘HR’, REPAIR_ALLOW_DATA_LOSS) B. Run DBCC SHRINKFILE (‘HR_Data’, TRUNCATEONLY) C. Run DBCC SHRINKFILE (‘HR_Data’, NOTRUNCATE) D. Use the Copy Database Wizard to copy the database, then delete the original database. Finally, rename the new database you have just copied to the same name as the original database. |
Correct Answer & Explanation: B. Running the DBCC SHRINKFILE command with the TRUNCATEONLY option will rearrange the file to put empty space at the end, then truncate the file to shrink it in size. Incorrect Answers & Explanations: A, C, and D. Answer A is incorrect because repairing the database will not shrink the file size nor rearrange the pages in the file to move all empty space to the end. Answer C is incorrect because running the DBCC SHRINKFILE command with the NOTRUNCATE option will rearrange the file to put empty space at the end but will not truncate the file to shrink it in size. The actual file size will remain unchanged. Answer D is incorrect because running the Copy Database Wizard will not shrink the file size.
|
|
|
3. You have a very large table named PhoneSurvey. The table contains a Question column and an Answer column, both of type char(1000). Many questions are similar or the same. Many answers are very similar. Many questions re-phrase the answers. Additionally, many values in the answer column are null. The table resides on a high-performance system, and you are not concerned about query performance. What is the best way to reduce the size of this table as much as possible (choose all that apply)?
A. ALTER TABLE PhoneSurvey REBUILD WITH DATA_ COMPRESSION = ROW B. ALTER TABLE PhoneSurvey ALTER COLUMN Answer ADD SPARSE C. ALTER TABLE PhoneSurvey ALTER COLUMN Question ADD SPARSE D. ALTERTABLEPhoneSurveyREBUILDWITHDATA_ COMPRESSION = PAGE |
Correct Answers & Explanations: B and D. You should enable page-level compression on the PhoneSurvey table as this will compress data at row level. Next, apply dictionary compression, which will yield the best compression results. You should also make the Answer column a sparse column to optimize null storage because this column contains many null values. Incorrect Answers & Explanations: A and C. Answer A is incorrect because row level compression will provide less space saving than page compression in this case. Answer D is incorrect because the Question column does not contain a large quantity of null values unlike the answer column.
|
|
|
4. Your organization is worried about the security risks of Database Mail and SQL Mail. You are asked to verify that all servers in your organization have these features disabled. If not, you must disable the feature on those servers that don’t have it enabled as soon as possible. What is the best way to accomplish this task with minimum administrative effort?
A. Use Policy-Based Management to create a policy condition for the Server Configuration facet, SQLMailEnabled = False OR DatabaseMailEnabled = False. Create a policy linking this condition with a target set. Evaluate the policy to identify servers that don’t comply with this policy.After evaluating the policy, apply the policy to reconfigure any noncompliant servers. B. Use Policy-Based Management to create a policy condition for the Server Configuration facet, SQLMailEnabled = False AND DatabaseMailEnabled = False. Create a policy linking this condition with a target set. Evaluate the policy to identify servers that don’t comply with this policy.After evaluating the policy, apply the policy to reconfigure any noncompliant servers. C. Use Policy-Based Management to create a policy with no conditions. Choose to apply this policy to a target set defined by the filter SQLMailEnabled = True OR DatabaseMailEnabled = True. Evaluate the policy to identify servers that don’t comply with this policy.After evaluating the policy, manually change the SQL Mail and Database Mail settings to reconfigure any noncompliant servers. D. Use Policy-Based Management to create a policy condition for the Server Configuration facet, SQLMailEnabled = False AND DatabaseMailEnabled = False. Create a policy linking this condition with a target set. Evaluate the policy to identify servers that don’t comply with this policy.After evaluating the policy, manually change the SQL Mail and Database Mail settings to reconfigure any noncompliant servers. |
Correct Answer & Explanation: B. Creating a policy with SQLMailEnabled = False AND DatabaseMailEnabled = False condition will find all servers
that have SQL Mail or Database Mail features enabled and show them as noncompliant. You can then easily apply the policy to reconfigure them with one administrative action. Incorrect Answers & Explanations: A, C, and D. Answer A is incorrect because setting the policy condition to SQLMailEnabled = False OR DatabaseMailEnabled = False will report all servers that have SQL Mail enabled but Database Mail disabled as compliant. Answer C is incorrect because you cannot create a policy with no conditions. Answer D is incorrect because reconfiguring server settings manually will take more administrative effort than simply applying the policy. |
|
|
5. You have created a multistep job to prepare data for an important weekly production report. The job consists of several steps: getting data from multiple servers, consolidating it into a single table, and producing a report. Your users have complained that several times no report was created because certain servers were unavailable. They tell you that they would rather see a report based on last week’s data than not see a report at all. What should you do to satisfy your users?
A. Change Step 2: Create Report runs only On Failure of Step 1: Consolidate Data. B. Change Step 2: Create Report runs On Success and On Failure of Step 1: Consolidate Data. C. Rearrange the job steps so that Create Report is Step 1 and Consolidate Data is Step 2. D. Rearrange the job steps so that Step 1: Consolidate Data runs on failure of Step 2: Create Report. |
Correct Answer & Explanation: B. You must change the execution precedence to always run Step 2: Create Report whether Step 1: Consolidate Data has succeeded or not.
Incorrect Answers & Explanations: A, C, and D. Answer A is incorrect because running Step 2 only on failure of Step 1 would mean that no report would be produced if the data was available and the consolidation succeeded with no errors. Answer C is incorrect because changing the order of the steps would mean that the report will always contain previous week’s data, even when the consolidation succeeded. Answer C is incorrect because rearranging the job steps so that Step 1: Consolidate Data runs on failure of Step 2: Create Report could possibly create an infinite loop if the server you are connecting to is not available. |
|
|
6. You have been asked to produce a report comparing sales activity between the London and São Paulo offices. You restore a backup of the São Paulo sales database on the London SQL Server. You write a query to show sales by product, ordered by product name, and execute it against both databases. You notice that the report based on the São Paulo database is sorted differently. The sales department requires your report in 15 minutes. What is the quickest way to make the sort results of both reports consistent?
A. Use the Import / Export data wizard to copy the table from the São Paulo database to the London database. B. Use the ALTER TABLE statement to change the collation of the Product Name column in the Product table to the collation used in the London database. C. Use the copy database wizard to copy the database to a new database on the London server, specifying the new collation. D. Use the COLLATE Collation_Name option with the ORDER BY clause for the SELECT statement that the report is based on. Specify the collation of the London server as Collation_Name. |
Correct Answer & Explanation: D. The COLLATE option can be used in the query you are running on the São Paulo database to specify the collation to use when sorting data. As you want to be consistent with the results of the query against the London database, use the collation of the London database as the collation name.
Incorrect Answers & Explanations: A, B, and C. Answer A is incorrect because the Import / Export wizard will take a long time; it is not the quickest way to make the sort results of both reports consistent. Answer B is incorrect because the ALTER TABLE statement will also take a long time. Answer C is incorrect because the copy database wizard does not allow you to change database collation. |
|
|
7. You have created a maintenance plan named BasicDBMaint and scheduled it to run every Sunday at 10:00 a.m. When you come back to work on Monday, you wonder whether the plan has executed successfully. What are the easiest ways to do this (choose all that apply)?
A. In SQL Server Management Studio, expand SQL Server Agent | Maintenance Plans. Right-click BasicDBMaint Maintenance Plan, and clickView History. B. Examine the contents of C:\Program Files\Microsoft SQL Server\ MSSQL.1\MSSQL\LOG\ BasicDBMaint_<DateTime> file. C. In SQL Server Management Studio, expand Management | Maintenance Plans. Right-click BasicDBMaint Maintenance Plan, and click View History. D. Examine the contents of C:\Program Files\Microsoft SQL Server\ MSSQL.1\MSSQL\LOG\ SQLAGENT.OUT file. |
Correct Answers & Explanations: B and C. Both of these approaches will show you the Maintenance Plan history.
Incorrect Answers & Explanations: A and D. Answer A is incorrect because maintenance plans are a feature of the Maintenance container in SQL Server Management Studio Object Browser not a feature of SQL Server Agent. Answer D is incorrect because SQLAGENT.OUT file does not contain maintenance plan history. |
|
|
8. Your organization has recently hired a new DBA named Keith. You ask Keith to optimize the maintenance plan you have configured for the ERP database. A few hours later, Keith calls you and explains that he cannot see the Maintenance Plans option in the Management folder in SQL Server Management Studio. What is likely to be the problem?
A. Keith is not a member of the db_owner role for the ERP database. B. Keith is not a member of the sysadmins server role. C. Keith is not a member of the serveradmins server role. D. Keith is connected to SQL Server with a SQL Server login not a Windows Integrated login. |
Correct Answer & Explanation: B. You must be a member of the fixed sysadmin server roles to see maintenance plans in the Object Explorer
Incorrect Answers & Explanations: A, C, and D. Answers A and C are incorrect because it is irrelevant whether Keith is a db_owner for the ERP database or a serveradmin. He must be a sysadmin to see maintenance plans. Answer D is incorrect because users with SQL Server logins or Windows Integrated logins can be members of the sysadmin server role, and, hence, can view and modify Maintenance Plans. |
|
|
9. A flood in the server room has rendered your primary SQL Server in Washington inoperable, and you must wait at least a week for replacement hardware. In the meantime, you must prepare a customer activity report based on the Sales database. You restore the Sales database to a SQL Server in Moscow. You know that the collation of the Washington server was set to Latin1_General_CI_AI, while the Moscow server’s collation is Cyrillic_ General_CS_AS. Unicode is used in all databases in your organization. What must you do to ensure that the customer activity report based on the restored database is sorted in the same way as it was before the disaster?
A. Use the COLLATE Latin1_General_CI_AI clause with the ALTER DATABASE statement after the restore. B. Use the COLLATE Latin1_General_CI_AI clause with the SELECT statement in the report. C. Do nothing.The results will be sorted the same way as they were before the restore operation because the database collation will not change. D. Use the COLLATE Latin1_General_CI_AI clause with the RESTORE DATABASE statement. |
Correct Answer & Explanation: C. The restored database will retain its original collation of Latin1_General_CI_AI because the restore operation preserves database and column level collation.
Incorrect Answers & Explanations: A, B, and D. Answer A is incorrect because the database will retain its original collation, and you will not need to change it with ALTER DATABASE. Answer B is incorrect because the database will retain its original collation, and this collation will be used in the query. Answer D is incorrect because collation is not an option when restoring a database. |
|
|
10. During some electrical maintenance work in your server room, someone has mistakenly pressed the emergency power down button, which caused the entire server room to lose power. After bringing all servers and devices back online you still feel uneasy about the state of your main SQL Server 2008 database. What should you do to check the entire database for corruption and repair the corruption if possible?
A. Connect to the database and execute SELECT ∗ on every table. If the results are displayed, the database is uncorrupted; if not, the database must be restored from backup. B. Run DBCC CHECKTABLE on every table in the database. C. Run DBCC CHECKDB on the database. D. Run DBCC DBREINDEX on the database. |
Correct Answer & Explanation: C. Answer C is correct because DBCC CHECKDB will check and try to repair every table in the database, every catalog in the database, space allocation for the database, and many other checks. It is the most complete validation you can run on a database.
Incorrect Answers & Explanations: A, B, and D. Answer A is incorrect because running SELECT statements does not guarantee that the database is corruption free. For example, indexes or other objects could be corrupted and you will not know until you attempt to use them. Answer B is incorrect because running DBCC CHECKTABLE on each table will only check tables not other objects. Finally, answer D is incorrect because DBCC DBREINDEX will rebuild database indexes not check database objects for validity and corruption. Additionally, this statement is for backwards compatibility only and should not be used in SQL Server 2008. |
|
|
11. You are a part-time database administrator for a small company that relies on its 3GB database for all business needs. You decide to implement a backup strategy of performing a full backup at 1:00 a.m. every night. What is the easiest way to implement your strategy?
A. Open NTBACKUP from Start | Programs | Accessories | System Tools. Create the backup and schedule it to recur every day at 1:00 a.m. B. Use the BACKUP DATABASE Transact-SQL statement to create and schedule the backup job to recur every day at 1:00 a.m. C. Use the Backup Database wizard from SQL Server Management Studio to create the backup job and schedule it to recur every day at 1:00 a.m. D. Use the SQL Server Agent New Job wizard to create the backup job and schedule it to recur every day at 1:00 a.m. |
Correct Answer & Explanation: C. The SQL Server Management Studio backup wizard allows you to backup databases and schedule ongoing backups.
Incorrect Answers & Explanations: A, B, and D. Answer A is incorrect because the NTBACKUP utility cannot be used to back up SQL Server databases. Answer B is incorrect because the BACKUP database Transact-SQL statement cannot be used to schedule ongoing backups. Answer D is incorrect because the SQL Server Agent New Job Wizard does not easily let you create backups, although it can be used to schedule recurring tasks. |
|
|
12. Your organization has recently hired a new DBA named Keith. You wish to notify Keith by e-mail when your SQL Server 2008 experiences an error of severity 025 – Fatal Error. What are the steps you must take to enable this action (choose all that apply)?
A. Create a SQL Server Agent operator to represent Keith. Assign Keith a valid e-mail address. B. Use the Database Mail wizard to configure Database Mail. C. Create a SQL Server Agent alert to be raised when an error of severity 025 – Fatal Error occurs. In the alert settings, choose to notify Keith by e-mail when this alert is raised. D. Start the SQL Server Agent Service and set its startup type to Automatic. E. Create a SQL Server Agent job. For the first step, create a command that parses the server log for the error of severity 025 – Fatal Error.The second step should notify Keith on success of the first step. |
Correct Answers & Explanations: A, B, C, and D. To enable the desired functionality, you must first configure Database Mail. Next, create an alert and an operator, then assign the operator to the alert. Finally, alerts will not fire if the SQL Server Agent Service is not started. Ensure that the service is started and set the startup type for this service to Automatic.
Incorrect Answer & Explanation: E. Answer E is incorrect because this notification should be performed by an alert not a scheduled job. |
|
|
13. Your organization wishes that any user-defined functions that developers create in the ERP database are prefixed with “udf_”. You wish to prevent developers from creating functions that do not adhere to the convention. What is the best way to accomplish this task with minimum administrative effort?
A. Use Policy-Based Management to create a policy condition for the User Defined Function facet, Name Like ‘udf_%’. Create a policy linking this condition with a target set of the database named ERP. Set the evaluation mode of the policy to On Change: Log Only. B. Use Policy-Based Management to create a policy condition for the User Defined Function facet, Name Like ‘udf_%’. Create a policy linking this condition with a target set of any server, any database. Set the evaluation mode of the policy to On Schedule. C. Use Policy-Based Management to create a policy condition for the User Defined Function facet, Name Like ‘udf_%’. Create a policy linking this condition with a target set of any server, any database. Set the evaluation mode of the policy to On Change: Prevent. D. Use Policy-Based Management to create a policy condition for the User Defined Function facet, Name Like ‘udf_%’. Create a policy linking this E. condition with a target set of the database named ERP. Set the evaluation mode of the policy to On Change: Prevent. |
Correct Answer & Explanation: D. Creating a policy with Name Like ‘%udf_’ condition will find all functions that don’t follow the naming convention and show them as noncompliant when you evaluate the policy. This policy will not allow developers to create any noncompliant functions, because the policy evaluation mode is set to On Change: Prevent.
Incorrect Answers & Explanations: A, B, and C. Answer A is incorrect because the On Change: Log Only evaluation mode will not stop developers from creating noncompliant objects. It will simply log an event, which may raise a notification for you to manually deal with. Answer B is incorrect because the On Schedule evaluation mode will not stop developers from creating noncompliant objects. It will evaluate the policy on a particular schedule and report on the findings. Answer C is incorrect because you only wish to apply the policy to the ERP database not all databases. This policy applies to any server, any database. |
|
|
14. You have a database named Procurement. A full backup runs on Procurement every day at 1:00 a.m. The data file for the Procurement database is placed on the D: drive, while the log file for the Procurement database is placed on the L: drive. Unfortunately, the RAID disk hosting the D: drive volume corrupts, and the data on this disk becomes unreadable. The database recovery model was set to FULL. What should you do after you replace the damaged disk to bring the database back online and recover as much data as possible (choose all that apply)?
A. Back up the tail-log that resides on the L: drive. B. Restore the latest full backup to the D: drive using the NORECOVERY option. C. Restore the tail-log you have just backed up and replay it into the Procurement database to bring the database forward using the RECOVERY option. D. Restore the latest full backup to the D: drive using the RECOVERY option. |
Correct Answers & Explanations: A, B, and C (in that order).You have set the recovery model for the procurement database to FULL. This means that the database can be brought forward by replaying logs into it after restore.
To recover the database, you must first backup the tail-log that is still intact on the L: drive. Next, you must restore the database from the latest full backup. However, if you bring the database online at this point, you will not be able to replay logs into this database.Therefore, you must use the NORECOVERY option. Once the full backup has been restored, you can replay the tail-log you have just backed up into the database.This time the database is ready to be brought online, and you can use the RECOVERY option during the restore operation. Incorrect Answer & Explanation: D. Answer D is incorrect because marking the database as RECOVERY before you replayed the tail-log into it will bring the database online. Once the database is online, you can never replay logs into it. |
|
|
15. Your organization has headquarters in Auckland, New Zealand, and branch offices in most countries around the world. Recently, you have centralized all SQL Servers on to a single server in Auckland. The main database is accessed by users from every office. The database is large and takes around 20 hours to perform a full backup. Additionally, the full backup is detrimental to performance and should be minimized during business hours of any of the offices. You are also aware that your organization cannot afford to lose more than one day’s data. Your task is to create a backup strategy that produces the fastest possible restore time, meets the recovery point objective, and has minimal impact on performance experienced by users. What will you recommend?
A. Perform a copy-only backup every Sunday and a differential backup every day at the least busy time. B. Perform a full backup every Sunday and a log backup every day at the least busy time. C. Perform a full backup every Sunday and a copy-only backup every day at the least busy time. D. Perform a full backup every Sunday and a differential every day at the least busy time. |
Correct Answer & Explanation: D. A full backup every week will happen when there is minimal user activity and has the least potential to adversely affect performance. The differential backup every day will record the page differences since the last full backup, so, if you should need to restore, you will only need to restore the full backup and the very last differential backup.
Incorrect Answers & Explanations: A, B, and C. Answer A is incorrect because you cannot base a differential backup on a copy-only backup only a full backup. Answer B is incorrect because although log backups will meet your recovery point objective, this strategy will not produce the fastest restore. Should you need to restore, you will need to restore the full backup and every log backup you have taken since the last full backup (potentially up to six log backups).This is considerably longer than restoring one differential backup. |
|
|
16. Your company headquarters are in London, United Kingdom. Your company also has a branch office in Paris, France. Most customers are registered with both the head office and the branch office. You are tasked with finding out which customers are registered with both offices. To do this, you obtain a backup of the Paris database and restore it onto the London server. You the print a report of all customers sorted by last name from the London and Paris databases. You notice that the same names are sorted differently from each database. You must quickly produce a report from the Paris database that is sorted in the same way as the London database. What is the quickest way to produce this report?
A. Use the ALTER TABLE statement along with the COLLATE option to change the Customers table in the Paris database to have the same colla- tion as the London database. B. Use the COLLATE clause with your SELECT statement to enforce the same collation as the London database. C. Use the Copy Table wizard to move the Customers table from the Paris database to the London database. D. Use the bcp utility to copy data from the Customers table from the Paris database to the London database. |
Correct Answer & Explanation: B. Using COLLATE with a SELECT query will produce results sorted according to rules of a specific collation. In this case, you must find out the collation of the London database, then use this collation when querying the Paris database.
Incorrect Answers & Explanations: A, C, and D. Answer A is incorrect because using the ALTER TABLE statement will probably take a long time and is not the fastest way to produce the required report. Answer C is incorrect because copying the table will not change the column-level collation. Answer D is incorrect because using bcp will not alter the collation of Customers table. |
|
|
17. You are tasked to identify all SQL servers that are running SQL Server 2005 or earlier within your organization. What is the best way to accomplish this task with minimum administrative effort?
A. Use Policy-Based Management to create a policy condition for the Server Information facet,VersionMajor >= 9. Create a policy linking this condition with a target set of all servers. Evaluate the policy to identify servers that don’t comply with this policy. B. Use Policy-Based Management to create a policy condition for the Server Information facet,VersionMajor <= 9. Create a policy linking this condition with a target set of all servers. Evaluate the policy to identify servers that don’t comply with this policy. C. Run the SELECT @@Version statement against every SQL Server in the organization. D. Run the SELECTVersion from sys.ServerInfo. |
Correct Answer & Explanation: A. Creating a policy with the condition of Server Information facetVersionMajor >= 9 allows you to identify all servers running SQL Server 2005 or earlier with minimum administrative effort.
Incorrect Answers & Explanations: B, C, and D. Answer B is incorrect because it will report all servers running SQL Server version 10 or later (SQL Server 2008 or later) as noncompliant, not SQL Server 2005 and earlier. Answer C is incorrect because it will consume a great deal of administrative effort. Policy-Based Management is a much faster way of performing this task. Answer D is incorrect because there is no global sys.ServerInfo table containing versions of all servers in the organization. |
|
|
18. You are a part-time database administrator for a small company that relies on its 3GB database for all business needs. Unfortunately, disk space is at a premium and recovery model for this database is set to SIMPLE. What should you do to protect the database so that their maximum data loss objective of 12 hours is met? What should you tell the company’s owner about the risks of having their primary business database set to a SIMPLE recovery model (choose all that apply)?
A. Perform a full backup every 12 hours. Explain that the SIMPLE recovery model means that should you lose the database due to hardware failure or corruption, you will not be able to recover any transactions that have happened after the last full backup. B. Perform a full backup every day and an incremental log file backup every 12 hours. Explain that the SIMPLE recovery model means that should you lose the database due to hardware failure or corruption, you will not be able to recover any transactions that have happened after the last incre- mental log file backup. C. Perform a full backup every day and a differential backup every 12 hours. Explain that the SIMPLE recovery model means that should you lose the database due to hardware failure or corruption, you will not be able to recover any transactions that have happened after the last differential backup. D. Perform a full backup every 12 hours. Explain that the SIMPLE recovery model means that should you lose the database due to hardware failure or corruption, you may be able to recover all transactions that have happened after the last full backup as long as the log file is intact. |
Correct Answers & Explanations: A and C. Answers A and C are correct because the SIMPLE recovery model dictates that the log is purged almost as soon as transactions are committed. Therefore, incremental log backup is not available on a database set to SIMPLE recovery model, and logs cannot be used to roll a restored database forward. However, you can still use full and differential backup to backup the database, then restore it to the point it was last backed up.
Incorrect Answers & Explanations: B and D. Answer B is incorrect because incremental log backup is not available for databases of SIMPLE recovery model. Answer D is incorrect because you should not set the owner’s expectations by telling him that you may be able to recover all transactions that have happened after the last full backup as long as the log file is intact. SIMPLE recovery model means that the log is automatically overwritten and cannot be replayed into restored database even if the log file is intact. |
|
|
19. You have decided to use the new SQL Server 2008 Data Collector feature to collect performance data from several servers in your organization. You install a SQL Server 2008 on a new server and run through the Configure Management Data Warehouse Wizard to set up data collection. One week later you view the performance reports and see that no data has been collected all week. What should you do to troubleshoot this issue (choose all that apply)?
A. Check that SQL Server Agent service is running and is configured for Automatic startup. B. Check that SQL Server Data Collector service is running and is configured for Automatic startup. C. Check that the recovery model on Management Data Warehouse is not set to SIMPLE. D. Check that you have configured Data Collector proxy accounts so that they have permissions on the servers you are collecting data from. |
Correct Answers & Explanations: A and D. Data collector relies on SQL Server Agent jobs to collect data. It also must have sufficient privileges to collect data from servers. Incorrect Answers & Explanations: B and C. Answer B is incorrect because the Data Collector uses the SQL Server Agent service not the SQL Server Data Collector service (there is no such service installed by SQL Server). Answer C is incorrect because the recovery model overwrites the log file as needed but does not affect the database data.
|
|
|
20. You work for a large organization in a regulated industry. Currently, you adhere to a backup strategy, as shown in Figure 7.6.
Figure 7.6 Current Backup Schedule A recent change in applicable regulatory requirements states that you must provide a backup of your database to a compliance officer every week. This backup must be performed to special media provided by the agency. The compliance officer must be able to easily restore the backup whenever they wish and be able to search through the entire database. The compliance related backup must not interfere with user activity or the existing backup mechanism. The compliance related backups cannot be used in a disaster recovery restore as they are not kept within your reach. What is the best way to meet these compliance requirements without interfering with the existing backup mechanism and avoiding impact on users? A. Take a full backup written to the special media on Sunday at 1:00 p.m. Give this backup to the compliance officer. B. Take a copy-only backup written to the special media on Sunday at 1:00 p.m. Give this backup to the compliance officer. C. Take an incremental backup written to the special media on Sunday at 1:00 p.m. Give this backup to the compliance officer. D. Take a partial backup written to the special media on Sunday at 1:00 p.m. Give this backup to the compliance officer. |
Correct Answer & Explanation: B. The copy-only backup is similar to a full backup but does not change the last backup timestamp. This means that all differential backups will be based on the last full backup and all incremental backups will be based either on the last full or the last differential backup. This will preserve the current backup mechanism and will fall within the light user activity period on Sunday (the copy backup will take the same amount
of time as the full backup does). Incorrect Answers & Explanations: A, C, and D. Answer A is incorrect because all differential and Monday and Tuesday’s incremental backups will be taken off the compliance-related full backup, which will not be accessible if you need to perform a restore. Answer C is incorrect because the incremental backup will not provide the compliance officer with a backup that can be restored. Additionally, by doing this you will invalidate Monday and Tuesday’s incremental log backups. Answer D is incorrect because a partial backup will not provide the compliance officer with a backup that can be restored. |
|
|
Chapter 8: ETL Techniques
|
xx
|
|
|
1. You want to export data from a SQL Server instance to a text file. This operation needs to be scheduled to run at night without human intervention. Select the best option from the following possibilities:
A. Use BCP in a SQL Server Agent Job B. Use BULK INSERT in a SQL Server Agent Job C. Use SQLCMD in a SQL Server Agent Job D. Use OSQL in a SQL Server Agent Job |
Correct Answer and Explanation: A. A is the correct answer. BCP can be used to export data from SQL Server as well as import to SQL Server. It could be entered in an Operating System Command step in a SQL Server Agent Job for scheduling purposes.
Incorrect Answers and Explanations: B, C, and D. Answer B is incorrect, because the BULK INSERT statement can not export data from SQL Server. Answers C and D are incorrect because you might be able to make the SQLCMD or OSQL tools export data, but they are not ideal for exporting data. |
|
|
2. You want to use BCP to transfer data between two instances of SQL Server 2008.The table that you will be transferring data for has some columns that use the nvarchar( ) data type, and has. To ensure that the data is transferred without any corruption of the character data, which data file type should you use?
A. Character B. Unicode C. Native D. Unicode native |
Correct Answer and Explanation: D. D is the correct answer because both the source and target instances will be SQL Server 2008, you can use one of the native data file types. However, to ensure that the Unicode characters in the nvarchar( ) columns are transferred correctly, you should use the Unicode native format.
Incorrect Answers and Explanations: B, C, and D. Answer B is incorrect because it does allow the transfer of Unicode characters in the nvarchar( ) fields, but it converts all data (including numbers, dates, etc) to a Unicode character representation. This method is not as efficient as the native representation of SQL Server data use by the native data file types. Answers C and D are incorrect because they do not support the extended Unicode characters that may occur in the nvarchar( ) fields. |
|
|
3. You need to import data from a data file that has a different number of fields than the target table. What could you do to import the file (select all that apply)?
A. Create a format file to map the columns B. Create an XML format file to map the columns C. Run BCP without a format file D. Run BULK INSERT without a format file |
Correct Answers and Explanation: A and B. Answers A and B are correct. By creating either a non-XML or XML format file, you can map the fields in the source file to the columns in the destination table.
Incorrect Answers and Explanations: C and D. Answers C and D are incorrect because without a format file of some type, both BCP and BULK INSERT will fail on the mismatched columns. |
|
|
4. You need to export data based on a join of two tables into a single flat file. Which of the following bcp command line arguments supports this kind of operation?
A. in B. out C. queryout D. format |
Correct Answer and Explanation: C. Answer C is the correct answer. The queryout BCP argument allows the results of a query to be exported to a data file. In this case, the query could perform the required join operation.
Incorrect Answers and Explanations: A, B, and D. Answer A is incorrect because the in argument specifies that data will be imported to SQL server, not exported. Answer B is incorrect because the out argument only allows us to specify a single table. Answer D is incorrect because the format argument does not export data. |
|
|
5. When doing a BULK INSERT operation you receive an error stating that the transaction log for the database is full. What can you do to make the BULK INSERT succeed (Select all that apply)?
A. Set the database’s recovery model to BULK_LOGGED B. Run the BULK INSERT with the TABLOCK option C. Set the database’s recovery model to FULL D. Use BCP instead of BULK INSERT |
Correct Answer and Explanation: A and B. Answers A and B combined are the correct answer. Setting the database recovery model to BULK_LOGGED allows SQL Server to record only minimal details in the log. Using the TABLOCK option is also required for minimally logged bulk operations.
Incorrect Answers and Explanations: C and D. Answer C is incorrect because the FULL recovery model requires that every record inserted by a bulk operation be completely logged. This is probably why the log is filling in the first place. Answer D is incorrect because BCP performs the same operation as BULK INSERT. Simply using BCP instead of BULK INSERT would have no effect. |
|
|
6. A BULK INSERT operation you run weekly takes too long to execute. You have been asked to optimize the performance of the operation. Which of the following options could help (select all that apply)?
A. Break the file into smaller pieces and run multiple BULK INSERT statements in parallel B. Break the file into smaller pieces and run multiple BCP commands in parallel C. Ask for the source file to be sorted in the same order as the clustered index on the target table, and use the ORDER option. D. Drop any non-clustered indexes from the table before the load |
Correct Answer and Explanation: B, C, and D. Answer B is correct because breaking a file down into smaller pieces and running it in parallel allows multiple client processes to connect to SQL server and stream data in. By running the parallel loads from BCP, a portion of the processing can be distributed among the multiple client systems. Answer C is correct because having a data file pre-sorted to match the clustered index and letting BCP know via the ORDER option that the data is presorted can optimize the load of data into tables
with clustered indexes. Answer D is correct because dropping non-clustered indexes on a table that has data in it Incorrect Answers and Explanations: A. Answer A is incorrect because running multiple batches in parallel with BULK INSERT increases the processing load on the server. This may even hurt the overall performance rather than help it. Parallel loads using BCP on the other hand can distribute a portion of the load over multiple client processors. |
|
|
7. When you are performing a minimally logged bulk operation against a data- base using the BULK_LOGGED recovery model, what must you do after the bulk operation completes to ensure your ability to recover the database?
A. Change the database recovery model to FULL B. Change the database recovery model to SIMPLE C. Truncate the transaction log D. Backup the transaction log |
Correct Answer and Explanation: D.Answer D is the correct answer. When you back up a transaction log that includes information about minimally logged bulk operations, the actual data that was modified as part of the bulk operation is included in the backup of the log, even if it wasn’t included in the live log. By backing up the transaction log after a minimally logged bulk operation, you ensure that you have full details about the bulk operation backed up, and can therefore restore the database to a point after the bulk operation in the event of a catastrophic failure.
Incorrect Answers and Explanations: A, B, and C. Answers A and B are incorrect. They simply change how new transactions will be handled in the transaction log. They do not help you recover previous transactions should the database become corrupt. Answer C is incorrect because it would remove any record of the bulk operation having occurred from the log. This would most certainly not help you recover the database after a failure. |
|
|
8. You have created a linked server to your Oracle instance to control access to the data in the Oracle instance via SQL Server. You now want to prevent your users from performing distributed ad hoc queries against your Oracle instance. However, there are situations where developers need to perform distributed
ad hoc queries against SQL Server instances. What could you do to prevent ad hoc access to only the Oracle instances? A. Use sp_configure to set “Ad Hoc Distributed Queries” to 0 B. Enable the “Disallow adhoc access” property on the Oracle OLE DB provider C. Un-install the Oracle providers from the server D. Create a linked server to the Oracle instance |
Correct Answer and Explanation: B. By setting the “Disallow adhoc access” on the Oracle provider, you prevent any users who are not system administrators from accessing the Oracle provider in ad hoc queries.
Incorrect Answers and Explanations: A, C, and D. Answer A is incorrect because it disables all ad hoc queries and prevents the required ad hoc access to other SQL Server instances. Answer C is incorrect because it would break the linked server you created for secured access to Oracle. Answer D is incorrect because you already have linked server, and while linked servers provide an alternative to distributed ad hoc distributed, they don’t prevent distributed ad hoc queries. |
|
|
9. You need to write a query that joins data from your local SQL Server Instance with data from an Oracle instance. You need to be able to submit specific syntax to the Oracle instance to get the correct results by using a pass-through query. No linked servers currently exist for the remote Oracle instance. You are not an administrator on the system. Which one of the following choices meets your needs?
A. Use OPENQUERY( ) B. Use OPENDATASOURCE( ) C. Use OPENROWSET( ) D. Create a Linked Server |
Correct Answer and Explanation: C. Answer C is correct because OPEN- ROWSET( ) allows you to submit pass-through queries to a remote system, and it does not require that you be an administrator as long as an administrator has allowed ad hoc distributed queries and enabled the required provider for ad hoc access.
Incorrect Answers and Explanations: A, B, and D. Answer A is incorrect because OPENQUERY requires that you have a linked server definition in order to use it. Answer B is incorrect because the OPENDATASOURCE( ) function does not support pass-through queries. Answer D is incorrect because you are not an administrator, and don’t have the privilege to create linked servers on the SQL Server instance. |
|
|
10. You need to provide support for distributed queries to a remote SQL Server instance, but you need to allow it only for selected users. Which one of the following solutions meets the requirements?
A. Grant execute permissions on the OPENROWSET function to the selected users. B. Create a linked server and assign the selected users credentials to use when connecting to the remote instance C. Enable the “disallow adhoc access” on the SQL Server OLE DB to prevent ad hoc access D. Use sp_configure to set the “Ad Hoc Distributed Queries” to 1 |
Correct Answer and Explanation: B. Answer B is correct because Linked servers allow you to create specific login details for individual users to remote databases.
Incorrect Answers and Explanations: A, C, and D. Answer A is incorrect because you can not grant specific permissions to system functions. OPENROWSET is a system function. Answer C is incorrect because disallowing ad hoc access on the provider would affect all users, not just specific users. Answer D is incorrect because it allows all users, not just specific users, to perform distributed ad hoc queries. |
|
|
11. Developers complain that they are receiving errors when trying to perform ad hoc distributed queries against Oracle, although they are able to run distributed ad hoc queries against SQL Server. What must you do to solve the problem?
A. Install the Oracle client libraries on the developer workstations. B. Use sp_configure to set the “Ad Hoc Distributed Queries” to 1 C. Enable the “disallow adhoc access” property on the Oracle OLE DB provider D. Disable the“disallowadhocaccess”propertyontheOracleOLEDBprovider |
Correct Answer and Explanation: D. Answer D is correct. Because developers can run ad hoc queries against other database engines, the problem must be specifically with the Oracle provider. Disabling the “disallow adhoc access” property on the Oracle provider would solve the problem.
Incorrect Answers and Explanations: A, B, and C. Answer A is incorrect because distributed queries rely on the providers available on the server, not on the client workstations. Answer B is incorrect because developers can already run ad hoc queries against other SQL Servers. The option must already be turned on for the query to be true. Answer C is incorrect because enabling the “disallow adhoc access” property on the provider actually prevents its use in ad hoc queries. |
|
|
12. You need to transfer a database to another server for testing purposes. You want to ensure that you get the entire database as well as any logins and SQL Server Agent Jobs related to the database. You want to perform the transfer as easily as possible. Which one of the following tools best meets your needs?
A. Import and Export Wizard B. Copy Database Wizard C. Business Intelligence Development Studio D. BCP |
Correct Answer and Explanation: B. Answer B is correct because the Copy Database wizard makes it easy to transfer an entire database, as well as environmental values like Logins and SQL Server Agent jobs between two instances of SQL Server.
Incorrect Answers and Explanations: A, C, and D. Answer A is incorrect. Although you could use the Import and Export Wizard to transfer each and every table in the source database, you would not be able to transfer the other database objects like stored procedures and functions, nor the metadata. Answer C is incorrect because it isn’t the easiest way to accomplish the task. BIDS does make it possible to create packages that meet our needs, but it would involve much more effort to create and deploy the package than is required by the Copy Database Wizard. Answer D is incorrect because BCP could help move only the data itself, not the object definitions and metadata, nor the external objects like logins and SQL Server Agent Jobs. |
|
|
13. You need to import data from an Access database into a database in SQL Server. You want to perform the import as simply as possible. Which one of the following options best meets the requirements?
A. Import and Export Wizard B. Copy Database Wizard C. Business Intelligence Development Studio D. BCP |
Correct Answer and Explanation: A. Answer A is correct. The Import and Export Wizard makes it simple to directly export data from an Access database and import it into SQL Server.
Incorrect Answers and Explanations: B, C, and D. Answer B is incorrect because it requires the source and destination both be SQL Server. Answer C is incorrect because while BIDS would allows us to create a package that meets our needs, it would not be as simple as the Import and Export Wizard. Answer D is incorrect because BCP import from flat files into SQL Server. |
|
|
14. You need to deploy an SSIS package to a production server. You want to use package roles to control access to the packages. When the developer creates the package, which protection level should they use?
A. Encrypt sensitive with password B. Encrypt all with user key C. Rely on server storage and roles for access control D. Encrypt all with password |
Correct Answer and Explanation: C. Answer C is correct. To use the package roles, the package must be stored in the MSDB database, and be saved with the “Rely on server storage and roles for access control” protection level.
Incorrect Answers and Explanations: A, B, and D. Answers A, B and D rely on passwords or a key unique to the package creator for access rather than package roles. |
|
|
15. Your company implements formal development, test, and production environments. Packages are using servers in the development environment, tested on servers in the test environment, and finally run in the production environment. You need to point the packages to the correct servers depending on the environment without having to edit the packages themselves. Which of the following options best supports changing the properties of a package at runtime?
A. Package Configurations B. DTExec command line options C. “SQL Server Integrations Services Packages” step in a job. D. DTExecUI options |
Correct Answer and Explanation: A. Answer A is correct. Package Configurations make it possible to store the various properties needed in the different environments in XML files, SQL tables or other locations. Package configurations can be assigned at run-time to easily change the properties of a package.
Incorrect Answers and Explanations: B, C, and D. Answers B, C and D all use the same runtime to run a package. While you can use the options of the commands to manually set properties of packages when you run them, it is much more difficult and repetitive than using package configurations. |
|
|
16. You receive a data file each day from a business partner. You need to import the file each morning after it is received. Your developers have created a package to properly import the file, and have deployed the package to your SSIS instance. When they saved the package, they used the “Encrypt all with
Password” protection level. You need to make sure the password can’t be seen in the scheduled job’s definition. What should you do to schedule the package? A. Create a SQL Server Agent job and use an “Operating System (cmdExec)” step to run the DTExec utility B. Create a SQL Server Agent job and use a “SQL Server Integration Services Package” step C. Use the “Scheduled Tasks” in windows to run the DTExec utility nightly D. Use the “Scheduled Tasks” in windows to run the DTExecUI utility nightly |
Correct Answer and Explanation: B. Answer B is correct. By using the
SQL Server Integration Services Package job step, we can securely enter the password needed to open the package, and prevent it from being stored in clear text. Incorrect Answers and Explanations: A, C, and D. Answer A is incorrect because the password to access the package would be easily viewed by anybody who could read the jobs definition. Answer C is incorrect because it too would allow the passwords to be seen by anybody that could read the scheduled task’s definition. Answer D is incorrect because the DTExecUI uses a GUI. You should not schedule GUI programs that require user input. |
|
|
17. You are a sysadmin on your SQL Server instance. You are trying to manage the package roles on the “Export Product Data” package shown in Figure 8.8, but receive an error. What must you do to allow the package roles to be used?
Figure 8.8 Exporting Product Data A. Use the “Encrypt all with user key” protection level B. Use the “Encrypt all with password” protection level C. Save the package to the MSDB database rather than the file system. D. Add your user to the db_ssisadmin database role |
Correct Answer and Explanation: C. Answer C is correct. The package shown was saved to the File System. Packages must be saved to the MSDB database to use package roles.
Incorrect Answers and Explanations: A, B, and D. Answers A and B are incorrect because the “Rely on server storage and roles for access control” protection level must be used, and the package must be saved to the MSDB database to use package roles. Answer D is incorrect because you are already a sysadmin and already have the same permissions as the db_ssisadmin role. |
|
|
18. Your developers and created and successfully tested a package. You have deployed it to your production server and scheduled the package to run using the SQL Server Agent. However, when you look at your SQL Server Agent job history, you see errors on the SQL Server Integration Services Package step. The errors indicate that the connection to the database failed inside the package. Which of the following should you check to find the problem (select all that apply?)
A. Verify that the SQL Server Agent Service account has access to the remote database B. Make sure that the SQL Server Agent service is running C. Make sure that the package was saved with the “Rely on server storage and roles for access control” protection level. D. Check the package configuration to ensure it is pointing at the right database |
Correct Answer and Explanation: A and D. Answer A is correct. When packages are run by the SQL Server Agent they, by default, run using the SQL Server Agent’s service account. If they service account does not have access to the databases referenced in the package the package will fail. You could create a proxy for the SQL Server agent to use that does have access to the database. Answer D is correct because the package may be pointing to a development database that the production SQL Server Agent service does not have access to. You should check package’s configuration to ensure it is pointing at the correct databases for he production environment.
Incorrect Answers and Explanations: B and C. Answer B is incorrect because the SQL Server Agent must be running already, or we would not have seen the error in the first place. Answer C is incorrect because the protection level does not affect how the package connects to remote databases. |
|
|
19. Review the SSIS package control flow shown in Figure 8.9.What is the simplest way to receive an e-mail message after the “Import the Data” task has run, but regardless of its status?
Figure 8.9 SSIS Package Control Flow A. Change the “Send Email” tasks precedence constraint to be on completion rather than success. B. Remove the precedence constraint from the “Send Email” task. C. Add a second precedence constraint between the “Import the Data” and “Send Email” tasks and set the status to Failure. D. Make a copy of the “Send Email” task and add a failure precedence constraint to it from the “Import the Data” task. |
Correct Answer and Explanation: A. Answer A is correct. The “completion” status of a precedence constraint allows the constrained task to run after the completion of the preceding task regardless of whether the preceding task succeeded or failed.
Incorrect Answers and Explanations: B, C, and D. Answer B is incorrect because it would allow the “Send Email” task to run even if the “Import the Data” task never ran. Answer C is incorrect because only a single constraint can exist between two tasks. Answer D is incorrect. Although it would work, there are now two tasks that send e-mail messages. This is harder to manage than answer A. |
|
|
20. You need to create a single package that imports data from multiple data files. Which of the following tools could you use to create the package (select all that apply)?
A. Import and Export Wizard B. Copy Database Wizard C. Business Intelligence Development Studio D. MaintenancePlan Wizard |
Correct Answer and Explanation: C. Answer C is correct because BIDS allows you to create data flows with multiple data sources.
Incorrect Answers and Explanations: A, B, and D. Answer A is incorrect because the Import and Export wizard cannot use multiple data files as a source. Answer B is incorrect because the Copy Database Wizard can only copy entire databases between two SQL Server instances. It does not support importing from data files. Answer D is incorrect because while the Maintenance Plan Wizard does use SSIS, it does not support the importing of data files. |
|
|
Chapter 9: Managing Replication
|
xx
|
|
|
1. You have a database and it was decided to implement load balancing technology so that it will reduce the current load in the existing database. However, after analyzing the existing database, it was found that you cannot have some columns like Salary or Credit card numbers in the secondary server. What technology are you going to use?
A. Clustering B. Log Shipping C. Mirroring D. Replication |
The correct answer is D. Replication is the only technology that allows you to filter from Columns and Rows. Log Shipping and Mirroring cannot be used when there is a requirement for filtering because it supports the entire database.
|
|
|
2. You are a DBA of an organization whose prime business is selling goods.
The organization has several branches around the country. Prices of the goods are controlled by the central location and it needs to be propagated to all branches. Price change occurs once every two years. What replication type are you going to implement? A. Snapshot B. Transactional C. Peer-to-Peer D. Merge |
The correct answer is A. Because this replication needs to update its subscription once every two years, snapshot replication can be used. Also, data is modified at the Publisher end only. Transactional Replication will be used when there are frequent updates, and Merge and Peer-to-Peer Replication is used when there is a need for data modification from Publisher and Subscriber.
|
|
|
3. You are a DBA of an organization whose main business is selling goods around the area. You have sales representatives who have PDAs with them. Before they travel to their clients, they need to download data from the server and at the end of the sales, they need to connect to the network and synchronize their data with the system. What replication type are you going to implement?
A. Snapshot B. Transactional C. Peer-to-Peer D. Merge |
The correct answer is D. Because Merge Subscribers has the option of receiving data, making changes offline and later synchronizing changes with the Publisher and other Subscribers, Merge Replication is the correct replication type.
|
|
|
4. You are a database architect in a data warehouse and you have a source system that is running in SQL Server 2005.You are referring to this database instance to generate Customer Slowly Changing Dimensions (SCD). The customer table has a million records; hence, you need to capture only the changes. You cannot change the source system. What is the technology you use to create
a copy of customer table? A. Clustering B. Log Shipping C. Mirroring D. Replication |
The correct answer is D. Replication is the only technology that allows you to select the needed table rather than selecting all tables. In Log Shipping and Mirroring, database will be in recovering mode while the primary server is in active state. So you cannot access the secondary database file during Log Shipping or Mirroring operations.
|
|
|
5. You are working in an organization where there are retail shops in several locations. Each location is running a SQL Server database and the end of each day there is a requirement to transfer a subset of data from all locations to the main server at the head office. What technology can you use to implement this scenario easily?
A. Linked Server B. Service Broker C. Transactional Replication D. SQL Server Integration Services (SSIS) |
The correct answer is C. Transaction Replication can be used to transfer data between two locations. A linked server is used when you need to transfer data between SQL Server and non-SQL Server databases. Even though you can use SSIS, there are many configurations you need to do provided you have a mechanism to find the modified data.
|
|
|
6. You are a DBA of an organization that has real-time data. This data has a high volume of inserts and updates at the head office. It was decided to expand the business to other locations and those locations need information at the head office. Replication mechanism was selected as the method of data distribution. What replication type are you going to implement?
A. Snapshot B. Transactional C. Peer-to-Peer D. Merge |
The correct answer is B. Because this replication needs to be near real-time data and the system has a high volume of transactions,Transactional replication can be used. Snapshot replication is not an option when there is a huge data load. Peer-to-Peer and Merge can be considered when data inserts/updates/ deletes are at all nodes.
|
|
|
7. You have asked to implement Disaster Recovery (DR) to your SQL Server 2008 database. In addition to the DR, you are looking into options for using the DR instance as reporting database. What technology can you implement?
A. Log Shipping B. Transactional Replication C. Merge Replication D. Mirroring |
The correct answer is B. Log Shipping and Mirroring can be used for the DR method but cannot be used as reporting solutions since the database will be
in recovering mode while Log Shipping and Mirroring are in operation. There is no need for Merge Replication since data will be modified only at one end. Therefore, Transactional Replication is the answer for this. |
|
|
8. You have configured Transactional Replication and you have not disabled DDL Replication. What is the T-SQL statement that will not be replicated to subscribers, if you run it in a Publisher?
A. ALTER TABLE statement on a table that is a published article B. ALTERVIEW statement on a view that is a published article. C. DROP TABLE statement on a table that is a published article. D. ALTER TRIGGER statement on a table that is a published article and the trigger is a DML trigger statement. |
The correct answer is C. You cannot drop the table when it is being used for replication, and all the other DDL statements will be replicated.
|
|
|
9. You have implemented replication between SQL Server 2008 and a SQL Server 2005 database server instance. Because of the system expansion, there are some modifications to be done to the existing article. What step can you take?
A. Since DDL replication is enabled by default, there is nothing you have to do. B. Drop the article and recreate them. C. Recreate the Publisher. D. Recreate the Subscriber. |
Answer A is correct. DDL Replication is enabled by default in SQL Server 2008 and 2005. Therefore, there is no need to do any additional configurations.
|
|
|
10. What action will result in schema changes in Subscriber where DDL replication is enabled?
A. Changing column type smalldatetime to date B. Adding a new table C. Modifying DDL trigger D. Adding Stored procedure |
Answer A is the correct answer. By enabling DDL replication, modification done to the replicated articles will be replicated. Therefore, changing column types will be replicated. DDL triggers are not replicated.
|
|
|
11. You have table named tblSETTINGS, which takes the following format:
CREATE TABLE tblSETTINGS (SETTINGNAME VARCHAR(150)) This table changes frequently. What replication type can you include to replicate the preceding table? A. Snapshot Replication B. Transactional Replication C. Merge Replication D. Cannot implement any replication type |
The correct answer is C. You cannot use Transaction Replication because there is no primary key in the table. Snapshot Replication cannot be used because
it is not feasible when there are frequent updates. Therefore, you have to use Merge Replication. |
|
|
12. You are a DBA at an organization with several locations, and each location has a SQL Server 2008 instance. Employee records from all the locations are at also at the head office, and each employee record needs to be replicated to each location. What scalable method can you use to implement this?
A. Implement many publishers and each publisher has a filter with hardcoded location name. B. Implement Merge Replication with dynamic filters. C. Implement this using Peer-to-Peer Replication with dynamic filters. D. You cannot implement this using replication. |
The correct answer is B. Dynamic filters are available only with Merge Replication. Though you can use many Publishers and subscribers, this is not a feasible solution because there are several subscribers.
|
|
|
13. You have enabled Peer-to-Peer Replication and later found that you want filter some data in the replication. How can you do this?
A. Modify the initial publication and filter data. B. Disable the Peer-to-Peer Replication and filter data. C. Create a new database, and restore the current database and recreate Peer-to-Peer Replication. D. You cannot have filtering for Peer-to-Peer Replication. |
Answer D is correct. You cannot have filtering in Peer-to-Peer Replication. After enabling Peer-to-Peer Replication, you don’t have the option of disabling it.
|
|
|
14. You have more than 20 subscribers; what is the subscription method are you going to use to improve the performance?
A. Push B. Pull C. Both Push and Pull D. Nothing will change the performance |
Answer B is correct. To improve the performance you can use Pull subscription. In Pull subscription, agents will run at the subscribers. Because there are more than 20 subscriptions, if you configure Push subscription, all the load will be at distribution.
|
|
|
15. You are going to propose buying SQL Server 2008 version to support replication. Currently you have Oracle Servers and they may be included for publication. What SQL Server edition are you going to purpose?
A. Enterprise B. Standard C. Workgroup D. Express |
The correct answer is A. Oracle publication can be used only in Enterprise, not in other editions.
|
|
|
16. You are a DBA of an organization where several replications are configured. In this configuration you have noticed that some agents suddenly stopped; you are not aware of the reason or when the agent was down. What is the monitoring tool you can use to find that information?
A. Replication Monitor B. DMVs C. Performance Monitor D. Profiler |
Answer C is correct. In Performance Monitor there is a counter object named Replication Agent. From this you can measure the number of agents that are running. Also, in Performance Monitor, you can monitor the Memory, CPU, and IO counters as well. Therefore, you can combine all the necessary counters and find the reason and time.
|
|
|
17. Oracle Servers can be included in your replication topology. How can an Oracle server function in SQL Server Replication?
A. As a Subscriber only B. As a Publisher only C. As a Subscriber or Publisher D. As a Subscriber, Publisher, or Distributor |
Answer C is correct. In SQL Server replication, Oracle can act as a Publisher or Subscriber, but not a Distributor.
|
|
|
18. You have implemented replication of a SQL Server database server, but are concerned about the additional load of having 100 PDAs running SQL Server Compact Edition subscribing to read-only publications. You have monitored that there are performance issues in the replication. What step can you take to improve the performance?
A. Move the Publisher duties to another SQL Server to lessen the load on the main server. B. Move the Distributor duties to another SQL Server to lessen the load on the main server. C. Move the Subscriber duties to another SQL Server to lessen the load on the main server. D. There is nothing you can do to lower the load if you have that many clients. |
The correct answer is B.You can offload the distribution duties to another server and have all the subscribers connect to the distribution database in the replication.
|
|
|
19. You have SQL Server 2005 replication and decided to upgrade it to SQL Server 2008. In which order do you perform an upgrade to SQL Server 2005 for replicated databases to SQL Server 2008?
A. Distributor,Publisher,thenSubscriber B. Publisher, Distributor, then Subscriber C. You cannot upgrade SQL Server 2005 to SQL Server 2008 D. Any order |
Answer A is the correct answer. The Distributor must be at the same or greater version than the Publisher to receive changes. The Subscriber must then be equal to or less than that of the Publisher.
|
|
|
20. What can tracer tokens measure in SQL Server 2005 Transactional Replication?
A. The time duration between a transaction being committed on the pub- lisher and it being written to the distribution database B. The amount of time a transaction is in the distribution database before all subscribers receive it C. The amount of time elapsed between a command being written to the distribution database and being committed on a subscriber D. A and C |
The correct answer is D. Tracer tokens are to measure the latencies in
Transaction Replication. Tracer tokens are to measure: o ■■ How much time elapses between a transaction being committed at the Publisher and the corresponding command being inserted in the distribution database at the Distributor. o ■■ How much time elapses between a command being inserted in the distribution database and the corresponding transaction being committed at a Subscriber. |
|
|
Chapter 10: Monitoring and Troubleshooting
|
xx
|
|
|
1. What protocol and port number need to be open between the client machine and SQL Server for clients to resolve instance names to port numbers?
A. TCP 1433 B. TCP 1434 C. UDP 1433 D. UDP 1434 |
The correct answer is D. The SQL Server Browser listens on UDP port 1434 while the default instance listens on TCP port 1433.The other two ports listed are not used by SQL Server.
|
|
|
2. You have been informed that a job has been failing. Where should you look to see why the job has failed?
A. ERRORLOG file B. SQLAGENT.OUT file C. Job history within SQL Server Management Studio D. Windows Application log |
The correct answer is C. Job history is written to the msdb.dbo.sysjobhistory table, which can be viewed from within the job history in the SQL Server Management Studio. While job failures are written to the ERRORLOG and Windows Application log, the reason for the failure is not viewable anywhere except in the job history within SSMS or by querying the table directly.
|
|
|
3. If the SQL Server service is not able to start, which log will contain the reason why?
A. SQLAGENT.OUT B. Windows Security Event log C. Windows System Event log D. ERRORLOG |
The correct answer is D. The reason the instance didn’t start will be in the ERRORLOG file. If the SQL Agent can’t start, the reason will be in the SQLAGENT.OUT file. Service failures are not logged to the Windows Security or System Event logs.
|
|
|
4. Your SQL Server will not start. You have been told by the systems administrator that they changed the permissions on the SQL Server folder tree. Where can you find information about why the SQL Server will not start?
A. The SQL Server Agent log file SQLAGENT.OUT B. Windows Application log C. Windows Security log D. Windows System log |
The correct answer is B. Errors about SQL Server not starting will be logged to the Windows Application log. These errors are not written to the system log, security log, or SQL Server Agent log.
|
|
|
5. You are attempting to locate the path to the ERRORLOG file. You open the SQL Server Configuration Manager and edit the service. Which parameter of the SQL Server service should you look at?
A. –d B. –l C. –e D. –t |
The correct answer is C. The –e parameter is the path to the error log file. The –d parameter is the path to the mdf file of the master database. The –l parameter is the path to the ldf file of the master database. The –t parameter is used to add trace flags to the instance so that the trace flag is turned on when the server instance starts.
|
|
|
6. Your company has a policy of rotating the SQL Server and Agent log files daily. You have configured a SQL Agent job to run daily and cycle the error log. You have found that the SQL Agent log is not being rotated as required. What stored procedure should be used to rotate the SQL Agent log?
A. sp_cycle_errorlog B. sp_cycle_agent_errorlog C. sp_cycle_sqlagent_errorlog D. sp_cycle_agent_sqlagent.out |
The correct answer is B. sp_cycle_agent_errorlog is used to cycle the SQL Agent error log. sp_cycle_errorlog is used to cycle the SQL Server’s ERRORLOG file. The system stored procedures in Answers C and D do not exist.
|
|
|
7. You are running a SQL Server trace looking for long running queries, but there is too much data to sift through. What filter should you apply to your trace to show only the long running queries?
A. CPU B. Reads C. Writes D. Duration |
The correct answer is D. The duration filter can be used to display queries that run longer than the duration specified. This same logic can also be used for the CPU, Reads, and Writes filters by showing queries that use more CPU cycles, Read operations, or Write operations respectively.
|
|
|
8. You are setting up a trace and wish to filter the trace to show only statements that take over 5 seconds to complete. What value (in what unit) should you enter in the filter window?
A. 5 (seconds) B. 50 (decaseconds) C. 5000 (milliseconds) D. .083 (minutes) |
The correct answer is C. The SQL Server Profiler requires that times be entered in milliseconds, so you would enter a value of 5000. If you enter a value of 50, all statements that run in under 50 milliseconds will be returned. If you enter a value of 5, all statements that run in under 5 milliseconds will be returned. If you enter a value of .083, an error will be returned as SQL Profiler accepts only whole numbers.
|
|
|
9. You are looking at a SQL Server that is having performance problems.
You have found that the SAN storage is not working correctly. Which performance monitor should you look at to confirm that the storage is not writing data quickly enough? A. Current Disk Queue B. Avg Disk Sec / Write C. Avg Disk Write / Sec D. Avg Disk Queue |
The correct answer is B. The Avg Disk Sec / Write counter tells you the number of seconds that each write operation takes to complete. The Avg Disk Write / Sec counter tells you the number of write operations being performed per second. The current disk queue and avg disk queue counters tell you how many operations are pending completion on the storage.
|
|
|
10. You wish to monitor the throughput of your backup operation to see if the backups are running at their full potential. What performance monitor counter should you monitor?
A. SQL Server: Backup Device – Device Throughput Bytes / sec B. PhysicalDisk – Avg Disk Write / Sec C. PhysicalDisk – Disk Writes / Sec |
The correct answer is A. This counter shows you throughput to the device the database is being backed up to. You can also look at the SQL Server: Databases – Backup/RestoreThroughput/sec counter. The two physical disk counters show all the activity on the disk not just the activity that the backup is causing. If other activities are putting load on the disks, this is shown in the Physical Disk counters but will not be shown in the Backup Device throughput.
|
|
|
11. You have a SQL Server Agent job configured to run T/SQL statements. The job runs under a domain account that is not a member of the sysadmin fixed server role. You wish to add a Command Exec job step to the job. The job fails on the step because the domain account doesn’t have rights to run Command Exec job steps. What should you do to correct this issue without granting excessive rights to the instance?
A. Make the logon a member of the sysadmin fixed server role. B. Make the logon a member of the Windows Administrators group on the server. C. Create a credential based on the Windows logon. Create a proxy based on the credential. Grant the logon rights to the proxy. Assign the proxy to run the job step. D. Create a proxy based on the Windows logon. Create a credential based on the proxy. Grant the logon rights to the credential. Assign the credential to run the job step. |
The correct answer is C. In order to correct this issue, you must create a credential, then create a proxy based on the credential. Next, assign logon rights to the proxy, then use the proxy to run the job step. Answer D is not correct because you must create a credential before creating a proxy. Adding the logon to the sysadmin fixed server role or the Windows Administrators group would give the logon more rights than it needs.
|
|
|
12. You have a SQL Server with several instances installed on it. After the server reboots users are only able to connect to the default instance. What should to do to enable your clients to connect to the named instances again?
A. Restart the SQL Server services for the named instances. B. Restart the SQL Server Agent services for the named instances. C. Restart the SQL Server service for the default instance. D. Restart the SQL Browser service. |
The correct answer is D. If your users are unable to connect to the named instances but are able to connect to the default instance, most likely you need to restart the SQL Browser service. The SQL Browser is used to convert the instance name to the TCP port that it is listening on. If there is a problem with the SQL Browser, then restarting the SQL Server services or the SQL Server Agent services will not resolve the problem.
|
|
|
13. While attempting to troubleshoot connection issues to your SQL Server, you need to identify the TCP port the SQL Server instance is listening on. You examine the SQL Server ERRORLOG as show in Figure 10.16.What ports are being used to connect to the instance?
Figure 10.16 A SQL Server ERRORLOG A. TCP Port 65216 B. TCP Port 50584 C. UDP Port 65216 D. UDP Port 50584 |
The correct answer is A. In this case, users were connecting to TCP Port 65216 and TCP Port 50584 was being used for the dedicated admin connection. Users do not connect to SQL Server instances over any UDP ports. The only UDP port used is UDP port 1434, which is used by the SQL Browser.
|
|
|
14. You have restarted your SQL Server in single user mode but are unable to connect to the SQL Server using the sqlcmd command line utility. The error message that you receive says that only one administrator can connect at this time. What does this error mean?
A. The sqlcmd utility cannot be used when the SQL Server is in single user mode. B. Another login that is a member of the sysadmin fixed server role has connected to the SQL Server. Disconnect that login before you can connect. C. The SQL Server is not yet ready for connections. Wait for the SQL Server to complete its startup process before connecting. |
The correct answer is B. Another user who is a member of the sysadmin fixed server role has connected to the instance. Have that user disconnect and try again. The sqlcmd utility is the recommended utility to use to connect to the SQL Server instance when the instance is in single user mode. It is recommended that you do not use SQL Server Management Studio as it can use more than one connection to the instance. If the SQL Server is not ready for connections, it will give you a different error message.
|
|
|
15. You are attempting to troubleshoot a poorly performing stored procedure that is very complex. What tool should you use to identity the problem statement within the procedure?
A. DatabaseTuningWizard B. SQL Server Management Studio C. SQL Server Profiler D. DatabaseEngineTuningAdvisor |
The correct answer is C. To identify which statements within a stored procedure are having performance problems, you should use the SQL Server Profiler.
Neither the SQL Server Management Studio nor the Database Engine Tuning Advisor will assist you with this task. The Database Tuning Wizard has been renamed to the Database Engine Tuning Advisor. |
|
|
16. You are attempting to troubleshoot another poorly performing stored procedure that is very complex. You need to determine which statement within the stored procedure is causing the performance problem. You decide to use SQL Server Profiler to identity the problem statement. What event should you be monitoring for?
A. RPC:Completed B. SQL:Batch Completed C. SP:Stmt Completed |
The correct answer is C. If you wish to view the run time of statements within a stored procedure, you need to monitor the SP:Stmt Completed event. The SP:Stmt Completed and RPC:Completed will show you commands that are executed against the database engine but not the commands or statements that command executes.
|
|
|
17. You are administrating a system that was upgraded from a single-user Microsoft Access database to a multi-user inventory application using SQL Server 2008 as the backend. Users are complaining that the inventory is no longer accurate. You examine the stored procedure that updates the inventory and you find the following stored procedure:
CREATE PROCEDURE UpdateInventory @Sku VARCHAR(50), @InventoryAmount INT AS UPDATE Inventory SET Inventory = @InventoryAmount WHERE Sku = @Sku GO How should you change this procedure in order to correct the problem? 1. CREATE PROCEDURE UpdateInventory @Sku VARCHAR(50), @AmountSold INT AS Self Test Appendix • Appendix 91 92 Appendix • Self Test Appendix UPDATE Inventory SET Inventory = Inventory - @AmountSold WHERE Sku = @Sku GO 2. CREATE PROCEDURE UpdateInventory @Sku VARCHAR(50), @AmountSold INT AS UPDATE Inventory SET Inventory = Inventory + @AmountSold WHERE Sku = @Sku GO 3. CREATE PROCEDURE UpdateInventory @Sku VARHCAR(50), @InventoryAmount INT AS UPDATE Inventory SET Inventory = Inventory - @InventoryAmount WHERE Sku = @Sku GO 4. CREATE PROCEDURE UpdateInventory @Sku VARCHAR(50), @InventoryAmount INT AS UPDATE Inventory SET Inventory = Inventory + @InventoryAmount WHERE Sku = @Sku GO |
The correct answer is A. The procedure in answer A removes the quantity sold from the current number stored in the database. The procedures in
answers B, C, and D would give an incorrect stored value. The procedure in answer B adds the number sold to the number stored in the database. The procedure in answer C subtracts the final amount according to that client computer from the stored final amount, giving us an effective value of close to 0 (depending on how out of date the value on the client is).The procedure in answer D adds the final amount according to that client computer to the stored final amount. |
|
|
18. You are the database administrator for Contoso, Ltd. Your company policy states that each employee uses a single workstation. The business unit uses a Win32 application on each workstation and a hard coded SQL Server login to connect to the database. You wish to monitor the activity of a specific user within the application by using SQL Profiler. What column can you use to filter against to restrict the data to only the one user in question?
A. TextData B. LoginName C. NTUserName D. HostName |
The correct answer is D. The HostName column contains the name of the workstation that each user is connecting from. You can filter against this column after finding the name of the computer the user is logged into. The TextData field contains the commands being run against the database. The LoginName in this case shows the SQL Server login, which is hard coded within the application. The NTUserName in this case would be blank as the domain credentials are not being passed to the database because a SQL login is being used to authenticate.
|
|
|
19. Suppose you are running a SQL Agent job with a command executable under a domain account that is not a member of the sysadmin fixed server role. If no proxy account is configured, what domain account would a job step be run under?
A. Users account B. Sysadmin account C. Account running the SQL Server Agent D. Account running the SQL Server |
The correct answer is C. If there is no proxy configured, the step must run under the credentials of the account that runs the SQL Server Agent, not the sysadmin account, user’s domain account, or the account that runs SQL Services.
|
|
|
20. A user who is not a member of the sysadmin fixed server role is attempting to create a job to call an SSIS package. This job step must be created under a proxy account, but the user reports that they do not have the correct proxy account listed in their drop down menu. What should be done to resolve the issue without granting the user unneeded rights?
A. Make the user a member of the sysadmin fixed server role. B. Grant the user rights to the credential that the proxy is based on. C. Grant the user rights to the proxy. D. Add the user to the SQLAgentOperatorRole fixed database role within the msdb database. |
The correct answer is C. If the user does not see the proxy account in the proxy drop down list, then the login does not have rights to use the proxy account. This can be corrected by editing the proxy and granting the user’s login rights to the proxy. Making the user a member of the sysadmin fixed server role would allow the user to use the proxy account but would also grant the user more rights than are needed. Granting the user rights to the credential would not allow the user to use the proxy account. Adding the user to the SQLAgentOperatorRole would not grant access to the proxies, which are defined within the SQL Server Agent.
|
|
|
Chapter 11: SQL Server XML Support
|
xxx
|
|
|
1. Which of the following is a benefit of using XML data?
A. NativetoWindows B. Better performance C. Native to SQL Server D. Platform independent |
The correct answer is D. Answers A, B, and C are incorrect because they are benefits of XML data.
|
|
|
2. Which of the following benefits can an XML index provide?
A. Easy table access B. XML data type conversion C. Efficient query processing D. Access to ADO.NET |
The correct answer is C.An XML index can provide efficient query processing and good scalability.Answers A,B,and D are incorrect;they are not benefits of using an XML index.
|
|
|
3. Which of the following is a SQL Server 2008 native XML feature?
A. Ability to back up XML data B. Ability to set up server roles C. Ability to set up database roles D. Ability to set up SQL Agent proxies |
The correct answer is A. Answers B, C, and D are incorrect; they are not native XML features.
|
|
|
4. Which of the following is the size limit of the XML data type?
A. 2MB B. 2GB C. 2KB D. 2 bytes |
The correct answer is B. Answers A, C, and D are incorrect because the XML data type provides a greater amount of space than the sizes specified in these answers.
|
|
|
5. Which of the following data types is used in SQL Server 2008 to store XML data?
A. Table B. bit C. XML D. Binary |
The correct answer is C. Answers A, B, and D are incorrect since these data types cannot store XML data.
|
|
|
6. When you use CREATE XML INDEX( ) syntax, what type of index are you creating?
A. A primary XML index B. A clustered index C. A secondary XML index D. A nonclustered index |
The correct answer is C; this syntax will create a secondary XML index. Answer A is not correct because the syntax CREATE PRIMARY XML INDEX( ) is needed to create a primary XML index. Answers B and D are incorrect because they do not apply to creating an XML index.
|
|
|
7. You have a table containing columns with the XML data type. No primary key has been assigned yet, but indexes have been set up on a few of the other table columns, including columns defined with the XML data type. You want to define the primary key. Which of the following best describes what you can do?
A. Use the CREATE PRIMARY XML INDEX( ) syntax to create the primary key. B. Drop all indexes on the non-XML columns. C. Drop all indexes on XML columns. D. Drop the table and re-create, defining the primary key on the XML column first. |
The correct answer is C. Answer A is not correct because running CREATE PRIMARY XML INDEX( ) will result in an error due to the existing XML indexes. Answer B is incorrect because the non-XML columns do not impact the XML primary key in this situation. Answer D will work in this situation, but it is not necessary and, in some cases, not practical to drop the table and re-create. This answer is not the best example of what can be done and is therefore incorrect.
|
|
|
8. Which of the following DDL statements will create a primary XML index?
A. CREATE XML INDEX( ) B. ALTER INDEX( ) C. DROP INDEX( ) D. CREATE PRIMARY XML INDEX( ) |
The correct answer is D. The syntax in answer A is incorrect because it creates a nonprimary XML index, and answers B and C are incorrect because
the request was to create a primary XML index, not to ALTER or DROP the index. |
|
|
9. Which of the following DDL statements will disable a XML index?
A. CREATE XML INDEX( ) B. ALTER INDEX [SD_XML_Data_customerAddress] ON XML_Table C. DROP INDEX( ) D. ALTER INDEX [SD_XML_Data_customerAddress] ON XML_Table DISABLE |
The correct answer is D. The syntax in answer A is incorrect because it creates a nonprimary XML index. Answer B is incorrect because the DISABLE syntax is missing from the statement. Answer C is incorrect because this statement will drop an index.
|
|
|
10. What happens to secondary indexes if the primary XML index is dropped?
A. Nothing happens to secondary indexes. They remain defined on the table. B. Secondary indexes will be dropped. C. Non-XML indexes are dropped. D. All XML and non-XML indexes are dropped. |
The correct answer is B. Answers A, C, and D are incorrect because each of these statements does not represent the result of dropping the primary XML index.
|
|
|
11. What effect does dropping a secondary XML index have on a table that has a primary XML index defined?
A. The primary XML index is automatically dropped and needs to be re- created. B. All secondary indexes will be dropped. C. Only the XML index defined in the DDL statement will be dropped. There is no effect on other indexes. D. All XML and non-XML indexes are dropped. |
The correct answer is C. Answers A, C, and D are incorrect because each of these statements does not represent the result of dropping a secondary XML index.
|
|
|
12. Which of the following has been deprecated in SQL Server 2008?
A. HTTP endpoints B. Native XML Web Services (SOAP/HTTP endpoints) C. Web Services D. XML storage |
The correct answer is B. Answers A, C, and D are incorrect because each of these features is still available in SQL Server 2008.
|
|
|
13. Which of the following SQL Server 2008 editions does not support HTTP endpoints?
A. Express edition B. Enterprise edition C. Developer edition D. Web edition |
The correct answer is A. Answers B, C, and D are incorrect because HTTP endpoints are supported in all SQL server editions, with the exception of the Express edition.
|
|
|
14. When creating the endpoint, what happens when the STATE argument is set to STARTED?
A. The endpoint is started, and it is actively listening for connections. B. The endpoint will not start until after the database server has been restarted. C. The endpoint will start when SQL Agent calls it. D. The endpoint will start when a user requests it. |
The correct answer is A. Answers B, C, and D are incorrect because these events are not accurate in relation to setting the STATE argument to STARTED.
|
|
|
15. How can you easily test an endpoint to ensure that it is working?
A. Type the URL of the endpoint into a query window of the SQL Server B. Management Studio and click on execute. C. Create a new test Web service that consumes the endpoint. D. Run the URL from the DOS command prompt. E. Browse to the endpoint URL in the add Web reference screen in Visual Studio. |
The correct answer is D. Endpoints can be easily checked by browsing to them from the Visual Studio add Web reference screen. If the methods of the
endpoint are displayed, it is working properly. Answers A, B, and C are incorrect because these answers do not represent either easy or valid methods of testing an endpoint. |
|
|
16. Which of the following program languages can be used when creating Web Services?
A. BASIC B. COBOL C. Action Script D. C# |
The correct answer is D.Web Services can be written in program languages such as C++,VB.NET, and C#. Answers A, B, and C are incorrect because these programming languages cannot be used when creating
Web Services. |
|
|
17. What does the XQuery Query( ) method do?
A. Returns XML data that matches a query. B. Returns the node of the query. C. Returns an extracted value from an XML document. D. Checks for the existence of a value in an XML document. |
The correct answer is A. Answers B, C, and D are incorrect because these events are not valid results of using the query( ) method.
|
|
|
18. Which of the following are extensions of the XQuery modify( ) method?
A. Update B. Delete C. Exists D. Nodes |
The correct answer is B. Answers A, C, and D are incorrect because these are not valid extension of the modify( ) method.
|
|
|
19. If you want to identify a set of nodes within an XML document, which of the following is used?
A. SELECT B. XQuery C. EXISTS D. XPath |
The correct answer is D. Answers A, B, and C are incorrect because these commands will not work when you are trying to identify a set of nodes within an XML document.
|
|
|
20. Which of the following is an expression type for XPath?
A. Approximate b. Binary C. Unicode D. Node |
The correct answer is D. Node is the only valid choice listed here; therefore answers A, B, and C are incorrect.
|
|
|
Chapter 12: Service Broker
|
xxx
|
|
|
1. You are working with a developer to design a message processing system.
SQL Server Service Broker has been selected as the Message Queuing system. You are tasked with designing a solution that will allow for the fastest processing of messages while impacting the system with the lowest possible impact. You are expecting only a few messages to be received per hour, but these messages should be processed as quickly as possible. What should you do? A. Write a Windows Service that queries the queue every minute looking for new messages to process. B. Write a stored procedure that queries the queue, and schedule this procedure to be run by the SQL Server Agent every minute. C. Write a stored procedure that queries the queue, and set it as an activated stored procedure on the queue. D. Write a Windows Service that queries the queue every hour looking for new messages to process. |
The correct answer is C. By writing a stored procedure and making the stored procedure an activated procedure of the queue, the only time the system
will be queried for new messages is after a new message has arrived. This will provide us with immediate processing of the message, while putting no unneeded impact on the system. The other three options all require extra load against the SQL Server because the queue would be queried at times where there were no new messages to process. Answer D will not get the messages processed in the quickest amount of time. Answers A and B will cause unneeded load to be placed on the database server. |
|
|
2. You are working with your database developer to design a SQL Service Broker application. The developer has a need to send a blank message as part of an existing conversation, but he is receiving an error saying that the message_body cannot be NULL. What should you do?
A. Delete the Service and the Contract. Create a new Message Type with a validation of empty. Create a new contract containing both the new Message Type and the original Message Type. Re-create the service.Tell the developer to use the new Message Type when sending the messages with the NULL message body, and that the message body should be excluded from the SEND statement when the message body should be blank. B. Create a new Message Type with a validation of empty. Add the Message Type to the Service using the ALTER SERVICE command.Tell the devel- oper to use the new Message Type when sending the message with the NULL message body, and that the message body should be excluded from the SEND statement when the message body should be blank. C. Create a new Message Type with a validation of empty. Add the Message Type to the Contract using the ALTER CONTRACT command.Tell the developer to use the new Message Type when sending the message with the NULL message body, and that the message body should be excluded from the SEND statement when the message body should be blank. D. DeletetheServiceandtheContract.CreateanewMessageTypewitha validation of WELL_FORMED_XML. Create a new contract containing both the new Message Type and the original Message Type. Re-create the service.Tell the developer to use the new Message Type when sending the messages with the NULL message body, and that the message body should be excluded from the SEND statement when the message body should be blank. |
The correct answer is A. In order to add a new Message Type to a Contract you have to delete the Contract and recreate it. In order to delete the Contract you first must delete the Services that are bound to it. You can then recreate the Contract with the new Message Type and the old Message Type, and then recreate the services. When the new message type is created the validation must be NONE or EMPTY to allow for an empty message. Answer B will not work as there is no ALTER SERVICE command. Answer C will not work since Message Types are not bound to Contracts. Answer D will not work since messages with the validation of WELL_FORMED_XML will not accept an empty message.
|
|
|
3. A developer is attempting to delete some obsolete SQL Service Broker objects from the database as part of your annual database object cleanup project. However, each time he attempts to drop the contract (YourApplication/ Contract1), he receives the error “The contract ‘YourApplication/Contract1’ cannot be dropped because it is bound to one or more service.” How can you identify the service or services that are bound to this contract in the least amount of time?
A. Script out all the Services on the instance and read through them looking for the service or services that are bound to the contract. B. Join the sys.services catalog view and the sys.contracts together in a SELECT statement and use the output to identify which Services are bound to the Contract in question. C. Join sys.services, sys.contracts, and the sys.service_contract_usages together in a SELECT statement and use the output to identify which Services are bound to the Contract in question. D. Use the object explorer to view the properties of the Contract. |
The correct answer is C. In order to see which services are bound to the contract in question you need to join the sys.services and sys.contracts catalog views to the sys.service_contract_usages catalog view.Answer A can also do this, but a requirement of the question is to do the required task in the least amount of time.The sys.services and sys.contracts cannot be joined together directly, and there is no listing on the contracts properties page to see which services are bound to it.
|
|
|
4. You work for Adventure Works LLC as a database administrator. Your order entry system uses SQL Service Broker to get orders from the Web site into the database. A customer contacts your customer service department saying that his order is showing on the Web site but has no order details. Your SQL Service Broker Queue is configured with the RETENTION flag set to ON. You need to reprocess the SQL Service Broker message; what do you do?
A. Mark the message for reprocessing by using the UPDATE command against the Queue. B. Use the RECEIVE statement to receive the message a second time by put- ting the message handle in the WHERE clause of the RECEIVE statement. C. Get the value of the message by using the SELECT statement against the Queue and send a new message using the value from the old message. D. Use the ALTER QUEUE statement with the ALTER MESSAGE syntax to change the status of the message from processed to pending. |
Answer C is correct. Of the four methods shown only the method in answer C exists within the SQL Server. You cannot write UPDATE commands against SQL Service Broker Queues. There is no message handle that can be used in the WHERE clause of the RECEIVE command. Even if you were to use the conversation handle, putting a conversation handle of a closed conversation handle will not reprocess the messages. There is no ALTER MESSAGE syntax of the ALTER QUEUE command to use to change the status of the messages.
|
|
|
5. You manage a large SQL Service Broker system, which sends messages from server to server. You have restored your production database to your development environment. You need to ensure that messages are being sent from your development server to the other development servers in your environment and not to your production servers. Which method do you use?
A. Use the ALTER ROUTE statement to change the ADDRESS and BROKER_INSTANCE values to those of the remote development server. Then use the ALTER DATABASE statement to change the local service broker GUID and enable the service broker with the ENABLE_BROKER syntax. B. Use the ALTER ROUTE statement to change the ADDRESS and BROKER_INSTANCE values to those of the remote development server. Then use the ALTER DATABASE statement to change the local service broker GUID and enable the service broker with the NEW_BROKER syntax. C. Use the ALTER DATABASE statement to change the local service broker GUID and enable the service broker with the NEW_BROKER syntax. D. Use the ALTER DATABASE statement to change the local service broker GUID and enable the service broker with the ENABLE_BROKER syntax. |
Answer B is the correct answer. Answer B will change the local databases service broker GUID to a new value, and bring the database online as well as direct the route to direct the messages to the development server. In order to ensure that you are not sending messages to the incorrect server, and that the incorrect server cannot send back messages, you need to change the route to point to the other development server as well as change the local service broker GUID by using the ALTER DATABASE command with the NEW_BROKER syntax. This will also remove all messages that are in the middle of being processed so that they are not sent on to a server that is not expecting them. If only one of these commands is issued, then not all requirements can be guaranteed.
|
|
|
6. You are setting up a new SQL Service Broker application and the application requires that messages be sent to a SQL Server Instance that is mirrored.
You need to configure a new route and ensure that the route fails over to the mirror server. Which syntax will create this route correctly (assuming that the GUID provided is correct)? 1. CREATE ROUTE YourRoute AUTHORIZATION dbo WITH SERVICE_NAME='YourApplication\YourService', BROKER_INSTANCE='721B1C8E-B314-4E73-A9ED-194CF1DA87EB', ADDRESS='TCP://SQLServer4:8432', MIRROR_ADDRESS='TCP://SQLServer4_Mirror:8432'; 2. CREATE ROUTE YourRoute AUTHORIZATION dbo WITH SERVICE_NAME='YourApplication\YourService', BROKER_INSTANCE='721B1C8E-B314-4E73-A9ED-194CF1DA87EB', ADDRESS='TCP://SQLServer4:8432'; 3. CREATE ROUTE YourRoute AUTHORIZATION dbo WITH SERVICE_NAME='YourApplication\YourService', BROKER_INSTANCE='721B1C8E-B314-4E73-A9ED-194CF1DA87EB', ADDRESS='TCP://SQLServer4:1433', MIRROR_ADDRESS='TCP://SQLServer4_Mirror:1433'; 4. CREATE ROUTE YourRoute AUTHORIZATION dbo WITH SERVICE_NAME='YourApplication\YourService', BROKER_INSTANCE='721B1C8E-B314-4E73-A9ED-194CF1DA87EB', ADDRESS='TCP://SQLServer4', MIRROR_ADDRESS='TCP://SQLServer4_Mirror'; |
Answer A is correct. In order to set up your route you have to specify the TCP port number on which the Service Broker Endpoint is listening, on the remote instance. This port number is different than the TCP port number on which the main SQL Server instance is listening. In order to ensure that the SQL Service Broker fails over to the mirror instance you must specify both the ADDRESS and MIRROR_ADDRESS settings. If you do not specify both settings then the SQL Service broker will not fail over to the mirror server in the event of a database mirroring failover. In order to meet all the specified requirements Answer A is the only option that will correctly create the route and successfully fail over.
|
|
|
7. Your application has an XML schema that was defined at a prior time. You wish to ensure that the message sent by your application conforms to the defined XML schema. Which syntax should you use to enforce this with the least amount of T/SQL code when sending the message?
A. CREATE MESSAGE TYPE [YourApplication\YourMessageType] AUTHORIZATION dbo VALIDATION = NONE B. CREATE MESSAGE TYPE [YourApplication\YourMessageType] AUTHORIZATION dbo VALIDATION = EMPTY C. CREATE MESSAGE TYPE [YourApplication\YourMessageType] AUTHORIZATION dbo VALIDATION = WELL_FORMED_XML D. CREATE MESSAGE TYPE [YourApplication\YourMessageType] AUTHORIZATION dbo VALIDATION = VALID_XML WITH SCHEMA COLLECTION YourXSDSchemaObject |
Answer D is correct. Of the four available validation options, the only option that will automatically enforce the preexisting schema collection is the VALID_ XML WITH SCHEMA COLLECTION. Although the WELL_FORMED_ XML option will ensure that the XML is valid, it does not automatically verify the XML against the XML schema. Doing so would require additional code within the T/SQL, which sends the Service Broker message. The NONE validation does no validation, and the EMPTY validation does not allow for any data to be placed within the message. All this being the case, Answer D is the only option that fulfills all the requirements of the question.
|
|
|
8. You are configuring SQL Service Broker on a database that has never used SQL Service Broker before. What command needs to be used to set up the SQL Service Broker for use for the first time?
A. ALTER DATABASE YourDatabase SET NEW BROKER; B. ALTER ServiceBroker ENABLE; C. ALTER DATABASE YourDatabase ENABLE BROKER D. EXEC sp_enablebroker |
The correct answer is A. Answer A is the only valid command or syntax shown. There is no sp_enablebroker stored procedure in the database. Service Broker is set up via the ALTER DATABASE command using the SET NEW BROKER switch.
|
|
|
9. You have restored your production database after the database became corrupt. What needs to be done, if anything, to ensure that the SQL Service Broker works correctly after the database restore has been completed, without losing any messages that are waiting to be processed?
A. Use the ALTER DATABASE command with the NEW_BROKER switch to enable the SQL Service Broker. B. Use the ALTER DATABASE command with the ENABLE_BROKER switch to enable the SQL Service Broker. C. Use the ALTER DATABASE command with the RESTORE_BROKER switch to enable the SQL Service Broker. D. Nothing needs to be done to enable the SQL Service Broker after a database restore is completed. |
Answer B is the correct answer. After the database is restored you need to use the ALTER DATABASE command with the ENABLE_BROKER switch to turn the SQL Service Broker back on without losing any messages in the queues or in flight. Using the NEW_BROKER switch will cause all messages in the queues to be purged and the guid of the SQL Service Broker in the database will change, which will then cause any routes that send messages into the database to stop working. There is no such switch as the RESTORE_ BROKER switch.
|
|
|
10. You wish to maximize the performance of your SQL Service Broker application. You have decided that you want to increase the number of messages sent per conversation. What technique should you use?
A. Create a conversation and send all messages to all services on that conversation. B. Create a conversation for each destination service and send all messages on the corresponding service. C. Send the messages with a NULL conversation handle. |
Answer B is the correct technique to use. Answer A is not a usable technique because messages must be sent on a separate conversation per service. Answer C is not a usable technique because messages cannot be sent with a NULL conversation handle. Sending all messages that go to the same service under the same message handle can be up to 80% more efficient.
|
|
|
11. You are managing a system that uses SQL Service Broker. You wish to pause
the automatic processing of messages that arrive sporadically without stopping messages from being delivered into the queue. Which command should you use? A. ALTER QUEUE YourQueue WITH STATUS=OFF; B. ALTER QUEUE YourQueue WITH ACTIVATION (STATUS=OFF); C. ALTER QUEUE YourQueue D. WITH ACTIVATION (PAUSE=TRUE); E. ALTER QUEUE YourQueue WITH MAX_QUEUE_READERS=0; |
Answer B is correct. You want to use the syntax shown in Answer B. This will stop new activation threads from starting without stopping the queue from receiving new messages. Answers C and D are not valid syntaxes for the ALTER QUEUE command. Answer A will disable the queue and stop all new messages from being delivered into the queue.
|
|
|
12. You need to identify the TCP port number that your SQL Service Broker Endpoint is using. Which query will allow you to most easily identify the TCP port number of the SQL Server Broker Endpoint?
A. SELECT p.port FROM sys.tcp_endpoints as p Self Test Appendix • Appendix 107 108 Appendix • Self Test Appendix JOIN sys.service_broker_endpoints as s ON s.endpoint_id = p.endpoint_id; B. SELECT p.port FROM sys.tcp_endpoints as p JOIN sys.endpoints as s ON s.endpoint_id = p.endpoint_id; C. SELECT p.port FROM sys.service_broker_endpoints p; D. SELECT p.port FROM sys.endpoints p |
Answer A is the query that will most easily identify the TCP port which the SQL Service Broker Endpoint is using. Answer B will show you the TCP port that all Endpoints on the system are using. Answers C and D are not valid queries and the column port does not appear in either of those catalog views.
|
|
|
13. You are designing a new asynchronous messaging infrastructure for a new Enterprise level application that your team is developing. Under which circumstance should you not use SQL Service Broker?
A. When both the sending and receiving processes are going to be T/SQL commands. B. When the sending process will be a T/SQL command, and the receiving process will be a Windows process. C. When the sending process will be a Windows process, and the receiving process will be a T/SQL command. D. When both the sending and receiving processes are going to be Windows processes. |
Answer D is correct. When both the sending and receiving processes are going to be Windows processes and no T/SQL will be involved, the SQL Service Broker may not be the best solution for your application. In this case you may be better off using the Microsoft Message Queue instead. In the other three options (A, B, and C), SQL Service Broker will be an excellent choice as your Message Queue infrastructure.
|
|
|
14. You are designing a SQL Service Broker application that will be sending messages from instance to instance. Both instances will be running SQL Server
2008 Express edition. The messages will be routed through a third instance of SQL Server 2008.What edition or editions can be used as the server that is forwarding the messages from one SQL Server 2008 Express instance to another SQL Server 2008 Express instance in your production environment? (Select all that apply.) A. SQL Server 2008 Express Edition B. SQL Server 2008 Web Edition C. SQL Server 2008 Workgroup Edition D. SQL Server 2008 Standard Edition E. SQL Server 2008 Enterprise Edition F. F. SQL Server 2008 Developer Edition |
Answers B, C, D, and E are correct. In order to route messages from one server to another you must route the messages through a paid-for edition of SQL Server 2008. SQL Server 2008 Developer Edition is a paid-for edition; however, it cannot be licensed for production use and does not qualify. SQL Server 2008 Express Edition also does not qualify. SQL Server 2008 Web, Workgroup, Standard, and Enterprise Editions all qualify.
|
|
|
15. You are managing an OLTP database that makes heavy use of the SQL Service Broker. You have identified that an activated stored procedure is causing performance problems. You need to stop processing by the activated stored procedure as quickly as possible. What action should you take?
A. Use the ALTER QUEUE command and set the MAX_QUEUE_ READERS setting to zero. B. Use the ALTER QUEUE command and set the ACTIVATION STATUS = OFF. C. Use the ALTER QUEUE command and set the STATUS = OFF. D. Use the ALTER QUEUE command and change the PROCEDURE_ NAME setting to an invalid stored procedure name. |
Answer C is the correct syntax to stop the activated stored procedures from processing messages as quickly as possible. When you use the STATUS = OFF the queue is disabled, which prevents the already running activated stored procedures from receiving any more messages out of the queue. The procedures will then exit with an error message. Setting the MAX_QUEUE_READERS to zero or setting the ACTIVATION STATUS to OFF have the same result. Making either of these changes will not stop the activated stored procedures from continuing to process. These settings simply stop any new instances of the activated stored procedures from being started. If you attempt to change the PROCEDURE_NAME setting to an invalid procedure name the ALTER QUEUE command will fail with an error message, because only valid procedure names can be used.
|
|
|
16. You are creating new Service Broker objects for a new SQL Service Broker application. The IT department coding standards require that when contracts are defined you must specify the message type used for the source and destination. You are creating your new services and are defining your message types. Which code block is the correct code block to use?
A. CREATE CONTRACT YourContract AUTHORIZATION dbo SourceMessageType SENT BY ANY, DestinationMessageType SENT BY ANY; B. CREATE CONTRACT YourContract AUTHORIZATION dbo SourceMessageType SENT BY INITIATOR, DestinationMessageType SENT BY ANY; C. CREATE CONTRACT YourContract AUTHORIZATION dbo SourceMessageType SENT BY INITIATOR, DestinationMessageType SEND BY TARGET; |
Answer C is the correct solution. Although answers A and B will correctly create the contract, those two code samples do not meet the IT department coding standards, which require that contracts are defined with specific definitions so that the message type can be used by only one side of the application.
|
|
|
17. You are seeing performance problems on your SQL Server Service Broker application. Messages are taking too long to be processed by the activated stored procedure. The stored procedure itself is already fully optimized. What can you do in order to decrease the amount of time it takes to process messages with the least amount of effort?
A. Increase the number of parallel processes that are run automatically by changing the MAX_QUEUE_READERS value via the ALTER QUEUE command. B. Increase the number of parallel processes that are run automatically by the receiving service by changing the MAX_QUEUE_READERS value via the ALTER SERVICE command. C. Reduce the number of messages by sending more data per message. D. Create more queues and send some of the messages to each of the queues. |
The correct answer is A. You will want to use the ALTER QUEUE command to increase the number of threads that are run in parallel. If you already have
set this to the maximum number of threads, then you could consider answer D, but it is not likely that you will ever need to make this change. |
|
|
18. You have two instances on a server that need to be able to send messages between the instances. Your corporate IT policies say that SQL Service Broker Endpoints must be created on TCP port 5600.You create and bring online the first Endpoint, but you cannot get the second Endpoint to come online. What should you do to correct the issue?
A. Add a second IP to the server. Configure the first instance to use the first IP for the Endpoint, and the second instance to use the new IP for the Endpoint. Start the Endpoint on the second instance. B. Change the port number of the second instance to a different port number using the ALTER QUEUE command and bring the Endpoint online. C. Restart the SQL Server instance that has the Endpoint that will not come online. D. Use the ALTER ENDPOINT command with the FORCE_ONLINE switch to force the Endpoint to come online. |
The correct answer is A. Because your IT policy states that the Service Broker Endpoint must be created on TCP port 5600, a second IP address must be added to the server so that each Endpoint can have its own IP to listen on. Restarting the instance will not help bring the Endpoint online as the port number is locked by the other instance. There is no FORCE_ONLINE switch for the ALTER ENDPOINT command.
|
|
|
19. You are defining new SQL Service Broker objects. Your messages must contain XML data, but there are several different schemas that can be used that will be identified by the receiving process by querying the XML document within the message body to identify which code branch to use. What validation method should you use when creating the Message Type?
A. NONE B. EMTPY C. WELL_FORMED_XML D. VALID_XMLWITHSCHEMACOLLECTION |
Answer C is correct. You should select the WELL_FORMED_XML.The VALID_XML WITH SCHAMA COLLECTION option requires a single XML schema be defined. Because your requirement is that different XML schemas can be sent, but you want to ensure that the XML is valid, the WELL_ FORMED_XML option is the correct answer.
|
|
|
20. You have a SQL Service Broker application that you wish to increase the performance when receiving messages. What changes should you make?
A. Change the receiving code to load the data into a table, increasing the number of messages being received at one time by increasing the value of the TOP parameter. B. Change the sending code to send more than one message in the same conversation. Change the receiving code to load the data into a table, increasing the number of messages being received at one time by increasing the value of the TOP parameter. C. Change the sending code to send more than one message in the same conversation. D. Increase the number of activated procedures that can run at any one time. |
Answer B is the correct answer. You have to change both the sending code and the receiving code to increase this performance. Increasing the number of activated procedures will not increase the performance of the RECEIVE command so that is not a good solution.
|
|
|
Chapter 13: Performance Tuning
|
xxx
|
|
|
1. You’re the DBA for a sales and marketing organization who have a high rate of data change (lots of INSERTS and DELETES). Over the past few weeks the SQL server has gradually become slower. Which DMV could help identify the cause?
A. Sys.dm_os_schedulers B. Sys.dm_os_slow_tasks C. Sys.dm_db_index_physical_stats D. Sys.dm_exec_requests |
The correct answer is C. Since there’s a high data churn, it’s possible that index fragmentation is the cause; the index physical stats will confirm whether
this is the case. Problems with the SQLOS schedulers are unlikely to get progressively worse. The DMV in Answer B does not exist. The DMV in Answer D shows currently executing tasks and will be less useful when investigating gradual server slowdown. |
|
|
2. Which of the following could be used within a function by Resource Governor to classify incoming sessions?
A. IPAddress B. Username C. Server Role Membership D. Hostname |
The correct answers are A, B, C, D. All options could be used within a UDF to classify incoming sessions.
|
|
|
3. You’ve recently enabled Resource Governor to ensure the Finance and HR departments each receive a fair share of CPU and memory on a consolidated SQL Server. Since Resource Governor has been operating, some users have experienced connection timeouts and you suspect the classifier function may
be the cause. How could you identify the cause? A. Run SQL Profiler B. Connect using Dedicated Admin Connection C. Capture Performance Monitor data D. Reboot the server and see if problem recurs |
The correct answer is B. Connections using Dedicated Admin Connection (DAC) are now exposed to the session classification so a problem with the UDF used for classification will not be experienced by a DAC connection. SQL profiler trace may help, but isn’t the fastest method. There are no performance monitor counters to help with this situation. Rebooting will not prove the cause of the problem.
|
|
|
4. You’re troubleshooting a slow running problem and you’d like to see which indexes, if any, are being used by the slow query. You’re configuring a SQL profiler trace. Which events should you include to see the actual execution plan in XML?
A. Showplan XML Statistics Profile B. SQL:BatchCompleted C. Showplan XML D. RPC:Completed |
Answer A is correct. The Showplan XML Statistics Profile event will capture the actual execution plan. Showplan XML captures the estimated plan and this may be different from the actual plan. Neither option in Answer B or D will capture any plan.
|
|
|
5. Users complain that the database server is slow to respond or causes timeouts, and you’ve been running a server-side trace capturing details of query performance. The server is busy and you’ve generated 10 × 1GB trace files. What’s the best way to find queries with the longest duration?
A. Open each trace file in Profiler and find longest duration. B. Open the trace in Excel and sort by duration. C. Use fn_trace_gettable to load the data into a table, analyze with TSQL queries. D. Run the trace again with a filter. |
The correct answer is C; load the trace data into a table and query this table. Answer A is incorrect; although possible, this will be difficult, time consuming, and error prone, so it isn’t the best option. Excel can’t read SQL trace data natively. No trace filter is available for worst performing queries.
|
|
|
6. You’re responsible for maintenance for a mission-critical database that is 650GB in size, with a particularly large sales order table. The nightly index maintenance scripts are fully optimized but regularly run longer than the maintenance window. How could you resolve this with minimal impact to the business?
A. Delete old data from the sales order table. B. Separate sales data into separate databases, one for each calendar year. C. Don’t carry out index maintenance. D. Implement table partitioning. |
The correct answer is D. Table partitioning is well suited to address this requirement. Options A, B, and C will all impact the business.
|
|
|
7. You’re responsible for managing a database that uses table partitioning for a stock trading firm. Performance on the partition storing most recent trades has started to degrade and you’d like to begin a new partition. Which statement should you use?
A. ALTER PARTION FUNCTION... SPLIT B. ALTER TABLE ... SWITCH C. ALTER TABLE ... SPLIT D. Denormalize the table to 5th Normal Form |
Answer A is correct. The partition function controls the boundary between partitions and will allow a new partition to be created. Alter table switch will transfer data, but won’t change the active partition for inserts. Alter table split is not a valid option. Schema changes are not necessary.
|
|
|
8. When a new SQL Server session is started that meets none of the criteria defined in the Classifier function, to which resource pool is the session assigned?
A. Internal B. Default C. Custom-1 D. None |
The correct answer is B; all nonassigned sessions are executed by resources in the default resource pool. Internal is resource pool for SQL Server internal operations. Answers C and D are also incorrect.
|
|
|
9. You’re the Administrator for a SQL Server used by a number of departments within your organization. At the end of each month the Accounts department runs a number of reports that calculate complex ratios and analytics. During this time other users frequently receive timeouts and poor performance. What’s the best solution to limit the impact of these CPU-intensive reports run by Accounts?
A. Implement table partitioning. B. Separate data and log files. C. Use Resource Governor. D. Request Accounting users run reports only at night. |
The correct answer is C. Use Resource Governor to configure a maximum CPU threshold for Accounting users. Answers A and B are good practice, but won’t necessary limit the impact of accounts’ reports. Answer D is a nice idea, but couldn’t guarantee this would actually happen.
|
|
|
10. You’re a Systems Engineer responsible for SQL Server, and a case had been escalated via the helpdesk where a user reported an exception in an application—the error included deadlock victim. You’d like to implement monitoring to capture details of the deadlocked processes and locks held with minimum overhead. Should you:
A. Start a SQL Profiler Trace. B. Capture Lock\Deadlocks per second with Windows System Monitor. C. Enable trace flag 1222. D. Review the sys.dm_tran_locks DMV. |
The correct answer is C; trace flag 1222 is the most lightweight method to capture the required information. SQL trace would provide this information, however there’s a performance impact when running a trace. Answers B and D are incorrect because neither will provide the required information.
|
|
|
11. A developer is using locking hints within a stored procedure and has some questions about lock escalation. You describe lock escalation as the time when SQL Server:
A. Changes down from table locks to row locks. B. Changes from SHARED to UPDATE locks. C. Changes up from row lock to table locks. D. Changes from an UPDATE lock to EXCLUSIVE lock. |
The correct answer is C. Lock escalation occurs when SQL Server changes many fine-grained locks for fewer coarse grained locks, such as moving from many row locks to a single table lock. All other options are incorrect.
|
|
|
12. You’re working with an external vendor on a patch to their application. They’ve applied the patch to the test environment and would like to simulate some production-like workload to assess the success of the patch. You’ve decided to capture a SQL trace. Which trace template will you use?
A. TSQL_Replay B. Tuning C. Standard (default) D. Capture_test_server |
The correct answer is A. The replay trace template was designed for capturing enough information to replay a trace on another server.
|
|
|
13. You’re responsible for SQL Server in the organization and the IT manager would like to forecast the remaining storage capacity that exists on the disk. Which SQL Server tools could help with capacity planning?
A. SP_spaceused B. Resource Governor C. SQL Profiler D. Performance Data Collector |
Answer D is correct.ccThe performance data collector tracks data and log file size and can help with capacity planning. sp_spaceused shows free space within a file, but doesn’t help in itself with capacity planning. Neither options B or C will help with capacity planning.
|
|
|
14. You’re responsible for SQL Server performance and one of the developers reports INSERTS are particularly slow. You suspect that a previous developer has created some redundant nonclustered indexes and these are hindering INSERT performance. How can you tell if an index is used?
A. Sys.dm_db_index_usage_stats B. Sys.dm_db_index_request_count C. Sys.dm_redundant_indexes D. Sys.dm_db_missing_index_details |
Answer A is correct; index usage stats includes a count of user seeks and user scans, which can be useful to determine whether the optimizer has used an index since last restart. Options B and C are invalid DMVs. Answer D shows suggestions for new indexes to create, does not include details on usefulness of existing indexes.
|
|
|
15. There is blocking occurring on your production SQL Server, you’ve found the session at the head of the blocking chain, and retrieved the SQL_HANDLE. Now you need to find the query executed by the user. Which DMV can help?
A. Sys.dm_exec_requests B. sys.dm_exec_sql_text C. sys.dm_db_query_plans D. sys.dm_stored_proc_text |
The correct answer is B; passing the SQL handle to this DMV will return the text of the actual query executed by the user causing blocking. Answer A can be useful to find the user causing blocking, but not the query text. Neither options C or D are valid DMVs.
|
|
|
16. Which SQL Server versions can be monitored by the Performance Data Collector?
A. SQL Server 2008 B. SQL Server 2005 C. SQL Server 2000 D. SQL Server 2005 Enterprise Edition |
The correct answer is A. The Performance Data Collector only works with SQL Server 2008 instances currently.
|
|
|
17. What’s the best method to run a SQL Trace with minimum overhead on the server being traced?
A. Run SQL Profiler from your desktop. B. Run SQL Profiler from the server. C. Run a server-side trace to a database on the server. D. Run a server-side trace to a file on a fast local disk |
The correct answer is D. The best method to minimize the overhead of running a SQL trace is to output the trace to a file on a fast local disk. Once the trace is stopped, the file can be copied to a workstation or another server for analysis. Answers A, B, and C are all valid options, but none are the best.
|
|
|
18. Which of the following collections could be monitored by the Performance Data Collector?
A. SQLTrace B. T-SQL Queries C. QueryActivity D. Client Response Statistics E. Performance Counters |
The correct answers are A, B, C, and E; all could be included in a custom data collection. Client response statistics could not be collected.
|
|
|
19. Which component of table partitioning determines the boundary between partitions?
A. Partition function B. Partition scheme C. Partition index D. Partition view |
The correct answer is A.The partition function determines the boundary between partitions.
|
|
|
20. Which transaction property ensures changes are still present following a crash or system failure?
A. Atomicity B. Consistency C. Isolation D. Durability |
The correct answer is D. Durability ensures all changes are written to the transaction log before being acknowledged to the application. Once written to the log, SQL Server guarantees durability of committed transactions.
|
|
|
Chapter 14: Implementing Objects
|
xxx
|
|
|
1. You are creating a view named WeeklySales. This view is used to create a sales report that is presented to management at the beginning of each week. You want to ensure that the underlying tables on which this view is based are not accidentally modified causing the report to break. What is the easiest way to implement this?
A. Use a CREATE VIEW WITH CHECK constraint to create a view. B. Use a CREATE VIEW WITH SCHEMABINDING statement to create C. the view. D. Do nothing. When a view is based on a table, the underlying table cannot be modified until the view is dropped. E. Use a DDL trigger to roll back any statement that attempts to modify the table that the view depends on. |
Correct Answer & Explanation: B. Answer B is correct. Using the CREATE VIEW WITH SCHEMABINDING option ensures that any table or column that the view depends on cannot be dropped or modified until the view itself is dropped.
Incorrect Answers & Explanations: A, C, D. Answer A is incorrect because a CHECK constraint validates table data, not structure. Answer C is incorrect because by default a view is not bound to the underlying table structure. Answer A is incorrect because a DDL trigger would be more complicated than using the WITH SCHEMABINDING option. |
|
|
2. You have a view namedYearlySales that lists all sales for the year. The reporting application your organization uses allows you to query theYearlySales view by CustomerName or by OrderDate.You receive unfavorable feedback from users that report generation is painfully slow. What is the best way to optimize report performance?
A. Create indexes on CustomerName and OrderDate columns. B. Create a UNIQUE constraint on CustomerName and OrderDate columns. C. Create a DEFAULT constraint on CustomerName and OrderDate columns. D. Create a full-text index on CustomerName and OrderDate columns. |
Correct Answer & Explanation: A. Answer A is correct because the index creates a lookup structure that maximizes search performance.
Incorrect Answers & Explanations: B, C, D. Answer B is incorrect because a UNIQUE constraint would not improve search performance and also would not allow any customer to place an order twice. Answer C is incorrect because a DEFAULT constraint would not improve search performance. Answer D is incorrect because a full-text index is used with text fields containing a large amount of text data. It cannot be used with dates. |
|
|
3. You have a very large table containing documents stored as a column of varbinary data type. The table is named Documents and is not referenced by FOREIGN KEY constraints. What is the most efficient way of removing all records from this table, while leaving the table ready for inserting new records (select all that apply)?
A. TRUNCATETABLEDocuments B. DELETE Documents B. DELETE FROM Documents C. DROPTABLEDocuments D. Set the Database Recovery Model to Full E. Set the Database Recovery Model to Bulk-Logged |
Correct Answers & Explanation: A, F. The TRUNCATE TABLE statement is the most efficient way of deleting all data from the table, as it is nonlogged. However, the statement is only nonlogged when the database recovery model is set to Bulk-Logged. Incorrect Answers & Explanations: B, C, D, E. Answers B and C are incorrect because they delete each individual row from the table in a logged manner, which is far less efficient than using a nonlogged operation. The two statements are equivalent, as FROM is an optional keyword when used in this way. Answer D is incorrect because the DROP TABLE statement removes the database object as well as the data. Answer E is incorrect because the full recovery model does not offer the performance advantage of Bulk-Logged operations.
|
|
|
4. You have a table named Products, which contains the ProductID, ProductName, Model, and Color columns. The ProductID is marked as IDENTITY. You wish to ensure that there are never two products with the same combination of name, model, and color. What is the easiest way to achieve this?
A. Create a PRIMARY KEY constraint on the ProductName, Model, and Color columns. B. Create a DEFAULT constraint on the ProductName, Model, and Color columns. C. Create a UNIQUE constraint on the ProductName, Model, and Color columns. D. Create a trigger FOR INSERT that checks that there is not already a combination of name, model, and color in the table. |
Correct Answer & Explanation: C. Answer C is correct because a UNIQUE constraint can ensure that the name, model, and color are unique without unnecessary overhead, while allowing ProductID to be the PRIMARY KEY.
Incorrect Answers & Explanations: A, B, D. Answer A is incorrect because the PRIMARY KEY is best created on the ProductID column that contains a surrogate key. Answer B is incorrect because a DEFAULT constraint will not ensure uniqueness. Answer D is incorrect because a UNIQUE constraint is more efficient than a trigger. |
|
|
6. You are tasked with creating a Reseller table, with the Commission column containing the commission percent. When a new reseller is added, the default commission level is 30%.What is the easiest way to implement this rule?
A. Create a FOR INSERT trigger on the Reseller table. B. Create an INSTEAD OF INSERT trigger on the Reseller table. C. Create a DEFAULT constraint on the Commission column. D. Create a CHECK constraint on the Commission column. |
Correct Answer & Explanation: C. Answer C is correct because a DEFAULT constraint inserts a default value into the table when an explicit value is not specified.
Incorrect Answers & Explanations: A, B, D. Answers A and B are incorrect because a DEFAULT constraint is more efficient than a trigger.Answer D is incorrect because the CHECK constraint validates data; it does not supply default values for the column. |
|
|
7. You have a table named EmployeePhoto. This table is not referenced by any FOREIGN KEY constraints. What is the most efficient way of deleting the EmployeePhoto table entirely, including data and structure?
A. TRUNCATETABLEEmployeePhoto B. DELETE EmployeePhoto C. DELETE FROM EmployeePhoto D. DROPTABLEEmployeePhoto |
Correct Answer & Explanation: D. Only the DROP TABLE statement removes the database object as well as the data.
Incorrect Answers & Explanations: A, B, C. Answers A, B, and C are incorrect because they delete the data from the table but do not drop the table object. |
|
|
8. The HR manager has asked you to create a table that will store candidate resumes. These files are created using Microsoft Word 2003 or Microsoft Word 2007, and the HR manager wishes that they are stored in this format. It is a requirement that the HR representatives are able to query the table using search engine style syntax. How should you create the table?
A. CREATETABLEResumes ResumeID int PRIMARY KEY NULL, ResumeFile varbinary(max), FileType varchar(10)) B. CREATE TABLE Resumes (ResumeID int PRIMARY KEY, ResumeFile varbinary(max)) C. CREATETABLEResumes (ResumeID int PRIMARY KEY, ResumeFile varbinary(max), FileType varchar(10)) D. CREATETABLEResumes (ResumeID int UNIQUE NULL, ResumeFile varbinary(max), FileType varchar(10)) |
Correct Answer & Explanation: C. Answer C is correct because in order to create the full-text index you must have a unique key column that cannot hold nulls, and if varbinary is used, you must store the file extension in another column. In order to be able to store binary Word 2003 files, you must use varbinary or image data type for the ResumeFile column.
Incorrect Answers & Explanations: A, B, D. Answer A is incorrect because the key column cannot allow null values. Answer B is incorrect because no file extension column is specified. Answer D is incorrect because the key column cannot allow null values. |
|
|
9. You have a table named TeamMembers containing the data shown in Table 14.3.
Table 14.3 TeamMembers Table MemberName varchar(50) PRIMARY KEY Valentine Hayden Matthew MemberName is the PRIMARY KEY for the table. You execute the following statement: INSERT TeamMembers Values ('Phil'), ('Valentine'), ('Peter') Which team members will be present in the TeamMembers table after the statement executes? A. Valentine,Hayden,Matthew,Phil B. Valentine, Hayden, Matthew, Phil, Peter C. Valentine,Hayden,Matthew,Phil,Valentine,Peter D. Valentine,Hayden,Matthew |
Correct Answer & Explanation: D. DML statements are always treated as a transaction. Either the entire statement succeeds, or the entire statement rolls back. In this case, because inserting the value “Valentine” twice will cause a primary key violation, the entire statement will be rolled back, and no rows will be inserted.
Incorrect Answers & Explanations: A, B, C. Answers A and B are incorrect because they require the INSERT statement to partially succeed. DML statements either commit as a whole or not at all.Answer C is incorrect because it is impossible to insert a duplicate value into a column marked as PRIMARY KEY. |
|
|
10. You have created a Customers table and an NZCustomers view using the following definitions....
CREATE TABLE Customers (CustomerID int IDENTITY PRIMARY KEY, CompanyName varchar(50), Country varchar(50), Notes varchar(max)); GO CREATE VIEW NZCustomers AS SELECT ∗ FROM Customers WHERE Country = 'New Zealand'; GO CREATE TRIGGER Trig_NZCustomers ON dbo.NZCustomers INSTEAD OF INSERT AS BEGIN SELECT CustomerID FROM INSERTED END GO You notice that when you insert data into the NZCustomers view, no error occurs, but the data does not appear in the table.What is the cause of the problem? A. The Trig_NZCustomers must be dropped because it is preventing the insert operation. B. The view cannot be updated because of the WHERE clause. Remove the WHERE clause from the view. C. The IDENTITY column is preventing new CustomerIDs from being gen- erated. Redefine the column not to use the IDENTITY attribute. D. You must explicitly call COMMIT TRANSACTION within Trig_ NZCustomers. |
Correct Answer & Explanation: A. Answer A is correct because the Trig_NZCustomers is defined as INSTEAD OF and therefore is preventing the insert operation.
Incorrect Answers & Explanations: B, C, D. Answer B is incorrect because as long as the view is based on a single table, it is updateable, regardless of the WHERE clause.Answer C is incorrect because the IDENTITY column does not stop update operations in any way.Answer D is incorrect because the transaction within the trigger commits automatically. |
|
|
11. You are adding a Notes column to a table named Customers.You are instructed that the Notes column must always contain a value, even if the value is “No notes available.” Unfortunately, many stored procedures you use to add data to the Customers table omit the Notes column, and it is impractical to redesign the stored procedures.What is the easiest way to ensure that the Notes column in the Customers table always has a value assigned?
A. Define the Notes column as NULL. Create a default constraint on the Notes column with the default value of “No notes available.” B. Define the Notes column as NOT NULL. Create a default constraint on the Notes column with the default value of ‘No notes available’. C. Define the Notes column as NOT NULL. Create a check constraint on the Notes column. D. Define the Notes column as NULL. Create a check constraint on the Notes column. |
Correct Answer & Explanation: B. Answer B is correct because when you define the column as NOT NULL, NULL values will not be allowed in the column. Creating a default will populate the column with default values and ensure that these default values are used when the column is not mentioned in the INSERT statement.
Incorrect Answers & Explanations: A, C, D. Answers A and D are incorrect because the column should not allow NULL values.Answer C is incorrect because you need a DEFAULT constraint, not a check constraint to meet the not null requirements. |
|
|
12. You have a view that displays records from multiple tables within the same database. These tables are managed by different team members. Frequently, your view is inaccessible, when someone modifies the underlying tables. You are instructed by management that the view must work and be accessible at all times. You wish to prevent people from deleting or modifying the tables your view relies on. What is the best way to achieve this?
A. Use an ALTER TABLE WITH SCHEMABINDING statement to update each of the source tables. B. Use an ALTER TABLE WITH CHECK statement to update each of the source tables. C. Use an ALTER VIEW WITH SCHEMABINDING statement to create the view. D. Use an ALTER VIEW WITH CHECK statement to create the view. |
Correct Answer & Explanation: C. Answer C is correct. Using the CREATE VIEW WITH SCHEMABINDING option ensures that any table or column that the view depends on cannot be dropped or modified until the view itself is dropped.
Incorrect Answers & Explanations: A, B, D. Answers A and B are incorrect because the SCHEMABINDING option must be specified on the view, not the source table. Answer D is incorrect because the CHECK constraint validates table data, not table structure. |
|
|
13. You have a large table named CustomerEnquiries. There is a clustered index on the CustomerName column. Unfortunately, your users inform you that it takes an excessively long time to add a new customer to the table. How should you resolve this issue?
A. Use the ALTER INDEX statement with the FILLFACTOR and PAD INDEX options. B. Use the DROP INDEX statement to drop the index. C. Use the ALTER INDEX statement with the NONCLUSTERED option. D. Use the ALTER INDEX statement with the REBUILD option. |
Correct Answer & Explanation: A. Answer A is correct because padding the index will create free space that can be used to insert new rows without rearranging data in the existing table’s pages.
Incorrect Answers & Explanations: B, C, D. Answer B is incorrect because it will reduce the read performance of the table. Answer C is incorrect because it will not attain an adequate performance improvement. Answer D is incorrect because the index must be rebuilt with the FILLFACTOR and PAD INDEX options to improve performance. Without these options the performance will remain the same. |
|
|
14. You have a table named Customers. When a new customer is added, you must check the CreditRating table for the proposed customer’s credit rating. If the credit rating is below a certain value, you must not allow the customer to be added to the Customers table. What is the best way to achieve this?
A. Create a FOR INSERT trigger. B. Create an AFTER INSERT trigger. C. Create an INSTEAD OF INSERT trigger. D. Create a CHECK constraint. |
Correct Answer & Explanation: A. Answer A is correct because a FOR INSERT trigger allows you to roll back the INSERT statement.
Incorrect Answers & Explanations: B, C, D. Answer B is incorrect because an AFTER INSERT trigger occurs once the INSERT operation has been committed and does not allow you to roll the statement back. Answer C is incorrect because an INSTEAD OF trigger occurs in place of the INSERT statement. Answer D is incorrect because a CHECK constraint cannot reference data in other tables. |
|
|
15. You must create a stored procedure that adds a row to the Customers table and returns the ID of the newly inserted customer. Select the statement that will declare the procedure parameters correctly.
A. CREATE PROC AddNewCustomer @ID OUT, @CustomerName varchar(50), @Notes varchar(max) AS... B. CREATE PROC AddNewCustomer @ID int OUT, @CustomerName varchar(50), @Notes varchar(max) AS... C. CREATE PROC AddNewCustomer @ID int, @CustomerName varchar(50) OUT, @Notes varchar(max) OUT AS... D. CREATE PROC AddNewCustomer ID int OUT, CustomerName varchar(50), Notes varchar(max) AS... |
Correct Answer & Explanation: B. Answer B is correct because it declares the @ID parameter as int data type and marks it as OUT.
Incorrect Answers & Explanations: A, C, D. Answer A is incorrect because the @ID parameter has no data type. Answer C is incorrect because the @ID parameter is not declared as OUT. Answer D is incorrect because parameter names must be prefixed with the “@” sign. |
|
|
16. You have a table named Orders that contains the OrderID, CustomerID, Amount, and OrderDate columns. You must retrieve all orders placed in the last seven days. What criteria should you use with the WHERE clause (select all that apply)?
A. WHERE DateDiff(“week”, OrderDate, GETDATE( )) <= 1 B. WHERE DateDiff(“day”, OrderDate, GETDATE( )) <= 7 C. WHERE GetDate(“day”, OrderDate, DATEDIFF( )) <= 7 D. WHERE GetDate(“day”, OrderDate, DATEDIFF( )) >= 7 |
Correct Answers & Explanation: A, B. Answers A and B are correct because the DateDiff function compares the difference, in days or weeks, between the order date and the current date
Incorrect Answers & Explanations: C, D. Answer C is incorrect because the GETDATE function returns the current date and time, not the difference between dates.Answer D is incorrect because it retrieves all orders with order dates that are seven days or more in the future. |
|
|
17. You have a table named Orders. You must make sure that the order date for each order occurs before the ship date. What is the best way to achieve this?
A. Create a CHECK constraint using CHECK (OrderDate > ShipDate) statement. B. Create a CHECK constraint using CHECK (OrderDate < ShipDate) statement. C. Create an INSTEAD OF INSERT, UPDATE trigger. D. Create a FOR INSERT, UPDATE trigger. |
Correct Answer & Explanation: B. Answer B is correct because a check constraint can make sure that the OrderDate is less than (occurs before) the Ship Date. Incorrect Answers & Explanations: A, C, D. Answer A is incorrect because this CHECK constraint checks that the OrderDate is greater than (occurs after) the Ship Date. Answers C and D are incorrect because triggers will not deliver the performance of a CHECK constraint.
|
|
|
18. You always allow 50 Unicode characters for names of people, and you never allow names to be null. You wish to create a user-defined data type that will be used whenever a person’s name must be stored. How should you create the data type?
A. CREATE TYPE PersonName { FROM varchar(50) NOT NULL } B. CREATE TYPE PersonName { FROM nvarchar(50) NOT NULL } C. CREATE TYPE PersonName { FOR varchar(50) NOT NULL } D. CREATE TYPE PersonName { FROM nvarchar(50)} |
Correct Answer & Explanation: B. Answer B is correct because it uses the nvarchar, which allows Unicode characters, limits the length to 50, and declares the type as not NULL.
Incorrect Answers & Explanations: A, C, D. Answer A is incorrect because the varchar data type cannot store Unicode characters. Answer C is incorrect because you must define a data type with the FROM keyword, not the FOR keyword. Answer D is incorrect because by default this data type will allow NULL values. |
|
|
19. In your database system, you must frequently convert from EURO currency to USD currency, using the exchange rate stored in the ExchangeRates table. What is the best way to achieve this?
A. Create a function that accepts an @EURAmount parameter and returns the USD value. B. Create a stored procedure that accepts an @EURAmount parameter and selects the USD value using the SELECT statement. C. Create a view that accepts an @EURAmount parameter and selects the USD value. D. Create a stored procedure that accepts an @EURAmount parameter and returns the USD value using the SELECT statement. |
Correct Answer & Explanation: A. Answer A is correct because the use of a function is best suited to performing a calculation and returning a value.
Incorrect Answers & Explanations: B, C, D. Answers C and D are incorrect because stored procedures should be used to perform actions, not calculations. Answer B is incorrect because views cannot accept parameters. |
|
|
20. You wish to add a new column to an existing view object. What are some of the ways to do this? (Select all that apply)
A. Use Visual Studio object designer. B. Use the SQL Management Studio object designer. C. Use the ALTER VIEW statement. D. Use the ALTER COLUMN statement. |
Correct Answer & Explanation: B, C. Answers B and C are correct because both of these methods can be used to change the view.
Incorrect Answers & Explanations: A, D. Answer A is incorrect because Visual Studio is a development tool, not a database administration tool. Answer D is incorrect because ALTER COLUMN is used with tables, not views. |
