TechBrothersIT is the blog spot and a video (Youtube) Channel to learn and share Information, scenarios, real time examples about SQL Server, Transact-SQL (TSQL), SQL Server Database Administration (SQL DBA), Business Intelligence (BI), SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS), Data Warehouse (DWH) Concepts, Microsoft Dynamics AX, Microsoft Dynamics Lifecycle Services and all other different Microsoft Technologies.
In this video of SQL Server DBA Tutorial you will learn following:
1- How to patch or update Availability group with one secondary?
2- How to change Synchronous commit to Asynchronous commit in AG settings?
3- How to failover Availability group?
4- How to failback Availability group after patching?
5- How to patch secondary replica?
6- How to patch Primary Replica?
7- How to test Availability group functionality after patching?
Patching or Updating Availability Group with one Secondary - DBA Tutorial
In this video you will learn following:
1- How to patch or update SQL Server instances replicas with multiple Availability groups
2- How to free up replica to patch
3- How to failover Primary replica to it's secondary replica
4- How to execute windows patch on Availability groups replicas
5- How to test Availability groups after patching or updating
6- How to resume data movement in Availability group
General process steps
1-Remove Automatic Failover on all
synchronous-commit secondary replicas
2-Upgrade all remote and secondary nodes
3-Manually failover AG to updated secondary
replica
4-Make sure everything is working fine before you
start primary replica
5-Update primary replica
6-Manually failback AG to primary replica
7-Make sure everything is working fine
8-Configure automatic failover, synchronous commit
as it was before upgrade/patching
Patching/Updating AG with one Remote Secondary
1-Change Synchronous commit to Asynchronous commit
(If Any)
2-Change Automatic Failover to Manual Failover
3-Make sure AG Group is on Primary Replica
4-Upgrade Secondary Replica (Remote Secondary)
5-Failover over to secondary Replica (Remote)
6-Upgrade Primary Replica
7-Failback AG to Primary Replica
8-Set Synchronous commit and Automatic Failover as
it was before upgrade
Patching/Updating AG with Failover Cluster Instance
CLT01
CLT02
AG
NY01
NY02
1-Failover SQL Server instance/instances to CLT01
2-Upgrade CLT02
3-Failover SQL Server instances on CLT02
4-Make sure All works fine
5-Repeat the process for NY01 and NY02
6-Manual failover AG to NY instances from CLT
7-Make sure all works fine.
Patching/Updating SQL Server instance with multiple AG groups
Note: Change Synchronous commit to Asynchronous and
Automatic Failover to Manual Failover before performing below steps
1- Free up Replica 3 and patch or update
2- Failover AG to make sure all works fine
3- Free up Replica 2
4- Failover AG to make sure all works fine
5- Free up Replica 1
6- Failover AG and see if all works ok
How to patch SQL Server with Multiple AlwaysOn Availability Groups - SQL Server DBA Tutorial
Create Tabular Report in SSRS
Add Images to the SSRS Report
Change the Image Depending Upon Cell Value in SSRS Report
Expressions used in the demo =Switch(Fields!ProductName.Value="TV","TV",Fields!ProductName.Value="Cell Phone","CellPhone",Fields!ProductName.Value="Laptop", "Laptop",Fields!ProductName.Value="Computer","Computer")
How to Show Different Images depending Upon Value of Cell in SSRS Report - SSRS Tutorial
1- Patching or updating Availability Group replicas when SQL Server is installed in Cluster mode
2- How to failover SQL Server instance from one node to another node in SQL Server?
3- How to apply Patch on secondary replica when Availability Group is setup on the server?
4- How to check if availability group is working fine after patching on secondary replica?
5- How to resume database movement in SQL Server Availability group?
General process steps
1-Remove Automatic Failover on all
synchronous-commit secondary replicas
2-Upgrade all remote and secondary nodes
3-Manually failover AG to updated secondary
replica
4-Make sure everything is working fine before you
start primary replica
5-Update primary replica
6-Manually failback AG to primary replica
7-Make sure everything is working fine
8-Configure automatic failover, synchronous commit
as it was before upgrade/patching
Patching/Updating AG with one Remote Secondary
1-Change Synchronous commit to Asynchronous commit
(If Any)
2-Change Automatic Failover to Manual Failover
3-Make sure AG Group is on Primary Replica
4-Upgrade Secondary Replica (Remote Secondary)
5-Failover over to secondary Replica (Remote)
6-Upgrade Primary Replica
7-Failback AG to Primary Replica
8-Set Synchronous commit and Automatic Failover as
it was before upgrade
Patching/Updating AG with Failover Cluster Instance
CLT01
CLT02
AG
NY01
NY02
1-Failover SQL Server instance/instances to CLT01
2-Upgrade CLT02
3-Failover SQL Server instances on CLT02
4-Make sure All works fine
5-Repeat the process for NY01 and NY02
6-Manual failover AG to NY instances from CLT
7-Make sure all works fine.
Patching or Updating Availability Group with SQL Server Failover Cluster Instance Part 1 - DBA Tutorial
1- Patching or updating Availability Group replicas when SQL Server is installed in Cluster mode
2- How to failover SQL Server instance from one node to another node in SQL Server?
3- How to apply Patch on secondary replica when Availability Group is setup on the server?
4- How to check if availability group is working fine after patching on secondary replica?
5- How to resume database movement in SQL Server Availability group?
6- How to failover SQL Server resources from one node to another node?
General process steps
1-Remove Automatic Failover on all
synchronous-commit secondary replicas
2-Upgrade all remote and secondary nodes
3-Manually failover AG to updated secondary
replica
4-Make sure everything is working fine before you
start primary replica
5-Update primary replica
6-Manually failback AG to primary replica
7-Make sure everything is working fine
8-Configure automatic failover, synchronous commit
as it was before upgrade/patching
Patching/Updating AG with one Remote Secondary
1-Change Synchronous commit to Asynchronous commit
(If Any)
2-Change Automatic Failover to Manual Failover
3-Make sure AG Group is on Primary Replica
4-Upgrade Secondary Replica (Remote Secondary)
5-Failover over to secondary Replica (Remote)
6-Upgrade Primary Replica
7-Failback AG to Primary Replica
8-Set Synchronous commit and Automatic Failover as
it was before upgrade
Patching/Updating AG with Failover Cluster Instance
CLT01
CLT02
AG
NY01
NY02
1-Failover SQL Server instance/instances to CLT01
2-Upgrade CLT02
3-Failover SQL Server instances on CLT02
4-Make sure All works fine
5-Repeat the process for NY01 and NY02
6-Manual failover AG to NY instances from CLT
7-Make sure all works fine.
Patching or Updating Availability Group with SQL Server Failover Cluster Instance Part 3 - DBA Tutorial
1- Patching or updating Availability Group replicas when SQL Server is installed in Cluster mode
2- How to failover SQL Server instance from one node to another node in SQL Server?
3- How to apply Patch on secondary replica when Availability Group is setup on the server?
4- How to check if availability group is working fine after patching on secondary replica?
5- How to resume database movement in SQL Server Availability group?
6- How to failover SQL Server resources from one node to another node?
General process steps
1-Remove Automatic Failover on all
synchronous-commit secondary replicas
2-Upgrade all remote and secondary nodes
3-Manually failover AG to updated secondary
replica
4-Make sure everything is working fine before you
start primary replica
5-Update primary replica
6-Manually failback AG to primary replica
7-Make sure everything is working fine
8-Configure automatic failover, synchronous commit
as it was before upgrade/patching
Patching/Updating AG with one Remote Secondary
1-Change Synchronous commit to Asynchronous commit
(If Any)
2-Change Automatic Failover to Manual Failover
3-Make sure AG Group is on Primary Replica
4-Upgrade Secondary Replica (Remote Secondary)
5-Failover over to secondary Replica (Remote)
6-Upgrade Primary Replica
7-Failback AG to Primary Replica
8-Set Synchronous commit and Automatic Failover as
it was before upgrade
Patching/Updating AG with Failover Cluster Instance
CLT01
CLT02
AG
NY01
NY02
1-Failover SQL Server instance/instances to CLT01
2-Upgrade CLT02
3-Failover SQL Server instances on CLT02
4-Make sure All works fine
5-Repeat the process for NY01 and NY02
6-Manual failover AG to NY instances from CLT
7-Make sure all works fine.
Patching or Updating Availability Group with SQL Server Failover Cluster Instance Part 2 - DBA Tutorial
1- Patching AlwaysOn Availability Group replicas best practices
2- General Steps to update or Patch Availability Group replicas in SQL Server
3- Overview of Availability Group configuration in SQL Server
4- How to change Availability Group configuration setting according to best practices.
Patching/Updating best practices for AG Groups
1-Make sure manual failover to one of your
synchronous commit replica is successful
2-Take backup of your database
3-Check consistency of your Database (DBCC) and
make sure you don’t get any error on all database involved in AG
4-Upgrade Remote Replica (if any) first, then
local secondary replica and primary should be updated in the end to avoid any
potential application downtime
5-Redirect your backup preferences if secondary
replica which is being updated is configured to take backups, during updates,
replica cannot perform backups
6-Remove Automatic failover from AG to prevent unintended failover during
patching/upgrading
7-If you have synchronous commit replica, manually
failover AG to that secondary replica, otherwise please make sure you resume
your data movement after you manually failover to asynchronous commit secondary
replicas
8-Disable any monitoring (Alerts) during
Patching/Updating process
9-Disable Replication if you configured
replication with AlwaysOn Availability Group Databases
Patching Or Updating AlwaysOn Availability group Replicas in SQL Server Best Practices
How to create Tabular Report by using Integer and String Type Columns with Null values
How to Convert Null value in Integer type column to 0 and Null value in String Type Column to "NA" by using IsNothing Function and IIF Function in SSRS
Replace Null Value to 0 or NA in SSRS Report by using IsNothing and IIF Function - SSRS Tutorial
1- How to plan backups with AlwaysOn Availability Group in SQL Server?
2- What are the backup options in AlwaysOn Availability Group in SQL Server?
3- What is Preferred Secondary backup option in SQL Server?
4- What is Secondary Only backup Option in SQL Server?
5- What is Primary Only backup Option in SQL Server?
6- What is Any Replica backup Option in SQL Server?
7- What is priority backup Option in SQL Server?
8- How to script backup Jobs for Availability Group in SQL Server?
9- How to create and schedule AG Group database backup jobs on all secondary replica
10- Understanding Auto backup preferences in AG database backups
Plan Backups with AlwaysOn Availability Groups in SQL Server - DBA Tutorial
1- How to setup Alerts for AlwaysOn Availability Group in SQL Server?
2- How to find Error Number related to Availability Group in SQL Server?
3- How to setup alert with Error number in SQL Server?
4- How to setup alert if Availability Group fails over?
5- How to setup Alert if Availability group database movement suspended?
6- How to setup Alert if Availability Group database movement resumes?
7- How to configure SQL Server Agent to send alerts using DBMail?
8- How to create Alert, operator and configure them in SQL Server Agent Jobs?
Setup Alerts for AlwaysOn Availability Group in SQL Server - DBA Tutorial
How to Create Tabular Report with Parameter
Call Tabular Report in Main Report by using SubReport Item and Map parameter to it
How to call Report without Parameter by using Sub Report Item
Create Sub Reports and Call them in Master ( Main) Report in SSRS - SQL Server Reporting Services Tutorial
How to create tabular report with Multi Value Parameter
How to use Allow Multiple values option in Parameter Properties
Can we use Allow Null Value Property for Multi Value parameter or not?
After Finding the Answer if we are able to use Allow Null Value Property or not for Multi Value Parameter, Find a way how to trick SSRS and use Null value in Drop down for Parameter Value
Write Expressions and use IIf in expressions to change the color or Font
Show Null value in SSRS Report Cells by using Expression
Default Multi Value Parameters are Not Selected in SSRS Report - SSRS Tutorial
1- How to setup replication with AlwaysOn Availability Group in SQL Server
2- How to configure Remote Distributor in SQL Server
3- How to enable Replication on all Replica in AlwaysOn Availability Group
4- How to add possible publisher at remote Distributor in AlwaysOn Availability Group
5- How to Validate replication
6- Resolution of below Error:
Msg 21892, Level 16, State 1, Procedure sp_hadr_validate_replica_hosts_as_publishers, Line 60
Unable to query sys.availability replicas at availability group primary associated with virtual network name '' for server names of the member replicas:error = 18456, Level 14, State 1, Message: Login Failed for user 'NT AUTHORITY\ANONYMOUS' ..'
How to setup Replication with AlwaysOn Availability Group in SQL Server Part2 - DBA Tutorial
1- How to setup replication with AlwaysOn Availability Group in SQL Server
2- How to configure Remote Distributor in SQL Server
3- How to enable Replication on all Replica in AlwaysOn Availability Group
4- How to add possible publisher at remote Distributor in AlwaysOn Availability Group
5- How to Validate replication
6- Resolution of below Error:
Msg 21892, Level 16, State 1, Procedure sp_hadr_validate_replica_hosts_as_publishers, Line 60
Unable to query sys.availability replicas at availability group primary associated with virtual network name '' for server names of the member replicas:error = 18456, Level 14, State 1, Message: Login Failed for user 'NT AUTHORITY\ANONYMOUS' ..'
Step by Step Document used in the video
Step1:
Configure Distributor on remote Server
which is not part of AG Replicas ( primary or Secondary)
USEmaster;
GO
EXECsys.sp_adddistributor
@distributor ='CLT01\SQLDistributor',
@password
='Pa$$w0rd';
Step2:
-- Create Distributor database
USEmaster;
GO
EXECsys.sp_adddistributiondb
@database ='distribution',
@security_mode = 1;
Step3:
-- Add Publisher (Primary Replica) to
the distributor, Run on Distributor below script
-- Add your Remote distributor to all
Secondary Replica using below script.
EXECsp_adddistributor
@distributor ='CLT01\SQLDistributor',
@password
='Pa$$w0rd'
Step 9:
-- Add Subscriber as a Link server on
all Secondary servers, Make sure subscriber is in every publisher including in
Distributor (Primary, All Secondary and Distributor)
Important:
--Use GUI with SQL Server Login to add
Linked server, make sure connection succeeded
Step 10:
--Redirect Orignal Publisher to AG (Run
below command on distributor)
USE distribution;
GO
EXECsys.sp_redirect_publisher
@original_publisher ='TBSSQLCluster\SQLPROD',
@publisher_db ='SalesOrder_New',
@redirected_publisher ='SQLPROD_List';
Step 11:
--Run the Validation Store procedure at
the Distributor and make sure you don't get any error
Validation Error:
--Msg 21892, Level 16, State 1,
Procedure sp_hadr_validate_replica_hosts_as_publishers, Line 60
--Unable to query
sys.availability_replicas at the availability group primary associated with
virtual network name 'SQLPROD_List' for the server names of the member
replicas: error = 18456, error message = Error 18456, Level 14, State 1,
Message: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'..',
Resolution:
--Easy fix is to run the validation
directly using SSMS on your distributor - Remote to Distributor and run
validation command, should run successfully.
