(Continued from previous part...)

To which versions of SQL Server 2005 can you upgrade an SQL Server 2000 MSDE SP3 edition database?

Answer: Any SQL Server 2005 Edition.
Explanation: You can upgrade to any SQL Server 2005 edition from SQL Server 2000 MSDE. This isn't a great question, so it's being discounted. The database format is the same in all editions, so you can move easily to any SS2K5 version.

You wish to initialize subscribers to your SQL Server 2005 database using FTP. Which versions can you do this with?

Answer: SQL Server 2005 and SQL Server 2000 subscribers only.

Explanation: You can only do this with SQL Server 2005 or SQL Server 2000 subscribers. SQL Server 7 subscribers are not supported through FTP.

You have upgraded your SQL Server 2000 on Windows 2000 server to SQL Server 2005 to take advantage of the new encryption features built into the platform. However not all algorithms are available. Which of the following can you not use?

Answer: AES-256

Explanation: The AES encryption algorithms are not available on Windows 2000 or XP.

What does the DUMP command do?

Answer: Backs up the transaction log.

Explanation: This command is included for backward compatibility, but has be deprecated and will be removed. The BACKUP command is preferred.
It allows for a backup of the database or log.

You have just upgraded your server from a single processor by adding 3 additional processors. Your settings are as follows:
Cost threshold for parallelism 5
Max Degree of Parallelism 8
Affinity Mask 1
Query Cost Governer Limit 0

What should you change to enable queries to run in parallel and finish quicker?

Answer: Change Affinity Mask to 0

Explanation: The affinity mask determines which processors can be used for parallel queries. A setting of 1 limits queries to processor 0 only. By changing this to 0, all processors can be used. You could also set this to 15 for all 4 processors in a 4 processor system to enable the bitmask for bits 0-3.

You want to ensure that your junior DBA properly enables your SQL Server 2005 servers to allow OPENROWSET and OPENDATASOURCE commands so developers can write routines that access various files. What is the best way to have your junior DBA do this?

Answer: Tell your junior DBA to use the SQL Server Surface Area Configuration Tool to enable this feature.

Explanation: The best way to ensure it is properly done is to use the Surface Area Configuration for Features tool. This minimizes the chance a mistake will be made by a junior DBA.

What is the timestamp datatype used for?

Answer: This is a binary number that uniquely marks a row in a database.

Explanation: The timestamp datatype is an automatically generated binary number that is unique within a database. It is used to version stamp rows for changes.

In SQL Server 2005, if you initialize a subscription using FTP, how is the password sent?

Answer: In clear text.

Explanation: The password is sent in clear text, which is why a UNC share on a Windows Authenticated server is recommended.

What rights does a user need to perform a truncate table command SQL Server 2005?

Answer: A member of the DDL_ADMIN role.

Explanation: You need to have db_owner, ddl_admin, sysadmin role membership or be the owner of the table.

You have just installed a 64-bit SQL Server 2005 instance and want to move all your SQL Server 2005 data to this new instance. You wish to migrate your DTS packages, but they do not seem to run. What is the quickest way to get them working?

Answer: Rewrite them using SSIS.

Explanation: There is no 64-bit support for DTS packages. They must be rewritten in SSIS on a 32-bit computer and compiled for 64-bit execution.

(Continued on next part...)