Publish SQL Server data to clients from saas website with multi-tenant database?
We maintain a Software as a Service (SaaS) web application that sits on top of a multi-tenant SQL Server database. There are about 200 tables in the system, this biggest with just over 100 columns in it, at last look the database was about 10 gigabytes in size. We have about 25 client companies using the application every entering their data and running reports. The single instance architecture is working very effectively for us - we're able to design and develop new features that are released to all clients every month. Each client experience can be configured through the use of feature-toggles, data dictionary customization, CSS skinning etc. Our typical client is a corporate with several branches, one head office and sometimes their own inhouse IT software development teams. The problem we're facing now is that a few of the clients are undertaking their own internal projects to develop reporting, data warehousing and dashboards based on the data presently stored in our multi-tenant database. We see it as likely that the number and sophistication of these projects will increase over time and we want to cater for it effectively. At present, we have a "lite" solution whereby we expose a secured XML webservice that clients can call to get a full download of their records from a table. They specify the table, and we map that to a purpose-built stored proc that returns a fixed number of columns. Currently clients are pulling about 20 tables overnight into a local SQL database that they manage. Some clients have tens of thousands of records in a few of these tables. This "lite" approach has several drawbacks: 1) Each client needs to develop and maintain their own data-pull mechanism, deal with all the logging, error handling etc. 2) Our database schema is constantly expanding and changing. The stored procs they are calling have a fixed number of columns, but occasionally when we expand an existing column (e.g. turn a varchar(50) into a varchar(100)) their pull will fail because it suddenly exceeds the column size in their local database. 3) We are starting to amass hundreds of different stored procs built for each client and their specific download expectations, which is a management hassle. 4) We are struggling to keep up with client requests for more data. We provide a "shell" schema (i.e. a copy of our database with no data in it) and ask them to select the tables they need to pull. They invariably say "all of them" which compounds the changing schema problem and is a heavy drain on our resources. Sorry for the long winded question, but what I'm looking for is an approach to this problem that other teams have had success with. We want to securely expose all their data to them in a way they can most easily use it, but without getting caught in a constant process of negotiating data exchanges and cleaning up after schema changes. What's worked for you? Thanks, Michael
November 15th, 2012 1:56am

There is so many solution possible... I think that simplest solution will be to use transactional replication with filter: http://msdn.microsoft.com/en-us/library/ms151775.aspx
Free Windows Admin Tool Kit Click here and download it now
November 16th, 2012 1:40am

Hi Boris, Thanks for the OLAP suggestion I think I'll certainly put that on the table for discussion, you make a good point that we can deal with schema changes ourselves as the data goes from the SQL relational DB into the cube. Change data capture may also be of use to us because I'm not sure whether it's safe (capacity-wise) for us to move forward with a solution that involves sending ALL of our clients' data to them every night, rather than just what has changed. How were you envisaging exposing the cube given we have upwards of 20 clients who may want to access their own data, but we absolutely must prevent leakage of data between clients. We do currently use a cube for some reporting, and expose it to (one or two) power-users via the HTTP data pump. Unfortunately we've found that role-based dimension data security requires Windows auth to work, and we're web-based with our own custom authentication, so we can't really expose this any wider. Were you thinking to build a separate cube per client and then spit each one out for them every night for their own reporting usage? We're already quite heavily invested in in SQL Server Reporting Services Report Models, the support for custom authentication is excellent - but to our frustration we've heard that these are being deprecated. I suppose a possible roadmap for us may include the "BISM" for SQL 2012 - but our current desire to stay away from SharePoint seems to rule this out for us at the moment. Cheers, Michael
November 21st, 2012 4:35am

Thanks, Piotr. So if our clients want the SQL database inside their four walls - how would you expose the replicas? As a *.bak file that we update every night that they can download via a batch job? Or we set up VPNs and so they can actually have the subscriber database sitting on their server but with an open TCP/IP channel to our publisher? Ideally we'd would like them to have some flexibility over how much data they choose to pull, and how frequently.
Free Windows Admin Tool Kit Click here and download it now
November 22nd, 2012 3:49am

I just thought I'd update the question and close it off for the benefit of anyone facing the same dilemma. We had a good internal discussion covering several options, including those suggsted here, and eventually opted to go stick with our current approach of exposing the data via a custom-built webservice API for clients to pull as & when they need. My concerns have been partially relieved with some planned changes in approach: 1) Each client needs to develop and maintain their own data-pull mechanism - in many cases this has the advantage of flexibility. For those that don't/can't, we can author a basic consumer for them. 2) Our database schema is constantly expanding and changing - our plan is to automate as much as we can. We'll minimise breaking schema changes where we can, use automation to notify clients of upcoming changes, and recommend simple precautions to avoid data truncation like clients defining large text fields even when only small fields are expected. 3) We are starting to amass hundreds of different stored procs built for each client and their specific download expectations, which is a management hassle - automation again. 4) We are struggling to keep up with client requests for more data - you guessed it, automation. What I'd add is that we'll try to focus on making the self-service interface (API) as open and comprehensive as we can and reduce the amont of one-off implementation work we need to do. Thanks for your input everyone. Michael
December 11th, 2012 7:13am

Thank you - we're still making slow and gradual progress on our approach, but these are good suggestions too. One of our clients is actually prodding us about Azure at the moment, so this might be a way to dip our toes into it. Michael
Free Windows Admin Tool Kit Click here and download it now
February 24th, 2013 12:51pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics