Administering Microsoft SQL Server 2012 Interview Questions - Part 1 of 2 (On Comprehensions from TK 70-462)

Question 1

What is the main difference between scaling up and scaling out? Answer.

Scaling up involves increasing capacity by upgrading the current hardware to more powerful hardware. Scaling out involves increasing capacity by deploying additional servers.

Hide the Answer.

Question 2

Which command do you use to remove a feature from SQL Server 2012 when it is installed on a Server Core version of Windows Server 2008 R2? Answer.

You use the Setup.exe command with the /Action=Uninstall option to remove a feature from SQL Server 2012 when it is installed on a Server Core version of Windows Server 2008 R2.

Hide the Answer.

Question 3

Which system database must you modify if you want all future databases to use the Full recovery model? Answer.

You must modify the model database because all new databases created on an instance inherit settings from the model database.

Hide the Answer.

Question 4

You must modify the model database because all new databases created on an instance inherit settings from the model database. Answer.

You can install a maximum of 25 instances of SQL Server 2012 on a failover cluster.

Hide the Answer.

Question 5

Which mode should you select during the installation of Analysis Services if you want to support OLAP databases? Answer.

You should install Analysis Services in multidimensional and data mining mode if you want to use OLAP databases.

Hide the Answer.

Question 6

After the appropriate keys and certificates are created, which Transact-SQL statement do you use to encrypt a database? Answer.

Use ALTER DATABASE with the SET ENCRYPTION ON option.

Hide the Answer.

Question 7

Which stored procedure should you call from a Transact-SQL statement if you want to detach a database from a SQL Server 2012 Database Engine instance? Answer.

You call the sp_detach_db stored procedure from a Transact-SQL statement when you want to detach a database from a SQL Server 2012 Database Engine instance.

Hide the Answer.

Question 8

Which statement or function would you use if you wanted to import data into a table from a remote data file through an OLE DB provider? Answer.

You can use the OPENROWSET(BULK) function to import data into a table by connecting to a remote data source through an OLE DB provider.

Hide the Answer.

Question 9

Which security principals can you use with a SQL Server login that uses Windows authentication? Answer.

You can use a local user account, a local security group, a domain user account, or a domain security group as the basis for a Windows-authenticated SQL Server login.

Hide the Answer.

Question 10

What is the maximum number of database users that can exist in a database for a specific SQL Server login? Answer.

You can have only one database user mapped to a SQL Server login in any specific database.

Hide the Answer.

Question 11

Which Transact-SQL statement do you use to remove a DENY permission from a securable? Answer.

You use the REVOKE permission to remove a DENY permission from a securable.

Hide the Answer.

Question 12

Which Transact-SQL statement can you use to unlock a locked-out login? Answer.

You can use the ALTER LOGIN Transact-SQL statement with the UNLOCK parameter to unlock a locked SQL Server–authenticated login.

Hide the Answer.

Question 13

What must you create before creating a server audit specification? Answer.

You must create a server audit before creating a server audit specification.

Hide the Answer.

Question 14

What recovery model must a database be set to use before you can mirror it? Answer.

A database must be set to use the full recovery model before you can mirror it.

Hide the Answer.

Question 15

Which type of replication would you configure if you had to allow updates from multiple sites with conflict resolution? Answer.

Merge replication offers updates from multiple sites and has a conflict resolution mechanism.

Hide the Answer.

Question 16

Which editions of SQL Server 2012 support more than two-node failover clusters? Answer.

Only SQL Server 2012 Enterprise edition supports more than two-node failover clusters.

Hide the Answer.

Question 17

Which availability mode is more suitable when replicas are located in geographically dispersed sites? Answer.

Asynchronous-commit mode is more suitable for availability replicas distributed over geographically dispersed topologies.

Hide the Answer.

Question 18

Which index type stores the actual table data at the leaf-level data page? Answer.

A clustered index stores the actual data at the leaf-level data page.

Hide the Answer.

Question 19

If an index contains a column with either a text or ntext data type, can you perform an online index rebuild? Answer.

Yes. In SQL Server 2012, only XML and spatial data types are excluded from online index rebuild operations.

Hide the Answer.

Question 20

Which feature do snapshot isolation levels use? Answer.

Snapshot isolation levels use row versioning in tempdb to provide consistent rows for reads while writes are occurring.

Hide the Answer.

Question 21

Of which fixed server role must the SQL Server Agent service account be a member? Answer.

The SQL Server Agent service account must be a member of the sysadmin fixed server role.(????). See Implement SQL Server Agent Security

Hide the Answer.

Question 22

Which recovery models enable you to take transaction log backups? Answer.

The full and bulk-logged recovery models enable you to take transaction log backups.

Hide the Answer.

Question 23

If the database that uses the full recovery model is currently running, what step should you take prior to attempting a restore operation? Answer.

You should perform a tail-log backup prior to attempting a restore operation.

Hide the Answer.