In the realm of database management, the BACPAC format is widely used for exporting and importing data between databases. However, we have found an interesting service request, where the recommended automatic indexes are not included in the exported data used by BACPAC.
The Scenario:
During a remote session with a customer, a comparison between the source and target databases revealed a discrepancy in the indexes. Specifically, while the source database contained three types of indexes (PK_xxx, IX_xxx, and NCI_xxx), only the manually created PK_xxx and IX_xxx indexes were visible in the target database after restoration. The absence of NCI_xxx indexes prompted an investigation into the BACPAC export process.
Unveiling the Missing Indexes:
To shed light on the issue, the .BACPAC file extension was changed to .ZIP, allowing access to the model.xml file. By analyzing the file, a search for PK_xxx and IX_xxx indexes revealed their presence, but the NCI_xxx indexes were absent. Further examination of the sys.indexes view showed a significant distinction: the column auto_created held a value of 1 for NCI_xxx indexes, indicating that they were automatically created by Azure.
Uncovering the Export Query Condition:
A test conducted in an on-premises environment using SQL Profiler provided valuable insights. The query responsible for exporting indexes contained a condition wherein auto_created = 0, thereby excluding automatically created indexes. This finding explains the root cause of the missing NCI_xxx indexes in the BACPAC export.
Enjoy!
Microsoft
