Professional Azure SQL Database Administration eBook

The e-book version of my book Professional Azure SQL Database Administration is available for free here - do check it out.
https://azure.microsoft.com/en-in/resources/professional-azure-sql-database-administration/

Create SQL Login and SQL User on your Azure SQL DB

/*1: Create SQL Login on master database (connect with admin account to master database)*/
CREATE LOGIN MyLogin WITH PASSWORD = '';


/*2: Create SQL user on the master database (this is necessary for login attempt to the database, as with Azure SQL you cannot set the DEFAULT_DATABASE property of the login so it always will be [master] database.)*/
CREATE USER MyUser FROM LOGIN MyLogin;


/*3: Create SQL User on the user database (connect with admin account to user database)*/
CREATE USER MyUser FROM LOGIN MyLogin;


/*4. Grant permissions to the user by assign him to a database role*/
ALTER ROLE db_datareader ADD MEMBER MyUser;


/*Login with the newly created login:

With SSMS  use login name and password to connect directly to user database \ master \

When using contained database used, you must set the here to be able to connect.
*/

How to find blocking queries in SQL Azure

The query below will display the top ten running queries that have been the longest total elapsed time and are blocking other queries.

SELECT TOP 10 r.session_id, r.plan_handle,  r.[sql_handle], r.request_id,      r.start_time, r.[status],      r.command, r.database_id,      
r.[user_id], r.wait_type,      r.wait_time, r.last_wait_type,      r.wait_resource, r.total_elapsed_time,      r.cpu_time, r.transaction_isolation_level,      r.row_count, st.[text]  
FROM sys.dm_exec_requests r  CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) as st  
WHERE r.blocking_session_id = 0       
and r.session_id IN  (SELECT distinct(blocking_session_id)  FROM sys.dm_exec_requests)  
GROUP BY r.session_id, r.plan_handle,      r.[sql_handle], r.request_id,      r.start_time, r.[status],      r.command, r.database_id,      r.[user_id], r.wait_type,      r.wait_time, r.last_wait_type,      r.wait_resource, r.total_elapsed_time,      r.cpu_time, r.transaction_isolation_level,      r.row_count, st.[text]  
ORDER BY r.total_elapsed_time DESC

How to find Azure SQL Database Usage

It is simple way to use below SQL query to extract Azure SQL Database usage metrics.

SELECT DB_NAME() as DatabaseName, 

         (max_size/128.0) / 1024 AS DBMaxSizeInGB ,

(size/128.0) / 1024 AS DBAllocatedSpaceInGB ,  

(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.) / 1024 / 1024 / 1024 AS DBUsedSpaceInGB,

(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0)/  1024 AS FreeSpaceInGB ,

  Getdate() as Created_Date  

FROM sys.database_files

WHERE name in ('data_0' ) and DB_NAME() = 'MyDatabase';

How to add more partitions to existing table

 --STEP 1 : Find File group

SELECT name AS AvailableFilegroups
FROM sys.filegroups WHERE type = 'FG'

--AvailableFilegroups
-------------------
--PRIMARY


--- STEP 2 : Create Partition Range Function & Schema

SELECT * FROM sys.partition_functions WHERE [name] = N'PF_CREATED_DATE'  
SELECT * FROM sys.partition_schemes   WHERE [name] = N'PS_PURCHASE_ORDER' 
  

CREATE PARTITION FUNCTION [PF_CREATED_DATE](Date) 
AS RANGE RIGHT FOR VALUES (
N'2019-08-01',
N'2019-08-02',
N'2019-08-03',
N'2019-08-04',
N'2019-08-05',
N'2019-08-06',
N'2019-08-07',
N'2019-08-08',
N'2019-08-09',
N'2019-08-10',
N'2019-08-11',
N'2019-08-12',
N'2019-08-13',
N'2019-08-14',
N'2019-08-15')

CREATE PARTITION SCHEME [PS_PURCHASE_ORDER] AS PARTITION [PF_CREATED_DATE]  TO  ( 
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ) 


--- STEP 3 : Create TABLE & Index

 SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[PURCHASE_ORDER](
[PURCHASE_ORDER_ID] [bigint] NOT NULL,
[CREATED_DATE] [date]  NOT NULL,
[LOAD_ID] [int] NOT NULL
) ON [PS_PURCHASE_ORDER]([CREATED_DATE])
GO


CREATE CLUSTERED INDEX [CI_CREATED_DATE] ON [dbo].[PURCHASE_ORDER]
(
[CREATED_DATE] ASC,
[PURCHASE_ORDER_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PS_PURCHASE_ORDER]([CREATED_DATE])
GO


--- STEP 4 : Add new partitions

 -- Alter PS to add File group
 ALTER PARTITION SCHEME [PS_PURCHASE_ORDER]  NEXT USED  [PRIMARY]

 --ALTER  PF to add new partition
 ALTER PARTITION FUNCTION [PF_CREATED_DATE]()  SPLIT RANGE (N'2019-08-16')

  -- Alter PS to add File group
 ALTER PARTITION SCHEME [PS_PURCHASE_ORDER]  NEXT USED  [PRIMARY]

 --ALTER  PF to add new partition
 ALTER PARTITION FUNCTION [PF_CREATED_DATE]()  SPLIT RANGE (N'2019-08-17')

How do i change table schema to dbo

ALTER SCHEMA TargetSchema TRANSFER SourceSchema.TableName

Example : 1
ALTER SCHEMA dbo TRANSFER Archive.TableName


Example : 2
change schema from dbo to rpt

Source table : dbo.rpt.TableName ( Invalid table name with additional dot)

Step 1 : Rename table with _ under score.
                   dbo.rpt_TableName

Step 2 : Change schema to rpt
ALTER SCHEMA rpt TRANSFER dbo.rpt_TableName

    Now table name with fully qualified name    rpt.rpt_TableName

Step 3 : Rename table with correct name ( to remove rpt_ )
                      rpt.TableName

My Parents

This blog dedicated to my parents. Thanks for your love, blessings and support in my life. You are inspired me to share my knowledge to the world especially for SQL Server DBA fellow members.

C. SANGARAPPAN  (Father)

Born: 05-June-1929  Died : 24-November-2017 at 9:40 pm in India

&

S. MEENAKSHI (Mother)

Died :  02-June-2020 at 3:40 pm in India