John McCormack DBA

SQL Server Databases and Cloud

  • Personal
    • About
  • Free Training
    • SQL Server on Amazon RDS (Free Course)
    • Free practice questions to help you pass DP-900
  • Save money in Azure
    • Azure IaaS SQL Backups – Stop burning money
    • Your Azure SQL Database and Managed Instance is too big
    • Turn the cloud off at bedtime to save 70%
    • Your Azure SQL Virtual Machine might be too big
    • Save money with Azure SQL DB serverless
    • Save up to 73% with reserved instances
    • Delete unused instances to save money in Azure
  • Hire me
    • 60 minute cost optimization
    • Let me solve your SQL Server problems
    • Take a look at my Sessionize speaker’s profile

How to find out Azure SQL DB Startup time

10th February 2021 By John McCormack 1 Comment

Alarm clock and cup of tea
Wakey Wakey SQL DB

Azure SQL DB Startup time

The query

SELECT sqlserver_start_time FROM sys.dm_os_sys_info;

Why write a post about this?

The traditional methods used for to find the start up time for SQL Server don’t work in Azure SQL DB.

I searched high and low to find this and thought I’ve got to share, and hopefully make it search engine friendly. A traditional google or bing search wasn’t bringing up the best way to find this out. I saw a lot of complicated queries to pull data, convert it and estimate start up time using functions and all kinds of magic. Actually, it turns out that the start up time is provided for you in sys.dm_os_sys_info (which works in Azure SQL DB). It also work in SQL Server which is a bonus.

Permissions to check Azure SQL DB Startup time

The permissions you need to run this query vary depending on the pricing tier of your Azure SQL DB. I ran this successfully on a P2 and a basic tier database.

On SQL Server, requires VIEW SERVER STATE permission.
On SQL Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the Server admin or an Azure Active Directory admin account is required. On all other SQL Database service objectives, the VIEW DATABASE STATE permission is required in the database.

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-sys-info-transact-sql?view=sql-server-ver15

I hope this one liner t-sql script was useful. Please let me know in the comments if you know a better way to get the start up time of Azure SQL DB, using T-SQL.

While you’re on my site, why not take a look to see if you can save money on your Azure SQL DBs, and other Azure resources by looking at my guide: 7 ways for data teams to save money in Azure.

Featured image by congerdesign from Pixabay

Filed Under: Azure SQL DB, front-page Tagged With: azure, azure sql db, azure sqldb managed instance

How do I set up database mail for Azure SQL DB Managed Instance

5th October 2020 By John McCormack 2 Comments

How do I set up database mail for Azure SQL DB Managed Instance

It’s not too difficult to set up database mail for Azure SQL DB Managed Instance in comparison to SQL Server (on-prem or IaaS) however there are a few extra things to consider. This post will describe how to set up database mail for Azure SQL DB Managed Instance. I will use Sendgrid as the mail provider but you can follow the same steps for any other mail provider or your company’s smtp server.

Before I go on, my personal opinion is that including database mail is a massive feature for Managed Instances. The lack of DB Mail on Azure SQL DB Single Database or Amazon RDS is a major blocker to PaaS adoption. Now with Managed Instance, we can have PaaS and database mail.

Create an Azure SQL DB Managed Instance

Whilst I’m planning a separate post on how to provision an Azure SQL DB Managed Instance, I’m going to assume for the purposes of this post that you already have an instance up and running. If you don’t, have a look at this howto article before going any further.

Change outbound settings on your Network Security Group (NSG)

All managed instances are created with a NSG. The NSG allows you to create inbound and outbound rules for traffic coming in and out of your database. You can configure ports, protocols and IP ranges etc.

To allow secure smtp for sendgrid, you must open port 587 for outbound traffic. Otherwise, any attempt to send mail will result in an error. You can do this in the Azure Portal by navigating to the NSG or you could do it in Powershell by modifying and running the code below.

# This code was originally taken from https://docs.microsoft.com/en-us/azure/service-fabric/scripts/service-fabric-powershell-add-nsg-rule
# and amended to be used to set up an outbound rule for Database Mail on Port 587

Login-AzAccount
Get-AzSubscription
Set-AzContext -SubscriptionId "yourSubscriptionID"

$RGname="RG-JOHN-DEMO"
$port=587
$rulename="DBMailOutbound"
$nsgname="nsg-johnmccormack-demo"

# Get the NSG resource
$nsg = Get-AzNetworkSecurityGroup -Name $nsgname -ResourceGroupName $RGname

# Add the inbound security rule
$nsg | Add-AzNetworkSecurityRuleConfig -Name $rulename -Description "Allow DBMail" -Access Allow `
-Protocol * -Direction Outbound -Priority 500 -SourceAddressPrefix "*" -SourcePortRange * `
-DestinationAddressPrefix * -DestinationPortRange $port

# Update the NSG
$nsg | Set-AzNetworkSecurityGroup

Set up a sendgrid account

The simplest way if you haven’t done it before is just to use the Azure Portal.

  1. Start typing sendgrid in the search bar
  2. Click on Sendgrid accounts from services
  3. Click + Add
  4. Complete the form to create your new account. (See image at bottom of post)
  5. Click manage sendgrid to get the credentials you will need for Database Mail (See other at bottom of post)
    1. Once you are logged in:
      1. Click Settings
      2. Click API Keys
      3. Click Create API Key
      4. Create and verify a sender identity. (If you don’t do this, no sendgrid emails will work)

Enable database mail for your Managed Instance

You must run this t-sql code to enable database mail on your Managed Instance:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE

EXEC sp_configure 'Database Mail XPs', 1
RECONFIGURE

Create a suitable database mail account and profile

Your database mail profile must be called AzureManagedInstance_dbmail_profile. If you try to call it anything else, your database mail won’t work.

-- Create a database mail account
IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysmail_account WHERE [name] = 'DBA Sendgrid Account')
BEGIN
DECLARE @servername NVARCHAR(255) = @@servername
SET @servername = substring(@servername, 1, charindex('.', @servername)-1)
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'DBA Sendgrid Account',
@description = 'DB account for DBAs and SQL Agent',
@email_address = 'john.mccormack@*****.***',
@display_name = @servername,
@mailserver_name = 'smtp.sendgrid.net',
@mailserver_type = 'SMTP',
@username = 'apikey',
@password = '******************',
@port = 587,
@enable_ssl = 1;
END
ELSE
PRINT 'sysmail_account already configured'

-- Create a database mail profile (Profile must be called AzureManagedInstance_dbmail_profile)
IF NOT EXISTS (SELECT 1 FROM msdb..sysmail_profile WHERE name = 'AzureManagedInstance_dbmail_profile')
BEGIN
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'AzureManagedInstance_dbmail_profile',
@description = 'Main profile for sending database mail';

-- Associate account with profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'AzureManagedInstance_dbmail_profile',
@account_name = 'DBA Sendgrid Account',
@sequence_number = 1 ;
END
ELSE
PRINT 'DBMail profile already configured'

Send a test email

-- Test Email
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'AzureManagedInstance_dbmail_profile',
@recipients = 'john.mccormack@*****.***',
@body = 'Email successfully sent from managed instance.',
@subject = 'OK, this works now. Thats great!';

So that’s all there is to it. Now you know how to set up database mail for Azure SQL DB Managed Instance.

If you have any comments or suggestions, please tweet me or leave them in the comments section.

John

More handy Azure links

How do I find the agent service account for Azure SQL Database Managed Instance

Change a user’s resource class within Azure Synapse Analytics using Azure Automation runbooks

Images to help you set up with sendgrid configuration for database mail

Create sendgrid account in Azure Portal
Create Sendgrid

Manage Sendgrid button in Azure Portal
Manage Sendgrid

Filed Under: Azure SQL DB Managed Instance, front-page Tagged With: azure, azure sql db, azure sqldb managed instance, database mail, managed instance, sendgrid

How do I find the agent service account for Azure SQL Database Managed Instance

25th September 2020 By John McCormack 1 Comment

How do I find the agent service account for Azure SQL Database Managed Instance

Normally if you want to know which service accounts run your SQL Server Agent, you can just run:

SELECT servicename, service_account FROM sys.dm_server_services

However this draws a blank when you run it on Azure SQL DB Managed Instance. So how do you find the agent service account for Azure SQL Database Managed Instance?

I’ve came up with 3 suggestions so far:

  1. Run an agent job and check which user ran it
  2. Check the error log
  3. Check sys.dm_exec_sessions

Run an agent job and check which user ran it

Output of SQL Server Agent Job

This is not exactly a very efficient process.

Check the error log

EXEC sp_readerrorlog 0,1,'service account'

Even if you cycle the error log, it will always be there but you’ll need to do some string manipulation to just pull out the credentials you need.

Check sys.dm_exec_sessions

(Thanks to David Alcock for this suggestion)

SELECT login_name FROM sys.dm_exec_sessions
WHERE program_name = 'SQLAgent - Generic Refresher'

This might be the best because it will only return one result and will only the login name, rather than a bunch of extra text as well.

Do you have a better way? Please let me know in the comments if you do.

John

Filed Under: Azure SQL DB Managed Instance, front-page Tagged With: azure, azure sql db, azure sqldb managed instance, SQL Agent service account

Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy

John McCormack · Copyright © 2025

 

Loading Comments...