Quite often, as a Production DBA, you come across issues that are not so apparent within error messages. For example, a few days after migrating a PROD server from SQL Server 2012 Standard Edition to SQL Server 2016 Standard Edition, I noticed reports were no longer being delivered via database mail. When migrating from SQL Server 2014 Standard to SQL Server 2016 in our QA environment, this same issue occurred. Within the database mail logs, I noticed many errors stating “object reference not set to an instance of an object.”



After some solid Googling, the search results were suggesting a permission/role issue, .NET Framework 64-bit, or patch level issue. However, the machine in question had all of the required .NET versions installed, most recent updates, and I was attempting all actions with sysadmin. While troubleshooting the database mail, we also noticed SSIS packages were not running and new deployments failed with the error “please create a master key in the database or open the master key in the session before performing the operation.” Now we are all hands on deck, and must get production running.



Examples of Errors: 



Database Mail Log



SQL Server Integration Services



SQL Server Integration Services Error



After continued digging into this situation, we discovered a known issue where SSIS packages start failing after migrating SSIDB from 2012-2014. However, when we experienced this issue the second time around, we were migrating SSIDB from SQL Server 2014 to 2016. It looks like this issue wasn’t fixed!! This can be verified when opening the Properties of the Integration Services Catalog SSISDB fails due to the invalid object name “SSISDB.catalog.customized_logging_levels."



Microsoft SQL Server Management Studio



Since you can’t open the properties to verify the Catalog Schema, query:



[SSISDB].[internal].[catalog_properties]



SELECT * FROM [SSISDB].[internal].[catalog_properties]



Schema_Build



There we go, the SCHEMA_BUILD is 12.0.5589.7 but we are using SQL Server 2016 (13.0.4474.0). We want our SSISDB to have a 13.% SCHEMA_BUILD.



Microsoft SQL Server 2016



You know you followed best practices when migrating SSISDB, so this confirms your issue. The SCHEMA_BUILD in the Catalog was not updated and now you have a mismatch in the schema, much to your chagrin.



The Fix



Hopefully, you still have access to the previous SSISDB on the instance you migrated from or a copy of the backup so that you can restore to an instance with the same version of SQL Server as your source instance. If not, you will need to locate the original SSIS package files and import the packages into Visual Studio to re-deploy to your new instance after we fix this issue. While exporting packages from the old instance, make sure to make note of any folders when you expand SSISDB within Integration Services Catalog because you will need to recreate them also.



The first part of this fix is pretty quick:




  1. drop SSISDB on the new 2016 server,

  2. create a new catalog,

  3. create the new folders.



The more packages you have to redeploy, the longer this will take. Should there be upwards of 100 + packages, it may be worth the time to invest in automating this process, pinging one of your Junior DBAs to assist, or calling up RDX.



On your new instance, drop the SSISDB:



USE [master]

GO

ALTER DATABASE [SSISDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE



GO



USE [master]

GO

DROP DATABASE [SSISDB]



GO



Create a new Integration Services Catalog, re-create all of the folders, and re-deploy your packages




  1. Right-click Integration Services Catalog, Create Catalog… Create Catalog

  2. When the GUI loads, create a strong password and save in a safe location, click OKCatalog SSIDB

  3. Verify you can now view the Properties of SSISDB in Integration Services Catalog & your Schema Build is now 13.% (12.% if from 2012 to 2014)Schema Build

  4. Start re-creating the folders in the Integration Services Catalog SSISDB ***You must use Windows Authentication to create Catalog Folders***  Operation Create

    Create Catalog Folders



  5. Redeploy packages & you are back in business



You will know with the successful package deployment, and/or validate database mail is functioning by sending a test mail and querying msdb.dbo.sysmail_allitems or msdb.dbo.sysmail_event_log.



Change time or mailitem_id for better filtering:



SELECT top 10 mailitem_id, subject, sent_status, sent_date, body

FROM msdb.dbo.sysmail_allitems



-- WHERE mailitem_id =



order by sent_date desc



SELECT top 10 *

FROM msdb.dbo.sysmail_event_log



WHERE event_type <> 'information'



-- mailitem_id =



order by log_date desc



In conclusion, this is just one example of the unique situations Production DBAs come across when supporting a plethora of environments. Sometimes, following best practices still have unforeseen obstacles that we need to work around, even if there isn’t clear-cut documentation when searching a specific error. Always make sure you are verifying your sources, and don’t start making changes without properly researching the root cause. Hopefully, this will help save some headache and relieve tension in a potentially high stress situation.



Thanks for reading!



References:



https://docs.microsoft.com/en-us/sql/integration-services/backup-restore-and-move-the-ssis-catalog?view=sql-server-2014



https://blogs.msdn.microsoft.com/dataaccesstechnologies/2015/07/09/import-deployment-execution-of-ssis-packages-fails-after-you-migrate-ssisdb-from-sql-server-2012-to-sql-server-2014/