Setting up Replication on a SQL Managed Instance

Question

Setting up Replication on a SQL Managed Instance

DanielP

SSCrazy

Points: 2302
More actions
November 15, 2021 at 3:49 pm

#3953215

Hello,
I have searched and found some articles on setting up replication in SQL MI between SQL Instances and going to on-premise but is there any article on how to setup just to replicate to a database on the same Managed Instance?
I think the issue I am running into is with the setup\permission of the file share on the Azure storage account. I am troubleshooting that with our network person.
But my question would be is this the best approach to replicate data to another database on the same SQL Managed Instance. Can't use database mirroring, log shipping, etc. anymore correct?
November 16, 2021 at 12:30 pm

This was removed by the editor as SPAM
November 16, 2021 at 12:31 pm

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

DNA_DBA SSCrazy Eights Points: 9107 More actions · Answer 1

Do you mean replication to/from one Managed Instance? Why do you need that and not just use cross-database queries?

If you mean between Managed Instances then replication is the only SQL Server way for keeping two separate servers in sync as mirroring, AG and log shipping are not available.

Depending on how much data there is, how often it is changing etc then Data Factory might be a way of copying data between servers.

DanielP SSCrazy Points: 2302 More actions · Answer 2

I am looking to replicate most or all of the tables from Database1 in SQL MI Instance 1 to Database2 in SQL MI Instance 1 (same instance). We want to push the reporting code onto it's own database.

Thanks,

Daniel

DNA_DBA SSCrazy Eights Points: 9107 More actions · Answer 3

If you have Business Critical tier you'll have a read-only instance available to you.

On General Purpose, transactional replication is probably the easiest way of getting another copy of all the data in a database. Guess you already know but check the size of the subscriber database, iops is based on disk size so the bigger the file (even if not used) will give better performance as it will be on a faster disk which may be important for reporting.

DanielP SSCrazy Points: 2302 More actions · Answer 4

Thank you. I will check on the Business Critical tier. Currently trying to resolve to resolve, "2021-11-16 16:15:35.08 Failed to connect to Azure Storage '' with OS error: 53.' with setting up the replication.

DNA_DBA SSCrazy Eights Points: 9107 More actions · Answer 5

Check that port 445 is open outbound in the NSG, might be that