The first tests performed with Microsoft Fabric's SQL databases (see previous post) revealed that with small exceptions many of the objects and administration scripts I built for SQL Server 2005 up to 2022 worked until now with minimal changes, though the tests made focused only on the standard basic functionality - the use of user-defined functions, stored procedures, views, respectively DMVs. As usual, the devil probably hides in details.
First of all, one can easily check current database's configuration settings:
-- database configuration values SELECT cnf.configuration_id , cnf.name , cnf.description , cnf.value , cnf.minimum , cnf.maximum , cnf.value_in_use , cnf.is_dynamic , cnf.is_advanced FROM sys.configurations cnf WHERE value <> 0 --WHERE name LIKE 'data%' ORDER BY cnf.name
Querying the sys.databases revealed that there seems to be only one system database available, namely the master database used to retrieve the definition of the system objects. In what concerns the various properties, it's probably important to check the compatibility level, the collation name, and other settings that apply:
-- database information SELECT db.database_id , db.[name] AS database_name , db.recovery_model_desc , db.log_reuse_wait_desc , db.[compatibility_level] , db.page_verify_option_desc , db.collation_name , db.user_access_desc , db.is_change_feed_enabled , db.is_data_lake_replication_enabled FROM sys.databases AS db
database_id | database_name | recovery_model_desc | log_reuse_wait_desc | compatibility_level | page_verify_option_desc | collation_name | user_access_desc | is_change_feed_enabled | is_data_lake_replication_enabled |
1 | master | FULL | NOTHING | 160 | CHECKSUM | SQL_Latin1_General_CP1_CI_AS | MULTI_USER | False | False |
26 | AdventureWorks01-... | FULL | NOTHING | 160 | CHECKSUM | SQL_Latin1_General_CP1_CI_AS | MULTI_USER | True | True |
Unfortunately, it's not possible to change a database's collation, though other collations on individual columns are supported [2].
There is no tempdb listed, though querying directly the objects from tempdb by using 3-part references allows to retrieve their content. For example, the following query retrieves the various database files available currently:
-- SQL database: tempdb files SELECT dbf.file_id , dbf.name file_name --, dbf.physical_name , dsp.name file_group --, type , dbf.type_desc file_type --, dbf.growth growth_kb , Cast(dbf.growth/128.0 as decimal(18,2)) growth_mb --, dbf.is_percent_growth --, dbf.max_size max_size_kb , Cast(NullIf(dbf.max_size, -1)/128.0 as decimal(18,2)) max_size_mb --, dbf.size file_size_kb , Cast(dbf.size/128.0 as decimal(18,2)) file_size_mb , dbf.state_desc , dbf.is_read_only FROM tempdb.sys.database_files dbf LEFT JOIN tempdb.sys.data_spaces dsp ON dbf.data_space_id = dsp.data_space_id ORDER BY dbf.Name
file_id | file_name | file_group | file_type | growth_mb | max_size_mb | file_size_mb | state_desc | is_read_only |
1 | tempdev | PRIMARY | ROWS | 256.00 | 32768.00 | 16.00 | ONLINE | False |
11 | tempdev10 | PRIMARY | ROWS | 256.00 | 32768.00 | 16.00 | ONLINE | False |
12 | tempdev11 | PRIMARY | ROWS | 256.00 | 32768.00 | 16.00 | ONLINE | False |
13 | tempdev12 | PRIMARY | ROWS | 256.00 | 32768.00 | 16.00 | ONLINE | False |
14 | tempdev13 | PRIMARY | ROWS | 256.00 | 32768.00 | 16.00 | ONLINE | False |
15 | tempdev14 | PRIMARY | ROWS | 256.00 | 32768.00 | 16.00 | ONLINE | False |
16 | tempdev15 | PRIMARY | ROWS | 256.00 | 32768.00 | 16.00 | ONLINE | False |
17 | tempdev16 | PRIMARY | ROWS | 256.00 | 32768.00 | 16.00 | ONLINE | False |
3 | tempdev2 | PRIMARY | ROWS | 256.00 | 32768.00 | 16.00 | ONLINE | False |
4 | tempdev3 | PRIMARY | ROWS | 256.00 | 32768.00 | 16.00 | ONLINE | False |
5 | tempdev4 | PRIMARY | ROWS | 256.00 | 32768.00 | 16.00 | ONLINE | False |
6 | tempdev5 | PRIMARY | ROWS | 256.00 | 32768.00 | 16.00 | ONLINE | False |
7 | tempdev6 | PRIMARY | ROWS | 256.00 | 32768.00 | 16.00 | ONLINE | False |
8 | tempdev7 | PRIMARY | ROWS | 256.00 | 32768.00 | 16.00 | ONLINE | False |
9 | tempdev8 | PRIMARY | ROWS | 256.00 | 32768.00 | 16.00 | ONLINE | False |
10 | tempdev9 | PRIMARY | ROWS | 256.00 | 32768.00 | 16.00 | ONLINE | False |
2 | templog | LOG | 64.00 | 216256.00 | 16.00 | ONLINE | False |
By removing the reference to tempdb from the query, one can get the same information for the current database. For the SalesLT were created only two database and one log file.
There seems to be no msdb database, used primarily by the SQL Server Agent for scheduling alerts and jobs. This implies that the functionality based on it won't work and one needs to find alternatives!
Looking at the feature comparison from [2], the most important are the lack of support for always encrypted, auditing, bulk inserts, change data capture (CDC), elastic queries, full-text search, in-memory optimization, ledger, OPENROWSET. In addition it supports cross-database three-part name queries via the SQL analytics endpoint [2].
Until now, the most annoying fact is that in the web UI results are returned in different "pages", and thus makes it a bit more challenging to navigate the output. However, using a cursor to iterate through the results and saving the content to a table solves the problem (see link). The last query from the post was used to retrieve the system objects together with the number of records returned.
Happy coding!
Previous Post <<||>> Next Post
References:
[1] Microsoft Learn (2024) Frequently asked questions for SQL database in Microsoft Fabric (preview) [link]
[2] Microsoft Learn (2024) Features comparison: Azure SQL Database and SQL database in Microsoft Fabric (preview) [link]