MySQL and MSSQL interaction: Stored Procedures?
Hi all,
I am a newbie to stored procedures and need some advice, please.
A client of mine has a MySQL database with over 120 tables, and 30K products in the products table, with a PHP application managing the workflow of day-to-day operations of the company, using this database.
The client also has Pastel Evolution, using MSSQL as the database. All stock management is done via Pastel Evolution, but workflow is managed via the PHP application. Therefore, these two databases, as far as stock movement (branch transfers, sales, manufacturing, damaged goods, etc.) need to be in sync with each other.
Currently, there is a VERY ineffecient PHP script in place that checks all 30K products, and then verifies this with the data in Pastel, to ensure that we have the same amount of stock in both databases.
I believe there should be a way to utilize MySQL stored procedures and triggers to retrieve ONLY products where stock on hand is NOT the same between the databases, and update the MySQL, thereby cutting out the PHP script inbetween. This will then most likely make the script run closer to 10 minutes (or whatever) as opposed to the 9 hours it currently takes to complete.
The two servers (MySQL and MSSQL) are in the same data centre, and nothing prohibiting talk between the two, and no bandwidth issues.
I have no idea on where to start with this. Somehow, I need to compare the data in MySQL directly with the data in MSSQL, and update only records that have changed (about 100 records a day) as opposed to 30K records per day.
Any advice will be greatly appreciated,
Thanks!
Kobus