Microsoft SQL Server and MySQL, both are quite well-established database engines. In our role as a DBA, at some given time point we require T-SQL straddling between both database engines unites Select, Insert, Update, Delete and some other…
In terms of requirements for correlating with a MySQL database, eventually, we need to get to information from a MySQL database and synchronize it into Microsoft SQL Server. Now, a simple way to pursue that is with the utilization of MySQL connectors. The SQL Server statements or a query to insert, fetch or to do manipulations with the data from MySQL tables which are concerning to SQL Server tables. This means by inserting, fetching or manipulating in the solution we will have enough to import data from a MySQL Server database.
Now before dealing with T-SQL for MySQL inside the SQL Server, the Connection needs to be set up within inter-related Server as an initial step to start with. Additionally, the MySQL ODBC driver needs to be preinstalled on the machine.
Create a MySQL Linked Server
Let’s start with establishing an ODBC data source to the MySQL Server on the machine on which the SQL Server is installed.
Open ODBC Data Sources
To add new source, click the Add button, select the MySQL ODBC 8.0 ANSI Driver to integrate MySQL connection and click the Finish button.
Subsequently, configure the required information in MySQL Connector window to configure MySQL Data Source Name. In this example, we have used the localhost as the MySQL server name. Now, we can verify the connection with tapping on Test button. upon the successful test, the data source would be available to include another MySQL Linked Server in SQL Server illustration.
We didn’t select any database on the ODBC connection, but it works with the default database to execute T-SQL query over the connection. If you do not have mentioned database, then use databasename.tablename in T-SQL query to redirect your T-SQL statement on that particular database.
With the help of the Microsoft OLEDB Provider for ODBC Drivers with vital security refinements, we’ll create the link between servers with the final goal line of MySQL like the username (login) and the password for Connection Authentication.
Now when MySQL_LOCAL linked server is created let’s test the link between servers, to see whether it has been created in the correct manner or not.
The linked server is configured successfully to get the access to the MySQL_Local Server in SQL Server. But, make sure to allow Data access and RPC parameters to it in the configuration. Now we can start using the T-SQL assignment. Why MySQL is query execution essential in the Microsoft SQL Server? How could it be used and under what circumstances it can be used?
Using T-SQL query to fetch the MySQL table data in SQL Server
Microsoft SQL Server and MySQL both are very diverse by choice and not by the behavior. In most cases, the reason for using MySQL within the SQL Server is that software products are using both databases and need to manage the data in both the server database SQL Server & MySQL. Or must be using MySQL table column reference inside SQL Server table.
In such cases of integration of the third-party application, we require to cross-database reference inside the parent database and it is quite possible to use references of the information with each other and in the real-time scenario to see some of the RDBMS concepts having cross-database technologies as well.
How to use T-SQL Querying with MySQL inside SQL Server
OPENQUERY a pass-through query to executes the specified query on the specified linked server which could be a data source itself. The OPENQUERY can be used to reference in the FROM clause of a query as if it is the name of the table name and can also be used as an indicator as to the target table of an INSERT, UPDATE, or DELETE statement with the subject to the abilities of the Data source provider. However, the query could return multiple results sets the OPENQUERY will return only the first one of them.
|
1 2 3 4 5 6 7 8 |
OPENQUERY ( Linked Server ,'Query' ) SELECT * FROM OPENQUERY([MYSQL_LOCAL], 'SELECT fname, lname, email_, mobile_, city_, pincode, password_, user_type_id, is_active, created_at FROM d_portal.users WHERE user_id = 1' ) |
As seen, the MySQL T-SQL Query is inside the OPENQUERY and [MYSQL_LOCAL] is a MySQL Linked server that exists in the SQL Server. The data could be filtered by both the query side, which means we can apply the WHERE clause at the outer side OPENQUERY level as well as the inside query statement.
Insert data into MySQL using SQL Server
The question will arise of maintaining the data consistency when product is using both database servers database SQL Server & MySQL then most certainly we need to supervise the number of transactions based on the substantiality of the databases.
We will come across this type of real-time scenarios while we will be operative on Micro-service based platform, hybrid of Micro-service and monolithic structural design platform. Recent trends operational are to make a database in Micro-service based design hence, that would be quite wearisome or not easy to manage data consistency in multiple databases if it is only Microsoft SQL Server or amalgamation of databases technologies such as SQL Server, MySQL etc.
See here, we have various examples to insert data in MySQL from SQL Server using T-SQL statement.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
INSERT INTO OPENQUERY([MYSQL_LOCAL], 'SELECT fname, lname, email_, mobile_, city_, pincode, password_, user_type_id, is_active, created_at FROM d_portal.users' ) SELECT 'Jerry' AS fname, 'Ren' AS lname, 'Jerry@test.com' AS email_, '+00 000 000 00' as mobile_, 1 AS city_, '1011 AA' AS pincode, '4a69676e6573682052616979616e69' AS password_, 1 as user_type_id, 1 AS is_active, GETDATE() AS created_at |
The data will be inserted into the table with the help of the above query which is mentioned inside the OPENQUERY. If a table has an Auto incremented column then that status will be managed by MySQL only. In the above example, we have Auto Incremental column user_id in the users table of d_portal database and the remaining columns will be part of the INSERT statement.
Fundamentally, data can be inserted into the table by a couple of T-SQL statement methods, INSERT … VALUES and INSERT … SELECT. Either of that can be applied in this case as well. So, it is a better way to administer transaction consistency rather than individual data insertion by application for any such cases.
Update data in MySQL using SQL Server (T-SQL)
In such cases, it is possible to maintain record in SQL Server and MySQL both databases and hence, the information updates on the row, row should be updated in both databases by back-end or using any such mechanism or processes. See here, we used MySQL UPDATE statement in the T-SQL query inside the SQL Server itself.
|
1 2 3 4 5 |
UPDATE OPENQUERY([MYSQL_LOCAL], 'SELECT user_id FROM d_portal.users where user_id = 1' ) SET is_active = 0 where user_id = 1; |
The above T-SQL query will update the column is_active with 0 where user_id = 1 in the users table of d_portal database. This method should be utilized in all such scenarios only to keep in mind for the performance of a database query. It is not compulsory to use this mechanism when we need to maintain the Lookup tables or the master tables in both SQL Server and MySQL databases. In the case of the metadata tables, I would suggest going with the back-end update applications only with an individual query however making it sure that should be in a persistent way and maintaining the data consistency.
Delete data in MySQL using SQL Server
In the same way as the update statement, if the rows need to be deleted from both databases SQL Server and MySQL then the rows should be deleted from SQL Server using this approach, same as SELECT, INSERT and UPDATE statement, we can also use DELETE within the OPENQUERY statement. The query structure will be the same as the usual DELETE statement for SQL Server and OPENQUERY statement will be same as mentioned in the above case in the T-SQL query.
|
1 2 3 4 |
DELETE FROM OPENQUERY([MYSQL_LOCAL], 'SELECT user_id FROM d_portal.users' ) where user_id = 1; |
Note that in the above statement, the T-SQL query will delete a row in MySQL database where user_id = 1. For delete statement and update statement, the SELECT statement inside the OPENQUERY needs only those columns only which are mentioned in WHERE clause to perform DELETE or UPDATE.
These are basic MySQL query tutorials within SQL Server which will help us to manage some level of data consistency. Otherwise performance inconsistency issues could appear and which are not even easy to identify in cross databases such as – what part is getting stuck and why?
View all posts by Jignesh Raiyani
- Page Life Expectancy (PLE) in SQL Server - July 17, 2020
- How to automate Table Partitioning in SQL Server - July 7, 2020
- Configuring SQL Server Always On Availability Groups on AWS EC2 - July 6, 2020