1.You have a custom application that employees use to manage product data. They often search for products based on the product description, so you decide to implement full-text search.
What version of SQL Server can you use to support this? (Choose all that apply.)
A. Express Edition
B. Workgroup Edition
C. Standard Edition
D. Enterprise Edition
2.In the scenario from question 9, how much space will the Vendors table take?
A. 167MB
B. 185KB
C. 200MB
D. 156MB
3. You are the database administrator of your company. One of your company’s applications should maintain data integrity and return custom error messages when the entry data is incorrect.
How can you achieve that with minimum effort?
A. Add check constraints to necessary columns.
B. Use a DDL trigger.
C. Use a CLR trigger.
D. Use a DML trigger.
4. You want to start a distributed transaction using MS DTC. Which statement can you use?
A. BEGIN DISTRIBUTED TRAN
B. BEGIN TRAN
C. SAVE TRAN
D. ROLLBACK TRAN
5. A small automotive parts company has four shops, each with its own inventory database to maintain. The owner wants the shops to be able to share inventory so that employees can pick up a part from another nearby store rather than waiting for a shipment from the manufacturer. To do this, employees at each shop should be able to update their local copy of the inventory database, decrement the other store’s inventory, and then go pick up the part. This way, the other store won’t sell their part because it will have already been taken out of stock.
Which replication model should you use to accomplish this?
A. Central subscriber/multiple publishers
B. Multiple publishers/multiple subscribers
C. Central publisher/central distributor
D. Remote distribution
6. You have created an operator with an email address and a Net Send address. You have also created several alerts for which the new operator should be notified. When any of the alerts fire, the operator gets the email but never receives the Net Send message. What should you do to fix this?
A. Start the Messenger service on the operator’s computer.
B. Start the alerter service on the operator’s computer.
C. Reinstall the SQL Server Agent on the SQL Server.
D. Reconfigure the SQL Server Agent service to log in using the Network Service account.
7. You need to install a new production installation of SQL Server 2005. The server purchased for your installation has four CPUs. Which edition of SQL Server 2005 should you choose to make use of all the CPUs?
A. SQL Server Express Edition
B. SQL Server Workgroup Edition
C. SQL Server Developer Edition
D. SQL Server Standard Edition
8. Which command is used to save the data modified in a transaction so that other connections can see it?
A. DELETE FROM TRANSACTION
B. COMMIT TRANSACTION
C. UPDATE TRANSACTION
D.SELECT TRANSACTION
9. You are a DBA working at a SQL Server hosting company. You need to ensure that none of your company’s client installations wastes disk space. As part of your maintenance tasks, you are charged with periodically shrinking databases.
You want to automatically shrink entire databases at a time. How can you achieve this maintenance goal?
A. Execute the DBCC SHRINKDATABASE statement.
B. Execute the DBCC SHRINKFILE statement.
C. Set each database to shrink automatically by using the ALTER DATABASE statement against each database.
D.You cannot shrink SQL Server 2005 database files automatically.
10. Users are complaining about sometimes being disconnected when using the secondary database of a log shipping configuration. How would you explain this behavior?
A. The secondary server is having performance issues and is losing user connections; you need to increase the secondary server’s hardware resources.
B. This is the default behavior for the No Recovery mode; you need to change the secondary database configuration to Standby mode.
C. This is the default behavior for the Standby mode. Using SSMS, clear the Disconnect Users In The Database When Restoring Backups check box of the log shipping configuration.
D. The primary server is having performance issues and is losing user connections; you need to increase the primary server’s hardware resources.
