Useful Open Source SQL Tools

The following are some useful Open Source tools available to assist with managing and administering a MS SQL Server:

SqlMonitoring Tool
http://sqlmonitoring.codeplex.com/
Also mentioned in http://www.codecapers.com/post/free-scom-alternative-for-sql.aspx

Open DBDiff
http://opendbiff.codeplex.com/

Performance Analysis of Logs (PAL) Tool
http://pal.codeplex.com/

Hopefully others will find them as useful as I have with simplifying my day-to-day DBA tasks.

Client Alias's.... don't forget your 32 bit apps

I have been caught out by this in the past myself and a colleague wasted a great deal of time trying all sorts of things only to find out there was a simple fix to all his connectivity woes.

Since the introduction of the 64-bit operating system we have seen applications become split into 32-bit (x86) and 64-bit (x64) branches. While the introduction of 64-bit was a god send for the application developer and server administrators who had been looking for ways to push more memory to applications to allow them to handle the huge tasks that today's business (and sometimes even the home user) wants to throw at them. The unfortunate down side to this was that 32-bit applications were cast out like the lepers of the Bible days and given their own registry space (ahhh you say, now he gets to the point of this, well all I can say is that it was worth it!!!)

If you don't know 32-bit and 64-bit applications have different registry spaces, because 32-bit applications cannot read anything within 64-bit memory. If you were to look at the registry on a 64-bit operating system it may not look different to the naked eye but if you look closely you will find that there is a new branch HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node and it is here were all the 32-bit applications store their registry settings.

So why is this important to anyone that has anything to do with database connections, because as you would be aware SQL Server allows you to configure Client Alias's within it's Configuration (of the connectivity tools) and it is through the use of these Alias's that we can map a Server Name (or even a made up friendly name) to a FQDN server address and non-standard TCP port. The problem is that on a 64-bit operating system there are both 32-bit and 64-bit Client Alias and depending on what the application you are connecting from is will depend on which type of alias it tries to read.

The times that I have usually come across this as a problem is when an installation package is asking for the SQL Server details of where it will create a database and the user is unaware if they are using a 32-bit or 64-bit application and create the Client Alias in the wrong branch.

So my rule is:
When you setup a Client Alias always set it up in both the 32-bit and 64-bit branches of the SQL Server Configuration Manager.

SQL Failure After Upgrade from VMWare 3.5 to VSphere 4 Fix

Came across this issue today and found this blog so useful I thought I would throw a ping back to it and help others

SQL Failure After Upgrade from VMWare 3.5 to VSphere 4 Fix
http://retr0rob.blogspot.com/2009/09/sql-failure-after-upgrade-from-vmware.html

In short the issue is that after an upgrade of VMWare, SQL may not start and the event log will report that a timeout occurred when trying to connect but the failure happens immediately. If you try and start SQL from the command line (e.g. in single user mode) then you will receive an application pop-up error "This application has failed to start because MSVCP71.dll was not found. Re-installing the application may fix this problem". To fix this issue download the missing MSVCP71.dll (get it here) and copy it to %windir%\system32, then reboot.

NOTE: No warrant is held that this is the solution in all cases but it certainly worked for me, and care should be taken when dealing with system files.

Windows 2008 and xp_logininfo

I've come across an issue which appears to be caused by changes in the security of Windows 2008.

Originally I believed this was related to SQL 2008 however I have replicated the same behavior on SQL 2008 and SQL 2005 when running on a Windows 2008 OS.

Scenario
In an environment where you have user accounts existing in a parent domain (e.g. DOMAIN01) and Computer and Service accounts existing in a Child Domain (e.g. DOMAIN02) you may find that SQL Server running on a computer in the Child Domain will report the following error when you perform specific operations from a SQL Server running either SQL Server 2005 or SQL Server 2008 on a Windows 2008 Operating System.

Msg 15404, Level 16, State 19, Procedure xp_logininfo, Line 62
Could not obtain information about Windows NT group/user 'DOMAIN01\matticus', error code 0x5.

Some operations which may cause this are:

• Attempting to restore a database using a 3rd party application such as Red-Gate SQL Backup
• Creating a schedule for the re-population of a Full Text Catalogue or creating an SQL agent job with the owner set to a domain account within the Parent Domain (e.g. DOMAIN01)
• Using xp_logininfo from within an application / script to check the group membership of a domain user account (e.g. exec xp_logininfo 'DOMAIN01\matticus')
  

This error occurs due to changes in the security of Windows 2008 and specifically changes to the Microsoft Windows API calls used to retrieve information about the Windows domains and the Windows user accounts.

The issue will only occur when SQL Server is attempting to retrieve information regarding Windows Domains and User accounts from the Parent Domain (e.g. DOMAIN01). Queries to AD objects within the same domain (e.g. DOMAIN02) will be successful.


Steps to Reproduce the Issue
The following steps are provided here to reproduce the error within an environment to meets the configuration as stated in the scenario above.

1. Connect to the SQL Server running in DOMAIN02 using SSMS and an account with SYSADMIN rights to the SQL Instance
2. Open a New Query window
3. Run the following TSQL to reproduce the error (replacing the account name with an account within the parent domain)
   
   exec xp_logininfo 'DOMAIN01\matticus'
   
   
4. You should receive the following error:
   
   Msg 15404, Level 16, State 19, Procedure xp_logininfo, Line 62
   Could not obtain information about Windows NT group/user 'DOMAIN01\matticus', error code 0x5.
   
   
5. Now run the following TSQL to demonstrate that querying within the same domain is not affected (replacing the account name with the service account within the child domain)
   
   exec xp_logininfo 'DOMAIN02\sqlserviceacc01'
   
   
6. This should complete successfully and return a result set. The same would occur for any other accounts within the Child Domain.
   
   

Solution 1
At this time the only solution I have found to this problem is to add the service account which the SQL Server service is running on to the "BUILTIN\Windows Authorization Access Group" group within the parent domain. This membership could be inherited through another domain group being added to that BuiltIn group if you have a large number of servers to manage in your organisation.

NOTE: In the past I had found a similar case where membership of the "BUILTIN\Windows Authorization Access Group" group in the parent domain was required. However that was for an issue with the SCOM2007 Service Broker and also SSRS Email Delivery where I believe they had their own methods of querying AD group memberships or had started to implement the changes which MS were planning for Windows 2008 as all servers in that case were running on Windows 2003 OS. These were though due to the accounts for those services being different to the main service account for the related application. That has been the only other case where I have seen this behaviour. I found this article which clearly outlines the SSRS Email Delivery issue.


Reference Material

How to troubleshoot a SQL Server 8198 error
http://support.microsoft.com/kb/834124

xp_logininfo (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms190369.aspx

Some applications and APIs require access to authorization information on account objects
http://support.microsoft.com/kb/331951

xp_logininfo fails for agent account
http://www.sqlservercentral.com/Forums/Topic612567-149-1.aspx

Just a few SQL 2008 links

Just some links relating to SQL 2008 which I have found from fellow bloggers which have some really good information:

Management Studio Improvements in SQL Server 2008
http://www.simple-talk.com/sql/learn-sql-server/management-studio-improvements-in-sql-server-2008/


Activity Monitor – SSMS 2008
http://sql-articles.com/blogs/activity-monitor-ssms-2008/
This is a really good break down of the activity monitor including permissions and the different tabs available.

New Challenge from the guys at "TSQL Challenges"

The guys at "TSQL Challenges" have released Challenge #12
http://beyondrelational.com/blogs/tc/archive/2009/08/09/tsql-challenge-12-build-sequential-ranges-of-dates-with-propagation-to-missing-values.aspx

When is a Backup Considered Successful?

Yesterday while reading the many industry related newsletters I subscribe to I came across a really good article which is essentially a DBA's "Best Practise" guide for ensuring they have backups available when they need them and that they are successful. I was please to find that there wasn't anything written in this article which I was not already aware of and performing as part of my DBA role. Of cause performing test restores for the ~13000 databases my team manage is not very realist.
I also found that this was a good article for not just the DBA but also for the end user to read and therefore gain an understanding of exactly what is involved in a Recovery / Restore request and therefore assist in setting a realistic expectation.

When is a Backup Considered Successful?
By Steven Bouffard, 2009/06/08 @ SQLServerCentral.com
http://www.sqlservercentral.com/articles/Backup+and+Recovery/66703/