Several methods are available to integrate SQL Server with an another system — SQL Server integration with MS Access, MS Excel, and commercial accounting and ERP systems are quite common.   Proper integration with SQL Server can significantly streamline business workflows, and avoid unnecessary (and often inefficient and error-prone) manual data management.   The engineering effort required to develop a SQL Server interface can vary significantly, so it is important to select the method most appropriate to the business need.

SQL Server integration methods can include ETL scripts that support file-based data extraction and import, ODBC or webservices connections, or API-level integration.   Each method is capable of supporting one-way or bi-directional data integration.  ODBC, webservices or API-level integration methods are often the best approach for continuous, real-time data integration needs.   The method one chooses is driven largely by two factors: whether the same data elements need to accessible by both systems (vs. a logical flow of data from one system to the other), and the frequency of updates needed by the business e.g. real-time vs. once a month.

For example, if your accounting system needs customer service hours from an external SQL Server database once a month for invoicing, an ETL script to extract and import the data is often the most cost-effective approach.   If on the other hand the accounting system needs customer service hours every day, then system-level integration is usually the best choice.

Popular accounting systems we have experience with include: QuickBooks, Sage 50, Microsoft Dynamics NAV, SAP Business One, Sage MAS 90 ERP and Sage MAS 200 ERP, and NetSuite.   Common ERP systems we have SQL Server data integration experience with include: SAP, Epicore, NetSuite, Oracle JD Edwards, Microsoft Dynamics GP, and Sage Accpac ERP.  SQL Server data integration with any accounting or ERP software package is feasible when the software package uses a relational database; and the entire relational database schema is accessible to the outside world.  

In our experience, the most common technical hurdle to integrating external SQL Server data with an accounting and enterprise resource planning (ERP) system is the vendor's inability (or unwillingness) to expose their underlying SQL table structures.   In circumstances where the SQL table structures are readily available, integration with data stored in a SQL Server database is a usually quite straightforward.

Two popular business scenarios for integrating MS Excel with a SQL Server database include using MS Excel as a flexible front-end calculation engine to perform flexible "what-if" business functions that would otherwise be too expensive to build into SQL Server; another popular scenario involves using Excel for SQL data selection and interactive charting functions, which can also require integration with SQL Server Analytic Services (SSAS).    Both SQL Excel integration scenarios can provide a powerful, cost-effective way to manipulate large sets of data stored in a SQL Server database — and take advantage of Excel functions that are familiar to many power users. Learn more about integrating MS Excel with SQL Server.

Many businesses manage core business data using a highly-customized version of MS Access, but eventually outgrow the limited capabilities that MS Access can offer.  More Users, more data, the need to support remote Users in different locations are among the primary factors that necessitate migration of an in-house MS Access system with SQL Server.  PCA specializes in migrating MS Access to SQL Server, to handle growing business needs. Learn more about Excel and/or MS Access to SQL Server migration services.

SQL Server - QuickBooks integrations can take on several forms. The easiest, low-cost approach is file-based integration, where a select set of data is exported from SQL Server (in CSV format), then imported into the QuickBooks application. This approach is very common for occasional/intermittent data integration needs, for example exporting work orders from SQL to support monthly invoicing in QuickBooks.    Using the same approach it is also common to export customer or invoice data from QuickBooks, and import into SQL Server, for the purpose of reconciling accounting and service functions.

File-based data integration can also be automated via SQL Server "jobs" to support more frequent/repeatable data update needs.    An alternative SQL Server - QuickBooks integration involves connecting the two applications in "real-time" via the QuickBooks Application Programming Interface (API).     This approach requires more engineering, but is very useful for high frequency data integration needs E.g. when accounting and services functions must mirror each another during the work day.