Step by Step Configure Always on Availability Group on SQL Server 2012 With Stand alone Instance

To configure always on Points to consider

Node should be part of windows cluster Node with Failover Cluster features Enabled

We should need to enable alwayson features on SQL Services Configuration Manager

SQL node should be running on Domain account

Windows firewall should be enabled on Port 1433,5022

Configure Quorum settings between both nodes

Need to store SQL backup on share path (network)

Database should be Both Backed Up (Full & Transaction Log Backup) on Primary and restored on Secondary

Secondary Databases should be in Restoring Mode

Asynchronous mode means Data Loss and Status would be in Secondary as Synchronizing or Not Synchronizing we can not set automatic failover we should set manual failover

Synchronous mode means NO Data Loss and Status would be in Primary & Secondary always Synchronized we can set automatic failover

Differential Backup/Full Backup is not supported on Secondary but log backup supported on Secondary

Logins we cant create on Secondary but we can create on Primary

Logins/Jobs/Maintenance Plans not created from Primary to Secondary when Failover Operation

Secondary Replica is Read only You cant update records on table

In AlwaysOn 2012 and 2014, you were allowed a maximum of two replicas to designate for automatic failover. AlwaysOn 2016 allows three replicas for automatic failover.

Prerequisites for Installing SQL Server Always On

Windows:

Windows Server 2008 R2. Windows Server 2008R2 is the minimum Windows Server edition allowed for deploying AlwaysOn. However, it is strongly recommended that you use Windows Server 2012 instead, as there are many issues and headaches associated with 2008 R2

Click here for a more in-depth explanation: https://www.youtube.com/watch?v=-jxQERXPM9s

Windows Server Failover Cluster. WSFC must be installed on every replica (primary and secondaries) in the AlwaysOn group. This is done through Server Manager > Add Roles & Features > Add Failover Clustering. https://youtu.be/X4gVaN-i_dE

And each SQL Server that hosts an availability group replica must reside on separate nodes of a single WSFC cluster.

Net 3.5.1 or greater. If .net 3.5.1 or greater is not already installed on your primary and secondary replicas, you will need to install this. This can be installed at the same time you install your Windows Failover Cluster (via Server Manager > Add Roles & Features > Add Failover Clustering)

Windows hot fixes. You need to install all available windows hotfixes on every replica prior to deploying AlwaysOn. This is particularly important if you are using Windows Server 2008R2, as most of the hotfixes are aimed toward that version. Again, it is strongly recommended that you use Windows Server 2012 on all of your replicas instead. Here is a list of the available hotfixes at this time: https://msdn.microsoft.com/en-us/library/ff878487(v=sql.110).aspx#WinHotfixes

Windows Domain. All replicas in your AlwaysOn group must be in the same windows domain. They must be able to communicate with each other.

No domain controller. None of your replicas can be a domain controller. AlwaysOn groups are not supported on a domain controller.

Windows Firewall. Most likely you will need to adjust your Windows Firewall setting to allow the replicas to communicate with each other.

Sufficient Disk Space. You don’t have to have identical hardware on each replica, but you do need enough disk space to hold all of your databases, and to account for the growth of those databases. As your databases on the primary replica grow, the replicated databases on your secondaries will also grow the same amount. And if you have other databases on your secondary replicas that are not part of your AlwaysOn group, your disk space must account for the size and growth of those databases as well.

Sufficient Resources. Again, you don’t have to have identical hardware on each of your replicas, but you need to have sufficient resources on your secondaries to handle the same workload as your primary. If you’re thinking that “we’ll use our good, powerful server as the primary replica, and use the slower, weaker server as a secondary” with the thought that if we do have to fail over, we will just know up front that the performance will be a little slower while we get the primary server back up and running… well guess what. You’re right. It will be slower performance if you have to fail over. But it will also be slower if you don’t fail over! That’s because the primary sends transactions to the secondary, and with synchronous data replication, the primary has to wait for the secondary to harden the log before it can move on to the next task. So your primary will only operate as fast as your slowest secondary. Your AlwaysOn group is only as fast and strong as your weakest link. So be very familiar with your workload, and try and make sure your replicas are equally yoked.

Instance:

SQL Server 2012 or 2014 Enterprise Edition. For testing or development purposes, you can install Developer or Evaluation edition, but for a production environment, you must install Enterprise edition. Standard edition will not work.

Database Collation. Databases in your AlwaysOn group must use the same collation…they cannot differ across replicas.

SQL Server Collation. All replicas in your AlwaysOn group must use the same SQL Server collation

Active Directory Services. No replicas can run Active Directory services. This is not supported with AlwaysOn.

Database Mirroring Endpoint. Each instance needs a database mirroring endpoint. If you have more than one instance on your server, you will need to create an additional endpoint so the instances can communicate with each other. Here is instructions on how to do that: https://msdn.microsoft.com/en-us/library/ms187811(v=sql.110).aspx

SQL Service Account. Your account that runs SQL Services must be a domain account. Do not use the local machine service account. The SQL service account must be able to access every replica, and therefore must be a domain account.

Also, this account must belong to the Administrators Group on each of the replicas.

And this SQL service account needs connect permissions (given through SSMS). Right click on the SQL Service login to open the properties dialog box, go to the Securables page, and make sure the ‘Connect SQL’ Grant box is checked. Do this on all your instances

Databases:

Full Recovery Model. Make sure your databases are in Full Recovery Mode, not Simple or Bulk Logged. Also, these databases must be taken out of any tlog backup maintenance process while the AlwaysOn group is being created.

User database. Databases included in your AlwaysOn group must be user databases. System databases cannot participate in AlwaysOn Availability Groups.

Read/Write database. Read-only databases cannot belong to an AlwaysOn group.

Multi-user database. Databases must be in multi-user mode, they can’t be in single user mode.

Don’t use AUTO_CLOSE. Check the properties of your databases, and make sure this option is set to ‘False’.

DB in only one Availability Group. Databases may only belong to one availability group at a time. You can have more than one AlwaysOn Availability Group on your instance, but databases cannot belong to more than one group.

Not configured for database mirroring. Your databases cannot be enabled for database mirroring. Make sure this feature is not enabled.

Full Backups. Make sure full backups of each of your databases are made prior to installing AlwaysOn.

Allow Remote Connections. This can be done in SSMS either through the Instance properties, or by using sp_configure, which ever you prefer.

EXEC sp_configure ‘remote access’, 1;

RECONFIGURE;

Some of the enhancements to the AlwaysOn feature in SQL Server 2016

AlwaysOn Availability Groups made its debut in SQL Server 2012 as a new feature that enhanced the existing technologies of database mirroring and failover clustering to provide an option for high availability and disaster recovery. AlwaysOn gives you the ability to scale out several secondary Servers (replicas) for high availability or disaster recovery purposes. The secondaries are usable for read-only operations (like reporting) and maintenance tasks (like backups), and AlwaysOn provides for zero data loss protection and automatic page repair

Some of the enhancements to the AlwaysOn feature in SQL Server 2016 are only available if your SQL server is running on the Windows Server 2016 platform. However, if you are still on Windows Server 2012 R2 there are still many robust improvements that are available.

Some of the new features and enhancements for AlwaysOn 2016 are:

More failover targets
Better log transport performance
Load balancing for readable secondaries
DTC support
Database-level health monitoring
Group Managed Service Account (GMSA) support
Basic Availability Groups
BI enhancements
Non-domain replicas
Encrypted database support
SSIS catalog support

for more info:

https://blog.sqlrx.com/2016/08/18/alwayson-2016-new-and-improved/

or see bottom of this blog page

Advantages of AlwaysON

1)It does not need witness server

2)It uses Log Cache transaction fast(Database Mirroring not use this method very slow)

3)Backup Preferences, Load Balancing with the use of Application Intent

Dis Advantages of AlwaysON

1) Temp db size very High due to snapshot isolation row versioning

2) Immediate read on Secondary after write/update on primary may not give right results

3) additional indexes on secondary databases

4) we cannot set automatic failover for a clustered SQL Instance Due to race condition

5) Same Disk Layout ( Disk needs to be same on all)

Configure Windows cluster and add nodes.

I have two VMs with Windows server 2012 Enterprise edition 64 bit and SQL server Denali RTM x64 and I’m going to create a windows cluster just by adding 2 nodes and a virtual name for the cluster . Starting from Windows server 2008 failover cluster manager has to be enabled from roles and features applet. The cluster required for host Always on / HADR database doesn’t need to have shared storage or instance virtual name, the cluster can just be created and validated with a virtual name for it, I am using the virtual name for my cluster as WinClusterSQLTechi and the nodes are SQLNODE01 and SQLNODE03

Nodes SQLNODE01 and SQLNODE03 are part of the cluster WinClusterSQLTechi

I am showing windows cluster as see below

I also created SQL Cluster VSQL(SQLTechiCluster) as see below

SQLNODE01 info @ cluster

SQLNODE03 info @ cluster

Disk info as see below

Network 1 info

Network 2 info

SQLNODE01 Physical Drive info

SQLNODE03 Physical Drive info

Domain controller ISCSI info

SQL edition info @ SQLNODE01

SQL edition info @ SQLNODE03

SQL Services info @ SQL Node 01

SQL Services info @ SQL Node 03

Enable Always ON features on Configuration Manger on both nodes

you should enable always on features from configuration manager otherwise we will get error as see below

Demo database @ SQL NODE 01 with table info

Backup of Database @ Source (SQLNODE01)

We are taking database backup on share path(DC (Domain Controller location) share path

Database has been backedup successfully on share path @ DC

We are doing some modification on table

Transaction log Backup

now we are going to take Transaction log backup @ source (SQLNODE01) and append to Full Backup

Location of Full Backup appended transaction log backup

Step 1:

Right Click on Always on High Availability Group and click new Availability Group

Step 2:
Need to Specify name

Step 3:

Need to choose Database

We already taken full backup and log backup and recovery model in Full so it meets pre request

Step 4:

Need to give Secondary Replicas some times we have following error

(If you Cant configure cluster correctly you might get error while you choose Secondary replica

you need to solve )

The specified instance of SQL Server is hosted by a system that is not a Windows Server Failover Cluster(WSFC) node. (Microsoft.SqlServer.Management.HadrTasks)

ref:

http://www.sqlpanda.com/2014/10/how-to-fix-the-specified-instance-of.html

To Resolve this we need to ensure following

The way to fix it is disable the HADR from SQL Server configuration manager . RESTART SQL Server and SQL agent Multiple time and Rentable HADR and bounce SQL server and SQL agent.

After that we need to check query on sys.dm_hadr_cluster_members and sys.dm_hadr_cluster for both nodes.
both should be same value

we can mention readable secondary on primary /secondary

Endpoint info

sometimes if you have choose Endpoints (db mirroring 5022) not enabled on Firewall and

SQL Services are running on non-domain account we might get error

The connection to the primary replica is not active. The command can not be processed ( Microsoft SQL Server Error 35250)

To resolve this error 35250

one of the SQL Services should be running on Domain account and we Need to enable firewall port 5022 and alter database endpoint 5022 if needed

We need to enable Windows Firewall on both Inbound and Outbound Rules as see below

Start Run-->WF.MSC

we need to alter database mirroring Endpoint on both node if needed

We need to change local system account into domain account (cluadmin)

Ref:

https://blogs.msdn.microsoft.com/alwaysonpro/2013/12/09/create-availability-group-fails-with-error-35250-failed-to-join-the-database/

we did not enable listener so we got warnings

so we configured AlwaysON Successfully

SQL NODE01 Info

we can see show dash board info see below

table info as see secondary

Data modification

Now am going to modify records on Primary SQLNODE01

Data replicated on Secondary

If am going to change records on secondary it will not allow due to read only mode on secondary

Failover on Always ON (Manual):

we can do failover as follows

information @ SQLNODE01 (primary current moment)

information @ SQLNODE03 (secondary current moment)

we can give Failover from Right click of AG group or Dashboard

We need to choose available Replica

we need to specify replica our case SQLNODE03

SQLNODE01 Failed over successfully

SQLNODE03 is new primary
SQLNODE01 is secondary

We can see SQLNODE01 is now Secondary

Asynchronous Mode failover:

Asynchronous Mode there could be Data Loss and is should be in Manual failover

you can see Synchronous mode status always synchronized on both but Asynchronous Mode status always be Synchronizing or Not Synchronizing

Synchronous Mode:(NO Data Loss and always Synchronized)

Asynchronous Mode:(Data Loss and always Synchronizing/Not Synchrozing)

We are now going to initiate manual failover on Asynchronous Mode

Asynchronous means data loss so it gives warning as see below

Asynchronous data loss see below with warning

After that we can see in Dashboard

It is time for we should need to verify /Compare with tables data and update data to ensure data are up to date

or we can resume data movement once we are fine

after successful we can see showboard

Synchronizing means it should be in Secondary and ASynchronized Mode

You cant setup always on between cluster nodes and Named stand alone (same which involved on clustering SQL node1/2)

Here you can SQL NODE01 is primary on SQL clustering

SQLNODE03 is secondary(passive) so we cant enable alwayson

Now you can SQL NODE03 is Primary on SQL clustering

SQL SERVER ALWAYS ON AVAILABILITY GROUP COMMON ERRORS OR FAILURES

REF

http://www.sqlserverf1.com/tag/microsoft-sql-server-error-19405/

AlwaysON Availability Groups has been introduced with SQL Server 2012 and has been a very popular and most used feature as it provides both High Availability (HA) and Disaster Recovery (DR) solutions. As it is a new feature, there are many issues which DBA’s face while configuring AlwaysON Availability Groups.

Below are some of the common errors or failures related to SQL Server AlwaysON Availability Groups

Checking for compatibility of the database file location on the secondary replica resulted in an error. (Microsoft.SqlServer.Management.HadrTasks)
The following folder locations do not exist on the server instance that hosts secondary replica Node1\AGTest: S:\MSSQL11.AG1\SQL_DATA; Microsoft.SqlServer.Management.HadrTasks)
– This error occurs if the drive letters or the folder path does not match between primary and secondary replica.
– To resolve this error, make sure that same drive letter and folder path exists on both the servers or perform manual synchronization of secondary server databases using backup/restore.

Failed to create, join or add replica to availability group ‘AGTest’, because node ‘Node1’ is a possible owner for both replica ‘AGTest\AGTest’ and ‘Node1\AGTest1’. If one replica is failover cluster instance, remove the overlapped node from its possible owners and try again. (Microsoft SQL Server, Error: 19405)

– This error occurs, if SQL Server instance where you are setting up AlwaysON Availability Group is part of SQL Server failover clustering and has both nodes as possible owners and then You tried to add another availability Group for another SQL cluster instance involving these nodes.
– To resolve this error, either follow the solution suggested in the error message “If one replica is failover cluster instance, remove the overlapped node from its possible owners and try again” or instead of using cluster SQL instances, just use standalone instances.

Error while trying to connect to AlwaysON Availability group using Listener Name
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)
– This error occurs if the port number of the Listener is not 1433. To resolve this issue either use the port number along with the listener name like SQLAGLSTNR,1467 or create an alias.
– Make sure listener port is open to communicate. Test using telnet listenername portnumber
– Communication only works when using TCP network protocol.

Database Mirroring login attempt by user ‘Domain\ComputerName$.’ failed with error:
‘Connection handshake failed. The login ‘Domain\ComputerName$’ does not have CONNECT permission on the endpoint. State 84.’
– This error will be logged in SQL errorlog when trying to setup AlwaysON or Database Mirroring. This error occurs if the SQL Server services are running under local system accounts or if the SQL Service account does not have connect permission on the endpoint.
– To resolve the error, Change the SQL Server services to run under a domain account and then grant connect permission on endpoint to SQL Server service account.
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [Domain\SQLServiceAccount]
GO

Summary for the replica hosted by ServerName\INSTANCE2 Replica mode: Asynchronous commit
This replica will use asynchronous-commit availability mode and support only forced failover
(with possible data loss).
Note: This is a Failover Cluster Instance. Failover Cluster Instances do not support AlwaysOn automatic failover.
– This error occurs, if you are using SQL clustered instances to setup AlwaysON Availability Group.
– Cluster SQL Server instance will not support the automatic failover in availability groups. If you want automatic failover, install SQL server instance as standalone instance.

Cluster network name resource ‘TestAG’ failed to create its associated computer object in domain ‘testdomain.com’ during: Resource online.The text for the associated error code is: A constraint violation occurred.Please work with your domain administrator to ensure that:
The cluster identity ‘SQLClus$’ has Create Computer Objects permissions. By default all computer objects are created in the same container as the cluster identity ‘SQLClus$’.The quota for computer objects has not been reached. If there is an existing computer object, verify the Cluster Identity ‘SQLClus$’ has ‘Full Control’ permission to that computer object using the Active Directory Users and Computers tool.
– This error occurs if Cluster Name Object does not have rights to create a new Virtual Network Name Object. Refer below article for instructions to prestage the Virtual Name Object.
http://technet.microsoft.com/en-us/library/dn466519.aspx

Joining database on secondary replica resulted in an error.
(Microsoft.SqlServer.Management.HadrTasks)
Failed to join the database ‘AGTest’ to the availability group ‘AGGrpTest′ on the
availability replica ‘Replica2′. (Microsoft.SqlServer.Smo)
The connection to the primary replica is not active. The command cannot be processed.
(Microsoft SQL Server, Error: 35250 Level 16, State 7)
– Try below steps to fix the error
o Make sure that the alwaysON endpoint [Hadr_endpoint] is not blocked by firewall
o Verify and make sure that SQL Server service account of primary server is added as a login on all the secondary servers and vice-versa.
o If SQL Server service accunt is “Nt service\” or local system account then ensure system account (Domainname\systemname$) of each replica is added as a login to other replicas.
CREATE LOGIN Domain\replica2$] FROM WINDOWS
o Grant connect permission on alwaysON endpoints on each replicas for SQL Server service account of all other replicas On Secondary replica run below query by changing the domain and replica server names
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [Domain\replica1$]
On primary replica run below query by changing the domain and replica server names
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [Domain\replica2$]
o Make sure SQL Server name (select @@servername) matches with hostname of the server.
o Make sure cluster service startup account is added as SQL Server login.

Error while trying to setup AlwaysOn availability group
Operating System Error 1265(The system cannot contact a domain controller to service the authentication request. Please try again later.).
– This error can occur if the password of inter-domain trust account is not synchronized on both sides of the trust relationship.
– Refer below link to see if it helps fixing the error, else engage your domain Administrator.
http://support.microsoft.com/kb/816577/en-us

This is applicable on below versions of SQL Server

SQL Server 2012
SQL Server 2014

The Remote copy of database 'dbname' is not recovered far enough to enable database mirroring or to join it to the availability group. You need to apply missing log records to the remote database by restoring current log backups from principal/primary database (Microsoft SQL Server Error: 1408)

https://www.mssqltips.com/sqlservertip/4687/fix-sql-server-alwayson-availability-group-error-1408-joining-database-on-secondary-replica-resulted-in-an-error/

Troubleshooting AlwaysOn Error 1408

Step 1: As mentioned above, this tip will not demonstrate the steps to configure SQL Server AlwaysOn because I am assuming that you have already gone through the steps mentioned in my latest tips. At this point, I will go directly to the last window of the SQL Server AlwaysOn configuration where we get errors post execution as shown below.

Here you can see that SQL Server AlwaysOn was successfully configured on secondary replica PRI-DB2 whereas an error was thrown for the secondary replica SEC-DB2. Now we will check the error details to fix this issue. You can click on the "Error" link to get the details of the error as shown in the below screenshot.

We can see in the error details that "The remote copy of database "DRTest" is not recovered far enough to enable database mirroring or to join it to the availability group. You need to apply missing log records to the remote database by restoring the current log backups from the principal/primary database. (Microsoft SQL Server, Error: 1408)". That means the source and destination databases are not in sync. We will fix this issue in this tip. Click on the OK button to close this window.

Step 2: Now if you launch the SQL Server AlwaysOn dashboard report then you can see the current state of the AlwaysOn configuration as shown in the below screenshot.

You can see the Availability Group state is not healthy and is showing a warning. If you look down the list, you will see the warning for the secondary replica SEC-DB2 whereas another secondary replica PRI-DB2 is green indicating a "Synchronized" state. Now click on the "Warnings (1)" link to get more details. Once you click on the warning link you can see that the issue is due to the synchronization issue between the availability databases on the primary and this secondary replica.

Step 3: As we have seen, the availability databases on the secondary replica SEC-DB2 are not in sync with the primary replica because they are missing some log records. This is why SQL Server AlwaysOn is not able to apply the remaining logs to keep them synchronized. Now we will take a full backup and transaction log backup of both databases (DRTest and Test) on the primary replica and then we will restore it on the secondary replica SEC-DB2. We can use the COPY_ONLY backup option to not break the LSN numbers of the log files.

--Run the below command to issue a full backup of both databases 
BACKUP DATABASE DRTest
TO DISK = 'F:\BACKUP\DRTest_Copy.bak'
WITH COPY_ONLY;
GO

BACKUP DATABASE Test
TO DISK = 'F:\BACKUP\Test_Copy.bak'
WITH COPY_ONLY;

I executed above commands on the primary replica to take a full backup of both databases.

Now we will run transaction log backup of these databases then we will restore them on SEC-DB2 replica.

--Run below command to issue Transaction log backup of both databases 
BACKUP LOG DRTest
TO DISK = 'F:\BACKUP\DRTest_Copy.trn'
WITH COPY_ONLY;
GO

BACKUP LOG Test
TO DISK = 'F:\BACKUP\Test_Copy.trn'
WITH COPY_ONLY;

Step 4: Now we will restore both backup files on the secondary replica SEC-DB2. Either copy these backup files to the secondary replica and restore there or use a network path to access the backup files to restore both databases as I have done in the below screenshot. Run the below command to restore both databases with the NORECOVERY option on the secondary replica.

--Run below command to restore both databases into NO RECOVERY mode. 
RESTORE DATABASE DRTest
FROM DISK = '\\PRI-DB1\F$\BACKUP\DRTest_Copy.BAK'
WITH NORECOVERY, REPLACE;
GO

RESTORE DATABASE Test
FROM DISK = '\\PRI-DB1\F$\BACKUP\Test_Copy.BAK'
WITH NORECOVERY, REPLACE

As we can see both databases have been restored successfully, so the next step is to apply the transaction log backups which we have taken after the full backup. Run the below commands to apply the transaction log backups on these databases.

--Run below command to restore transaction log backups on both databases into NO RECOVERY mode. 
RESTORE LOG DRTest
FROM DISK = '\\PRI-DB1\F$\BACKUP\DRTest_Copy.trn'
WITH NORECOVERY, REPLACE;
GO

RESTORE LOG Test
FROM DISK = '\\PRI-DB1\F$\BACKUP\Test_Copy.trn'
WITH NORECOVERY, REPLACE

restore log backups on secondary replica

Step 5: You can see a "warning" icon on both availability databases on replica SEC-DB2. Ideally these availability databases should be green. So now we will join both databases to the Availability Group, so they show that they are healthy with a green status. Right click on database "DRTest" and choose "Join to Availability Group..."

Once you click on this option another window named "Join Database to Availability Group" will appear on your screen as shown below.

join Database to Availability Group window

Step 6: You can see the details about the database name on the screen. Now click on the "OK" button to proceed with the process of joining the Availability Group. Once you click on the OK button the execution will start and then disappear from your screen if it has executed successfully. The status of the availability databases should then become green. If there is an issue, it will appear on the screen with the error details.

Step 7: Now repeat step 5 and step 6 for the second availability database "Test" to add it to the availability group DBAG.

Step 8: You might be see the red cross bar for the availability database after adding them to Availability Group for few seconds. Don't worry about that just right click on the Availability Databases folder and choose the Refresh option. Once refreshed, you can see Availability Database state should be healthy and in green as shown in the below screenshot.

Availability database state post adding them to AG

Step 9: Now go to the primary replica PRI-DB1 and launch the SQL Server AlwaysOn dashboard report for this configuration. We can see the dashboard report for Availability Group "DBAG" in the right pane. The Availability Group state is now healthy and all status values are green. You can validate the configuration for the secondary replica SEC-DB2 as well as shown below.

Database "dbname" is not in a recovering state which is required for a mirror database or secondary database. the Remote database must be restored using WITH NORECOVERY (Microsoft SQL Server Error : 1464)

To fix this issue While you join database kindly ensure secondary database should be in restoring mode

Remote The mirror Database, "dbname", has insufficient transaction log data to preserve the log backup chain of the principal database. This may happen if a log backup from the principal database has not been taken or has not been restored on the mirror database (Microsoft SQL Server Error: 1478)

Solution:

this error causes we have only taken Full Backup from Primary and restored to Secondary we did not take Transaction log backup from primary

fix this problem we should need to take transaction log backup from principal and restored it to Secondary

you can see we had only taken full backup and restored That is cause this issue

we should need to take transaction log from primary (copy_only) backup and restored to secondary

Add Data File on Always ON Database

if both Primary and Secondary Data file location is same on database then alwayson automatically place the newly added data file primary to Secondary

Database info on Primary

Dabase info on Secondary

We are now adding one additional data file on Primary

it was successfully added on database @ primary

file added on primary

on table added/new row on primary

after few seconds data file successfully replicated through always on @ secondary

table newly added row replicated on secondary

How to Add Data file on Always ON availability Group database location have Different path

we can remove the database from always on on the secondary server that do not have the same drive configuration as the primary server. This puts the database on the secondary server in restoring state.
Now add the File to the primary sever.
Take a log backup on primary
Restore the log on secondary using the with move option and provide a folder that exists on the secondary
Now add the database back to always on secondary.
This way you don’t have to reinitialize from scratch, kind of useful when you are dealing with VLDB

Drive location on Primary

Drive location on Secondary

it is not be same if you have add file on X drive on Primary those X drive not available on Secondary

Here we trying to add on X drive for N secondary file

we have successfully added secondary file on Primary @ X drive

After we added our AlwaysON db throws Error

Database on Secondary not accessible and went to suspect mode

When we investigated it gives new path not available on Secondary ( X drive)

Normal restore operation would not work until we have to remove db from alwayson

we can info of backup from FILELISTONLY option

restore with move also did not work until we remove db from alwayson

Now we have to remove database from Alwayson Availability Group

Now we can restored database WITH MOVE ,Norecoveryoption successfully

Now database went it to restoring mode

Now we need to join database to always on availability group

it seems fine see below

you can see newly added file on Secondary

after restore and join db to alwayson

we can see SHOW DASHBOARD Info see below

AlwaysOn 2016 – New and Improved

AlwaysOn Availability Groups made its debut in SQL Server 2012 as a new feature that enhanced the existing technologies of database mirroring and failover clustering to provide an option for high availability and disaster recovery. AlwaysOn gives you the ability to scale out several secondary Servers (replicas) for high availability or disaster recovery purposes. The secondaries are usable for read-only operations (like reporting) and maintenance tasks (like backups), and AlwaysOn provides for zero data loss protection and automatic page repair.

Since the AlwaysOn 2012 release, there have been several enhancements that have improved manageability, scalability, and availability. This article will discuss some of these improvements and why they’re important.

Some of the new features and enhancements for AlwaysOn 2016 are:

More failover targets
Better log transport performance
Load balancing for readable secondaries
DTC support
Database-level health monitoring
Group Managed Service Account (gMSA) support
Basic Availability Groups
BI enhancements
Non-domain replicas
Encrypted database support
SSIS catalog support

More Failover Targets

In AlwaysOn 2012 and 2014, you were allowed a maximum of two replicas to designate for automatic failover. AlwaysOn 2016 allows three replicas for automatic failover. You must have synchronous data replication and automatic failover set between the primary and the secondaries.

Automatic failover is generally used to support high availability, and because of synchronous data flow there is near zero data loss in the event of failover.

Better Log Transport Performance

The increased use of solid-state disks (SSDs) has provided users with high-speed hardware, enabling very fast throughput. This however, can be overwhelming to a system trying to write transactions to a secondary server. Because of this, Microsoft has revamped the data synchronization process for AlwaysOn, streamlining the pipeline so that there is better throughput and also less stress on the CPU. Bottlenecks are most likely to occur during the Log Capture and Redosteps. Previously the log-capture and the redo steps used a single thread to process the logs, but now these steps use multiple threads and run in parallel, which greatly improves performance.

The steps of data replication are illustrated below.

Transaction Occurs –> Log Flush –> Log Capture –> Send –> Log Received –> Log Cached –> Log Hardened –> Acknowledgement Sent –> Redo

Load Balancing for Readable Secondaries

One of the great features of using AlwaysOn is the ability to use the secondary replicas for read only operations. Prior to AlwaysOn SQL2016, the listener would direct any read-only requests to the first available replica, even though you might have several secondary replicas available, and you might have preferred the read operations go to secondary #3 or #4, instead of #2. Now in SQL2016 the list of readable secondaries are presented to the listener in a round-robin fashion. By executing TSQL statement similar to the following, the workload will adjust in the event of failover:

— designate server with read-only access in Secondary status