We needed a way to archive data. I have seen this request multiple times in my career and the most common solutions I have seen either used:
- INSERT data into the archive table, then DELETE data from the original table, or
- SSIS, or
- Table partitioning
All of these options are great, but they all have drawbacks that we weren’t happy with.
We needed our process to meet the following criteria:
- Archive anything older than 1 year
- Store archive data in a separate database
- Run the archive process daily
- Do not interfere with other database transactions
- Minimal administrative overhead (Isn’t this always the case? J)
Once again, all of the options I mentioned in the first paragraph could have met these criteria, and I’m sure there are many other options as well. However, I came across an article that presented exactly what I needed:
It’s not anything new, as it was introduced in the 2008 version, but it is pretty handy. I like this option because it only accesses the table from which you are archiving a single time (as opposed to option #1 above), and it comes with very low administrative overhead (unlike options #2 and #3 above).
I created a stored proc which uses dynamic SQL to build archive statements which utilize the method from the article. The proc is called from a SQL Server Agent job, which is run every 10 seconds for a 2 hour period every night. We had to find the “sweet spot” of how many records to archive at a single time, versus how often to run the job (this is because if we query too many records, we can start blocking user queries, but if we query too few, or run the job too infrequently, then we don’t keep up with the volume of data that needs to be archived). The other good thing about this method is that if the proc is still running when the next scheduled execution comes up, it will just skip that execution and try again 10 seconds later – in our case, missing a few runs is not a big deal.
The other thing to notice is that the DEADLOCK PRIORITY is set to LOW. This will ensure that this proc is always the deadlock victim, and not other user queries.
Anyway, here is the link to the project.
Hello world! For anyone that may see this, my name is Clint, and I have been working in enterprise-level IT for nearly a decade – mostly on the database side of things. My buddy, Mr. Julius, has been kind enough to let me post on this blog, so I hope I do it justice. I plan to share some cool things here. Enough introduction. On with it!
This link will take you to a stored procedure that I wrote to back up encryption objects, specifically Service Master Keys, Database Master Keys, and Certificates. I am relatively new to working with SQL Server encryption, so I’m not sure how this proc will evolve, but for now, it backs up all the encryption objects I need to be concerned with.
Why should we back up these objects? Well, I won’t describe how SQL Server column-level encryption works – there are plenty of sites dedicated to that, and it would take a lot of typing. In a nutshell, it’s because of this: if you lose one, or more, of these objects for any reason, you risk losing the ability to decrypt your data (which would basically mean it’s lost forever). Therefore, you need the entire hierarchy of your encryption objects to be intact to have viable, encrypted data. Some examples of why these objects may need to be backed up include:
- Service Master Keys are unique to a SQL Server Instance, so if you want the same key on a separate instance, you must manually restore to it. These are not carried over in a DB backup/restore operation.
- Database Master Keys are re-encrypted when you restore a DB to SQL Server instance with a different Service Master Key, causing the “downstream” objects to also be re-encrypted (i.e. unreadable). Therefore, even thought these are carried over when you do a backup/restore, if the Service Master Key was different upon restore, you won’t get the desired results.
- Certificates can be used to open/close keys (which, in turn, do the actual encryption/decryption). One certificate can control many keys. I believe, if you lose the certificate, but still have the keys, you can create a new certificate to control the keys…I am not sure about this! I still have some testing to do, so I will update this post when I have definitive info. Either way, to be safe, I back them up anyway.
The above list is not all-inclusive. There may be many more reasons why you need to backup/restore the objects.
It seems crazy to me that there is not an “out of the box” way to back up these objects through the GUI, and/or schedule it, considering the MSDN page on Service Master Keys says it should be one of your first admin tasks performed on the server. Maybe it’s a security concern to have something built-in to the product like that. Who knows?
Some references are below in the event you want to dig a little deeper. Hopefully this will prove helpful to someone besides me. Later!