Mixing MySQL and MSSQL

[WayneH]

Ok, this is more of a general solicitation of opinions and in the world of SQL, programmers generally support and like what they use. I'm not as interested in responses around which is better.

I am looking for feedback (read, talk me out of it) on mixing MS SQL and MY SQL in a single application.

Background: we're cheap, we have an ERP/accounting system that runs MS SQL server express as its back end, the accounting system costs alot of money and has and will always use MS as its back end. The accounting data and web app data are pretty integrated. All that background to say, 2 gigs of accounting data and a business operating around it, so moving everything to MYSQL just isn't possible.

My situation: At the current pace, I am fine on the accounting functionality part of our system and won't bump into the express 10 gig limit. The problem? We're now adding more and more functionality into the web app. Functionality that is driving content. That rate of data growth is picking up speed. Alpha Anywhere will push it hard... I'll have some 10 field based service techs using tablets and iPhones for service reports and all kinds of field support of our equipment.

I started getting the feeling I was making code decisions based on my worry about the backend DB size.

So, that brings me to a decision. Regardless, by next year or at the latest the year after, I'll smack into the 10 gig limit on express.

My crazy notion has been: what about mixing MySql with my current DB? Where certain data is never shared with the accounting system (tables that aren't used in any way by the accounting system), put those tables in MySql?

Am I just asking for problems and have lost my senses or is this reasonable?

Is there Merit to this thinking or is it better to start mentally preparing that in the next year or so my wallet is going to get molested by Microsoft for 7k in server costs and sail past the 10 gigs?

[Ken Tjia]

Re: Mixing MySQL and MSSQL

wayne, it is absolutely no problem to mixed.
i once have my own data in ms sql full version
my security in express, while the accounting data are in two separate pervasive sql
(two different companies's accounting data) and lastly the inventory file for look up
built on my sql.

you will notice a slight change of select statements.
and the pervasive was connected via ODBC etc.

while MS-SQl and My-SQL get connected directly.
I did notice delay from time to time, imagine three grids
show up on one page with data from different sources.
Those ODBC's seem much slower.

[Steve Workings]

Re: Mixing MySQL and MSSQL

Where are you hosting your A5 site? Most ISPs have a license for the Web edition of MS SQL, and you can share the cost of that license for a small price -- usually around $25/month. A nice benefit of this is that the Web edition doesn't limit the use of RAM like SQL Express, so you may get a nice performance improvement as well.

[WayneH]

Re: Mixing MySQL and MSSQL

Where are you hosting your A5 site? Most ISPs have a license for the Web edition of MS SQL, and you can share the cost of that license for a small price -- usually around $25/month. A nice benefit of this is that the Web edition doesn't limit the use of RAM like SQL Express, so you may get a nice performance improvement as well.

We host the application internally on our intranet and require mobile users to establish a VPN connection for security. We also assumed we need to host it internally due to the accounting system using the same back end.
Never really thought about hosting it outside to solve my issue .... That may really have some merit. As long as I can gain the comfort on the security end (general ledger on the Internet) and do a test that the accounting system runs fast enough, it may work well. I know my office has a fiber optic connection... I assume zebra is sitting on a good backbone.

[Steve Workings]

Re: Mixing MySQL and MSSQL

Zebrahost co-locates with Softlayer, which was just purchased by IBM. Big, fast, secure, redundant, terrific customer service.

[drgarytraub]

Re: Mixing MySQL and MSSQL

I too cannot say enough good about ZebraHost. Truly outstanding in every respect: quality, availability, and GREAT support!!

[Ken Tjia]

Re: Mixing MySQL and MSSQL

My answer refer to our own internal server
now i realize the limitation when you app hosted by an outsider

having said that, migrate to one sql is the way to go
considering the routine of backup and trouble shooting.

[WayneH]

Re: Mixing MySQL and MSSQL

Just to close the loop for any who might search and review this thread later (knowledge sharing is the goal here in the forum :) )
Clive at ZebraHost was extremely responsive. I dropped Steve's name as the referral, but with how quick he emailed back and the level of detail he provided, you could tell this is his mode of business.

They can host a VPS for approx $130 per month at high bandwidth.
SQL web is $25 per month. Mere mortals can't buy that version, its for hosters to resell to guys like me. Financially, seems like a very logical choice. If I amortize the $130 over the useful life of on of my web machines, it is a break even deal, not counting the fact I don't have to store it, maintain it and it comes with support and backup.

I'll end up running a test and seeing if the speeds are acceptable going this cloud or hosting direction. I have no doubt my alpha app will be fine, its whether or not our accounting system runs fast enough with the sql server being over VPN.

Thanks for the ideas and direction.

[Steve Workings]

Re: Mixing MySQL and MSSQL

We worked with Zebrahost and Softlayer to model and demonstrate a web app (happens to be A5 - could be anything of course) through a VPN. We didn't get real deep into it as this part of the project is a bit speculative but our evaluations were that it worked really well -- as good as a typical https connection. I'm hoping this part of our project comes to fruition because I view this as a very good arrangement.