SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Providing datasets to customers - is replication the best approach? What is most commonly used?


Providing datasets to customers - is replication the best approach? What is most commonly used?

Author
Message
dev_etter
dev_etter
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 50
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
MysteryJimbo
MysteryJimbo
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5293 Visits: 15346
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.
MysteryJimbo
MysteryJimbo
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5293 Visits: 15346
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.
Carlaabanes
Carlaabanes
SSC Veteran
SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)

Group: General Forum Members
Points: 226 Visits: 474
i would suggest reporting services over https. its the same setup we have in my current organization.

Cheers! :-)
[url=http://coffeeandsql.com/][/url]
dev_etter
dev_etter
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 50
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search