July 18, 2012 at 9:30 am
Our product has one OLTP MSSQL database per customer, all using the same schema. We are in the early phases of supplying a customer who uses MSSQL with a handful of specific recordsets.
Currently, our approach is:
*Create a new schema within each customer's database exclusively used for data sharing.
*Create a table within the new schema for each dataset.
- The table will contain a complete history at all times
- The table will have some flattened out id's and enumerations so that it is easily consumed by the customer.
- The table will be kept up to date with a regularly scheduled job that refreshes the transactional data within the window of the last x days.
- This table will be an article for replication, where the customer is a read-only subscriber.
The proof of concept is working and appears to have no problems thus far.
My question is, is this how other SQL folks would approach this? We were throwing around the idea of using OData to publish over the HTTP(s) but don't know much about OData, compared to what we do about SQL Replication.
EDIT:
We need to choose a solution that wouldn't result in maintenance nightmares when it comes to managing replication/data-sharing amongst several customers.
Thanks
July 18, 2012 at 10:15 am
Are you effectively providing a reporting mechanism for you customers to look at their history?
Can you publish SSRS reports securely?
Alternatively you could consider
-an XML report delivered by a secure webservice.
-CSV reports generated by SSIS which could be downloaded via FTP
The question is, how complicated do you want to go and how much time do you want to invest. Personally, I'm an advocate of keeping it simple so I'd go for the SSRS or SSIS solution.
July 18, 2012 at 10:16 am
I'll follow up by saying that replication to 3rd party customer sites will likely be a logistical nightmare with access policies etc.
Do what you need to do to make the information accessible but put some responsibility back on them.
July 18, 2012 at 8:51 pm
August 2, 2012 at 10:07 am
We do use SSRS for other needs. However this falls outside the role of typical report requirements. Our db is OLTP and we have often millions of records in several tables, and SSRS reports can only provide so much. Especially when it comes to 2008's 65k row limitation, something we're battling with our existing reports already.
In this scenario, the customer would like relational tables (with lookups and enumerations flattened out) exposed to them so that they can use them in their OLAP installation, or develop custom reports as they desire. Our intent is not to host or configure OLAP on our servers. We want to provde customers with the data they need to implement OLAP at their discretion.
It does stink that we need a vpn running site-to-site to allow for replication publications, and that could get ugly if more customers want that kind of solution. Currently only a few customers have the IT resources to support an exchange like this, and only one has actually shown interest in getting data like this. I doubt it'll stay at one customer for long though, when they show other customers what they can do with the same data.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply