0

I have pretty old ERP system running on SQL Server 2005 (about 20GB database. Largest able has 2 million rows). Unfortunately, the ERP can't run on SQL Server 2008 or newer. I have a huge amount of legacy systems as well. Some of them are VBA frontend and stored procedures as backend and business logic. Some of them are C# applications. I know it is a very bad approach and I'm looking for better solution. I fetch data directly from ERP DB with SELECT WITH (NOLOCK).

If one day (hopefully soon) I will have to replace my ancient ERP with a new one, it will be a nightmare to update thousands of stored procedures, VBA and C# code.

The only solution I can think about is a definition of objects. For example, I plan to maintain P/N table which will hold all my part numbers and relevant data. On another hand, I have to merge the data from ERP to the P/N table every 5 minutes (either SSIS or stored procedure) to keep it updated. It is a huge load on my ERP DB. Considering tremendous amount of objects (warehouses, inventory data, logistics and financial transactions, engineering data) this approach will kill my ERP DB.

Can you propose a better solution?

  • Can you say more about why you are doing this? Why do you need to copy the (is it Part Numbers?) data to a new table if you can already access it directly? – Mike M Feb 4 '18 at 2:50
  • Mike, I'm more than familiar with the current DB structure. I know table names (about 500 tables) and schema. If I will replace my current ERP, I will have to rebuild queries in all my applications. Instead of doing this, I'd like to create a sort of data warehouse where I will store objects. My goal is to create an abstraction level between ERP and other applications. Moreover, the abstraction level can serve other objectives such as BI, integration with CR, etc... – IgorM Feb 4 '18 at 8:18
  • 1
    Sure. So then we're stuck with how to copy out the data frequently other than direct querying. What database system is hosting the ERP? My next question being, have you looked into any standard mirroring by the database system? – Mike M Feb 4 '18 at 11:27
  • 1
    Well, presumably they know a lot more about making it efficient than you or I do :-) . A quick google search suggests there absolutely is mirroring starting from 2005 SP1. I have to think you should try that before you try to reinvent it with your own query set. – Mike M Feb 4 '18 at 11:40
  • 1
    And I will accept it as the answer ) Thanks man – IgorM Feb 4 '18 at 12:57
1

If you really want to move lots of data off the source server often, maybe you should consider the db's native solution of

  • Replication.

You can organize it to push only what you want it to push.
It is made to operate in parallel to normal operation. There are lots of configurations to balance getting it thorough versus not bothering the server too much (at both ends). It can do things like read transactions logs with separate Services, avoiding running queries through the db engine at all.

There's a ton of information out there.
You can start with the official stuff at:
https://technet.microsoft.com/en-us/library/ms151198(v=sql.90).aspx
It walks you through a lot of the options, including
choosing an overall Replication type,
and considerations for Data Warehousing and Reporting scenarios.

Any internet search for more help on Replication, including on SO, will turn up a lot more.

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.