(Continued from previous part...)

What level of permissions does a user need to create an assembly with UNSAFE permissions?

Answer
sysadmin

Explanation
Only a sysadmin can create assemblies that are specified as unsafe.

Which optional SQL Server component must you install to ensure that you can create and execute maintenance plans?

Answer
SQL Server Integration Services

Explanation
You must install Integration Services in order to properly create and execute maintance plans since these plans are a subset of SSIS packages.

You have tightly secured access to your SQL Server 2005 server by placing it behind a firewall and limited ports 1433 to those clients that need to directly access the SQL Server. One of your developers complains about connecting to the Integration Services esrvice on your server. What could be the problem if you know the service is working properly?

Answer
The firewall needs to allow port 135 through for Integration Services

Explanation
Integration Services requires access to both DCOM and port 135. You need to open this port to allow the developer to communicate with SSIS.

In SQL Server 2005, which network protocols use endpoints?

Answer
All of the above

Explanation
All communication points with SQL Server using network protocols use an endpoint. SQL Server creates defaults for each protocol that you enable.

You have a SQL Server 2005 cluster and need to add anti-virus software as per your corporate standards. What should you exclude from scans?

Answer
Log and data files for your databases as well as the Quorum drive and the backup folders.

Explanation
Anti-virus programs can exist on the same server as SQL Server without an issue if you exclude certain items. The database data and log files, backup files and folders, the quorum drive for clusters, temporary replication files, the SQL Server log files, and Log Shipping folders/files. You do not need to exclude that SQL Server executables and probably do not want to so that they are protected.

What port do you need to open on your server firewall to enable named pipes connections?

Answer
Port 445

Explanation
Named pipes communicates across TCP port 445.

When the network service built-in account connects to a network resource, what is the context?

Answer
The computer account of the Windows installation.

Explanation
The network service account connects to network resources as the computer account for the Windows installation.

A certificate is like which type of key in SQL Server 2005 encryption?

Answer
An Asymmetric Key

Explanation
A certificate is like an asymmetric key and can validate the identity of the issuer.

How many connections are available in SQL Server MSDE 2000?

Answer
Unlimited, no different from the other SQL Server 2000 editions.

Explanation
The answer is actually an unlimited number of connections, though this in a practical sense is limited by memory on the machine (The actual limitation is 32k connecitons). Instead there is a governer that kicks in to limit things to 8 operations, of which 3 are system operations in some cases. But these are operations, not connections.

What will be the result of the last SELECT statement if you execute the T-SQL script below as a sysadmin.
USE master
GO
IF DB_ID('test_schema') IS NOT NULL
DROP DATABASE test_schema
GO
CREATE DATABASE test_schema
GO
USE test_schema
GO
CREATE SCHEMA Schema1
CREATE TABLE Table1 (col1 int)
GO
CREATE SCHEMA Schema2
GO
CREATE TABLE Table1 (col1 int)
GO
SELECT SCHEMA_NAME(schema_id), name FROM sys.objects
WHERE name = 'Table1'

Answer
Schema1 Table1
dbo Table1

Explanation
The correct answer is A. You can combine the CREATE SCHEMA with a CREATE TABLE. You can even specify GRANTs or DENYs. Search Books online for CREATE SCHEMA.

You are considering consolidating a number of your existing SQL Servers onto a new, large SQL Server 2005 server. You have 3 SQL Server 6.5 servers, 6 SQL Server 7.0 servers, and 16 SQL Server 2005 servers. You cannot make any application changes, but you can size a large SQL Server 2005 server to handle the disk, memory, and CPU needs of the existing servers as well as set compatibility modes. How many of these servers can you consolidate?

Answer
25

Explanation
In SQL Server 2005, you can handle a large number of transactions, which makes it a good choice for consolidating servers. It includes compatibility modes for SQL Server 6, 6.5, 7 and 2000. Therefore you can consolidate all the servers, 25 of them, onto the new server.

To maintain the best performance of your SQL Server 2005 databases, how should you deploy encryption technologies?

Answer
Encrypt data with a symmetric key and secure all keys with asymmetric keys.

Explanation
For performance reasons, you should encrypt data using a symmetric key and secure that key with an asymmetric key.

In SQL Server 2005, which network protocols use endpoints?

Answer
All of the above

Explanation
All communication points with SQL Server using network protocols use an endpoint. SQL Server creates defaults for each protocol that you enable.

You have a SQL Server 2005 cluster and need to add anti-virus software as per your corporate standards. What should you exclude from scans?

Answer
Log and data files for your databases as well as the Quorum drive and the backup folders.

Explanation
Anti-virus programs can exist on the same server as SQL Server without an issue if you exclude certain items. The database data and log files, backup files and folders, the quorum drive for clusters, temporary replication files, the SQL Server log files, and Log Shipping folders/files. You do not need to exclude that SQL Server executables and probably do not want to so that they are protected.

(Continued on next part...)