0

We have a production DB with 10k tables (4TB). It is currently in an availability group so all the data is being streamed to a replica copy - mostly for redundancy. We want to make a dedicated data warehouse with the important tables ... cleanup of the 10k is recommended, but will take a while.

Can "replication" be used to push the key 200 tables over to the data warehouse IN ADDITION to the availabiltiy group? The microsoft docs on the topic make me think they are using replication synonymously with the Availability group. I mean the kind of replication you can turn on for individual tables... "transactional" - I think.

Adding the warehouse server to the availability group is not preferred because 1. large amount of data not really needed 2. I'm told I won't be able to add/change indexes on this replica copy (which would be nice for DW needs) 3. The DW has 12 cores, existing servers in the AG have 28. 4. I'm told that if I am given access to read from the DW replica, I would have to be given ability to read from the primary (seems suspect) - and they would prefer not to have anyone with read rights to primary.

Jon Blomiley
  • 1
  • Ideally you'd want to be able to replicate your key tables from the AG secondary replica. I've read that this might be possible but is currently unsupported. The best advice I could give would be to look at change tracking or CDC, this has to be on the primary replica unfortunately but CDC is pretty efficient I think, it uses the same methods to access the log that AG sync does. – Stu Jun 04 '21 at 22:22
  • "Ideally you'd want to be able to replicate your key tables from the AG secondary replica." -- YES!! that would be ideal. – Jon Blomiley Jun 07 '21 at 12:59

0 Answers0