0
  • Our company uses an application with a SQL Server database where we store all of our Project Item data.

  • Our company's Portal Website uses a MySQL database that holds other Project information (location, start date, billing agent etc...)

  • These two databases are currently only "connected" via a read only ASP.NET API on SQL Server.

We want to expand on what the SQL Server database currently offers us, however we don't want to make any changes to the SQL Server database considering it is also updated by the company that maintains the application that uses it.

For example, the SQL Server database doesn't currently have any change tracking and doesn't send notifications to relevant parties when key data is changed.

Our current thinking is, we create a lightweight duplicate of the SQL Server database on our Portal's MySQL database (only taking over the information we need) and periodically take over changes via our ASP.NET API, which should allow us to extend the database to safely implement change tracking, increase automation with change notifications and some other nice stuff.

As many of you have probably picked up, I'm pretty new to all this myself so any and all guidance is appreciated. My questions are:

  • Does this sound like an efficient / effective way to get what we want?
  • What are the advantages / disadvantages of our current trajectory?
  • What better / alternative ways are there of reaching our goals?
  • Any other questions I should be asking?

I believe the SQL Server version is 2012.

  • 1
    Check your licence for the application allows you access to the SQL Server database. – Michael Green Apr 18 '18 at 20:29
  • Perhaps there is a 3rd party took that lets you have MySQL as a Replication Slave to SQL Server? (Oracle has a tool for doing Oracle -> MySQL.) – Rick James May 5 '18 at 18:59
0

You do have Change Tracking in SQL 2012 version and you can use that to set up alerts for key data changes. And regarding, getting data from MySQL, you can create a linked server from MSSSQL server pointing to MySQL and read the data directly using OPENQUERY to sync the data between both.

See this link to set up linked server:

https://www.mssqltips.com/sqlservertip/4570/access-mysql-data-from-sql-server-via-a-linked-server/

That would be the easiest way I believe. There are other ways too as I know.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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