1 2 3 Previous Next

SAP on SQL Server

31 Posts

Moving the SAP DATA FILES from existed to newly added drive

 

If your SAP database or log files are running out of disk space, you have to add new disks to the file system.


Security Permissions :

 

Your Windows user account must be a member of the db_owner fixed server role in the SQL Server instance to detach DQS databases.

Your Windows user account must have CREATE DATABASE, CREATE ANY DATABASE, or ALTER ANY DATABASE permission to attach a database.

You must have the dqs_administrator role on the DQS_MAIN database to terminate any running activities or stop any running processes in DQS.

 

If an existing device cannot be extended, you have to add an entire new device to your system.

In this case, you first have to configure the new device as an NTFS partition. Then you have to move existing SAP database files have to the new device by detaching from and re-attaching the database to the SQL Server.

 

Case study - I have taken an instance SM1.

 

Procedure –

 

1. Shut down the instance.

2. Detach the Database.

3. Copy the Data files from Existed drive.

4. Paste the copied Data files to newly added drive.

5. Attach the Database.

6. Start the instance.

1.JPG                                      

  • Instance has shut down.

 

  1. In the SQL Server Management Studio right-click the SAPSM1 database and choose Tasks -->Detach, The Detach Database dialog box appears.

2.JPG

Select the “Drop connections” check box, Choose OK to detach the database.

3.JPG

The SAP database is detached from the SQL Server and is no longer visible in the SQL Server Management Studio.

 
Copy the required SM1 Data files from Existing device,

4.JPG

Paste the SM1 Data files to newly added drive

5.JPG

In the SQL Server Management Studio, expand the server, right-click Databases and choose Attach. The Attach Database dialog box appears.

6.JPG

 

Specify the mdf file of the SAPSM1 database by choosing the Add button.

All the files belonging to the SAPSM1 database are listed under Original File Name.

7.JPG

We have to change the path of which one need to change

Now where we have located, it’s assigned that path only. Choose ok to attach the Data base.

8.JPG

 

Now able to view the SAP Instance in SQL server Management studio, and is again visible and accessible for the SQL Server. The SQL Server can now use the newly added drive.

9.JPG

 

 

Started the SAP instance, and Instance is up on running.

This blog post will cover some part of this topic which seems to be the most painful and unpredictable  situation: database corruption. They're mostly physical inconsistencies, associated with storage and raid controllers (physical devices), drivers, software controllers, etc.

 

Database corruptions can happen at any time due to many factors, but most of them caused by hardware issues. You should be prepared for it.

 

Of course this blog post is intended to help BASIS administrators or new DBAs starting with SQL Server. If you're already an experienced SQL Server DBA and is new to SAP, this blog has some useful information, but nothing that you haven't seen before I hope. If you're an "old sea dog" on SQL Server and SAP, then probably the content of this blog is irrelevant with redundant information that bothers you every day .

 

 

Identifying database corruptions:

 

I can say that 2 DBCC CHECKDB are enough, most of the times, to check the database consistency.  1 result against the database and a second result against either the same database on the same database server/instance after you clear the buffers (it may be only a transient corruption, but still a corruption!) or you restore the current database against a second server (preferable) or another instance on the same server and get the results to compare.

 

Make sure you read completely the SAP Note 142731 - DBCC checks of SQL Server.

 

 

Performing the first DBCC CHECKDB:

 

In the SQL Server Management Studio perform the command:


DBCC CHECKDB ('<SID>') with no_infomsgs;

dbcc_checkdb1.png

 

Save the results for later analysis.



Re-validating the results:

 

When occurs a first access a database page, it's first stored in the data buffer of the SQL Server. Due to software bugs or defective RAM modules, a transient corruption may occur. When you invalidate the data cache, the database corruption can simply go away - but you shouldn't sit and forget about it! Always investigate the source of the corruption! For this it's necessary to have a second result to ensure that the database is really free of corruption or the corruption was transient. Or in the worst case, confirm a database corruption.

 

 

 

When you invalidate the data cache, the SQL Server will have to read all the data from the disks again. If you use a RAID system, some pages can be out of sync (RAID system mirror not identical). Due to optimization the reads are taken sometimes from the original and sometimes from the mirror. With this, the results of the DBCC CHECKDB can differ each time you execute it after you clear the data cache.

 

 

I personally suggest to backup / restore your database into another server/instance. Preferably, to another hardware, to eliminate a complete investigation over a system that is suspect.


  1. Reset the data cache:


    To clear the buffer cache, perform the command:

    DBCC DROPCLEANBUFFERS;
    
    

    dbcc_dropcleanbufers.png

    Note: This command invalidate the buffer cache and if you perform it in a production system, the database will have to read all the data from the disk again. The users will observe bad performance and even TIME OUTS when running programs and transactions. If it's not feasible for you to invalidate the buffer cache, then backup and restore the database into another server/instance!

     

  2. Backup the database and restore it into another server/instance.

    This option will eliminate some questions about the effectiveness of the analysis and efforts in order to fix/restore your database/system. I won't enter in details on how to backup / restore your database.

  3. Repeat the DBCC CHECKDB, save the results to analyze it.

 

 

An inconsistency has been detected. What now?

 

Well it's time to identify them in the results. Depending on the following message, you're able to repair the tables:

CHECKDB found 0 allocation errors and 8 consistency errors in database 'NW1'.

repair_rebuild is the minimum repair level for the errors found by DBCC CHECKDB (NW1).


The above results indicates that the corruption is not severe and you can fix without data loss, which is the opposite of the following error message:

CHECKDB found 10 allocation errors and 1739 consistency errors in database 'NW1'.

repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (NW1).


It indicates that at least one table is severely corrupted and cannot be repaired.


I won't go in deep on methods that can help you to extract the data from a severely damaged table because it's not the goal of this blog post. You may also keep in mind that depending on the table that is affected you can also rebuild the table with data from other tables (it depends on the component that is responsible for the table involved) or it's a table that doesn't store sensitive data and can simply be truncated or dropped and recreated. But for this I strongly suggest you to read the entire blog. I'll approach later with some suggestions if your minimum repair level is "repair_allow_data_loss" - also known as RADL.

 

For each table that is listed in the CHECKDB results is necessary to perform another DBCC CHECKTABLE to check the level of the damage to each table:

DBCC CHECKTABLE ('<sid>.<TABLE_NAME>');

dbcc_checktable.png

 

For the tables listed with "repair_rebuild" or "repair_fast" as the minimum repair level, you can repair the tables with the DBCC CHECKTABLE and REPAIR_REBUILD. However, you have to set the database to SINGLE_USER first:

 

ALTER DATABASE <SID> SET SINGLE_USER;

After completing, you can repair each table:

DBCC CHECKTABLE ('<sid>.<TABLE_NAME>',REPAIR_REBUILD);

dbcc_checktable2.png

 

 

After completing the repairs, perform another DBCC CHECKDB whether your database is now clear. If the result is positive, set the database back to MULTI_USER:

 

ALTER DATABASE <SID> SET MULTI_USER;

 

Before releasing the system back online to the users, take a full backup for safety

 

 

The minimum repair level is "repair_allow_data_loss". What to do?

 

If the table was marked as the minimum repair level as "repair_allow_data_loss" it means that this corruption cannot be fixed. In some cases (most of them), the database catalog is still consistent and the affected objects are application tables. Depending on which data was affected, maybe it can be (or in most of the cases, cannot be) reconstructed from redundant application data.  You can identify the application areas that are responsible for those tables and try to approach them with the list of the affected tables and see if they can recover. It's explained on SAP Note 1597910.

 

It's not guaranteed that it's possible to recreate a database table with the complete data it had before. But you can give a try.

 

For all instances, in case you have a database object corruption with "repair_allow_data_loss", the best solution is to rely on your backups. Restore your database into another server. Start with the latest good full backup and start restoring the transaction log backups until you recover your system completely.

 

If a restore is not possible due to retention policy of your backups or your backups are also corrupted/not consistent, then the best approach is to contact Microsoft directly or a third party. SAP urges to customers to maintain a minimal (and efficient) backup strategy and even provide functions which assist the administrators in the areas of backup and database consistency checks. Disaster recovery solutions and efficient backup strategies have proved to be more efficient than to try to remedy those situations.

 

To understand the SAP Goals and Policy with the handling of the database corruptions on SQL Server, read carefully and completely the SAP Note 1597910.

 

 

Action required! Investigate and eliminate the source of corruptions!

 

All work done so far to fix/restore your database may be jeopardized if you simply ignore the fact that something went (and probably still) wrong in your database server. Investigate and eliminate the source of the corruption is as important as fixing/restoring the database. Even that the corruption can be caused by software or a bug, you can almost always find the root cause of the corruption in the hardware or driver problems.

 

 

In order to prevent database corruptions, what can I do?

 

You can't prevent database corruptions. It's something that is beyond your control. It can happens even if follow all the best practices. The difference is the impact / downtime and the risk that you lose your data is even lesser or 0 if you follow all the recommendations from SAP and Microsoft.

 

You can work proactively to:

  • Keep your software updated: regularly apply patches to your Windows and SQL Server;
  • Keep your drivers tested and updated.
  • An efficient backup strategy - database corruptions do not spread through transaction logs (unless the storage where you keep your backups is also damaged). Read the SAP Note 1297986 for suggestions to backup/restore strategies for SQL Server.  Testing the consistency of the backups is also part of the backup strategy.

  • Regularly perform DBCC checks to make sure your database is free of corruption.
  • As of SQL Server 2012 you can benefit from AlwaysOn. As there is no shared storage between the primary and secondary replica, if the primary replica is corrupted, there is a chance that the secondary is not. Consider implementing an efficient disaster recovery strategy, it can save a lot of time and your heart may live longer.
  • Do not neglect a backup strategy only because you have a high availability like Windows Failover Cluster or AlwaysOn. Backup is a must and you shouldn't even consider think about giving it up!
  • DO NEVER RUN DBCC WITH REPAIR_ALLOW_DATA_LOSS! Unless it's explicitly asked to you from SAP. And I haven't asked!



Best Regards,

Luis Darui

Support Engineer, SAP NetWeaver DB/OS Platforms
SAP Labs Latin America.

1. Check the Data Cache Hit Ratio of your database!

 

One of the most important things to configure is the memory for SQL Server. If you observe a bad database performance and a low data cache hit ratio for a long time during database load, you should increase the memory for SQL Server. Low Data Cache Hit ratio values means that your server will be performing more I/O operations than it should be.

hit_ratio.png

 

If you run your SQL Server on a Virtual Machine and your data cache Hit Ratio doesn't increase overtime, despite of having enough memory to increase the data cache Hit Ratio, check the number of "Free Pages". If the value is high , it may indicate that VM is using a dynamic memory feature, which is also known as "Memory Ballooning". If the data cache Hit Ratio is low, there will be a lot of I/O operations and it will "mislead" you to think that your I/O subsystem is not working properly.

 

 

Check the following blog page of our Microsoft colleagues on MSDN and read the white paper to better understand:

 

Virtualization – Overcommitting memory and how to detect it within the VM

 

 

You can use the RAMMAP to analyze this:

Introduction to the new Sysinternals tool: RAMMap

 

 

 

2. Check the I/O performance!

 

For the most recent SAP NetWeaver systems, you can access the DBACockpit transaction and open the Performance folder and select "I/O Performance". Hit the button "Current Values" and observe the ms/Read columns for the data files and the ms/Write columns for the transaction log.

 

ioperformance.png

 

 

You can get more details by double clicking the files to compare the data load/write and the ms/Read/Write.

If your database have been restarted recently and your data cache Hit Ratio is not high enough, you might notice high data loads (Reads/sec and ms/Read).

 

 

In normal operation, the read time of the data files shouldn't be higher than 15ms and the write to the transaction log shouldn't be higher than 3ms.

This kind of error is usually caused by hardware. Check and fix the I/O subsystem or approach another I/O subsystem vendor in order to get assistance to deliver an acceptable performance.

 

 

More information:

987961 - FAQ: SQL Server I/O performance

Juergen's whitepaper:

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

 

 

Ah, make sure you don't have more than one transaction log file. It's also a common mistake for DBAs starting with SQL Server to use other database vendor recommendations .

 

 

3. Check whether locked pages can be used!

 

The major part of the main memory allocated by the SQL Server is the Data Cache. It's important that the Data Cache is not paged out, otherwise the database will read the pages from the disk and not from the memory. With SQL Server 2005 and higher versions, it's possible to disallow the operating system to page out pages allocated by the SQL Server.

Check the SAP Note 1134345 - Using locked pages for SQL Server

 

 

4. Check the database parameters

 

Bad or incorrect parametrization can impact the SQL Server I/O performance, specially for the memory settings. Check whether your SQL Server parameters are set according to bellow notes (select the note that fits your SQL Server version):

 

879941 - Configuration Parameters for SQL Server 2005

1237682 - Configuration Parameters for SQL Server 2008

1702408 - Configuration Parameters for SQL Server 2012

1986775 - Configuration Parameters for SQL Server 2014

 

 

5. Database Compression

 

As of SQL Server 2008 you can use row or page compression in SQL Server. Compressing the database objects can significantly decrease the amount of space occupied. With less space occupied, you'll have less I/O operations. Database compression is default in all newly installed systems as of May 2011.

 

 

To compress your database you must first fulfill all the prerequisites from the following SAP Note:

 

1488135  - Database compression for SQL Server

 

 

How to compress:

 

    1. Open the SA38 transaction and run the report "MSSCOMPRESS"

    2. Filter the tables that are not compressed:

db_compress.png

 

    3. If uncompressed objects are found, you should compress them. The above SAP Note 1488135 gives more background on how to do this.

 

 

In order to open the SAP Notes you must be logged into Service Marketplace.

 

 

Best Regards,

Luis Darui

Are you facing disconnections (sql/database error 0, 11, 121, -1 or 10054) with your SAP system?

 

The following points might help you to solve this issue!

 

1. Refer to Dale's presentation: Analyzing Network Errors on SQL Server.

 

2. Follow all the steps in the SAP KBA 1478133.

 

3. Some of the following points are common errors that are prone to cause this issue:

  • Using a ODBC client instead the SQL Native Client;
  • Using older versions of the SNAC (always use at least the same version of your database instance);
  • Older versions of DBSL and SAP Kernel;
  • Running old versions of SQL Server (always run the latest Service Pack and the latest Security update, hotfixes and cumulative updates provided by Microsoft - see Note 62988 and KBA 1733195).
  • Hardware problems, outdated NIC drivers, Server Memory, etc (hardware, physical network, etc);

 

4. You can perform the following steps to try to avoid those errors:

  1. On the server side, increase the registry setting HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters.
    TcpMaxDataRetransmissions to 10 {up from the default setting of 5}.
  2. On the client side, both the "Keep Alive" and "Keep Alive interval" can be safely increased via the SQL Server Configuration Manager tool.
    The numbers are in milliseconds and the defaults are 30000 and 1000 respectively.
    Increase "Keep Alive" to 90000 and "Keep Alive interval" to 5000.

 

5. For further information, double check Notes 1794178 and 1593183.

 

6. Is your SAP system running on VMware? If yes, also check:

 

Related Spaces:


In order to view the contents of the SAP Notes and KBAs, you will need to be logged into Service Marketplace.


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

 

Best regards,

Eduardo Rezende

Performance issue becomes a real problem for some SAP systems. Especially if your DB size is big, there are many customer codes.

 

In one of our customer's system we had a performance issue. We had a meeting with all parts involved in SAP Systems. HW vendor, Network group, Business, System Admins and BASIS. The result of the meeting was an action list.

Beside all architectural actions, one of the action was finding fragmanted indexes and applying defragmantation and it was my duty to do the needful.


This blog is output of this action and about index defragmantation on SQL Server.

 

I will try to explain the topic in the following order.

  1. List the indexes that will be possibly defragmanted and effecting the system performance
  2. Analyze the indexes
  3. Defragmant the indexes
  4. Run new Statistics for the indexes
  5. Results and Chart
  6. Pros and Cons of defragmanting

 

And these are the details of the steps:

 

1. List the Indexes

When we were listing the fragmanted indexes our scope was on the indexes that are used in expensive SQL Statements. We also checked the indexes of the tables that are accessed sequentially on process overview.

 

Go to DBACOCKPIT --> Performance --> SQL Statements

SQL_1.jpg

Double Click on one of the SQL Statements.

Go to Explain Tree Tab to see the index names used in the SQL Statements.

SQL_2.jpg

For other indexes we use SM66 Global Work Process Overview.

Check the sequential reads and related tables.

 

SQL_3.jpg

After taking the list of related tables you go to SE11 and take the index names.

SQL_4.jpg

 

2. Analyze the Index

Go to DBACOCKPIT --> Space --> Single Table Analysis

Enter the related table name and select the index name.

Select DBCC showcontig  button.

     SQL_5.jpg

 

     Run one of the options Fast, All Levels and Normal.

     SQL_6.jpg

Yo can also use SQL SQL Server Management Studio to find DBCC Showcontig results using similar Queries like below.

     SQL_7.jpg

DBCC SHOWCONTIG allows you to measure fragmentation and page density levels on indexes.

 

The result is analyzed according to the document "Microsoft SQL Server 2000 Index Defragmentation Best Practices" mentioned in SAP Note 159316 - Reorganizing tables on SQL Server. You can check the "Analyzing Output from DBCC SHOWCONTIG" section in the document for details.


The document says that  "High values for logical scan fragmentation can lead to degraded performance of index scans. Consider defragmenting indexes with 20 percent or more logical fragmentation."


It states also that "indexes containing more than 10,000 pages realized performance gains, with the biggest gains on indexes with significantly more pages (greater than 50,000 pages)."

 

So we selected all indexes that has logical scan fragmantation more than 20% and more pages.

 

3. Defragmant the indexes

    

We used SQL Server Management Studio Query editor to run scripts for defragmanting.

Examples:   DBCC INDEXDEFRAG (<SID>, "<sid>.EKPO", [EKPO~0])

                    DBCC INDEXDEFRAG (<SID>, "<sid>.RFBLG", [RFBLG~0])

 

Duration of defragmantation changes according to the index sizes and Logical Scan Fragmentation percentage. As you see below screen it took 3 hours. Some lasts more than a day.

 

This process also generates logs. So you must monitor your log space during the process. Otherwise your system can stop due to lack of log space.

 

DBCC INDEXDEFRAG reports the estimated percentage completed every five minutes.

 

DBCC INDEXDEFRAG can be stopped at any point in the process, and any completed work is retained.

 

SQL_9.jpg

4- Update Statistics

     We updated statistics of the related tables after defragmantation to obtain the changed statistics.

     SQL_10.jpg

5- Results and Graph

    When we rerun DBCC Showcontig command after defragmantation we can easily see the decrease in Logical Scan Fragmentation.

     The value became an acceptable value as it is seen in below picture.

     SQL_11.jpg

     As a result, we took the I/O Stall read and write times from DBACOCKPIT --> Performace --> History --> Database Collector Data Time Series table.

     SQL_12.jpg

     We exported data to an excel sheet and took the averages of all read and write times for the whole period. (Red and orange lines in below chart).

     From the chart below it can easily be seen that I/O values decreased significantly. So it worth defragmenting.

     SQL_13.jpg


6- Pros and Cons of defragmanting

  • First of all, SAP explicitly recommends not to reorganize or rebuild any database objects on a regular basis. You should not even reorganize or rebuild objects as an attempt to solve a performance problem as long as it is not evident that fragmentation is the root cause of the problem (which it hardly ever is).

 

  • You can do this process for particular indexes that are fragmented with high percentages, has alot off pages and used in expensive sql statements.

 

  • It is time consuming job

 

  • Generates lots of logs, monitoring is must

 

  • Defragmentation can be done online. Instead DBREINDEX can be used for offline solution.

 

Resources:

159316 - Reorganizing tables on SQL Server

1660220 - Microsoft SQL Server: Common misconceptions

Microsoft SQL Server 2000 Index Defragmentation Best Practices

You're back from your coffee break, and your SQL Server 2012 installation has completed successfully. What next? You're ready to install your SAP application now, right?

 

Not quite yet. There are a few configuration and housekeeping steps you should take before going further. Note: many of these steps can also be handled within an ABAP system via DBACOCKPIT, or from Solution Manager, but I'm an old hand and still find some of this considerably easier using the SQL Server native tools.

 

SQL Server Post-Install Configuration (Pre-SAP)

 

 

You should still have your Upgrade to and Installation of SQL Server 2012 in an SAP Environment guide open to chapter 4.2 (Installing the SQL Server 2012 Database Server Software Manually). You are now at Step 6 in the guide. In most circumstances the TCP/IP protocol will be enabled by default, so you should not have to change anything. However, it is worthwhile to follow this step to ensure this is the case, and this is a good time to pin the SQL Server Configuration Manager to your taskbar and/or start menu.

 

Database Mail

First up, let's give SQL Server the ability to email you when it's in trouble.

 

  • Open the SQL Server Management Studio tool from your Start menu (and pin it to your taskbar and start menu as well), and logon using Windows authentication.
  • Expand your server, then expand the Management folder.
  • Right-click on Database Mail, then select Configure Database Mail.
    • Click Next, then on the Select Configuration Task screen, choose the first (default) option for Set up Database mail... Click Next.
    • A popup warns that the Database Mail feature is not available and asks if you'd like to enable it. Click Yes.
    • On the New Profile screen, give your mail profile a name. I usually choose the SID of the SAP system I'm about to install.
    • Under SMTP accounts click Add.
    • The New Database Mail Account window pops up. Fill in the following fields (those not specified are optional and can be left blank or at their default):
      • Account name: You can again use the SID just as you did for the profile name.
      • Outgoing Mail Server (SMTP)
        • E-mail address: Here I usually enter something like "SID@server.domain.com", using your SAP system's SID, your SQL Server host name as the server, and your organization's domain (or that of your mail server) for "domain.com".
        • Server name: This is the host name of your email server, not your SQL Server. Enter it fully-qualified, i.e. "mail.domain.com".
      • The other fields on this screen should only be changed if your mail server requires it, or to fit your organization's email connection standards. Most often the defaults work fine.
    • Click OK.
    • You are now back on the New Profile screen, and your SMTP account shows up in the list. Click Next.
    • On the Manage Profile Security screen, on the Public Profiles tab, your SID profile shows up in the list. Select the checkbox next to it under Public, and switch the property under Default Profile to Yes. Click Next.
    • On the Configure System Parameters screen, review the defaults and click Next.
    • On the Complete the Wizard screen, review the actions and click Finish.
    • If all goes well, you should see a "Success" message. Click Close.
  • Back in the Management Studio's Object Explorer, right-click Database Mail again, and choose Send Test E-Mail.
    • Your newly-created Database Mail Profile should be shown. In the To: field enter your own email address, then click Send Test E-Mail.
    • In a moment you should receive an email in your inbox. If it worked, click OK on the test popup window.

 

SQL Agent

While humming the tune SQL Agent Man to yourself, refer back to your Upgrade to and Installation of SQL Server 2012 in an SAP Environment guide and open it to chapter 5, Setting the SQL Server Agent Configuration. Keep that handy.

 

  • In SQL Server Management Studio, expand SQL Server Agent.
    • Right-click Operators and select New Operator.
      • On the General page, enter the name of a system administrator or group to receive alerts from this SQL Server. If it's not a production system, this might be just yourself. Make sure the Enabled checkbox is checked.
      • Under Notification options, in the E-mail name field, enter the email address for the system administrator or group distribution list (or yourself) as appropriate. Click OK.
    • Right-click SQL Server Agent and select Properties.
      • Switch to the Alert System page.
        • Under Mail session select the checkbox for Enable mail profile.
          • In the Mail profile field, select the SID mail profile you configured earlier. Don't worry if the Test button is greyed out.
        • Under Fail-safe operator select the checkbox for Enable fail-safe operator.
          • The operator you created earlier should already be defaulted; if not, select it.
          • Next to Notify using: select the checkbox for E-mail.
      • Switch to the History page.
        • Per the instructions from the installation guide, set the following parameters:
          • Maximum job history log size (in rows): 6000
          • Maximum job history rows per job: 500
      • Click OK.

 

Alerts

Now it's time to configure some alert notifications for general error conditions that hopefully will not occur.

 

  • In SQL Server Management Studio, expand SQL Server Agent (if not still expanded from the previous section).
  • Right-click Alerts and select New Alert.
    • On the General page, give the alert a name of 11 - Specified Database Object Not Found.
      • Under Event alert definition, in the Severity field, select severity 011.
    • Switch to the Response page.
      • Select Notify operators and the checkbox under E-mail next to your operator's name.
    • Switch to the Options page.
      • Under Include alert error text in: select the checkbox for E-mail.
      • Set Delay between responses to 30 minutes.
    • Click OK.
  • Repeat this process, creating new alerts for severity conditions 013 through 025. I like to name the alert using the severity number and name so it is logically ordered and descriptive when looking at them in the Management Studio.

 

When you've finished, your list of alerts should look like this:

 

sql alerts.png

 

Later, after you've installed your SAP system, you may want to add additional database-specific alerts (for instance, Transaction Log Full) which can, in addition to notifying you by email, kick off a process, such as a log backup.

 

Configuration Parameters

Now it's time to open Note 1702408: Configuration Parameters for SQL Server 2012.

 

It is not necessary at this time to set any trace flags, as SWPM will set the appropriate ones automatically during the installation of your SAP system (though you will want to review them afterwards to ensure this happened correctly).

 

The Note walks you through setting parameters via SQL Query commands. However, many of them can also be set using the Management Studio's GUI options, and it's not a bad idea to familiarize yourself with the tool this way.

 

  • In SQL Server Management Studio, right-click on your server name (top of the list in Object Explorer) and select Properties.
  • On the General page, make a note of the number (in MB) shown for Memory. This should match the total physical memory installed in your server.
  • Switch to the Memory page.
    • Here the fields Minimum server memory (in MB) and Maximum server memory (in MB) equate to the configure options mentioned in the Note min server memory (MB) and max server memory (MB). How much you set these values to depends on your particular landscape and how much else is also on this server, and the Note gives some general guidelines for making a decision. No matter what you choose, you are going to set both parameters to the same value, so SQL Server will use a fixed amount of memory.
    • If this is a dedicated database server, subtract between 1.5 GB and 6 GB from your total physical memory (as described), convert the remainder into MB, and enter this value in the fields.
    • If this is a central instance, i.e. you will be running both SQL Server and an SAP application instance on this box, then divide your total physical memory by three and enter that number into the fields (i.e., give SQL Server 1/3 of the physical memory). Later, after installing and running your SAP application for a while, you may find you need to adjust this number, but this is a good starting point.
    • If you have a previously installed system with the same amount of physical memory and the same release of NetWeaver application installed, you may find that the installer set a different default for the SQL Server memory. If so, and that system is running well, you may want to use that number again here. For instance, on a machine with 48 GB of physical memory and one NetWeaver AS Java 7.4 instance co-hosted with SQL Server 2012, the number might be 19656.
  • Switch to the Database Settings page.
    • Under Backup and restore, select the checkbox for Compress backup.
  • Switch to the Advanced page.
    • Under Parallelism, set Max Degree of Parallelism to 1.
      • There are exceptions to this rule, generally for BW systems, but in almost all other cases this will give better performance.
  • Click OK.

 

Now again right-click on your server name and this time select New Query.

 

  • In the query window, type:
    • sp_configure 'show advanced options', 1
    • reconfigure with override
  • Click on Execute (or hit F5 on your keyboard).
  • Edit the query command so that 'show advanced options' becomes 'xp_cmdshell'. Now the query should look like:
    • sp_configure 'xp_cmdshell', 1
    • reconfigure with override
  • Execute (or hit F5).
  • If you want to confirm all the settings you have made, delete all but the command sp_configure (or highlight just that part) and execute. You will get a list of all the parameters, their minimum and maximum values, and their configured and runtime settings.
  • Close the query window. Do not save changes.

 

The rest of the parameters described in the Note only apply after installing an ABAP system, so you are done with it for now.

 

Lock Pages in Memory

There is one additional, important setting that is not described in Note 1702408. It is, however, described in Note 1134345: Using locked pages for SQL Server. This setting cannot be made using the Management Studio, and it is the first reason why I recommended during installation to use an actual service account and not a builtin account (such as Local Service).

 

  • In Windows, click on Start and select Run.
  • In the Run dialog box, type gpedit.msc in the Open field, and click OK.
  • This will open the Local Group Policy Editor. On the left, expand Local Computer Policy -> Computer Configuration -> Windows Settings -> Security Settings -> Local Policies.
  • Select User Rights Assignment. A list of related policies appears on the right.
  • Find and double-click Lock pages in memory.
  • Click Add User or Group.
  • Type in the name of the domain user account used for your SQL Server service, then click Check Names.
  • Confirm that the correct account is returned, then click OK.
  • Confirm the account appears in the list, and click OK again.
  • The account should now show up under Security Setting next to Lock pages in memory.
  • Close the Local Group Policy Editor.

 

The setting requires a restart of SQL Server to take effect. Close the SQL Server Management Studio (if it's still open) and start the SQL Server Configuration Manager. On the left, select SQL Server Services, then on the right right-click SQL Server (MSSQLSERVER) and select Restart.

 

This setting will prevent SQL Server's buffer memory from being paged to disk, which can be a performance-reducing event. The Note gives more detail about this setting, and also some considerations around its use. However, in general, for the majority of installations, it will be appropriate to set this.

 

You can confirm the setting is operational after the restart by examining the most recent SQL Server Log in the Management Studio. Expand Management -> SQL Server Logs and double-click the one marked Current. In the log, near the beginning, look for the line Using locked pages in the memory manager.

 

System Maintenance Plans

It's time to get a backup of the system databases and set up some housekeeping jobs.

 

System DB Backup

When installing SQL Server, you set aside a dedicated drive for database backups, and created a folder there called Backup. If you didn't, now is a good time to do so. Under the Backup folder, create another folder called System. Eventually you will have others here as well, but this will do for now.

 

Back in the SQL Server Management Studio, expand the Management folder under your server. Right-click Maintenance Plans and select Maintenance Plan Wizard.

 

  • On the starting page, click Next.
  • On the Select Plan Properties page, give the plan a name: System DB Backup.
    • Under Schedule, click Change.
      • On the New Job Schedule page, set the frequency and time of the backup job as appropriate. For the system databases (Master, Model, and MSDB), I recommend daily backups.
      • Under Frequency, for Occurs: select Daily.
      • Under Daily frequency, for Occurs once at: set an appropriate time (e.g. 12:30:00 AM).
      • Click OK.
    • Back on the Select Plan Properties page, click Next.
  • On the Select Maintenance Tasks page, select the checkboxes for Back Up Database (Full) and Maintenance Cleanup Task. Click Next.
  • On the Select Maintenance Task Order page, use the Move Down or Move Up buttons so that Maintenance Cleanup Task is at the top of the list. Click Next.
  • On the Define Maintenance Cleanup Task page:
    • Under Search folder and delete files based on an extension, search for and select your \Backup\System folder.
    • In the File extension: field type bak (no periods).
    • Select the checkbox for Include first-level subfolders.
    • Under File age: set an appropriate amount of time you wish these backups to remain on disk (at least long enough for network filesystem backups to pick them up, and also long enough for any potential need for them to be covered). Two or three weeks is probably sufficient in most cases.
    • Click Next.
  • On the Define Back Up Database (Full) Task page:
    • For Database(s): select System databases.
    • Under Create a backup file for every database, select the checkbox for Create a Sub-directory for each database.
    • Set the Folder: to your \Backup\System folder.
    • Select the checkbox for Verify backup integrity.
    • Click Next.
  • On the Select Report Options page, click Next.
  • On the Complete the Wizard page, click Finish, then after the success notification, click Close.

 

Clean Up History

Now use the wizard to create a second maintenance plan.

 

  • Name the plan Clean Up History.
  • Give this plan a schedule frequency of monthly. A possible option is to occur on the first Saturday of each month at 2:00am.
  • Select the tasks Check Database Integrity, Update Statistics, Clean Up History, and Maintenance Cleanup Task.
  • There is no need to change the order of operation.
  • For Define Database Check Integrity Task, choose All databases.
    • Note, for ABAP systems it is often recommended to schedule this check via DBACOCKPIT, and it may be scheduled automatically on installation. In this circumstance, you may choose to restrict this check to your system databases.
  • For Define Update Statistics Task, again choose All databases and leave the other options at default.
    • Again, note, at least for ABAP systems it is generally recommended to not schedule this job, in part due to the large database sizes involved, and in part because it may not be necessary (autostats should handle this). So, you may choose in this circumstance to restrict this to system databases.
  • For Define History Cleanup Task, set an appropriate amount of time to retain the job history data. The default is 4 weeks, but I feel this is too short. I recommend 2-6 months.
  • For Define Maintenance Cleanup Task:
    • Set Delete files of the following type: to Maintenance Plan text reports.
    • Set Search folder and delete files based on an extension to the folder in your SQL Server installation where job log reports are kept. By default this is \Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log.
    • Set an appropriate file age. I suggest 2 months.

 

Maintenance Plan Notifications

The wizard does not include an option for setting email alerts in the event of job failure. However, you can set this on the jobs themselves.

 

  • Expand SQL Server Agent and double-click Job Activity Monitor.
  • On each of your two new jobs (Clean Up History.Subplan_1 and System DB Backup.Subplan_1), double-click to open the job properties.
  • Select the Notifications page.
  • Select the checkbox for E-mail and select your previously defined operator.
  • Click OK.

 

Now you will receive an email in the event one of these jobs fails for any reason.

 

Get a Backup

You're done with initial configuration of SQL Server and ready to proceed with installing SAP. Before you do so, I recommend that you get a backup of your configuration. In the Management Studio, expand Management -> Maintenance Plans and right-click System DB Backup. Select Execute.

 

Let me know if you find any errors in this guide, or if you have alternate suggestions.

SQL Server 2012 is relatively easy to install, and the installation guide from SAP is very clear. However, there are a few guidelines from my own experience that may be of benefit to others performing this installation for the first time. The following example assumes an installation on Windows Server 2012 R2.

 

SQL Server 2012

 

 

Obtaining Installation Guides, Notes, and Software

 

Critical Guides and Notes

The main installation guide is Upgrade to and Installation of SQL Server 2012 in an SAP Environment. It can be downloaded from the Service Marketplace (logon required) at http://service.sap.com/instguides -> Database Upgrades -> MS SQL Server.

 

The two most important SAP Notes (logon required) are:

  • 1702408: Configuration Parameters for SQL Server 2012
  • 1134345: Using locked pages for SQL Server

 

A list of other useful Notes, documents, and websites for reference appears at the end of this blog. Many of the performance and configuration Notes, such as for setting traceflags, are covered in Note 1702408. Others refer to settings specific to tables or ABAP databases and thus are not required for the initial installation.

 

Software

If you licensed SQL Server through SAP, then you can download the software from the Service Marketplace (login required) at http://support.sap.com/software/databases.html -> MS SQL Server -> Database -> MS SQL SERVER X86_64 -> MS SQL SERVER 2012/X86_64 -> Installation -> Microsoft SQL Server. Download all 8 files that collectively make up DVD material number 51047515. Run the self-extracting executable that makes up the first file, and it will automatically combine with the other 7 files to create a folder structure mirroring an installation DVD.

 

Service Pack and Cumulative Update

SAP's distribution comes with Service Pack 1 Cumulative Update 7. However, Microsoft has released both a new Service Pack and new Cumulative Updates for both Service Packs 1 and 2. Unfortunately, at this time Microsoft's support website for downloads does not make it easy to find the latest SP and CU, nor even to know which are the latest. Therefore I recommend checking the Microsoft SQL Server Version List blog found at http://sqlserverbuilds.blogspot.com. Scroll down to the SQL Server 2012 section and identify the most recent Service Pack and the most recent Cumulative Update for that Service Pack. At this time, that is SP2 and CU4. The link to the Service Pack will take you directly to a Microsoft download page. The link to the Cumulative Update will take you to a Microsoft page describing the CU. From there, you must supply your email address, and a few minutes later you will receive an automated email with a link to a temporary download location. Unzip both archives.

 

Preliminary Steps

 

Service Account

I recommend using a domain account as the service account for SQL Server (the installation guide recommends using Local System). This will be of use when setting configuration parameters such as using locked pages in memory. Create (or request) your service account now so that it is ready during the setup. The account should be configured according your organization's policies for service (non-interactive) accounts, i.e. the password should never expire and the account should not have a logon script associated with it. It does not require any special domain privileges beyond this.

 

.NET Framework 3.5

The SQL Server Setup program requires .NET Framework 3.5 SP1. This feature is not typically installed by default with Windows 2012 R2, and it is not included with SQL Server. It is available, however, on the Windows 2012 installation media. Assuming the media is available to you, you can install or enable it by launching Server Manager (by default, this tool launches upon logon to the server console, which I personally find annoying and usually turn off), then selecting Manage -> Add Roles and Features.

 

server manager add roles features.png

 

On the page Before you begin click Next.

On the page Select installation type choose Role-based or feature-based installation and click Next.

On the page Select destination server select your server and click Next.

On the page Server Roles make no changes and click Next.

On the page Features select .NET Framework 3.5 Features and its subcomponent .NET Framework 3.5 (includes .NET 2.0 and 3.0).

 

server manager select features.png

If the feature is already enabled (the checkboxes are already selected), then click Cancel. Otherwise, click Next and follow the prompts to complete the installation.

 

Windows Updates

Apply the latest after enabling .NET Framework 3.5 but before proceeding further. A reboot may be required.

 

Pagefile

This is a good time to set your desired pagefile size for your SAP installation. The size chosen will depend on many factors and is beyond the scope of this blog, but as a rule of thumb it should be a minimum of 20 GB. If your server has a mix of local and SAN storage, the general recommendation is to locate the pagefile on a local disk (which will likely be your C: drive).

 

Open Control Panel and select System and Security -> System -> Advanced system settings -> Performance: Settings -> Advanced -> Virtual memory: Change. Deselect Automatically manage paging file size for all drives. Choose the appropriate drive and set Custom size with Initial size and Maximum size at the same value. Click Set then OK. (Reboot usually required).

 

pagefile.png

 

Windows Explorer Preferences

This is optional, but to avoid annoyance I prefer to change a couple of these settings. In Windows Explorer select View -> Options -> Change folder and search options. On the General tab select Automatically expand to current folder. On the View tab deselect Hide extensions for known file types. This will make your life easier.

 

Drive and Folder Structure

My recommendation is to have a separate drive or volume for:

  • Backup
  • Operating System / Pagefile
  • SQL Server
  • SAP (/usr/sap)
  • TempDB
  • Transaction Log
  • Database (spread across 4, 8, or 16 drives, depending on number of logical processors)

 

On the Backup drive, create a Backup folder, and on the TempDB drive, create a TempDB folder.

 

Installation

 

Set Up Source Files

Copy the SQL Server installation media you downloaded from SAP to a temporary location on your server with at least 7 or 8 GB of free space (e.g. C:\source\SQLServer). Expand \Source\SQLServer\x86-x64\ServicePacks and create a subfolder called SPxCUy (where x and y represent the Service Pack and Cumulative Update numbers you downloaded, e.g. SP2CU4). Copy the SP and CU executables you downloaded from Microsoft into this subfolder. If there are older SP and CU folders present, you may optionally delete them.

 

Start Setup With Integrated SP and CU

Click the Windows Start menu icon, then the 'down-arrow' to get the Apps menu. From the apps, choose Run. In the Run dialog box select Browse and navigate to \source\SQLServer\x86-x64\EnterpriseEdition\setup.exe and open it. Back in the Run dialog, edit the 'Open' field to add the following command-line switches so that your command looks like this:

 

     C:\source\SQLServer\x86-x64\EnterpriseEdition\setup.exe /Action=Install /UpdateSource="C:\source\SQLServer\x86-x64\ServicePacks\SP2CU4"

 

This will eliminate the need to separately apply patches after the installation, as they will be included.

 

Setup

In general, from this point the setup will follow the installation guide as described in chapter 4.2 "Installing the SQL Server 2012 Database Server Software Manually." I will only highlight where I deviate from the instructions given in the guide.

 

Product Updates

After the License Terms page, you will see a Product Updates page where you can confirm that your Service Pack and Cumulative Update were correctly selected.

 

setup product updates.png

 

Feature Selection

In addition to the features listed in the installation guide, I recommend selecting Integration Services (under Shared Features). This is not required to run SAP, but if you choose to setup backups and other housekeeping jobs via Maintenance Plans in the SQL Server Management Studio, then you will need this component. If you choose to setup your backups and housekeeping via DBACOCKPIT, then this is not required.

 

I strongly recommend changing the default drives suggested by the setup program. By default, setup will recommend installing on your C: drive. I recommend having a dedicated drive for SQL Server, e.g. D:, and thus changing the Shared feature directory and Shared feature directory (x86) as shown below.

 

setup feature selection directories.png

 

Clicking the ellipsis after each entry box will give you an opportunity to create these folders directly.

 

Instance Configuration

Again, I strongly recommend changing the Instance root directory to be the same as the Shared feature directory you just set. Otherwise, leave the selection at Default instance and do not change the Instance ID.

 

setup instance configuration directory.png

 

Server Configuration

I do not recommend using Local System accounts as suggested in the guide. I recommend using a domain account for the SQL Server Agent and SQL Server Database Engine.

 

setup server configuration service accounts.png

 

After entering the service account ID and password, select the Collation tab to customize the server collation to SQL_Latin1_General_CP850_BIN2 as described in the guide.

 

Database Engine Configuration

After setting the Server Configuration with Authentication Mode and specified SQL Server administrators (i.e., set BUILTIN\Administrators for this), switch to the Data Directories tab. Change the Temp DB directory to the TempDB folder you created earlier, and the Backup directory to the Backup folder you created. There is no need to change the User database or log directories, as you will be creating multiple directories across multiple drives later when you install your SAP system.

 

After a few more screens, the installation will begin. It will take several minutes or so, so this is a good time for coffee. Later, when you come back, we'll talk about initial (pre-SAP installation) configuration of SQL Server (in another blog).

 

Additional Resources

 

SAP Notes

  • 1238993: Proportional File Auto-Growth with SQL Server 2008
  • 1459005: Enabling index compression for SQL Server
  • 1482275: Setting Traceflags for SQL Server
  • 1488135: Database compression for SQL Server
  • 1558087: SQL Server Statistics Maintenance
  • 1612283: Hardware Configuration Standards and Guidance
  • 1648817: Disallow Page Level Locks for Microsoft SQL Server
  • 1649078: Disabling autostats for certain tables with Microsoft SQL Server
  • 1651862: Release planning for Microsoft SQL Server 2012
  • 1676665: Setting up Microsoft SQL Server 2012
  • 1744217: MSSQL: Improving the database performance
  • 1725220: New Trace Flags set and recommended with SQL Server 2012

 

SCN Space and Documents

There are many good blogs and documents in the SAP on SQL Server space on SCN. One in particular to highlight for planning purposes is 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 (yes, that's a doozy of a title, and I have yet to see Part II, unfortunately).

 

External Resources

 

Please comment if you have any thoughts on any of my recommendations, as I am always open to learn new ways of doing things and to benefit from the experience of others.

 

UPDATE:

Please see SQL Server Configuration: Pre-SAP Installation for my personal recommendations on what to do next after initial installation.

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.


What?

Detail

Size

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


Status

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

9,8

0

9,8

-

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

1,3

13,3

14,6

+ 4,8

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

0,5

14,7

15,2

+ 0,6

After creating Columnstore indexes for the Infocube

0,4

1,5

1,9

- 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:


Query

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

12

8

3

Query 2

62

40

26

Query 3

30

17

 

Query 4

117

29

 

Query 5

245

27

 



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:


System

Total runtime for all tested queries [sec]

Source System (Oracle, no database compression)

2500

SAP Hana

300

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

360

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

308

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.

 

Problem

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)

 

Solution

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:

SQLCMD

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,
s.text,
start_time,
percent_complete,
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
WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')

 

bak1.png

 

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

 

SELECT command,
start_time,
percent_complete,
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
WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')

 

bak2.png

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

 

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

 

Regards,

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
:
1.png

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
:
2.png
  • Backup files in directories:
C:\usr\sap\<SID>\DVEBMGS00\exe
C:\usr\sap\<SID>\SYS\exe\uc\NTAMD64
  • 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).
3.png
  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:
4.png
Download, unpack and replace outdated system files with the new ones.
DBSL:
5.png
R3SZCHK, R3LOAD, R3LDCTL:
6.png
R3TRANS, R3TA:
7.png
TP, DISP+WORK:
8.png

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:

9.png

     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):

10.png
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:
USE
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:
USE
master
GO
ALTER
DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'D:\tempdb2005.mdf')
GO
ALTER
DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'D:\tempdb2005.ldf')
GO
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:

11.png

     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:

12.png
     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’:

13.png

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

14.png

     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:

15.png
     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:

16.png

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

17.png

     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.

18.png

 

     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.
Solution:
- 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.
Solution:
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. ======================================================================================

Phase of SPAM Update - CHECK_INACT_OBJECTS
Problem: SPAM import finished with the following error.
! There are inactive objects in the system
! Abort the import due to an error situation
Solution:
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.
OR
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)
Solution:
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
Solution:
Right from SPAM go to menu: GoTo -> Statu -> Queue (Click 'Approve adjustment').

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

           Problem: During SPAM package update GUI interface became unavailable
           Solution:
           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.0 - SPS 26 (SAP BASIS 26, SAP BW 28)

SAP EHP1 FOR SAP NETWEAVER 7.0 - SPS 11 (SAP BASIS 11, SAP BW 11)

SAP EHP2 FOR SAP NETWEAVER 7.0 - SPS 11 (SAP BASIS 11, SAP BW 11)

SAP EHP3 FOR SAP NETWEAVER 7.0 - SPS 03 (SAP BASIS 03, SAP BW 03)

SAP NETWEAVER 7.1 - SPS 14 (SAP BASIS SP14)

SAP EHP1 FOR SAP NETWEAVER 7.1 EhP1 - SPS 10(SAP BASIS SP10, SAP BW SP10)

SAP NetWeaver 7.2 - no restriction

SAP NETWEAVER 7.3 - SPS 07 (SAP BASIS 07, SAP BW 07)

SAP EHP1 FOR SAP NETWEAVER 7.3 - SPS 03 (SAP BASIS 03, SAP BW 03)

 

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.0 - SPS 27 (SAP BASIS 27, SAP BW 29)

SAP EHP1 FOR SAP NETWEAVER 7.0 - SPS 12 (SAP BASIS 12, SAP BW 12)

SAP EHP2 FOR SAP NETWEAVER 7.0 - SPS 12 (SAP BASIS 12, SAP BW 12)

SAP NETWEAVER 7.1 - SPS 14 (SAP BASIS SP14)

SAP EHP1 FOR SAP NETWEAVER 7.1 EhP1 - SPS 10(SAP BASIS SP10, SAP BW SP10)

SAP NetWeaver 7.2 - no restriction

SAP NETWEAVER 7.3 - SPS 08 (SAP BASIS 08, SAP BW 08)

SAP EHP1 FOR SAP NETWEAVER 7.3 - SPS 05 (SAP BASIS 05, SAP BW 05)

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

http://scn.sap.com/docs/DOC-29159

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

 

70swpm.png

 

Check note 1890950!

 

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

  • 70SWPM*.SAR
  • SWPM*.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)

swpm.png

Actions

Filter Blog

By author: By date:
By tag: