modelrail.otenko Trains, computers, vintage, retro, model railways… the lot…

Restoring an Azure DB Bacpac into SQL Express

Every so-often, one receives a request out of the blue to revive things that one has totally forgotten about. In most cases, it's comments on pages on this hideous blog asking questions about things that I've gotten rid of, in one form or another, and can't answer! Fortuantely, this time I could: it was a request to revive a project which had been on ice for a long time, but had been well-backed-up... or so I thought.

Firstly, it took me two days to find the files... not cool. If you shut down a project, get EVERYTHING and put it in ONE LOCATION. The system comprised of a zip of the IIS inetpub folder, a zip of the data drive and finally, a database backup from the SQL-as-a-service database. This, thanks to Azure not providing direct access to the server, takes the form of a bacpac file.

Back in the day, when developing, the preprod server was located on a HP something-something with 44gb of RAM which hosted multiple VMs. It made a fantastic noise when it first booted, making sure the fans could hit the required RPMs, and so it was left on most of the time. It was quite the powerhouse and did everything we needed. To save cost, the preprod server only ran SQL Express, so whenever we needed to back-port the production database to diagnose and squash bugs, we needed to manipulate the backups to make them fit.

What do I mean by this? Well SQL Express (or, at least the old 2014 version running in the VM) isn't as capable as Azure DBs and therefore you needed modify the backups so that they wouldn't throw errors when they were being restored. Below is a perfect example (this happened tonight) of such errors...

All looking OK, taking large amounts of time, and then...

Bang! With is not a valid operator... what's it doing embedded in a SQL script? Let's find out! Did you know that a bacpac file is just a pkzip archive? Install 7-zip, right click on the bacpac file and choose Open Archive.

Nice, contents. You'll find the database tables stored as BCP files in the Data folder and actual entire structure and scripts in the model.xml file. If you have your favourite editor installed correctly, then you should just be able to double-click model.xml and edit the file inline.

We know that the error is about shared access keys, so I CTRL-F'd the relevant keyword and the result was at the very bottom of the file. It's trying to store the logging and auditing on Azure shared storage... which is obviously not a good idea if we're running a private server. It's also totally incompatible with SQL Express, so lets rip it out.

<DataSchemaModel>
	<Model>
		...
		<Element Type="SqlDatabaseCredential" Name="[https://azure.net/sqldbauditlogs]">
			<property Name="Identity" Value="SHARED ACCESS SIGNATURE">
		</Element>
		<Element Type="SqlDatabaseCredential" Name="[https://azure.net/sqldbtdlogs]">
			<Property Name="Identity" Value="SHARED ACCESS SIGNATURE" />
		</Element>
		<Element Type="SqlFullTextCatalog" Name="[SnomedCatalog]">
			<Property Name="IsAccentSensitive" Value="True" />
			<Relationship Name="Authorizer">
				<Entry>
					<References ExternalSource="BuiltIns" Name="[dbo]" />
				</Entry>
			</Relationship>
		</Element>
	</Model>
</DataSchemaModel>

I also ripped out the Full-Text Searching block above, as shown, and another block much further above that was also referencing this block. Once I'd made all the changes to the zip file, I closed my editor and went back to 7-zip.

Yes, 7-zip, I very muchly would love to commit the changes back into this 1gb archive, thank you. From here, I attempted to deploy the bacpac to the server...

Ok, right, by editing a file inside this archive, we've trashed the thumbprint. A checksum is a magical calculation of a chunk of binary data that, somehow, resolves it down to a unique number that indicates the bits. If a bit changes, the checksum number changes and this allows software to know if things have been tampered with. That's what has occurred here... we've actually tampered with it and it's changed the calculation.

The SQL Server has run a calculation on the file and the result has mismatched with something, but with what? Turns out that in the original.xml file is a checksum value! We need to change this value to the new checksum, but how do we calculate that? Don't even bother thinking, just copy the following script into a file called checksum.ps1 and store it in your home directory.

$modelXmlPath = Read-Host "model.xml file path"
$hasher = [System.Security.Cryptography.HashAlgorithm]::Create("System.Security.Cryptography.SHA256CryptoServiceProvider")
$fileStream = new-object System.IO.FileStream ` -ArgumentList @($modelXmlPath, [System.IO.FileMode]::Open)
$hash = $hasher.ComputeHash($fileStream)
$hashString = ""
Foreach ($b in $hash) { $hashString += $b.ToString("X2") }
$fileStream.Close()
$hashString

If you've never run scripts in Powershell before, then you'll need to run the following command first if you get an error like 'checksum.ps1 cannot be loaded because running scripts is disabled on this system.'

set-executionpolicy remotesigned

Once sorted, run the following:

PS D:\> checksum.ps1
model.xml file path: d:\model.xml
898D9323A53F0C52B782867B9E8343D8371059A1192849F793CEA168FEAC7AA0

That final number needs to be pasted into original.xml in the archive. Save it back into the archive and deploy once more. Note that, before you try to deploy again, you'll need to detach the half-deployed database and delete the mdf/ldf files that were half-stored in the deployment folders that you set when you tried previously.

I then hit another error, a long way into deployment where the server said that AT was a shit command. Turns out one of my previous developers had tried to cast a datetime into a timezone using the query getdate() AT TIME ZONE 'UTC' AT TIME ZONE 'Aus Eastern Standard Time'. Neat! But SQL Express hates that... so... open the archive, edit model.xml, save it, copy it out of the archive, checksum it, edit original.xml and deploy again...

Fiinnnnaaalllyyyy....!!! IT'S ALIVE! ... Hah... it's not...

dbo.Sort Failed? What dat? Google! There's always someone else that has had your error before... tempdb full? Let's add a new 100gb disk and slap tempdb over there. You can do this by running the following in a new query editor against master...

USE master;
GO

ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'T:\MSSQL\DATA\tempdb.mdf');
GO

ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'T:\MSSQL\DATA\templog.ldf');
GO

And you'll need to restart SQL before it applies...

The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.

Completion time: 2020-04-08T22:55:04.1929547+10:00

Of course, you'll need to change T drive to something else! Enjoy!... or not... what the hell is this error?

Right, memory? Is that physical memory? Swapfile memory? Or storage space? Fortunately, it's a VM, so lets increase every one of those. If you have the RAM, then that's easy... slide that slider to the right. Disk space is a little trickier. Open the main Virtual Box console and choose Virtual Media Manager from the File menu. From here, select your disks, slide the slider to the right and then hit apply. Note that this will only change the dimensions of the disk, it wont touch the partitions.

So, to extend the partitions, open Disk Management (right-click the start button) and then find the drive. Once located, right-click and choose Extend Volume. If the free space is directly after the drive, it'll already be selected and you can just hit nekk-nekk-finish. Enjoy!