When taking over a SQL Server, respectively database, one of the first checks I do focuses on the overall configuration, going through the UI available for admins to see if I can find anything that requires further investigation. If no documentation is available on the same, I run a few scripts and export their output as baseline.
Especially when documenting the configuration, it's useful to export the database options and properties defined at database level. Besides the collation and probably the recovery mode, typically the rest of the configuration is similar, though in exceptional cases one should expect also surprises that require further investigation!
The following query retrieves in a consolidated way all the options and properties of a SQL database in Microsoft Fabric.
-- database settings/properties SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation') Collation --, DATABASEPROPERTYEX(DB_NAME(), 'ComparisonStyle') ComparisonStyle , DATABASEPROPERTYEX(DB_NAME(), 'Edition') Edition --, DATABASEPROPERTYEX(DB_NAME(), 'IsAnsiNullDefault') IsAnsiNullDefault --, DATABASEPROPERTYEX(DB_NAME(), 'IsAnsiNullsEnabled') IsAnsiNullsEnabled --, DATABASEPROPERTYEX(DB_NAME(), 'IsAnsiPaddingEnabled') IsAnsiPaddingEnabled --, DATABASEPROPERTYEX(DB_NAME(), 'IsAnsiWarningsEnabled') IsAnsiWarningsEnabled --, DATABASEPROPERTYEX(DB_NAME(), 'IsArithmeticAbortEnabled') IsArithmeticAbortEnabled --, DATABASEPROPERTYEX(DB_NAME(), 'IsAutoClose') IsAutoClose , DATABASEPROPERTYEX(DB_NAME(), 'IsAutoCreateStatistics') IsAutoCreateStatistics --, DATABASEPROPERTYEX(DB_NAME(), 'IsAutoCreateStatisticsIncremental') IsAutoCreateStatisticsIncremental --, DATABASEPROPERTYEX(DB_NAME(), 'IsAutoShrink') IsAutoShrink , DATABASEPROPERTYEX(DB_NAME(), 'IsAutoUpdateStatistics') IsAutoUpdateStatistics --, DATABASEPROPERTYEX(DB_NAME(), 'IsClone') IsClone --, DATABASEPROPERTYEX(DB_NAME(), 'IsCloseCursorsOnCommitEnabled') IsCloseCursorsOnCommitEnabled --, DATABASEPROPERTYEX(DB_NAME(), 'IsDatabaseSuspendedForSnapshotBackup') IsDatabaseSuspendedForSnapshotBackup , DATABASEPROPERTYEX(DB_NAME(), 'IsFulltextEnabled') IsFulltextEnabled --, DATABASEPROPERTYEX(DB_NAME(), 'IsInStandBy') IsInStandBy --, DATABASEPROPERTYEX(DB_NAME(), 'IsLocalCursorsDefault') IsLocalCursorsDefault --, DATABASEPROPERTYEX(DB_NAME(), 'IsMemoryOptimizedElevateToSnapshotEnabled') IsMemoryOptimizedElevateToSnapshotEnabled --, DATABASEPROPERTYEX(DB_NAME(), 'IsMergePublished') IsMergePublished --, DATABASEPROPERTYEX(DB_NAME(), 'IsNullConcat') IsNullConcat --, DATABASEPROPERTYEX(DB_NAME(), 'IsNumericRoundAbortEnabled') IsNumericRoundAbortEnabled --, DATABASEPROPERTYEX(DB_NAME(), 'IsParameterizationForced') IsParameterizationForced --, DATABASEPROPERTYEX(DB_NAME(), 'IsQuotedIdentifiersEnabled') IsQuotedIdentifiersEnabled --, DATABASEPROPERTYEX(DB_NAME(), 'IsPublished') IsPublished --, DATABASEPROPERTYEX(DB_NAME(), 'IsRecursiveTriggersEnabled') IsRecursiveTriggersEnabled --, DATABASEPROPERTYEX(DB_NAME(), 'IsSubscribed') IsSubscribed --, DATABASEPROPERTYEX(DB_NAME(), 'IsSyncWithBackup') IsSyncWithBackup --, DATABASEPROPERTYEX(DB_NAME(), 'IsTornPageDetectionEnabled') IsTornPageDetectionEnabled --, DATABASEPROPERTYEX(DB_NAME(), 'IsVerifiedClone') IsVerifiedClone --, DATABASEPROPERTYEX(DB_NAME(), 'IsXTPSupported') IsXTPSupported , DATABASEPROPERTYEX(DB_NAME(), 'LastGoodCheckDbTime') LastGoodCheckDbTime , DATABASEPROPERTYEX(DB_NAME(), 'LCID') LCID --, DATABASEPROPERTYEX(DB_NAME(), 'MaxSizeInBytes') MaxSizeInBytes , DATABASEPROPERTYEX(DB_NAME(), 'Recovery') Recovery --, DATABASEPROPERTYEX(DB_NAME(), 'ServiceObjective') ServiceObjective --, DATABASEPROPERTYEX(DB_NAME(), 'ServiceObjectiveId') ServiceObjectiveId , DATABASEPROPERTYEX(DB_NAME(), 'SQLSortOrder') SQLSortOrder , DATABASEPROPERTYEX(DB_NAME(), 'Status') Status , DATABASEPROPERTYEX(DB_NAME(), 'Updateability') Updateability , DATABASEPROPERTYEX(DB_NAME(), 'UserAccess') UserAccess , DATABASEPROPERTYEX(DB_NAME(), 'Version') Version --, DATABASEPROPERTYEX(DB_NAME(), 'ReplicaID') ReplicaID
Output:
| Collation | Edition | IsAutoCreateStatistics | IsAutoUpdateStatistics | IsFulltextEnabled | LastGoodCheckDbTime | LCID | Recovery | SQLSortOrder | Status | Updateability | UserAccess | Version |
| SQL_Latin1_General_CP1_CI_AS | FabricSQLDB | 1 | 1 | 1 | 12/31/1899 | 1033 | FULL | 52 | ONLINE | READ_WRITE | MULTI_USER | 981 |
The query can be run also against the SQL analytics endpoints available for warehouses in Microsoft Fabric.
Output:
| Collation | Edition | IsAutoCreateStatistics | IsAutoUpdateStatistics | IsFulltextEnabled | LastGoodCheckDbTime | LCID | Recovery | SQLSortOrder | Status | Updateability | UserAccess | Version |
| Latin1_General_100_BIN2_UTF8 | DataWarehouse | 1 | 1 | 1 | 12/31/1899 | 1033 | SIMPLE | 0 | ONLINE | READ_WRITE | MULTI_USER | 987 |
Respectively, for lakehouses:
| Collation | Edition | IsAutoCreateStatistics | IsAutoUpdateStatistics | IsFulltextEnabled | LastGoodCheckDbTime | LCID | Recovery | SQLSortOrder | Status | Updateability | UserAccess | Version |
| Latin1_General_100_BIN2_UTF8 | LakeWarehouse | 1 | 1 | 1 | 12/31/1899 | 1033 | SIMPLE | 0 | ONLINE | READ_WRITE | MULTI_USER | 987 |
A similar output is obtained if one runs the query against SQL database's SQL analytics endpoint:
Output:
| Collation | Edition | IsAutoCreateStatistics | IsAutoUpdateStatistics | IsFulltextEnabled | LastGoodCheckDbTime | LCID | Recovery | SQLSortOrder | Status | Updateability | UserAccess | Version |
| Latin1_General_100_BIN2_UTF8 | LakeWarehouse | 1 | 1 | 1 | 12/31/1899 | 1033 | SIMPLE | 0 | ONLINE | READ_WRITE | MULTI_USER | 987 |
SQL databases seem to inherit the collation from the earlier versions of SQL Server.
Another meaningful value for SQL databases is MaxSizeInBytes, which in my environment had a value of 3298534883328 bytes ÷ 1,073,741,824 = 3,072 GB.
There are however also server properties. Here's the consolidated overview:
-- server properties SELECT --SERVERPROPERTY('BuildClrVersion') BuildClrVersion SERVERPROPERTY('Collation') Collation --, SERVERPROPERTY('CollationID') CollationID , SERVERPROPERTY('ComparisonStyle') ComparisonStyle --, SERVERPROPERTY('ComputerNamePhysicalNetBIOS') ComputerNamePhysicalNetBIOS , SERVERPROPERTY('Edition') Edition --, SERVERPROPERTY('EditionID') EditionID , SERVERPROPERTY('EngineEdition') EngineEdition --, SERVERPROPERTY('FilestreamConfiguredLevel') FilestreamConfiguredLevel --, SERVERPROPERTY('FilestreamEffectiveLevel') FilestreamEffectiveLevel --, SERVERPROPERTY('FilestreamShareName') FilestreamShareName --, SERVERPROPERTY('HadrManagerStatus') HadrManagerStatus --, SERVERPROPERTY('InstanceDefaultBackupPath') InstanceDefaultBackupPath , SERVERPROPERTY('InstanceDefaultDataPath') InstanceDefaultDataPath --, SERVERPROPERTY('InstanceDefaultLogPath') InstanceDefaultLogPath --, SERVERPROPERTY('InstanceName') InstanceName , SERVERPROPERTY('IsAdvancedAnalyticsInstalled') IsAdvancedAnalyticsInstalled --, SERVERPROPERTY('IsBigDataCluster') IsBigDataCluster --, SERVERPROPERTY('IsClustered') IsClustered , SERVERPROPERTY('IsExternalAuthenticationOnly') IsExternalAuthenticationOnly , SERVERPROPERTY('IsExternalGovernanceEnabled') IsExternalGovernanceEnabled , SERVERPROPERTY('IsFullTextInstalled') IsFullTextInstalled --, SERVERPROPERTY('IsHadrEnabled') IsHadrEnabled --, SERVERPROPERTY('IsIntegratedSecurityOnly') IsIntegratedSecurityOnly --, SERVERPROPERTY('IsLocalDB') IsLocalDB --, SERVERPROPERTY('IsPolyBaseInstalled') IsPolyBaseInstalled --, SERVERPROPERTY('IsServerSuspendedForSnapshotBackup') IsServerSuspendedForSnapshotBackup --, SERVERPROPERTY('IsSingleUser') IsSingleUser --, SERVERPROPERTY('IsTempDbMetadataMemoryOptimized') IsTempDbMetadataMemoryOptimized , SERVERPROPERTY('IsXTPSupported') IsXTPSupported , SERVERPROPERTY('LCID') LCID , SERVERPROPERTY('LicenseType') LicenseType , SERVERPROPERTY('MachineName') MachineName , SERVERPROPERTY('NumLicenses') NumLicenses , SERVERPROPERTY('PathSeparator') PathSeparator --, SERVERPROPERTY('ProcessID') ProcessID , SERVERPROPERTY('ProductBuild') ProductBuild --, SERVERPROPERTY('ProductBuildType') ProductBuildType --, SERVERPROPERTY('ProductLevel') ProductLevel --, SERVERPROPERTY('ProductMajorVersion') ProductMajorVersion --, SERVERPROPERTY('ProductMinorVersion') ProductMinorVersion --, SERVERPROPERTY('ProductUpdateLevel') ProductUpdateLevel --, SERVERPROPERTY('ProductUpdateReference') ProductUpdateReference --, SERVERPROPERTY('ProductUpdateType') ProductUpdateType , SERVERPROPERTY('ProductVersion') ProductVersion , SERVERPROPERTY('ResourceLastUpdateDateTime') ResourceLastUpdateDateTime , SERVERPROPERTY('ResourceVersion') ResourceVersion , SERVERPROPERTY('ServerName') ServerName , SERVERPROPERTY('SqlCharSet') SqlCharSet , SERVERPROPERTY('SqlCharSetName') SqlCharSetName , SERVERPROPERTY('SqlSortOrder') SqlSortOrder , SERVERPROPERTY('SqlSortOrderName') SqlSortOrderName , SERVERPROPERTY('SuspendedDatabaseCount') SuspendedDatabaseCount
Output (consolidated):
| Property | SQL database | Warehouse | Lakehouse |
| Collation | SQL_Latin1_General_CP1_CI_AS | SQL_Latin1_General_CP1_CI_AS | SQL_Latin1_General_CP1_CI_AS |
| ComparisonStyle | 196609 | 196609 | 196609 |
| Edition | SQL Azure | SQL Azure | SQL Azure |
| EngineEdition | 12 | 11 | 11 |
| InstanceDefaultDataPath | NULL | NULL | NULL |
| IsAdvancedAnalyticsInstalled | 1 | 1 | 1 |
| IsExternalAuthenticationOnly | 1 | 0 | 0 |
| IsExternalGovernanceEnabled | 1 | 1 | 1 |
| IsFullTextInstalled | 1 | 0 | 0 |
| IsXTPSupported | 1 | 1 | 1 |
| LCID | 1033 | 1033 | 1033 |
| LicenseType | DISABLED | DISABLED | DISABLED |
| MachineName | NULL | NULL | NULL |
| NumLicenses | NULL | NULL | NULL |
| PathSeparator | \ | \ | \ |
| ProductBuild | 2000 | 502 | 502 |
| ProductVersion | 12.0.2000.8 | 12.0.2000.8 | 12.0.2000.8 |
| ResourceLastUpdateDateTime | 11/6/2024 3:41:27 PM | 3/5/2025 12:05:50 PM | 3/5/2025 12:05:50 PM |
| ResourceVersion | 16.00.5751 | 17.00.502 | 17.00.502 |
| ServerName | ... | .... | .... |
| SqlCharSet | 1 | 1 | 1 |
| SqlCharSetName | iso_1 | iso_1 | iso_1 |
| SqlSortOrder | 52 | 52 | 52 |
| SqlSortOrderName | nocase_iso | nocase_iso | nocase_iso |
| SuspendedDatabaseCount | NULL | 0 | 0 |
It's interesting that all three instances have the same general collation, while the Engine Edition of SQL databases is not compatible with the others [2]. The Server Names has been removed manually from the output from obvious reasons. The warehouse and lakehouse are in the same environment (SQL Azure instance, see sys.databases), and therefore the same values are shown (though this might happen independently of the environments used).
The queries were run in a trial Microsoft Fabric environment. Other environments can have upon case different properties. Just replace the "--" from the commented code to get a complete overview.
The queries should run also in the other editions of SQL Server. If DATABASEPROPERTYEX is not supported, one should try DATABASEPROPERTY instead.
Happy coding!
Previous Post <<||>> Next Post
References:
[1] Microsoft Learn (2024) SQL Server
2022: DATABASEPROPERTYEX (Transact-SQL) [link]
[2] Microsoft Learn (2024) SQL Server 2022: SERVERPROPERTY
(Transact-SQL) [link]
No comments:
Post a Comment