Downgrade Azure SQL Database from P15 (2TB) to P4 (500GB)

Question

As per documentation:

If the maximum size of a database is set to greater than 1 TB, then it cannot be changed to 1 TB even if the actual storage used is below 1 TB. Thus, you cannot downgrade a P11 or P15 with a maximum size larger than 1 TB to a 1 TB P11 or 1 TB P15 or lower performance tier, such as P1-P6).

I wonder why this limitation apply? What are the workarounds?

Answer 1

The answer provide by SQL Azure team is that the downgrade is not possible because there are multiple files on a 4 TB database and the only way to downgrade is exporting the data though something like bcp,

Adding another reason is that those premium tiers of 4 TB reside on a special hardware.

EDIT: Answer updated with information provide by Microsoft.

Answer 2

So I solved the problem using the Export-Import approach. I first exported existing database to the blob and then imported produced bacpac file to the new database. Steps below:

1. Export database Db (P15, 2TB)
2. Import bacpac to Db-New (P15, 500GB to speed up the process)
3. ALTER DATABASE [Db] MODIFY NAME = [Db-Old]
4. ALTER DATABASE [Db-New] MODIFY NAME = [Db]
5. Drop Db-Old
6. Downgrade Db plan to P4

Answer 3

Note: Once a database is configured with a maximum size greater than 1 TB, all restore operations of this database must be run into a P11/P15 with a maximum size greater than 1 TB.

For example: If your database size is 1.2TB, then you need to choose P11 or P15 because Maximum Storage choices for P11 and P15 is 4096.

You cannot downgrade is because Maximum storage choices for P1-P6 is 500, 750, 1024.