Basic question: Simplest way to share company databases

Question

My company is looking to access large quantities of data from another company we have good relations with. Currently we are using a web interface to interrogate their information, however it is quite limited in its functionality (set reports), and the available data is vast. At the moment we have limited technology resources to develop ways of mining this data, and though I am pretty clued up on basic database construction, this is something totally new to me.

If the data was small enough to be manageable, I could request a CSV file to be sent to an FTP server, pull it into the software we have (Access, Tableau) and query it from there. However, there's going to be loads of it.

• So what are my options here?

From the little I know, the simplest thing would be to query source data. Are there any tools that would do that outside them using another API?

Similarly in my head I could foresee a way to send new data to a SQL server somewhere, and then query/link to it.

• Is this possible?
• Is this how SQL Server works?
• How much effort would this take on their part?

I am not looking for a full schematic on how to do this (though I wouldn't turn one down), but just some clear terms and tools I can research on my own would be enough.

Essentially, we are meeting with the company to discuss how best we would do this. They currently have a limited UI for customers, but are interested in expanding. We have very little database infrastructure but are also eager to expand and looking for tools/products that will help us. We don't have SQL Server at the moment - we don't hold much data here, hence why we rely on querying third party data. So without them building a new interface for customers, how would one go about sharing this data considering the volume is considerable?

((Update re data size: Apparently we're looking in the region of 1-2 million rows a day with, probably, about 50 fields (Mostly short number and text < 50)....I don't know exactly what that translates into in terms of Mb/day, but suspect it's going to be too much to handle over email on anything over a daily basis))

Answer 1

It's not really how SQL Server works and I think you're going to see some underwhelming real life implementations of the data distribution process.

1. Many data publishers (post offices, automotive and parts suppliers) will publish a set data format document along with full monthly data extracts usually in a CSV or similar text format followed by ongoing updates-only extracts. The duty of pulling updates over SFTP, creating a database schema, loading it, and updating it becomes the customer's problem.

   Usually a customer will then allocate a unique ID to each table which they'll use for their own referential integrity and to seperate it from the key used by the provider; as providers often delete or reallocate records (even though we know they shouldn't), and you can't have a record of a part sale disappear from someone's invoice just because that part is no longer supplied or something.

2. Some inter (or intra) company transfers are done literally as entire database backups. In this case you could have a weekly full backup and daily differential backups transferred over SFTP and restored on each client. This requires an outage window on your side to do each restore though.

3. There are other options. SQL Server Replication (either snapshots, but maybe not daily snapshots because these can't be incremental, or transactional which is an ongoing stream of changes) but it's mostly avoided between companies because they don't want to expose SQL Server to the internet; and it fails too often and requires too much knowledge to troubleshoot and keep going on the client side.

4. Some data publishers use APIs but only when you're sending a request off; i.e. send a part number get back a part record. It's not usually used to publish complete data sets. And it requires programmers, there's no native SQL interface for it anymore.

I think 1 is the standard way to go. If they want to go the extra mile they can provide a database schema and routines to do everything (pull an initial data stream, then apply changes every day). It's not difficult but it's not trivial either.

2 is the easiest by comparison but also the ugliest.

They can do something in between. Provide a full backup, and then BCP export only new records each day, and provide a script to load these new records into the database. But records do change and get deleted, despite best laid plans, and then those become problems to deal with in this way.

As for your comment on not using SQL Server currently, the free edition (Express) can hold 10GB of data, which is really quite a lot when you get down to it.