1-New Features in SQL Server 2008-QA

Self Test

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?
A. Use the Surface Area Configuration Tool to secure the installation
B. Use the new Security Analysis tool to secure the installation
C. Use SQL Server Configuration Manager to secure the installation
D. Use Windows Service Manager to enable and disable the appropriate services

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?
A. Use Windows Group Policy
B. Create DDL Triggers
C. Create DML Triggers
D. Create a Declarative Management Framework policy

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?
A. Create a normal backup. Use that to restore to development.
B. Create a Copy Only Backup. Use that to restore to development.
C. Use the Wednesday backup. Restore the transaction log backups since Wednesday.
D. Copy the .mdf and log files and use SP_attach.

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?
A. Restore the 7.0 database on your new server and set the compatibility mode to 7.
B. You must upgrade the database to SQL 2005 or greater.
C. Restore the 7.0 database on your new server and set the compatibility mode to 6.5.
D. Copy the .mdf and log files and use SP_attach.

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 =.

6. Your disk is almost full on the transaction log drive for a database server.
How can you resolve this issue?
A. Use BACKUP LOG WITH TRUNCATE_ONLY
B. Change the mode to simple and shrink the log
C. Reinstall SQL
D. Drop the database and restore from the last backup

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

8. You want to enforce a standard naming convention for tables and stored procedures. Your company has two SQL 2008 Servers and 60 SQL 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

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

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

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

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

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

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

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

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

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

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?
A. View the table properties
B. Enable compression, then check the table size
C. Use sp_estimate_data_compression_savings
D. Use sp_check_compression

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.

20. You have a SQL Server 2008 installation, and you want to create a highavailability solution. What are the ideal approach(es) to solve this problem?
A. Backup and restore
B. Replication
C. Mirroring
D. Clustering


Ref>
ISBN 13: 978-1-59749-420-5