AlwaysON Bug with Failure to Restore the Backup Performed on Secondary Server

SQLServerF1

AlwaysON Availability Groups are one of the most popular feature introduced with SQL Server 2012 with SP2. With introduction of every new release, comes lot of new features and functionality, but at the same time also comes lot of bugs or problems. Some of the issues found or raised or faced will be very basic, yet creates lot of issues. One of such issue with SQL Server 2012 AlwaysON is “Error 4360 when you restore the backup of secondary replica to another server in AlwaysOn Availability Groups“.  AlwaysON Availability Groups setup in SQL Server 2012 and when you perform log back up of a database from the secondary replica, and then try to restore the log backup file to another server after a restore of full/diff backup and in this situation, you may receive the below error message

Msg 4360, Level 16, State 1, Line 1
RESTORE LOG WITH CONTINUE_AFTER_ERROR was unsuccessful. Execution of the RESTORE command was aborted.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

This issue has been notified to Microsoft by various customers and raised as a bug. This issue has been addressed by Microsoft SQL Server Product team and a fix has been released for the same which is available with Cumulative Update 5 for SQL Server 2012 SP2. Once you apply this fix, you should be able to restore the log backups performed on AlwaysON Availability Group database on secondary server to some other server.

Although the issue can be fixed by applying CU5 for SQL Server 2012 SP2, it is advisable to apply latest available CU for SQL Server 2012 SP2, to avoid any other known issues or failures too. Each new cumulative update released for SQL Server will contain all the hotfixes and all the security fixes that were included with the previous cumulative update. We recommend that you download and install the latest cumulative updates for SQL Server:

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2012 SP2

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

WSFC Cluster Quorum Vote Configuration Not Recommended for AG

SQLServerF1

When you create an AlwaysOn availability group by using the New Availability Group Wizard in Microsoft SQL Server 2012, or when you are performing failover of the AlwaysON Availability Group (AG) every time you may receive a warning message as shows below

The current WSFC cluster quorum vote configuration is not recommended for this availability group.

The above error occurs due to various reasons. and one of which is missing windows OS patch KB 2494036 on all cluster nodes part of the AlwaysON Availability Groups. Verify and apply the mentioned patch and reboot the nodes and the issue should be resolved. However if the issue still persists even after this patch is applied on all the cluster nodes then apply latest Service Pack or Cumulative Update available for SQL Server 2012 or SQL Server 2014.

Starting with Windows Server 2008, failover clustering introduced node based voting where Windows Server Failover Clustering (WSFC) uses a majority of votes to establish a quorum for determining cluster membership. Votes are assigned to nodes in the cluster or to a witness that is either a disk or a file share witness. We can use the Configure Cluster Quorum Wizard to configure the clusters quorum model. When you configure a Node Majority, Node and Disk Majority, or Node and File Share Majority quorum model, all nodes in the cluster are each assigned one vote. However there are limitations to the votes for AlwaysON Availability Groups which involve SQL Server Clustered instance. If a  primary or a secondary replica is hosted by a Windows cluster then this replica is configured for an automatic failover without a vote. If this is by design then you may ignore the warning as it still allows the initial setup of AlwaysON Availability Groups and subsequent manual failover of AlwaysON Availability Groups, but the warning will appear each time you try to perform a manual failover.

Also it is possible to configure the votes for each node and customized as per the business requirement. This functionality helps with multi-site clusters where you may want one site to have more number of votes than other sites in a disaster recovery data center. By default all nodes in the cluster have one vote but administrators can change the vote functionality in the quorum model by configuring a particular node to have 0 votes.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Effective Use of Secondary Replicas of AlwaysON Availability Groups

SQLServerF1

SQL Server AlwaysON Availability groups were introduced in SQL Server 2012 onwards which provides High Availability and Disaster Recovery solutions for most of the organizations. In AlwaysON Availability Groups at any given point of time there is one server/replica which acts as primary replica which serves both Read/Write operations and there can be one or more secondary replicas which can allow Read_Only operations if required. Along with read_only options there are few maintenance tasks like backups and Integrity checks which can be offloaded to the secondary replicas thus reducing some load of the primary replica.

Below are some of the benefits and effective way of using secondary replica.

– Secondary replicas support read_only workload which will make effective use of Hardware resources on the secondary replica which improves the return on investment on secondary server, unlike Windows failover cluster where the secondary nodes are just passive.

– ReadOnly work load like Reporting can be offloaded to one or more secondary replicas thus reducing the load and locking on in primary server which enhances the performance.

– Read-only workloads use row versioning to remove blocking contention on the secondary databases which will enhance the performance of readonly workload compared to running both read/write and readonly workloads on same SQL Server instance.

– Backups can be performed on secondary replica thus offloading some load from primary replica. Copy_Only Full, File or Filegroup can be performed on secondary replica, however regular transaction log backups can be performed on any of the secondary replica and SQL Server will maintain consistent backup chain across all replicas.

– Integrity checks like DBCC CheckDB can now be run on Secondary replicas. So, you can perform daily checkdbs on secondary replica and can perform weekly checkdb on primary replica over weekends.

– Secondary replica creates and maintains temporary statistics for secondary databases in tempdb, but these temporary statistics can only be created by SQL Server based on the queries being run on the secondary server. SQL Server detects when permanent statistics on a secondary database are stale. But changes cannot be made to the permanent statistics except through changes on the primary database. For query optimization, SQL Server creates temporary statistics for disk-based tables on the secondary database and uses these statistics instead of the stale permanent statistics. When the permanent statistics are updated on the primary database, they are automatically persisted to the secondary database. Then SQL Server uses the updated permanent statistics, which are more current than the temporary statistics.

– Read-only workloads for memory-optimized durable tables access the data in exactly the same way it is accessed on the primary database, by using native stored procedures or SQL Interoperability with the same transaction isolation level limitations. Reporting workload or read-only queries which generally run on the primary replica can now be run on the secondary replica without requiring any changes. Similarly, a reporting workload or read-only queries running on a secondary replica can be run on the primary replica without requiring any changes.

This is applicable on below versions of SQL Server

SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

AlwaysON Availability Group Does Not Failover to second Node Upon Stopping SQL Server Services

SQLServerF1

When SQL Server AlwaysON Availability Groups are setup in SQL Server 2012 or SQL Server 2014, DBAs perform various testing by shutting down one node and verifying that the Availability Group fails-over to the second node and another approach tried is manually stopping the SQL Server services and expect the Availability Group to failover to the second node, but sometimes you may see that the failover does not occur, instead the AlwaysON AG and IP address resources get into failed state. Manually failing over the AlwaysON AG to the second node however successfully brings the resources and Availability Group online and accessible.

This issue generally happens if the failover has been attempted multiple times which exceeds maximum number of failures supported for the availability group during a given time period. This is not specific to AlwaysON rather can also happens with SQL Server Failover Cluster instances as well.

The Default value for the maximum number of failures during this period is n-1, where n is the number of WSFC nodes and the default time period is six hours. If an availability group exceeds its WSFC failure threshold, the WSFC cluster will not attempt an automatic failover for the availability group. Furthermore, the WSFC resource group of the availability group remains in a failed state until either the cluster administrator manually brings the failed resource group online or the database administrator performs a manual failover of the availability group. Ref

With default values, Automatic failover only happens once during six hours. So, the solution to this problem is to change the failover-threshold values for a given availability group. We can use the WSFC Failover Manager Console and increase “Maximum Failures in the specified period” to higher value based on our requirement and reduce the “Period (Hours)”. For example, we can set the “Maximum Failures in the specified period” to a value like 10 and “Period (Hours)” to 1 hour, which means in one hour AlwaysOn Availability Group will be tried to failover for 10 times. If it fails for 10 times, then it will be left in failed state and DBA has to investigate based on the cause of the failure and fix it after which it can brought online manually. The values are to be set based on your requirement and may vary in different environments.

This is applicable on below versions of SQL Server

SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

SQL Server AlwaysON Availability Group Common Errors or Failures

SQLServerF1

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

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Top New SQL Server 2012 Features

SQL Server 2012 was released by Microsoft on April 1st 2012. There are many new features introduced in SQL Server 2012 and some existing features are enhanced as well. One of the most popular feature and most talked feature that comes in SQL Server 2012 is AlwaysON Availability Groups.

Below are some of the top new features in SQL Server 2012.

AlwaysOn Availability Groups – SQL Server 2012 AlwaysON Availability Groups is a revolutionary feature which replaces many other features such Database Mirroring, Logshipping and up to an extent Replication. AlwaysON also known as HADRON, which provides High Availability and Disaster Recovery solution for critical databases. AlwaysOn Availability Groups is a container which consists of one or more databases which can together failover as an unit. a set of replicas(servers) host the primary and secondary AlwaysON Availability Group databases. AlwaysON configuration Availability Group has One Primary Replica and up to 4 Secondary replicas including one Synchronous and 2 asynchronous replicas. We can perform Read/Write operations on Primary replica and can use Secondary replicas for Read_Only work loads, thus offloading primary server. We can have multiple Available Groups created between same server and failover happens at Availability Group level. Failing over an availability Group brings all databases part of that group ONLINE on the secondary replica. We can also offload backups to be performed on the secondary replicas. CheckDB can also be offloaded to the secondary replicas. It is also simple to implement AlwaysON Availability Groups.

AlwaysON SQL Server Failover Clusters – SQL Server AlwaysON Failover Clusters allows multi-subnet failover clustering where cluster nodes can be spread at geographically at different locations and data is moved to other node through SAN level replication. This provides both High Availability and Disaster Recovery. Another enhancement include flexible failover policy, where in we can choose the condition on which a failover should occur.

Contained Databases – SQL Server 2012 provides partial containment. In previous versions of SQL Server, moving a database from one server to another server also involved moving associated logins and other objects. Starting with SQL Server 2012 Contained Databases, users are associated with the database itself, thus no more dependent on the SQL Server instance. Moving contained database to new server does not require any additional actions of creating logins, or fixing orphan users. Also, previously there were issues related to collation where SQL Server instance is of different collation and databases are of different collation. SQL Server 2012 contained databases solves this problem where tempdb now creates objects related to contained database under same collation of the database.

ColumnStore Indexes – Microsoft SQL Server team brought the concept of column based indexes from VertiPaq. Columnstore indexes in the SQL Server 2012 Database Engine is used to significantly speed-up the processing time of some common data warehousing queries. In traditional clustered and non-clustered indexes which data is stored row wise in pages, where as in SQL Server 2012 ColumnStore Indexes, data is grouped and stored column-wise, so each column can be accessed independent of other columns.

Enhancements to ONLINE rebuild index operations – Starting SQL Server 2012, we can rebuild indexes ONLINE for BLOB indexes, which have data types like nvarchar(max), varchar(max), varbinary(max). This was not possible in previous SQL Server versions.

Database Recovery Adviser – In previous versions of SQL Server, DBA’s had to perform manual restores in order to recover a database from a failure for which Database administrators had to plan and restore a set of backup files in a logically correct order. New Database Recovery Advisor facilitates preparing the restore plans which implement optimal and correct restore sequences, thus reducing the overhead and any manual mistakes. Also with AlwaysON Availability Groups, backups could be performed on secondary replicas as well, in such cases Database Recovery Advisor will be helpful in preparing the restore sequence.

Reduced downtime for Application Upgrades – Adding new columns with default constraint is now meta data only operation, thus significantly reducing amount of time for changes.

New FILETABLE feature has been introduced in SQL Server 2012, which is built on top of FILESTREAM technology. With FILETABLES, we can now store the files and documents in special tables in SQL Server, but access them from Windows applications as if they were stored in the file system, without making any changes to the Windows applications.

Product Update is another new feature introduced in SQL Server 2012 Setup where, setup integrates the latest available product updates with the main product during the installation time, so that the main version and all applicable updates are installed at the same time, thus reducing additional overhead and downtime later.

New Startup Parameters has been added to the SQL Server 2012 Configuration manager, so it makes it simple and manageable for changing paths for master database files or to add any startup trace flags.

Starting with SQL Server 2012 support for partitions is increased to 15,000 by default, which in previous versions was limited to 1,000 by default.

Starting with SQL Server 2012, FileStream FileGroups can contain multiple files and can be on different drives, which improves I/O performance by spreading the I/O load.

There are some changes in Licensing of SQL Server 2012 Enterprise Edition, where two types of Enterprise Edition Licenses are sold based on Server/Client Access License (CAL) or Per Core licensing. Also Data Center Edition has been removed in SQL Server 2012 and Business Intelligence Edition has been introduced.

Starting with SQL Server 2012, we can install it on Windows Server 2012 Core, which basically does not have any GUI.

– SQL Server Business Intelligence Development Studio has been redesigned and modified into SQL Server Data Tools (SSDT).

There are many other features and enhancements made in SQL Server 2012 as well, refer MSDN for more information.

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Planning and Implementation of Maintenance of Servers with Least Amount of Downtime with SQL Server 2012 AlwaysON Availability Groups

There was a request where we had to perform maintenance on servers which were hosting Primary and Secondary AlwaysON Availability Group Databases. Initially, when the Servers were setup, the hardware was not planned for best performance, so it was decided that we will replace the servers with complete new servers with new and better Hardware. Below is the environment setup.

Servers Server01 and Server02 are running Windows Server 2012 Operating System with One Default Standalone Instance on each server installed with SQL Server 2012 Enterprise Edition. AlwaysON Availability Group with Listener name as “PROD_LSTNR” has been setup between Server01 as Primary AlwaysON replica and Server02 as Secondary replica and this AG has couple of user databases. There is another AlwaysON Availability Group with Listener name as “PROD_LSTNR_RPT” has been setup between Server02 as Primary AlwaysON replica and Server01 as Secondary replica and this AG has ReportServer and ReportServerTempDB databases.

Now, we had to replace both the servers with brand new servers with new and better hardware. This needs to be accomplished with minimum downtime for end users and least amount of manual work to be performed by DBA or SysAdmins.

Below are the High Level steps which we identified and later successfully implemented with minimum downtime and least amount of manual work.

Prepare for Maintenance of AlwaysON Availability Group Secondary Replica
– Let the AlwaysON Availability Group “PROD_LSTNR” run on the Primary Replica Server01.
– Make a list of Report Server Subscription jobs which are currently enabled on Server02. Save it for future reference. If there are any other user created jobs related to Report Server, script and create them on Server01 and keep them disabled for now.
– Backup ReportServer and ReportServerTempDB databases and system databases on secondary replica Server02
– Backup ReportServer Encryption Keys on Server02.
– Backup ReportServer Encryption Keys on Server01.
– Synchronize all logins and resolve any orphan users on Primary Server Server01
– Failover AlwaysON Availability Group “PROD_LSTNR_RPT” to Replica Server01. This causes very short downtime for reporting services.
– Restore the Encryption Keys on Server01 with the backup taken from Server02.
– Remove Server02 from Scale-Out deployment tab using Report Service Configuration Manager on Server01.
– Stop Reporting Services on new secondary server Server02.
– Restart SQL Server Agent on Server01. This will create or enable Report Server Subscription jobs on Server01 automatically. Enable any user created report server related jobs on Server01.
– Test the reports to make sure they are working. If you have configured reports data sources to use AlwaysON Secondary databases read_Only databases, then make sure that AlwaysON Availability Group settings allow Primary replica to allow both Read/Write connections.
– Turn off SQL Services on Secondary replica Server02.

Steps for System Administrators:
– This is critical step and to be done by System Administrators. Take down Secondary replica Server02 and copy the consistent server snapshot of Secondary server Server02 in to new server with better hardware.
– Rename Server02 to Server02_Old and rename new server to Server02 and change the IP addresses same way.
– Add new secondary server Server02 with powerful hardware back to the network and domain.
– Now, you should be able to see that AlwaysON Availability Groups start synchronizing and after some time will get fully synchronized.
– Now that the secondary replica maintenance is complete, next we need to prepare the primary server for the maintenance to replace the server with new server with powerful hardware.

Prepare for Maintenance of AlwaysON Availability Group Primary Replica
– Backup all user and system databases on Primary replica Server01.
– Backup Encryption Keys on primary and secondary server Server01 and Server02
– Make a list of all application or user created jobs which are enabled on primary replica Server01 and disable the jobs on primary server Server01.
– Failover AlwaysON Availability Group “PROD_LSTNR” to Secondary Server Server02 and now this will become new primary server.
– Enable all application or user created jobs as we noted earlier.
– Test the Application.
– Failover AlwaysON Availability Group “PROD_LSTNR_RPT” to Secondary Server Server02 and now this will become new primary server.
– Stop the reporting Services on new secondary server Server01.
– Restart SQL Server Agent Service on new primary server server02. This will create or enable report server subscription jobs on new primary replica Server02.
– Restore the Encryption keys on Server02 using the backup taken from Server01.
– From Report Server Configuration Manager, remove Server01 from Scale-Out Deployment.
– Test the reports to make sure they are working. If you have configured reports data sources to use AlwaysON Secondary databases read_Only databases, then make sure that AlwaysON Availability Group settings allow Primary replica to allow both Read/Write connections.

Steps for System Administrators:
– This is critical step and to be done by System Administrators. Take down new Secondary replica Server01 and copy the consistent server snapshot of Secondary server Server01 in to new server with better hardware.
– Rename Server01 to Server01_Old and rename new server to Server01 and change the IP addresses same way.
– Add new secondary server Server01 with powerful hardware back to the network and domain.
– Now, you should be able to see that AlwaysON Availability Groups start synchronizing and after some time will get fully synchronized.

– Now that the new secondary replica Server01 maintenance is complete, next we can failover the AlwaysON Availability Group “PROD_LSTNR” to Server01 where it was running as primary before, enable all the user created jobs on Server01 and disable them on Server02. Let the AlwaysON Availability Group “PROD_LSTNR_RPT” run on Server02 and use the read_only copy and offload the reporting from primary server.
– Test the application.

We have successfully followed these steps in our environment, but you should test this solution thoroughly on your lab or test environment before proceeding with the production changes. This post is provided “AS IS” with no warranties or guarantees.

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings, SQL Server Logshipping on Databases which are part of AlwaysON Availability Groups (AG)

 

Configuring SQL Server 2012 Logshipping on AlwaysON Availability Group Databases with Successful Failover of AG and Logshipping

Configuring SQL Server Logshipping on Databases which are part of AlwaysON Availability Groups (AG) is supported in SQL Server 2012 or higher. We can failover AlwaysON Availability Groups between Primary and Secondary Server and also move or enable the Logshipping from new Primary Server to DR server. Below is the environment setup.

Three servers Server01, Server02, Server03 with Windows Server 2008 R2 as Operating System. Each server has one standalone default instance of SQL Server 2012. AlwaysON Availability Group (AG) and Listener “Prod_LSTNR” has been setup between Server01 and Server02.

Now we can setup Logshipping from Server01 to Server03 just the same way how we setup logshipping between any two server databases as mentioned below.

How to Setup Logshipping between two servers?
– Connect to SQL Server instance from SSMS using instance name as Server01.
– On Server01, right click the database you want to use as your primary database in the log shipping configuration, and go to its Properties and go to last tab “Transaction Log Shipping”.
– Select the “Enable this as a primary database in a log shipping configuration” check box, which will now enable the logshipping configuration options to choose backup, copy and restore settings.
– Under “Transaction log backups”, click on Backup Settings.
– In the Network path to the backup folder box, type the network path to the backup share to use it for placing the transaction log backups.
– If this backup share is located on the primary server itself then type the local path to the backup folder in the “If the backup folder is located on the primary server” text box. If the backup folder is not on the primary server, you can leave this box empty.
– Make sure that the SQL Server service account on the primary server has full permissions on the backup share.
– Configure the Delete files older than and Alert, if no backup occurs within parameters.
– You can choose to customize the schedule for log backup and alerts.
– Starting SQL Server 2008, backup compression is supported, so you may choose to enabled it or set it to use Instance wide backup compression configuration.
– Now, under Secondary server instances and databases, click Add.
– Click on Connect and then connect to the SQL Server instance Server03 that we want to use as the secondary server for logshipping.
– In the Secondary Database box, either choose a database from the list or just type the name of the database you want to create.
– On the Initialize Secondary database tab, choose the option that you want to use to initialize the secondary database.
– Make sure that you choose correct option above and appropriately prepare the database on secondary server.
– Now go to Copy Files tab, and in the “Destination folder for copied files” text box, put the path of the folder to which the transaction logs backups should get copied. This folder is often located on the secondary server or common share. Make sure that SQL Server service account on secondary has full permissions to this path.
– You may optionally choose to change the copy and alert thresholds as per your SLA’s
– Now move to Restore tab, under “Database state when restoring backups”, choose the No recovery mode or Standby mode option. If you are choosing Standby mode option, then also select that you want to disconnect users from the secondary database while the restore operation is performed.
– If you want to delay the restore process on the secondary server, choose a delay time under Delay restoring backups at least.
– You may optionally alter the restore and alert thresholds.
– If you want, you may also add a third server as Monitor server instance to monitor this log shipping configuration. You need to decide whether you want to use monitor server or not, because, if you want to add the monitor server later, we need to remove logshipping entirely and reconfigure it with monitor server.
– If you choose to configure monitor server as well, then click on Connect and connect to the SQL Server instance to use as the logshipping monitor server.
– Under Monitor connections, choose the connection method to be used by the backup, copy, and restore jobs to connect to the monitor server. Under History retention, choose the length of time you want to retain a history of log shipping.
– On the Database Properties dialog box, logshipping tab, click OK to begin the configuration process.
– Verify and make sure that logshipping log backup job is running successfully on Primary server Server01 and is creating the backup file on the backup share we provided to place the log backup files.
– Verify and make sure that logshipping copy job is running successfully on Secondary Server Server03 and is copying the backup file from the backup share to path specified during copy settings for secondary server.
– Verify and make sure that logshipping restore job is running successfully on Secondary Server Server03 and is restoring the backup files.
– Check Logshipping report from SSMS on Server01 and Server03, by right clicking on SQL Server Instance -> Reports -> Standard Reports -> Transaction Log Shipping Status.

Now we have successfully setup logshipping from Server01 to Server03, Next thing is to failover AlwaysON Availability Group “PROD_LSTNR” from Server01 to Server02, after which we also want to move the logshipping to happen from Server02 to Server03 and disable logshipping from Server01 to Server03.

Disable the logshipping from Server01 to Server03 by following below steps
Connect to SQL Server instance using SSMS with instance name as Server01 and Server03
Disable logshipping log backup job on Server01.
Disable logshipping copy job on Server03 related to Server01 as primary.
Disable logshipping restore job on Server03 related to Server01 as primary.

Perform AlwaysON Availability Group “PROD_LSTNR” failover from Server01 to Server02. Verify to make sure databases are synchronized and no errors related to AlwaysON.

Now configure logshipping from Server02 to Server03
– Connect to SSMS using SQL Server instance name as Server02.
– Configure logshipping from Server02 to Server03 using steps mentioned above under section “How to Setup Logshipping between two servers?”.
– Make sure logshipping is working fine by checking new backup job on Server02 and new copy and restore jobs on Server03 which are related to Server02 as primary.

From now onwards, you can failover AlwaysON Availability Group from Server01 to Server02 or vice-versa and at same time switch the logshipping to be performed from Primary AlwaysON AG database to Server03, by disabling the logshipping jobs related to AlwaysON AG secondary server.

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings, SQL Server Logshipping on Databases which are part of AlwaysON Availability Groups (AG)

 
1 2