Set - 6

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?

Answer :

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) ?

Answer :

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?

Answer :

Fast Parse
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?

Answer :

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?

Answer :

Phantom rows
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
BEGIN TRANSACTION
SELECT * FROM table
We return the following rows:
ColumnName
-----------
row1
row2

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
ColumnName
-----------
row1
row2
row3

To prevent phantoms, use the SERIALIZABLE isolation level.