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.

Evict and Destroy a Cluster

In powershell,

get-clusternode

#Please do the following steps first.
# Step 1. Pause and Drain Role on a secondary node
# Step 2. Stop Cluster service on the node to be evicted

#Now proceed to remove a secondary cluster Node

Remove-ClusterNode -Name ServerName

#Now on the last node, Remove the cluster and cleanup

Get-Cluster ClusterName | Remove-Cluster -Force -CleanupAD

How long did t take to load One point five Billion rows using ETL data flow ?

1.5 + Billion rows was Loaded to a table using ETL data flow. It took about 15 hrs 9 mins and 59 seconds.

Both data source and Destination are tables

2000 rows / batch

Table load - fast load

A few warning 

[SSIS.Pipeline] Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 5 buffers were considered and 5 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.

[SSIS.Pipeline] Warning: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available.  To resolve, run this package as an administrator, or on the system's console.

Rolling Cluster upgrade to Windows 2012R2 to Windows 2016 - (SQL Server availability group is a workload)

The cluster has two nodes and the workload is SQL server availability group.

Step 1

1. Drain role on one of the nodes
2. Evict the node from a cluster
3. Upgrade OS to Windows 2016
4. After upgrading successfully, add the node back to the cluster

SQL Listener

During the node 1 upgrade, SQL listener is online and application has no down time.

ClusterFunctionalLevel Verificaton

Open PowerShell , Run 

PS C:\WINDOWS\system32> get-cluster | select clusterfunctionallevel

ClusterFunctionalLevel

----------------------

                     8

you will get ClusterFunctionalLevel 8 ( which is a mixed mode clustering )

Step 2

1. Drain role on the second node
2. Evict the node from a cluster
3. Upgrade the node OS to Windows 2016
4. After upgrading successfully, add the node back to the cluster

Step 3

1. Run validate cluster
2. Check ClusterFunctionalLevel again

Open PowerShell , Run 

PS C:\WINDOWS\system32> get-cluster | select clusterfunctionallevel

ClusterFunctionalLevel

----------------------

                     8

you will get ClusterFunctionalLevel 8 ( which is a mixed mode clustering )

Final Step

PS C:\WINDOWS\system32> Update-ClusterFunctionalLevel

NOTE: after you run this command, you cannot rollback.

if it runs successfully, you will get 

PS C:\WINDOWS\system32> get-cluster | select clusterfunctionallevel

ClusterFunctionalLevel

----------------------

                     9

Summary

1. Windows Server 2016 makes upgrading clusters easy with rolling upgrades
2. Control when your cluster upgrade is complete with the Cluster Functional Level
3. Add and manage your Windows Server 2016 cluster from the up-level (2016) nodes