SQL Server DBA Q and A Page – 2

The following Question and Answers were added on Dec 14th 2011

41. How many Mirrored Servers can be setup in a Database Mirroring setup?

  • Only 1 Mirrored Server can be configured in a Database Mirroring setup.

42. Can there be a 3rd Server take part in Database Mirroring? 

  • Yes.
43. What is that Server called and what is the role of that Server?
  • The 3rd Server (which is optional) is called a Witness Server, its main role is for Automatic failover of the Prinicpal and Mirrored Servers.
The following Question and Answers were added on Dec 24th 2011
44. What are Dynamic Management Objects and how are they useful?
  • DMOs provide Server wide and Database level information on the SQL Server. DMOs include Dynamic Management Views and Dynamic Management Functions. Together, these DMOs provides internal information about the SQL Server with respect to Performance, I/O, Security, Indexes, Database Mirroring, Replication and many more. This information can be used to monitor the SQL Server and troubleshooting.
45. In what editions is Backup Compression available in SQL Server 2008 and 2008 R2?
  • in SQL Server 2008, Backup Compression is supported in Enterprise Edition only (apart from Developer and Eval Editions). In SQL Server 2008 R2, the feature is available in Datacenter Edition, Enterprise Edition and Standard Edition.
The following Question and Answers were added on Jan 2nd 2012. The questions (46-48) have been submitted by Venkat M.
46. How will you know that data is copied to mirror Server in Database Mirroring?
  • We can monitor the status of the Principal Server and the Mirror Server using Database Mirroring Monitor from SSMS.
47. What is the difference between Table Scan and Index Scan?
  • A Table Scan is performed when the table has no Indexes OR the existing Index(es) are not beneficial to fulfill the request in the query. Generally, for a large table, Table Scans are slower. 
  • Index Scans are usually faster than Table Scans as the Index access reduces the I/O operations. 
48. List some of the Important DMV’s used in day to day work.
  • sys.dm_db_index_usage_stats
  • sys.dm_db_missing_index_details
  • sys.dm_exec_query_stats
  • sys.dm_os_wait_stats
  • sys.dm_tran_locks
49. What is  a Trace flag?
  • A Trace flags are used to change certain characteristics of the SQL Server. Trace flags can be used to switch off or enable certain feature, for troubleshooting purpose, or to diagnose performance issues such as deadlock monitoring. 
50. How do you enable Trace flag globally?
  • Trace flags can be set permanently using -T trace flag # as a start-up parameter or temporarily by running a command DBCC TRACEON (xxxx,-1).
The following Question and Answers were added recently (on Jun 19th 2012).
51. How is Installation of SQL Server 2008 / 2008 R2 different on a failover cluster, compared to SQL Server 2000/ 2005 on a failover cluster?
  • Prior to SQL Server 2008, installing SQL Server on a failover cluster was a one time task, that is, when you install SQL Server on one of the nodes, the binaries are installed on all of the nodes. Whereas starting from SQL Server 2008, you have to manually install SQL Server on all of the nodes participating in the failover cluster.
52. What are the ways you can find blocking and dead locks on a SQL Server?
  • You can use SQL Profiler (filtering only those events to find deadlocks), use System catolog view sysprocesses (there is a blocked column) and finally from Server based reports (Right click on the Server, from the pop-up menu choose Reports and then Standard Reports, choose Activity – All Blocking Transactions).
53. What is Surface Area Configuration in SQL Server, and how is it different in SQL Server 2005 and beyond?
  • SQL Server by default enables only those necessary features / services or Server Options in order to reduce the risks of malicious attacks. In SQL Server 2005, Surface Area Configuration was a tool available under Configuration Tools program menu of SQL Server. Starting from SQL Server 2008, this tool is no longer available, but the same features/ services can be enabled / disabled by right clicking the SQL Server (in Object Explorer, in SSMS) and choosing Facets and selecting Surface Area Configuration Facet.
The following Question and Answers were added recently (on Sep 12th 2012).
54. Can a SQL login be created with a blank password in a SQL Server?
  • Yes, you can create a SQL login with a blank password. This might be surprising, but it is true..

The following Question and Answers were added recently (on Sep 26th 2012).

55. Which Editions of SQL Server 2012 support Master Data Services?
  • Enterprise and Business Intelligence Editions support Master Data Services (as always anything supported in Enterprise Edition is available in Developer Edition).
56. What does it mean by minimum and recommended hardware for making sure the machine is good to install SQL Server?
  • The minimum hardware is the bare minimum you can have on a machine to install that version of SQL Server, the drawback of having a minimum hardware is poor performance, on the other hand, recommended hardware is required to give a decent performance for the SQL Server standpoint.

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

- Bru Medishetty

  • Asdfasdf

    Thanks very useful

  • Amar

    How would you troubleshoot a slow running query/slow running server?

  • kamaraj

    Very nice Site Medishetty Garu !!!