Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I have two Azure SQL Database, prod and dev.
How can i daily replicate the prod database into dev database?
I have tried to automate it by exporting and importing the BACPAC file but it takes too long time.
There is Data Sync service in Azure but I am not sure if it works in one direction.
Do you have any suggestions?
The Copy Database function could be an option for you:
From my experience, it's much quicker than the BACPAC method.
You can try "Geo-replication"
Go to your prod "SQL Database", Settings > Geo-replication
Configure your dev server/database to be readable "secondary"
All changes made at prod database , will be applied to dev database right away
Beware that you won't be able to change (insert,update,delete,create,etc) data at dev database while it is in the "readable secondary" role - only read (select)
You can write a batch file that exports the database to BACPAC file and then imports into the dev database.
You will need sqlpackage.exe utility and sqlcmd.exe utility
The script is simple:
rem drop dev database
sqlcmd -S myserver.database.windows.net -U username -P password -Q "DROP DATABASE [dev_database]"
rem Export production azure sql database to BACPAC file
sqlpackage.exe /TargetFile:"w:\temp\dev_db.bacpac" /Action:Export /SourceServerName:"myserver.database.windows.net" /SourceDatabaseName:"prod_database" /su:username /sp:password
rem Import bacpac file to dev database
sqlpackage.exe /SourceFile:"w:\temp\dev_db.bacpac" /Action:Import /TargetServerName:"myserver.database.windows.net" /TargetDatabaseName:"dev_database" /tu:username /tp:password
rem change edition for dev database
sqlcmd -S myserver.database.windows.net -U username -P password -Q "ALTER DATABASE [dev_database] MODIFY (EDITION='Basic');"
-
You can schedule this script in windows scheduler in your development computer.
Data Sync seems to be the way. It has bidirectional sync https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-data-sync-data-sql-server-sql-database?view=azuresql
The disadvantage is that it places some overhead on prod databases https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-data-sync-data-sql-server-sql-database?view=azuresql#performance-impact
