Replicate data in near real time from On Prem SQL server to Azure SQL

Question

Hi,

I have a scenario wherein I want to replicate the data from a Hyper-V Server 2025 Host with Windows Server 2019 Standard VM and SQL Server 2019 Standard, on premise to Azure SQL database.

I was planning across the transaction replication scope: https://learn.microsoft.com/en-us/sql/relational-databases/replication/transactional/transactional-…

 

I had the below queries:

1. Can I create additional tables/ views/ Stored procedures in subscriber Azure SQL database ? And Upsert data in the newly created tables?
2. would creating #1, have any impact on the sync performance issue
3. Can I add indexes on the tables getting replicates from on prem SQL to Azure SQL
4. Would there be any performance issue on the On Prem SQL server due to this replication set up?
5. Can I replicate data from tables that do not have any primary key

Any suggestions would be appreciated.

Answer 1

Hey **BharathKumarS-1759
**It sounds like you're looking to set up real-time data replication from your on-premises SQL Server to Azure SQL Database using transactional replication. Let’s tackle your questions one by one.

1. Can I create additional tables/ views/ Stored procedures in subscriber Azure SQL database ? And Upsert data in the newly created tables?
   • You can create additional database objects like tables or views in the Azure SQL Database subscriber, but you won't be able to replicate data into these newly created objects from the publisher. Replication is limited to the published tables.
2. Would creating additional tables/views have any impact on the sync performance?
   • Since these additional objects don't participate in replication, their creation should have no direct impact on the replication performance itself. However, keep in mind that they could potentially affect overall performance if they require significant resources.
3. Can I add indexes on the tables being replicated from on-prem SQL to Azure SQL?
   • Yes, you can add indexes to the replicated tables in the Azure SQL Database. Just ensure that the primary key is maintained on the replicated tables, as this is a requirement for replication.
4. Would there be any performance issue on the on-prem SQL server due to this replication setup?
   • Some impact on performance can be expected, as replication requires resources for managing changes and sending them over to the Azure SQL Database. The degree of impact can depend on the volume of changes and the hardware/resources of your SQL Server.
5. Can I replicate data from tables that do not have any primary key?
   • No, replication to Azure SQL Database requires that all tables have a primary key defined. If your tables lack primary keys, you’ll need to add them to enable replication.

https://learn.microsoft.com/en-us/azure/azure-sql/database/replication-to-sql-database?view=azuresql
https://learn.microsoft.com/en-us/sql/relational-databases/replication/transactional/transactional-replication?view=sql-server-ver17

Hope this helps! If you have more questions or need further clarification, feel free to ask!

Answer 2

PFB the responses:

1. Can I create additional tables/ views/ Stored procedures in subscriber Azure SQL database ? And Upsert data in the newly created tables?

Yes, you an create new objects and you can even update the data in the replicated tables but that wont sync back to publisher as it is 1 way sync.

1. would creating #1, have any impact on the sync performance issue

Not majorly as long the current scope is not choking up the DTUs

1. Can I add indexes on the tables getting replicates from on prem SQL to Azure SQL

Yes

1. Can I replicate data from tables that do not have any primary key

Based on my understanding, the tables need to have primary key for CDC aspects