Handy TSQL for Azure SQL Database

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


Standards2

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

premiump1

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.

EP_Details

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.

meetwo

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

operational

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

badindex

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

 

 

 

5 thoughts on “Handy TSQL for Azure SQL Database

  1. Pingback: Dew Drop – May 15, 2018 (#2725) | Morning Dew

  2. Pingback: Useful Azure SQL Database T-SQL Statements – Curated SQL

  3. 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.

    Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s