Basics of SQL Server Database Instant File Initialization

SQLServerF1

Data and log files are initialized to overwrite any existing data left on the disk from previously deleted files. Data and log files are first initialized by filling the files with zeros when you perform one of the operations like Create a database, Add files, log or data, to an existing database, Increase the size of an existing file (including autogrow operations), Restore a database or filegroup. File initialization causes these operations to take longer. However, when data is written to the files for the first time, the operating system does not have to fill the files with zeros.

In SQL Server, data files can be initialized instantaneously. This allows for fast execution of the previously mentioned file operations. Instant file initialization reclaims used disk space without filling that space with zeros. Instead, disk content is overwritten as new data is written to the files. Log files cannot be initialized instantaneously.

How to Enable Database Instant File Initialization in SQL Server?
Instant file initialization is only available if the SQL Server (MSSQLSERVER) service account has been granted SE_MANAGE_VOLUME_NAME. Members of the Windows Administrator group have this right and can grant it to other users by adding them to the Perform Volume Maintenance Tasks security policy. Important point to note regarding instant file initialization is that Instant file initialization is not available when TDE is enabled.

To grant an account the Perform volume maintenance tasks permission:
On the computer where the backup file will be created, open the Local Security Policy application (secpol.msc).
In the left pane, expand Local Policies, and then click User Rights Assignment.
In the right pane, double-click Perform volume maintenance tasks.
Click Add User or Group and add any user accounts that are used for backups.
Click Apply, and then close all Local Security Policy dialog boxes.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Suspect Database Issue Due to MSDTC Orphan Transaction

SQLServerF1

It is not strange to see different kinds of issues after restart of SQL Server instance or reboot of a server or after failover of cluster SQL Server instances. Below is one of the issue you may encounter due to MSDTC service orphan transaction causing the database recovery to fail after a restart of the server. Below are the errors you may see in the SQL Server errorlog.

Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
The Microsoft Distributed Transaction Coordinator (MS DTC) service could not be contacted. If you would like distributed transaction functionality, please start this service.
Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
The Microsoft Distributed Transaction Coordinator (MS DTC) service could not be contacted. If you would like distributed transaction functionality, please start this service.
Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
The Microsoft Distributed Transaction Coordinator (MS DTC) service could not be contacted. If you would like distributed transaction functionality, please start this service.
SQL Server detected a DTC/KTM in-doubt transaction with UOW {995D82F2-3C9B-4249-C827-B33FC42BAD82}.Please resolve it following the guideline for Troubleshooting DTC Transactions.
Error: 3437, Severity: 21, State: 3.
An error occurred while recovering database ‘TestDB’. Unable to connect to Microsoft Distributed Transaction Coordinator (MS DTC) to check the completion status of transaction (0:-1647710395). Fix MS DTC, and run recovery again.
Error: 3414, Severity: 21, State: 2.
An error occurred during recovery, preventing the database ‘TestDB’ (database ID 6) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.

– If you run query against sys.sysdatabases, you will see that the status of the database is shows as suspect. Also suspect status is shown for the database under SSMS GUI.
– This issue happens after restart of server and SQL Server is trying to recover a database which has a incomplete transaction related to linked server or a remote query which uses MSDTC.
– Once the server is rebooted, SQL Server started and tried to start and recover all databases as usual and it found a open transaction in TestDB which was a linked server query using MSDTC, but the MSDTC service was not yet started resulting in failure to recover the transaction.
– To resolve the issue, DBA has to start the MSDTC service, if it is not yet started.
– If MSDTC service has started later, then DBA has to recover and bring the database online by running the below query
Alter database DBName SET ONLINE

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Thanks,

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

SQL Server Agent Fails with Error The remote procedure call failed

SQLServerF1

SQL Server Agent is one of the important feature which allows DBAs to Create and Schedule jobs to perform regular maintenance or run application related jobs regularly. There are many other options provided by SQL Server Agent which include Creating Alerts and Operators, and sending database mails from Agent jobs or when the jobs fail, etc. SQL Server Agent service gets installed along with the SQL Server installation. A separate windows service gets added for SQL Server Agent and it is dependent on SQL Server Services. When the SQL Server Agent Service starts, a windows process is created for the SQL Server Agent and handles job execution, etc.

Although SQL Server Agent functions smoothly, sometimes it can fail due to various reasons. One of the error which DBAs may observed is the below mentioned error when you try to enable the SQL Server Agent service which is in disabled state. Most often or no this happens due to installation of any visual studio components which affects the files related to SQL Server.

WMI Provider Error
The remote procedure call failed.[0x800706be]

The above error can occur due to various reasons including some known issues. Below are some of the options which you can try to fix this error and then start the SQL Server Agent Service.

– Always use SQL Server Configuration Manager to make any changes to the SQL Server or SQL Agent services.

– Launch SQL Server Configuration Manager by “Run-as-Administrator”

– If the SQL Server Agent Service is in disabled state, then enable the service and then try to start the service. If you get the same error when you try to change it from disabled to enabled then try changing it from services.msc and after that come back to SQL Server configuration manager and then attempt to start the SQL Server Agent service.

– Apply latest Service Pack available for the SQL Server instance. Example, if SQL Server is of version SQL Server 2008 R2 SP1, then install SP2 which should fix any problems.

– If already the SQL Server is on latest version or latest service pack, then perform repair of the SQL Server instance using the SQL Server media.

– Try to start the SQL Server Agent service from command line using below command. Make sure command prompt is launched using “Run-as-Administrator”

SQLAGENT90 -C -V > C:\SQLAGENT.OUT

– If there are multiple instances of SQL Server or related components installed on the same server, then use the configuration manager of the latest version. Example: If there are SQL Server 2008 R2 and SQL Server 2012 components installed on the server, then use the SQL Server 2012 configuration manager.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Decoding SQL Server Startup Process and Checking for Errors

There are various tasks that take place when SQL Server is started in a particular sequence. If there are any failures, it will be reported in the SQL Server Errorlog. In this article let’s try to understand what SQL Server does while it is starting up. There are many internal tasks which SQL Server performs, but will not be covered here as our focus is to understand what SQL Server is doing for a general user perspective by decoding the SQL Server Errorlog file.

Below is the snipped of SQL Server 2008 R2 errorlog, broken down into sections to understand what action is being performed.

At the very start of SQL Server we can find the details about SQL Server Version, Edition, Platform and Operating System related details.
Server Microsoft SQL Server 2008 R2 (RTM) – 10.50.1600.1 (X64)
Apr 25 2011 16:43:45
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7600: ) (VM)
Server (c) Microsoft Corporation.
Server All rights reserved.

Next, we can find the process ID of SQLServr.exe process for this SQL Server instance. If it is a VMware system, we can find those details too

Server Server process ID is 566.
Server System Manufacturer: ‘VMware, Inc.’, System Model: ‘VMware Virtual Platform’.

Below shows us which Authentication Mode is SQL Server instance currently configured to allow connections. Mixed means, it allows both Windows and SQL Server Authentication.

Server Authentication mode is MIXED.

Next, we can find the location of the SQL Server Errorlog file where informational, Warning and Error Messages of SQL Server are logged.

Server Logging SQL Server messages in file ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG’.

We can next fine, what the the process ID of SQL Server used last time before the restart of SQL Server. Next, we can see SQL Server reads the registry details and identified the path of Master database .mdf and .ldf files locations.

Server This instance of SQL Server last reported using a process ID of 1844 at 5/21/2012 5:23:23 AM (local). This is an informational message only; no user action is required.
2011-03-28 19:13:01.850 Server Registry startup parameters:
-d C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf
-e C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG
-l C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

The checks for the priority with which SQL Server should start and logs the same. Then there will be additional details identified like number of CPUs on the server detected by the SQL Server. It then checks if lock pages in memory privilege has been enabled for the SQL Server service account or not and logs if it is enabled. If any configuration to default lock settings was changed, it will read and shows the same.

Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
Server Detected 4 CPUs. This is an informational message; no user action is required.
Server Using locked pages for buffer pool.
Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
Server Node configuration: node 0: CPU mask: 0x000000000000000f:0 Active CPU mask: 0x000000000000000f:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.

Next, SQL Server will start the Master database and then performs recovery the master database. If it fails in this process, it will be logged here and SQL Services will be stopped.

spid5s Starting up database ‘master’.
spid5s Recovery is writing a checkpoint in database ‘master’ (1). This is an informational message only. No user action is required.

Some additional details related to additional features usages is logged as shows below it shows information related to Resource Governor, SQL Server Audits, FileStream, etc.

spid5s Resource governor reconfiguration succeeded.
spid5s SQL Server Audit is starting the audits. This is an informational message. No user action is required.
spid5s SQL Server Audit has started the audits. This is an informational message. No user action is required.
FILESTREAM: effective level = 0, configured level = 0, file system access share name = ‘MSSQLSERVER’.

If default trace configuration option is enabled, then it will start the same. Then it starts the internal hidden database Resource Database. We can see its version from here.

spid5s SQL Trace ID 1 was started by login “sa”.
spid5s Starting up database ‘mssqlsystemresource’.
spid5s The resource database build version is 10.50.1600. This is an informational message only. No user action is required.

We can notice the physical SQL Server server name. On cluster server, we can see the NetBios Name followed after this.

spid5s Server name is ‘Test_Server’. This is an informational message only. No user action is required.

Next Model database is started and same will be logged in the errorlog.

spid10s Starting up database ‘model’.

Additional information related to any SSL Certificates used by SQL Server or is it using the self-generated certificate is logged. Any failure using the SSL certificate will be logged here and then SQL Server will stop.

Server A self-generated certificate was successfully loaded for encryption.

SQL Server will check for the TCP/IP Port and Named Pipe details and same will be logged. Any failure using a particular port will be logged here.

Server Server is listening on [ ‘any’ 1433].
Server Server is listening on [ ‘any’ 1433].
Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
Server Server named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ].
Server Server is listening on [ ::1 1434].
Server Server is listening on [ 127.0.0.1 1434].
Server Dedicated admin connection support was established for listening locally on port 1434.

TempDB is restarted each time, SQL Server starts, so we will see messages related to tempdb cleaning and then starting.

spid10s Clearing tempdb database.
spid10s Starting up database ‘tempdb’.

Next, it will check for SPN settings and try to setup SPN, if SQL Service account has enough permissions to create. If it fails, it will be reported and NTLM authentication will be used instead of kerberos.

Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This i
Server SQL Server is now ready for client connections. This is an informational message; no user action is required.

Next, SQL Server starts and recovers all the user databases which are Online. Any failures starting a particular database will be logged here.

spid21s Starting up database ‘ReportServer’.
spid23s Starting up database ‘ReportServerTempDB’.
spid13s Starting up database ‘msdb’.
spid22s Starting up database ‘test_db’.

SQL Server startup completes here and any further messages logged will be related to activities performed on SQL Server which are configured to be logged in SQL Server Errorlog like, Full Backup and Log backup completion informational message, backup failure message, Login Failed Messages, any other warnings or errors.

Looking through the SQL Server Errorlog carefully will let us identify any problems causing SQL Server failure during startup or when it is stopped or restarted unexpectedly.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

SQL Server Setup or Installation Failure While Trying to Start SQL Server Services

New installation of Microsoft SQL Server 2012 standalone Express Edition instance Failed with error “Could not find the Database Engine startup handle” “Error Code 0x851A0019”. Below is the error snippet from Setup Summary log file.

Detailed results:
Feature: Database Engine Services
Status: Failed: see logs for details
Reason for failure: An error occurred during the setup process of the feature.
Next Step: Use the following information to resolve the error, uninstall this feature, and then run the setup process again.
Component name: SQL Server Database Engine Services Instance Features
Component error code: 0x851A0019
Error description: Could not find the Database Engine startup handle.

Setup failed at the very end while it was trying to bring the SQL Server services online.
Upon checking the SQL Server Errorlog, found below errors

Error: 5178, Severity: 16, State: 1.
Cannot use file 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Template Data\master.mdf' because it was originally formatted with sector size 4096 and is now on a volume with sector size 1048576. Move the file to a volume with a sector size that is the same as or smaller than the original sector size.

Above error message points towards some issue with the DISK or SAN drive, so check with the storage admin. There are issues resolved by updating the Firmware and drivers of the Hardware provider. Also, there are few known issues at Operating System level, so apply all latest fixes for the Operating System. Additionally make sure disk is formatted with NTFS file format, but not as FAT32. Make sure that the disk/folders are not read-only, mapped, or compressed drives.

Windows 7 and Windows Server 2008 R2
– Install Service Pack 1 (SP1), or install the update that is described in the following article in the Microsoft Knowledge Base:
982018 An update that improves the compatibility of Windows 7 and Windows Server 2008 R2 with Advanced Format Disks is available
– Make sure that the drivers and firmware for your storage controller and other hardware components are updated. Also, make sure that the drives and firmware support large-sector drives.

Windows Vista and Windows Server 2008
– Install the hotfix that is described in the following article in the Microsoft Knowledge Base:
2553708 A hotfix rollup that improves Windows Vista and Windows Server 2008 compatibility with Advanced Format disks
– Make sure that the drivers and firmware for your storage controller and other hardware components are updated. Also, make sure that the drivers and firmware support large-sector drives.

References:
http://support.microsoft.com/kb/2510009
http://support2.microsoft.com/kb/926930

If you could not find the SQL Server Errorlog as mentioned above, then try the below general steps to see if that is helpful in resolving the issue or making some progress.

– Make sure to uninstall the failed SQL Server instance completely using add remove programs.

– Make sure you remove all file sand folders related to SQL Server from all the drives.

– Run the setup.exe by selecting “run as administrator”

– During installation when you are selecting SQL Server service accounts from account selection page, instead of using default service accounts, use domain account ot Local system account.

This is applicable on below versions of SQL Server

SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

What Happens to the Connections When we Move or Failover SQL Server Group to Another Node

It is common to see many people ask this question, What happens to the connections from Application to SQL Server, when we move or failover SQL Server Group from one node to another Node?

If we understand what happens when we Failover or Move SQL cluster instance from one node to other, we will get the answer to the question.

We we initiate a failover, All the resources in that group which include(SQL Server resource, SQL Server Agent resource, Multiple Disk Resources, Network Name and IP Address resource, other third party resources like backup, fileshare resources) will be taken OFFLINE on that node, then these resources are moved to the new node and then all these resources are brought ONLINE. Once all the resources are ONLINE, we can make connections to the SQL Server.

As we can see from the above process, SQL Server will be stopped, which means that any existing connections will fail, it may be possible that these failed transactions already would have modified some data in the database, but as per design of SQL Server engine, this ingormation is also logged into SQL Server Transaction log file(.ldf). When SQL Server starts on new node, it will read the transaction log file to see which transactions were committed and which were not, based on that it will rollback any incomplete work done by uncommitted transactions.

Hope this answers the question.

Happy Reading,
SQLServerF1 Team

 

SQL Server 2005 Instance crashes while starting

Recently I was worked on a case wherein we were unable to start the SQL Server 2005 instance as it was failing with the below error:

Faulting application sqlservr.exe, version 2005.90.5057.0, time stamp 0x4d8d381e, faulting module kernel32.dll, version 6.0.6002.18449, time stamp 0x4da47b2f, exception code 0xc06d007e, fault offset 0x00000000000170cd, process id 0xe34, application start time 0x01ccd9ea6d1b2185.

Errorlog file was not getting created and the above error was seen in the event viewer application log. Initially, it looked to be some executable or binary was corrupt. Ideal solution for these kinds of issues is to uninstall & re-install the SQL instance after taking backup of all the database files.

As no one likes the option of un-install & reinstall of SQL instance, I kept that as a last resort and decided to try out other options to resolve this issue. As a first step I tried to start from command line but it was crashing and in the event viewer I saw the same error. Then I tried starting SQL Server in a single user mode with -c -m -f but still it was crashing.

As some of the SQL Server 2005 binaries seem to have been corrupted, I decided to rebuild the SQL Server binaries files & registry.

To set correct expectations, below option may or may not resolve the issue always, so before trying the below action plan, make sure to take the backup of all you database files and keep them in a safe location.

I ran the below command to rebuilt SQL specific files and registries which completed successfully after which I was able to start SQL Server 2005 instance:

start /wait setup.exe /qb INSTANCENAME=Instance_Name REINSTALL=ALL REINSTALLMODE=omus

Rebuilding the Registry
To rebuild the SQL Server 2005 registry, you must use the Setup command with the REINSTALL=ALL and REINSTALLMODE=omus parameters. Running Setup.exe with these parameter settings rebuild, verify, and repair a SQL Server instance and rebuild the registry. This procedure is most often used to rebuild the registry for a damaged SQL Server installation.
Note:
To rebuild the registry, you must use the same package file and options that you specified during the initial installation. If you do not know this information, uninstall and then reinstall SQL Server, rather than rebuild the registry.
Your syntax must specify either the /qb or /qn option. The /qb option displays basic Setup user interface and error message dialog boxes. If the /qn option is specified, all Setup dialog boxes, including error messages, are written to Setup log files.
Important:
In order to rebuild system databases, a new sa password is REQUIRED. Do not use a blank password. Use a strong password.
To repair all files, rebuild the registry, and replace all SQL Server shortcuts from the command prompt
1. Insert the SQL Server 2005 installation media into the disk drive.
2. Run the following command from the command prompt:
start /wait \setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=ALL REBUILDDATABASE=1 REINSTALLMODE=omus SAPWD=
For INSTANCENAME, use MSSQLSERVER for a default instance. For a named instance, specify the instance name.
Important:
The resource database (mssqlsystemresource) is restored from SQL Server 2005 distribution media. When you rebuild the resource database from distribution media, all Service Packs and QFE updates are lost, and therefore must be reapplied. Before you proceed, see the [REBUILDDATABASE] section more information.

Happy Reading,
SQLServerF1 Team

 

Script upgrade may fail when Database snapshot is created on subscriber database

Consider a scenario where you are applying Service Pack or Cumulative Update for SQL Server instance and after which the SQL Server instance does not start. Below errors are logged in SQL Server errorlogs.

Error: 3906, Severity: 16, State: 1.
Failed to update database "DBSnapshot_20120512" because the database is read-only.

Error: 912, Severity: 21, State: 2.
Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 3906, state 1, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

As the SP or CU scripts are trying to upgrade the snapshot database which is a read-only database the upgrade script was failing. Below workaround should help you with starting the SQL Server service.

Start the SQL Server service from command line using trace flag -T902 which will by-pass running upgrade scripts

Sqlservr.exe -s Instance_Name -T902 -T3608

From another command prompt connect to the SQL instance

Sqlcmd –S Server_Name\Instance_Name

Drop the snapshot database and stop the SQL Service which was started from command prompt.

Start the SQL Service normally and it should start fine and you should be able to see from SQL Errorlog that the upgrade scripts completed successfully.

The reason for failure was because the parent database for database snapshot was also a subscriber in replication setup.

Within the upgrade script procedure “master.sys.sp_vupgrade_replsecurity_metadata proc”, a cursor is being opened that walks through all user databases and for each database where HAS_DBACCESS()returns 1, we then check if an object with name MSsubscription_properties exists and if sys.columns contains an entry with name ‘job_step_uid’ and if both conditions are met, we call sys.sp_MSupgrade_subdb_security_metadata on the database. This procedure fails with error 3906 if the database is read only.

In this particular scenario the failure happens on a snapshot database which meets the two conditions.

Run the below script to identify any database snapshots that can cause this issue. If the script returns any results then drop those database snapshots before applying the Service Pack/ Cumulative Update

use master
go
declare @name sysname, @has_problem bit, @stmt nvarchar(512)
declare @tab table (name sysname)
declare dbcur cursor local fast_forward for
select name from sys.databases
where database_id > 4 and has_dbaccess(name) = 1 and is_read_only = 1 and source_database_id is not null
open dbcur
fetch next from dbcur into @name
while @@fetch_status = 0
begin
set @stmt = N'SET @has_problem = CASE WHEN EXISTS (SELECT * FROM ' + quotename(@name) +
N'.sys.objects WHERE name = N''MSsubscription_properties'') AND EXISTS (' +
N'SELECT * FROM ' + quotename(@name) + N'.sys.columns WHERE name = N''job_step_uid'') ' +
N'THEN 1 ELSE 0 END'
exec sp_executesql @stmt, N'@has_problem bit output', @has_problem output
if @has_problem = 1
insert @tab values (@name)
fetch next from dbcur into @name
end
close dbcur
deallocate dbcur
select name as problem_databases from @tab

Cheers,
Keerthi Deep

 

Installation of SQL Server 2008 R2 on cluster may fail while trying to bring the resource online

Recently I worked on an issue wherein the installation of SQL Server 2008 R2 on cluster fails with the below error

The cluster resource 'SQL Server (CWTSQL02)' could not be brought online.
Error: The group or resource is not in the correct state to perform the requested operation.
(Exception from HRESULT: 0x8007139F)

In cluster administrator I found that SQL resource was created but SQL Agent resource was not created and the SQL resource was in failed state. I tried to start SQL service from services.msc console, but not able to start as cluster instance. However, I wasn’t able to connect to SQL Server without providing port number. As I wasn’t able to connect to SQL instance without using port number, it points towards issue related to SQL browser service. This can occur when the SQL browser service is not started or is not functioning properly.

Not to my surprise I found that SQL Browser service was in a stopped state. I tried to start it but it failed, so tried to start it from command prompt which failed with below errors

C:\Program Files (x86)\Microsoft SQL Server\90\Shared>sqlbrowser.exe -c

SQLBrowser: starting up in console mode
SQLBrowser: Both SSRP and OLAP redirection services are disabled. Shutting down browser service

Then I looked at the below registry hive and could see the value of SsrpListener was 1, but AnalysisServiceListener was missing which should have been present if analysis services is installed on the server.
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\90\SQL Browser

SsrpListener – Value allows or disallows the SQL Browser to expose Engine instance discovery information, So if the value is 0 it disallows the SQL Browser to expose Engine instance discovery.

AnalysisServiceListener – Value allows or disallows the SQL Browser to expose Analysis Service discovery information.

Created the registry entry for AnalysisServiceListener with value 1. Even though the SsrpListener and AnalysisServiceListener are set correctly as 1, but still SQL browser was not starting.

Then I collected process monitor and from the log I found that the dll’s related to “some third party component” was loaded in SQLBrowser. We cannot name the third party component in this blog! If you encounter a similar issue, please manually verify if any third party dll (non-Microsoft) is loaded and interfering with SQL browser. After uninstalling the third party tool from the machine and then reboot it, we were able to bring the SQL browser online and then SQL Server resource online on cluster.

As a matter of fact, Microsoft has always recommended stopping any unwanted third party services and Anti-Virus for the duration of the setup as these components can interfere with the setup which may cause either setup to fail or may cause services to fail like the one we experienced.

You can find more information in below articles about Microsoft policy about antivirus and detouring
http://support.microsoft.com/KB/309422
http://support.microsoft.com/KB/920925

Cheers,
Keerthi Deep

 
1 2