Skip to Content

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.

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Joshua Lacroix

    Luis, I’ve read many of your blogs and solutions to problems over the years, I’d like to ask you what do you personally think of the evolution of the SAP products and Microsoft SQL? More specifically, when Netweaver suite is eventually retired in 2025 or whenever and S4 on Hana is the only kid on the block…where do you think Microsoft stands on that? Allot of us use SQL, but SAP is going towards a HANA only platform…is there still room in the future for SQL in SAP?? I’m not a reporter, just a regular Basis looking for you opinion

    (1) 
    1. Luis Darui Post author

      Hi Joshua,

      SAP hasn’t announced anything beyond 2025 as far as I know (the last announcement was in 2014 in this blog), so my opinion can be biased or even mislead any reader from this point. And S/4HANA also is based on SAP NetWeaver, so I don’t know how things will be later on.

      I took a position to work with HANA because that was something I had planned long time ago. I honestly don’t know what will happen after 2025 with SAP NetWeaver and SQL Server. It is possible that SAP release a new suite? We are speaking about 8 years from now. It is a long time, if you look back 8 years ago and what we had SAP NetWeaver 7.3 released (I think around October), SQL Server 2008 R2 wasn’t released yet (only in 2010) and SAP HANA wasn’t shipped yet.

      Have you considered that maybe in 2025 you will have support with only A.I. instead of a person?

      (0) 
      1. Joshua Lacroix

        LOL, I like the AI! I agree its a long time away, I guess SAP will watch the S4/HANA take up by its customers. In the end, customers pay their bills…see what happens

        (0) 

Leave a Reply