configure replication from Iaas SQL Server to SQL Managed Instance

Ray R. Mosely 1 Reputation point
2022-09-06T15:54:43.29+00:00

I find a lot of procedure fragments for this, but I am having trouble putting all the pieces together. I have two IaaS servers, SQLPROD2 and SQLDEV, and one MI called SQLPROD. SQLPROD2 has a large DB that I want copy to SQLPROD using transactional replication. SQLDEV is the acting distributor. I am looking for a complete set of instructions to accomplish this setup.

SQL Server on Azure Virtual Machines
{count} votes

1 answer

Sort by: Most helpful
  1. Ray R. Mosely 1 Reputation point
    2022-09-08T12:07:05.09+00:00

    Thank you for your response, however my question was not about data migration. Both links provided point to articles on migration and not articles for replication. I have still not found articles specific to my problem. We need a solution without the downtime of backups and restores, etc. I have actually succeeded in setting up the replication as of yesterday from IaaS SQL Server to SQL MI.

    The apparent lack of documentation makes it difficult to know what the prerequisites are. SQL MI does not exist in a Windows Domain that I can access, therefore

    1. many service accounts, process accounts, and connections accounts useful in SQL replication do not work.
    2. Error messages are cryptic, often just saying that a process could not connect, without saying which process or which connection.
    3. An Azure storage account with its credentials is necessary.
    4. I had replication metadata get corrupted to the point where I needed to change to a different distribution DB server. The original distribution DB server could not be repaired.
    5. I ran a clean up Stored Procedure that deleted all of my publications including one that was in service.
    6. A standard replication configuration process can use the same accounts for different things, such as agent service account and connection account. So, for example, in my final iteration of getting this to work, the Distribution DB and snapshot could not connect to SQL MI. I was unable create two credentials in the configuration process, but I was able to override the connection credentials. This was the final step to getting replication working.
    7. and other issues. These are the big ones.

    As far as I know, the process of replication from on-prem or IaaS SQL servers to SQL MI is not documented, and the error messages from running a standard replication configuration are almost useless. If documentation related to setting up replications from IaaS SQL Server to SQL MI exists, I would still be very interested and curious.

    0 comments No comments