Between October 22nd and 24th 2012 the fifth installment of the Bleeding Edge conference took place in Laško, Slovenia. Considering the responses from the attendees as well as the speakers, the conference was once again a great success.
Congratulations to the organizers, and big thanks to the attendees! Special thanks go to the companies, who decided to send their developers and administrators to the event in spite of the current economic situation. With so many public, and privately owned, companies trying to reduce their costs by abandoning training altogether, it is these few, reasonable, employers who continue to drive the economy.
Considering its typical ROI, education is one of the least expensive investments these days; unfortunately too many CEO's fail to acknowledge this simple fact.
SSISDB Catalog Deep Dive
At this year's Bleeding Edge I presented a session on SSISDB catalog, the new Microsoft SQL Server 2012 feature used for storing SSIS solutions. Actually, the SSISDB catalog is much more than that; it provides an integrated environment for SSIS project deployment, maintenance, execution, and monitoring.
You can read more about the SSISDB catalog in SQL Server Books Online (you should start with the article entitled "SSIS Catalog"); the subject is also covered in the upcoming Training Kit (Exam 70-463): Implementing a Data Warehouse with Microsoft® SQL Server® 2012, written by SolidQ mentors Dejan Sarka, Grega Jerkič, and yours truly.
At the session I received one question from the audience that I didn't feel comfortable answering just there and then. I was simply not quite sure whether the most obvious answer also represented an actually supported scenario. I've since been able to locate the appropriate solution, and have been able to confirm it in practice.
SSISDB Catalog Disaster Recovery and Migration
The question was simple:
How to migrate an SSISDB catalog from one server to another?
As far as SSISDB database disaster recovery is concerned, the answer is fairly simple: BACKUP and RESTORE are supported for the SSISDB database, and on the same instance no additional activities are required to facilitate the restore. By default, the SSISDB database is in full recovery mode, which means that both the full database backup as well as regular transaction log backups must be in place to correspond to the recovery mode, and allow point-in-time restores.
But what about SSISDB catalog migrations to a different SQL Server instance? Naturally, the procedure is documented in SQL Server Books Online, in the article entitled "Backup, Restore, and Move the SSIS Catalog".
Special consideration is required when the SSISDB database is restored on a SQL Server instance where the SSISDB catalog has not previously been created. To simplify the migration in such a case, I would suggest to first create a new SSISDB catalog (for instance, by using SSMS) as described in the SQL Server Books Online article entitled "Create the SSISDB Catalog", and then replace the newly created, empty, SSISDB database by restoring the actual one from the backup files.
Of course, you should not attempt any of this without first carefully studying the disaster recovery and migration article mentioned earlier. Remember: SSISDB catalog migration is not trivial; the creation of the SSISDB catalog consists of more activities than just the creation of the SSISDB database (two SQL Server Agent jobs are created, the appropriate security settings are put in place, the SSIS startup procedure is configured, and specific permissions are granted to allow the execution of SSIS CLR stored procedures).
I hope this answers the questions concerning SSISDB catalog disaster recovery and migrations. If there were any other questions raised at the session that I've not responded to yet, please, let me know.
ML
