SQL server --> MySQL?

[Milesy]

Hi all,

Not sure if this is something anyone can really answer but here goes. I have taken over as it manager at a company. They have about 100 ms access front ends with odbc links to SQL server, pervasive and MySQL. I have discovered that I don't have a licience for SQL server, so I'm looking at porting the database from SQL server to MySQL. I have managed to achieve this, but have only just started relinking tables. Has anyone tried this before? Anything I need to watch out for? Or am I simply dreaming that this is going to work?

Thanks for any insights.

Milesy.

 

[Galaxiom]

Welcome to the forum. I am no expert on this but ODBC is supposed to take care of the translations. One of the more experienced guys will be able to tell you more in the next day or so.

However are you sure that the SQL Server was not the free Express version?

MS SQL Server 2008 R2 Express is a pretty substantial database server with a capacity to host databases up to 8GB. I have one with 35 million records and it still hasn't reached the limit.

 

[Milesy]

Hi,

Thanks for the quick reply.

Its SQL Server 2000. I asked the old IT Manager about it, and he told me he installed it years ago off a dodgy copy just so he could play around with it. When it went live he gave management the bill for a licence and they said no. It was to late to go back. Its not an express version. He has just turned a blind eye since then.

I looked at SQL Express 2008 months ago. I thought it was a 2Gb limit? Actually Googling it now, its a 10Gb on 2008 R2. That's more than enough (my largest database is around 4Gb currently).

Does the the 1Gb of Ram and the single CPU limitations create performance issues? I have around 300 users. My current server has an AMD Opteron 2.2Ghz and 4Gb Ram. It really struggles. If I get some new hardware (looking to go virtual) with a decent CPU with multiple cores. Windows Server 2008 R2 to go with it. Give it 6 -8 Gb Ram so Windows etc. doesn't chew into SQL's 1Gb.

I also read somewhere that its limited to running 1 query at a time? Or was that false? I can't find a reference to it now that I have started looking again.

Milesy.

 

[tehNellie]

The hardware limitations can pose performance issues for sure.

There are performance monitoring tools (not least perfmon for windows itself) that can give you a better idea of where your performance bottlenecks are, a good place to start is also rechecking and tuning the SQL for views/stored procedures and indexes. What works fine on a test database with a few hundred rows of data can grind a live production database with a few million rows to a complete standstill regardless of the hardware. You'd be surprised how many people never go back to a query once it returns the data they're expecting to see to see if it returns the data they're expecting to see efficiently.

Off the top of my head I can't remember the exact limitations of Express over a full SQL server license, but it is certainly limited in terms of functionality compared to a full licensed version of SQL Server. I believe you're limited to a certain amount of concurrent connections but I'd be suprised if you could only execute a single query at any one time.