0

Every hour, we receive a database backup file via SFTP from a third-party vendor. We restore that database locally as a read-only database. The data in this database is an extract of our company's data from their web application. We restore it locally so we can run our own custom queries. The reason it's read-only is because it's just an import of our "real" data - and it will be overwritten the next hour when we receive and apply the next update from them. This arrangement is not under our control; it's the only mechanism that our vendor provides us with which to get a copy of our data.

For the purpose of illustration, let's say that database contains 1,000 tables. We then, in turn, want to share 100 tables with a different partner of ours but only specific rows from those tables, namely where they specific FacilityIDs. So it's not enough to limit which tables they can see, but we also need to filter the tables prior to providing them access.

It was suggested that I create a read-only user to provide access to the 100 tables to the "consumer" company "downstream" from us. The problem with creating a read-only user is because I believe that will only allow me to restrict which tables they see, but not filter, e.g. using a WHERE clause, that I need to apply prior to giving them access.

So I've been looking into replication as was recommended in a comment on a different question. Since our (1,000 table) database and the one we'd provide to the "consumer" company (100 table) database are both read-only, I'm not looking for two-way replication. I don't care about the changes they might make to the data we provide them. My goal is simply to provide them with a subset of what we've got. We're going to be providing them an update hourly in a similar manner, namely by providing a SQL Server .bak file to them via SFTP. If they make changes to the data, we're not interested in replicating them back to us.

I saw the following on MSDN:

You can't replicate or use a standby/read-only database as a publisher. Some system procedures will need to be created into that database which in a read-only mode is prohibited.

Is there anyway to have those system procs created in a "helper" database, leaving the published database in standby/read-only status at all times? Alternatively, is there an altogether better approach to this scenario?

Eliezer
  • 429
  • 1
  • 9
  • 20
  • No. But you could make the database not readonly to establish replication and then make it read only again. of course...kind of pointless to have replication on a database that can't change isn't it? Would be about a million times simpler to just make a backup and restore it on the other server. – Sean Lange Feb 13 '18 at 21:26
  • @SeanLange, in an effort to keep my question concise, I see I was a bit skimpy on some key details. This read-only database is restored from a backup that we receive hourly via SFTP from a third-party vendor. The data in this database is an extract of our company's data from their web application. We restore it locally so we can run our own custom queries. So the reason it's read-only is because it's just an export of the "real" data - and it will be overwritten within the hour. – Eliezer Feb 13 '18 at 21:39
  • If you overwrite it every hour why are trying to use replication? Or are you replicating from the other database to this one? Meaning that your read only database is the intended target of replication. – Sean Lange Feb 13 '18 at 21:41
  • Our full hourly restore from the third-party vendor contains many tables and we want to share only a small portion of them. I'm looking into replication as was recommended [in a comment on a different question](https://stackoverflow.com/questions/48694757/most-efficient-and-easiest-way-to-back-up-a-portion-of-specific-tables-hourly/48695696?noredirect=1#comment84392064_48695696) of mine. – Eliezer Feb 13 '18 at 21:47
  • So your readonly database is the target? You need to rethink this a little bit. How can you insert, update, delete from a readonly database? That is what you are asking replication to do here. Why not provide access to this via a readonly user instead? – Sean Lange Feb 13 '18 at 21:49
  • I'm looking for a one-way replication. We'd be providing a subset of read-only tables to the "consumer" company. – Eliezer Feb 13 '18 at 21:51
  • Give them access to this database with a readonly user. You can't have a database be both readonly and not readonly. A readonly database is intended for archiving permanent data which this is clearly not. – Sean Lange Feb 13 '18 at 21:52
  • This database can be read-only as far as I'm concerned. I don't care to persist any changes to it. I just want it to be replicated to the "consumer" company. The problem with creating a read-only user is because it's not only *which* tables they're allowed to access, it's also only specific data therein. In other words, I only want them accessing rows in those tables with specific Facility_IDs. Since it needs a query/filter, a read-only user won't work. – Eliezer Feb 13 '18 at 21:59
  • I think you need to read what you have posted here and ask yourself if you can make heads or tails of it. I can say for certain that I can't follow you here. I have told you many times already, you CAN'T replicate to a readonly database. And no idea how a readonly database would help you prevent a user from seeing certain rows. – Sean Lange Feb 13 '18 at 22:01
  • Sounds like a fair assessment. I've completely revised my question in an attempt to be clearer and to include all pertinent details. – Eliezer Mar 15 '18 at 19:04
  • Holy cow this is completely different beast than what you originally posted. It seems you are way overthinking and overcomplicating this. Why don't you just create some views that where clauses to filter the data that you want to allow access. Then you only allow select permission to those views. There is no need to make copies and copies of data. – Sean Lange Mar 15 '18 at 19:09
  • I agree with the "beast" terminology. :) That which I wrote towards the end of the post: "We're going to be providing them an update hourly in a similar manner, namely by providing a SQL Server .bak file to them via SFTP" is not my requirement; it's that of the "consumer company"... – Eliezer Mar 15 '18 at 19:23
  • LOL. But seriously....just create a view with a where clause and you are done. If you put that view on another database and have the users query there you don't even have to worry about recreating the view when you restore the new backup. It will just pull data from whatever the database is currently. – Sean Lange Mar 15 '18 at 19:27

0 Answers0