1 2 Previous Next

SAP on SQL Server

24 Posts

As I’ve been quite busy within the last year I didn’t find much time to contribute or share any breaking new for a while – even though the lack of spare time didn’t improve much recently it’s finally time to talk about a ‘new’ topic which at least everyone working with BW has definitely come across lately – Columnstore.


Within the last year I spent a lot of time migrating BW systems to SQL Server, implementing SQL Server Columnstore Indexes and trying to make the queries and loads as fast as I can or if possible faster than they are on Hana. Today I want to share the results with you.


What is a SQL Server Columnstore Index, how does it work and how to implement it in an SAP BW system?


Many documents answer these questions a lot better than I ever could so I will not repeat the explanations for you but rather point you to the documentation that I found most helpful:


Column Store Indexes Described

Brief explanation on
- what Columnstore is
- how it works technically and
- what the main advantages are

Using SQL Server 2012 Column-Store with SAP BW

My personal favorite as the author managed to compress all the information you need to understand:
- which requirements need to be fulfilled
- how to implement column store indexes
- what to expect

within 19 pages


How difficult is it to implement SQL Server Columnstore Indexes and how long does it take?


In my opinion, SAP does a very good job with providing easy-to-use tools on SAP software level to implement quite powerful features on SQL Server level.
I saw this with row/page compression where a single report (MSSCOMPRESS) was all one needed to row/page compress database objects online/offline. With implementing SQL Server Columnstore Indexes it’s as simple again. SAP provides a report called MSSCSTORE which allows you to create Columnstore Indexes for a single infocube or all infocubes in the system and you can also use this report to switch back to rowstore for a single or all Infocubes (while I can’t think of a reason to switch back) with just a few clicks. The interface of the report is quite self-explanatory – if some questions still remain open it makes sense to look into SAP Note 1771177 and  Using SQL Server 2012 Column-Store with SAP BW.


I didn’t do any detailed performance analysis on the runtimes of implementing Columnstore Indexes and runtimes - as always – are influenced by many factors (hardware, system load, database size, and many more). However, the database sizes where I implemented column store indexes varied from 3 to 12 TB, and the runtime for all Infocubes in total varied from 3 to 12 hours.


Real-World Results with SQL Server Columnstore Indexes

The experiences I gained with SQL Server Columnstore Indexes are based on several proof-of-concept projects where the aim was to find out how fast BW queries and data load processes could get after switching from the conventional table structures of Infocubes to the usage of SQL Server Columnstore Indexes. Even though reducing the database size was not an important goal in any of the POCs I could observe a mentionable and sometimes even a massive reduction of the database size in every single case.

Case Study 1 - Reduction of space consumption

These numbers show how the space consumption of the database changed after different actions.




Original Size of the database

Allocated MB in transaction DB02 so no freespace within the DB Files contained in that number. All objects in the DB are page compressed.

9,5 TB

DB Size after compressing requests of Infocubes

All requests except the ones from the current month were compressed in transaction RSA1.
This has been done for all Infocubes. The aggregates still existed.

9,5 TB

DB Size after implementing Columnstore Indexes for the infocubes and deactivating aggregates

All requests except the ones from the current month were still compressed from the last action.
Columnstore Indexes were created with the option to deactivate all aggregates during the run.

6,2 TB

DB Size with Columnstore Indexes for Infocubes and aggregates using Columnstore Indexes as well

Aggregates were configured to use Columnstore Indexes as well and have been activated again

7 TB

Looking at the tables and indexes of an Infocubes in more detail before and after implementing Columnstore Indexes I gained an idea on how this amount of space is saved without doing anything but creating Columnstore.

Space Consumption of E + F fact table of an Infocube after different actions


Size of F-fact table [GB]

Size of E-fact table [GB]

Size of E + F fact [GB]

Delta to last step [GB]

Original state – no BW requests compressed, no Columnstore indexes, page compressed objects





After compressing all requests of the Infocube except the ones from the last and current year




+ 4,8

After compressing all requests of the Infocube except the ones from the last and current month




+ 0,6

After creating Columnstore indexes for the Infocube




- 13,3

In this case I’ve been using SQL Server 2012 for my tests. As Columnstore indexes are not updatable with this SQL Server release they are only used on the E-fact table of an Infocube. After creating a Columnstore Index including all columns of the E-fact table all other secondary indexes on the table become superfluous – for this reason MSSCSTORE drops them when it creates the Columnstore Index. The space previously occupied by the secondary indexes of E-fact tables becomes freespace now. The Columnstore Indexes of course consume space as well but they are stored column-wise and not row-wise and as columns often have similar data, high compression rates can be achieved (also pointed out here Column Store Indexes Described). In all my tests the Columnstore Indexes were quite small in comparison to the table sizes.

In another project I didn’t record the sizes in this level of detail but it might still be interesting to know that the original database size (Oracle with no database compression feature in use) was around 3 TB and after migrating to SQL Server and creating columnstore indexes (page compression in use as well) it went down to 800 GB.

Case Study 2 – Comparison of Query Runtimes

Looking at the runtimes of a defined set of BW queries on the source system (Oracle) and a SQL Server system with Columnstore Indexes I could observe the following runtimes:


Source System [sec]

SQL Server 2012 with columstore on E-fact tables but without any aggregates [sec]

SQL Server 2012 with columstore on E-fact tables and selected aggregates without Columnstore [sec]

Query 1




Query 2




Query 3




Query 4




Query 5




When I carried out this test SAP’s recommendation was to let report MSSCSTORE deactivate all aggregates while creating the Columnstore Indexes. As the above runtimes show I was able to further speed up 2 queries after re-activating aggregates for 2 queries – back then these were still conventional aggregates without a Columnstore. In the SAP released column-store also for BW-aggregates (SAP Note 1951490) which presumably would have speeded up all tested queries again.


The total runtime of all tested queries looked like this:


Total runtime for all tested queries [sec]

Source System (Oracle, no database compression)


SAP Hana


SQL Server 2012 with page compression and Columnstore Indexes on the E-fact tables but without any aggregates


SQL Server 2012 with page compression and Columnstore Indexes on the E-fact tables and with selected conventional aggregates without Columnstore Indexes


SQL Server 2012 with page compression and Columnstore Indexes on E-fact tables and on aggregates

Unfortunately didn’t have the chance to test this but presumably < 308

Lessons Learned

Keeping it brief without going too much into detail I learned the following lessons:


  • In any case mentionable, mostly even massive reduction of the database size
  • Speedup by factor 0-50 per query depending on the query and the cube design
  • The bigger the cube, the more speedup
  • If bottleneck is not the database, the speedup with implementing tuning measures on DB level like Columnstore Indexes understandably turns out to be a modest affair
  • Load runtimes improve as less index maintenance is required
  • As soon as the requirements are fulfilled the implementation of Columnstore Indexes is very simple
  • The integration of Columnstore Indexes in SAP BW is constantly improved so it makes sense to look for new features on a regular basis and adopt them
  • Considering the achievable speedup it makes sense to benchmark if a BWA still makes sense or if the required runtimes can already be achieved using SQL Server Columnstore Index


Continuous Improvements

Within the last year Iots of improvements were introduced regarding the integration of SQL Server Columnstore Indexes into SAP BW. To make sure I do not miss any of them I keep looking for SAP documentation, SAP notes, blogs on SCN and the SAP on SQL Server section of MSDN before I start with a new migration. It seems that some improvements are shipped within SPs without being announced explicitly for this reason I also try to always use a recent SAP BW and SAP Basis support package.

Ok, first a few caveats:

  • Windows Server 2012 R2 isn't quite yet officially supported by SAP at the time of this writing, but it's coming very soon.  It is currently supported in the First Customer Shipment program, details of which can be found in Note 1955608.
  • In-place upgrades of Windows servers with SAP software installed are supported, but not in all situations, and generally a system copy or migration to new hardware with a fresh install of Windows is recommended instead.  Details about when in-place upgrades are supported can be found in Note 1494740, which is applicable for Windows Server 2012 and higher as well as the versions explicitly mentioned in the Note's title.


So, with that out of the way, here's an interesting 'gotcha' which you may encounter if you do elect to perform an in-place upgrade with SQL Server installed.


The scenario is upgrading a Windows Server 2008 R2 sp1 system with SQL Server 2012 sp1 to Windows Server 2012 R2.  SQL Server is previously upgraded from 2005, which may or may not be a factor.


After the Windows upgrade is complete, everything works fine, except the SQL Server Configuration Manager tool is missing from the Start menu.  This is definitely a minor annoyance, and it appears to not be an isolated case, as I found external blogs mentioning this issue.  I want to give credit to Marnix Wolf for figuring out what is happening and how to fix it (Thoughts on OpsMgr and System Center 2012: Quick Trick: Where Is SQL Server Configuration Manager After Updating To WS20…).  In a nutshell, the upgrade is unregistering the Configuration Manager MMC snap-in, but the files required to operate the Configuration Manager are still present.

So, how to fix it?

The easy way is to find another SQL Server 2012 system and copy a shortcut from it.  If you install SQL Server 2012 fresh on Windows 2012, you won't have this problem, so with a little luck you have such a system sitting around.  Open the folder C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Microsoft SQL Server 2012\Configuration Tools, and copy the shortcut SQL Server Configuration Manager from that folder to the same folder on your upgraded system, where you'll notice that it is missing.

Alternatively, you may be able to copy the shortcut from a location on the same server you just upgraded.  By default, the 2012 R2 upgrade creates a copy of the critical folders on your C: drive before starting the upgrade and places them in C:\Windows.old.  So, if you drill into C:\Windows.old\ProgramData\Microsoft\Windows\Start Menu\Programs\Microsoft SQL Server 2012\Configuration Tools, you should find that same shortcut for SQL Server Configuration Manager there.  Copy it to the same location outside of \Windows.old (i.e., just remove \Windows.old from the file path), and you will be good to go.

That's it.  Look in your Start menu, and now the Configuration Manager shows up as it should, with the correct icon.  Click it, and the tool starts and operates correctly.

If you don't have another functioning SQL 2012 system from which to copy the shortcut, then you should be able to make your own shortcut, in this same folder, with the target pointing to C:\Windows\SysWOW64\mmc.exe /32 C:\Windows\SysWOW64\SQLServerManager11.msc.

If you're doing a system copy of a SAP System on top of Microsoft SQL Server 2008 R2 and you're using a local admin other than Administrator, then you need to continue to read. Otherwise you can skip this blog and read other valuable blogs.



When you try to logon to the target system to restore a SQL Database Backup to restore, and you're not .\Administrator, probably you'll get the following error:


Login failed for user 'Domain\User'. (Microsoft SQL Server, Error: 18456)



1. Open an elevated Command Prompt.

2. Stop the SQL service with command: net stop mssqlserver

3. Start SQL Server in Single User Mode with command: "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlservr" -m"SQLCMD"

4. Now open another command prompt window and write command SQLCMD. In case of connecting to named instance we need to supply server and instance name as with -S switch:


5. Grand your admin user to SQL role sysadmin with following commands:

> EXEC master..sp_addsrvrolemember @loginame = N'STFANET\c2eadm', @rolename = N'sysadmin'

> go


6. Go back to first command window and press Ctrl+C to stop the SQL server and then type 'Y' for confirmation. This will stop the sql server.

Start SQL server again and no startup parameters need to specified this time.


Source: http://beyondrelational.com/modules/2/blogs/115/posts/11143/how-to-access-a-newly-installed-sql-server-2008-r2-instance-…

While one can search a lot of documents in SAP on SQL. Running SAP on SQL server and notes in BC-DB-MSS, what I want to do is integrate all materials togather and provide a single place to check your SAP on SQL system. Okay, let's build a checklist and if you have any comments, please give me a feedback below.


1. First things first, the most important factor to affect the system performace is memory so please make sure you install and allocate sufficient memory for SQL server. SAP KBA 1612283 - Hardware Configuration Standards and Guidance mentions this in great detail. I recommend the size of data cache should be more than 20% of the database size after compressed.


2. Check if you use the correct core-based licening E.E. if more than 40 logical processors are used.


3. Check if all configurations for SAP on SQL are met. SAP KBA 1744217 - MSSQL: Improving the database performance.


4. Leverage AlwaysOn or Database mirroring to prevent from Corruption Handling in SAP Databases.


5. Also check juergen's white paper for the sake of completeness. SAP with Microsoft SQL Server 2008 and SQL Server 2005: Best Practices for High Availability, Maximum Performance, and Scalability - Part I: SAP Architecture and SQL Server Basic Configurations, Features Used, and Windows Configurations.


6. If thousands of IOPS is still required, consider to use Fusion IO to maximize IO performance. Compared to traditional SAN storages, MLC NAND flash is much cost effective.


7. Leverage RZ20 auto reactions to perform daily monitor like oldest open transactions, transaction log utilization, SAP DB backup status...etc. and read EWA to check additional configurations and system performance.


8. Leverage DBACockpit to run DBCC CHECKDB and monitor expensive SQL statements(SQL Statement History is supported after SAP Netweaver 7.0 EHP2).

If your backup (or restore) have been started by another adminitrator or by a job, you cannot use the GUI (or the Messages tab) to follow the progression.


In this case some dynamic management views can be used to track the backup/restore progress:


SELECT command,
estimated_completion_time/1000 as "seconds to go",
dateadd(second,estimated_completion_time/1000, getdate()) as "estimated completion time"
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s




If you are not interested in the command used, the s.text and view dm_exec_sql_text can be removed:


SELECT command,
estimated_completion_time/1000 as "seconds to go",
dateadd(second,estimated_completion_time/1000, getdate()) as "estimated completion time"
FROM sys.dm_exec_requests r



Of course, you can adapt this statement to select more (or less) information.


Hope you like this blog and any comments/suggestions are welcome!



Eduardo Rezende

IDES on SAP ERP6 EHP6 on Windows 2008R2/MS SQL Server 2008R2
  • Common preparations;
  • Performance tuning;
  • Applying patches;
I. IDES common preparations to update SP (support package).
1. SAP Kernel update
Stop your SAP system, including any SAP services that were installed. Backup your kernel. Download the most recent kernel and apply. We need to download a kerned for this particular release, taking OS version and DB server into consideration. Assuming Kernel Release is 720_REL 64B UNICODE for IDES ERP6 EHP6 (as a matter a fact it is!), latest patch of Kernel on SMP is Patch 402 under path: 

SAP MarketPlace => Software Downloads => Support Packages & Patches => A-Z Index => K => SAP Kernel 64 Bit Unicode => SAP KERNEL 7.20 64-BIT
UNICODE => <Your OS> => #Database independent

SAP MarketPlace => Software Downloads => Support Packages & Patches => A-Z Index => K => SAP Kernel 64 Bit Unicode => SAP KERNEL 7.20 64-BIT
UNICODE => Windows on IA64 64bit -> MS SQL Server
  • Backup files in directories:
  • Unpack the kernel:
  • Create a directory (‘c:\update’ for instance) and copy ‘sapcar.exe’ there (from one of the directory specified above). Copy kernel update files to just         created dir (SAPEXE_100-10011367.SAR and SAPEXEDB_100-10011365.SAR for instance… the name of the file can vary, depending on kernel release). Unpack the *.SAR archives using the command in command line:
sapcar -xvf SAPEXE_100-10011367.SAR
  • Stop all the SAP* services in windows ‘control panel’ and sapmmc.
  • Copy all the extracted files from both archives to the folders specified on the step B with replace.
  • Now run the services again.
  • It is highly recommended to read SAP Note 19466 before update and detailed user manual on this subject located here.
2. Make changes to the instance profile (RZ10)
First of all, import all active profiles (in RZ10 go to ‘Utilities’ menu -> Import profiles -> Of all active servers).
  1. Second of all, please read carefully this article about optimal values for ‘rdisp’
    processes and calculate them in accordance to your system configuration.
  2. Here is the typical values (and
    again, they could be not suitable for you hardware and may affect badly the
    overall performance):
rdisp/wp_no_dia = 10
rdisp/wp_no_btc = 3
rdisp/wp_no_enq = 1
rdisp/wp_no_vb = 3
rdisp/wp_no_vb2 = 3
rdisp/wp_no_spo = 1
login/system_client = 800 (for IDES system)
rdisp/keepalive_timeout = 600
rdisp/keepalive = 6H
ms/keepalive = 9999999
ms/conn_timeout = 100000
ms/http_timeout = 600
em/initial_size_MB = 1024
icm/keep_alive_timeout = 6000
icm/conn_timeout = 9999999
rdisp/max_wprun_time = 14400
rdisp/gui_auto_logout = 0
c.  Restart sapmmc to apply the changes.
3. Other post-install activities

     a. Change passwords for SAP* (default values are: 06071992 or PASS on clients 000, 001) and DDIC users (19920706 for the same clients).

     b. Connect SAP Online Help using SR13.

     c. Use SE06 to initialize the TMS (t-code STMS).

     d. Import RFC's

     e. Check TMS configuration via ABAP report, use SE38, report RSTPTEST.

     f. Add an initial screen system message called ZLOGIN_SCREEN_INFO using SE61.

     g. Recompile all your ABAP loads by scheduling a job using SGEN.

     h. Apply to SAP for your permanent license key.

     j. Back up your system.

4. Update SAP components
Apart from kernel update it is required to patch other system components such as: DBSL (database package), TP, DISP+WORK, R3TRANS, R3TA, R3SZCHK, R3LOAD, R3LDCTL. On the screenshot below DBSL is already patched to the latest release:
Download, unpack and replace outdated system files with the new ones.

II. IDES common preparations to update SP (support package).

1. MS SQL Server tweaks & tricks


     a. Apply latest SQL Server patches (there is SP2 available for MS SQL Server 2008 R2)

     b. Update SQL Server statistics (sp_updatestats). There is no need to schedule a job because SAP has already done that itself.

     c. Check out DBACOCKPIT to make sure there are no warnings on ‘Alert’ tab (it this tab is missing it means that current state of MS SQL Server is fine). Correct existing alerts according to the suggestions given:


     d. Check DB02 t-code to make sure there are no missing objects in the database (Diagnostics -> Missing tables and indexes -> Objects missing in the database):

If there are indexes missing in the DB, please create them by scheduling a job, it will improve overall performance dramatically.
     e. Check this article written by  Beate Grötschnig toimprove SQL Server performance and align it to optimal parameters.

     f. It is highly recommended to read SAP note 1237682 (SQL Server 2008 parameters).

     g. Short instruction on how to apply a trace flag to SQL Server configuration:

    • On the server where SQL Server 2008 has been installed.
    • Start SQL Server Configuration Manager
    • Choose the "SQL Server Services" in the left pane and then
    • Right click on the SQL Server instance of interest
    • Choose Properties
    • In the Properties dialog box select the "Advanced" tab
    • Drop down the list box to the right of "Startup Parameters"
    • Add a semicolon at the end of all those parameters and then the trace flag:  ;-T1117

     h. It also required to move TEMPDB apart from SAP databases to improve the performance (SAP DB log should be moved to another disk as well). To do that please use the next t-sql statement:

    • The first step is to determine the logical file name of the data files for tempdb:
tempdb GO EXEC sp_helpfile GO
    • Knowing this we can run the following ALTER DATABASE statement in SQL Server 2005 or SQL Server 2008 to move tempdb.  Set the FILENAME parameter to the location where you'd like each file:
MODIFY FILE (NAME = tempdev, FILENAME = 'D:\tempdb2005.mdf')
MODIFY FILE (NAME = templog, FILENAME = 'D:\tempdb2005.ldf')
Restart SQLserver services and check whether configuration is correct.

2. Other preparation activities


     a. Make sure after the Kernel update all of the below locations are of same Patch Level.

LOCATION 1: /sapmnt/<SID>/exe
LOCATION 2: /usr/sap/<SID>/<Instance>/exe

     b. From the productive client check the TRBAT entries. 

  • You can clear two entries there (one is header information and other is ‘Tr’ number) from SM30 and then save.
  • Then login to 000 client. Run RDDNEWPP in dialog mode and give it a Priority A.
  • Then come back to the productive client and reimport the request.
  • When reimporting, please check TRBAT table again (it should contain the current request only)
Theoretical help:
  • Executing the report RDDNEWPP schedules the transport dispatcher RDDIMPDP as an event-controlled background job in the current client (use SE38, run (F8) and choose "High priority"). The transport dispatcher RDDIMPDP must be scheduled as a background job in client 000 and in all the clients from which data is exported or into which data is to be imported.
  • When you execute the report RDDNEWPP, you are asked whether you want the job to be scheduled as normal (job class C) or with high priority (job class A). If you choose high priority, the transports will have priority over other background jobs.
  • RDDIMPDP can be scheduled or rescheduled at any time by executing RDDNEWPP. Any earlier scheduling in the same client is deleted.


III. Applying service patches to IDES.


1. Update support package manager (SPAM)


     a. As a first step before any serious update – you have to update SPAM to the latest release available, you can check current release version by entering to this transaction:


     b. In the console (cmd) go to “/usr/sap/trans” and unpack SPAM update package (KD73149.SAR for instance) using SAPCAR as it was shown in the first topic:

sapcar -xvf KD73149.SAR
     In the “/usr/sap/trans/EPS/in” you will see *.PAT file.

     c. Now go to ‘Support package -> Load package -> From application server’ menu:

     Extracted files will be loaded to SAP.

     d. Go back to the same menu and choose ‘Import SPAM/SAINT update’. Wait until SAP finished update processing.

2. Update SAP BASIS and ABAP components


     a. First thing you need to know is that all the SAP SP updates are strongly sequential, ie we cannot have component of level 0002 and jump over the next one right to 0004 for instance, because 0003 should be applied first (in another words they are not cumulative!)

     b. To check components patch level go to ‘System -> Status’ menu, and press ‘Component information button’:


     c.  Now you have to see all the components level:


     d. Assuming you have already downloaded all the necessary patches (taking in account SAP release number, which is 731 in our case) you can go ahead to unpack them using SAPCAR.  Go to “/usr/sap/trans” directory as usual and copy all the patches there:

     After unpack they will be available in the “/usr/sap/trans/EPS/in”.

     e. Go to client 000 and login with user DDIC.

     f. Proceed to ‘Support package -> Load package -> From application server’.

     g. Define the queue:


     h. Remember that you have to update BASIS subsystem first, then ABAP:


     j. You can add all the patches for BASIS in one queue or apply them one by one, but read the SAP note  1597765 first (about common errors of patches update). Be careful, as this note explains some common mistakes of update and also (which is more important) the right approach for BASIS update (the combination of SAP BASIS patches).  I recommend to use small queues for better control on SAP update.



     k. Remember! If you apply one single patch in the queue it shouldn’t take more than 2 hours! Thus it means that you didn’t follow the instructions preceding patching procedures.

     l. Use ‘Component information’ window from step ‘c’ to make sure the update was successful.

3. Common errors during update and their solution.


     1. =====================================================================================

Problem: Phase CHECK_REQUIREMENTS. The system has found a number of open data
extraction requests. These should be processed before starting the object import process.
- use se38 (report RMCEXCHK) to identify application which cause an error (for instance, let it be 02 (logistics));
- if there are awaiting V3 in SM13, process them all (as a selection criteria, begin from 2000 year);
- clear open extractions in the system (t-code LBWG);
- clean up SMQ1 from the BW system (use t-code SMQ1 to select and delete existing requests for all the clients (*));
- after that se38 shouldn’t show any errors;

     2. =====================================================================================

Problem: "R3TRCLASCL_XXXX was repaired in this system" error message.
It's shown because the object mentioned in the message is locked in a transport request that has not been released yet.
In order to solve the incidence you have to do the following:
SE03 -> Objects  -> Display Repaired Objects   -> click the 'Execute' icon (F8)  -> select the R3TR CLAS CL_XXXX  object by single click on it ->
click the button 'Repair flag' (F5) to reset the repair of this object.

     3. ======================================================================================

Problem: SPAM import finished with the following error.
! There are inactive objects in the system
! Abort the import due to an error situation
This issue arise because of some objects stuck in transport.
a.Enter transaction code: SE10
b.Next, click on Display
c.Locate your request under "Modifiable"
d.Click on the plus + next to the request to expand the node
e.Select the customizing task and then click on transport "Release Directly"
             f. Wait till the customizing task is fully released.
             g. Thereafter, select the main request and click again on the transport "Released Directly".
h.Once the request is fully released, give that number to your basis guys to transport to test system for you.
The object could be released right from the log (Go to -> Status -> Queue). Click on the object name and in the next window click
'activate'. Do not forget to release transport after that as well (in SE10).

     4. =====================================================================================

Problem: SPAM import ended with return code ==>8<== (phase TEST_IMPORT)
SE03 -> Objects -> Display Repaired Objects If R3TRFUGRSZA1 is listed and you are sure it has been released (check with developers if unsure)  then select it and choose the "Repair Flag" button which will reset the repair flag.

     5. =====================================================================================

Problem: SPAM import warning 'PERFORM ADJUSTMENT' SPDD
Right from SPAM go to menu: GoTo -> Statu -> Queue (Click 'Approve adjustment').

     6. =====================================================================================

           Problem: During SPAM package update GUI interface became unavailable
           If you're unable to control package update progress in SPAM or SM37 you can use windows-level tools such as 'dpmon'.
           It can be found in the 'run' directory, i.e. C:\usr\sap\<SID>\SYS\exe\uc\NTAMD64
           Run 'dpmon' in console (cmd), in work processes you can see SAPLSTPA process is still running.
           Wait until it finish background job and SAP GUI will be available again.

In this blog, I am trying to accumulate all the relevant information one should consider in the planning phase for upgrading MSSQL database to 2012.


Supported Environments for Upgrade

Upgrades to products based on SAP NetWeaver 7.1 (including EhP1) and SAP NetWeaver 7.2 are not supported on SQL Server 2012.

Hence, if you plan to upgrade your SAP product based on SAP Netweaver 7.0 (including all EhPs ) on SQL Server 2012, you have to upgrade to a SAP product based on SAP NetWeaver 7.3 and higher.

SAP products prior to SAP NetWeaver 7.0 are not supported at all on SQL Server 2012.


SAP Application pre-requisites for upgrading to MSSQL 2012

Before upgrading any SAP system running on MSSQL (lower than SQL server 2012) to MSSQL 2012, SAP application needs to fulfill certain patch level pre-requisites.

Required minimum SAP Netweaver Support Package Stacks (SPSs) for SQL Server 2012







SAP NetWeaver 7.2 - no restriction




For BI Systems the SAP application pre-requisites are as follows

The minimum required SPSs are the same for SAP BW as for all other SAP NetWeaver products. However, SAP recommends the following newer SPSs when running SAP BW on SQL Server 2012:






SAP NetWeaver 7.2 - no restriction



Note: If your system is running on a SPS lower than the one required above, you have to apply the minimum required SPS before upgrading/migrating to SQL Server 2012.


Platform support

The minimum OS release supported for running on SQL Server 2012 is Windows Server 2008. In particular, SP2 is required for Windows Server 2008, while SP1 is required for Windows Server 2008 R2.

In addition, SAP will only support SQL Server 2012 on x64 (also known as X86_64, AMD64, EMT64).Restrictions:  SAP will not support SQL Server 2012 on Windows IA64 or 32 bit.


Tool used to perform MSSQL 2012 upgrade

To complete the database upgrade, use the latest STM tool to finalize the configuration of your database (see SAP note 683447).
Run the SAP Tools for MS SQL Server on the central instance host. You have to use the SWPM for 7.3+ (SWPM.SAR, not 70SWPM.SAR) in all cases - even when you are performing the task for a SAP NetWeaver 7.0x system (e.g. SAP EHP4 for SAP ERP 6)


Reference Docs/ SAP notes while upgrading database to MSSQL 2012

Note 683447 - SAP Tools for MS SQL Server

Note 1651862 - Release planning for Microsoft SQL Server 2012

Note 1676665 - Setting up Microsoft SQL Server 2012


After performing a system copy, you must perform the post steps but could find the "Generic Installation Options" in SWPM?




Check note 1890950!


Checking SAP Note 1680045, you notice there are two different versions of the SWPM:

  • 70SWPM*.SAR


Checking SAP Note 683447, you can see the tools are only available in the SWPM*.SAR (not 70SWPM*.SAR):

o Run the SAP Tools for MS SQL Server on the central instance host.
You have to use the SWPM for 7.3+ (SWPM.SAR, not 70SWPM.SAR) in
all cases - even when you are performing the task for a SAP
NetWeaver 7.0x system (e.g. SAP EHP4 for SAP ERP 6)


...does not work after a system copy?


Check note 1886616!


SAP Netweaver based systems uses two different variables sets, environment and profile variables.


During a system copy those variables should be updated.


If only the profile variables (<Unit>:\usr\sap\<SID>\SYS\profile\DEFAULT.PFL) are updated, you will be able to start your SAP system but some tools (e.g. tp) which uses the environment variables of your <sid>adm user will not work.


To exemplify:


You have a SAP system ERM located on host VM-MSS-OLD with default database instance, the database variables should be:


  • C:\usr\sap\ERM\SYS\profile\DEFAULT.PFL
    • dbms/type = mss
    • dbs/mss/dbname = ERM
    • dbs/mss/schema = erm
    • dbs/mss/server = VM-MSS-OLD
  • Environment variables of user ermadm:
    • DBMS_TYPE=mss
    • MSSQL_SCHEMA=erm


After a system copy of system ERM to host VM-MSS with default database instance, the database variables should be:


  • C:\usr\sap\ERM\SYS\profile\DEFAULT.PFL
    • dbms/type = mss
    • dbs/mss/dbname = ERM
    • dbs/mss/schema = erm
    • dbs/mss/server = VM-MSS
  • Environment variables of user ermadm:
    • DBMS_TYPE=mss
    • MSSQL_SCHEMA=erm


If a mismatch in the server variable is observed, this could be the cause of the issue:


  • C:\usr\sap\ERM\SYS\profile\DEFAULT.PFL
    • dbms/type = mss
    • dbs/mss/dbname = ERM
    • dbs/mss/schema = erm
    • dbs/mss/server = VM-MSS
  • Environment variables of user ermadm:
    • DBMS_TYPE=mss
    • MSSQL_SCHEMA=erm


If the system <SID> is changed during the system copy, all database variables must be updated.

This variables mismatch might affect other SAP procedures, as other tools (e.g. R3trans) also uses the <sid>adm user variables.


Hope you like this blog and any comments/suggestions are welcome!



Eduardo Rezende



Is this possible database refresh in sand box server from production server backup and different SID if yes please send me link step by step procedure.


Environment detail is below:


SAND Box server    : windows server 2008 R2 , SQL Server 2008 R2 and SAP ECC 6.0 ,  support pkg level 157, kernel release 701


Production Server : windows server 2008 R2 , SQL Server 2008 R2 and SAP ECC 6.0  ,  support pkg level 157, kernel release 701  



your responce is highly appreciation


Thanks in Advance



Recently my client asked me to run a promotion. They were moving code from development to QA and that too in a newly configured server.

The promotion contained certain SQL scripts which queries the Active directory. But since there were no ADSI linked server created and configured, I had to create it.


Sometimes we would need to query the Active directory using SQL query.

The script below will create a linked server and and use OPENQUERY to retrieve data.


1. Create the linked Server -  to create a linked server

          EXEC sp_addlinkedserver @server = 'ADSI', @srvproduct = 'Active Directory Services 2.5', @provider = 'ADSDSOObject', @datasrc = 'adsdatasource'

2. Add a security Context - create a security context

          EXEC sp_addlinkedsrvlogin @rmtsrvname = 'ADSI', @useself = 'False', @locallogin = 'sa', @rmtuser = '<DOMAIN>\<username>', @rmtpassword = '<password>'

3. Configure the server to allow OPENQUERY functions

--Configure the server to allow OPENQUERY functions

sp_configure 'show advanced options', 1
reconfigure with override

sp_configure 'Ad Hoc Distributed Queries', 1


We can now access Active Directory using a query such as this:

SELECT * FROM OpenQuery(ADSI, 'SELECT * FROM ''LDAP://DC=<DOMAIN>,DC=com'' WHERE objectCategory=''User'' ')


Hello ,


In layman language, I found MS SQL very user friendly in SAP environment which tackles most of all the database administration things automatically. By bench-marking with SAP with oracle, I got following major positive points, which are being handled automatically without any manual intervention :-


1. We don't need to check the table space .

2. No need to maintain Archive log mode --- ON /OFF  which generally leads to full the archive directory .

3. No need to do statistics update manually .

4. Upgrade is also comparatively easier than oracle.


I have raised all above points based on my perception , so there may be some ambiguities which I would like you to suggest for correction, so that we can get to a bottom line of the topic being discussed over here.


And of course,  there are many advantages are also there for oracle and other databases, but all mentioned points only subjects to past experience. Request you all to update on this to make a valuable blog.


With Thanks,


If you need to check which columns are in a particular table, with Oracle databases you can use command desc:


SQL> desc "SAPGFP"."T000"









Within SQL Server we do not have the same command but we can use the following statement:


where TABLE_NAME='T000'

* More columns from INFORMATION_SCHEMA.COLUMNS can be selected if necessary









Also, sp_help (much more information about the object) can be used:


sp_help 'sm1.T000'


Results in the attached file sp_help T000.txt.zip.


And of course use transaction DBACockpit:




Hope you like this blog and any comment/suggestion are welcome!




Eduardo Rezende

SCN Gamification

Posted by Eduardo Rezende Apr 29, 2013

What do you think about the new SCN Gamification?


If this is a complete strange topic for you, check Chip's blog:


We’re LIVE with #SCN Gamification!  #SCNGameOn


Personally, I think this new platform will help the community.

Do you have suggestions about any particular badge (or mission) we should have here at SQL Server space?


I already got some badges, like:

  • I Was Here
  • First Steps
  • I Shared Some Knowledge!


Which badges did you got?

Any particular badge you are looking for?

Quite often someone asks me how an external SQL Server database can be accessed by an SAP system, e.g. to:


  • Access data in an external SQL Server database with the SAP system 
  • Report against data in an external SQL Server database with Business Intelligence / Business Warehouse 
  • Use DBACockpit to monitor an external SQL Server instance


Depending on:


  • Which operating system your SAP application servers run on
  • Which purpose you want to use the connection for
  • Which type of SAP application servers (ABAP, Java, Dual-stack) are available in the SAP system


There are different connection types, technical requirements and restrictions. This blogpost clarifies the possibilities and restrictions and covers frequently asked questions:


  1. Options and technical requirements to access an external SQL Server database 
  2. How to setup a connection with UDConnect 
  3. How to setup a connection with DBCon / Multiconnect 
  4. How to monitor an external SQL Server Database using DBACockpit 
  5. Troubleshooting



1. Options and technical requirements to access an external SQL Server Database


The SAP standard ways to connect an external SQL Server instance with an SAP system are:

  • Multiconnect (DBCON)

  • UDConnect (Universal Data Connect)

Regardless of the way you choose you can only connect to remote databases which are reachable via network from your SAP Application Server.


DBCON / Multiconnect


DBCON / Multiconnect uses the Microsoft SQL Server Native Client Software (SNAC) to establish a connection to the remote SQL Server instance. The Microsoft SQL Server Client Software for Windows consists of several *.dll files. For long time it was available for Windows platforms only. Recently, Microsoft ported its ODBC SQL Native Access driver to Linux. For this reason heterogeneous Linux/Windows scenarios are now possible. DBCON utilizes the SAP ABAP stack to access the external databases so your system requires at least one ABAP-stack-based SAP Application Server running on Windows or Linux x86_64.




UDConnect uses a JDBC (Java Database Connectivity) driver to establish a connection to the remote SQL Server instance. The JDBC driver consists of one or more *.jar files and can be used on Windows, Unix and Linux operating systems. As UDConnect utilizes the J2EE engine of the SAP Application server to access the external databases you need to have at least one Java-Stack-based SAP Application Server in your SAP system in order to use UDConnect.


Connectivity Matrix



Linux x86_64


Java Stack




ABAP Stack




Dual StackUDConnect



  • If your system comprises solely of ABAP stack-based servers running on Unix platforms you can neither use UDConnect nor DBCON. Why? Because UDConnect requires at least one Java-stack based SAP Application Server (regardless of the operating system) and DBCON requires at least one Windows- or Linux x86_64-based SAP Application Server.  
  • Using DBCon on a Linux x86_64 based application server can only be used to connect to SQL Server versions 2005 and higher. Predecessor releases are not supported by the Microsoft driver. Furthermore, the driver is only supported for Red Hat Enterprise Linux 5.x and higher and for Suse SLES11 SP2 and higher.


2. How to setup a connection with UDConnect


UDConnect cannot be used for remote monitoring a SQL Server based system. However, you can use it to access data in an external SQL Server database.

Setting up UDConnect in order to access data in an external SQL Server Database with BW/BI requires four steps:

  • Adding an RFC server on Java-stack side 
  • Defining an RFC destination on BW/BI side 
  • Installing and configuring the JDBC driver on Java-stack side 
  • Configure the connection URL for the external database on Java-stack side

For step-by-step instructions please see the configuration guide available under:

SAP Netweaver '04: How to configure UD Connect on the J2EE Server for JDBC Access to External Databases
SAP Netweaver 7.1: see attached guide (UDConnect_for_710.pdf)




3. How to setup a connection with DBCON / Multiconnect


To access data in an external SQL Server Database with DBCON / Multiconnect three steps are required:

  • Installing the SAP DBSL for SQL Server (dbmssslib.dll / dbmssslib.so)

  • On a Windows-based server: installing the Microsoft SQL Server Native Client (SNAC) or
    On a Linux x86_64 - based server: installing the Microsoft ODBC driver for Linux

  • Creating a DBCON entry for the external database 

  SAP note 1774329 explains the steps required to prepare your SAP instance to connect to a remote SQL Server instance. 



SAP DBSL for Windows

DBCON utilizes the ABAP-stack to connect to an external database. The ABAP-stack itself requires the Database Shared Library (DBSL) to communicate with a database. For each Relational Database Management System (RDBMS) supported by the ABAP-stack there is a separate DBSL provided by SAP. To install the DBSL:

  • Determine which kernel your SAP system is using (32 bit / 64 bit, Unicode / Non-Unicode, Kernel Release, Operating System)

    • kernel release: go to ransaction SM51 → place the cursor on the SAP instance → click "Release Info"

    • bitversion, Unicode / Non-Unicode, Operating System: go to "System" → "Status"

  • Download the archive containing the most recent SAP DBSL for SQL Server matching your kernel

    • go to SAP Software Download Center → Browse our Online Catalog → Additional Components → SAP Kernel → SAP KERNEL <bitversion> <Unicode / Non-Unicode> → SAP KERNEL <kernel_release> <bitversion> → <Operating System> → MS SQL Server → lib_dbsl_<patchlevel>-<number>.sar

  • Extract the downloaded archive using command

    • sapcar -xvf lib_dbsl_<patchlevel>-<number>.sar

  • Copy the unpacked dbmssslib.dll file into the kernel directory of all SAP application servers which you want to use to establish the connection.

SAP DBSL for Linux x86_64

Please see SAP note 1644499 if you need to download and install the SAP DBSL for Linux x85_64-based servers. The note describes how to request the DBSL and also explains in detail which steps are required to properly set it up.



DBCON entry

The DBCON entry informs the ABAP-stack where to find the external SQL Server Database and how to authenticate. Please see SAP note 178949 to learn how to create a DBCON entry for an external SQL Server Database.

Microsoft SQL Server Client for Windows

The SQL Server native client is used to establish the connection to the external SQL Server instance. To install it you need to run the sqlclni.msi installation package which is available from the SQL Server installation DVD / CD, or from the Microsoft Software Download website.

Microsoft ODBC Driver for Linux x86_64

SAP note 1644499 explains in detail where to download the Linux x86_64 - based ODBC driver and how to install it.






4. How to monitor an external SQL Server instance using DBACockpit


To monitor an SQL Server database with DBACockpit you first need to configure a DBCON connection to the external database. Please refer to section 3 for details.

If your local system is running on SQL Server as well you can skip installing the Microsoft SQL Server Native Client (SNAC) and SAP DBSL for SQL Server as both will already be in place. Then, proceed with the DBACockpit-related configuration steps. You can find detailed guides attached to SAP note 1027512 (sqldba_cockpit.pdf) and in SAP note 1316740.


UDConnect cannot be used for remote monitoring - the only way you can monitor a remote system is by using DBCon.



5. Troubleshooting

  • No shared library found for the database with ID <DBCON_entry_name> or Unable to find library <kernel_directory>/dbmssslib.sl'. ->
    DLENOACCESS (0,Error 0) or ERROR => DlLoadLib()==DLENOACCESS - dlopen - ("/usr/sap/<SID>/DVEBMGS00/exe/dbmssslib.so") FAILED
    or could not load library for database connection <DBCON_entry_name> or cannot open shared object

  This error indicates that the ABAP stack could not find the SAP DBSL for SQL Server (dbmssslib.dll) in the kernel directory. If you encounter this error on a Unix - based server the root cause is clear: the DBSL does not exist for other platforms than Windows or Linux x84_64. In this case use a Windows-based or a Linux x86_64-based SAP Application Server to establish the connection. If your system does not contain a Windows-based or a Linux x86_64-based Application Server you need to setup a small one as workaround. If you encounter this error on a Windows Application Server or a Linux x86_64 based Application Server make sure that the DBSL is properly installed in the kernel directory as explained in point 3. 

  • B Wed Jan <timestamp>
    B  create_con (con_name=<dbcon_name>)
    B  Loading DB library '<kernel_directory>\dbmssslib.dll' ...
    M  *** ERROR => DlLoadLib: LoadLibrary(<kernel_directory>\dbmssslib.dll) Error 14001
    M  Error 14001 = "This application has failed to start because the application configuration is incorrect.
        Reinstalling the application may fix this problem."
    B  *** ERROR => Couldn't load library '<kernel_directory>\dbmssslib.dll'
    B  ***LOG BYG=> could not load library for database connection <dbcon_name>

The DBSL could be found successfully in the kernel directory but there was a problem while loading it. This can have various reasons. To ensure that the file itself is not corrupt please download and install the file from scratch as explained in point 3. If the error remains afterwards please check the OS Log for further errors at the time of the error.

  • Generate Activation Context failed for
    Reference error message: The referenced assembly is not installed on your system.
    Dependent Assembly Microsoft.VC80.CRT could not be found and Last Error was
    The referenced assembly is not installed on your system.

  The Microsoft runtime DLL's which are required by the DBSL are missing on your server. Please install them as explained in SAP Note 684106. 

  • Could not find stored procedure 'SAPSolMan<version>.sap_tf_version'"

DBACockpit uses stored procedures to collect monitoring information from a database. These stored procedures need to exist in the database that is being monitored. If you are using the connection for a purpose other than remote monitoring with DBACockpit you can ignore this error. If you want to remote monitor the SQL Server database please make sure that you've configured the connection exactly as described in the configuration guide referenced in point 4. Then you need to create the missing stored procedures in the remote database. To do so open transaction DBACockpit in the monitoring system, use the "System"-Dropdown field to select the remote SQL Server system which you want to monitor -> go to Configuration -> SQL Script Execution. If the monitoring schema is missing in the remote database you will be offered a button called "create/repair schema". After using it to create the schema you will be offered a button called "Execute script(s)". Click on it to create all required monitoring Stored Procedures in the remote database.

  • You want to update the JDBC driver used by your UDConnect connection

Follow the instructions in SAP Note 1009497.


Filter Blog

By author: By date:
By tag: