Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

SQL Server 2016 AlwaysOn with Automatic Seeding option

luisdarui
Advisor
Advisor
‎07-26-2017 4:26 PM
In my last blog I stated I wanted to implement the AlwaysOn Availability Groups with SQL Server 2016 and I would try the new automatic seeding option. I always found tedious to manually backup the database and restore on other replicas to finally join them in the Availability Group.

First of all, you should read Juergen's blog post on Running SAP on Microsoft Platform blog to understand the new features and what I'm talking about. If you are not familiar with SQL Server AlwaysOn Availability Groups, there is a blog series about implementing AlwaysOn with SAP NetWeaver applications. You can access the summary post from here.

My installation scenario

I have two identicals VMs (Dual core processor, 16GB RAM, 300GB disk).
SQL Server was already running on the first server as a default instance (MSSQLSERVER) and I just installed SQL Server 2016 over the secondary VM. SAP NetWeaver 7.5 was already installed and the VM1 had the ASCS/DI and the VM2 had only a DI.

The AlwaysOn configuration (in a nutshell!)

Since I had my system already working, I performed a few tasks to move this from a single database on an instance to a HA/DR with AlwaysOn. I will describe in a few steps, but if you are doing this for your very first time, I recommend fully reading the AlwaysOn blog series as well books Online.

  1. Installed the Windows Server Failover Cluster role on both VM1 and VM2.
    - This is a prerequisite, AlwaysOn uses WSFC.

  2. Created a Cluster, which I named VM12S
    - I didn't want to escape the naming of the servers.

  3. Enabled AlwaysOn Availability Groups in the "AlwaysOn High Availability" Tab.

  4. At the same time, I also changed the SQL Server services to start with a domain user in both nodes, through SQL Server Configuration Manager.
    - In my setup I don't use the Local System account, which avoid further steps in the AlwaysOn configuration - make sure you read this if you want to stick with Local System, I do not recommend.

  5. Restart the instances.

  6. At this point, I followed the instructions from Juergen's blog as it is.


A huge remark I make here is: you have to manually create the path to the database files across all replicas you will be seeding. That is right. SQL Server will not be creating the path directories. I don't know for sure why it doesn't.

At first I haven't read the official documentation, I just went through the blog post (huge mistake from my side), and I ended up having my automatic seeding failing with failure_code 15, failure_message: VDI Client failed (taken from sys.dm_hadr_physical_seeding_stats). ERRORLOG on the secondary replica was very clear and give a hand to understand the problem:

2017-01-06 20:03:22.11 spid49s Error: 911, Severity: 16, State: 1.
2017-01-06 20:03:22.11 spid49s Database 'AN5' does not exist. Make sure that the name is entered correctly.
2017-01-06 20:03:22.30 spid49s Error: 5133, Severity: 16, State: 1.
2017-01-06 20:03:22.30 spid49s Directory lookup for the file "C:\AN5\AN5DATA1.mdf" failed with the operating system error 2(The system cannot find the file specified.).
2017-01-06 20:03:22.30 spid49s Error: 3156, Severity: 16, State: 3.
2017-01-06 20:03:22.30 spid49s File 'AN5DATA1' cannot be restored to 'C:\AN5\AN5DATA1.mdf'. Use WITH MOVE to identify a valid location for the file.
2017-01-06 20:03:22.30 spid49s Error: 5133, Severity: 16, State: 1.
2017-01-06 20:03:22.30 spid49s Directory lookup for the file "C:\AN5\AN5DATA2.ndf" failed with the operating system error 2(The system cannot find the file specified.).
2017-01-06 20:03:22.30 spid49s Error: 3156, Severity: 16, State: 3.
2017-01-06 20:03:22.30 spid49s File 'AN5DATA2' cannot be restored to 'C:\AN5\AN5DATA2.ndf'. Use WITH MOVE to identify a valid location for the file.
2017-01-06 20:03:22.30 spid49s Error: 5133, Severity: 16, State: 1.
2017-01-06 20:03:22.30 spid49s Directory lookup for the file "C:\AN5\AN5DATA3.ndf" failed with the operating system error 2(The system cannot find the file specified.).
2017-01-06 20:03:22.30 spid49s Error: 3156, Severity: 16, State: 3.
2017-01-06 20:03:22.30 spid49s File 'AN5DATA3' cannot be restored to 'C:\AN5\AN5DATA3.ndf'. Use WITH MOVE to identify a valid location for the file.
2017-01-06 20:03:22.30 spid49s Error: 5133, Severity: 16, State: 1.
2017-01-06 20:03:22.30 spid49s Directory lookup for the file "C:\AN5\AN5DATA4.ndf" failed with the operating system error 2(The system cannot find the file specified.).
2017-01-06 20:03:22.30 spid49s Error: 3156, Severity: 16, State: 3.
2017-01-06 20:03:22.30 spid49s File 'AN5DATA4' cannot be restored to 'C:\AN5\AN5DATA4.ndf'. Use WITH MOVE to identify a valid location for the file.
2017-01-06 20:03:22.31 spid49s Error: 5133, Severity: 16, State: 1.
2017-01-06 20:03:22.31 spid49s Directory lookup for the file "C:\AN5\AN5LOG1.ldf" failed with the operating system error 2(The system cannot find the file specified.).
2017-01-06 20:03:22.31 spid49s Error: 3156, Severity: 16, State: 3.
2017-01-06 20:03:22.31 spid49s File 'AN5LOG1' cannot be restored to 'C:\AN5\AN5LOG1.ldf'. Use WITH MOVE to identify a valid location for the file.
2017-01-06 20:03:22.31 spid49s Error: 3119, Severity: 16, State: 1.
2017-01-06 20:03:22.31 spid49s Problems were identified while planning for the RESTORE statement. Previous messages provide details.
2017-01-06 20:03:22.31 spid49s Error: 3013, Severity: 16, State: 1.
2017-01-06 20:03:22.31 spid49s RESTORE DATABASE is terminating abnormally.
2017-01-06 20:03:22.31 spid49s Automatic seeding of availability database 'AN5' in availability group 'Batterydb' failed with a transient error. The operation will be retried.
-----

After creating the directory c:\AN5 it worked. Really fast and would have been straightforward if I have read Books Online first.

It can represent some sort of complication if you have more than 16 Data files on your database and each of them is sitting on a different folder, but most of the times these folders are actually mount points, so it is very likely they will be there already, just make sure that all replicas have the same path structure before deciding to seed your database.

The rest of the AlwaysOn configuration is not new, you will have to create the users and replicate the jobs to the secondary replicas, as well update the Environment  variables as well system profiles as described on the AlwaysOn blog series. I won't go on as this is very well explained already with every possible remark. You can certainly go on with the configuration.

The automatic seeding option can be extremely useful in many scenarios, but just think about seeding a database over 5 secondary replicas, automatically, now for 5 different installations, you are now restoring 25 backups to start a synchronization. Yes, seeding is tedious, and boring. Now, a simple task.

Next?

I think this will be the last of my blogs about SAP on SQL Server. After 10 years working with SQL Server (the last 3 at SAP), as of June 2017, I started working on SAP HANA Platform in Product Support.
3 Comments
Labels in this area
Popular Blog Posts