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.