Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

I have a Microsoft SQL Server 2005 Database inhouse I have to publish some information on the internet and I can't (for security policy) query this database from the web server.

So I came up with the solution of replicating the information (contained in 3 views) from my SQL Server to a MySQL server on the web server.

I have to schedule this synchronization (one way SQL Server always overwrites MySQL) every night. Ideally SQL Server should push updates into MySQL server (so security ninjas are satisfied).

I read some article about but I have no clue where to begin.

I'm familiar with PHP, Python. Preferably I'd like to realize this with the internal schedule of SQL Server and a stored procedure or a maintenance task

Thank you in advance

share|improve this question

1 Answer

up vote 2 down vote accepted

You'll want to look at SQL Server Replication. If you just want a one time nightly feed then you'll probably want Snapshot Replication. If you want a more real time feed then you'll want to look at Transactional Replication.

Now replication doesn't support replicating the views, so you'll need to replicate the base tables, removing any columns which aren't needed by the website then create the views on the MySQL database.

share|improve this answer
is it possibile to replicate views from source database as tables in the destination database? – eldblz May 24 '12 at 7:31
If your source view is of only a single table, you could perhaps define an Article at the Publisher that contains a subset of the base table columns. Otherwise you will need to replicate the base tables and define the views on the MySQL Subscriber – John Sansom May 24 '12 at 10:13
Eldblz, you can do ETL via DTS, but it isn't supported in the UI and is pretty hard to do. You'll be better off just replicate the base tables then create the views in the MySQL database. – mrdenny♦ May 29 '12 at 3:01
Premise: i need to export the database once a day or once a week. I'm thinking out of the box: what if i schdule a DB export in MSSQL (csv format) and then an import from MySQL? (in the middle a batch will push the csv via ftp) is a viable solution? – eldblz Jun 4 '12 at 9:38
Yeah, that would be a viable solution. That or you could just use SSIS to do it without needing the csv file in the middle. Just open a connecton to the MySQL database, truncate the destination table and push the new values from the view. – mrdenny♦ Jun 4 '12 at 18:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.