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 clearly 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 an accidentally 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 this above example, the user does not have access to move resources to the selected resource group. 

• With appropriate permissions, the next step is 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 number of resources to move then check the box and click "Move". 

• Azure will pop up a notification when the move has 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. 

Your Synapse Moment: Adding Synapse Workspace User

 Just a quick script for Azure Synapse. Let's say your synapse workspace is named "dev-synapse-workspace", and you've already set up the Managed Identity

To allow synapse to actually interact with your SQL pool user databases, in each database you should run the following script (put your synapse workspace name in place of the example name)

CREATE USER [dev-synapse-workspace] FROM EXTERNAL PROVIDER;

exec sp_addrolemember 'db_owner', 'dev-synapse-workspace';

After that, your pipelines should be able to easily access your Synapse SQL Pools. 

This same code works in an Azure SQL database if you need to access one from Azure Synapse. 

Happy coding!

My NEW Crafting Blog

 I have decided to completely separate my technical blog (SQL server / Azure posts) from my crafting (sewing / crafting) blog because it makes more sense to have them separate rather than intertwined. 

Crafting blog: https://craftingwithandie.blogspot.com/

This blog will remain as the technical blog. If you're interested in reading about my future adventures in crafting and sewing, please visit my crafting blog. Otherwise, stay tuned here for more interesting Azure and SQL server articles.

First Heat Transfer Vinyl Project: Awesome Aunt Shirt

 I'm fairly new to using a cutting machine - I bought my Silhouette Cameo in April 2021 - and since my niece was planning to visit me soon, I thought it would be neat to have a shirt labeling me as an awesome aunt. Awesome aunts do crafting with their nieces, of course, and I planned to help her make some things using the Cameo. 

For the shirt, I decided to use one of my old but rarely worn t-shirts just in case my first use of heat transfer vinyl (HTV) was a disaster and it had to be discarded. After searching around, I found a "This is what an awesome aunt looks like" design on Creative Fabrica in their print templates category. 

I was happy to see that it was not only a free design, but it also came with a commercial license. Most other design houses mark their free designs as "personal use only", which means you can't use those designs for items you sell. All of Creative Fabrica's free designs come with a commercial license, which is fantastic and extremely generous. They also offer more free designs than most sites - over 25,000 free designs including fonts, SVGs, graphics and machine embroidery files are available in their freebies section. Creative Fabrica also has "daily gifts" which are selected premium fonts, crafts, and graphics designs available free for one day only.

The "This is what an awesome aunt looks like" design came in 6 formats: AI, SVG, DXF, EPS, PNG, and PDF. I have Silhouette Studio Designer edition so I chose to use the SVG instead of the DXF because I read that SVG is supposed to be the best non-.studio3 file format available. I made one small edit to the file, removing the extra space between the k & s in "looks". I also resized the design so it would fit on my 12" x 7.5" piece of HTV.  I then connected my machine and prepared to send the file.

My Cameo came bundled with a collection of vinyl including 9 sheets of HTV. I selected the silvery gray because it looked like it would stand out well on the dark green shirt. After rechecking the documentation on determining which side of the HTV to cut (dull side up because shiny side is the carrier sheet), I placed the HTV on the mat and loaded it in the machine. 

When I hit "send", Silhouette Studio asked me if I remembered to mirror the design? 

I looked back at the design and realized I had forgotten to reverse the design. I mentally thanked the creator of that error message - I'm sure many have forgotten to flip designs and ruined numerous sheets of expensive HTV in the process. I canceled the job, returned to editing mode, mirrored the design, and switched back to the send screen to send it to the machine. The design cut out smoothly and relatively quickly.  

After weeding the design, I stuck it to the shirt and checked the design placement by pinning the shirt's shoulders to my dressform. 
I decided to move the design up closer to the neckline because my initial placement put the hands pointing right at my bust (not a good look). The higher placement looked much better, so I put the shirt on my ironing board and ironed the HTV. I found I had to press certain sections again because the HTV was pulling up when I tried to remove the backing. I think that would happen with any HTV design when using a regular household iron. I have a heat press on my wish list so I can do more HTV designs with less struggle. 

I wore the shirt the day my niece visited. She noticed it after we had been talking for about an hour. 

I have washed the shirt once and noticed that couple of letters are starting to peel up on the edges. The good news is that was easily fixed by ironing the shirt inside out. Of course, if I had that heat press, it would have stayed on better. 

I think I'm going to have a lot of fun decorating other garments with HTV. I have already ordered more HTV in a variety of colors.

HAPPY CRAFTING!