Upgrading my SAP NetWeaver system to SQL Server 2016

[luisdarui]

Since SAP released the support for SQL Server 2016 to some products based on SAP NetWeaver - not all products are currently supported, see SAP Note 2201059 - I was preparing my test systems to upgrade to SQL Server 2016. I took me 2 days to complete all the necessary steps to upgrade the database to SQL Server 2016.

My system is a SAP NetWeaver 7.50 (initial shipment, no SPS), ABAP stack,running on SQL Server 2014 SP2, central system with DB and ASCS/DI installed on the same host.

Preparation

Before going forward I took some time to review the existing SAP documentation about SQL Server 2016. I started with the "Upgrade to and Installation of SQL Server 2016" guide (edit 29/11/2017: the database guides have moved, so the link was broken. You can find the new guides in this wiki. Check also this blog as well) . Another good reading I had was Seabastian's blog post on MSDN "How to install or upgrade SAP Systems to SQL Server 2016".

I used SPAM to apply the required SAP BASIS in my system, as it made no much sense to use SUM - be careful, if you're doing a NEW installation on SQL Server 2016, you cannot use SPAM to apply the minimum required SPS for NetWeaver, you have to use SUM - see SAP Note 2201060. After the SPAM update and updating SAP_BASIS, SAP_ABA and SAP_BW packages to SPS 05 each of them (and other dependency packages like SAP_GWFND, SAP_UI and ST-PI), I was ready forthe upgrade, but I had to download the media.

Download of SQL Server 2016 and SWPM

As SAP employee I have access to such media for internal testing. But if you're a customer or a partner, and acquired SQL Server from SAP, you would have to consider the changes in licensing terms of SQL Server described in SAP Note 2139358, before downloading the SQL Server you have to follow the steps described in the note. If you acquired SQL Server from Microsoft or any other re-seller, you cannot download the media from SAP, only the installation framework - see SAP Note 2313067.

So I've downloaded the DVD number 51051068 (consider always checking SAP Note SAP Note 2313067 for the most current DVD number). It already contains the CU1 for SQL Server 2016 RTM, which as of 16/10/2016 is the minimum supported by SAP (see SAP Note 62988), but I decided to go for CU2, which is the most recent available. Personally, I use this non-official blog for getting the SQL Server builds and versions, but if you're more comfortable with official links, you can use the MS KB article 321185. I also downloaded the most recent version of SWPM available at http://support.sap.com/sltoolset.

Running the upgrade

I stopped the SAP system and took a FULL database backup from my system (I forgot to mention - I took other full backup before applying the SPAM and SPS updates). The SQL Server upgrade took just a few minutes to complete. The next step would run the SAP Tools for MS SQL Server to complete the upgrade (see SAP Note 683447). This is a very important (and mandatory step), as monitoring objects from DBACockpit are subject to change between different releases - and they're maintained mostly based on SQL Server release. There are also basis and application component objects that are bound to SQL Server specific release and the database compatibility level, they are all adjusted by SWPM.

Conclusion and next steps

There were some interesting changes with SQL Server 2016. For example, the trace flag 2371 is no longer necessary - it is now part of SQL Server database engine by default (see SAP Note 2263545). Trace flags 1117 and 1118 were replaced by database configurations (see SAP Note 2294688). Interesting thing is that SWPM is not removing the trace flags 1117 and 1118 for now, only 2371 was removed, so I had to remove them from the startup parameters manually. Although they are no longer required, they will not cause any alert, error or harm to the system. The trace flag 9481 is still present, so I am still using the old Cardinality Estimation (see SAP Note 1961467).

Next, I have an identical hardware that I will install SQL Server 2016 and also implement AlwaysOn Availability Groups (see SAP Note 1772688). I will follow Jurgen's blog post about the new features for AlwaysOn on SQL Server 2016 as I'm really excited about the new seeding option, and I also find very tedious to manually create them.

For a complete list about what is new in SQL Server 2016, check books online,

[Former Member]

Very detailed information about the upgrade to MSS 2016.
Congratulations!

[lbreddemann]

Good write up!
Appreciated the personal description of what you did and why and especially the sparing of screenshot walls (which are part of such a post far too often IMO).

[Matt_Fraser]

Luis,
.
Thanks for this write-up. It does seem that Microsoft is pushing out new versions of SQL Server fast and furious lately; a new version every two years? Whereas there was a (theoretical) four-year gap from 2008 to 2012, a five-year gap from 2000 to 2005, etc.
.
I'm glad you highlighted the licensing changes for customers with runtime licenses. I was aware of the licensing change, though hadn't gone into specifics of it as we're fine on 2012 for the time being. I suspect, however, that many customers may not be aware of the change in terms, and given how many organizations are pushing toward or considering cloud-based or hosted solutions, this could new a real gotcha.
.
Equally important, thank you for highlighting the Notes describing the various startup parameter changes, and what SWPM handled automatically and what you had to do manually.
.
I use and rely upon the same "non-official" blog of SQL Server versions myself; it's the easiest way to keep track of and link to the latest cumulative update!
.
(p.s. trying a trick I just picked up from Steffi Warnecke about putting a period into my "between-paragraph" blank lines to see if the comment can retain some easier-to-read formatting).
.
Cheers,
Matt

[luisdarui]

Hi Lars,
Thanks for the feedback. Going into the new ~ SCN ~ vibe that blogs are more personal opinion, experiences, etc, I tried to mix that up. I also blogs with many screenshots, but I feel that they still have some kind of utility, specially when you want to highlight something.

[luisdarui]

Hi Matt,

Yes, I too feel that Microsoft is pushing new versions, maybe too often, don't you think? I was afraid that if with too often product releases there wouldn't be adherence to the recent versions. Maybe the advantage of upgrading from 2012 to 2014 isn't that much - speaking of SAP databases, but for BW with the Columnstore it is a huge upgrade. Now what is left to understand is how much benefits you would gain from 2012 to 2016 for non-BW. I'm still working and testing SAP NetWeaver on SQL Server 2016 to find out how some of the new features would work with SAP NetWeaver.

I highlighted the startup changes because before SAP released the support for SQL Server 2016, I was testing SQL Server 2016 already and noticed those changes but I really didn't know how it would end up.

I really didn't know the comments weren't retaining the line break on the comments. I think such things will be improved in time (I really hope!).

Cheers, Luis

[former_member212248]

Luis,

We are at a point where we need to upgrade the O/S (from 2008 R2 to 2012 R2) and SQL Server (from 2008 R2 to 2016).  Our shop is very new to SAP, and would like to know if you have any documents/comments/etc. about the strategy to do such an upgrade.  We are running several SAP modules (ECC, CRM, PI, BW, and Solman), and trying to work through a strategy to upgrade DEV, TEST, ... PROD for all modules.  I've heard that you have to build a new duplicate environment in a bubble (isolated from the other servers), keep the same server names, SAP SIDs, etc.  This all seems very complicated, so i would appreciate any guidance you can offer.  Thank you for your time.

Sincerely,

Melanie

[luisdarui]

Hello Melanie,

SAP support Windows in-place upgrades. You might want to check SAP Note https://launchpad.support.sap.com/#/notes/1494740 for more information.
When it comes to SQL Server, then it starts to get complicated.
You have to met the minimum SAP NetWeaver SPS requirements to start with. After this, upgrade your Windows Server 2008 R2 to 2012 R2. Immediately, you will have to upgrade SQL Server 2008 R2 to SQL Server 2016, because the combination of Windows Server 2012 (R2) with SQL Server 2008 (R2) is not productively supported, but this won't be a problem if you upgrade SQL Server immediately after upgrade Windows.

Note that, even though you follow every recommendation, you might still have problems after upgrade Windows or during the upgrade of SQL Server. These problems are somehow unpredictable, e.g. missing DLL, DLL not registered, missing assembly... corrupted assembly, missing registry key, etc...

With virtualization it become quite easy to create a new VM, install all required software and simply run a system copy. It might look more complicated than an in-place upgrade, but in the end of the day, it eliminates a lot of complexity and prevent so many unpredictable problems. And you can have both systems running for a while The option for in-place upgrade is quite challenging, but possible and as per you can see, supported.

[Former Member]

Hi Luis, maybe you know the answer to this question, maybe not. As you said, planning for SQL Server can be complex.

I am upgrading SAP from ECC 6.0 EHP4 on NW 7.01 to ECC 6.0 EHP6 on NW 7.31. The platform is Windows 2008 R2 / SQL Server 2008 R2 using Windows Cluster. (which does not support a Windows inplace upgrade)

After the SAP upgrade, I want to migrate the same SID to a new Windows 2012 host and attach the SQL 2008 database to a new SQL 2012 installation (not on Windows CLuster) using the detach, copy files, and attach method of SQL Server in order to upgrade the database. (strongly suggested by note 888210).

For guidance, I am reading note 683447 - SAP Tools for MS SQL Server which states "Use the "Database Upgrade Completion" option of the SAP Tools for MS SQL Server to perform mandatory tasks on the database after you upgraded your SQL Server to SQL Server 2005 or higher with the SQL Server setup. If you copied and attached an SQL Server 2000 database to an SQL Server 2005 server, use the "Database Copy Completion" option of the SAP Tools for MS SQL Server instead."

I am looking for clarification on the following:

For SQL Server 2012, do I run the "Database copy completion" after attaching the SQL 2008 database to SQL 2012 instance, or do I need to first upgrade SQL 2012, run the upgrade completion procedure, then do the migration and database copy completion ? I am assuming that I can use the new System Rename tool (note 1619720) in SWPM SP19 for the database copy completion part.

I've looked at notes 1676665 - Setting up Microsoft SQL Server 2012, 683447 - SAP Tools for MS SQL Server, 151603 - Copying an SQL Server database, 1780433 - System copy of SAP systems to Windows Server 2012 (R2) : SQL Server, 1619720 - System Rename for SAP Systems based on SAP NetWeaver, but it is not clear when both a SQL upgrade and migration to new host are happening.

Thoughts ?

Thanks,

Margaret

[luisdarui]

Hi Margaret, you just have to use the Copy completion in this case. I have re-read the SAP Note 683447. It will carry out the necessary steps that upgrade completion would do.

[Former Member]

Thanks so much. One more question, is the new "system rename" in SWPM SP19 the same thing as the post database copy ?

[luisdarui]

Hi Margaret,

The system rename will change an entire system SID (e.g. PRD to PR1), including the database.

If you want to simply refresh the database of an existing system (e.g. Copy PRD database to QAS), you should copy the database as per SAP Note 151603 (you can rename the data and log files before attaching or during the restore, by specifying the file names) and later on the file logical names (they aren't changed automatically but there is no use within the SAP NetWeaver), all you need is to rename the database to QAS. Once you run the database refresh option in SWPM, it will move all the objects from the old schema (prd to qas) and will get your system online after completing the copy.

Check out this blog: https://blogs.sap.com/2017/03/02/refresh-database-content-without-reinstalling-the-database-or-kerne...

In the system copy guide there is more information in:
"6 Copying Single Instances Only"
"6.3 Copying the Database Only – Refresh Database Instance"
"6.4.1  Copying the Database Only - Refresh Database Content on MS SQL Server"

[Former Member]

yes, but in my case it is the same SID, just a new server.  I do not want to use the old procedure because I'd have to reinstall the application server. There too many permissions and shares that had to be set, and I'd lose that work.

[luisdarui]

If you are migrating the entire system to a new server (e.g. Windows 2012 from Windows 2008) you wil have to run the system copy option and install the target system.

If you are moving only the SQL Server database, you can simply copy the database (backup/restore or detach/attach) and run the Database Copy Completion.

[Former Member]

Thanks. That worked great. I appreciate your help!

[Former Member]

Hi Luis,

I am working for ANZ Australia. We have existing SAP Netweaver 7.0EHP1 on Windows 2003 and SQL Server 2005. Recently as part of Remediation, I installed distributed Netweaver 7.5 on Windows 2012 and SQL server 2016 using SWPM. I asked my SQL DB to refresh the Database using SQL tool. He did that but I am unable to connect. Can you suggest way to migrate the DB. Do you have steps which I should follow.

Regards,

Nitin

[luisdarui]

That is a bit complex, without knowing all the details (logs, etc). I think your best bet is to reach the SAP Product Support through Expert Chat or an Incident.

[former_member349777]

Hi Luis,

You are linking the the SQL Server Upgrade to and Installation Guides in this amazing blog.

All these guides, including “Upgrade to and Installation of SQL Server 2016”,have been migrated to the new SAP Help Portal.

You can find detailed information on the new location and formats here: https://blogs.sap.com/2017/11/28/upgrade-to-and-installation-of-sql-server-guides-moved-to-sap-help-... and on this updated wiki page: https://wiki.scn.sap.com/wiki/x/3hO7Gg

Best,
Jessica

[luisdarui]

 

Hi Jessica! Thanks for the heads up. I have updated the blog to make sure anyone who reads can find the new guides!

Kind regards, Luis

[former_member385776]

Hello luisdarui

 

Thanks for this write-up. In our SAP PI system, Currently we are using SQL 2012, We are planning to upgrade our application to PO for that SQL DB need to be upgrade from 2012 to 2016. I've discussed with my DBA they can upgrade from SQL 2012 to 2016.by referring the document Upgrade to and Installation of SQL Server 2016 in an SAP Environment,  but we do not have any clue how upgrade 2012 to SQL 2016 always on concept.

 

Could you share your thought how we can adopt 2016 always on from SQL 2012. This will highly helpful to complete our project.

 

Thank you in advance.

 

Regards

Selva

[luisdarui]

Hi Selva,

Upgrading the SQL Server database is quite simple, in a nutshell you do a backup of your current database and run the upgrade from the SQL Server DVD.

When we add the application layer, and SAP NetWeaver-based systems being the application layer, you have to be careful about the application prerequisites.

Before anything else, you need to make sure your OS x DB x Application is supported in PAM. If the SAP Netweaver release isn't supported on SQL Server 2016, then you cannot upgrade.

Next, you have to check if SQL Server 2016 support requires an specific SPS of your NetWeaver. SAP Note 2201059 describes the minimum software requirements.

Once you have the minimum requirement, you can run the SQL Server database upgrade.

 

You can get all this information, on detail, in the Upgrade to and Installation guide of SQL Server.

 

Best regards,

Luis

[former_member385776]

Thanks for the quick reply luisdarui  🙂

 

I've checked the all prerequisites from net-weaver system point of view and fine.

When i was discussed with my DBA, they said SQL 2012 to 2016 are possible and they can do that by referring Upgrade to and Installation guide of SQL Server document.

 

Currently we having SQL 2012 with mirroring concept, my DBA said, once they done the upgrade to SQL 2016, they have to continue the SQL 2016 mirroring concept which not recommended by Microsoft and Microsoft recommended to go with 2016 AlwaysOn concept

We do not have any clue, how to move or upgrade from SQL 2016 mirroring or SQL 2012 mirroring to SQL 2016 AlwaysOn .

 

Could you share your thoughts, How to move/upgrade from SQL 2012 mirroring concept to SQL 2016 AlwaysOn.

 

Or Is it upgrade is not possible from SQL 2012 mirroring concept to SQL 2016 AlwaysOn in SAP environment.

Regards

Selva

[luisdarui]

Hi Selva,

 

Yes, it is possible to upgrade SQL Server databases that are part of an AlwaysOn Availability groups. Check out this document: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/upgrading-always-on...

 

Best regards,

Luis