Lesson Learned #211: Monitoring SQLPackage Import Process

Published Jun 27 2022 06:08 PM 1,309 Views

We used to have cases where our customer asked what is current of SqlPackage state, do I need more resources in my database to import the data?, etc.. In this video below we are going to give some insights about it. 

 

I developed this small PowerShell Script to run some queries using some DMVs to obtain the current status of SQLPackage Import process:

 

  • Check the rows, space used, allocated and numbers of tables per schema.
  • Check the number of connections and current status of sessions established by SQLPackage.
  • Show the number of requests and wait stats.
  • Show the number of indexes and constrains disabled or not.
  • Show the performance counters of the database.

 

Basically we need to configure the parameters:

  • Connectivity
    • $server = "xxxxx.database.windows.net" // Azure SQL Server name
    • $user = "xxxxxx" // User Name
    • $passwordSecure = "xxxxxx" // Password
    • $Db = "xxxxxx" // Database Name, if you type the value ALL, all databases will be checked.
    • $Folder = $true // Folder where the log file will be generated with all the issues found.
  • Outcome
    • PerfSqlPackage.Log = Contains all the data collected found.

 

This project is public and all the contributions to make more accesible, easy and effective will be very welcome. 

 

 

Enjoy!

 

Version history
Last update:
‎Jun 28 2022 03:06 PM
Updated by: