SQL Server AlwaysON Database Refresh

[manish_singh13]

Overview

SQL Server AlwaysOn is very good functionality introduced by Microsoft as you can achieve high availability solution with actual copy of database with real time synch mode. Now as part of maintenance we might have to do database refresh (as part of regular support or if you build new system and want to restore database from existing system). I have tried to consolidate all points & steps included in doing such kind of activity. Hope this will help fellow group members

Steps To Perform

1. Take Full DB Backup Source & Target.
2. Stop Target System SAP Application & Database.
3. Backup SAP System Profile Directory.
4. Logon to Primary Node of Database and open SQL Management Console as Administrator.
   1. Delete Database From Availability Group (Always On High Availability à Availability Groupà <DB_CLUSTER> (Primary)àAvailability Databasesà Database)
   2. Delete Listener Services
   3. Bring your Database Offline on Primary Node.
5. Logon to Secondary (Replica) Node of Database and open SQL Management Console as Administrator
   1. Bring your Database Offline on Secondary Node.
6. Delete Data and Log Files from both Nodes.
7. Restore Source Database from Disk or Tape on Primary Database Node.
8. Run SWPM and select option System Copyà High Availability System à Database Instance as per your SAP Release/Version
9. Need to select “Homogeneous System Copy (MS SQL Server Specific: Detach/Attach or Backup/Restore) option
10. After SAP Installer runs successfully, login to SQL Management Console and delete tables (e.g. TLOCK, DBSNP, DDLOG etc) as per SAP System Copy Guide.
11. Initiate Backup from Primary Node and transfer this backup to Secondary Node.
12. Login to SQL Management Console on Secondary Node and check if Availability group or Listener exists (If entry still exists then simply delete it). Database (<SID>) can also be deleted from Databases as it will be restored later.
13. Restore Backup taken from Primary Node with option “RESTORE WITH NORECOVERY”
14. Check & Set the security settings for Always On Group.
15. Create Availability Group from Primary Node.
16. Add both nodes in Availability Group as Primary & Secondary during Availability Group Creation.
17. Check Dashboard for the Availability Group Status if there is any error or not.
18. Open Failover Cluster Manager
19. Create Listener
20. Check ILB IP Address and Port 59999 is properly assigned to Listener (* This is relative to Microsoft Azure Cloud Cluster)
21. Check database status in both Nodes (It should be synchronized).
22. Safer side execute sp_help_revlogin scripts on Primary and Secondary nodes (Similar to when initial installation was done).
23. Compare and fix the SAP profile (DEFAULT) parameters from copied profile directory.
24. Start SAP and perform initial check, apply license
25. Perform post database refresh steps (As per SAP System Copy Guide).

Issues & Resolution:

Issues & Resolution: Below are the few issues I had faced/identified with their resolution while doing SQL DB RefreshIssue: During SWPM System Copy process if you face issue related to Server Princial <Domain>\<SIDADM> already exists.Resolution: This might be related to hard endpoints already existed due to prior database configuration. This can be checked and deleted as it will get created automatically later on. Below are the commands to check and fix:

• select suser_sid ('<Domain>\<SIDADM>')
• select * from sys.server_principals sp where sid=<Result from first query>
• SELECT p.name, e.* FROM sys.endpoints e inner join sys.server_principals p on e.principal_id = p.principal_id
• DROP ENDPOINT [Hadr_endpoint] (Run this query if you see <Domain>\<SIDADM> hard point entry in previous query)

Issue: While creating Availability Group you get error that this group name already exists.Resolution: If you want to user same Availability Group Name then open a query window and give below query to delete the entry:

• Drop availability group ‘<Group Name>’

After this you can recreate the Availability Group with same name.

Issue: While adding Secondary Node to Availability Group post-refresh if you get error that “Mirror Database “<SID>” has insufficient transaction log data to preserve the log backup chain of the principal database.Resolution: Take log backup from Primary Node and Restore it on Secondary Node using below command:

• Restore Log “<SID>” from disk = ‘Path to log backup file’ with NORECOVERY
• Continue with Availability Group Creation it will be successful

Related Content

Related Documents

https://msdn.microsoft.com/en-us/library/hh213326.aspx