Observations on fetching data over a network with 100ms latency using Oracle ODBC driver

Dear Reader,

I was troubleshooting a SQLNet performance issue. The client server is located @ 60 to 100ms latency from the Oracle DB server. I was looking for ways to improve the performance.

Lab:

1. 100ms latency added to the Oracle Server Public interface (egress)

2. Client Server is windows with a dblink created in MSSQL server pointing to oracle using Oracle ODBC drive.

3. We are using tns entry in tnsnames.ora file in client to connect to the server.

Conducted 3 tests:

1. Fetch buffer size left ASIS in default value - 64000

2. Fetch buffer size increased to 6400000

3. Fetch buffer size @ 6400000 and SDU size adjusted to 64K (follow notes section on how to increase SDU size).

Results summary:

Inference: 

From the results, it looks neither fetch buffer size nor the SDU size makes any difference to the ODBC performance.

Notes: Steps to increase SDU size

Let us set SDU size to 65536

1. Server side:

    a. Listener - done (added SDU to the DESCRIPTION part)

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (SDU = 65536)

      (ADDRESS = (PROTOCOL = TCP)(HOST = vcentos79-oracle-sa1)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

    b. sqlnet.ora

DEFAULT_SDU_SIZE=65536

2. Client Side:

    a. sqlnet.ora

DEFAULT_SDU_SIZE=65536

    b. tnsnames.ora

OPDB1 =

  (DESCRIPTION =

    (SDU=65536)

Verify the SDU setting by making a test connection from sqlplus on the client:

lsnrctl show trc_level

lsnrctl set trc_level admin

Connect from client and see a message like below to confirm the new setting are reflecting fine.

=== SDU size visible properly for client and server

2023-07-09 18:43:15.617 : nsconneg:vsn=318, lov=300, opt=0xc41, sdu=65535, tdu=65535, ntc=0xc60e

2023-07-09 18:43:15.617 : nsconneg:Large sdu=65536,tdu=2097152,compression flg=0

2023-07-09 18:43:15.617 : nsconneg:after negotiation compression flag=0

2023-07-09 18:43:15.617 : nsconneg:vsn=318, gbl=0xc01, sdu=65536, tdu=2097152

===

lsnrctl show trc_level

lsnrctl set trc_level off (or whatever level it was set before).

lsnrctl show trc_level

In deed a lot of work has gone to test the arraysize using sqlplus. But this is specific to odbc driver.

Thanks

MSSQL: Create linked server using SSMS (SQL Server Management Studio)

Setting up MSSQL Linked Servers

From MSSQL documents:

Linked servers helps executing T-SQL commands against other sql server instance or oracle.
To execute stored proc, the linked server needs to be defined as an instance of SQL Server.

sp_addlinkedserver (TSQL statement used to adding linked server).

Permissions needed:
when using T-SQL : ALTER ANY LINKED SERVER or setupadmin role
when using SSMS: control server or sysadmin fixed role is needed.

First method: SSMS 

These steps are to be done in the linked server target machine (the destination to which we wish to connect to)

1) Let us create a test db for this excercise in both source and target server (in my case it is windows 2019 server & windows 10 laptop)

Use the method outlined in previous blog - refer here.

https://oracledbaplanner.blogspot.com/2023/05/mssql-create-database-using-sqlcmd.html

2) Ensure to make SQL Server listen on the IP reachable from the other server.

a. Verify from the ERRORLOG (startup message, which port and ip the SQL Server is listening on)
2023-05-07 18:15:12.71 spid25s     Server is listening on [ x.x.x.7 <ipv4> 1434] accept sockets 1.  <<< this is the ip we are going to reach from other server
2023-05-07 18:15:12.72 Server      Server is listening on [ ::1 <ipv6> 1434] accept sockets 1.
2023-05-07 18:15:12.72 Server      Server is listening on [ 127.0.0.1 <ipv4> 1434] accept sockets 1.

Note: To enable your private network ip to be listened to apart from default 127.0.0.1. you have to go to SQL Server configuration manager > SQL Server network configuration > TCP/IP Properties > Ensure you set "Enabled = Yes" and "Listen All = No" > then in IP Addresses, navigate to the x.x.x.x ip you wanted to be listened to, set "Enabled=yes"

This change needs SQL server restart.

See quick screenshots below..

The target protocal page:

The target IP Addresses page:

3) Add port exceptions in windows defender/antivirus based firewall.
4) Ensure you have a user account with necessary permission...

a. Ensure the SQL Server authentication mode is known and set as expected (mixed mode in my case & for this test case).

b. Create a user with the below commands

USE master
GO
CREATE LOGIN tsqltestdb_user WITH PASSWORD='tsqltestdb_user', DEFAULT_DATABASE=[tsqltestdb], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
use tsqltestdb
create user tsqltestdb_user
for login tsqltestdb_user
with default_schema=dbo;
go
ALTER ROLE db_datareader ADD MEMBER tsqltestdb_user;
go
       c. Ensure you verify that your able to connect to the user using sqlcmd as below...

C:\Users\lab>sqlcmd -U tsqltestdb_user -P tsqltestdb_user
1> select * from t1;
2> go
i      j
------ ----------
     1 V
     2 R
(2 rows affected)
1> SELECT @@servername
2> go
--------------------------------------------------------------------------------------------------------------------------------
DESKTOPXXXXXXXXXXXXX
(1 rows affected)
1> SELECT @@servicename
2> go
--------------------------------------------------------------------------------------------------------------------------------
MSSQLSERVER
(1 rows affected)
1>

5) Get the name of the target server and instance you wish to connect to using the below commands...

To get the server name:

SELECT @@servername
go
To get the instance/service name:
SELECT @@servicename
go
Example output:
1> SELECT @@servername
2> go
--------------------------------------------------------------------------------------------------------------------------------
DESKTOPXXXXXXXXXXXXX
(1 rows affected)
1> SELECT @@servicename
2> go
--------------------------------------------------------------------------------------------------------------------------------
MSSQLSERVER
(1 rows affected)
1>

Now on the source machine, where the linked server has to be created:

6) Try connecting to the linked server target using the below sqlcmd command from the linked server source; it should succeed. Ideally this meand we are able to connect fine.Note: SQL Browser isnt needed for this authentication to happen (meaning the SQL browser can stay stopped/disabled in linked server target)

C:\Users\testusr>sqlcmd -U tsqltestdb_user -P tsqltestdb_user -S tcp:192.168.0.7,1434
1> exit
C:\Users\testusr>

7) Let us connect to SSMS using windows authentication on the source machine (linked list creation server)

8) Let us navigate to the below option

Open [Object Explorer] > Expand [Server Objects] > Right-click [Linked Servers] > Select [New Linked Server]

An option like below screen opens up...

Please follow the screenshot to create the linked server and test as well.

To test the linked server:

Openquery method:

select * from openquery("x.x.x.7,1434",'select * from t1');

4 part query method:

select * from "x.x.x.7,1434".tsqltestdb.dbo.t1;

Both worked in our ssms based linked server setup. But the sqlcmd couldnt get past the '.' characters in ip.

Note: Microsoft asks us to enter the name of the server alone in case if its a default sql server instance (if it is named, use named instance).

Remember we arent using default port, hence we have to put a comma followed by the server name and give the nodefault port as well. In our case it was 1434.

8) Since I dont have name resolution, I ended up using IP. The IP isnt working very well when sqlcmd is used to query the linked server.With either openquery or 4 part query.

This closes the excercise.

Thank you!