Hello,
I am wondering, have 2 questions in regards to transaction replication... probably best to ask my question with using an example, so I have 4 servers, Server A, B, C, D, and the SQL servers A, B, and C, will be publishers, and Server D I would like to be the distributor and Subscriber, is that possible for 1 server to act as multiple subscribers and maybe distributor?
on top of that, for the replicated databases, I want to add 1 more additional database, but in that database will have views, and those views which will be on SQL Server D, wondering if I can create views to point and do select statements from the replicated databases?
thanks in advance.
June 8, 2020 at 2:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
June 8, 2020 at 4:41 pm
An instance (not server, that word gets really confusing quickly) can be used as a distributor for multiple published. However, each publisher needs a separate distributor database. I haven't tried this, but it likely means you might need to script and customize names. The wizard might have issues here, and certainly you can't call all distributor database with the same name.
In many cases, we leave the distributor with the publisher. Can I ask why you want to move this to the subscriber?
You can have subscriptions from multiple publishers, but these need to be separate tables, and you could have collisions with objects. Are you trying to somehow centralize data from instances A, B, C, into D?
June 8, 2020 at 6:05 pm
hey SSC Guru, thanks for the info, yes trying to make SQL server D centralized as this will be the datawarehouse, and needs real-time data.
I did find some examples online that some did this, but you do bring up a good point with distribution, so multiple distributions cannot be on Server D correct? or can it, just named differently?
June 8, 2020 at 8:54 pm
You can, but why? I'd put the distributor on each publisher. Simpler and works well.
For moving this into a central warehouse, you can use some sort of structure to ensure the data goes to the same table, but that can be an issue. Replication sometimes breaks and trying to ensure you can restart this is hard. I'd be sure the subscriber tables for each publisher were separate. If I needed to combine the data then, I'd copy if from each subscriber table over to some fact structure.
June 9, 2020 at 5:51 pm
cool thanks Steve, i will try this out, just curious, have you tried creating a few on a database and fully qualified it with a replicated database and able to select from it?
example, on SQL server D, database results is the replicated database, and DW is another database but has a view and doing a select statement like:
use DW
select * from results.dbo.table1
would that be possible? I havent tried nor have a place to try this out, but wondering if you have or not?
Can I run this code? Sure, there are security implications, but this can work. Would I do this? No, because you've not littered a three part naming in code. The better way to handle this is with a synonym in DW.
Use DW
CREATE SYNONYM ResultsT1 for Results.dbo.Table1
go
select * from ResultsT1
Or better yet, if there are multiple databases, then use a view pointing to multiple synonyms for each table.
June 10, 2020 at 9:16 pm
Gotya thank you Steve for that info, good security best practice, thanks again for taking the time to answer 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply