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.
Pingback: Dew Drop – May 15, 2018 (#2725) | Morning Dew
Pingback: Useful Azure SQL Database T-SQL Statements – Curated SQL
I’ve been doing the SSMS approach since diving into Azure SQL Database but felt “guilty” because I wasn’t using Powershell… :-). Regarding re-indexing, etc, we run an MS-written stored procedure that has ‘smart’ and ‘all’ options, logs what it does in table, and keeps the table trimmed to the latest three executions. This is executed from a sql automation account run book across all of our databases.
LikeLiked by 1 person
Sounds Interesting John – where can I find out more about this?
LikeLiked by 1 person
Here’s the link that give the details on the MS stored procedure I mentioned: https://blogs.msdn.microsoft.com/azuresqldbsupport/2016/07/03/how-to-maintain-azure-sql-indexes-and-statistics/
LikeLiked by 1 person