SQLServerCentral

In a SQL Server 2008 R2 clustered node, how many instances can you install?

The maximum number of instances on a clustered node is 25 in SQL Server 2008 R2. A non-clustered computer can have 50 instances.

In SQL Server 2008 R2, can you run a full database backup and a filegroup backup at the same time?


Certain operations cannot be run concurrently in SQL Server 2008 R2. A full backup and a file backup must be done at separate times.

Which network protocol is deprecated in SQL Server 2008 R2?

The VIA protocol has been marked as deprecated in SQL Server 2008 R2 and will be removed in a future version. You should not be using this in any future development or migrations.


When you execute DBCC CHECKDB, which of the following DBCC commands are executed?

DBCC CHECKALLOC, DBCC CHECKCATALOG, DBCC CHECKTABLE


Can you disable full-text search on a user database that was created in Microsoft SQL Server 2008 or higher?                 

No,User databases are always enabled for full-text seach post SQL Server 2008. Also, sp_fulltext_database cannot (and should not) be used to enable/disable full-text search.

Which of these commands will find the oldest open transaction?

DBCC OPENTRAN                 


Your SQL Server 2008 instance crashes during disaster recovery, and you need to rebuild the master database. How should you do this?


Use setup.exe with the REBUILDDATABASE action.

         In SQL Server 2000, rebuildm.exe was the recommended method for rebuilding a master database. This was replaced in SQL Server 2005 and 2008 with the REBUILDDATABASE action of setup.exe, although the syntax differs. The Dedicated Administrator Connection (DAC) allows DBAs to connect to a potentially unresponsive instance using SSMS or SQLCMD. If the DAC can connect to the instance the Master database is guaranteed to be available, as the instance will be running.
For more information see: Rebuilding the master database in SQL Server 2000 - http://msdn.microsoft.com/en-us/library/aa213831%28v=sql.80%29.aspx
Rebuilding the master database in SQL Server 2005 - http://msdn.microsoft.com/en-us/library/ms143269%28v=sql.90%29.aspx/a>
Rebuilding the master database in SQL Server 2008 - http://msdn.microsoft.com/en-us/library/dd207003%28v=SQL.100%29.aspx/a>
Using a Dedicated Administrator Connection - http://msdn.microsoft.com/en-us/library/ms189595.aspx/a>

What data is stored in the sys.dm_database_encryption_keys DMV?

The encryption state of all encrypted databases on the instance.