Advertisement
Guest User

Untitled

a guest
Jul 31st, 2014
155
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.74 KB | None | 0 0
  1. USE master;
  2.  
  3. DECLARE @last_role TINYINT;
  4. SET @last_role = (
  5. SELECT TOP 1 [replica_role]
  6. FROM [dbo].[replica_role]
  7. );
  8.  
  9. DECLARE @current_role TINYINT;
  10. SET @current_role = (
  11. SELECT ROLE
  12. FROM sys.dm_hadr_availability_replica_states
  13. WHERE is_local = 1
  14. );
  15.  
  16. IF (@last_role = 2 AND @current_role = 1)
  17. BEGIN
  18. USE SSISDB;
  19. OPEN MASTER KEY DECRYPTION BY PASSWORD = 'x'
  20. ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
  21. END
  22.  
  23. USE master;
  24. UPDATE dbo.[replica_role] SET [replica_role] = @current_role;
  25.  
  26. The target database ('SSISDB') is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online.
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement