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?
- The 3rd Server (which is optional) is called a Witness Server, its main role is for Automatic failover of the Prinicpal and Mirrored Servers.
- 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.
- 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.
- We can monitor the status of the Principal Server and the Mirror Server using Database Mirroring Monitor from SSMS.
- 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.
- 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.
- 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).
- 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.
- 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).
- 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.
- 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).
- Enterprise and Business Intelligence Editions support Master Data Services (as always anything supported in Enterprise Edition is available in Developer Edition).
- 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