RENXT is the new-ish cloud version of Raiser's Edge (which as I wrote before, still looks suspiciously like Raisers Edge 7). There is a SKY API where you can poke about with the data but if you want to download the whole lot regularly and have a local copy, there is a way to do that too.
RENXT, being a cloud product, wont let you access the database directly. But you can download the nightly SQL Server backup and restore it on your own SQL Server. Spoiler: The table structure is the same as the RE7 table structure from the on-premise days.
A local copy of your RENXT data is useful for various things:
- Ad-hoc queries in SQL
- Creating Reports using SSRS or PowerBi etc
- Feeding the data into some other system such as a Data Warehouse
If you want a regularly updated, local copy of your RENXT data, here's what you need to do:
You need the NXT Bulk Data Delivery solution
You'll need to purchase the RE NXT Bulk Data Delivery solution from Blackbaud - this is an 'add on' that makes the nightly backups of the cloud database available.
You need a SQL Server
You need a SQL Server. As RENXT runs on Azure your best bet is to have an Azure VM running SQL Server - then when you copy the backup from RENXTs servers to your servers, it should copy pretty fast. That said everything explained here would also work with an on-premise SQL Server. Currently what you need is SQL Server 2019
You need to know how to find the RENXT backups
When logged into the Raiser's Edge NXT site, click on the database icon in the top-right, then select 'Database Options'. This will get you to the 'Database View Options' page which should have a 'Backups' tab. If you can't get to the page or you can't see the Backup tab, you need more admin rights. Which admin rights? Probably all of them. Also, you probably need to be paying for the Bulk Data Delivery option (see above) for the Backups tab to be available.
The 'Backups' tab looks like this:
(NB: Blackbaud's knowledge base describes how to find the backups tab here)
As well as providing ways to download the backups (see below) this page also guides you through some of the setup, which is:
Set up keys/certificates for the backups
The RENXT backups of the cloud database are encrypted with a key, to restore the backups on a SQL Server you need that key/certificate on your SQL Server. The instructions on the first part of the 'Backups' tab walks you through this.
Get the Azure SAS URL for the backups
If you scroll down a bit on the 'Backups' tab you'll see something like this:
The last seven nightly backups will be available. You can download them directly using the links if you like.
The filenames contain the day the backup was made - note that if the backup was made shortly after midnight on Tuesday it will be named 'Tuesday' but really its the backup of Mondays data. The seven filenames are re-used on rotation so to work out which is the newest you have to look at the creation dates.
More useful for automation is the Azure SAS URL, where SAS stands for Shared Access Signature, which is a long URL containing a key to give you access to an Azure folder.
You need this Azure SAS URL, but its a form of credential - like a username and password rolled into one - so dont leave it lying around.
Download the AzCopy tool
There are various ways of using SAS Urls, but the simplest is the AzCopy command line tool that Microsoft provide
A script to find the most recent backup and download it
Here's a bare-bones Powershell script that uses Azcopy to identify the most recent backup and download it. Put AzCopy.exe in the same directory as the powershell script:
Set-Location $PSScriptRoot
Set-Alias -Name azcopy -Value ./azcopy
# AzCopy likes to use lots of RAM, setting this environment variable sets the max
$env:AZCOPY_BUFFER_GB=1
$sasurl = "(your SAS URL as per the RE NXT Database Options - Backups tab)"
$root = $sasurl.split('?')[0]
$sastoken = $sasurl.split('?')[1]
# fetch the list of available files including modified time
$rawfilelist = azcopy list "$($root)?$($sastoken)" --properties "LastModifiedTime"
# file info gets returned in odd string format, reprocess it into something powershell can work with
$filelist = $rawfilelist | ForEach-Object {
$fields = $_.Split(';')
# each field contains field name so trim off the first characters
[PsCustomObject]@{
'INFO' = $fields[0].Substring(6);
'LastModifiedTime' = $fields[1].Substring(19);
'ContentLength' = $fields[2].Substring(18)
}
}
# this prints the list of available files
$filelist | Sort-Object LastModifiedTime -Descending
# this works out the most recent one
$mostrecent = $filelist | Sort-Object LastModifiedTime -Descending | Select-Object -First 1
$mostrecentfilename = $mostrecent.INFO
Write-Output "Most recent file:"
$mostrecentfilename
# download the most recent one
azcopy copy "$($root)/$($mostrecentfilename)?$($sastoken)" "C:\Temp\renxtbackup.bak"
A command to Restore the backup
With the above script, the latest backup will now be downloaded to "C:\Temp\renxtbackup.bak" and from there you can restore it.
To generate the 'restore database' SQL statement you need to restore the backup, set up the restore in SSMS and then instead of running it, choose the 'Script' option. This will generate a restore statement with all the options you need.
Wrap that statement with two 'alter database' statements to put the target database into single user mode and back:
ALTER DATABASE TargetDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
go
[ your RESTORE DATABASE statement here]
ALTER DATABASE TargetDatabase SET MULTI_USER
go
A scheduled SQL job to tie it all together
Create a scheduled SQL job that runs the Powershell script above to find the latest backup and download it. Then add a second step to run the SQL (as discussed above) to restore the database.
Thats it, basically. But also watch out for:
Other tips
- The backups wont necessarily get created by RENXT at the same time every day. Sometimes it might be hours later than usual. So you may need to adjust your script to re-check a few times an hour to see if any recent backups have been posted
- I've found that downloading the backup too soon after it appears can lead to weird results - corrupted backup files or huge files that are mostly empty space. I'm not sure whether this is an Azure bug or a Blackbaud bug. But generally it seems safer to wait an hour or so after a backup has appeared before downloading it