I do not always use the Azure portal to make database changes or to check for certain information. I use it a lot of for blogging purposes but for some tasks I rather just run code via SSMS – SQL Server Management Studio.

Like what?

Creating a database

Very easy to create something like the below – a S2 database.

CREATE DATABASE [MeeTwoDB]
( MAXSIZE = 5GB, EDITION = 'standard', SERVICE_OBJECTIVE = 'S2' ) ;  

--checking details
SELECT Edition = DATABASEPROPERTYEX('MeeTwoDB', 'Edition'),
       ServiceObjective = DATABASEPROPERTYEX('MeeTwoDB', 'ServiceObjective')

Upgrading

Another common task, upgrading to a P1 database.

ALTER DATABASE [MeeTwoDB] MODIFY (EDITION = 'Premium',  SERVICE_OBJECTIVE = 'P1');

-- Recheck
SELECT Edition = DATABASEPROPERTYEX('MeeTwoDB', 'Edition'),
       ServiceObjective = DATABASEPROPERTYEX('MeeTwoDB', 'ServiceObjective')

Database names and service tiers

SELECT  d.name,
		s.database_id,
		s.edition,
		s.service_objective,
		(CASE WHEN s.elastic_pool_name  IS NULL
				THEN 'No Elastic Pool used'
				ELSE s.elastic_pool_name
				END) AS [Elastic Pool details]
FROM sys.databases d
JOIN sys.database_service_objectives s
ON d.database_id = s.database_id;

This script is very useful; it needs to be executed on master database. It will return information such as the service tier and whether or not it is part of an elastic pool.

Move a database into an Elastic Pool

Quite simply when you want to move MeeTwoDB into the elastic pool called SQLPOOL.

 ALTER DATABASE MeeTwoDB
MODIFY ( SERVICE_OBJECTIVE = ELASTIC_POOL ( name = SQLPOOL ) ) ;

You can confirm this via the above script – see the yellow highlighted text after the move completed.

Checking operations

Such as the move into the elastic pool – This is very handy and I have used this in the past where I wanted to track database sizing changes too.

SELECT * FROM sys.dm_operation_status
ORDER BY start_time DESC

Renaming a database

Very handy for when you want to rename a freshly restored database. For the below I am renaming a database called facedb_restored to just facedb.

ALTER DATABASE  facedb_restored
Modify Name = facedb ;

Indexing Information

Just because you are in the cloud doesn’t mean you should be neglecting your indexing strategies and maintenance. So, enter the powerful sp_Blitzindex which DOES work with Azure SQL Database, see this link for more details. https://www.brentozar.com/blitz/

I run in mode 4. You should too.

 EXEC sp_BlitzIndex @mode = 4

You just cannot get this sort of  indexing information through the portal so I totally recommended using it.