Problems un-installing Windows Programs – error code 0x84B30002

Today I was trying to install SQL Server 2014 CTP 1 on one of my test servers. As you might be aware the CTP1 version needs a clean server and doesn’t support Side-by-side installation with other SQL Server products.

While clearing some previous installations, I had issues un-installing SQL Server 2008 R2 Database engine which is not visible in the Control Panel -> Uninstall Programs. There were other SQL 2008, 2008 R2 and SQL 2012 binaries that were dependent on the Database Engine and couldnt not be properly uninstalled.

As part of the manual uninstall of these binaries, I used the Microsoft Fix It. One can run this tool from here.

I went ahead with recommended options on this wizard and chose to uninstall any thing related to SQL server 2008 R2 Database Engine. Once it gave a green by patching the registry and uninstalling the tools, rebooted the server and SQL 2014 CTP1 is installed successfully.

Bug? Verify HADR DB’s backups using Maintenance plans

Today I came across this issue where a backup job has been failing on a SQL 2012[11.0.3000]. The server is member of AlwaysOn Availability Group and this full-backup job is running on the Primary. The Backup Preferences for the AG is set to “Prefer Secondary” and the backup priorities [info sake,not related to current error] are 60 and 40 for the corresponding nodes.

The maintenance plan is so created to take backups for all the DBs and Verify Backup integrity is checked.

With the above configuration, I would expect the Backups to happen and verified when the node is the preferred node. Since the current node is primary and the preferred node for backups is Secondary, I would expect the backups wouldn’t be taken as part of this execution. Also the job should end successfully without creating the backups and expect the job to verify the backups that are successfully created.

Since the job is failing –  I generated the TSQL from the Maintenance plan designer to see the code.. it looks like below

</pre>
DECLARE @preferredReplica int
SET @preferredReplica = (SELECT [master].sys.fn_hadr_backup_is_preferred_replica('AlwaysOnDB'))
IF (@preferredReplica = 1)
BEGIN
BACKUP DATABASE [AlwaysOnDB] TO DISK = N'R:\AlwaysOnDB\AlwaysOnDB_backup_2013_07_10_115020_3120404.bak' WITH COPY_ONLY, RETAINDAYS = 7, NOFORMAT, NOINIT, NAME = N'AlwaysOnDB_backup_2013_07_10_115020_3120404', SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 10
END
GO

declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'AlwaysOnDB' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'AlwaysOnDB' )
if @backupSetId is null
begin
raiserror(N'Verify failed. Backup information for database ''AlwaysOnDB'' not found.', 16, 1)
end
RESTORE VERIFYONLY FROM DISK = N'R:\AlwaysOnDB\AlwaysOnDB_backup_2013_07_10_115020_3120404.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO
<pre>

If you see the sql generated, it is checking the preferred node  for taking the backups but verifying the backups irrespective of whether the backup is created or not. I think the verify code should also be part of the condition whether its preferred node or not. There is no point in verifying the backup that the code is aware that it has skipped.

SQL Server Agent Proxy Error for insufficient privileges.

Have recently came across this error popping up while creating a new job using TSQL during SQL 2012 migration.

Error:

Msg 14516, Level 16, State 1, Procedure sp_verify_proxy_permissions, Line 164
Proxy (2) is not allowed for subsystem “CmdExec” and user “Domain\account”. Grant permission by calling sp_grant_proxy_to_subsystem or sp_grant_login_to_proxy.

This error states that the Proxy doesn’t have access to the cmdexec [Operating System] subsystem. But when I tried looking whether the proxy has access to the subsystem, the mapping does exist. Used the following TSQL for that.

</pre>
SELECT sss.subsystem,sp.name,sl.name
FROM syssubsystems sss
INNER JOIN sysproxysubsystem sps
ON sss.subsystem_id=sps.subsystem_id
INNER JOIN sysproxies sp
ON sps.proxy_id = sp.proxy_id
INNER JOIN sys.server_principals sl
ON sp.user_sid = sl.sid
<pre>

Solution:

The actual problem is that the login thats used by the credential which the proxy wraps doesnt have access to execute commands from the Cmdexec subsystem.  Thought the error message should have been framed. So once I granted the underlying <domain/Account> access to execute the CMDExec [sysadmin], it worked fine.

The Different SSAS OLAP Storage settings and their key differences.

The Different SSAS OLAP Storage settings and their key differences.

[In case the table looks truncated, have added a screenshot here. Also, the settings can be modified/configured in the Storage Options window]

The Different SSAS OLAP Storage settings and their key differences.

SSAS Partition Storage Setting	Structure of Detailed Data	Structure of Aggregates	Is Caching Used?	Notification	Silence Interval	Silence Override Interval	Drop Outdated Cache	Is Processing Scheduled*
Real-Time ROLAP	Relational	Relational	Not Required	Not Required	—	—	—	Not Required
Real-Time HOLAP	Relational	Multi- Dimensional	Not Used	Yes	0	—	—	NO
Low Latency MOLAP	Multi-Dimensional	Multi-Dimensional	Yes	Yes	10s	10m	30m	NO
Medium Latency MOLAP	Multi-Dimensional	Multi-Dimensional	Yes	Yes	10s	10m	4h	NO
Auto MOLAP	Multi-Dimensional	Multi-Dimensional	Yes	Yes	10s	10m	Never	NO
Scheduled MOLAP	Multi-Dimensional	Multi-Dimensional	Yes	No	—	—	Never	Yes
MOLAP	Multi-Dimensional	Multi-Dimensional	No	No	—	—	—	NO

Legend:

ROLAP: Relational OLAP.
HOLAP:  Hybrid OLAP.
MOLAP: Multi-Dimensional OLAP.
Caching: Proactive Caching for Queries.
Notification: Notification that underlying data has changed.
Silence Interval: A silent time with no data modifications for fresh processing to initiate.
Silence Interval Override: The time that overrides Silence Interval, and initiates processing after notification irrespective of any modifications.
Drop Outdated Cache: Duration for holding cache once processing starts. This serves any queries during processing. If processing takes longer than this duration, the queries wont be served as the cache is outdated. This can be said as a Latency threshold.
*Processing Schedule: Processing can always be triggered manually.

Error creating SSIS Catalog Database

Error:

The catalog backup file ‘C:\Program Files\Microsoft SQL Server\110\DTS\Binn\SSISDBBackup.bak’ could not be accessed. Make sure the database file exists, and the SQL Server service account is able to access it. (Microsoft.SqlServer.IntegrationServices.Common.ObjectModel)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProductVersion=11.0.2100.60+((SQL11_RTM).120210-1846+)&LinkId=20476

Reason : This error occurs if Integration Services isnt installed. Once Integration services is installed the SSISDBBackup file and other other binaries are back in the Binn Folder.

Workaround: The workaround for this error as suggested here is to copy the  SSISDBBackup.bak from any of your other SQL Servers. The Connect item doesnt talk about other missing files including critical ones like SSISUpgrade and loads of Libraries.

Solution: Not sure why a Workaround is preferred here – unless you dont have the SQL Installation Binaries or you dont want to reboot the server before getting the SSIS Catalog in place. Would always prefer to have Integration Services installed in case you need a Catalog in place.

Script to get Backup file Locations

SELECT TOP 1000
bs.database_name DBName, bs.name BackupName,bs.backup_start_date,bmf.physical_device_name,
bs.[type] BackupType,bs.backup_size,bs.compressed_backup_size,
(bs.compressed_backup_size/bs.backup_size)*100 CompressionRatio
FROM msdb..backupset bs<br />INNER JOIN msdb..backupmediafamily bmf
ON bs.media_set_id = bmf.media_set_id
ORDER BY bs.backup_start_date DESC</p><p>

Troubleshooting Database Mirroring Connectivity issues.

Issues in configuring Database Mirroring using SQL Server Management Studio [SSMS].

Error :

TITLE: Database Properties
——————————

An error occurred while starting mirroring.

——————————
ADDITIONAL INFORMATION:

Alter failed for Database ‘MirrorTest’. (Microsoft.SqlServer.Smo)

——————————

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————

The server network address “TCP://mirror.abc.testdomain.local:5022” can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)

Environment : SQL Server 2008 R2 – 10.50.2500. ; Windows 2008 R2 SP1.

DB Mirroring settings :

a) Principal and Mirror are in same Domain. [ not a prerequisite for mirroring, but we had the luxury.]

b) SQL Services on both servers running on same service account [again, Not a prerequisite – Services can even run on local accounts].

c) Firewalls opened for the DB Mirroring port TCP 5022 and ms-sql-s port TCP 1433. No firewall rules for UDP 1434 applied.

d) asynchronous Database Mirroring.

Troubleshooting method :

a) Telnet from Principal to Mirror on Port 5022. Works fine. So that establishes that there are no firewall issues between the endpoints. Telnet is the easiest way to test connectivity for any TCP port, though remember that it cant be used to test UDP ports.

Telnet <targetserverFQDN> <MirrorPort>

b) The Mirror Database has been restored with NORECOVERY from a Full backup but a log backup hasn’t been restored. Well, a Log backup is needed as the Full backup wouldn’t grab the pages set in Bulk Change Map while the Log backup actually check the BCM page and grabs the pages. This is needed for the SQL Server to determine that no Bulk copy pages are missed between the restores. So got the Log backup applied with NORECOVERY. Still the error hasnt changed.

C) Checked the SQL Server and Windows Application Logs on Principal :  can see errors like

-> The description for Event ID ‘-1073740350’ in Source ‘MSSQL$InsName’ cannot be found. The local computer may not have the necessary registry information or message DLL files to display the message, or you may not have permission to access them. The following information is part of the event:’4′, ‘An error occurred while receiving data: ‘10054(failed to retrieve text for this error. Reason: 15105)’.’, ‘TCP://server.domain.local:5022’

-> Same Error in SQL Server log  just after “Database mirroring has been terminated for database ‘MirrorTest’. This is an informational message only. No user action is required.”.

-> Ran Profiler to trace  Database: Database Mirroring Connection and Database Mirroring State Change events on Principal. It caught the same error “An error occurred while receiving data: ‘10054(failed to retrieve text for this error. Reason: 15105)’.” and nothing more.

-> Ran Profiler to trace DBM events on Mirror. This one caught a more meaningful error – Connection handshake failed. The login ‘Domain\Computer$’ does not have CONNECT permission on the endpoint. State 84.

These errors can be found out on the Application Log Diagnostics on the Mirror as well.

Solution :

As the last error clearly says – the Computer account needs access to the SQL Server Mirroring Endpoint even though the service accounts are Domain accounts. The services are running on Domain accounts and there is no restart pending on any of the nodes.

GRANT CONNECT ON ENDPOINT::Mirroring TO [Domain\Computer$]

The above statement has been executed for the sql server instances after creating logins for each of the Computer account on the corresponding sql instance. so the Domain\Mirrorserver$ account has been created on Principal sql instance and granted CONNECT privilege to Database Mirroring endpoint [select name from sys.database_mirroring_endpoints]. Likewise for Domain\Principalserver$ on Mirror sql instance.

In case any of the instances is a clustered instance – make sure that all the node accounts have been granted the required access.

Once the mirroring has been started, it worked fine.

Query to list Collection and Upload Set Job details

SELECT [collection_set_id]
 ,scsi.[collection_set_uid]
 ,scsi.[schedule_uid]
 ,scsi.[name]
 ,scsi.[name_id]
 ,scsi.[target]
 ,scsi.[is_running]
 ,scsi.[proxy_id]
 ,scsi.[is_system]
 ,scsi.[collection_job_id]
 ,sj1.name collection_job_name
 ,sj1.enabled collection_job_enabled
 ,sj1.description collection_job_desc
 ,scsi.[upload_job_id]
 ,sj2.name upload_job_name
 ,sj2.enabled upload_job_enabled
 ,sj2.description upload_job_desc
 ,scsi.[collection_mode]
 ,scsi.[logging_level]
 ,scsi.[description]
 ,scsi.[description_id]
 ,scsi.[days_until_expiration]
 ,scsi.[dump_on_any_error]
 ,scsi.[dump_on_codes]
 FROM [msdb].[dbo].[syscollector_collection_sets_internal] scsi
 INNER JOIN msdb..sysjobs sj1
 ON scsi.collection_job_id = sj1.job_id
 INNER JOIN msdb..sysjobs sj2
 ON scsi.upload_job_id = sj2.job_id

Backupset queries.

Some queries about Backups from MSDB.Bcakupset table. Will keep on adding here.

Backup size by Date and Database name :

</pre>
select convert(varchar(12),backup_finish_date,103) date,
cast(backup_size/(1024*1024) as decimal(10,2)) backupsizeMB,
database_name from msdb..backupset where type='D'
and convert(varchar(12),backup_finish_date,103)= '26/10/2012'
order by database_name desc
<pre>

XML for Analysis Parser: Error while browsing a cube in Excel.

Error:

“XML for Analysis parser: The LocaleIdentifier property is not overwritable and cannot be assigned a new value.”

Solution:

Change the locale of the system to English[US].

More reading : http://social.msdn.microsoft.com/Forums/en-AU/sqlanalysisservices/thread/6bb416a0-dde8-4ffe-a0cd-9d4d73a5685c