wondering if it's possible to replicat into a different schema using Transactional Replication?
My situation is that I have 3 DB servers, each hosting it's own instance of our software, in three different locations. I would like to grab the data from each database and replicate them to a central server for reporting in Azure. I'm seeing if it's possible to combine them into a single database as opposed to having to pay for 3.
e.g. (hope this makes sense)
Server 1, db=MySoftware, Table=dbo.Invoice -> replciation to db=MySoftware, table=server1.invoice in Azure SQL server
Server 2, db=MySoftware, Table=dbo.invoice -> replication to db=MySoftware, table=server2.invoice in Azure SQL server
Server 3, db=MySoftware, Table=dbo.invoice -> replication to db=MySoftware, table=server3.invoice in Azure SQL server
I'm not worried if it's not possible, more of a curiosity thing.
Cheers
Best Answer
Yes, this approach is feasible; it just requires the right setup. I'm going to assume you'll be setting up replication via the wizard because any other way is basically like trying to navigate your way out of a series of caverns with a terrible flashlight. This answer is a bit wordy and light on instructions because it combines links to a few walkthroughs that are just better documented. I'm only going to draw attention to the key items you need to adjust in order to achieve your desired setup.
First, you must define publications at each individual server. When specifying your articles in each publication, you need to be on the lookout for the Destination Object Owner setting. You can specify a new schema for your articles to get pushed to. I assume you'll want this to be different for each publication.
The above screenshot was taken from a SQL Server Central Walkthrough on how to set this up within the publication, which can be found here.
After you have completed configuring your publications and distributor, you now setup what I've heard referred to as a central subscriber. To do this, you basically setup subscriptions to point to the same subscriber database. This SQL Server Central Article walks through the process, but because you've setup your publications to remap the tables to different schemas (unlike the article), I believe you can initialize each publication via snapshot, which should hopefully save you some time on initial setup and anytime down the line you need to reset the subscription. It's only when you are combining tables within the subscriber that one publisher snapshot will overwrite the data from a different publisher's snapshot. I don't believe this will occur in your setup, but test to be certain.