SQL2008R2 Cluster Installation Failure on WIN Server2012R2

Migrating a SQL2008R2 DB from a WIN2008R2 Server to a WIN2012R2 server can cause a few problems especially while rebuilding the cluster setup.
DB Cluster installation can throw away anything from a validation failure to a setup installation failure message.

Even if the Server/OS teams have shown you a green flag on the cluster health check/validation level, you may very likely end up scratching your heads figuring out these installation related errors.

When I attempt to run the SQL 2008 R2 installer to create a new Failover Cluster, the Setup Support Rules check gives me errors about the cluster, that the Cluster Service and Cluster Storage aren’t available.

It turns out that SQL 2008 R2 relies on some Windows cluster functionality that is not installed by default in Windows 2012 R2, specifically the Failover Cluster Automation Server.

You can open a PowerShell prompt in Administrator mode and execute the following:

Get-WindowsFeature RSAT-Cluster*

You’ll see that the Failover Cluster Automation Server is not checked off. To install it, execute the following:

Install-WindowsFeature -Name RSAT-Clustering-AutomationServer

If you quit your SQL Installer and attempt to re-run it to create a Failover Cluster, you’ll now see that the setup support rules around the cluster pass.

Hope it helps you.

Happy Learning!

Aman Kharbanda

Backup/Restore IO Size

By default, SQL Server will use jumbo sized IOs (typically of 1MB) to perform backup and restore operations. These can adversely affect the IO latency for your array. Whenever possible, use MAXTRANSFERSIZE=65536 on your BACKUP or RESTORE statements. This could make your backups and restore take a tad longer, so test accordingly. If you are leveraging Transaction Log Shipping, you might have no way to tune this, as sqllogship.exe doesn’t offer a means to tune this parameter. If log shipping backups/restores are affecting your performance, considering rolling out scripts to perform log shipping manually.

If you are not achieving good throughput from your backup/restore operations after using the MAXTRANSFERSIZE=65536 parameter, considering bumping up the number of buffers available to the process by using the BUFFERCOUNT parameter. In our testing, a 1.5TB database backed up in 24 minutes, 38 seconds at default settings, and 25 minutes, 6 seconds with 64Kb IO block size and BUFFERCOUNT=2048.

Happy Learning.
Aman Kharbanda




Fixing SQL Server Log Shipping after new DB file has been added to Primary

If you are maintaining a standby copy of your PRD DB enabled with log shipping mechanism, and it breaks after a data file was added to your Primary database, refer to the below to overcome this kind of situation.

You might see restore errors like these on your secondary DB server-

Error: Could not apply log backup file ‘\\File server Name\MSSQL\PRD\DBName_20151111199999.trn’ to secondary database ‘DBName’.(Microsoft.SqlServer.Management.LogShipping) ***  2015-11-12 13:30:11.98 *** Error: Directory lookup for the file “E:\Data\MSSQL10_50.PRD\MSSQL\DATA\DBName_2.ndf” failed with the operating system error 3(The system cannot find the path specified.).  File ‘DBName_Data3’ cannot be restored to ‘E:\Data\MSSQL10_50.PRD\MSSQL\DATA\DBName_2.ndf’. Use WITH MOVE to identify a valid location for the file.  Problems were identified while planning for the RESTORE statement. Previous messages provide details.  RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***

Check which transaction log has been last applied on Secondary DB (Via T-SQL or Log Shipping Status Report).
You may use this t-sql to get the status:

SELECT [rs].[destination_database_name],
[bs].[database_name] as [source_database_name],
[bmf].[physical_device_name] as [backup_file_used_for_restore]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs
ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf
ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY [rs].[restore_date] DESC
Now to validate which secondary file was lately added to your DB, use the below T-SQL:
RESTORE FILELISTONLY FROM DISK=’\\File server Name\MSSQL\PRD\DBName_20151111199999.trn’

And the final step would be to restore the transaction log with norecovery and move option:
RESTORE log DBName FROM Disk=’\\File server Name\MSSQL\PRD\DBName_20151111199999.trn’
with norecovery,
MOVE ‘DBName_Data3’
TO ‘E:\Data\MSSQL10_50.PRD\MSSQL\DATA\DBName_2.ndf’

Once DB is restored, you should be able to restart your log shipping restore automated jobs.

Happy learning!

Aman Kharbanda

Avoid a reboot on your server while installing multiple updates or patches

Ever wondered how cool would it be to avoid server reboot prompts should you be installing multiple updates or deploying patches on your Windows Servers? I’m sure many of you must have encountered this error sometime before-

“This error indicates that a restart from a previous install or update is pending and the system must be restarted before setup can continue”

Here’s one trick up your sleeve which you may use to avoid such errors-HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\PendingFileRenameOperations

If PendingFileRenameOperations registry key has any value then we got to clean it, in order to overcome this issue.

To delete the PendingFileRenameOperations registry key value-

  1. Open a registry editor, such as Regedit.exe or Regedt32.exe.
  2. Navigate to HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\
  3. In the right navigation pane, right-click the PendingFileRenameOperations key and select Delete.
  4. Close Registry Editor.

Happy learning!!

Aman Kharbanda

Query processor ran out of internal resources and could not produce a query plan

If you happen to see the below error in your SQL Server log file, and not sure what’s it related to and why is it logged, here’s what you should be doing-

Error: 8623, Severity: 16, State: 1   The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query.

If you look closely into the error, it states that the query processor ran out of internal resources and could not produce a query plan. When the query processor finds itself consuming a lot of time optimizing a query, it may decide to stop the optimization process abruptly, and choose the best available plan. This is to ensure that the optimizer doesn’t end up optimizing forever.

There are trace flags available which are meant to be used on a session level to avoid such issues.
But if such issue happens regularly, you should approach session owner, and advise them to fine tune the queries or use Database Tuning Advisor, and apply the recommendations.

Happy Learning!

Aman Kharbanda

Changing Ports for SQL Server Analysis Services

Here’s all what you might be interested to know about the configuration and customization of port configured for SQL Server Analysis Services-

How to check the Port number configured for SQL Server Analysis Services:
a) Open Task Manager and get the Process Id (PID) for msmdsrv.exe.
b) Open command Prompt type netstat /abo >>c:\output.txt .
c) Look for the PID in output file and corresponding TCP IP:Port information for same PID.
d) To confirm whether you got right Port number, Open Management Studio and connect to AS using IP Address:Port Number (For example:

How to change Port for SSAS Service:
1) Non-clustered environment (Default Instance or Named Instance):
You can configure a named instance to run under a specified port by changing the Port property for the Analysis Services instance to a specified value by using SQL Server Management Studio (this is an advanced property) or by editing the Port server property (<Port>0</Port>) in Msmdsrv.ini file directly (in the ../OLAP/Config folder). A value of zero (the default value) means that Analysis Services dynamically assigns the port at startup.

2) Clustered environment (Default Instance or Named Instance):
SSAS will start listening on all Public IP addresses of the cluster group using the default port (2383). Any alternate port configuration is ignored.  All clustered SSAS instances, no matter whether they were originally installed as default or named instance, behave like default instances and listen on port 2383. The only supported way to connect to a clustered SSAS instance is to use the “virtual server name” (also called network name) – Usage of the instance name like in  “VirtualServerName\InstanceName” is not supported.

Happy Learning!

Aman Kharbanda

Resetting a lost MySQL root password

MySQL root password allows full access to the MySQL database and allows for all actions to be undertaken including creating new users, new databases, setting access rules and so on.

You might get the following error (Error 1045) while trying to key in the wrong password->
Enter password:
mysqldump: Got error: 1045: Access denied for user ‘root’@’localhost’ (using password: YES) when trying to connect

If you set a root password previously, but have forgotten it, you can set a new password using these steps->

1) Stop MySQL-
/etc/init.d/mysqld stop

2) Start MySQL in Safe Mode (This enables anyone to connect without a password and with all privileges. Because this is insecure, you might want to use --skip-grant-tables in conjunction with --skip-networking to prevent remote clients from connecting) –
mysqld_safe –skip-grant-tables &

3) Login without a Pwd-
mysql -u root

4) Reset the password by executing the following statement
mysql> UPDATE mysql.user SET Password=PASSWORD(“MyNewPwd”) where User=’root’;

5) Stop and Start MySQL Service-

/etc/init.d/mysqld stop

/etc/init.d/mysqld start

6) Login again using the new password and you should be good to go.

mysql -u root -p newpwd

Happy Learning!

Aman Kharbanda