UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Getting Data From SQL To Mysqldb, SQL Server 2012    
 
   
StevenLS
post Oct 15 2017, 05:08 PM
Post#1



Posts: 172
Joined: 5-July 01
From: Sydney


Hey all,

We have an large asset tracking system where it's database resides on a SQL Server. For some custom reporting I extract a subset of that data and move it to a mysql database that us in IT can use to assemble data, spawn PHP pages from etc etc.

The way I move the data is from an access database I ODBC the tables from the SQL Server. I run a process to extract the data that I need to append that to another odbc table from the mysqldb.

I am not sure of any other way to move a subset of data from A to B. It seems like I am adding an unnecessary access step into the equation but not sure how else to achieve this.

Happy to hear some recommendations, but I cannot change the way the SQL Server and the mysql servers are setup.. that's the way it works here with everything that runs.

Cheers

S.
Go to the top of the page
 
nvogel
post Oct 16 2017, 06:27 AM
Post#2



Posts: 811
Joined: 26-January 14
From: London, UK


Why don't you run your custom reporting on SQL Server? It's a better DBMS than MySQL and making use of SQL Server seems a lot easier and better use of resources than copying the data around.

If you can't do that then maybe consider using SQL Server Integration Services (SSIS). SSIS is quite a powerful way to manipulate and move data. SSIS runs server-side rather than client-side.
Go to the top of the page
 
StevenLS
post Oct 16 2017, 06:35 PM
Post#3



Posts: 172
Joined: 5-July 01
From: Sydney


Thanks for the reply. Unfortunately production databases (i.e. a third party product needing a SQL Server) are off limits for inhouse databases.

I will do some scratching around in relation to the SSIS and speak to out systems engineering team about what I can do to leverage it.

Thanks for the reply, appreciated.

S.
Go to the top of the page
 
cheekybuddha
post Oct 16 2017, 08:03 PM
Post#4


UtterAccess VIP
Posts: 9,275
Joined: 6-December 03
From: Telegraph Hill


MySQL have tools that do this, but the process seems the same as what you are doing at the moment.

You could always write php scripts to do the same. That way the import is launched directly from the server machine (assuming the web server redsides on the same server as MySQL) rather than going through your machine.

hth,

d

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


Regards,

David Marten
Go to the top of the page
 
MadPiet
post Oct 16 2017, 10:48 PM
Post#5



Posts: 2,255
Joined: 27-February 09



What if you write the package in SSIS that does the export to MySQL and then you run it in the middle of the night? Just create a job to run the package, and have it run at maybe 2 AM?
Go to the top of the page
 
StevenLS
post Oct 16 2017, 10:51 PM
Post#6



Posts: 172
Joined: 5-July 01
From: Sydney


Made the mistake of typing SSIS beginners tutorial in youtube.. just wading through the mass results to filter out the rubbish.

Also.. per the previous comment - webserver is different also (of course).
Go to the top of the page
 
MadPiet
post Oct 17 2017, 10:25 PM
Post#7



Posts: 2,255
Joined: 27-February 09



Maybe check out Brian Knight's SSIS In 24 Hours... his stuff is pretty good. Also dig around on YouTube or PragmaticWorks.com he has tons of free videos.
Go to the top of the page
 
StevenLS
post Oct 18 2017, 12:49 AM
Post#8



Posts: 172
Joined: 5-July 01
From: Sydney


Thanks, that site looks like a really good resources. The SSIS in 24 hours is cheap also, will get that underway as well I reckon.

thanks folks.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    12th December 2017 - 03:06 AM