How to configure Always On for a TDE database

Creating Availability group for an Encrypted database:  In this blog we will walk you through the steps to create availability group for an encrypted database. Though we have various articles, I would like to keep the entire step by step approach in one place.   We cannot create an availability group for an encrypted database…


Upgrade from SQL 2005 to SQL 2012 fails with error “Service ‘SQLSERVERAGENT’ start request failed.”

I was working on a 2005 to 2012 upgrade issue and came across an interesting issue which I thought of sharing with you guys.  Whilst running an upgrade from 2005 SP4 to 2012 RTM (and/or slipstreamed copy) it failed half way through, giving the error:   “There was an error attempting to remove the configuration…

4

How do I map a differential/transaction log backup to its full backup without using MSDB?

If you have multiple full, differential and transaction log backups, there are chances you may end up facing the error below during the restore process. “This differential backup cannot be restored because the database has not been restored to the correct earlier state”   You know that any differential/T-log  backup is mapped to particular full…


Change Data Capture gets disabled when you break mirroring and bring the database online

  Issue ================   Recently we encountered an issue where customer performed a planned failover for a mirrored database and after the failover the new principal went into suspect mode because of some reason (which is outside the scope of this blog) and was not accessible. The database which was mirrored had CDC(Change Data Capture)…


Do NOT delete files from the Windows Installer folder

C:\windows\Installer is not a temporary folder and files in it should not be deleted. If you do it on machines on which you have SQL Server installed you may have to rebuild the operating system and reinstall SQL Server.   The Windows Installer Cache, located in c:\windows\installer folder, is used to store important files for…

4

How to monitor deadlock using extended events in SQL Server 2008 and later

Before I talk about how to monitor the deadlocks let us have a small brief what is deadlock and how we were handling them in the previous versions of SQL Server.   Deadlock: A deadlock occurs when there is a cyclic dependency between two or more threads, or processes, for some set of resources within…

9

Ventured into BULK INSERTS

Recently, whilst working with bulk insert queries I came across an unexpected result set which led to some more research. Therefore, following facts/tips/tricks/workarounds might help you:   If you execute the following code for example:   NOTE: Please bear in mind that the log file used to import the data in this example (errorlog.1) has…

1