Why SQL 2012 SSIS Catalogs are useless

When I first got started playing with the new SQL 2012 SSIS Catalogs I was pretty excited; To be able to use project parameters instead of config files and using the new project deployment model instead of the package deployment model makes life alot easier in the world of managing and deploying SSIS packages.

However there has been a few issues with the new features that has been a pain to work with:

1. Project hangs indefinitely due to the new SSISDB logging
   Packages that use to run in SQL 2008R2 now hangs with SQL 2012. This may be caused by deadlocks with the SSISDB database. Turning off logging in the job resolves this issue however when I need to troubleshoot anything I have to manually turn it back on and off again.
2. SSIS Server Maintenance Job breaks jobs and causes locks
   This is a default job created when installing SSIS 2012. It cleans out all old history and project revisions as configured. However when this job is running it will cause all other jobs to fail as it is placing a lot of locks on the SSISDB table. The job itself can take 3-4 hours to complete which means our other jobs are failing in the meantime causing massive backlogs.
3. SSISDB logging and reports are tediously slow to use
   It's nice to see that Microsoft decided to create better reporting features with SQL 2012 however it is way too slow to use and navigating it to find issue is painful. Not only do I have to deal with the slow response time, but navigating between pages and finding issues is hard to figure out.

In summary we have decided not to use SQL 2012's SSIS catalog till SP1 hits, hopefully all the issues I listed above will be resolved then.

SSAS File Corruption

This following error made me work on a Sunday night:

The following file is corrupted: Physical file: <blah>

Pretty much a server bit it pretty hard and after a manual reboot it came back up with a bunch of cubes having these corruption issues.

Sadly at this point all you can do is pray you have a decently recent backup to restore from however just working with the SSAS database is hard enough; if you try to do anything (detach, delete) it will just throw the corruption error.

Only way to handle this is to:

1. Shutdown the SSAS service instance
2. Find where the cube data directory is stored and delete all files related to the corrupted cubes
3. Startup the SSAS service instance
4. Restore from backup
5. Pray

Hope you don't have to deal with this issue like I have.

Service account doesn't have rights to check domain user SIDs

We started getting the following error in SSRS on subscriptions executions

Failure sending mail: The report server has encountered a configuration error. Mail will not be resent.

I naturally started looking through the SSRS logs and found this wonderful gem:

ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The Report Server has encountered a configuration error; more details in the log files, AuthzInitializeContextFromSid: Win32 error: 5; possible reason - service account doesn't have rights to check domain user SIDs.;

So what am I to do? Well the good news is there is a KB article that helped point me in the general direction of what to look at: http://support.microsoft.com/kb/842423.

I created a test subscription (as advised from the KB article) that would execute within a minute which only created a link to the report not embedding or attaching the report. After successfully receiving this report it confirms that the AuthzInitializeContextFromSid API function call is the issue and the service account is having permission issues executing it.

The three methods to resolve this issue in the KB article didn't apply to me as I know that the SSRS service account I use has enough permission to run everything required as it was working before. So instead of following the directions I decided to restart the SSRS service instead.... and then... BAM! "Service cannot restart due to a LogOn failure."

At this point I re-entered the service account credentials, started up the service, sent a test subscription and everything works again. So super weird how Server 2008R2 forgot the service credentials but at least it was a simple fix.

What is all this random crap?

I've decided to start posting all the fun issues and topics I've had with Microsoft SQL hoping that it could help some poor soul (or my future forgetful self) that has to figure this stuff out. Do note that the solutions I find might only pertain to my environment and it may not fix others. Cheers.