MariaDB To SQL Server via Connect Table Type

-


With rising complexity in applications, organizations are turning to heterogeneous database platforms for scalability, efficiency and cost effectiveness. This leads the database administrators to create connectivity between databases of two different platforms.

SQL server has utility of linked server which helps tremendously when it comes to configuring such inter database connectivity. MariaDB based on Linux lacks this feature but surely provides a work around for the same. Here's when table with CONNECT table type comes into picture

CONNECT uses this API to access data contained in other DBMS without having to implement a specific application for each one. It works with ODBC drivers provided by Microsoft (freeware), with drivers in place MariaDB can now connect to SQL server using the installed driver with a connection string

Microsoft® ODBC Driver 17 for SQL Server® - Windows, Linux




create table test_student ENGINE=CONNECT DEFAULT CHARSET=latin1

CONNECTION= 'Driver=/opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.790.0; 

Server=<SQL_Server_name_or_ip>,<sqlserver_port>; Database=<sql_db_name>; 

UID=<sql_login>; PWD=<sql_password>;' TABLE_TYPE='ODBC'

Installing the ODBC driver at MariaDB end is must for this configuration to work. the MariaDB Linux should be able to telnet to SQL database server. Now with respect to above code the MariaDB using mentioned ODBC driver will look for the table as mentioned in example "test_student" at the SQL server end specified in the connection string.

Once the query is executed it returns " table created " message. The configuration is complete. The SQL server table can be accessed from MariaDB and with rights the sql server login has on the table it can perform various dml statements. all the changes from both end can be seen to each other.

Comments

Post a Comment

Popular posts from this blog

SQL Server’s STATISTICS IO

-
Image
Usually the goal of performance tuning is to make your query run faster.  One of the easiest ways to get a faster query is to reduce the volume of data a query is processing.  STATISTICS IO makes it easy to see how much data SQL Server is actually processing. How it is going to help?  The data it shows acts as a measuring for your performance tuning.  It provides a good way of knowing the number of io Sql server has gone to fetch the data  It is session specific only How to Use? -- For current session only SET STATISTICS IO ON; GO How to read the output?  Logical reads: The number of pages SQL Server had to read from the memory in order to process and return the results of your query.  Query performs slower if number of pages read is high.   Physical reads:  this read happens when the page requested is not present in the cache and needs to be fetched from files on the disk, this operation causes a IO wait.   Worktables/Workfiles:  sql server cre...
Read more

(session ID -2 is not valid) Dealing with Negative Session ids in Sql Server

-
Image
  SQL server being a complex mechanism of handling processes throughout their life cycle often gives out interesting anomalies, One such was encountered today on one of my production servers, there was a huge blocking on chain of processes and at the root of this chain was a session with the weirdest session id  The session id literally was " -2 "   ,  it is hard to make sense out of it as SQL server always works with positive spids. Research on this is itself a tedious job. After the R&D phase it was somewhat easier to diagnose the problem. The root cause refers to a phase of distributed transaction, A distributed transaction is referred when a process is extended over more than one servers i.e Process A of server 1 needs data to be manipulated on Server 2, so SQL server uses MSDTC (linked server) service to carry this process from one server to another. Going back to our issue, now According to msdn and other online resources, the session id -2 means that ...
Read more

ORA says NO DATA FOUND

-
Image
Linked servers always have a surprise error hidden under its selvee, Managing these issue magnets is a huge task for a DBA , recently i was stumbled upon a same suprise invloving a oracle linked server. The linked server "oracle_db" is present on my prod db server essentially for select and insert purposes, Developers tested the linked server with couple of select querries and all was fine until the deployed a procedure on server and executing the same gave a alien error OLE DB provider "OraOLEDB.Oracle" for linked server "oracle_db" returned message "ORA-01403: no data found" inspecting the code it was seen that the table from linkedserver was directly involved in a " inner join" and other joins ,re running the same satement produced the given error . The code snippet was pinned, diagnosis and resolution was underway After a thorogh R&D it was found that either taking the whole oracle side output into a #Temp table and then using t...
Read more