Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

While developing a product, I've come across a situation where my group wants to store meta data for data entry forms (questions, layout, etc) in a different database then the database where the collected data is stored. This is mostly for security because we want to be able to have our meta data public facing, while keeping collected data as secure as possible.

I was thinking about writing a web service that provides the meta information that the data collection program could access. The only issue I see with this approach is the front end is going to have to match the meta data with the collected data, which would be more efficient as a join on the back end.

Currently, this system is slated to run on .NET and MSSQL.

I haven't played around with .NET libraries running in SQL, but I'm considering trying to create logic that would pull from the web service, convert the meta data into a table that SQL can join on, and return the combined data and meta data that way.

Is this solution the wrong way to approach the problem? Is there a pattern or "industry standard" way of bringing together two datasets that don't live in the same database?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

Well, the bigger problem is that effectively prevents you from maintaining consistency between the data sets. If you store the data like this, it will break.

I would probably store the metadata in the internal database alongside the data and set up replication of the metadata only to another server in DMZ where the public facing interface can access it.

share|improve this answer
    
Why will it break? There is a single copy of the meta data on its own server and the data collected is on its own server. Replication means the possibility of having one server get out of sync. I was thinking about replication, but I'm worried about the syncing issue. –  afuzzyllama Jul 3 '13 at 13:59
    
It will break, because you don't have transaction over the two databases. You can do careful two-phase commit in the application above the database, but one day you'll have a bug in it and something will only get written to one of the databases and than you'll have big problem hunting those records down. –  Jan Hudec Jul 3 '13 at 14:21
1  
As for syncing, neither application would ever look at both databases to mind the synchronization delay. And the database replication is well tested, so it is orders of magnitude more reliable than any your own distributed commit scheme. And unlike the distributed commit easy to fix by simply rebuilding the replica. –  Jan Hudec Jul 3 '13 at 14:29

Depending on how secure the data needs to be, if the web service is restricted to using a Database User account that only has security set up to access the specific data that is needed, then that may be enough to satisfy concerns. MSSQL has pretty robust security.

I don't think this is a superior solution to replication, but it is an alternative. So while the replication solution may be cleaner, using MSSQL's security to limit a database account in what it has access to will keep all the data in one place.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.