Have you every noticed that when you add a database (where the db owner is ‘sa’) in to a SQL 2012 availability group and synchronise the db to one or more secondary replicas, the database owner at each secondary is no longer ‘sa’ but the name of the account used to add the database into the AG? More specifically, the db owner is set to the account you used to connect to each secondary during the wizard process, as it’s this account that does the db restore at the secondary replica (hint).
It would be nice if SQL 2012 didn’t change the db owner after the mirror process but unfortunately that isn’t the case (issue noted on Microsoft Connect). Update 25-Jun-16: This behaviour is in SQL 2012, 2014 and 2016 RTM.
How to fix?
Ok, so now the database exists at each secondary – how can we set the db ownership to ‘sa’ in all the secondary replicas? We can’t just issue an ALTER AUTHORIZATION ON DATABASE statement because all the databases are in a read-only state and therefore cannot be changed.
Well ideally, if you can fail over to each secondary in turn, you’ll have a read/write database you can change the db owner on. But this may not be practical in a Production environment (see Precautions below).
If fail-over is not an option, then you could delete the database from every secondary replica and re-synchronise. However this time, with SSMS you’ll need to log in to the primary replica using the ‘sa’ account, create or restore the database in question (or use an existing db), ensure the owner is set to ‘sa’, backup the db and log then add it in to the availability group and synchronise with all the secondary replicas (ie. let the wizard copy and restore the db and log at each secondary via a network share).
If you can’t do this because the database in question is large, or you can’t recall your ‘sa’ password, then you can manually restore the db and log at each secondary replica. Here are the steps: (a) stop the data flow from primary to all secondaries and remove the database from the availability group, (b) delete the db from all secondary replicas, (c) on the primary replica back up the db and its log, (d) copy these files to each secondary replica’s hard disk, (e) on each secondary replica, log in as ‘sa’ (or log in as a sysadmin and run the below T-SQL code), (f) restore the db and log (don’t forget to ‘restore with no recovery’ so the db is left in the ‘recovering’ state), (g) check the db owner is ‘sa’, (h) on the primary replica add the database back in to the AG with ‘join only’, (i) check all secondaries – the database should exist in the AG fully joined and synched up. Don’t forget you may need to reapply SQL login and db permissions across all secondary replicas.
EXECUTE AS login='sa'; RESTORE DATABASE [YourDatabaseName] FROM DISK = '<YourBackupPath>' WITH NORECOVERY; REVERT; GO
I’d be remiss if I didn’t mention a few items.
- Keep in mind that when you change the owner of the database the user that owned the database might have less or no permission anymore on the database.
- Failing over an availability group in a production environment will most likely require Change Management approval (lead time) and an unplanned outage window.
- Any unplanned outage may also impact any up-time / service level agreement in place which may be a significant metric to the business come the end of the year.
- Any fail over may impact other applications whose databases are in the same availability group.
- It is prudent practice to create multiple availability groups for separation/isolation of application databases or business units – therefore failing over one AG won’t have an impact on the other AG’s (AGs do not incur any additional SQL licensing cost so you can create hundreds if need be but your kit had better be able to handle the workload!!).
Most people prefer to have the ‘sa’ account as the database owner because the ‘sa’ account cannot be deleted (unlike any user or service account) and so the database will never end-up in an orphaned state. Is this best practice? Is it the most secure practice? Not really (unless your ‘sa’ account is disabled and has ‘connection denied’ set). To find out what constitutes recommended practice refer to Andreas Wolter’s post sql server database ownership survey – results and recommendations
Hope this helps someone.