SQL High Availability

We’ve created a nightly job that synchronises logins across all instances in our HA environment, using Microsoft’s sp_help_revlogin (more info on the revlogin stored procedure can be found here https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server).  With this stored procedure you can copy sql logins across instances keeping the SIDs the same so you don’t have any synchronising issues when you failover.

I think there may be a more elegant solution using PowerShell and dbatools (https://dbatools.io/), although I haven’t had time to investigate this in any detail.

Let me know if you’d like a copy of the code we are using and I can send it round (there’s a table and a few stored procedures).

Regards

Phil

Philip Cartwright
MSSQL Database Administrator

Digital Development and Technology
Royal Opera House
Covent Garden, London WC2E 9DD
Telephone: +442037726393

Website: www.roh.org.uk
YouTube: www.youtube.com/royaloperahouse
Facebook: www.facebook.com/royaloperahouse
Twitter: @royaloperahouse

Royal Opera House Covent Garden Foundation – a charitable company limited by guarantee

Registered in England No. 480523 | Registered Charity No 211775 | Please consider the environment before printing this email

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Mark Ridley
Sent: 04 May 2017 09:29
To: Philip Cartwright <Philip.Cartwright@roh.org.uk>
Subject: Re: [Tessitura Technical Forum] SQL High Availability

The logins can be an issue, but as Tessitura stores active logins in a table I was able to write a script to re-enable those that were already on the server

declare @table as table (
   username varchar(50),
   usersid varbinary(4000)
)

insert into @table
exec sp_change_users_login 'Report'

delete @table
where username not in (
   select username
   from @table u
   join impresario.dbo.T_METUSER m on u.username=m.db_userid and m.inactive='N'
)

declare @login varchar(50)

select @login=(
   select top 1 username
   from @table
   order by username
)

while @login is not null
begin
   BEGIN try
      exec sp_change_users_login 'Update_One', @login, @login
      print 'Updated ' + @login
   END TRY
   BEGIN CATCH
      print 'Updated failed for ' + @login
   end catch

   select @login=(
    select top 1 username
    from @table
    where username>@login
      order by username
   )
end

Needs to be run on both impresario and impresario_cci

For newer users who didn't have a login on the secondary I just used the security program to create their accounts, but at least that number was a lot easier to manage.

Due to issues like this we didn't switch on automatic failover and left it is Manual.

Mark

The Science Museum of Minnesota is also HA on the production side. There were some things we needed to do for efficiency. One, was moving the SSRS reporting services to another server to centralize reporting. We also ran into an issue where the SIDS would not syncronize after a failover. We finally found a solution here: http://www.msbicoe.com/post/2012/04/05/Automatically-Transfer-and-Synchronize-SQL-Server-logins-SIDs-Passwords-and-Permissions.aspx for that.

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

We’ve created a nightly job that synchronises logins across all instances in our HA environment, using Microsoft’s sp_help_revlogin (more info on the revlogin stored procedure can be found here https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server).  With this stored procedure you can copy sql logins across instances keeping the SIDs the same so you don’t have any synchronising issues when you failover.

I think there may be a more elegant solution using PowerShell and dbatools (https://dbatools.io/), although I haven’t had time to investigate this in any detail.

Let me know if you’d like a copy of the code we are using and I can send it round (there’s a table and a few stored procedures).

Regards

Phil

Philip Cartwright
MSSQL Database Administrator

Digital Development and Technology
Royal Opera House
Covent Garden, London WC2E 9DD
Telephone: +442037726393

Website: www.roh.org.uk
YouTube: www.youtube.com/royaloperahouse
Facebook: www.facebook.com/royaloperahouse
Twitter: @royaloperahouse

Royal Opera House Covent Garden Foundation – a charitable company limited by guarantee

Registered in England No. 480523 | Registered Charity No 211775 | Please consider the environment before printing this email

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Mark Ridley
Sent: 04 May 2017 09:29
To: Philip Cartwright <Philip.Cartwright@roh.org.uk>
Subject: Re: [Tessitura Technical Forum] SQL High Availability

The logins can be an issue, but as Tessitura stores active logins in a table I was able to write a script to re-enable those that were already on the server

declare @table as table (
   username varchar(50),
   usersid varbinary(4000)
)

insert into @table
exec sp_change_users_login 'Report'

delete @table
where username not in (
   select username
   from @table u
   join impresario.dbo.T_METUSER m on u.username=m.db_userid and m.inactive='N'
)

declare @login varchar(50)

select @login=(
   select top 1 username
   from @table
   order by username
)

while @login is not null
begin
   BEGIN try
      exec sp_change_users_login 'Update_One', @login, @login
      print 'Updated ' + @login
   END TRY
   BEGIN CATCH
      print 'Updated failed for ' + @login
   end catch

   select @login=(
    select top 1 username
    from @table
    where username>@login
      order by username
   )
end

Needs to be run on both impresario and impresario_cci

For newer users who didn't have a login on the secondary I just used the security program to create their accounts, but at least that number was a lot easier to manage.

Due to issues like this we didn't switch on automatic failover and left it is Manual.

Mark

The Science Museum of Minnesota is also HA on the production side. There were some things we needed to do for efficiency. One, was moving the SSRS reporting services to another server to centralize reporting. We also ran into an issue where the SIDS would not syncronize after a failover. We finally found a solution here: http://www.msbicoe.com/post/2012/04/05/Automatically-Transfer-and-Synchronize-SQL-Server-logins-SIDs-Passwords-and-Permissions.aspx for that.

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!