the best approach to copying a dev database to Prod in a different subscription
I have created a database in Development which now for the first time can be copied into production.
After this it would be good to use a more DevOps approach but for now I just want the initial production database
I created a Bacpac from Dev and Imported it into Prod but there are lots of issues with this
- The database doesnt appear within SQL databases in Azure, You can only see it when you go to the subscription and then resources
- It has exactly the same Server Admin details and users as Dev but I cant log into it at all with Power BI using any of them
- For example I can log in, in SSMS with the admin details fine. but not with Power BI (We couldn't authenticate with the credentials)
I have tried a few times but cant get any further with this set up
I then tried to do it via Powershell using the following code
The SubscriptionId in which to create these objects
$SubscriptionId = 'd017f57e-a193-42c3-a816-bf772d0602db'
Set the resource group name and location for your source server
$sourceResourceGroupName = "dev-uks-project-rg-$(Get-Random)"
$sourceResourceGroupLocation = "UKSouth"
Set the resource group name and location for your target server
$targetResourceGroupname = "prd-uks-project-rg-$(Get-Random)"
$targetResourceGroupLocation = "UKSouth"
Set an admin login and password for your server
$adminSqlLogin = "project_admin"
$password = "REDACTED"
The logical server names have to be unique in the system
$sourceServerName = "dev-uks-project-$(Get-Random)"
$targetServerName = "prd-uks-project-$(Get-Random)"
The sample database name
$sourceDatabaseName = "dev-uks-project-sqldb "
$targetDatabaseName = "prd-uks-project-sqldb "
The ip address range that you want to allow to access your servers
$sourceStartIp = "0.0.0.0"
$sourceEndIp = "0.0.0.0"
$targetStartIp = "0.0.0.0"
$targetEndIp = "0.0.0.0"
Set-AzContext -SubscriptionId $subscriptionId
Create two new resource groups
$sourceResourceGroup = New-AzResourceGroup -Name $sourceResourceGroupName -Location $sourceResourceGroupLocation
$targetResourceGroup = New-AzResourceGroup -Name $targetResourceGroupname -Location $targetResourceGroupLocation
Create a server with a system wide unique server name
$sourceResourceGroup = New-AzSqlServer -ResourceGroupName $sourceResourceGroupName
-SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminSqlLogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force)) $targetResourceGroup = New-AzSqlServer -ResourceGroupName $targetResourceGroupname
-SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminSqlLogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))
Create a server firewall rule that allows access from the specified IP range
$sourceServerFirewallRule = New-AzSqlServerFirewallRule -ResourceGroupName $sourceResourceGroupName
-FirewallRuleName "AllowedIPs" -StartIpAddress $sourcestartip -EndIpAddress $sourceEndIp
$targetServerFirewallRule = New-AzSqlServerFirewallRule -ResourceGroupName $targetResourceGroupname
-FirewallRuleName "AllowedIPs" -StartIpAddress $targetStartIp -EndIpAddress $targetEndIp
Create a blank database in the source-server with an S0 performance level
$sourceDatabase = New-AzSqlDatabase -ResourceGroupName $sourceResourceGroupName
-DatabaseName $sourceDatabaseName -RequestedServiceObjectiveName "S0"
Copy source database to the target server
$databaseCopy = New-AzSqlDatabaseCopy -ResourceGroupName $sourceResourceGroupName
-CopyServerName $targetServerName `
Clean up deployment
Remove-AzResourceGroup -ResourceGroupName $sourceResourceGroupName
Remove-AzResourceGroup -ResourceGroupName $targetResourceGroupname
However this created two new servers for both dev and Prod and two new resource groups for both dev and prod
I already have the resource group and the Server set up.
So the question is, whats the best process for me to copy the SQL Dev Database over to the Prod server, and I can actually log into it with Power BI using the same user details as I have in Dev?
Can you please confirm few things
- The sql type of Dev and Prod SQL server. Is it on prem or SQL Server on a VM or Azure SQL PaaS server
- can you give screenshot where you say you dont see the DB in sql server when using bacpac. Where are you not able to see it.
- How does PowerBI comes into picture and how are credentials created for the same.
Please get back to us with answers to these and we will try to help you.
Sign in to comment
Please help us with the above questions so that we can work further on this,