Standard transactional replication from on-premise SQL to Azure SQL Managed Instance
Are you planning to replicate selected database(s) or table(s) or stored procedure(s) or view(s) or user defined function(s) from on-premise SQL server to Azure SQL managed instance? Then you are at right place to achieve your goal with no downtime.
How it works ?
Data changes and schema modifications made at the Publisher (on-premise SQL server) are usually delivered to the subscriber(Azure SQL MI) as they occur (in near real time). The data changes are applied to the subscriber in the same order and within the same transaction boundaries as they occurred at the publisher; therefore, within a publication, transactional consistency is guaranteed.
By default, Subscribers to transactional publications should be treated as read-only, because changes are not propagated back to the Publisher
In this tutorial, you learn how to:
- Configure a On-premises SQL Server as a replication publisher.
- Configure Azure SQL managed instance as a subscriber.
- Monitor replication.
Prerequisites:
To complete this tutorial, you need following
1) On-premises SQL Server (publisher)
2) Azure SQL Managed Instance (subscriber)
3) SQL Login account on subscriber
4) On-premise SQL server agent account should be a member of admin group on publisher
Configure a On-premises SQL Server as a replication publisher
Step 1: Create new Publication
Right click on “Replication” and then new -> Publication
Click “Next”
select database from the list populated.
Choose Publication Type as “Transactional publication”
Select objects like tables that you want to publish as articles.
Note: A publication profile can be created on entire database or individual database object like tables, stored procedures, views, user defined functions or even a single article like a single table/view/ stored procedure.
Click “Next” if you do not need to filter the data in your publication or Click “Add” to begin filtering your publication.
Specify when to run the Snapshot Agent as “immediate”.
In Agent security section, click “Security Settings”
Select as shown below and click on “OK”
Note: Make sure the account on which your Publisher SQL Server Agent is running have permissions to create a folder or directory by adding that account to server administrator group or minimum permissions required.
Click “Next”
Choose “Create the publication” and click “Next”
Enter “Publication name” and Click “Finish”
If all steps status is success. Click on “Close”
Configure Azure SQL managed instance as a subscriber
Step 2: Create Subscription profile
create a blank database on the subscriber (Azure SQL MI server) end with similar database name that you want to replicate from publisher (on premise server)
Then Expand Local publications and right click on the publication that you created and select New Subscriptions
Click “Next”
Click “Next”
Choose Run agents at the Distributor and Click “Next”
Under “Add Subscriber” drop down, select “Add SQL Server Subscriber”
Enter Subscriber (Azure SQL MI) server name, Authentication, and username details
Select subscriber as Azure SQL MI and subscription Database and then click “Next”
Click (…) to set the security options.
Select the following options and under “connect to subscriber” section, enter Subscriber SQL server login account credentials and click “OK”
Click “Next”
Click “Next”
Click “Next”
Click “Next”
Click “Finish”
Click “Close”
Monitor Replication
Step 3: Launch Replication Monitor
Double click on row and go to “Distributor To Subscriber History” tab
To check if any pending transactions/ commands to be replicated, go to “Undistributed Commands” tab.
General Considerations:
1. All objects on the Subscriber that are referenced when adding a foreign key must have the same name and owner as the corresponding object on the Publisher.
2. It is recommended that constraints be explicitly named. If a constraint is not explicitly named, SQL Server generates a name for the constraint, and these names will be different on the Publisher and each Subscriber. This can cause issues during the replication of schema changes.
3. If a table is published for replication, it is not possible to alter a column in that table to a data type of XML if a publication snapshot has already been generated. To alter the column, you must first remove replication.
4. Read uncommitted is not a supported isolation level when doing DDL on a published table.
5. Adding an identity column to a published table is not supported, because it can result in non-convergence when the column is replicated to the Subscriber.
6. By default, the column add or drop in a published table will be reflected at all Subscribers.
7. During snapshot replication, a shared lock will be placed on all tables published as part of replication for the duration of snapshot generation. This can prevent updates from being made on publishing tables.
8. Subscribers receive transactions in the same order in which they were applied at the Publisher.
9. Tables published for transactional replication must have a primary key.
10. Temporal tables are not supported for transactional replication.
11. Schema changes should be made only at the Publisher, not at Subscribers (including republishing Subscribers). Merge replication prevents schema changes at the Subscriber. Transactional replication does not prevent the changes, but the changes can cause replication to fail.
12. You cannot drop columns used in a primary key from articles in transactional publications, because they are used by replication.
13. You cannot alter indexed views that are replicated as tables. Indexed views that are replicated as indexed views can be altered, but altering them will cause them to become regular views, rather than indexed views.
14. If an error occurs when applying a schema change (such as an error resulting from adding a foreign key that references a table not available at the Subscriber), synchronization fails and the subscription must be reinitialized.
15. All applications or ad hoc queries should read the data from subscriber by adding with(nolock) , if not the table at subscriber will be locked and transaction replication will get delayed due to lock on table.
