Changing Quorum File Share witness

Open Failover Cluster Manager

To remove the exisitng quorum

• Right click on more actions ->Configure Cluster Quorum Settings -> Select Quorum witness
• Select Do not configure quorum witness.

Choosing the option will remove the existing quorum

To add /configure a new quorum

• Right-click on more actions ->Configure Cluster Quorum Settings -> Select Quorum witness
• Choose "Configure a file share witness"

• Type the new quorum name in File share

Click Ok in the end.

Thank you for reading my post.

Hope it helps you.

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.