2

I learned a hard lesson this week. It turns out what while table updates/ schema updates do get replicated across databases on an Availability Group, changing the DB Owner on the primary replica does not change the DB owner on the secondary replica automatically.

Thus if you perform a failover, the database owner could now be out of sync with the master table's SID.

Should my plan of action when e.g. making a security related change be to

1. Apply change to primary replica
2. Fail over the Availability Group
3. Apply change to the new primary replica

Am I correct in making these statements? Is it only DBOwner/ Schema changes that I must be aware of when I am making changes.

  • 1
    How big is your database which participates in availability group ?. Yes this is a limitation which, I am not aware whether is fixed in SQL Server 2016 or not. – Shanky Nov 15 '16 at 11:50
  • It is a 3GB database at this stage but is growing rapidly - would there be an advantage / other option depending on size of db? – Peter PitLock Nov 15 '16 at 12:07
  • 1
    If you dont want to do failover you can take database out of AOAG and then configure again with correct DB owner. I also see your method is correct one, I dont see any issue with limited things we have got – Shanky Nov 15 '16 at 12:11
  • Thank you Shanky - do you agree with the comment by Smairo where i should set the owners to 'sa' at intervals? – Peter PitLock Nov 15 '16 at 12:13
  • 1
    If you really want owner as SA you can do it but that is not a good security practice, moreover this will again require a failover. So if you have to do failover why not go with owner you want to keep instead of SA – Shanky Nov 15 '16 at 12:15
2

Yes, you need to make sure that databases have the same owner, schema changes will travel along the availability group. This gets problematic when there are changes in database owner as you need to reapply those changes too to the secondary nodes.

If it is possible, you could change owner to SA on every database, then make this change every night and when fail-over happens. When you capture the fail-over you can run this to make SA owner for every database:

EXEC sp_MSforeachdb 'ALTER AUTHORIZATION ON DATABASE::? TO SA';

EDIT:

If you don't wish to go SA as a owner, you could create table containing the database owners

CREATE TABLE DatabaseInAvailabilityGroup.dbo.dbownersMaintenance (
id INT IDENTITY(1,1),
dbname NVARCHAR(100) NOT NULL,
username NVARCHAR(100) NOT NULL
);

Filling up the table (make this as a job and schedule it once a hour or something)

DELETE FROM DatabaseInAvailabilityGroup.dbo.dbownersMaintenance;

INSERT INTO DatabaseInAvailabilityGroup.dbo.dbownersMaintenance
SELECT db.name, sl.name
FROM master.sys.databases db
INNER JOIN master.sys.syslogins sl ON db.owner_sid = sl.sid;

As soon as the fail-over has happened, this needs to be run on the new active node:

DECLARE @maxRow INT, @currentRow INT, @sql NVARCHAR(MAX), @dbname NVARCHAR(100), @owner NVARCHAR(85);
SELECT @maxRow = MAX(id), @currentRow = MIN(id) FROM DatabaseInAvailabilityGroup.dbo.dbownersMaintenance;

WHILE @maxRow >= @currentRow
BEGIN
SELECT @dbname = dbname, @owner = username FROM DatabaseInAvailabilityGroup.dbo.dbownersMaintenance WHERE id = @currentRow;
SET @sql = 'ALTER AUTHORIZATION ON DATABASE::'+@dbname+' TO ['+@owner+']';
EXECUTE (@sql);
SET @currentRow = @currentRow+1;
END

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.