Azure SQL and MI - scripting out a database or just some objects, and putting it in GIT.

I need this. You need this. We all need this. Source control all the things.

 <#

.SYNOPSIS

  Will script an object from SQL Server and CHECKIN/ADD to git.

.EXAMPLE 

  sql_script_to_git.ps1 -server yourservername -Database yourdatabasename -ScriptType "FULL" -Author yourgitname -Comment "full checkin of database"

#>

# Purpose - given parameters, script out an object from a SQL Server, using SMO, then check it into git.

#Scripting code gleefully copied from Phil Factor - the great parts that work are his, the errors are mine

# https://www.simple-talk.com/sql/database-administration/automated-script-generation-with-powershell-and-smo/

#Other code from others copied and attributed below.

#mdb 2013/12/03 adding ScriptType so that we can add "create database"-specific changes ONLY. 

#     To do FULLs you will have to have  -ScriptType = 'Full' parameter. By default it will do objects.

#mdb 2013/12/05 trying to better bullet-proof the app against TF.exe failures, which appear to occur randomly

#mdb 2013/12/07 works great, though occasionally it cant script out an item.  Trying to bulletproof that

#mdb 2013/12/16 broke the individual-item portion, fixed. 

#mdb 2024/10/03 we're back, baby! Modifying to handle the sqlauth needed, the keyvault, and git instead of TFS.

#param has to be the first line of the script.

param( 

        [Parameter(Mandatory=$true,Position=1)]

         [string]$Server, 

        [Parameter(Mandatory=$true,Position=2)]

         [string]$Database, 

        [string]$ScriptType ='Object',

        [string]$SchemaToScript, 

        [string]$ObjectToScript, 

        [string]$Author = 'powershellautomation0104', 

        [string]$Comment

      )

        #make sure not ObjectToScript blank if scripting out the entire DB; makes checkin better

if ($null -eq $SchemaToScript) {$SchemaToScript = ''}

if ($null -eq $ObjectToScript) {$ObjectToScript = ''}

#these are the parameters for testing

#$Server='myservername'# the server it is on

#$Database='mydbname' # the name of the database you want to script as objects

#$SchemaToScript = ''

#$ObjectToScript = ''

#$Author = 'mynameandemail'

#$ScriptType ='Full'

#$Comment = 'author test'

#setting up an error code for later

$myerror = 0

#getting the password from keyvault.

Connect-AzAccount -Tenant tenantnamegoeshere -Identity -AccountId acctnamehjere -Subscription subnamehere

$sqlusername = "sqlloginhere"

$sqlpassword = Get-AzKeyVaultSecret -VaultName "keyvaulthere" -Name $sqlusername -AsPlainText

if ($comment -eq '')

{ $comment = "No Comment Given"}

if ($author -eq '')

{ $author = "authorhere"} 

#field is mandatory, if we dont know, use a known-valid.

$ServerNameClean = "$(($server -split "\.")[0] -replace '[\\\/]','__')"

clear 

#writing this out for logging and troubleshooting. These are all the parameters except ScriptType

write-host $Server, $Database, $SchemaToScript, $ObjectToScript, $Author, $Comment

#Git workspace folder - whatever you set it up as on your server

$DirectoryToSaveTo='c:\repos\DatabaseSchema' # the directory where you want to store them, within the git repo

#doing a pull prior to checking in, due to "error: failed to push some refs to"

set-location $DirectoryToSaveTo

#handling crappy git responses as per https://github.com/dahlbyk/posh-git/issues/109

$env:GIT_REDIRECT_STDERR = '2>&1'

& 'C:\Program Files\git\bin\git.exe' pull origin master

# Load SMO assembly, and if we are running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries

$v = [System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.SqlServer.SMO')

if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {

    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | out-null

}

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SmoEnum') | out-null

set-psdebug -strict # catch a few extra bugs

$ErrorActionPreference = "stop"

$My='Microsoft.SqlServer.Management.Smo'

$srv = new-object ("$My.Server") $Server # attach to the server

$conContext = $srv.ConnectionContext

$conContext.LoginSecure = $false

$conContext.Login = $sqlusername

$conContext.Password = $sqlpassword

if ($srv.ServerType-eq $null) # if it managed to find a server

   {

   Write-Error "Sorry, but I could not find Server '$Server' "

   return

}

$scripter = new-object ("$My.Scripter") $srv # create the scripter

#Add the various options we care about

$scripter.Options.ToFileOnly = $true   

$scripter.Options.ExtendedProperties= $true # yes, we want these

$scripter.Options.DRIAll= $true # and all the constraints

$scripter.Options.Indexes= $true # Yup, these would be nice

$scripter.Options.Triggers= $true # This should be includede

$scripter.Options.AppendToFile = $False

$scripter.Options.AllowSystemObjects = $False

$scripter.Options.ClusteredIndexes = $True

$scripter.Options.DriAll = $True

$scripter.Options.ScriptDrops = $False

$scripter.Options.IncludeHeaders = $False #so you do not get the one line "scripted at..." which would be NOW.

#$scripter.Options.ToFileOnly = $True

#$scripter.Options.Indexes = $True

$scripter.Options.Permissions = $True

$scripter.Options.WithDependencies = $False

$scripter.Options.Bindings = $true

$scripter.Options.IncludeDatabaseRoleMemberships = $true

#phil factor does a bit where he checks it in separately, but i'm doing it this way. The only downside is that SMO won't script out database-level properties.

#################################################

#First, script out the database "create" itself.# 

#################################################

if (($Database) -and $ObjectToScript -eq '')  #if database has a value but there is no object, script out the DB.

{

$db_scripter = new-object ("$My.Scripter") $srv # script out the database creation

$db_scripter.options=$scripter.options # with the same options

$db_scripter.options.filename="$($DirectoryToSaveTo)\$($ServerNameClean)\$($Database)\$($Database)_database_create.sql" # with the same options

#explcitly creating the path for the DB script here. We still need to do it for all the sub-types.

# Could probably move the $d declaration up here, but leaving it here for readability.

$SavePath="$($DirectoryToSaveTo)\$($ServerNameClean)\$($Database)"

# create the directory if necessary (SMO does not).

if (!( Test-Path -path $SavePath )) # create it if not existing

     {Try { New-Item $SavePath -type directory | out-null }

     Catch [system.exception]{

         Write-Error "error while creating '$SavePath' $_"

         return

      }

 }

#putting in a try/catch so we get error messages if it breaks, and it can continue.

try

    {

    $db_scripter.Script($srv.Databases[$Database]) # do it        

    }

Catch

    {  

    "Error Message trying to script out $SavePath\$Filename"

    $error[0].Exception

    }

###########################

## Scripting out Objects ##

###########################

# we now get all the object types except extended stored procedures

# first we get the bitmap of all the object types we want

# and we had to exclude a bunch in order to support Azure SQL Database/MI.

# tested by running stuff like this against every option in: https://learn.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.smo.databaseobjecttypes?view=sql-smo-160 (SMO DatabaseObjectTypes Enum)

# $srv.databases[$Database].EnumObjects( [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::DatabaseEncryptionKey )

# -bxor in this case means "don't use these!". They are non-scriptable via Azure SQL DB's SMO, which makes sense since it can't include them. Unsure why SMO can't do DatabaseAuditSpecification, though.

$all =[long] [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::All `

-bxor [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::DatabaseEncryptionKey          `

-bxor [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::DatabaseAuditSpecification     `

-bxor [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::RemoteServiceBinding           `

-bxor [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::SearchPropertyList             `

-bxor [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::ServiceBroker                  `

-bxor [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::ServiceContract                `

-bxor [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::ServiceQueue                   `

-bxor [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::ServiceRoute                   `

-bxor [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::MessageType                    `

-bxor [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::Certificate                    `

-bxor [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::ExtendedStoredProcedure #we're ignoring this as per the original version, don't remember why.

# and we store them in a datatable

$d = new-object System.Data.Datatable

# get almost everything; skipping most service broker, information_schema, system_views, certificates (cannot be scripted)

#  there are other items that may need to be skipped, like SymmetricKeys in SSISDB

# Yes, I realize the irony in skipping SB given that it is powering this.  

        #putting in a try/catch so we get error messages if it breaks, and it can continue.

        #and note that we're excluding stuff that's above, but also other stuff that does enumerate but we don't want. 

        try

            {

            $d=$srv.databases[$Database].EnumObjects( $all) | `

                Where-Object {$_.Schema -ne 'sys'-and $_.Schema -ne "information_schema" -and $_.Schema -ne "INFORMATION_SCHEMA" -and $_.DatabaseObjectTypes -ne 'ServiceBroker' `

                -and $_.DatabaseObjectTypes -ne 'Certificate' `

                -and $_.DatabaseObjectTypes -ne 'MessageType' `

                -and $_.DatabaseObjectTypes -ne 'ServiceContract' `

                -and $_.DatabaseObjectTypes -ne 'ServiceQueue' `

                -and $_.DatabaseObjectTypes -ne 'ServiceRoute' `

                -and ($SchemaToScript -eq '' -or $_.Schema -eq $SchemaToScript) `

                -and (($ObjectToScript -eq '' -and $ScriptType -eq 'Full') -or $_.Name -eq $ObjectToScript) `

                -and (!(($_.DatabaseObjectTypes -eq 'Schema' -or $_.DatabaseObjectTypes -eq 'DatabaseRole') -and $_.Name  -match "db(_)*" -and $_.Name -ne 'dbo')) `

                -and ($_.Name -notmatch 'sp_MS*') }

            # mdb 2013/11/07 previous line skips replication objects.  This comment below code as comment lines break extended 1-liner.

            # mdb 2024/10/03 previous line to that is because it would script out the db_datareader etc roles.

            }

        Catch

            {  

            "Error Message trying to enumerate the database - may be logshipped or being restored"

            $myerror = 1

            $error[0].Exception

            write-host $error

            }

# List every item that we are going to do

$d = $d | sort

# $d | select databaseobjecttypes, schema, name

 

if ($d.Count -gt 10000)

{

"skipping the database objects - more than 10000"

}

# Now write out each scriptable object as a file in the directory you specify

#it appears that an empty array never actually enters the FOREACH, leaving variables unset

# -and -$d.Count -ne 0

if ($myerror -eq 0 -and $d.Count -lt 10001) #20k of objects takes up 5gb of RAM in the PS script and causes problems

{

$d| FOREACH-OBJECT { # for every object we have in the datatable.

   #"" #blank line so each block of error messages is separated out

   $SavePath="$($DirectoryToSaveTo)\$ServerNameClean\$Database\$($_.DatabaseObjectTypes)"

   # create the directory if necessary (SMO does not).

   if (!( Test-Path -path $SavePath )) # create it if not existing

        {Try { New-Item $SavePath -type directory | out-null }

        Catch [system.exception]{

            Write-Error "error while creating '$SavePath' $_"

            return

         }

    }

    # tell the scripter object where to write it, and make sure it is actually writeable

    if ($_.schema)

    {

    $Filename = "$($_.schema -replace '[\\\/\:\.]','-').$($_.name -replace '[\\\/\:\.\ ]','-').sql";

    }

    else

    {

    $Filename = "$($_.name -replace '[\\\/\:\.]','-').sql";

    }

    $scripter.Options.FileName = "$SavePath\$Filename"

    $scripter.Options.FileName #print it out so we know what is being done

    $UrnCollection = new-object ('Microsoft.SqlServer.Management.Smo.urnCollection')

    $URNCollection.add($_.urn)

    try

    {

    $scripter.script($URNCollection)

    }

    Catch

    {  

    "Error Message trying to script out $SavePath\$Filename"

    $error[0].Exception

    }

    #"script done"

}

}

}

<# GIT checkin - somewhat easier than before #>

Write-Host "Checking in of Files to Git "

Set-Location "$DirectoryToSaveTo"

#force commit and push all files

#git add .

#git commit -m "$($Comment)" --author="$($Author)"

#git push -f --porcelain #the two switches don't work together.

#git push --porcelain

#doing it this way since the container(?) account doesn't have the path for git.

#and this is so incredibly finicky. can't use -m and -c, have to have values set for -c since it's a network service account, etc.

#you need to run, as a job step, 

#do NOT get clever with the path. putting it in quotes so you can say program files causes failures.Maybe.

& 'C:\Program Files\git\bin\git.exe' add .

& 'C:\Program Files\git\bin\git.exe' commit -m "$($Comment)" --author="$($Author)"

& 'C:\Program Files\git\bin\git.exe' push --porcelain

# -m "$($Comment)" 

#go back to script directory

Set-Location $PSScriptRoot

Azure MI - Failed email -2147467259

A job step on an Azure Managed Instance that calls msdb.dbo.sp_send_dbmail (or possibly calling a linked server) can fail with error -2147467259.

The fix is to put "EXECUTE AS LOGIN = 'SQLAGENTUSER' " at the front of the code inside the job step. What's happening is that the Login that's used by the agent (ContainerAdministrator) is lacking perms (MSDB for email, but also linked server).

Applying Query Store hints to fix cardinality estimation issues where you can't change the query.

This morning I had a performance issue on a piece of code that worked long ago on a different server, and they were trying to put it in place today.  It was SLOW. Like, 10 minutes slow. With the added bonus that it's done through a web app, so it never finishes, it just always times out. After dealing with various approaches, I finally tried using the old Cardinality Estimator, and it went from 10 minutes to 3 seconds. But the query is inside the application, it doesn't call a stored procedure. Which means the devs changing it is Non-Trivial. So I went to an updated version of an old trick - query store hints (which used to be Plan Guides)

In theory you can probably get enough info from the query store/plan cache, so you can find the exact query  

What I did: 

• ran a profiler trace for that user/database. You could do this in Azure Data Studio for Azure SQL Database - I got "lazy", and since it was on a Managed Instance, used Profiler. Don't judge me. :)
• found the exact statement that their code ran. Easiest way to get this (it needs to be EXACT down to the white space) is to right-click on the cell in profiler, extract events, save to a file, then copy/paste out.
• found the AUDIT LOGIN from when it ran
• launched SSMS
• in one window, posted all of the SET QUOTED_IDENTIFIER (etc) statements from the AUDIT LOGIN, newline, "go", newline, then copy/paste from that second step. NO trailing anything. Ran it. Waited the 10 minutes for it to finish. You need to do it this way because the application default settings are not the same as the SSMS defaults (for more info, read Erland's seminal work "Slow in the Application, fast in SSMS?" https://www.sommarskog.se/query-plan-mysteries.html#defaultsettings), specifically 2.5.
• Expand that database in SSMS.
• Query Store->Top Resource Consuming Queries
• Mouse over and find the query in the left-side. Get the query_id (not the plan id). For me, it was 9605.
• run this in that database: EXEC sys.sp_query_store_set_hints @query_id= 9605, @query_hints = N'OPTION(USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';
• have people test it and make sure it works.
• later, go into Query Store->Tracked Queries, put in the number above (for me, 9605), and see if it's getting called, and how long it takes.  Oddly, it doesn't show up in "Queries with forced plans".  : ( Unsure how to find queries that we've tuned this way, maybe Managing Query Store Hints - Simple Talk (red-gate.com)  ?).

ODBC OLEDB - returning the version of the actual driver using powershell.

 I have no idea why this is that difficult. The get-odbcdriver cmdlet is overly complicated to read, and doesn't return the actual DRIVER VERSION. The "DriverODBCVer", I believe, is the version of ODBC that's supported.

odbc driver file versions (github.com)

<script src="https://gist.github.com/mbourgon/05c75cda141745e4244bbe876cba5c10.js"></script>

Azure SQL Database - ports you didn't realize you needed for your firewall request.

 Azure is fun. FUN, I say!

Ports: 

1433 - standard SQL port

11000-11999 - ports if you're using Proxy instead of Redirect

14000-14999 (!) - ports for the DAC (Dedicated Admin Connection)

Mermaid (the language) is life!

Just found out about this the past month. 

I like diagrams for my documentation, and I detest making it. I also would like to build it via script, since that's more useful.

Sample:

graph TD;

     A-->B;

     A-->C;

     B-->D;

     C-->D;

Which produces: 

Pretty cool right? 

VSCode supports it (load the extension "Markdown Preview Enhanced"), but Jira and Confluence don't support it natively.... 

But there's a workaround!

In Chrome, load the extension Mermaid Previewer (https://chrome.google.com/webstore/detail/mermaid-previewer/oidjnlhbegipkcklbdfnbkikplpghfdl)

There are a couple of tweaks. For one, you need to enable sandbox mode in the extension. 

For Confluence, I put the site as my\.confluence\.site and a selector of pre > code. To add it, I create a markdown block, but within it do this:

```mermaid

graph LR

A --> B

```

(update 2023/10/27) another way that seems to work for confluence is to make a regular code block ( for me, type ``` by hand, then do it with just the "graph LR / A --> B"), and in Mermaid Previewer as div.container, but I don't know how that affects other "containers". the nice thing about doing it the other way is that (for some reason) you HAVE to have the ```mermaid, which means it can't accidentally go crazy with it.

For Jira, my selector is pre.code-java, since by default our code blocks are Java. You can look at the element in Chrome developer mode (right-click, choose "inspect").

Then, within the Jira ticket, just do:

graph LR

A --> B 

(NOTE THE SPACES!) No idea why it doesn't act the same between them, but for me it's fine.

SSIS and Parameters and where to set them

 SSIS has a LOT of ways to set configs. Parameters & variables, and then those can be overridden at different levels.

in order (from lowest to highest) - bottom of this list supercedes the top. You can see WHERE it was overridden because at that level, the parameter name is in bold. 

1. SSIS Package level
2. SSIS Project level
3. XML Configs? (I don't remember where these can be set)
4. SSISDB - right-click on the project within integration catalog and choose "Config"
5. SSIS Job Step configuration

Azure Data Factory - how to ACTUALLY use storage event triggers to copy data from Blob Storage to Azure SQL Database (Azure SQL DB)

 This post is in response to an incredibly frustrating interaction with Azure.

I figured I would do something incredibly simple - import a parquet file from blob storage into Azure SQL DB. When the file is written to Blob Storage (using CETAS, fwiw), it should activate the storage trigger, which should kick off the pipeline. 

The problem is that it won't work as the tutorial (https://learn.microsoft.com/en-us/azure/data-factory/tutorial-copy-data-portal) shows. It says it can't find the file. Why not? Well, it wasn't firewalls or anything else. It's that a linked service dataset, in "Sink" requires a container name - and the trigger sends the container name also. So I kept getting messages that it couldn't find \mycontainer\mycontainer\myfolder\myfile.parquet . I found a single message on Stack Overflow (https://stackoverflow.com/questions/67294521/azure-data-factory-how-to-get-new-file-location-from-triggerbody-folderpath/74738786) on how to solve it. So, here we go: 

package parameters:

Source:

@pipeline().parameters.trigger_folder_path

@pipeline().parameters.trigger_file_name

My Source Dataset:

Sink:

And the last part in the trigger that makes it all work:

Here's what to put for trigger_folder_path, as per Steve Johnson on SO. No, you don't have to change the YAML, you can modify the values above: 

@substring(triggerBody().folderPath,add(indexof(triggerBody().folderPath,'/'),1),sub(length(triggerBody().folderPath),add(indexof(triggerBody().folderPath,'/'),1)))

Why it's that hard to find/use, I have no idea. 

All Hail Steve Johnson!

[rant] Freaking Microsoft.Graph cmdlets for 1.27 - I swear, y'all.

 Never, for the love of god, run update-module -all. 

I went from 1.23 to 1.27 and my scripts broke.

First problem: incompatibility with Microsoft.Graph.Authentication. I had multiple copies of it, and uninstall older versions didn't work - had to uninstall ALL of microsoft.graph (not easy, whhhhyyyy), then reinstall WITH "-requiredversion 1.27" (or was it 1.27.00?).  Otherwise it still grabbed the wrong versions somehow.

Second problem: invalid filter clause

get-mgchat : Invalid filter clause

At line:1 char:1

+ get-mgchat -all -PageSize 50 -filter "lastUpdatedDateTime gt '2023-05...

why? Because between 1.23 and 1.27, they stopped accepting the datestring being in quotes. Now, if it is, it doesn't work.  What the everliving...

Patching Visual Studio via Powershell

 

Basic script to update all your Visual Studio instances. On some servers I have/need 3.

The VSSetup is the magic, courtesy of Microsoft (and hence the prereqs), the rest is just running their installer. 

#this didn't find the 2022 install. Run as ISE admin

Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -force

set-psrepository psgallery -InstallationPolicy Trusted

Install-Module VSSetup 

get-vssetupinstance|%{

$installpath = $_.installationpath

"$installpath"

Start-Process -Wait -FilePath "C:\Program Files (x86)\Microsoft Visual Studio\Installer\vs_installer.exe" -ArgumentList "update --passive --norestart --installpath ""$installpath"""

}

#this should work, but doesn't. Just kicks back instantly.

#Start-Process -Wait -FilePath "C:\Program Files (x86)\Microsoft Visual Studio\Installer\vs_installer.exe" -ArgumentList "updateall --passive --norestart"