SQL Server Integrations
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
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.
Integrating SQL Server With Accounting and ERP Software
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
the SQL table structures are readily available, integration with data stored in a SQL Server database is a usually quite straightforward.
Integrating SQL Server with MS Excel
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.
Integrating SQL Server with MS Access
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.
Integrating SQL Server with QuickBooks
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.
The SQL Server integration method one chooses is driven largely by two factors —
whether the same data elements need to accessible by both systems,
and the frequency of data updates required by the business e.g. real-time, daily, weekly, monthly.
Infrequent updates from one
system to another need not be over-engineered; Frequent updates to two or more systems should not be under-engineered.