Backup On-Premise SQL Server Database to Microsoft Azure

Blog36_1

-> Creating a Storage Account,

Blog36_2.PNG

Blog36_3.PNG

-> Once the Storage account is deployed. Click on Blog36_4 Resources and then click on your Storage that we deployed just now. It is jbsqlbackups in my case,

Blog36_5.PNG

-> Click on Containers and add a new container.

Blog36_6.PNG

-> Click on Access Keys and track down your Storage Account Name and the Keys,

Blog36_7.PNG

-> Open SQL Server Management Studio and try a backup,

Blog36_8.PNG

Blog36_9.PNG

Blog36_10.PNG

Blog36_11.PNG

Blog36_12.PNG

-> Lets connect to the Azure storage and check the backup file,

Blog36_13.PNG

-> Copy the Access key that was noted earlier and paste it connect to the Azure Storage,

 

Blog36_14

Blog36_15.PNG

-> Lets try restoring the database,

Blog36_16

Blog36_17.PNG

-> Click on OK and you will get the below screen. Select the backup that you ant to restore,

Blog36_18.PNG

Blog36_19.PNG

Blog36_20.PNG

-> The database is restored and ready for use.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Advertisements

AlwaysON – Unable to access the ‘JB1’ database because no online secondary replicas are enabled for read-only access. (Microsoft SQL Server, Error: 982)

Environment

Blog29_1

-> The Application makes a connection to the Database JB1 with ApplicationIntent=ReadOnly and receives below message,

Unable to access the ‘JB1’ database because no online secondary replicas are enabled for read-only access. Check the availability group configuration to verify that at least one secondary replica is configured for read-only access. Wait for an enabled replica to come online, and retry your read-only operation.
Changed database context to ‘JB1’. (Microsoft SQL Server, Error: 982)

-> The message indicates that there are no active Read-Only partners. When checked further it is clear that Server JBSERVER2 and JBSERVER3 were down.

-> We were advised that the Servers JBSERVER2 and JBSERVER3 will not be up for next 6 Hours. Now the Application team doesn’t want to change their connection string by dropping ApplicationIntent=ReadOnly and wanted us to make sure that the primary accepts Read connections.

-> The Alwayson Availability group setting is as below,

Blog35_1.PNG

-> We changed the setting “Connections in Primary Role” from “Allow read/write connecions” to “Allow all connections” as below,

Blog35_2.PNG

-> The Application started connecting to the Primary Replica even with ApplicationIntent=ReadOnly.

-> This now brings up the question, why can’t we have the setting “Connections in Primary Role” set to “Allow all connections” instead of “Allow read/write connecions”. “Allow all connections”  can be an issue as Connections where the Application Intent is set to ReadOnly are not disallowed on the Primary Replica anymore. With this setting in place the read workloads may execute on both Primary and the secondary, If server JBSERVER2 which is on the same data centre as the PRIMARY goes down and we have intermittent network issues between DataCenters which makes JBSERVER3 to be offline Intermittently for JBSERVER1. So the Read workload might run on both JBSERVER1 and JBSERVER3.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

AlwaysON – How to disable Read-Only routing for an Availability Group

-> We have configured Read-Only routing list for an availability group. Now the requirement changes and the Read-Only routing needs to be disabled. If you have a scenario like this in your hand, then this post is for you!

-> Please refer article “https://jbswiki.com/2017/09/06/configuring-read-only-routing-list-for-alwayson-availability-group/” to create read-Only routing list for you Availability group.

Environment

Blog29_1

-> Looking at SSMS,

Blog_34_1.PNG

-> Checking the Read-Routing List that is setup currently on the Primary Replica using below query,

SELECT ag.name as “Availability Group”, ar.replica_server_name as “When Primary Replica Is”,
rl.routing_priority as “Routing Priority”, ar2.replica_server_name as “RO Routed To”,
ar.secondary_role_allow_connections_desc, ar2.read_only_routing_url
FROM sys.availability_read_only_routing_lists rl
inner join sys.availability_replicas ar on rl.replica_id = ar.replica_id
inner join sys.availability_replicas ar2 on rl.read_only_replica_id = ar2.replica_id
inner join sys.availability_groups ag on ar.group_id = ag.group_id
ORDER BY ag.name, ar.replica_server_name, rl.routing_priority

Blog_34_2.PNG

-> Trying a connection with Switch -KReadonly to see if it Read-Routing list works and connects to JBSERVER3.

Blog_34_3.PNG

-> We will Disable the Read-Routing list using below query,

ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON
N’JBSERVER1′ WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=NONE));
ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON
N’JBSERVER2′ WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=NONE));
ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON
N’JBSERVER3′ WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=NONE));

-> Checking the Read-Routing details on the Primary Replica after it is removed,

SELECT ag.name as “Availability Group”, ar.replica_server_name as “When Primary Replica Is”,
rl.routing_priority as “Routing Priority”, ar2.replica_server_name as “RO Routed To”,
ar.secondary_role_allow_connections_desc, ar2.read_only_routing_url
FROM sys.availability_read_only_routing_lists rl
inner join sys.availability_replicas ar on rl.replica_id = ar.replica_id
inner join sys.availability_replicas ar2 on rl.read_only_replica_id = ar2.replica_id
inner join sys.availability_groups ag on ar.group_id = ag.group_id
ORDER BY ag.name, ar.replica_server_name, rl.routing_priority

Blog_34_4.PNG

-> It is evident from the above screenshot that the Read-Routing listi ndeed is removed. Lets check what happens if we connect the listener using -KReadonly switch,

Blog_34_5.PNG

-> It connects to the primary Replica. this proves that read-only routing is indeed disabled.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Creating Linked Server to a Multi-Subnet Availability Group Listener with ReadOnly routing

-> A multi-subnet environment is defined when the OS cluster used for AlwaysOn has server nodes that are located in multiple, different subnets. By default, the behavior of the SQL client libraries is to try all IP addresses returned by the DNS lookup – one after another (serially) until the all of the IP addresses have been exhausted and either a connection is made, or a connection timeout threshold has been reached.

-> Microsoft added a new connection string parameter that can be added to change the connection behavior. This new parameter, MultiSubnetFailover, should be used and set to “TRUE.” When set to TRUE, the connection attempt behavior changes. It will no longer attempt all of the IP addresses serially, but in parallel.

-> The parameter MultiSubnetFailover cannot be used in the Linked Server as part of the provider string. We will have to create a Data Source and then use the Data Source in the Linked Server.

Blog33_1.PNG

Blog33_2.PNG

-> I am using Windows Aunthentication to connect the Listener. If you want to use SQL Server Authentication, select as appropriate.

Blog33_3.PNG

-> Change “Change the default database to:”  as indicated in the below screenshot from database Master to a database that is part of Alwayson Availability group that is part of the listener. Make sure the Application Intent is READONLY and Multi-subnet failover is checked.

Blog33_4

Blog33_5.PNG

Blog33_6.PNG

Blog33_7.PNG

-> Once the Data Source is created and tested. Move onto creating the Linked server.

Blog33_8.PNG

-> Select “Be made using this security context” and provide the SQL Server Authentication Credential if required. I am using Windows authentication, so using “Be made using the login’s current securoty context”.

Blog33_9.PNG

-> Once the Linked server is created. Execute the below query and check if the output returned is your readable secondary and not pointing to the primary.

select * from openquery([JBSERVER1],’select @@servername’)

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

AlwaysON – Working through Crashed Synchronous Secondary Replica

Environment

Blog29_1

-> JBSERVER2 Crashes. The Server is expected to be online after 2 days.

Blog32_2.PNG

-> Checking the SQL Server Management Studio and the Cluster Administrator,

Blog32_3

Blog32_4

-> JBSERVER3 which is an Asynchronous replica, will be changed to a Synchronous replica.

-> Removing the Crashed Replica from Availability group,

Blog32_5.PNG

Blog32_6

Blog32_7.PNG

-> Remove the Node JBSERVER2 from Cluster Administrator,

Blog32_8.PNG

Blog32_9

Blog32_10

-> Server JBSERVER2 comes online after the Rebuild. Lets Add it back to the Availability group.

-> Adding JBSERVER2 to the Failover Cluster,

Blog32_11.PNG

Blog32_12.PNG

Blog32_13.PNG

-> Adding JBSERVER2 as a Replica,

Blog32_14.PNG

Blog32_15.PNG

Blog32_16.PNG

Blog32_17.PNG

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

AlwaysON – Login Synchronization across Replicas

Environment

Blog29_1

-> Create a Job called “Login Synchronization” on all the Database Servers as part of your Alwayson Availability group. In my Environment, the Job will be created on Database Server JBSERVER1, JBSERVER2 and JBSERVER3. The Job “Login Synchronization” will have the below script executed as part of it.

set nocount on
create table #Sync_Logins (Script varchar(max))
Declare @sql nvarchar(max)
Declare @Primary_Replica varchar(20)
SELECT @Primary_Replica = primary_replica
FROM sys.dm_hadr_availability_group_states a INNER JOIN sys.availability_group_listeners b
ON a.group_id=b.group_id where b.dns_name=’PPN-VDBSL’
IF (@Primary_Replica= @@servername) BEGIN;
Print N’Script cannot run on primary Replica’;
drop table #Sync_Logins
RETURN;
END;
SET @sql=N”;
set @sql = ‘SELECT ”If not Exists (select loginname from master.dbo.syslogins where name = ””” +name +”””’+’) BEGIN CREATE LOGIN ” + QUOTENAME(name) + ” WITH PASSWORD=”
+ sys.fn_varbintohexstr(password_hash) + ” HASHED, SID=”
+ sys.fn_varbintohexstr(sid) + ”, ”
+ ”DEFAULT_DATABASE=”+ QUOTENAME(COALESCE(default_database_name, ”master”))
+ ”, DEFAULT_LANGUAGE=” + QUOTENAME(COALESCE(default_language_name,
”us_english”))
+ ”, CHECK_EXPIRATION=” + CASE is_expiration_checked WHEN 1 THEN ”ON” ELSE
”OFF” END
+ ”, CHECK_POLICY=” + CASE is_policy_checked WHEN 1 THEN ”ON” ELSE ”OFF” END + ” END”
FROM [‘+@Primary_Replica+’].master.sys.sql_logins
WHERE name<>”sa”
UNION ALL
–Windows logins:
SELECT ”If not Exists (select loginname from master.dbo.syslogins where name = ”””+ name +”””’+’) BEGIN CREATE LOGIN ” + QUOTENAME(name) + ” FROM WINDOWS WITH ”
+ ”DEFAULT_DATABASE=”+ QUOTENAME(COALESCE(default_database_name, ”master”))
+ ”, DEFAULT_LANGUAGE=” + QUOTENAME(COALESCE(default_language_name,
”us_english”))+ ” END”
FROM [‘+@Primary_Replica+’].master.sys.server_principals
WHERE type IN (”U”,”G”)
AND name NOT LIKE ”%\SQLServer2005MSSQLUser$%$%”
AND name NOT LIKE ”%\SQLServer2005SQLAgentUser$%$%”
AND name NOT LIKE ”%\SQLServer2005MSFTEUser$%$%”
AND name NOT IN (”BUILTIN\Administrators”, ”NT AUTHORITY\SYSTEM”);’
insert into #Sync_Logins
execute sp_executesql @sql
SET @sql=N”;
SET @sql = ‘SELECT ”EXEC sp_addsrvrolemember ” + QUOTENAME(L.name) + ”, ” +
QUOTENAME(R.name)
FROM [‘+@Primary_Replica+’].master.sys.server_principals L JOIN [‘+@Primary_Replica+’].master.sys.server_role_members RM
ON L.principal_id=RM.member_principal_id
JOIN [‘+@Primary_Replica+’].master.sys.server_principals R
ON RM.role_principal_id=R.principal_id
WHERE L.type IN (”U”,”G”,”S”)
AND L.name NOT LIKE ”%\SQLServer2005MSSQLUser$%$%”
AND L.name NOT LIKE ”%\SQLServer2005SQLAgentUser$%$%”
AND L.name NOT LIKE ”%\SQLServer2005MSFTEUser$%$%”
AND L.name NOT IN (”BUILTIN\Administrators”, ”NT AUTHORITY\SYSTEM”, ”sa”);’
insert into #Sync_Logins
execute sp_executesql @sql
SET @sql=N”;
SELECT @sql=@sql+’ ‘+[Script] FROM #Sync_Logins;
EXECUTE master.sys.sp_executesql @sql;
drop table #Sync_Logins

-> Create a Linked Server to query the primary Replica. In my Environment, Linked servers JBSERVER2 and JBSERVER3 will be created on JBSERVER1. Linked servers JBSERVER1 and JBSERVER3 will be created on JBSERVER2. Linked servers JBSERVER1 and JBSERVER2 will be created on JBSERVER3.

-> The job will gracefully exit with a message “Script cannot run on primary Replica” if the job executes on Primary Replica. If the Job executes on the Secondary replica, It queries the list of Logins on the primary replica and will create the logins that are missing on the Secondary Replicas.

-> This solution just adds the missing Logins on the Secondary Replicas, but will not Drop logins on the Secondary Replica that are not present on the Primary.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Moving all Availability Groups in the Event of a Single Availability Group Failover

Environment

Blog30_1.PNG

-> Disaster Recovery for a SQL Server 2016 Standard Edition Database  Server was implemented using Alwayson Basic Availability group.

-> One of the availability group failed over and that caused application outage, as other databases required by the application were residing on a different server.  Implementing a Listener doesn’t makes sense in this scenario as the application is dependent on bunch of databases and creating Listener for each Availability group doesn’t seem practical and the application cannot use multiple Listeners in their connection string.

-> We decided that in an event of a failover for any Availability group. All other availability groups will also be failed over, Just to make sure that all the availability groups are on the same replica.

-> We decided to implement a Powershell script to perform this. This solution will work only for Basic Availability groups.

-> Create a Linked Server on each of the 2 SQL Server nstance to access the master database of other Database Instance. For security reasons we can use “Be made using the login’s current security context’.

-> Make sure all the Availability groups are at the same instance. Execute the below query on both Primary and secondary instance on Master Database only once.

IF EXISTS(SELECT *
FROM   dbo.AG_role)
DROP TABLE dbo.AG_role
create table AG_role (AGName varchar(30)
,PrimaryReplica varchar(30)
,DBName varchar(30)
,Role int)
set nocount on
Declare @AGName varchar(30)
Declare @PrimaryReplica varchar(30)
Declare @DBName varchar(30)
DECLARE AG_Cursor CURSOR STATIC FOR
SELECT
AG.name AS [AvailabilityGroupName],
ISNULL(agstates.primary_replica, ”) AS [PrimaryReplicaServerName],
dbcs.database_name AS [DatabaseName]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
ON arstates.replica_id = dbcs.replica_id
LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs
ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id
ORDER BY AG.name ASC, dbcs.database_name
OPEN AG_Cursor
FETCH NEXT FROM AG_Cursor into @AGName, @PrimaryReplica, @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
insert into AG_role values(@AGName, @PrimaryReplica, @DBName,sys.fn_hadr_is_primary_replica(@DBName))
FETCH NEXT FROM AG_Cursor into @AGName, @PrimaryReplica, @DBName
END
CLOSE AG_Cursor
DEALLOCATE AG_Cursor

-> Save the below file as a powershell script on both Primary and secondary Database server. Create a Windows task on both servers to execute the script every 1 min on both servers. Please make sure $PrimaryReplica is same as the server where the powershell script is saved. The $SecondaryReplica will be the other server that is part of the Alwayson Availability group.

$PrimaryReplica = “JBSERVER1”;
$SecondaryReplica = “JBSERVER2”;
Invoke-Sqlcmd “create table AGFailover(AGSQL nvarchar(max),flag int default 0);” -ServerInstance $PrimaryReplica;
Invoke-Sqlcmd ”
Declare @Ag_Current int
Declare @Ag_Previous int
Declare @AGName varchar(30)
Declare @PrimaryReplica varchar(30)
Declare @DBName varchar(30)
Declare @Role int
Declare @Flag int
DECLARE AG_Cursor CURSOR STATIC FOR
SELECT * from AG_Role
OPEN AG_Cursor
FETCH NEXT FROM AG_Cursor into @AGName, @PrimaryReplica, @DBName, @Role
WHILE @@FETCH_STATUS = 0
BEGIN
set @Ag_Current = (select sys.fn_hadr_is_primary_replica(@DBName))
set @Ag_Previous = (select Role from Ag_Role where DBName=@DBName)
if(@Ag_Current = @Ag_Previous)
BEGIN
print ‘No Failover occured’
insert into AGFailover (AGSQL)
select ‘ALTER AVAILABILITY GROUP [‘+@AGName+’] FAILOVER;’
END
ELSE
BEGIN
–print ‘Failover occured’
if (@Ag_Previous =1)
BEGIN
set @flag=1
END
END
FETCH NEXT FROM AG_Cursor into @AGName, @PrimaryReplica, @DBName, @Role
END
CLOSE AG_Cursor
DEALLOCATE AG_Cursor;
if (@flag=1)
begin
update AGFailover set flag=1
end” -ServerInstance $PrimaryReplica;
Invoke-Sqlcmd ”
DECLARE @AGSql NVARCHAR(MAX) = N”;
SELECT
@AGSql = @AGSql + AGSQL FROM $PrimaryReplica.master.dbo.AGFailover where flag=1;
EXEC sp_executesql @AGSql;” -ServerInstance $SecondaryReplica;
Invoke-Sqlcmd “drop table AGFailover;” -ServerInstance $PrimaryReplica;
Invoke-Sqlcmd “delete from AG_role;” -ServerInstance $PrimaryReplica;
Invoke-Sqlcmd ”
set nocount on
Declare @AGName varchar(30)
Declare @PrimaryReplica varchar(30)
Declare @DBName varchar(30)
DECLARE AG_Cursor CURSOR STATIC FOR
SELECT
AG.name AS [AvailabilityGroupName],
ISNULL(agstates.primary_replica, ”) AS [PrimaryReplicaServerName],
dbcs.database_name AS [DatabaseName]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
ON arstates.replica_id = dbcs.replica_id
LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs
ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id
ORDER BY AG.name ASC, dbcs.database_name
OPEN AG_Cursor
FETCH NEXT FROM AG_Cursor into @AGName, @PrimaryReplica, @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
insert into AG_role values(@AGName, @PrimaryReplica, @DBName,sys.fn_hadr_is_primary_replica(@DBName))
FETCH NEXT FROM AG_Cursor into @AGName, @PrimaryReplica, @DBName
END
CLOSE AG_Cursor
DEALLOCATE AG_Cursor;” -ServerInstance $PrimaryReplica;

-> Whenever a failover of any of the availability group happens, the script executing from the task scheduler will check for the failover and fails over all other Availability groups.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.