What is the difference between the 2 operating modes of Database Mirroring (mentioned in above answer)?

High-Safety Mode is to ensure that the Principal and Mirrored database are synchronized state, that is the transactions are committed at the same time on both servers to ensure consistency, but there is/might be a time lag

What are the operating modes in which Database Mirroring runs?

Database Mirroring runs in 2 operating modes High-Safety Mode and High-Performance Mode.

Does Transparent Data Encryption provide encryption when transmitting data across network?

No, Transparent Data Encryption (TDE) does not encrypt the data during transfer over a communication channel.

What is Transparent Data Encryption?

Introduced in SQL Server 2008 Transparent Data Encryption (TDE) is a mechanism through which you can protect the SQL Server Database files from unauthorized access through encryption. Also, TDE can protect the database backups of the instance on which TDE was setup.

What are the different types of Upgrades that can be performed in SQL Server?

In-place upgrade and Side-by-Side Upgrade.

What are the different types of database compression introduced in SQL Server 2008?

Row compression and Page compression.

Which autogrowth database setting is good?

Setting an autogrowth in multiples of MB is a better option than setting autogrowth in percentage (%).

What is the difference between dropping a database and taking a database offline?

Drop database deletes the database along with the physical files, it is not possible to bring back the database unless you have a backup of the database. When you take a database offline, you the database is not available for users, it is not deleted physically, it can be brought back online.

When setting Replication, is it possible to have a Publisher as 64 Bit SQL Server and Distributor or Subscribers as a 32 Bit SQL Server.

Yes it is possible to have various configurations in a Replication environment.

When setting Replication, can you have Distributor on SQL Server 2005, Publisher on SQL Server 2008?

No you cannot have a Distributor on a previous version than the Publisher.

What are the different ways you can create Databases in SQL Server?

T-SQL; Create Database command.
 Using Management Studio
 Restoring a database backup
 Copy Database wizard

Due to some maintenance being done, the SQL Server on a failover cluster needs to be brought down. How do you bring the SQL Server down?

In the Cluster Administrator, rick click on the SQL Server Group and from the popup menu item choose Take Offline.

How do you open a Cluster Administrator?

From Start -> Run and type CluAdmin (case insensitive) and the Cluster Administrator console is displayed OR you can also go to Start -> All programs -> Administrative Tools -> Cluster Administrator.

On a Windows Server 2003 Active – Passive failover cluster, how do you find the node which is active?

Using Cluster Administrator, connect to the cluster and select the SQL Server cluster. Once you have selected the SQL Server group, in the right hand side of the console, the column “Owner” gives us the information of the node on which the SQL Server group is currently active.

List out some of the requirements to setup a SQL Server failover cluster.

Virtual network name for the SQL Server, Virtual IP address for SQL Server, IP addresses for the Public Network and Private Network(also referred as Hearbeat) for each node in the failover cluster, shared drives for SQL Server Data and Log files, Quorum Disk and MSDTC Disk.

What is meant by Active – Passive and Active – Active clustering setup?

An Active – Passive cluster is a failover cluster configured in a way that only one cluster node is active at any given time. The other node, called as Passive node is always online but in an idle condition, waiting for a failure of the Active Node, upon which the Passive Node takes over the SQL Server Services and this becomes the Active Node, the previous Active Node now being a Passive Node.
 An Active – Active cluster is a failover cluster configured in a way that both the cluster nodes are active at any given point of time. That is, one Instance of SQL Server is running on each of the nodes always; when one of the nodes has a failure, both the Instances run on the only one node until the failed node is brought up (after fixing the issue that caused the node failure). The instance is then failed over back to its designated node.

What are the differences in Clustering in SQL Server 2005 and 2008 or 2008 R2?

On SQL Server 2005, installing SQL Server failover cluster is a single step process whereas on SQL Server 2008 or above it is a multi-step process. That is, in SQL Server 2005, the Installation process itself installs on all of the nodes (be it 2 nodes or 3 nodes). In 2008 or above this has changed, we would need to install separately on all the nodes. 2 times if it is a 2 node cluster or 3 times in a 3 node cluster and so on…

What are the different Authentication modes in SQL Server and how can you change authentication mode?

SQL Server has 2 Authentication modes; Windows Authentication and SQL Server and Windows Authentication mode also referred as Mixed Mode.

If you are given access to a SQL Server, how do you find if the SQL Instance is a named instance or a default instance?

I would go to the SQL Server Configuration Manager. In the left pane of the tool, I would select SQL Server Services, the right side pane displays all of the SQL Server Services / components that are installed on that machine. If the Service is displayed as (MSSQLSERVER), then it indicates it is a default instance, else there will be the Instance name displayed.

What the different Topologies in which Replication can be configured?

Replication can be configured in any topology depending keeping in view of the complexity and the workload of the entire Replication. It can be any of the following:
 Publisher, Distributor and Subscriber on the same SQL Instance.
 Publisher and Distributor on the same SQL Instance and Subscriber on a separate Instance. Publisher, Distributor and Subscriber on individual SQL Instances.