What Happens on Azure SQL Database?

Last week I posted the results from using Extended Events to snoop on what happens inside an AWS RDS database. This week, I’m taking a look at what happens on Azure SQL Database. I’m using the same toolset again, if for no other reason that I’m consistent in my approach. So it’s basically just rpc_completed & sql_batch_completed on the database in question. Let’s check out the results.

What Happens on Azure SQL Database

I would be doing the same thing as before, breaking apart the batch commands from the stored procs and/or prepared statements. However, after 48 hours, I only have 116 of both, so I’m just going to combine them this time.

The batch called most frequently, for a whopping total of 8 times over 48 hours, isn’t even a monitoring query. It’s this:

DECLARE @edition sysname;
SET @edition = CAST(SERVERPROPERTY(N'EDITION') AS sysname);
SELECT CASE
           WHEN @edition = N'SQL Azure' THEN
               2
           ELSE
               1
       END AS 'DatabaseEngineType',
       SERVERPROPERTY('EngineEdition') AS DatabaseEngineEdition,
       SERVERPROPERTY('ProductVersion') AS ProductVersion,
       @@MICROSOFTVERSION AS MicrosoftVersion;
SELECT N'Windows' AS host_platform;
IF @edition = N'SQL Azure'
    SELECT 'TCP' AS ConnectionProtocol;
ELSE
    EXEC ('select CONVERT(nvarchar(40),CONNECTIONPROPERTY(''net_transport'')) as ConnectionProtocol');

I assume they’re using that to build a connection string or something for other queries.

A few different batches looked like variations of this query:

SELECT SERVERPROPERTY('EngineEdition'),
       SERVERPROPERTY('productversion'),
       SERVERPROPERTY('productlevel'),
       SERVERPROPERTY('edition'),
       SERVERPROPERTY('MachineName'),
       SERVERPROPERTY('ServerName'),
       (
           SELECT CASE
                      WHEN EXISTS
                           (
                               SELECT TOP 1
                                      1
                               FROM [sys].[all_columns] WITH (NOLOCK)
                               WHERE name = N'xml_index_type'
                                     AND OBJECT_ID(N'sys.xml_indexes') = object_id
                           ) THEN
                          1
                      ELSE
                          0
                  END AS SXI_PRESENT
       );

That is clearly internal monitoring. Probably tracking which databases need updates. Fun to see it in action. For another example, this was called twice, once for each day:

SELECT CAST(CASE
                WHEN 'a' <> 'A' THEN
                    1
                ELSE
                    0
            END AS BIT) AS [IsCaseSensitive],
       @@MAX_PRECISION AS [MaxPrecision],
       CAST(FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') AS BIT) AS [IsFullTextInstalled],
       SERVERPROPERTY(N'ProductVersion') AS [VersionString],
       CAST(SERVERPROPERTY(N'Edition') AS sysname) AS [Edition],
       CAST(SERVERPROPERTY(N'ProductLevel') AS sysname) AS [ProductLevel],
       CAST(ISNULL(SERVERPROPERTY(N'ProductUpdateLevel'), N'') AS sysname) AS [ProductUpdateLevel],
       CAST(SERVERPROPERTY('IsSingleUser') AS BIT) AS [IsSingleUser],
       CAST(SERVERPROPERTY('EngineEdition') AS INT) AS [EngineEdition],
       CONVERT(sysname, SERVERPROPERTY(N'collation')) AS [Collation],
       SERVERPROPERTY(N'ResourceVersion') AS [ResourceVersionString],
       SERVERPROPERTY(N'ResourceLastUpdateDateTime') AS [ResourceLastUpdateDateTime],
       SERVERPROPERTY(N'CollationID') AS [CollationID],
       SERVERPROPERTY(N'ComparisonStyle') AS [ComparisonStyle],
       SERVERPROPERTY(N'SqlCharSet') AS [SqlCharSet],
       SERVERPROPERTY(N'SqlCharSetName') AS [SqlCharSetName],
       SERVERPROPERTY(N'SqlSortOrder') AS [SqlSortOrder],
       SERVERPROPERTY(N'SqlSortOrderName') AS [SqlSortOrderName],
       N'Windows' AS [HostPlatform],
       CAST(SERVERPROPERTY(N'Servername') AS sysname) AS [Name],
       CAST(ISNULL(SERVERPROPERTY(N'instancename'), N'') AS sysname) AS [InstanceName],
       CAST(0x0001 AS INT) AS [Status],
       N'\' AS [PathSeparator],
       0 AS [IsContainedAuthentication],
       CAST(NULL AS INT) AS [ServerType];

As to procedures, there were several of these over the period:

EXEC sp_executesql N'SELECT TOP 1  [backup_metadata_uuid],[database_guid],[physical_database_name],[time_zone],[first_lsn],[last_lsn],[checkpoint_lsn],[database_backup_lsn],[backup_start_date],[backup_finish_date],[backup_type],[backup_storage_redundancy],[database_version],[backup_size],[compressed_backup_size],[server_name],[is_damaged],[last_recovery_fork_guid],[differential_base_lsn],[differential_base_guid],[backup_path],[last_valid_restore_time],[compression_algorithm]  FROM [ce8f8a1e-fbcc-46b2-a2bd-5f518d18d6f7].[sys].[backup_metadata_store] WHERE (backup_type = @backupTypeEquals) AND (backup_path != @backupPathNotEquals) AND (last_lsn <= @lastLsnLesserThanEqualTo) ORDER BY backup_start_date DESC',
                   N'@backupTypeEquals nvarchar(1),@backupPathNotEquals nvarchar(3),@lastLsnLesserThanEqualTo decimal(35,0)',
                   @backupTypeEquals = N'L',
                   @backupPathNotEquals = N'nul',
                   @lastLsnLesserThanEqualTo = 58000000360000001;

That’s monitoring the state of backups. Again, fun to see in action.

And honestly, that’s about it. There were quite a few variations on, what’s the state of the database, types of queries, space used, stuff like that. However, most of them were only called once or twice.

Also, for what it’s worth, I captured a few of these queries, but not all of them, in the Query Store running on my Azure database as well. Nothing else special though.

Conclusion

Already?

Yeah, well, there’s not much meat to chew on here. What I think is happening (again, no inside knowledge on this) is that Microsoft is primarily relying on Extended Events, controlled at the server level, which we can’t see, in order for Microsoft to know what happens on Azure SQL Database. That, and probably they have a way to query stuff that we can’t see through normal processes. It’s all, more than a little bit, locked down in Azure (which is a GREAT thing). While it may be SQL Server at root, under the covers, Azure SQL Database has more than a few secrets in there too.

However, I can still go back to my original premise that started this peak into what happens in our various cloud databases, we all have to use the same tools, which means queries against stuff like backup_metadata_store and SERVERPROPERTY, etc.. Even Microsoft is using the tools we use to do their own work. In a way, it’s very reassuring.