Patching or Updating Availability Group with one Secondary - DBA Tutorial

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

SSRS Tutorial 55- How to Limit Number of Rows per Page by Parameter in SSRS Report

In this video of SSRS Tutorial, You will learn


  1. How to create Tabular Report
  2. How to use RowNumber and Ceiling Function in SSRS Report to build Logic for Row per page Group
  3. How to Create Parameter in SSRS so users can control the Number or Rows per page


Expressions used in the SSRS Report
=Ceiling(RowNumber(nothing)/Parameters!RowsPerPage.Value)



Limit Number of Rows per Page by Parameter in SSRS Report - SSRS Tutorial

SSRS Tutorial 54 - Limit Rows per Page in SSRS Report with Group

In this video of SSRS Tutorial, You will learn


  1. Create a Tabular Report in SSRS
  2. How to create a Group in SSRS Report
  3. How to Limit Rows per Page with Group in SSRS Report

Limit Rows per Page in SSRS Report with Group - SSRS Tutorial

SSRS Tutorial 53 - How to Handle Rows per Page in SSRS Report

In this video of SSRS Tutorial, you will learn

  1. Create Tabular Report
  2. Create Row Numbers for all the Rows in SSRS Report by using RowNumber Function
  3. How to use Ceiling Function to create Group
  4. How to use Page Break for Group Value to control rows per page
How to limit Number or Row per page in SSRS Report - SSRS Tutorial 

SSRS Tutorial 52 - What is Page Break in SSRS Report

 In this video of SSRS Tutorial, you will learn


  1. Create Tabular Report
  2. Add Group in SSRS Report
  3. How to use Page Break by Group value


How to Add Page Break on Other Report Items such as Matrix, Rectangle etc. SSRS Tutorial

Patching Or Updating SQL Server with multiple AlwaysOn Availability Groups - 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

SSRS Tutorial 50 - How to Show Different Images depending Upon Value of Cell in SSRS Report

In this SSRS Video Tutorial, You will learn

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

SSRS Tutorial 49 - What Are Expressions in SSRS Report

In this video of SSRS Tutorial, you will learn


  1. Introduction to Expressions in SSRS
  2. Where can we use Expressions in SSRS Report
  3. Change Backgroud Image by using Parameter Value
  4. Change Report Title by using Expressions

Expressions in SSRS Reports - SQL Server Reporting Services Tutorial 

Patching or Updating Availability Group with SQL Server Failover Cluster Instance Part 1 - DBA Tutorial

In this video you will learn following:

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

Patching or Updating Availability Group with SQL Server Failover Cluster Instance Part 3 - DBA Tutorial

In this video you will learn following:

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

Patching or Updating Availability Group with SQL Server Failover Cluster Instance Part 2 - DBA Tutorial

In this video you will learn following:

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

Patching Or Updating AlwaysOn Availability group Replicas in SQL Server Best Practices - DBA Tutorial

In this video you will learn following:

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 

SSRS Tutorial 51 - Add Page X of TotalPages in Report Footer or Report Header by using expressions in SSRS Report

In this video of SSRS Tutorial, you will learn


  1. How to Create Tabular Report in SSRS
  2. How to Add Report Header or Footer in SSRS Report
  3. How to Add Page Number of Total Pages in Report Footer or Report Header



="Page "+ CStr(Globals!PageNumber) + " of "+CStr(Globals!TotalPages)


Add Page X or TotalPages in Report Footer by using expressions in SSRS Report - SSRS Tutorial

SSRS Tutorial 48 - How to show Report Parameter values while displaying Rows in SSRS Report

In this SSRS Video Tutorial , You will learn

  1. How to create Tabular Report with Single Value Parameter and display on Report
  2. How to create Multi Value Parameter and Display multiple values from Parameter to Report
  3. Use Join Function in SSRS Report to Join Values from Multi Value Parameter


Expressions for Single Value parameter used in the video
="Currently Looking at "+=Parameters!RegionName.Value)+" Region Records"

Expressions for Multi Value parameter used in the video
="Currently Looking at "+Join(Parameters!RegionName.Value," and ")+" Region Records"


Show Report Parameter values while displaying Rows in SSRS Report - SSRS Tutorial

SSRS Tutorial 47 - How to use IIF function and Switch Function in SSRS Report

 In this video of SSRS Tutorial, you will learn

  1. How to Change Background Color or Rows depending upon Field Value
  2. How to Change Font Color depending upon Field Value in SSRS Report

Use IIF function and Switch Function in SSRS Report - SSRS Tutorial

SSRS Tutorial 46 - How to Hide Empty Rows in SSRS Report

In this video of SSRS Tutorial, You will learn

  1. Create Tabular Report in SSRS with Empty Values
  2. Hide the Row depending upon Empty Value in One Column
  3. Hide the Rows Depending Upon Empty Value in Multiple Columns
  4. Understand Row Visibility and Expressions in SSRS Report
  5. Understand IsNothing Function and IIF Function in SSRS Report
=IIF((Isnothing(Fields!SoldPrice.Value) and Isnothing(Fields!Region.Value)),true,false)


How to Hide Empty Rows in SSRS Report - SSRS Tutorial 

SSRS Tutorial 45 - How to Replace Null Values in SSRS Report

 In this video of SSRS Tutorial, you will learn

  1. How to create Tabular Report by using Integer and String Type Columns with Null values
  2. 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

How to Plan Backups with AlwaysOn Availability Groups in SQL Server - DBA Tutorial

In this video you will learn following:

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

How to Setup Alerts for AlwaysOn Availability Group in SQL Server - DBA Tutorial

In this video you will learn following:

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

SSRS Tutorial 44 - How to change color of Rows per Group in SSRS Report

In this video of SSRS Tutorial, you will learn
  1.  
  2. How to create Tabular Report
  3. How to create Group in SSRS Report
  4. How to use RunningValue Function with CountDistinct
  5. How to use IIF function to check the expression if True or False and Change Background Color

=IIF(RunningValue(Fields!Region.Value,CountDistinct,Nothing) Mod 2=1,"Pink","Gray")


How to Change Color or Rows Per Group in SSRS Report - SSRS Tutorial 

SSRS Tutorial 43 - How to change Color of Alternative Row in SSRS Report

 In this video of SSRS Tutorial, you will learn

  1. How to create Tabular Report
  2. How to use RowNumber Function
  3. How to use IIf Function to check either expression is true or false and set the Background Color or Rows

 How to change Color of Alternative Row in SSRS Report - SSRS Tutorial

SSRS Tutorial 42- Create Sub Reports and Call them in Master ( Main) Report

In this video of SSRS Tutorial, You will learn

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


  Check out related Posts / Videos 

SSRS Tutorial 41- How to Add, Remove,Rename Parameter in SSRS Report

In this video of SSRS Tutorial, you will learn


  1. How to Add Single Value parameter or Multi Value parameter
  2. How to Rename parameter in SSRS Report
  3. How to Delete Parameter from SSRS Report

How to Add, Remove,Rename Parameter in SSRS Report - SSRS Tutorial

SSRS Tutorial 40 - Default Multi Value Parameters are Not Selected in SSRS Report

In this video of SSRS Tutorial, you will learn
  1. Create SSRS Report with Multi Value parameter
  2. Provide Values to Parameter by using Query
  3. Set Default Values for Parameter and Find out why No rows are displayed and Fix

 Default Multi Value Parameters are Not Selected in SSRS Report - SSRS Tutorial

SSRS Tutorial 39 - Show Null value for Multi Value Parameter in SSRS Report

In this video of SSRS Tutorial, you will learn

  1. How to create tabular report with Multi Value Parameter
  2. How to use Allow Multiple values option in Parameter Properties
  3. Can we use Allow Null Value Property for Multi Value parameter or not?
  4. 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
  5. Write Expressions and use IIf in expressions to change the color or Font
  6. Show Null value in SSRS Report Cells by using Expression


 Default Multi Value Parameters are Not Selected in SSRS Report - SSRS Tutorial

How to setup Replication with AlwaysOn Availability Group in SQL Server Part2 - DBA Tutorial

In this  video you will learn following:

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

How to setup Replication with AlwaysOn Availability Group in SQL Server Part1 - DBA Tutorial

In this  Part 1 video you will learn following:

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)

USE master;
GO
EXEC sys.sp_adddistributor
    @distributor = 'CLT01\SQLDistributor',
    @password = 'Pa$$w0rd';

Step2:


-- Create Distributor database

USE master;
GO
EXEC sys.sp_adddistributiondb
    @database = 'distribution',
    @security_mode = 1;

Step3:


-- Add Publisher (Primary Replica) to the distributor, Run on Distributor below script

USE master;
GO
EXEC sys.sp_adddistpublisher
    @publisher = 'TBSSQLCLUSTER\SQLPROD',
    @distribution_db = 'distribution',
    @working_directory = '\\CLT01\AG_Replication_Share',
       @security_mode =---This shows windows Authentication, you can use sql Auth using @login and @password Parameter

Step 4:


-- Add Remote distributor to the Publisher (primary Replica)

exec sys.sp_adddistributor
    @distributor = 'CLT01\SQLDistributor',
    @password = 'Pa$$w0rd'

Step 5:


-- Create Publication and subsciption

Step 6:


-- Make sure replication is enabled on all secondary Replicas

USE master;
GO
DECLARE @installed int;
EXEC @installed = sys.sp_MS_replication_installed;
SELECT @installed;

--Results=1

Step 7:


-- Add all secondary replica's as possible publisher incase of failover of AG, Run below
script on Distributor

EXEC sys.sp_adddistpublisher
    @publisher = 'NYSQLCLUSTER\NYSQLPROD',
    @distribution_db = 'distribution',
    @working_directory = '\\CLT01\AG_Replication_Share'

Step 8:


-- Add your Remote distributor to all Secondary Replica using below script.

EXEC sp_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
EXEC sys.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.

USE distribution;
GO
DECLARE @redirected_publisher sysname;
EXEC sys.sp_validate_replica_hosts_as_publishers
    @original_publisher = 'TBSSQLCLUSTER\SQLPROD',
    @publisher_db = 'SalesOrder_New',
    @redirected_publisher = @redirected_publisher output;












Setup Replication with AlwaysOn Availability Group in SQL Server - DBA Tutorial