Question 6 :
You want to be sure your database server if properly secured. You have two instances, a default instance and a named instance installed. Which ports do you need to open?
1433 and a specific port after configuring the named instance to use that specific port
Named instances must be configured to use a specific port and that port then opened in the firewall.
Question 7 :
You have a relatively new SQL Server 2005 and msdb is corrupt. How can you fix this database without a restore? (data loss is acceptable) ?
Stop the server and restart it with a trace flag to allow system databases to be detached. Then copy a new version from the /INSTALL folder.
By starting SQL Server with trace flag 3608 you can detach the msdb database. You can copy a new initial version from the installation CD.
Question 8 :
In SQL Server 2005 Integration Services, if you want to import a flat file very quickly that contains only integer data, what type of parsing should you use?
The fast parse mode that is set on columns for a flat file source connection can import a limited set of data types extremely quickly.
Question 9 :
In a new default SQL Server 2005 installation, what is the status of the dedicated administrator connection?
Enabled for local connections only.
The dedicated adminstrator connection is enabled for local connections only by default.
Question 10 :
In the REPEATABLE READ isolation level, what phenomena is still possible?
The answer is Phantoms rows. A phantom row refers to the situation where you execute a DML statement that retrieves data, and another process may add new rows the result set. For example:
SET ISOLATION LEVEL REPEATABLE READ
SELECT * FROM table
We return the following rows:
You are guaranteed to get back at least these rows, and no other user may delete these rows (the rows are locked with a shared lock) This is what is referred to as a repeatable read. However, a user might add another row:
SELECT * FROM table
To prevent phantoms, use the SERIALIZABLE isolation level.