Cluster Core Resources failed state. Unable to bring it back online

The cluster core resource was in a failed state after the witness went down several times. The witness came back online but the cluster resource was still in a failed state. The following errors are in the cluster event log.

Cluster resource 'Cluster IP Address' of type 'IP Address' in clustered role 'Cluster Group' failed.

Based on the failure policies for the resource and role, the cluster service may try to bring the resource online on this node or move the group to another node of the cluster and then restart it.  Check the resource and group state using Failover Cluster Manager or the Get-ClusterResource Windows PowerShell cmdlet.

The Cluster service failed to bring clustered role 'Cluster Group' completely online or offline. One or more resources may be in a failed state. This may impact the availability of the clustered role.

I am not able to bring the cluster online. The public IP address for both node 1 and node 2 in FCM has unreachable state error though I can ping both nodes by IP address and get a response.

I could not find any resolution online. So, I rebooted the secondary node and the cluster core resource came back online. I will continue to research. Please leave a comment if you encounter the same issues and have a resolution.

Thank you for reading my blog.

AlwaysOn availability group - Setting Up Read-Intent Only Secondary Replica

Setting Up Read-Intent Only on Secondary Replica

SQL Sever version : 2016

Step 1
Change Readable Secondary Replica Setting to Read-intent Only

Step 2

Set up Read-Only Routing,

Read-Only Routing URL Format

Server 1 , TCP://server1.domain.us.dom:portno
Server 2 , TCP://server2.domain.us.dom:portno

Example

Select server 1 and add Server 2 in Read-only routing list

Select Server 2 and add Server1 in Read Only routing list

Read Only Routing List Testing


We tested Read-Intent Only configuration using SQLCMD and PowerShell

SQLCMD

Run cmd as administartor, Go to the path that SQLCMD is installed

C:\Program Files\Microsoft SQL Server\110\Tools\Binn> Sqlcmd -S SQLListener -d DatabaseName -U Login -P Password -K ReadOnly

PowerShell

Invoke-Sqlcmd -Query "select Top 100 * from dbo.TableName" -ConnectionString "Data Source=SQLListenerName;User Id=Login;Password=Password;Initial Catalog=Database;Integrated Security=False;ApplicationIntent=ReadOnly"

Thank you for reading my post.

SQL Server Table row size

SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N'Pega73'), OBJECT_ID(N'[Schema].[TableName]'), NULL, NULL , 'DETAILED')

dbcc showcontig ('[schema].[Tablename]') with tableresults

JDBC Driver bad default connection string sendStringParametersAsUnicode

When you use JDBC driver to set up an application connection string to MS SQL Server database, it passed the sendStringParametersAsUnicode parameter as True if you don't define  sendStringParametersAsUnicode in the connection string specifically. The default value is True. It will cause high CPU usage on SQL Server due to implicit conversion in queries passed from the application. The default setting send all the parameters in the queries as nvarchar.
If the data type in the tables does not align with the parameters datatype declared in the application queries, it will cause implicit conversion and table scan.

We need to always set sendStringParametersAsUnicode = False in JDBC connection string to SQL server database.

JDBC URL —
url="jdbc:sqlserver://

host:port;databaseName=dbName;SelectMethod=cursor;SendStringParametersAsUnicode=false"

Thank you for reading my post.

Create your own Microsoft Management Console

HAPPY NEW YEAR EVERYONE!!!

HELLO 2019!!!

I am back to work and this is my first blog for 2019. I used MMC control to group configuration managers that I need to use daily on each server. So, I would like to share this tips.

Click Run
Type MMC

After Control opened, Press Ctl+M

Add the tools to Selected Snap-ins: Choose SQL configuration manager and click Add.

Click OK after adding a few snap-ins.

Click on Save on Console and give a name.

Now, I have my console on my server desktop where I can open and check things.

SQL 2017 Configuration error

When I launched SQL 2017 configuration manager, I got the error below.

To fix the issue, I ran the following command in command prompt on the server.

mofcomp "%programfiles(x86)%\Microsoft SQL Server\140\Shared\sqlmgmproviderxpsp2up.mof"

I was able to open SQL server configuration manager.


Thank you for reading my blog!

Good Bye 2018...

Hello 2019..............

Creating Alias for SQL server

To Create an alias for SQL instance

Open SQL Server Configuration Manager

Go to both SQL Native Client branch on 32 and 64 bit. Please see screenshot below.

Right click on the right side of the window pane.

Alias Name : any name

Port No : 1433 (SQL default Port no)

Protocol :  TCP/IP

Server :  Fully qualified local server name(ex. server.domain.us.dom)

Next step is very important. We need to add the alias to DNS entry.

Please add the following SQL server alias to DNS entry alias/CName.

Alias Name : Newalias
Fully Qualified domain Name(FQDN)for target host : server.domain.us.dom

After adding it to DNS entry, we will be able to connect to Alias using SSMS from our desktop or application.