Hi,

Due to security reasons and the fact our website is running php/mysql we want to export tables from our MSSQL server at our office to our MqSQL server at our hosting company.

The reason for this "connection" is that the backend of our website (user-management and so...) needs to based on the data used my our companysoftware. So I was thinking to export the data once a dag (or often if possible) to make sure all the changed that are done in the office (new users, changes, ...) are reflected in our website.

But due to the fact the sql-database is very large it would alsow be handy to give "a search routine" to every table for exporting. For example: only records with status "active" or "editted last 15 days".

Who know's how to do this? Or can give me some tips about "connection" the data of our companysoftware to the backend of our website.

Greetings,

cK

Hi,

For connections, download the ODBC driver for MySQL and setup an ODBC data source for your mysql table. You can then use the MSSQL tools to export your data pretty seamlessly.

Cheers,

Alex
--
Gossamer Threads Inc.

As Alex mentioned, you can set up an ODBC connection to the MySQL database.

In terms of transferring data, you should use the Data Transform Service utility (DTS) in SQL Server Enterprise Manager, you can write queries within the DTS and also schedule the DTS to run at a certain, although I would run the DTS job manually before scheduling it. OR for faster performance, you could use the Export/Import Utility directly since loading a shared database on a shared SQL Server via Enterprise Manager is a pain.

Hope this helps.
========================================
Buh Bye!

Cheers,
Me

What do you mean exactly with 'is a pain'? What do you suggest in my case? Because won't using Export/Import Utility mean I need to export the whole database?

And there's one thing I'm still missing: I add a ODBC to my mysql database and then 'push' the data to the database? O do I 'get' the data from the MSSQL database with php/mysql/odbc?

The pain is that if you are on a slow Internet connection (like dialup, even ISDN), loading the remote SQL Server database in Enterprise Manager is a "PAIN".

No, the Export/Import Utility and DTS jobs can be configured to export certain tables, not the whole database.

By using either Export/Import Utility or the DTS in Enterprise Manager, you don't have to do the export/import process via scripts, you can do it in Enterprise Manager.

Do you even use Enterprise Manager? If not, then I would use it and learn about the Data Transform Service (DTS) and the Export/Import Utility client tool, outside of Enterprise Manager.
========================================
Buh Bye!

Cheers,
Me

No, therefore I just read http://www.databasejournal.com/features/mssql/article.php/1432371

So the best thing to do would be: Use the EM to export and 'push' certain tables to our remotely hosted sql-server where ODBC in installed (by using our dsl-cionnection)?

Correct?

Two additional questions:

- Does EM need more ports than 80 are can this be done over the normal internet port? (Due to strict firewall settings)
- Can EM be configured to export not the whole tables but only certain fields (with the where statement or by a search routine)?

Within EM, you would use the DTS utility OR the Export/Import Utility (outside of EM) to take the data ("export") and put the data into your remote server's table ("import"). You can automate this task via DTS or within the Scheduled Jobs utility in EM.

* Does EM need more ports than 80 are can this be done over the normal internet port? (Due to strict firewall settings)

- Yes, depending how you have configured your EM (client), you can use standard TCP/IP or piped IP connections to remote databases. You should double check to see what port your MySQL database is running under, the default port for MySQL is 3306, the standard port, of course, for SQL Server is 1433, although hopefully more people will close down that port and open another port, since many hackers have been able to use port 1433 as a port to hack SQL Server databases (unpatched).

* Can EM be configured to export not the whole tables but only certain fields (with the where statement or by a search routine)?


- Again, I have answered this question, you can use DTS and run custom queries to pull certain fields from one database and export them to another database. Please refer to the Microft TechNet website, or peruse the help files and demos that come with SQL Server Enterprise Manager.
========================================
Buh Bye!

Cheers,
Me

Many thanks...