Data and Analytics

Recently I was summoned for a severe production issue where MS DTC failed to come online after a failed attempt of SQL Cluster failover. By the time I logged in, it was already a seriously escalated issue so I was under tremendous pressure to bring everything online immediately. I found many critical errors logged for the failed MS DTC resource and a normal attempt to bring MS DTC online failed. For a quickest fix, I decided to recreate the MSDTC resource after deleting the failed one on the cluster . (Please refer to http://blog.consultdba.com/2010/04/sql-server-ms-dtc-installation-and.html#links if you wish to refer to the steps to configure MS DTC on a cluster). Then I made sure MSDTC security settings in Windows Component Service is fine. At last we had to restart cluster to resolve couple of Distributed transaction errors even after new MS DTC was online. Though it was not an ideal approach, it was a quick and practical resolution with in 5 minutes and everyone was hap

We configure both minimum and maximum limit of CPU and Memory for each RESOURCE POOL in Resource Governor. While we should be very careful to decide on both these limits, did you ever wonder which one is most critical? While there is no direct answer to this, as per me we should be more careful to configure the minimum limit. If we do not guarantee a proper minimum limit for the resource, there is a good chance that resource (which means the applications connecting via the specific Resource Pool) will face a serious degradation of performance. So think very cautiously before deciding both limit for RESOURCE POOL . Especially a wrong configuration of Minimum limit can really hurt the performance of your application. However in reality you should pay equally critical attention to decide both minimum and maximum value.

Join is an important function for TSQL enabling us to retrieve data from two or more tables based on logical relationships between the tables .  Joins are performed by specifying the column from each table to be used for the join and by specifying logical operator defining how the data will be picked up from the table. Joins are categorized as below: Inner joins - Inner joins use a comparison operator to match rows from two tables based on the values in common columns from each table . This is most natural way of joining. Outer joins – This is classified as there sub-categories as below: LEFT JOIN or LEFT OUTER JOIN : The result set of a left outer join includes all the rows from the left table specified in the LEFT OUTER clause , not just the ones in which the joined columns match . When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.   RIGHT JOIN or R

Configuration of MSDTC (Microsoft Distributed Transaction Coordinator) remains a critical step for building a new SQL Server cluster. Although MSDTC can run strictly on a node of a cluster, it is highly recommend running it as a clustered resource. Running MSDTC as a clustered resource ensures high availability of MSDTC in case of a fail-over. After the SQL Cluster is online, first step will be deciding if you want to run MS DTC as an independent cluster group or a part of SQL Server group . This decision can be influenced by many parameters. However in a nut shell, I recommend that if you can accommodate MS DTC log to reside in one of the SQL Server drive (a storage dependent on SQL Cluster), then you can create MS DTC with in the SQL Server group only. This is most recommended way to build MS DTC for a SQL Server cluster. Please refer to http://technet.microsoft.com/en-us/library/cc770748(WS.10).aspx for Requirements for Creating an MS DTC Resource in a Failover Cluster. Now

Rotating a table (ie. To turn the values of a specified column into column names ) is one of the most completed task prior to SQL 2005. From SQL 2005, this complex operation cab be done using PIVOT operator easily. A Pivot Table automatically sort, count, and total the data stored in one table or spreadsheet and create a second table displaying the summarized data .  The PIVOT operator in SQL Server turns the values of a specified column into column names, effectively rotating a table. Let us see below example to understand it clearly and also to get an example of this feature: Consider we have a table named [dbo].[tbl_SalesMaster] as below: If we want to report this data, one of the ideal solution will be to list the Products across the top for each Name and then report the amount sold for each product per person. It should look as below: To complete this, there is no direct way with out using Pivot. (This is actually rotating the table and Making each Product Name as column

This is from my experience of today. While a SQL 2005 EE (64 Bit) on Win 2008 EE Failover Cluster is upgraded to SP3, all the SQL Accounts failed to logon to SQL Server reporting below error: "Login Failed for user ' ', the user is not associated with a trusted SQL Server connection". Root cause of this issue was SP3 setup on cluster changed the SQL Server Authentication mode from Mixed to Windows and hence all SQL Logins started to fail. Hence I simply changed SQL Server authentication mode to SQL Server and Windows (mixed mode) and everything started working. (Please note that this instance is supposed to be on SQL and Windows Mixed mode authentication) Moral of the story: If you experience similar error a production server after a major change, make sure you are using correct authentication mode before diving into advanced troubleshooting. 

There is lot of concern among many people on this issue. The concern of executing DBCC CHECKDB on a database is typically from the fear of severe performance degradation of database/application. Possibility of performance degradation can be classified in below two sub-categories: 1. DBCC CHECKDB creates blocking for other processes: This is simply not true . DBCC CHECKDB does not block anything and solely works on database snapshot. There is no need to be worried about blocking or locking related issues from execution of DBCC CHECKDB if you are running SQL 2005 or later versions (Even in SQL 2000, it will only take occasional SCH _S locks (schema-stability locks) that would only block table scans and table schema modifications) . Unless you explicitly use WITH TABLOCK option (which is not recommended also), there will be no problem if you execute DBCC CHECKDB on a database. Please refer to http://www.sqlskills.com/BLOGS/PAUL/category/Database-Snapshots.aspx if you are inter