Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Providing datasets to customers - is replication the best approach? What is most commonly used? Expand / Collapse
Author
Message
Posted Wednesday, July 18, 2012 9:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 18, 2012 7:31 AM
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
Post #1331562
Posted Wednesday, July 18, 2012 10:15 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,362, Visits: 15,269
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.
Post #1331608
Posted Wednesday, July 18, 2012 10:16 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,362, Visits: 15,269
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.
Post #1331610
Posted Wednesday, July 18, 2012 8:51 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 8:32 PM
Points: 80, Visits: 399
i would suggest reporting services over https. its the same setup we have in my current organization.

Cheers!
[url=http://coffeeandsql.com/][/url]

Post #1331888
Posted Thursday, August 2, 2012 10:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 18, 2012 7:31 AM
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.

Post #1339332
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse