Replication from on premise SQL 2019 to Azure SQL MI for reporting purposes

Question

Replication from on premise SQL 2019 to Azure SQL MI for reporting purposes

SCarter 21

Hi all,

Can anyone point me in the direction of a guide on setting up standard transactional replication from an on premise SQL 2019 to a Azure SQL Managed Instance. I'm struggling to get this to work and I can't find any documentation on doing it this way around, all the guides seem to be from the MI to on premise and they aren't helping me get this working.

Connectivity between the two isn't an issue (I can connect to the MI from SSMS on the on premise server), I have also successfully managed to replicate from on premise to SQL 2019 installed on a VM in Azure.

Thanks in advance!

0 comments

Answer accepted by question author

YufeiShao-msft 7,156

Hi @

You can refer to this article:

Standard transactional replication from on-premise SQL to Azure SQL Managed Instance

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

SCarter 21 Reputation points

2022-05-06T11:57:03.163+00:00

@YufeiShao-msft that's great thank you for that, that's perfect for a push subscription, is there any details on having the a pull subscription anywhere? Ideally I'd like to remove the load off the production on premise server and when I try I get an error saying that it is unable to connect to the distributor. If not no worries and thank you again, I'll mark as resolved if there is no guidance on this.
YufeiShao-msft 7,156 Reputation points

2022-05-10T00:56:52.127+00:00

If the reply above was helpful please mark as accepted answer so it can be helpful for other community members with same questions.

Regards

1 additional answer

Your answer

SCarter 21 Reputation points

2022-05-06T11:57:03.163+00:00

@YufeiShao-msft that's great thank you for that, that's perfect for a push subscription, is there any details on having the a pull subscription anywhere? Ideally I'd like to remove the load off the production on premise server and when I try I get an error saying that it is unable to connect to the distributor. If not no worries and thank you again, I'll mark as resolved if there is no guidance on this.
YufeiShao-msft 7,156 Reputation points

2022-05-10T00:56:52.127+00:00

If the reply above was helpful please mark as accepted answer so it can be helpful for other community members with same questions.

Regards

Answer 1

YufeiShao-msft 7,156

Hi @SCarter ，

This seems to be a permissions issue, the distribution agent process account does not have rights on the distribution, please verify this account is a member of the PAL, and in the db_owner role in the distribution database, has read permissions on the snapshot share.

https://dba.stackexchange.com/questions/20395/all-pull-subscriptions-cannot-connect-to-distributor?msclkid=32ae2f72cf3211ec9fec8fdcebd16ab3

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

SCarter 21 Reputation points

2022-05-10T07:17:37.823+00:00

That's great thank you for your help with this, marking as accepted.

Share via

Replication from on premise SQL 2019 to Azure SQL MI for reporting purposes

1 additional answer

Your answer