Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Sunday 6 August 2017

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

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;
GO
RECONFIGURE;
GO

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.
Error during configuring AlwaysON
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.
Error during configuring AlwaysON
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.
AlwaysON dashboard
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.
AlwaysON dashboard error
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.
Full Backup
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;
Tlog backup
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
restore on secondary replica
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..."
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.
AG dashboard report
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 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
 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.
Blog_20160818_1
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
Blog_20160818_2
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
ALTER AVAILABILITY GROUP AGSQL2016
MODIFY REPLICA ON N’SQLSRVTST1′
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

ALTER AVAILABILITY GROUP AGSQL2016
MODIFY REPLICA ON N’SQLSRVTST2′
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

ALTER AVAILABILITY GROUP AGSQL2016
MODIFY REPLICA ON N’SQLSRVTST3′
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

— provide read-only routing URL
ALTER AVAILABILITY GROUP AGSQL2016
MODIFY REPLICA ON N’SQLSRVTST1′
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N’TCP://SQLSRVTST1.domain.com:1433′));

ALTER AVAILABILITY GROUP AGSQL2016
MODIFY REPLICA ON N’SQLSRVTST2
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N’TCP://SQLSRVTST2.domain.com:1433′));

ALTER AVAILABILITY GROUP AGSQL2016
MODIFY REPLICA ON N’SQLSRVTST3′
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N’TCP://SQLSRVTST3.domain.com:1433′));

— designate priority of read-only routing lists for each server in primary status
ALTER AVAILABILITY GROUP AGSQL2016
MODIFY REPLICA ON N’SQLSRVTST1′
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(‘SQLSRVTST3’,‘SQLSRVTST2’,‘SQLSRVTST1’)));

ALTER AVAILABILITY GROUP AGSQL2016
MODIFY REPLICA ON N’SQLSRVTST2′
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(‘SQLSRVTST1’,‘SQLSRVTST3’,‘SQLSRVTST2’)));
GO

ALTER AVAILABILITY GROUP AGSQL2016
MODIFY REPLICA ON N’SQLSRVTST3′
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(‘SQLSRVTST2’,‘SQLSRVTST1’,‘SQLSRVTST3’)));
GO

In addition to configuring your availability group routing lists, you must also ensure that the client’s application connection string use an application intent of read-only in the connection string when connecting to the AG listener. If this is not set in the client application connection string, the connection will automatically be directed to the primary replica. Following is an example of a connection string:
Server=tcp:AGListener,1433; Database=AdventureWorks;IntegratedSecurity=SSPI; ApplicationIntent=ReadOnly
Also it’s best not to mix synchronous and asynchronous replicas in the same load balance group.
DTC Support
*Only available with Windows Server 2016 or Windows Server 2012 R2 with update KB3090973
Distributed Transaction Coordinator (DTC) is necessary if your client application needs to perform transactions across multiple instances.   DTC is part of the operating system, and ensures consistency when your database engine makes multi-server transactions. Applications can use DTC when they connect to the database engine, or can be started through TSQL by using the BEGIN DISTRIBUTED TRANSACTION command.
USE AdventureWorks2012;
GO 
BEGIN DISTRIBUTED TRANSACTION;
— your tsql statement here
DELETE FROM AdventureWorks2012.HumanResources.JobCandidate
WHERE JobCandidateID = 13;
GO 
COMMIT TRANSACTION;
GO

Not only can your application perform transactions between multiple SQL Server instances, but also between other DTC compliant servers such as WebSphere or Oracle.
DTC is not supported in AlwaysOn 2014 and earlier. You cannot add DTC support to an already existing AlwaysOn availability group. For complete information click here https://msdn.microsoft.com/en-us/library/mt748186.aspx .
To implement this in AlwaysOn 2016, Availability Groups must be created with the CREATE AVAILABILITY GROUP command and the WITH DTC_SUPPORT = PER_DB clause.
CREATE AVAILABILITY GROUP AGSQL2016
WITH (DTC_SUPPORT = PER_DB)
FOR DATABASE [Database1, Database2, Database3]
REPLICA ON
‘SQLSRVTST1’ WITH — substitute node name
(
ENDPOINT_URL = ‘TCP://SQLSRVTST1.<domain>:7022’,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = AUTOMATIC
),
‘SQLSRVTST2’ WITH — substitute node name
(
ENDPOINT_URL = ‘TCP://SQLSRVTST2.<domain>:7022’,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = AUTOMATIC
);
GO

The clauses in the TSQL script above are only a partial list of available options, just for demonstration purposes. For more information on creating availability groups click here https://msdn.microsoft.com/en-us/library/ff878399.aspx .

 Database-Level Health Monitoring

In earlier versions of AlwaysOn (SQL 2012 & 2014), failover would occur if there was a problem with the health of the instance. If one of your databases was in trouble however, the AlwaysOn group would not fail over as long as the instance was okay. So if you had a database that was offline, suspect or corrupt, no failover would occur. In AlwaysOn 2016, failover will occur not only if the instance is in trouble, but also if one or more of your databases is in trouble. This is not a default setting however. You have to specify this when creating your AlwaysOn group, by selecting the Database Level Health Detection checkbox in the setup wizard.

Blog_20160818_3

You can also adjust the setting for what triggers a database failover, by setting the property value in the Failover Condition Level properties. You can increase or reduce the values from the default level if necessary. For more information click here https://msdn.microsoft.com/en-us/library/ff878667.aspx 

Group Managed Service Account (gMSA) Support

In SQL Server 2012, Microsoft added the gMSA enhancement so that service account passwords can be more easily managed. You can now create a single service account for your SQL Server instances, manage the password in Active Directory, and also delegate permissions to each of your servers. This feature can be useful for AlwaysOn groups because passwords and permissions to access certain resources, like shared files, can be managed for one account instead of each instance individually. Using a gMSA is also more secure than using a regular domain user account to manage services in your AG.
Basic Availability Groups

AlwaysOn Basic Availability Groups (BAG) are available with SQL Server 2016 Standard Edition. The functionality is generally the same as database mirroring (which has been deprecated). BAGs provide a failover environment for only one database, there can only be two replicas in the group, replication can be synchronous or asynchronous, and there is no read access and no backups on the secondary.   To create a Basic Availability Group, use the CREATE AVAILABILITY GROUP TSQL command and specify WITH BASIC.   Below is a partial script (again for demonstration purposes) of creating a Basic Availability Group. For more detailed information click here https://msdn.microsoft.com/en-us/library/ff878399.aspx .

CREATE AVAILABILITY GROUP BAGSQL2016
WITH (AUTOMATED_BACKUP_PREFERENCE = PRIMARY,
BASIC,
DB_FAILOVER = OFF,
DTC_SUPPORT = NONE)
FOR DATABASE [Database1, Database2, Database3]
REPLICA ON
‘SQLSRVTST1’ WITH — substitute node name
(
ENDPOINT_URL = ‘TCP://SQLSRVTST1.<domain>.com:5022’,
FAILOVER_MODE = AUTOMATIC,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
SECONDARY_ROLE (ALLOW_CONNECTIONS = NO)
),
‘SQLSRVTST2’ WITH — substitute node name
(
ENDPOINT_URL = ‘TCP://SQLSRVTST2.<domain>.com:5022’,
FAILOVER_MODE = AUTOMATIC,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
SECONDARY_ROLE (ALLOW_CONNECTIONS = NO)
)
GO

BI Enhancements

By using AlwaysOn Availability Groups, a data warehouse workload can be offloaded to a single readable secondary replica — or even across multiple readable secondary replicas — leaving the resources on the primary replica to efficiently support the mission-critical business process. Reporting and data analysis can be very resource intensive on a server, so offloading to a non-production server can enhance overall performance. Another added benefit is that because Microsoft revamped the data synchronization process, there is very low latency on the data feeding the data warehouse, so that near real-time analytics can be a reality.

Domain Replicas No Longer Necessary

*Only available with Windows Server 2016

Most companies operate in the context of a single Active Directory domain, but some organizations are set up with multiple domains and could benefit from spreading an AlwaysOn Group across domains so that multiple servers can host DR replicas. And then there are other organizations that operate without Active Directory domains at all.
With Windows Server 2016 operating system, WSFC does not require cluster nodes be in same domain, or in any domain at all (it can be in a workgroup). SQL Server 2016 is now able to deploy AlwaysOn Availability Groups in environments with:
  • All nodes in a single domain
  • Nodes in multiple domains with full trust
  • Nodes in multiple domains with no trust
  • Nodes in no domain at all
This gives improved flexibility by removing domain specific constraints for SQL clusters. For more info click here https://blogs.msdn.microsoft.com/clustering/2015/08/17/workgroup-and-multi-domain-clusters-in-windows-server-2016/

Encrypted Database Support

Encrypted databases were allowed in earlier versions of AlwaysOn, however they couldn’t be added using the New Availability Group wizard, and they could not be accessed if there was a failover to a secondary replica. In SQL Server 2016, it is possible to add an encrypted database via the wizard, and there is now access to the database in the event of failover. This is because during the creation of the Availability Group, the wizard executes sp_control_dbmasterkey_password for each replica, and consequently creates the credentials associated with the database master key for each instance. In the event of failover SQL will search for the correct credentials until it is able to decrypt the database master key.
There are some restrictions with adding an encrypted database to an AlwaysOn group. For more information on how to make an encrypted database eligible to add to an availability group, click here https://msdn.microsoft.com/en-us/library/hh510178.aspx .

SSIS Catalog Support

In SQL Server 2016, you can add your SSIS catalog (SSISDB) and its contents (projects, packages, etc.) to an AlwaysOn Availability Group in SQL2016 like any other database, in order to enhance high availability and disaster recovery.
There are some special prerequisites and configurations for adding the SSISDB to an AlwaysOn group. For more information click here https://msdn.microsoft.com/en-us/library/mt163864.aspx

Conclusion

Many new features and enhancements have been made to SQL Server 2016. This article discussed basic information about some of the enhancements to the AlwaysOn feature. The latest version of AlwaysOn Availability Groups 2016 has improved functionality, scalability, and manageability, and continues to be a robust enhancement for high availability and disaster recovery.


Ref:

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

http://sql-articles.com/articles/high-availability/steps-to-configure-sql-alwayson/

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

https://www.tintri.com/resources/whitepapers/sql-alwayson-availability-groups-tintri

https://blog.sqlrx.com/2015/04/23/prerequisites-for-installing-sql-server-alwayson/


https://sqlrx.wordpress.com/2015/04/30/steps-for-installing-sql-server-alwayson-availability-groups/

No comments:

Post a Comment