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.
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')
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.
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 ;
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.