Azure SQL Database – How to Copy Between Azure Subscriptions

azure-sql-database

Getting straight to the point, is this even possible?

I have successfully copied an Azure SQL database within the same subscription. In doing so, I kept it simple by copying the database to the same server (within the subscription). I used the Powershell commands Add-AzureRmAccount and New-AzureRmSqlDatabaseCopy.

I have tried executing the Add-AzureRmAccount twice (once for each subscription) but this seems to replace the first subscription with the second in the context as the New-AzureRmSqlDatabaseCopy command reports that it cannot find the specified resource group (which exists in the source subscription).

All that said, if this is something that is possible, what are the steps / commands that I need to execute?

Best Answer

Create a new Azure SQL Server on a different resource group.

New-AzureSqlDatabaseServer -Location "East US" -AdministratorLogin "AdminLogin" -AdministratorLoginPassword "AdminPassword"

Copy the source database to the newly created Azure SQL Server.

Start-AzureSqlDatabaseCopy -ServerName "SourceServer" -DatabaseName "Orders" -PartnerServer "NewlyCreatedServer" -PartnerDatabase "OrdersCopy"

Move the resource group of the Newly created Azure SQL Server to another subscription.

Move-AzureRmResource -DestinationResourceGroupName [-DestinationSubscriptionId ] -ResourceId [-Force] [-ApiVersion ] [-Pre] [-DefaultProfile ] [-InformationAction ] [-InformationVariable ] [-WhatIf] [-Confirm] []

Related Solutions

Azure SQL Database – Creating Server with Different Collation

The collation for user databases can be specified for SQL Azure Database user databases nowadays (e.g. CREATE DATABASE YourDatabase COLLATE Latin1_General_CI_AS;).

EDIT:

The logical Azure SQL Server collation is fixed at SQL_Latin1_General_CP1_CI_AS and is not configurable (as of this writing). However, this collation is only used for system objects in master, tempdb, and user databases. It does not affect the collation of user objects in tempdb, which is the collation of the current Azure SQL Database context (not logical SQL Server) unless otherwise specified. This behavior differs from on-prem and IaaS SQL Server instances where the instance collation also determines the tempdb default database collation used for both system and user objects, therefore having a more significant impact.

With this in mind, you might think of system and user collations orthogonally in the Azure SQL Database world because you won't run into collation conflict errors in the user space as long as you use the same collation consistently. Also, if you use either Latin1_General_CI_AS or SQL_Latin1_General_CP1_CI_AS as your user database collation, you'll still get the same sorting and comparison behavior as system object names because Windows sorting and comparison rules are used for Unicode data and system object names are stored as nvarchar(128) (a.k.a sysname).

SQL Server – Detecting Push-Subscription Information on Subscriber Side

You have 4 tables For transactional replication (push subscription) at the subscriber's side:

MSreplication_objects

contains one row for each object that is associated with replication in the Subscriber database

MSreplication_subscriptions

contains one row of replication information for each Distribution Agent servicing the local Subscriber database

MSsubscription_agents

is used by Distribution Agent and triggers of updateable subscriptions to track subscription properties

MSsnapshotdeliveryprogress

is used to track files that have been successfully delivered to the Subscriber when a snapshot is being applied

you can use this columns to monitor the status for your replication at the subscriber's side

SELECT login_time,
       last_sync_status,
       last_sync_summary,
       last_sync_time,
       spid 
FROM dbo.MSsubscription_agents

Best Answer

Related Solutions

Azure SQL Database – Creating Server with Different Collation

SQL Server – Detecting Push-Subscription Information on Subscriber Side

Related Question