Dumping .SQL files on a MS SQL Server?

[rizd999]

Not sure if this has been asked or not... didn't see anything on my searches but does anyone know of an easy way to dump the MySQL CDR database to MS SQL Server 2005? I currently have backup dumps being done on a nightly basis on the ShoreTel Server and on to one of our MS SQL Servers. Now what can I do with that .SQL file on MS SQL Server so that I can use that data on that server. I tried SQL Server Import utility but that's for raw data import... not .SQL files. Has anyone tried importing data that way?

In the end, I would like to run queries/reports on the MS SQL Server instead of runnning it on the ShoreTel server itself.

Thanks in advance for any information on it.

[Palitto Consulting]

Personally, I would recommend you set up a second MySQL server instead. Then you can either import the static data dumps or you can do live replication from the server on the Shoretel box.

If you really want to do a conversion, you might be able to do it with text replacement, replacing MySQL-specific syntax with Microsoft-specific syntax in the SQL dump. Or perhaps you could use a linked server.

[rizd999]

I thought being an SQL file it would be more universal and I was just missing a way for MS SQL to interpret it. Everything has got to be proprietary in some way .

Now what do you mean exactly by text replacement? Does MySQL have different SQL commands than MS SQL and those are the text that would have to be reaplced? Hmmm, I see how that can be more complicated than it's worth.

I already had added the ShoreTel server as a linked server but running some test queries on it took a while which is one reason why I wanted to move the processing out of the ShoreTel box. I guess the second MySQL server option is looking to be the most efficient route, unfortunately.

Thanks for the information.

[Palitto Consulting]

Each vendor has their own SQL dialect. For example, you'll see references to ISAM or InnoDB, which are MySQL's storage engines, in the SQL. These are to ensure that when you execute the SQL in the dump, things are recreated EXACTLY as they were before. Microsoft does the same.

If you went the linked server route, I would recommend that you do a one-time conversion of the table structure then do a SELECT to pull the data from the linked server. This is a lot of work to maintain.

Setting up MySQL replication is very easy. It usually takes me about an hour, with most of that time spent waiting for the data dump to dump/import.