With SAS® 9.3 the SAS code for a Stored Process can be located in metadata rather than located the file system (as was required with earlier versions). I had often wondered whether this meant the SAS code was really in metadata or whether it appeared to be in metadata but was really kept in the SAS Content Server (as is done with SAS Web Report Studio report definition .srx XML files). I don’t like not knowing the answers to questions, even ones I ask myself, so today I went looking for an answer. Continue reading “SAS Stored Process Code in Metadata”
Metadata Time Travel with SAS 9.3
The way SAS metadata backups are done in SAS® 9.3 has changed significantly. Significantly for the better I think. There are big changes but they are well well worth it.
I’ll warn you now that this is a very long blog post with lots of images. I wanted to show someone how metadata backups are done in SAS 9.3 and also run a few tests for myself so a blog post seemed like a good idea. It turned out to be much longer than I expected though. I’ll split the post up into a short version and a long version. Here’s the short version first …
The Short Version …
I think SAS 9.3 metadata backups are fantastic! They are integrated, configured by default, and continuous. It’s almost worth upgrading to SAS 9.3 just for the metadata backups (let alone all the other enhancements) :)
- Integrated: SAS 9.3 metadata backups are performed by the metadata server itself in a background thread. In SAS 9.2 and SAS 9.1.3 the %OMABAKUP macro (and associated backup wizard front end) was used. This macro paused the metadata server, made copies of the metadata repository tables, repository manager, and key config files, then resumed the metadata server. SAS 9.3 metadata backups don’t even require the metadata server to be paused anymore.
- Configured by default: With SAS 9.1.3 and SAS 9.2 someone, usually the installer or the administrator, had to consciously decide to configure SAS metadata backups and arrange for them to be scheduled. In the past I had spoken to people who didn’t do metadata backups or assumed their file system backups would be sufficient. With a brand new SAS 9.3 installation, metadata backups will be scheduled by default: daily/nightly at 1am, with weekly reorganizations, and keeping 7 days of prior backup history. Even those sites that didn’t know they should be doing metadata backups should have them without realizing it. You would have to make a conscious decision to disable metadata backups rather than a decision to enable them.
- Continuous: This is my favourite. SAS 9.3 is effectively backing up metadata continuously. With SAS 9.2 and 9.1.3 only periodic, usually daily, snapshots of metadata were taken – if disaster struck at the end of the day you had to decide whether it was worth losing the days metadata changes to revert to the previous nights snapshot. In SAS 9.3 the combination of regular metadata snapshots/backups with journals of metadata transactions provides a continuous backup. The journal, previously only used for performance gains, now also acts as a replay log for metadata transactions. This allows roll-forward recovery of metadata to any point in time. It’s like having a “metadata time machine” – you can roll back to any point in the history you have kept, be it 5 days ago or 5 minutes ago.
Some might say that these concepts have been around in other software for years. That may well be the case, but the fact that SAS Institute are now providing this method of metadata recovery in SAS 9.3 is still a stroke of genius in my opinion.
More information on SAS 9.3 metadata backups can be found in the SAS® 9.3 Intelligence Platform: System Administration Guide in the section Backing Up and Recovering the SAS Metadata Server
The Long Version …
If you are only interested in the short version then stop reading here. Otherwise, please read on. You might want to grab a tea/coffee/beer/wine and settle in though. Don’t say I didn’t warn you. ;)
I wanted to test out the roll-forward recovery feature with SAS 9.3 metadata backups in a scenario that would not be recoverable using nightly SAS 9.2 or SAS 9.1.3 metadata backups. I decided to test recovering content that was both newly created and accidentally deleted, on the same day, between 2 consecutive nightly snapshots/backups. With an effective continuous backup in SAS 9.3, roll-forward recovery should allow a restore to the point in time between the content creation and its accidental deletion. The following timeline diagram illustrates the steps taken in this test:
In the diagram above the green boxes represent metadata backups or snapshots (either scheduled or ad-hoc). The yellow boxes represent the metadata journals being all the metadata transactions that follow the prior snapshot. The grey boxes represent the metadata backup directories that contain a snapshot backup and a journal file containing the metadata transactions up to the time of any subsequent snapshot backup.
The sequence of events for this test was as follows:
- 06Oct2011:01:00: the normal scheduled nightly backup
- 06Oct2011:19:49: an ad-hoc backup taken late in the day just prior to the test content creation and deletion. This ad-hoc backup was not really necessary. The previous backup would have been sufficient, but it gave me a bit of extra practice with ad-hoc backups!
- 06Oct2011:19:58: some test content was created – in this case a folder and a stored process.
- 06Oct2011:20:02: the test content was ‘accidentally’ deleted. With the creation and deletion both occurring between snapshot backups, this would not normally be recoverable without the roll-forward capability (and in the absence of any intermediate snapshot backup or personal metadata export (.spk) backup taken after the content creation but before its deletion).
- 06Oct2011:20:06: another ad-hoc backup taken just after the test content deletion. This additional ad-hoc backup was also not necessary but it did allow me to verify that roll-forward recovery could still be done with an ad-hoc backup taken after the ‘disaster’.
- 06Oct2011:20:22: the roll-forward recovery restore. To get the content back, this had to be timed between the content creation (19:58), and it deletion (20:02). Half way in between at 20:00 should do. A restore was done using the 19:49 backup, the most recent one prior to the deletion, rolled-forward to 20:00, just before the deletion occurred at 20:02.
The rest of this post shows, with a number of screenshots, some of the new features of SAS 9.3 metadata backups. It also illustrates the process of running the test discussed above via SAS Management Console 9.3.
Exploring SAS 9.3 Metadata Backups
The SAS Management Console 9.3 Metadata Management plug-in provides access to a historical list of metadata backups under the Metadata Utilities/Server Backup plug-in folder. The screenshot below shows a few of the available scheduled nightly 1am backups (the ones with the green tick icons) and a couple of expired backups (with the yellow warning icons).
The table in the screenshot above shows basic summary information for the available backups. More detailed information can be found in the properties dialog for each backup. The properties dialog has a General tab with general information like status, date/time, location, size etc:
… and a Repositories tab showing which metadata repositories are included in the backup:
Metadata backups can be configured by right mouse clicking over the Server Backup plug-in folder and selecting one of the items from the context menu:
Selecting the “Backup Schedule…” context menu item displays a dialog where the backup schedule can be modified. The screenshot below shows the default schedule for backups: nightly backups at 1am every day of the week with a reorganization (reclaiming space for logically deleted metadata) on Mondays (i.e. 1am Monday morning or Sunday night depending on how you think about it).
Selecting the “Backup Configuration…” context menu item displays a dialog (shown below) where you can specify where you want the backups to be stored and how many days of backup history to keep. The defaults (unless modified during installation) are to keep 7 days of backups in the Backups folder underneath the main MetadataServer configuration folder. Depending on how your metadata server has been configured, this might potentially be on the same storage device/volume as your metadata repositories. If so, it would be advisable to move them to a different device/volume to improve your chances of being able to restore from backup if you happen to lose the storage volume the repositories are on. In terms of backup history, I personally feel 7 days is a bit short. If space allows, I prefer to keep around 1-3 months of backups. Usually I compress metadata backups to allow for more history. The option to automatically compress (zip, tar/gz, tar/bzip2 etc) backups during backup, and then expand them during restore, would be a useful enhancement I think. Whilst you probably wouldn’t want to do a full restore from more than a day or two ago, I have on occasion needed to export a metadata package from a 3 week old backup to import/restore partial content. I like to keep a basic Lev9 deployment available for such things. I could imagine another potential enhancement where you might use the Metadata Manager to export an SPK package of metadata as at a historical point in time (derived from a temporary roll-forward recovery) to immediately import (removing the need to use a Lev9 environment or equivalent). :idea:
The “Recover from Alternate Location…” context menu item displays a dialog (shown below) where you can specify the location of an unlisted backup to restore from (a backup which is not in the list of historical backups). I haven’t used this yet but I imagine it would be useful in the situation of needing to do a temporary restore into a Lev9 private admin environment for the purposes of exporting a package of metadata for a partial restore/import. I notice there’s no browse button so it looks like a job for copy/paste.
The “Run Backup Now…” context menu item is used to perform a forced ad-hoc backup between normal scheduled backups. You get to provide a comment/reason for the backup which will be logged in the backup history. You can also elect to reorganize the repositories at the same time if you want, reclaiming any space taken up by logically deleted metadata. Bear in mind that reorganizing does involve a read-only pause of the the metadata server.
You would use this “Run Backup Now…” action to force an immediate backup or snapshot when required, such as just before doing something potentially dangerous, where you want the option to easily restore. In earlier versions of SAS this was required in order to have the ability to restore. With SAS 9.3 it doesn’t sound like we need to be so strict with this practice, since we now have the option for roll-forward recovery if we hadn’t perform a forced backup (but we did keep track of the time of the significant event). I think I’ll stick with performing explicit ad-hoc backups – if not just for recording comments in the backup history log. SAS 9.3 metadata backups don’t need to pause the metadata server, unless reorganizing, so we needn’t be worried about any small windows of unavailability for our users either – all the more reason to backup.
SAS 9.3 Roll-Forward Metadata Recovery Test
This is where I started my testing. At 19:49 just before I was about to do a test of create, delete, and roll-forward restore, I used “Run Backup Now…” to force an ad-hoc backup. This wasn’t really required. I could have done the testing based on the previous night’s backup but with a longer roll-forward. I just wanted to surround the event with a couple of ad-hoc backups to confirm that they wouldn’t have any negative impact on my ability to do the recovery.
After the ad-hoc backup was completed, an information dialog popped up to let me know:
… and the backup was recorded in the backup history list.
I then set about creating some content that I could ‘accidentally’ delete and then attempt to restore. I recorded the times for the various events so I could use them to determine how much roll-forward would be required. I wanted to restore to the point after the creation but before the ‘accidental’ deletion.
At 19:58 I created my test content. This was simply a folder named “Test Restore” containing a stored process named “Test SP” as shown in the SAS Management Console 9.3 Folders tab below:
At 20:02 I then ‘accidentally’ deleted the “Test Restore” folder and all of its contents. 8-O
At 20:06 just after the ‘accidental’ deletion I did another ad-hoc backup. Once again, this wasn’t required. As mentioned before, I just wanted to surround the ‘disaster’ with a couple of ad-hoc backups to confirm that they wouldn’t have any negative impact on my ability to do the recovery.
The two backups immediately before, and after, the creation/deletion can be seen recorded in the backup history list in the screenshot below:
Now it was time to attempt the recovery.
The scenario as described would be unrecoverable if it were SAS 9.1.3 or SAS 9.2. There is no snapshot backup in between the creation and the deletion of the content. None of the available snapshot backups I had, when restored on their own (without roll-forward), would have the deleted content in them. However, by default, SAS 9.3 keeps a log of all metadata transactions since the most recent prior backup in a journal file (unless you configure it otherwise). This allows the transactions to be replayed, or rolled-forward, to any point in time.
To perform a roll-forward recovery in my test scenario, I just needed to choose a point in time where the content still existed, somewhere between the creation at 19:58 and the deletion at 20:02. Half way in between at 20:00 seemed like a reasonable choice. I could recover the deleted content by restoring the most recent backup prior to 20:00 (the 19:49 one) and then roll-forward the transactions up to 20:00.
As shown in the screenshot below I right mouse clicked on the 19:49 backup in the backup history list to select the “Recover from This Backup…” context menu item:
The initial “Recover from This Backup” dialog was then displayed:
A few changes were made to this dialog to control how much recovery was required. I provided some comments, ticked the “Roll forward transactions” checkbox and provided the end time of 2011-10-06T20:00:00 as a local time (Brisbane is GMT+10).
At 20:22 I clicked the OK button to start the restore process. A progress dialog was displayed:
… and when the restore was finished an information dialog popped up:
After the restore I checked the backup history list. I was a little surprised when I didn’t see a Restore entry type, but instead a Backup entry type.
After thinking about it for a little while, it kind of makes sense that a restore should involve a new backup and new journal from the point of restore.
Checking in the SAS Management Console 9.3 Folders tab I confirmed that the ‘accidentally’ deleted content had indeed been restored:
I even ran the stored process to double check it still ran successfully.
SAS 9.3 Metadata Backup Storage
It is interesting to see how the backups are stored too. The screenshot below shows the directory structure for the Metadata Server/Backups folder:
Each metadata backup directory is named using the date and time of the backup. It contains a snapshot backup of the metadata repositories, repository manager, and key configuration files, at the time the backup was taken. It also contains a journal file containing a log of the metadata transactions that have occurred since the backup snapshot was taken. This journal file continues to be used and grow until the time of the next backup, when the journal is closed and retained, and a new journal file is started in the new backup directory. The current journal in use will be the one in the most recent backup directory.
As I’m sure you’ve worked out by now, I’m most impressed with these enhancements to the SAS metadata backup and restore process in SAS 9.3. Whilst we hope to never need to use metadata backups, they can be business critical when they are required, and flexibility is paramount in limiting metadata loss in those rare situations. It’s very comforting to know that I should be able to recover from a much wider set of recovery scenarios with SAS 9.3, including a very fine level of control over the recovery time point. It really feels like we now have a metadata time machine that allows recovery with the absolute minimum loss of metadata changes right up to the disaster point itself.
SAS Metadata Backup on Windows 2008 R2 64-bit
I was trying to run a manual SAS Metadata Server backup (OMABAKUP) on Windows 2008 R2 64 bit today. It failed! I run metadata backups many many times so I was sure all the normal prerequisites were in place but this was the first time I had run one on Windows 2008 R2 64 bit … hmm … I remembered seeing a SAS usage note about metadata backups on Windows 2008 a week or so back. A quick search found SAS Usage Note 38869: What credentials are needed when running a SAS® Metadata Server backup in batch on Windows 2008. It described the error I saw in the SAS log perfectly!
The resolution was to Run as administrator. Even though I was logged in to Windows as an Administrator I still had to explicitly run the SAS program in an administrative context (it’s a Windows security thing). As mentioned in the usage note, this is also documented in the SAS® 9.2 Intelligence Platform: System Administration Guide, Second Edition, Chapter 11 Using the %OMABAKUP Macro to Perform Backups and Restores, in the section Ensuring Appropriate User Credentials.
So I had a SAS usage note describing the problem and the solution. The trouble was I couldn’t quite get the instructions in the SAS usage note to work for me. When I right click over the Command Prompt window (or a PowerShell window for that matter) I don’t get a Run as administrator item. What did work for me though was to right mouse click over the Command Prompt item in the Windows Start Menu, and click the Run as administrator menu item – here’s a quick screenshot to clarify.
That opens a Command Prompt window running in an administrative context so I could then change to the metadata server directory and run the backup:
… a quick check of the SAS log shows everything worked as expected this time.