Azure SQL Database

Moving Azure SQL Database from One Subscription to Another

Multiple Environments

Many companies like to separate Production from Development / UAT environments. I have frequently seen Dev / UAT deployed to one subscription, and Production to a different subscription. This works well for the Finance team because the billing for each environment is separated. However, having the environments in different subscriptions inserts a speed bump in a common practice among DBAs – restoring a copy of the Production database in the UAT and Dev environments to ensure that code is synchronized across all environments.

Azure SQL Database Restore Database 

In Azure SQL Database, when you select “Restore”, your options are limited to restoring the same server. Notice that the options for changing the subscription, resource group, and server are greyed out. Only the database name can be changed.

If you need to restore a copy of your Azure SQL database on the same server so that you can copy a deleted table back into your database, then this works fine.

The restore Azure SQL database operation will not allow restoring the database over the existing database, however, you can change the name of the database at any time.


Rename Azure SQL Database

In SQL Server Management Studio run the following command on your Azure SQL Server:

ALTER DATABASE OriginalDatabaseName MODIFY NAME = NewDatabaseName;


Azure SQL Database Copy Database

The next option would be to COPY the database. This option looks promising because it allows the database to be copied to another server, and if a different Azure SQL server does not exist, a new one may be created without having to leave the Copy dialog and create one separately.

Although the New Server option looks promising, it does not provide the desired options: changing the subscription and resource group.

All is not lost, a solution exists: since an Azure SQL Server can move from one Subscription to another, the database can be copied to a Migration server, moved to the other Subscription then copied onto the existing server to refresh the existing database.

It’s Easier than It Sounds

You must have at least Contributor rights in both subscriptions to successfully perform this operation.

  • Copy the Azure SQL Database to a new migration Azure SQL server using the “Copy database” blade as shown above. This server will exist only for the duration of the database transfer and can be deleted immediately following the completion of the process
  • Go to the Migration Azure SQL Server’s Overview page

  • Click on “Move” and select “Move to another subscription” in the drop list

In the above example, the user does not have access to move resources to the selected resource group.

  • With appropriate permissions, the next step is the validation of resources to move

(The above image is linked to the image in Microsoft Documents “Move resources to a new subscription” article because my account doesn’t have rights to perform this action so I cannot get a good screenshot. The validation dialog should show only the Migration Azure SQL server in this step.)

  • Upon completion of Validation, the Review screen pops up

  • Confirm Source and Target subscriptions, resource groups, and several resources to move then check the box and click “Move”
  • Azure will pop up a notification when the move has been completed
  • Go to the subscription and resource group where the Migration server has moved
  • Go to the overview page of your Azure SQL Database on the Migration server
  • Click on “Copy”
  • Select your UAT or Dev Azure SQL Server and set the database name
  • Click on “Review + Create”
  • Click on “Create” to complete the process

Upon successful completion of the deployment, the migration server may be deleted.

If you have questions about Azure SQL Database, either how to use it or how to implement it in your organization, reach out to us. Our expert team and solution offerings can help your business with any Azure product or service, including Managed Services offerings. Contact us at 888-8AZURE or  [email protected].

Andie LetourneauMoving Azure SQL Database from One Subscription to Another
Read More

Azure SQL Database Elasticity – Queries, Jobs, and Transactions

Do you want to learn Azure SQL Database Elasticity? In this recent webinar, 3Cloud Director of Consulting, Steve Hughes, explores the flexibility of Azure SQL Database and how it makes an even better target for solutions that could only be supported on premises.

Steve dives deep into Azure SQL Database Elasticity, covering:

  • Elastic Queries (still in preview)
    • These are similar to Polybase functionality found in SQL Server 2019 and Azure Synapse but only for use with Azure SQL Database and Azure Synapse.
    • Elastic Queries strategies like vertical partitioning, horizontal partitioning or Sharding and data virtualization.
    • Touches upon cost information.
  • Elastic Jobs
    • Provides a mechanism to support T-SQL scripts to be run across one or more databases in parallel, on demand or on a schedule.
    • Elastic Jobs can be created in the portal, with PowerShell, with REST, or SQL.
    • Elastic Job target groups (i.e. databases, servers, pools, and Shard Maps).
    • Jobs and job steps.
  • Elastic Transactions
    • Allows you to create cross database transactions in Azure SQL Database.
    • What transactions it supports and its limitations.

All these elasticity topics will also be demoed in this webinar. So, if you’d like to learn more about Azure SQL Database elasticity and how to use this flexibility in your solutions, this webinar is for you. You can watch the complete webinar below.

Be sure to join us each week for our free webinars that happen every Tuesday at 11 AM ET. Our webinars cover a wide range of Azure topics and are presented by industry experts. Check out what webinars are coming up on our events calendar.

Need further help? Our expert team and solution offerings can help your business with any Azure product or service, including Managed Services offerings. Contact us at 888-8AZURE or  [email protected].

3CloudAzure SQL Database Elasticity – Queries, Jobs, and Transactions
Read More

Static Data Masking

I’d like to tell you about a newly announced feature called Static Data Modeling. This feature is not exclusive to Azure as it’s available with both Azure SQL Database and SQL Server on prem. I had a chance to try out this interesting feature and wanted to share my experience.

Bob RubockiStatic Data Masking
Read More