LOBApps

SAP on Windows and Anti Virus Scan

Fernando Armada — Tue, 05 Mar 2013 13:48:00 GMT

Anti-Virus Exclusions

Microsoft generally recommends that servers are protected against viruses using anti-virus software. The alternative is to adequately harden and lower the software attack surface of the server, together with physical network segmentation and firewalls etc. For customers that will deploy anti-virus software, Microsoft has some specific guidance for configuring antivirus exclusions on Window/SAP/SQLServer environments.

In particular it is our recommendation that database files should not be scanned. Online scans cause additional load on IO, it may also cause some contention issues. It is therefore required to exclude some files or directories from AV scans in order to protect the performance of a SAP installation.

Previous guidance was detailed in the following blog entry:

http://blogs.msdn.com/b/saptech/archive/2010/06/21/sap-on-windows-and-anti-virus-scan.aspx

This blog entry is now a little outdated and in some cases there was not specific guidance on what should be excluded based on what SAP software and components that are installed.

For the most commonly installed software and components here are specific recommendations.

1) SAP Anti-Virus Exclusion (Excludes the complete directories and Sub Directories)

SAP ABAP or Java installs
- \usr\sap\
SAP Content Server Install
- \SAPDB\
SAP Printer Server
- SAPSprint.exe
Servers where are SAPGui is installed
- lsagent.exe
During SAP installs or upgrades, it is a good idea to exclude the base SAPinst directories and subdirectories.

\program files\sapinst_instdir\

2) Microsoft SQL Server 2008/2008 R2/2012 AV Exclusions

A. SQL Server data files:

.mdf - Primary Data file.
.ndf - Secondary Data files.
.ldf - Transaction Log file(s).

In some cases it might not be enough to exclude the data files, it might also be necessary to exclude the directories that contain the data files. Normally on SAP systems the SQLServer files are in their own individual directories e.g.:

\SAPDATA?\

If there are more than 10 SAPDATA directories it might be necessary to use the following as not all antivirus products use the asterisk (*) on directories exclusions:

\SAPDATA??\

B. SQL Server backup files:

*.bak - Database backup files.
*.trn - Transaction Log backup files.

3) Windows 2008/2012 Exclusions.

a. Turn off scanning of the Microsoft Forefront "tmp.edb" file

If you are using Forefront, turn off scanning of the Forefront database file (tmp.edb). This file is located in the following folder:

%windir%\SoftwareDistribution\Datastore

b. Turn off scanning of the log files that are located in the following folder:

%ProgramData%\Microsoft\Search\Data\Applications\Windows

c. Turn off scanning of Windows Update or Automatic Update related files

Turn off scanning of the Windows Update or Automatic Update database file (Datastore.edb). This file is located in the following folder:

· %windir%\SoftwareDistribution\Datastore

d. Turn off scanning of the log files that are located in the following folder:

%windir%\SoftwareDistribution\Datastore\Logs

Specifically, exclude the following files:

Res*.log
Edb*.jrs
Edb.chk
Tmp.edb

e. Turn off scanning of Windows Security files

Add the following files in the %windir%\Security\Database path of the exclusions list:

*.edb
*.sdb
*.log
*.chk
*.jrs

Note if these files are not excluded, antivirus software may prevent proper access to these files which can result in the security databases becoming corrupted. Scanning these files can prevent the files from being used or may prevent a security policy from being applied to the files. These files should not be scanned because antivirus software may not correctly treat them as proprietary database files.

f. Turn off scanning of Group Policy related files

Group Policy user registry information. These files are located in the following folder:

%allusersprofile%\

Specifically, exclude the following file:

NTUser.pol

Group Policy client settings file. This file is located in the following folder:

%Systemroot%\System32\GroupPolicy\

Specifically, exclude the following file:

Registry.pol

g. Windows Server Failover Cluster Service (Only if is deployed)

The %Systemroot%\Cluster folder.
The temp folder for the Cluster Service account. For example, exclude the \clusterserviceaccount\Local Settings\Temp folder from virus scanning.
The path of the \mscs folder on the quorum hard disk. For example, exclude the Q:\mscs folder from virus scanning.

If your antivirus is Microsoft Forefront Endpoint Protection (FEP) you may use the Preconfigured Policy Templates for different server roles and Microsoft applications.

References:

http://social.technet.microsoft.com/wiki/contents/articles/953.windows-anti-virus-exclusion-list.aspx

Enterprise Configuration Recommendations:

http://support.microsoft.com/kb/822158

Windows:

http://support.microsoft.com/kb/822158

Windows / Active Directory:

http://support.microsoft.com/kb/822158
http://support.microsoft.com/kb/837932
http://support.microsoft.com/kb/943556

Cluster:

http://support.microsoft.com/kb/250355

SQL:

http://support.microsoft.com/kb/309422

Taking Advantage of IT Consolidation Efforts for Standardizing Environments: Part 1

Jock Williams — Tue, 08 Jan 2013 21:53:00 GMT

Today many customers are looking at ways to consolidate their IT systems. By way of application consolidation through hardware consolidation using partitioning methodologies like LPARs, database consolidation using Named Instances, or by way of the most popular method today which is virtualization using hypervisors like Hyper-V and VMware. As many of you already know, the consolidation of these IT components help to lower cost of ownership by reducing the number operating systems or databases, reducing hardware footprint in datacenters, and even improving DR Failover. Now most customers today have already consolidated or have begun working on a strategy to consolidate their environments. However, in most cases they may have missed the mark when it comes to truly taking advantage of all the benefits consolidating an IT infrastructure could have. Our goal with this series of blogs is to help customers start thinking of how they can use major IT engagements like consolidation projects to introduce new or revamp existing IT standards. For most of you reading this, the next response is more than likely “what do you mean”!

Over the past 6 years the Microsoft LOB CoE has been heavily focused on working with new customers on replatforming and deploying SAP on the Microsoft platform. During those years our customers have been asking the CoE to focus on how they can get the most out of their consolidation (or replatforming) efforts during these engagements. Based on this we have started taking into consideration other areas that impact IT which we call “Value Added Services”. Items that we place emphasis on include Standardization, Optimization, Streamlining and Simplification of the architecture for deployment, testing, maintenance and overall operational services.

For this series of blogs we will focus on Standardization of the platform architecture (application, database, server, etc…) for projects like consolidation efforts. Standardization provides customers with the ability to define templates for rapid deployment of applications, simplify operations and administration, provide consistency in application and platform behavior whether daily operations or during testing. It also allows for alignment of IT with the Business and its expectations surrounding IT’s behavior in supporting OLAs, which support SLAs. The standards mentioned in this series of blogs can also be incorporated for Replatform and New Deployment projects.

SAP components used as examples will SAP ECC, BI, APO, Solman and PI. So the first question is where to start! With customers planning the consolidation of SAP environments (new or existing) on the Microsoft platform, we start with sizing the environment. Sizing the environment is a practice for all existing and new SAP customers and done by way of either gathering statistics or using the SAP Quicksizer tool. This information from either method will be used to provide a sizing footprint that can be used for creating some of the standards for our consolidation efforts. How is that? Well an SAP sizing, using Quicksizer for new deployments or information gathered for existing environment will be utilized to determine the number of SAPs (SAP performance metric) for each SAP application environment (Development, Test, Production, etc…). Next the hardware vendors will take the SAPs sizing information and convert it into a hardware sizing that includes the number of processors (cores), memory, disk space and so forth. The sizing process usually runs about 1 – 2 months depending on the customer, new or existing implementation, hardware vendor, and back and forth discussions. Here is an example of the results for a new virtual environment from such an effort:

As can be seen we now know the amount of resources required for our SAP environments. Although this does not show a detailed breakdown it gives you an idea of what you are working with from a resources point of view. A deeper dive into the spread sheet would reveal a breakdown of number of VMs per environment, development, test, sandbox, production and other systems making up the landscape.

Based on the sizing information from the hardware vendor we can now focus on writing our standards that will align with our consolidation methodology. For the consolidation methodology we will utilize the following standard developed from the pharmaceutical customer engagement. Using this as an example will allow us to simplify this discussion and avoid a lengthy conversation over a standard that took weeks to agree upon. The consolidation methodology will consist of the following:

All Application Single Points of Failure will use clustering technology in order to achieve a four 9s’ Availability of Service requirement
- All Central Services Instances will be clustered
- All Central Services Instances will be part of a SAP Multi-SID cluster
All SAP Applications will be virtualized (includes all SAP Application Servers, Central Services and Bolt-On Applications)
All SAP Application Databases will be on physical servers (Dev, Test, Prod, etc…)
All SAP Application Databases will be consolidated to 4 node clusters using named instances
All Bolt-on Application Database will be virtualized (these are small instances)
All HA methodologies will be used in Development, Test and Production environments for testing and validation purposes.
Architecture will be designed for rapid deployment of applications in alignment with the current Service Catalog
Architecture will be designed for ease of administration

So these are just a few of our requirements, but enough to get us going on standardizing our configuration standard for the virtual environments. Many of these standards will be carried forward into Part 2 of this blog when we focus on standards for our database consolidation effort. However, keep in mind that this is a pharmaceutical customer we are using as an example and therefore we have standards related to qualified and non-qualified environments we must follow for compliance with government standards.

Now we take all of the above information and begin to develop our standards. Some have already been outlined for us in the previous bullets. For example, we know we will use clustering to eliminate SPOFs, SQL Server Named Instances for consolidation of DBs, Multi-SID for consolidation of Central Services and all applications will be virtualized. Let’s start with our virtualization standard. We first need to develop VM footprints for the SAP applications as part of the virtualization standards. Based on the previous sizing information and the more detailed spreadsheet (not seen here), we can easily deduct that we will have VM sizes from X-Small to X-Large as part of our configuration standard. We refer to this as our T-Shirt sizings. The virtual server throughput capacity listed in the ‘Estimate SAPS’ column (not shown) is an extrapolation based on the number of vCPUs as a fraction of the total CPUs in the physical servers. The associated virtual resources allocated to each category of virtual machines are summarized in the table below: VMs that will look like the following:

Application Server VM T-Shirt Sizing

VM Sizes	vCPU	Memory (GB)
X-Small	1	4
Small	2	8
Medium	2	16
Large	4	32
X-Large	4	64

Next we define the number of work processes we can deploy per image and add a column to represent this. The following was based on a combination of SAP standards found in SAP OSS Notes and the customer’s experience.

Application Server VM T-Shirt Sizing (modified)

VM Sizes	vCPU	Memory (GB)	ABAP WP
X-Small	1	4	9
Small	2	8	18
Medium	2	16	30
Large	4	32	60

As part of the standardization process we also need to develop a pattern for the types to be utilized on each of the SAP Application VM. The distribution of WP between the different types must fit into the model above. This is again based on SAP standard and customer experience. So results will vary by customer. Below we are using only Medium and Large as an example, but the information is developed using guidance from SAP OSS Notes and resources, and customer historical experience:

Example of WP Type Breakdown

VM Size	Dialog	Batch	Spool	V1 Update	V2 Update	Reserved	Total WP
Large	32	10	2	4	2	10	60
Medium	16	5	1	2	1	5	30

As can be seen we have defined the number of Dialog, Batch, Spool and Update process that each image will have. The next step following this would be to define the volume sizes for the VMs. For example, page file size and volume size would be based on the amount of memory each VM has. Following that you would look at defining the type of drivers, NIC team, patching levels to be used as part of the VM standard. An example of this would be would be using Secure Path over MPIO for the Virtual Host Bust Adapter, since the VMs would reside on a SAN. Another example would be NIC configuration Load Balancing over Fault Tolerance.

Now that we have defined the VM standards for size of VMs, number of work processes, and process types, we need to define which applications fit into which templates or T-Shirt sizing as we call it. Below is an example of how we broke up ABAP and JAVA instances to fit into the T-Shirt sizing.

Applications Mapped to T-Shirt Sizing

Application	VM Size
BI (ABAP)	Large
BI (Java)	Medium
ECC (ABAP)	Large
ECC (Java)	Medium
PI (ABAP+Java)	Large
PI AdapterEngine (Java)	Small
SCS/ASCS	Medium
Solman (ABAP+Java)	Large

Note: Although Java instances are listed above, they are not referred to in Part 1 of this blog.

Once we have completed defining the standards for the VM standards in our consolidation effort, then the next step is to define our Host system standards. The standards will be the following:

Two Host Server Cluster environments will exist.
- The first Virtual Host environment (Host Cluster A) will house SAP Application Servers and Bolt-On Application VMs.
  - High Availability and Fault Tolerant capabilities will be utilized for the availability of the Application VMs.
- The second Virtual Host environment (Host Cluster B) will house clustered SAP Central Services and other applications requiring clustering of SPOFs.
  - All SAP Central Services will be deployed on Virtual Cluster using SAP’s Multi-SID install methodology.
  - All additional applications requiring clustering will be housed in (Host Cluster B)
  - Affinity and Anti-Affinity rules will be utilized to avoid Guest VM nodes running on the same Host.
- General Standard
  - Each host will consist of two Dual Port HBAs
  - Each host will consist of three Dual Port NICs

There are many more areas to address than listed here from standardization point of view. However, since this is provided as part of the Architecture Design Workshop conducted by the LOB CoE during the design phase of the engagement, I am excluding some of these items. In other words, I do not want to give away too much in this blog J, but provide just enough for customers to get the picture. Consolidation, replatforming or new deployment projects provide customers with the perfect opportunity to take advantage of implementing or updating IT standards for platform, infrastructure or applications. Focus on using standards to simplify deployment, administration and maintaining of applications. In closing, the above standards were designed to fit the needs of the customer that we worked with and are based on Industry, SAP, Microsoft and historical customer data for an existing SAP environment. These standards were not meant to meet the needs of all customers, where dramatic variances could be introduced based on many factors, including existing or new deployment, historical data and platform version (Server/OS/DB). These standards are meant to show customers what can and should be done during Architecture Design Workshops for consolidation, replatforming, and new deployment projects, which provide IT with the opportunity to introduce or revamp IT standards.

For customers interested in learning more about Architecture Design Workshops delivered by Microsoft’s Line of Business (LoB) CoE. Please email the LoB CoE Team at lobapps@microsoft.com.

This blog and other blogs included in this series were developed from material created by the following SMEs and Architects:

Ben Trinh
Chad Cox
Ben Ellsworth
Grant Carter
Fazil Osman

The above resources were also contributor to this blog, which again the first in a series of many.

How Do I Manage Multiple Servers Effectively Using SQL Server Central Management Server (CMS)

PankajAgarwal — Tue, 01 Jan 2013 18:59:00 GMT

I would like to spend some time discussing one of the wonderful multi-server management features built into SQL Server that can help DBAs manage a wide and deep environment typically associated with SAP. This blog post is dedicated to discussing the Central Management Server feature that was introduced in SQL Server 2008 version. The discussion in this topic is applicable to SQL Server 2008, 2008 R2 and 2012 (screenshots included in this post are captured on a server running SQL Server 2012)

Introduction

As a DBA managing an SAP landscape, one of the biggest challenges you face is keeping all servers in sync with different policies, changes, software updates and patches. Its all too common to find that an sp_configure setting such as max degree of parallelism has been modified inadvertently on one of the servers. Such small changes could lead to confusion, performance issues and ultimately end-user dissatisfaction. Fortunately all versions of SQL Server including and beyond SQL Server 2008 provide several features that simplify the often frustrating task of managing multiple servers through a single SQL Server Management Studio (SSMS) window.

What is a Central Management Server?

Central Management Server (CMS) was introduced in SQL Server 2008 as part of management feature enhancement. This feature simplifies the task of managing and monitoring multiple SQL Server instances installed on different physical servers. CMS is implemented as a set of server groups in SSMS. The biggest benefit of this functionality is that a single TSQL statement (with some restrictions) can be executed on all registered servers within a server group in a single execution. The results of such execution is presented as a single result set, which further simplifies the collection and analysis of this data.

What is a Server Group?

A server group is a group of registered SQL Server instances that typically require similar management parameters such as configuration settings, scheduled tasks etc. A single SQL Server instance can be added to multiple server groups. This provide DBA the opportunity to decide how to categorize and customize the servers groups. Most commonly deployed server groups may be based on the SQL version (SQL 2008 R2, SQL 2012), geographies where the servers may be placed (eg. Local Datacenter, Backup Datacenter), purpose the servers serve (eg. Development, Staging, Production) etc. In a nutshell, server groups should be created to facilitate the management task and should align with any grouping policy used by the enterprise or the DBA.

As mentioned earlier a single SQL Server instance could be a part of multiple server groups. In the examples demonstrated in this blog, I use a single Server Group, however I would encourage you to group your servers into multiple server groups based on the management criteria that best suits your needs.

How do I setup Central Management Server?

CMS is typically installed on a single SQL Server instance that is used to manage the entire SQL Server presence in the enterprise. Ideally a non-production instance of SQL Server should be used as CMS. Hence the first step to setting up a CMS is to decide which server will play this role in your SAP landscape. CMS metadata is stored in MSDB (table names begin with sysmanagement_shared prefix). This means that the information related to CMS can be backed up using the regular backup/recovery process that includes the MSDB database.

Follow the steps in this Books Online topic to create a new CMS.

Step 4 in this topic walks through the creation of a Server Group. Repeat this setup multiple time, once for each of the groups you would like to setup.

Steps 5 and 6 in the process walk through registering servers under the Server Group created in step 4. Repeat steps 5 and 6 multiple times once for each of the servers you would like to register under each of the groups.

At the end of the setup process, you will have a server structure that is grouped by server groups. Server group now becomes the atomic unit of TSQL command execution thereby facilitating server management through a single SSMS window.

Security Requirements

Users who will be managing the CMS structure should be member of the ServerGroupAdministratorRole role in the MSDB database. It is highly recommended that Windows Authentication be used to connect to the servers being managed through CMS. This choice is made when setting up servers in the CMS structure in the previous section. If Windows Authentication is used to configure security for registered CMS servers, login credentials for the logged in user are used to connect to each of the CMS targets.

How can I leverage Central Management Server to manage a SAP landscape effectively?

Once the CMS is setup, managing the target servers becomes an easy task. Commands such as the one required to check server collation can be executed on all servers in a group with single execution. This capability allows an administrator to perform auditing and standardization tasks across multiple SQL Server instances, installed on multiple machines with a single command. Following are some example of how to undertake such tasks.

Note: Screenshots in the sections below are captured in a lab environment in order to demonstrate the capability of the Central Management Server functionality and are not representative of a production environment.

Auditing Tasks

Scenario 1 - Checking to ensure Max Degree of Parallelism is set to 1 across all servers in the SAP landscape.

In this scenario, an administrator can quickly check to ensure that the sp_configure setting is set correctly to 1.

Scenario 2 - Monitor the database properties and ensure recovery model and page verify options are set correctly

The following screenshot demonstrates how an administrator can utilize the CMS functionality to monitor the database properties for databases on all servers registered under the CMS.

Scenario 3 - Ensuring all necessary trace flags are enabled on each server.

In this scenario, an administrator ensures that the trace flags recommended for SAP are turned on each of the servers in the SAP landscape.

Standardization Tasks

Scenario 1 - Setting the Max Degree of Parallelism.

In this scenario an administrator can use CMS to set Max Degree of Parallelism for all servers to a specific number. For SAP non-BI systems, in general the recommendation is to keep Max Degree of Parallelism at
1. The following code sets the Max Degree setting to 1 for all servers in the Central Management Server group.

Next Steps

Central Management Server provides a launch-pad for an administrator to standardize configurations in environments where there are more SQL Server than can be managed manually. The next step in this standardization journey is to explore SQL Server's Policy Based Management feature. For more information on how to use Policy Based Management in SAP environments, please review the blog post at this location.

Conclusion

Central Management Server presents a quick and easy method to manage multiple instances of SQL Server from a single SQL Server Management Studio console. It provides the capabilities that a DBA would need when faced with the responsibility of maintaining a standard configuration across an environment with many SQL Server instances such as managing an SAP landscape. As illustrated by the content and examples above, this management task can be simplified by planning and implementing Central Management Server. I would highly encourage DBAs managing SAP environments to explore this feature and use it to simplify and streamline their daily tasks.

Acknowledgements

I would like to thank the following colleagues for their valuable feedback associated with the content presented in this blog.

Fazil Osman
Mark Weber
Cameron Gardiner
Jack McCullough

References

SQL Server 2012 MSDN Books Online

SAP on SQL Server Blog

Universal Drive Mappings for SAP Service Accounts

Fazil Osman — Wed, 12 Dec 2012 22:54:00 GMT

Scenario Description

On the Microsoft Windows application platform, SAP application servers cannot access a network resource using a simple mapped network drive. This issue is caused by the isolation between various user accounts running on the Windows environment. In essence, a drive mapping by the administrator cannot be accessed by a different user account like that of the SAP Service <SAPServiceSID>.

Background

SAP application instances are installed on the Microsoft platform to run as a specific Windows service account <SAPServiceSID>. You can see this easily when looking at the installed services and how the logon security is defined for that service, as shown in the two screenshots below:

The SAP service account <SAPServiceSID> is normally created with limited privileges (by the SAP installer) and specifically cannot logon or interact with the desktop. Below is an example of trying to launch a CMD window as the SAP service account <SAPServiceSID> and the associated error which demonstrates this issue:

Now, let’s say that I want to login with the interactive SAP administrator account <sidadm> and map a network drive M: to the location \\SAPIDES\share. I then want this drive mapping to be accessible from all accounts and specifically from the <SAPServiceSID>.

Let’s start with setting up the share. First, I created a directory on the Z: drive and then a share called “share”. I set the permissions to have the SAP_IDE_GlobalAdmin (SAPServiceIDE is a member) have full control as shown below:

Next, I map a drive from the command prompt as shown:

Below, you can see that the M: network drive is accessible. I called the directory listing and also displayed the content of the TestFile.txt. Additionally, you can see that Windows Explorer also now has access to the share via the M: drive.

The problem is that this drive mapping is only available to the <sidadm> account and as show with AL11, the SAP application instance cannot access the drive. When I try to access the mapped drive M:\ you can see the error message being thrown on the status bar.

Solution #1 – Using UNC (Universal Naming Convention)

There are a few ways to solve this issue. Firstly, do not use mapped drives but instead use UNC (Universal Naming Convention) naming conventions. As shown below, I can access the network share by simply changing the AL11 entry to use UNC (Universal Naming Convention):

I can now see my TestFile.txt and even the content in the next two screenshots:

Solution #2 – Using a Symbolic Link from a local file system

Second, you can use a symbolic link that is accessible from any local drive. In my case, I will create a link from the \usr\sap (SAPMNT) location to the shared drive. I then update the AL11 entry as shown to point at this new location:

Again, I can now see my TestFile.txt and even the content in the next two screenshots via the symbolic link.

More information on MKLINK can be found at: http://technet.microsoft.com/en-us/library/cc753194(v=ws.10).aspx

Solution #3 – Map the network drive with higher privileges

Finally, for those who cannot let go of the mapped drive concept you can have this by mapping the drive using the SYSTEM account. This essentially makes the mapped drive available to all users on the local machine. There are a few steps to making this work:

Step #1: Create a small batch/command file as shown:

Step #2: Next, create a Windows task schedule that will run the batch file at system startup. The screen shot below shows the overview:

Step #2.1: There are some parameters that are needed here to make this work correctly. As shown, change the user to SYSTEM and also run the task with the highest priviledges.

Step #2.2: Trigger the task to run at system startup with a 1 minute delay. This delay is to ensure that the network and other underlying services are available before the script is run.

Step #2.3: You can see from the screenshot below that this task has been configured to launch my ZMap.cmd script as its default action.

Step #2.4: Finally as an extra precaution, I will use the condition that my corporate network connection is available before running the action. This together with the delay ensures that the network is available and ready for the drive mapping command (net use).

So, now I will test the command by running it manually from the task scheduler:

You can see that a new drive mapping has been inserted into my File Explorer:

Back to AL11, and setup the original entry that through the error:

But now, we can access the M:\ drive via the SAP system due to the mapping running under the SYSTEM account.

“DON’T TURN SAP SERVERS INTO FILE SERVERS!!!!”

Turning an SAP application or database server into a network file server can have disastrous impacts on the security of the system. More and more of our customers want our solutions to be secure by default. This means no direct connections between clients and SAP infrastructure.

We highly recommend and suggest that customers to have a File Server *outside* their SAP VLAN. Further, we recommend using the SAP transaction FILE to define LOGICAL FILE NAMES as shown below:

Step #1. Create an interface path – here “BANK DATA” – but you could create one called “INTERFACE_UPLOAD_DOWNLOAD”

Step #2. Click here

3. Press New Entries

4. Specify UNC path – we don’t need drive letters

5. Save and transport from development (DEV) to production (PRD).

Acknowledgements

Special thanks to the Jock Williams, Cameron Gardiner, Mark Weber and Juergen Thomas for their contributions to this blog entry.

How do I work with Trace Flags?

PankajAgarwal — Tue, 28 Aug 2012 23:38:00 GMT

Introduction

As a SQL Server DBA, chances are that you've come across situations where you had to add/remove trace flags to your SQL Server instances. You may have wondered as to what these trace flags are used for and why do we need them anyway. I'd like to use this blog post to introduce you to the concept of trace flags and why they are needed. Additionally, we will explore the recommended trace flags recommended by Microsoft for use with a SAP deployment running on SQL Server 2008 R2 and SQL Server 2012.

What is a Trace Flag?

In easiest of terms, SQL Server Trace Flag is an indicator for the SQL Server binary to perform an action in a non-default manner. In other words, when using a trace flag, you're requesting the SQL Server engine to use functionality that would not be considered default. The most common scenarios where trace flags are required revert the SQL engine behavior to a previous version or by-pass newly introduced functionality. In other cases, trace flags are used to enable or disable functionality that may help troubleshoot specific issue(s).

There are 2 types of trace flags in SQL Server. Let's discuss the differences between these 2 types now.

Global Trace Flags

A Global level trace flag is enabled for the entire time SQL Server service is running and is applied at the server level. This means that the behavior associated with a global trace flag is applied to each of the client connections. There is no way for the clients to bypass such trace flag. Hence a DBA should always monitor and control the Global trace flags deployed in
production environment.

Session Trace Flags

A session trace flag can be enabled/disabled at the client session level. Any behavior changes associated with session level trace flag is only applicable to the client connection that enables this trace flag. Once the client connection is closed, the behavior reverts back to default.

Enabling/Disabling and Monitoring Trace Flags

There are 2 ways to enable and disable trace flags. This section describes the options available and what each of the options offers.

DBCC TRACEON/DBCC TRACEOFF commands

DBCC TRACEON and TRACEOFF commands allow enabling/disabling trace flag once the SQL Server has been started. These commands allow enabling both Global and session level trace flags. Any trace flags enabled using the DBCC TRACEON command can be disabled using the DBCC TRACEOFF without having to restart the SQL Server service.

A global trace flag can be enabled through DBCC TRACEON using the "-1" value for the last parameter when executing this command. For example, the following command enables trace flag 1204 globally. Any global trace flags enabled/disabled using this method do not require restart of SQL Server service. However it should be noted that any trace flags enabled using this method only exist while the SQL Server service is running. If the SQL service is restarted, trace flags have to be enabled again.

Screen clipping taken: 8/23/2012 5:50 PM

Executing the DBCC TRACEON and TRACEOFF command requires the login to be a member of the sysadmin fixed server role.

SQL Server Configuration Manager

If SQL Server Configuration Manager is used, trace flags can be added to the Startup Parameters tab using the -Txxxx format. Following dialog illustrates how to add trace flag 1204 globally for an instance of SQL using SSCM. Please note that any trace flags enabled using this method take effect only after restarting SQL Server service. Additionally, any trace flags enabled using this method are applied in global scope only. Hence all users connecting to SQL Server instance will experience the functionality associated with such trace flags.

WARNING: SINCE THE FUNCTIONALITY MENTIONED BELOW UPDATES THE REGISTRY, ANY INCORRECT TEXT ENTERED FOR STARTUP PARAMETER MAY LEAD TO FAILURE OF SQL SERVER SERVICE AT STARTUP. PLEASE ENSURE THAT THE FORMAT DEMONSTRATE IN THE SCREENSHOT BELOW AND DESCRIBED HERE IS USED.

Monitoring Trace Flags

Use the DBCC TRACESTATUS command to monitor the status of trace flags on an instance of SQL Server. If this command is executed with the single "-1" parameter, it lists all enable trace flags for the session and instance of SQL Server. The output also indicates which trace flags are enabled globally and which are enabled for the session only.

SAP OSS Note 1482275 describes the steps mentioned in the above section also.

Enabling Trace Flags on a Failover Cluster Instance of SQL Server

SQL Server stores the trace flag(s) enabled globally (through SSCM) in the registry entry as startup parameters. Hence the trace flags that are enabled for a clustered instance of SQL Server in this manner, should be enabled on all nodes of the Windows Failover cluster that the SQL Server instance can failover to. This will ensure that SQL Server demonstrates consistent behavior on every node of the failover cluster.

Most Commonly used Trace Flags in SQL Server 2012

This section describes the most commonly used trace flags on a SQL Server 2012 instance.

Trace Flag 1204

1204 is a very commonly enabled trace flag since it captures detailed information regarding the processes that are involved in a deadlock scenario. This trace flag is only available in Global scope. If this trace flag is enabled and processes on the SQL Server instance experience deadlock, information regarding the victim and winner of the deadlock is captured in the SQL Server errorlog. This information can be extremely useful in diagnosing the possible cause of deadlock and may be required by the support personnel. 1204 is a publicly documented trace flag and more details regarding documented trace flags can be found at this MSDN link.

Trace Flag 1118

1118 is another commonly used trace flag that suppresses the mixed page allocation in certain versions of SQL Server (most commonly on SQL Server 2005 SP2 and SQL Server 2008). Usage of this trace flag is recommended for SQL Server 2008 R2 and SQL Server 2012 versions also. More information regarding this trace flag is available on CSS Blog at this location.

Trace Flags Recommended for use with SAP

Following section lists the recommended and mandatory trace flags that should be used when running SAP on SQL Server platform. Please research the usefulness of optional trace flags for your specific situation before deploying in production environment.

SQL Server 2012

Following table lists the trace flags that are recommended for use with SAP instances deployed on SQL Server 2012 RTM version. A brief description of each trace flag is provided also. Please be advised that all of the trace flags mentioned below should be enabled globally using the SSCM method described above. Refer to OSS Note 1725220 for more information.

Trace Flag	Importance	Description
1117	Mandatory	Enables SQL Server to grow all DATA files for a database at the same time if auto-growth event is initiated for one of the files. For this trace flag to work correctly, autogrowth settings should be the same for every file in the database. Refer to OSS Note 1238993 for more information.
1118	Mandatory	Described above.
2371	Mandatory	This trace flag reduces the threshold for auto-update statistics for large tables containing more than 25,000 rows. More information for this trace flag can be found at this blog post.
617	Mandatory	Reverts the behavior of the SQL Server Lock Manager back to pre-2012 functionality in order to avoid side-effects resulting from improvements that benefit vast majority of non-SAP customers.
2562	Optional	Provides scalability improvements (for databases that are in terabyte range) for DBCC CHECKDB in PHYSICAL_ONLY mode. Refer to KB 2634571 article for more information.
2549	Optional	Provides scalability improvements (for databases that are in terabyte range) for DBCC CHECKDB in PHYSICAL_ONLY mode. Refer to KB 2634571 article for more information.

SQL Server 2008 R2

Trace Flag	Importance	Description
1117	Mandatory	Enables SQL Server to grow all DATA files for a database at the same time if auto-growth event is initiated for one of the files. For this trace flag to work correctly, autogrowth settings should be the same for every file in the database. Refer to OSS Note 1238993 for more information.
1118	Mandatory	Described above.
2371	Mandatory	This trace flag reduces the threshold for auto-update statistics for large tables containing more than 25,000 rows. More information for this trace flag can be found at this blog post.
2562	Optional	Provides scalability improvements (for databases that are in terabyte range) for DBCC CHECKDB in PHYSICAL_ONLY mode. Refer to KB 2634571 article for more information.
2549	Optional	Provides scalability improvements (for databases that are in terabyte range) for DBCC CHECKDB in PHYSICAL_ONLY mode. Refer to KB 2634571 article for more information.

References

Trace Flags (Transact-SQL) - SQL Server Books Online

CSS Blog

SQL Server Failover Clustering Whitepaper - MSDN

SAP on SQL Server Blog - MSDN

Standing on the Shoulders of Students

-ByGeorge- — Sat, 10 Mar 2012 09:19:00 GMT

Morning! I just arrived home from an interesting week up in the Pacific Northwest, and wanted to share some insights. As senior architects and consultants, my colleagues and I are often looked to to provide strategic direction, or insight, or innovative perspectives, or that other awful and overused phrase: thought leadership. We do this on behalf of Microsoft for our enterprise customers and other stakeholders in the overall customer ecosystem. And it's a great gig. But I think we sometimes forget what we can learn from our juniors, especially from those who not only haven't walked in our shoes but weren't even born until the 1990s. I'm talking about today's college students....and I've been fortunate to lead and work with two very capable Microsoft-sponsored University student teams over the past 6 months.

In the first case, I was tasked with guiding development of an executive-level paper and presentation for CIOs faced with the challenges surrounding Mergers, Acquisitions, and Divestitures – common business transactions that can easily derail an organization for months even as it seeks to gain synergies directly related to executing the M/A/D in the first place..... my student team was bright, and as is the norm I built an advisory team of Microsoft-internal business architects and consultants to review our work as it progressed. Read more about this project in my CIO's blog: http://blogs.msdn.com/b/mscio/archive/2012/03/08/a-mergers-amp-acquisitions-playbook-for-cios-part-1.aspx

More recently, just this week my newest student team gave me and my stakeholders a mid-project update on some work we're doing around Social CRM. We're looking to create another CIO-level paper and summarized PPTX deck with actionable advice and a fresh perspective.

If my student teams were simply to stick to the plan and deliver the papers I need, I'd be quite happy. Mission accomplished, right? And they'd be happy with their As and the chance to include a nice little write-up and couple of extra bullet points on their resume.

But here's the thing, and why I'm writing at 3:00 in the morning. Both of my student teams saw opportunities to take our projects to the next level, to a different place, beyond the picture I painted for them. And they did so effectively – in the most amazing way. Now, I'm not a guy who needs to take credit for things I have no business taking credit for, but I have to admit that I set the stage pretty well. I pushed the teams to think beyond the obvious, beyond the research, beyond what everyone else was saying. I didn't want reworked advice and tired axioms published again and again over the last 10 years. I especially didn't want to echo what the analysts were already telling my customers. I demanded – in the nicest way of course – something new. They just over-delivered.

In the first case, my team and I were unhappy with the quality and depth of data we were finding in the contemporary M&A space. We needed something new, and we found that in part by developing and delivering to current and former CIOs a custom survey aimed at identifying real-world lessons learned and if-only-I-could-do-it-again advice. Simply having such primary research at our disposal made our paper much more credible, and yes, full of some very nice nuggets of CIO wisdom and perspectives. But my students took the project further and developed a first-of-its-kind comprehensive M&A framework. Not only is it absolutely unique, but given its chronological orientation, the framework serves as an actionable roadmap as well. I only hoped for a decent paper, but in return I received a differentiating piece of Intellectual Property vetted by CIOs for CIOs. Amazing. And they delivered a great paper and supporting decks (executive-level as well as a very detailed one). Not bad for a bunch of kids..... :).

In my most recent experience this week, my team has again surprised me. Different team and different students, mind you, but the same promise of shock and awe. We're only half-way through the Social CRM project and the team has already produced a model that captures and describes the key social platforms in terms of potential revenue impact, time-to-value, and more. The step-wise nature of the model seems destined to provide CIOs today with the kind of carefully aligned insight and actionable guidance necessary to not only identify and navigate the smartest path forward but also to develop the underpinnings of the business case necessary for pursuing that path. I wanted a paper....but I'm convinced I'm going to get a whole lot more. Again.

Sure, we can look at these project scenarios as an example of effective collaboration bounded by decent project management and guided by the maxim 'show me something new.' But it's more than that. My students are looking at the same problem space I am, but they're synthesizing it differently. As a leader, I might like to think that my team is being enabled, empowered, and positioned for success based on what I've provided them. And yes, I'm pretty helpful. :) But at the end of the day, I'm really standing on the shoulders of my students. They're the giants, and my customers will benefit mightily from their fresh perspectives and our combined work. As for me, I'm just grateful for each of them and for the chance to be part of developing something truly innovative, and truly inspiring. Imagine what they'll accomplish as they move into our workplace and transform it in front of our eyes. Very cool times ahead of us, for sure..... :)

Take care all,

George

SQL Server TDE with SAP Applications - Part #1 Enabling TDE

Fazil Osman — Thu, 01 Dec 2011 14:43:00 GMT

Introduction

An organization can take several precautions to help secure a database, such as designing a secure system, encrypting confidential assets, and building a firewall around the database servers. However, in a scenario where the physical media (such as disk drives or backup tapes) are stolen, a malicious party can simply restore or attach the database and browse the contained data. One solution is to encrypt the sensitive data in the database and protect the keys that are used to encrypt the data with a certificate. This prevents anyone without the keys from using or accessing the data, but this kind of protection must be planned in advance.

As of SQL Server 2008 a new feature called Transparent Data Encryption (TDE) was introduced into the RDBMS product. The new feature differs significantly from Data Encryption introduced with SQL Server 2005, which could be used to encrypt data of certain columns in tables. Microsoft received the following feedback from customers and partners, especially from larger ISVs:

The encryption technology should be transparent to the underlying applications. The emphasis of such a technology would be less on protecting the data from user access, and more on protection from media loss (backup tapes or disks) or media theft.
Data encryption should cover the entire database.
Data encryption should be extended to database backups.

Transparent data encryption (TDE) performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. The DEK is a symmetric key secured by using a certificate stored in the master database of the server or an asymmetric key protected by an EKM module. TDE protects data "at rest", meaning the data and log files. It provides the ability to comply with many laws, regulations, and guidelines established in various industries. This enables software developers and more specifically system administrators to encrypt data by using AES and 3DES encryption algorithms without changing existing applications.

Two very important technical and operational aspects to consider before implementing SQL Server TDE, is the impact on database performance (CPU and I/O) and the management of certificates and private keys. Activating TDE will directly impact the database performance to varying degrees, depending on which encryption algorithm is chosen. This impact is discussed in more detail in Step #3 below.

Additionally, the management and backup of database certificates and keys are vital to ensure that the encrypted database can be restored in the advent of a disaster. If you cannot retrieve your database certificate and private keys, the data is lost! Step #7 discusses the procedures for creating file level backups of these keys and certificates. SAP fully supports TDE on SQL Server databases and OSS note 1380493 can be referenced for more information.

Step by Step Procedure

Step #1 – Verify SAP & SQL Server

Verify SAP & SQL Server before starting the TDE procedure. Login to SAP and execute DBACOCKPIT to display some basic database information thus verifying the database is open and working satisfactorily.

Next, using SQL Server Management Studio to verify the database options showing that encryption (TDE) is not enabled.

Step #2 – Full Backup

Execute a full database backup including transaction logs before starting the TDE procedure. Using SQL Server Management Studio or any 3^rd party backup software to start and execute the full backup. Be sure to provide a descriptive label including the text that this backup is not encrypted.

After the backup completes, verify with DBACOCKPIT and/or SQL Server Management Studio or any 3^rd party backup software.

Step #3 – Encryption Type

Before proceeding, a decision must be made regarding the encryption algorithm that will be used. Microsoft supports multiple encryption algorithms which have different levels of performance and encryption. The table below depicts a simple load comparison conducted using various encryption algorithms and base lined with no encryption.

Supported encryption algorithms are AES with 128-bit, 192‑bit, or 256‑bit keys or 3 Key Triple DES (see Database Encryption in SQL Server 2008 Enterprise Edition at the end of this post for more information).

The Microsoft LoB CoE recommends using AES_256 or lower for SAP systems in general. Special consideration must be given to systems which already are I/O bottlenecked because of the increased encryption overhead caused by the data getting encrypted/decrypted in the I/O path. Therefore, servers with low I/O and a low CPU load will have the least performance impact. Applications with high CPU usage will suffer the most performance loss.

Selecting a higher encryption level will exacerbate this degradation and large scans which mainly happen on disk will be slowed down. In particular, TRIPLE_DES has significant higher impact than one of the AES algorithms.

Step #4 – Stop SAP

Before starting the encryption procedure, we recommend that all SAP applications utilizing the database be shut down. Stopping the SAP application prevents any DDL statement from being executed from the SAP data dictionary or via SAP transports.

The following operations are not allowed during initial database encryption, key change, or database decryption:

Dropping a file from a filegroup in the database
Dropping the database
Taking the database offline
Detaching a database
Transitioning a database or filegroup into a READ ONLY state

The following operations are not allowed during the CREATE DATABASE ENCRYPTION KEY, ALTER DATABASE ENCRYPTION KEY, DROP DATABASE ENCRYPTION KEY, or ALTER DATABASE...SET ENCRYPTION statements.

Dropping a file from a filegroup in the database.
Dropping the database.
Taking the database offline.
Detaching a database.
Transitioning a database or filegroup into a READ ONLY state.
Using an ALTER DATABASE command.
Starting a database or database file backup.
Starting a database or database file restore.
Creating a snapshot.

The following operations or conditions will prevent the CREATE DATABASE ENCRYPTION KEY, ALTER DATABASE ENCRYPTION KEY, DROP DATABASE ENCRYPTION KEY, or ALTER DATABASE...SET ENCRYPTION statements.

The database is read-only or has any read-only file groups.
An ALTER DATABASE command is executing.
Any data backup is running.
The database is in an offline or restore condition.
A snapshot is in progress.
Database maintenance tasks.

Step #5 – Create the Master Key

Create a master key that applies within the entire SQL Server instance. This creates the database master key (DMK), which again is used to encrypt certificates to be created for database encryption. The master key itself is protected by the Service Master Key, which is automatically generated when SQL Server is installed. Be aware that the password needs to accommodate the default Windows password policies if those are enabled on the server you are creating the certificate on.

Step #6 – Create the Certificate

Create a certificate that is also valid for the entire SQL Server. Because no specific encryption password is used, the DMK is used as encryption password.

Step #7 – Backup the Key and Certificate

If the local security certificate is used, back up the certificate. If possible, the backup location should be on a different server. If you cannot retrieve this certificate, the data is lost. It doesn't matter whether there is a valid backup of the encrypted database. In order to restore the backup, a valid certificate is needed. If the only location of the certificate is the production SQL Server instance and the server goes down or the SQL Server instance is lost, the backup is not worth a cent. Therefore it doesn't make sense to keep the backups of the certificates on the same server; they should be stored on a different computer.

The following procedure creates a file level backup of the database certificate (.CER) and corresponding private key (.PVK). The private key file is also encrypted with an additional password as shown in the screenshot.

Strategies for Managing Keys

The illustration below shows the architecture of TDE encryption and how each key and certificate is protected. In general the generated certificate and keys are stored in the master database within the SQL Server instance. To ensure that the keys are available after a disaster should the SQL Server instance be lost, the keys should be stored in additional locations and media independent of the database server. The following scenarios can be considered:

Using a secondary SQL Server database to store the certificates and keys. A smaller SQL Server 2008 instance or even SQL Server 2008 Express instance can be used to accomplish this scenario. The secondary instance must be 100% independent from the encrypted database and should not share any infrastructure components.
Storing the backup of the database certificate (.CER) and corresponding private key (.PVK) on a third party file repository with Access Control Lists (ACL). One example would be creating a SharePoint repository, setting the appropriate ACL and then storing these files with that repository. Additionally, to simplify the management of the file pair, both files can be consolidated into a single password protected ZIP file before storage.
Storing the backup of the database certificate (.CER) and corresponding private key (.PVK) on removable media such as a USB flash drive and then physically securing the USB flash drive. Additionally, to simplify the management of the file pair, both files can be consolidated into a single password protected ZIP file before storage.
Using an extensible key management (EKM) provider to generate and store the keys. The procedures for this step will vary depending on the requirements of the EKM provider, but might include installing a certificate from the EKM provider in the local certificate store, and copying EKM DLL files onto the SQL Server computer. Using an EKM provider is not in scope for this article and thus not discussed further in this document. Using this key/certificate management mechanism would be preferable when multiple systems/databases require encryption. The LoB CoE together with our engineering colleagues are considering a more in depth and focused blog entry for the future.

Step #8 – Link the master key, certificate and database

Link the master key, the certificate, and the database to be encrypted. This is done by creating an encryption key for the specific database to be encrypted.

Step #9 – Encrypt the database

Execute the database encryption command per the screenshot below:

Step #10 – Monitoring the progress

While the background processes continue to work, this query can be used to get an overview of the progress of work. The progress of the encryption can be monitored using the following transact SQL statement:

SELECT DB_NAME(database_id), encryption_state, key_algorithm, key_length, percent_complete FROM sys.dm_database_encryption_keys

The first column will show the database name. The second column will have a value from 1 to 5 where the values stand for:

Unencrypted
Encryption in progress
Encrypted
DEK change in progress
Decryption in progress

You will also notice additional CPU utilization for the SQLSERVR.EXE process as shown below via task manager:

Executing the query after encryption is completed displays the encryption_state = 3 and percent_complete = 0.

Step #11 – Start SAP

After the encryption process has completed, start the SAP system. No additional changes or profile parameters are required for SAP to function correctly.

Step #12 – Verification

After the encryption process has completed and the SAP system restarted, small verification checks can be executed to ensure that the system is stable and working correctly. Using SQL Server Management Studio, open the database parameters options and looks at the encryption setting:

DBACOCKPIT can also be executed again to verify that SAP and the database are communicating correctly:

Step #13 – Full Backup

Finally, execute a full database backup including transaction after the TDE procedure. Using SQL Server Management Studio or any 3^rd party backup software to start and execute the full backup. Note that this backup is now encrypted and can only be restored if the database certificate and private key are present on the target SQL Server instance. Any restore will fail if the keys are not installed and available to SQL Server.

One important aspect to note about SQL Server backups after enabling TDE is that compression rates fall significantly. TDE destroys recurring patterns in the data stream that the compression algorithms use. Our engineering experience has shown that backup compression rates drop to only 1% on average when TDE is enabled and thus the resulting backup set is much larger.

Conclusion

The process of encrypting your Microsoft SQL Server 2008 database is fairly straight forward and painless. The LoB CoE highly recommends performing this process multiple times in a non-production system to ensure that the results are understood and validated before moving into your "live" SAP environments. Additionally, please ensure that the Microsoft KB article 2300689 is referenced and applied before enabling or disabling TDE.

A special thanks to Juergen Thomas and Raymond Smith for helping with this content. Part #2 - Impacts of TDE and Disabling/Removing TDE will be published mid-December 2011 and I expect a total of 4 blogs on this topic:

Part #1 - Enabling TDE
Part #2 - Impacts of TDE and Disabling/Removing TDE
Part #3 – System Copy with TDE
Part #4 – Log Shipping and Database Mirroring with TDE

Additional Information

Related SAP OSS Notes
1380493 - SQL Server Transparent Data Encryption (TDE)

Restore error after disabling TDE
KB - 2300689 - http://support.microsoft.com/kb/2300689

Database Encryption in SQL Server 2008 Enterprise Edition
http://msdn.microsoft.com/en-us/library/cc278098

Understanding Transparent Data Encryption (TDE)
http://msdn.microsoft.com/en-us/library/bb934049.aspx

How to: Enable TDE Using EKM
http://msdn.microsoft.com/en-us/library/cc645957.aspx

SAP with Microsoft SQL Server 2008 and SQL Server 2005: Best Practices for High Availability, Maximum Performance, and Scalability
http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/SAP_SQL2008_Best Practices_Part_I.docx

Issues with SAP tools during migration/Unicode conversion

GuidoSchmitt — Tue, 03 May 2011 15:50:00 GMT

Hi,

We raised some issues with the SAP migration tools during a replatforming project including Unicode conversion from SUN/Oracle to Windows SQL Server.

Our migration went well and all the log files from the technical migration reported no error, but during the application testing the customer claimed some missing data in a couple of tables.

What happened:

These tables were splitted in at least four parts from the R3TA tool and exported and imported without any errors. After a detailed investigation, we found that some of the packages a empty:

0 rows exported and 0 rows imported.

A cross check inside the source SAP system with the select patterns out of the WHR files showed the missing entries. The select statement with the select criteria out of the HWR files executed directly on Oracle using sqlplus resulted with 0 rows again.

After tracing the selection of SE16 with ST05, we found the issue:

The WHERE condition inside SE16 was like

SELECT COUNT(*) from "/BIC/ADC_CNT200" WHERE "CRM_OHGUID" <= '4909283A9B121CF6E10000000A31EA29' AND "CRM_ITMGUI" = ' ';
Result: 238656 rows

The WHERE clause with the conditions of the WHR files was:

SELECT COUNT(*) from "/BIC/ADC_CNT200" WHERE "CRM_OHGUID" <= '4909283A9B121CF6E10000000A31EA29' AND "CRM_ITMGUI" = '';
Result: 0 rows

The difference is the blank in the second condition! So the error is caused by the '' in the where condition created by the R3TA tool.

After changing the WHR file and re-export/re-import the table, we got the same row count on both sides.

Now the customer raised the concern about the consistency of the migrated data. Therefore we checked the row count for all tables on the source and the target system and compared these values with the information recorded in the *.toc (export) and *.log files (import) to be sure we didn't lost any data on the way from Oracle to SQL.

Thereby we found two additional strange phenomena:

The cluster table REGUC had app. 18.000 additional rows on the target side, but from the application point of view it was consistent.

The row count information from the *.toc and *.log files was not consistent with the row count in the source and target DB

To 1.: The phenomena with REGUC was raised by the data-growth during the UNICODE conversion. The table is a table cluster resp. A physical cluster table with a VARDATA field containing compressed raw data. The content of the VARDATA field is extracted and converted during the migration/Unicode conversion. The length of the field (3.800 bytes) doesn't change - however after the Unicode conversion some data doesn't fit in one record only so you have more records on the target. So a continuation record is created and the table has more records after the migration. This could be verified with the following select statement, which needs to show the same values on the source and target:

Select count(*) from REGUC where PAGENO=0;
(table records without continuation records)

To 2.: For some transparent tables (e.g. PA0000 in the HCM system) we had the same row count information in the *.toc (export) and *.log (import) files. Both files showed 145.189 rows, but a select count(*) on the source and target DB showed 142.615.

We reported all the issues to SAP. The issue with the R3TA is already corrected in version 7.20 and higher and will be back-ported to earlier versions (7.0x and 7.1x), too. The issue with the inconsistent row count is still under investigation from SAP. I will update the blog, if we get an response from SAP

Lessons learned:

Don't trust the log files of the SAP migration tools, you have to verify the consistency of the databases by additional checks.

The Year of Hindsight: 2020

-ByGeorge- — Fri, 29 Apr 2011 23:16:00 GMT

With the year 2020 now less than a decade away, my esteemed colleague Juergen Imhoff and I have put together the following "fictional roadmap" representing upcoming technology and business themes or events. Keep in mind this blog posting in no way reflects Microsoft’s official position, any special personal insight, or any overt or implied guarantees one way or the other. It’s simply the ramblings of a couple of IT/Business professionals interested in playing a bit of what-if as Spring unfolds and Summer approaches… written in hindsight from our carefully crafted and lofty “Year 2020 hindsight” perches…

--- --- --- --- --- --- --- --- --- ---

We've seen Enterprise IT take a remarkably diverse journey over the last decade. Back in 2013, still called "The Year of SaaS," we saw a great amount of consolidation in the increasingly successful SaaS and IT services markets. Through these mergers, the then-Big 4 software providers managed to maintain their competitive positions from years earlier, though they were faced with two "new" enterprise competitors. Initially known for search and later a mixed portfolio of OSs, tools, and a less-than-successful initial application strategy, Google's acquisition spree (particularly of Salesforce.com and several open source enterprise applications providers) created a new contender in enterprise SaaS-based business applications. More surprising, Apple's unforeseen entry into the Enterprise Data Center marked the beginning of a new wave of highly consumable and easily customized software, services, and underlying infrastructure. Leap-frogging one another during the year, the six companies battled on several fronts ranging from technology innovations, standards rationalization, and commoditization plays to tier 1 references, industry alignment, and market cap valuations.

In 12 months, IT's landscape was forever changed. To be sure, businesses around the globe benefitted. During this time though, access strategies to the cloud's growing collection of business-critical applications became increasingly prone to disaster recoverability and business continuity concerns. In the same way, security concerns began to crop up and consume a disproportionate share of IT budgets as data privacy issues and secure accessibility by a global community ill-prepared for the cloud threatened businesses and consumers alike. Unfortunately, at the time it all seemed business-as-usual, and band-aids seemed to be an adequate cure. Looking back, it's obvious what a mistake that kind of thinking turned out to be.

By mid-2014, in the midst of diminished margins and generally tremendous upheaval in their cash cow businesses and consumer applications marketplaces, the Big 6 found it necessary to pursue (in some cases, last-minute) aggressive partnering and vertical M&A strategies. Failed buyout attempts and successful acquisitions of one another underscored the scramble. Vertical mega-acquisitions up and down the delivery, technology, and application stacks added billion-dollar facilities at one end of the spectrum and mobile networking/telecom assets at the other. An especially bright spot emerged as Microsoft completed SaaS-enabling and integrating the last of its business and consumer applications and integration solutions. In doing so, they created the first true business/consumer experience meshing a consumer's business and personal worlds and workloads as loosely or as tightly as those consumers found useful.

The real news towards the end of 2014 revolved around the growth and automated accessibility of “virtual payment,” though, as PayphoneX catapulted into the forefront of daily news due to its significant online-energy payment system breakthroughs. PayphoneX made it easy to own and operate the new breed of hybrid and electric cars, particularly in the world's most congested cities. Payphone’s ability to accept virtual payment for car & mobile systems based exclusively on Microsoft’s Windows Phone XA enabled millions of consumers to not only gain real-time insight into road conditions, traffic, and even air travel, but to also allow automated payment processing at recharging stations around Europe, North America, and eventually the rest of the world. More remarkably, Phone XA and several competing products eventually made it possible to dynamically improve travel conditions using smart reroute analytics for Windows Phone and Windows-engineered vehicles. With a 42% annual growth rate across the world's most populous automobile-centric cities, PayphoneX and its infrastructure partners changed travel like Facebook had changed social networking 5 years earlier.

By the middle of 2015, a new cloud phenomena known today as NewCloud owned the headlines. Pure-play Public Cloud Services (PCS) had finally matured enough to enable consumers, businesses, and their partners, vendors, customers, and friends to connect to one another effortlessly and securely; short-lived Hybrid Cloud models fell away for all but the ultra-conservative or federally regulated. This ubiquitous connectivity ushered in another wave of paperless integration, speeding along the demise of more printed and faxed forms as promised two decades earlier. And this newfound interconnectedness set the stage for yet another round of automated and orchestrated business-to-business and business-to-consumer processes. Conversely, Private Cloud deployments peaked that year as their stop-gap “purpose for being” lost traction in the wake of less expensive, low risk, and highly available NewCloud offerings with their standardized and ubiquitously integrated development and hosting environments.

On the business/technology front, IT departments continued to dramatically shrink. In the space of only 2 years, many of these final bastions of techno-geek empires gave way instead to Business-enabled IT Project Management Offices responsible for the coordination rather than the implementation and management of information technology. The real work of IT was being done by the IT15, a who’s who of Mergers and Acquisitions yielding marriages between various telecommunications and network providers, software providers, systems integrators, traditional hardware vendors, and several hosting and other services providers. Nearly all desktop, network, database, and server administration and support professionals went the way of green-screen terminal service providers as both client and back-end devices became more and more disposable and as business/productivity applications were delivered as a service. Private Data Centers still hosted a few core or differentiating applications but everything else had since been tossed into the cloud (consistent with Geoffrey Moore’s core-vs-context work socialized a decade earlier), where literally a handful of subject matter experts addressed the infrastructure and platform needs of a billion end users.

And then everything changed in 2017 and the world was forced to reckon with the magnitude of all these changes. Despite great progress on many other fronts, security threats continued to mount as data was logically centralized by relatively few providers; so much power consolidated so tightly and wielded by so few was destined to be exploited. To make matters worse, another world-wide recession and the continued re-leveling of global assets set the stage for a new round of terrorist activities. This culminated in the events of October 2017 when $2 trillion worth of trading was compromised and eventually held hostage. Though officially resolved after 2 weeks, the hijacking combined with a related string of dirty bomb detonations orchestrated over 3 continents changed the face of not only global business but communications all up. The resulting exodus of liquidity from public markets devastated the global economy; repercussions continue to emanate today across social, economic, political, and business divides. And in the wake of the event’s success in destabilizing the globe, the subsequent growing incidence of copycat events has weighed heavily on global recovery. Back in those days, the prevalence of hijacking both business data and business processes pushed data security squarely into the limelight as the number one risk management challenge today. The uptick in “backsourcing” IT from the cloud back to a company’s own data center only made matters worse; the economics simply couldn’t justify the risk/reward model over any period of longevity. Companies made huge exit investments to the detriment of their already skittish shareholders and stakeholders, only to ultimately go back tail-between-their-legs to the very clouds they had left the year before.

And so we find ourselves here, today, in 2020. With this new decade of hindsight upon us, can we indeed finally see more clearly? Do we have better answers? We still haven’t solved third world hunger (though we have the resources), we are still too dependent on old world fuels (though we have effective alternatives), we still solve problems by deploying troops first and diplomacy second (though we know better), and we continue to face an increasingly dramatic cultural divide between those who have information and those who need it. Our world of enterprise and consumer technology is certainly much less expensive and much more connected and accessible than only five or ten years ago, too. But has it made a positive difference in our lives?

As we move through this Year of Hindsight, large in-house business application-focused Data Centers exist only for those firms where government regulations and compliance requirements (ITAR, FDA, HIPAA, etc) stipulate data-to-physical asset mapping and control. With much of our business, economic, and personal data continuing to grow even more tightly interconnected, Business Intelligence has again topped the business application charts as the killer app of the year—for the 10^thyear in a row. Interestingly though, this continued focus on BI has done little to change individual work efficiencies. Productivity from an individual perspective remains at the same level we saw five to ten years ago, likely a direct result of the massive increases in information, subsequent analysis, and limitations of the human brain to effectively multi-task. Of course, the breadth of automation and orchestration implemented over the last decade has circumvented many of these challenges, driving record profits for companies around the world while nearly doubling the GNP of several countries. New low-cost “people-pluggable” productivity systems from MBI (MicroBook Inc), GoogBM, AppleZon, and OraATT hold the promise to significantly overcome individual productivity and efficiency limitations. But with fewer and fewer people holding fewer and fewer knowledge worker jobs, the net effects might take years to bear fruit.

There is some good news on the consumer front though. Excess compute power is not only traded on the open market but donated tax-advantageously to the benefit of government programs, research projects, and society in general. Year 2020 has arrived to see many people using large-scale well-performing and secured pay-as-you-go public clouds in the same way that utilities like electricity and water are consumed. The “utility computing” concept envisioned at the turn of the century has finally been realized: a ubiquitous, seamless, and everyday part of life as a consumer on Earth. More than ¾ of the world’s 13 billion inhabitants across 190 countries seamlessly access the cloud and thus each other; business, ongoing education, and indeed a big part of life are being transacted nearly exclusively via the connectedness enabled by NewCloud, all fed by a host of vNext mobile and other tetherless devices. Indeed, the Green Consortium’s work towards solving the battery life problem has opened new doors to online education and 24x7 collaboration despite differing global infrastructure availability and technology standards (a sticky issue handled in large part by standardized data sharing protocols, cloud-access message bus technology, and IPv7).

Major roads, bridges, and ports, and several billion homes and businesses around the globe have been instrumented, resulting in a new wave of innovative cloud services developed and sold for fractions of pennies per transaction. Ninety percent of IT assets are controlled, and 95% of all transactions are at least passively handled, by the Global 10 (G10). People and goods alike are tracked today from birth to death, from creation to consumption, awakening a new level of insight and frankly a new incarnation of Big Brother like the world has only to this point imagined in fiction.

Massive exabyte and zettabyte-sized globally distributed data stores and the inherent physical vulnerabilities of the networks carrying all this information have finally necessitated government protection and regulation, which in turn along with the growing crisis around data theft and information hostage has pushed the development of more sophisticated security and identification controls. Despite global redundancy and safeguards, with its ability to broadly affect entire countries and markets as well as individual companies and high-visibility industry and political leaders, hacking components of the G10 has become the dominant and favored method of terrorism. To be sure, the recently approved trillion Euro funding initiative will help quell these attacks. But this unforeseen wrinkle in the power shift has recently given rise to the notion that many countries and geographies will soon become second-tier constructs underneath the umbrella of the G10 and its carte blanche access to funding.

In all this, privacy has become a luxury few can afford. Most every business, vehicle, and commerce-related entity is instrumented and tracked for reasons described as “SoS”—for the “safety of society.” In the name of global security and in the wake of near-cataclysmic terrorist close-calls post-2017, there is even serious talk by a small circle of G10 and others global leaders of eventually instrumenting individuals as a prerequisite to buying and selling goods. In hindsight, perhaps this outcome is fairly predictable, if not an obvious outcome of the cloud phenomenon initiated a decade ago. After all, it too was predicted many, many, years ago….

--- --- --- --- --- --- --- --- ---

Hope you enjoyed our little journey! Your thoughts and feedback are welcomed, as usual.

Best regards,

George Anderson & Juergen Imhoff

BI: The New Pervasive Mission-Critical Application

-ByGeorge- — Tue, 19 Apr 2011 06:07:00 GMT

I have been thinking about Business Intelligence and Business Analytics the last few weeks. In my earliest days designing and supporting BI, data marts, and data warehousing systems in the late 1990s, the work I did was aimed at something typically less-than-critical. Back then, my customers ran Oracle and SAP Data Warehouses with a sprinkling of Teradata and others. BI reports could take a few hours if not a few days to land on someone’s desk or in someone’s inbox. If a report failed, my customers were often OK with re-running the job that created the report later that evening. They needed the information contained in the report, sure. But with the exception of the last few pages in their month-end reports, in many cases my customers didn’t need these reports real-time. It’s safe to say then that the data in these reports didn’t affect mission-critical business operations one way or the other. They were important but not mission-critical.

A decade later, nothing had really changed….In the early 2000s, BI was still expensive and oftentimes still ‘batch’ oriented. The highest performing BI systems – those capable of delivering complex real-time analytics – could only be afforded by very large organizations with equally large budgets. For the rest of us, Microsoft came out with Analysis Services (OLAP Services) in 2000, and a Reporting Services add-in to SQL 2000 in 2004, and those were nice steps in the right direction. SAP came out with its Google-like BI appliance a bit later and saw some decent adoption in the SAP ecosystem, but even then SAP’s customers didn’t really know what to do with all that power and ability to do real-time analytics. And it was still “SAP” expensive….not exactly BI for the masses. In the end, we had a mix of expensive real-time and inexpensive near-real-time BI solutions, and end-users (actually, their IT organizations making the purchasing decisions) pretty much had to accept the trade-off.

Five years later, and things are finally changing. Cost-effective real-time analytics are here. As the cost of world-class BI systems has fallen, and BI’s capabilities to provide real-time business visibility have grown so much, we’re at a new crossroads. I have come to the conclusion that this new cost model and capabilities combination has moved BI into the realm of mission-critical gotta-stay-up applications. Why? Because a contemporary BI system’s failure may now cause an organization's overall mission at hand to fail – real mission critical impact. Because waiting a day for a report means losing a day’s advantage to your competitors who now have not only the same kind of inexpensive real-time data, but also the ability others have to do mash-ups in a way that uncovers new trends, markets, expense reduction possibilities, and net new OPPORTUNITIES. Cheap real-time BI gives businesses the agility they need today, this minute, right now, to make smarter decisions and employ course corrections....to keep the business not only afloat but ahead of its competitors. Again, mission critical impact.

What next? It’s time that the platform providers shared the kind of prescriptive guidance and practices necessary to host these real-time business capabilities in a mission-critical, unbreakable, resilient, and risk-mitigated manner. Yes, beyond unbreakable... which arguably is the most important attribute but at the end of the day really only represents 'table stakes.' Microsoft is one of very few players that can offer mission critical platforms across a breadth of innovative and cost-effective hardware platforms, from on-premises to cloud, from HP and Dell to Fujitsu and Unisys hardware partners, from CSC and IBM to smaller hosting providers. The combination of these four attributes – unbreakable, innovative, cost-effective, and superior choice – sets the stage for a new generation of mission critical platforms, a new normal in enterprise computing, and a new sense of the phrase 'table stakes.' All of this sounds like something we need to explore in another post…

Thanks for your (gentle!) comments and feedback,

george

Experiences with SQL Server Database Compression

GuidoSchmitt — Wed, 13 Apr 2011 10:03:00 GMT

I want to share some customer experiences with database compression we gathered in a large SAP migration project in EMEA.

Most of the public available data about SAP database compression ratios only shows values for compression of an existing SQL Server database, like:

· Using SQL Server Database Compression with SAP NetWeaver:

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/50c5de8f-282a-2e10-cb8a-a5859a0994a4?QuickLink=index&overridelayout=true

· Quanta case study:

http://www.microsoft.com/casestudies/casestudy.aspx?casestudyid=4000003962

In our project we performed a heterogeneous system copy of a whole SAP landscape based on Netweaver 7.0 from SUN/Oracle to Windows SQL Server including a Unicode conversion of the SAP systems. So we combined the change of the operating system and database software (SAP OS/DB migration) together with the Unicode conversion of the systems. The technical procedure is based on the SAP migration tool R3load, which converts the system during the export of the data to Unicode.

The target database software is SQL Server 2008 R2 and we are using Page compression for all the database tables. Source database software is Oracle 10.2.0.4.

Until SQL Server 2008 R2, we had the disadvantage of storing the data in a UCS2 format which resulted in a growth of the database after the Unicode Conversion. SAP officially provided the following sizing information for databases in regard of Unicode Conversions:

With SQL Server 2008 R2, Microsoft introduced UCS-2 compression on top of our existing compression functionality, which removes our disadvantage in case of a Unicode conversion of a SAP system: (http://blogs.msdn.com/b/saponsqlserver/archive/2009/09/16/sql-server-2008-r2-ucs2-compression-what-is-it-impact-on-sap-systems.aspx)

Now back to the customer example. In the following table you find the database sizes and compression ratios of five productive (PRD) and development (DEV) SAP systems:

SAP System	Instance	Oracle		SQL Server
		DB Size Total	DB Size Used	DB Size Total	DB Size Used	Saving % (Used)
ECC 6.0	PRD	2.800	2.450	1.220	1.170	52
	DEV	230	160	126	65	59
HR	PRD	477	405	255	199	51
	DEV	174	125	112	76	39
CRM	PRD	311	230	133	121	47
	DEV	67	51	42	24	53
BW	PRD	152	85	100	59	31
	DEV	110	37	50	16	57
BW	PRD	1.161	890	634	452	49
	DEV	120	60	50	21	65
SUM		5.602	4.493	2.722	2.203	51

As you see in the saving column, even with a Unicode conversion, which normally ends up with a growth of the data, we came up with an average saving of 51% for the used database size. If we include the QAS systems, which are 1:1 copies of the PRD systems, the customer came down from app. 10TB total DB space on the Oracle side to app. 5TB on the SQL Server 2008 R2 side.

We only found one strange phenomena in regards to the compression. For legal and compliance reason the customer needs to store his data online for 10 years and needs to protocol all changes to the database tables. Inside a SAP system this changes are stored in the standard table DBTABLOG. In the large ECC 6.0 system this table has > 15millions rows and uses app. 280GB of disk space on the Oracle side. After the migration the size of the table grows up to 520GB on SQL Server 2008 R2, which means near the half of the database size is occupied by one table. This difference is due to Oracle’s BLOB/CLOB compression; a functionality which will compress content in BLOB/CLOB columns. Since DBTABLOG keeps the main content in such columns, there is a reduction in size of such a table compared to SQL Server. SQL Server so far doesn’t have compression implemented for BLOB/CLOB data types. Except for the fact that SQL Server would apply its Page compression algorithms on BLOB/CLOB content which is stored in-row.

Transitioning Non-Production LOB systems into the Cloud

-ByGeorge- — Wed, 19 Jan 2011 18:42:00 GMT

The more I speak with executive IT leaders nowadays, the more I hear the same: help me cut costs, reduce my risks, and show quantifiable and near-immediate value to the business. What’s not being said today is more important, though – and alarming in its absence. "Where can I do this? What else should I look at?" CIOs who have already cut budgets, people, and nonessential projects are scanning their enterprise assets and struggling to figure out their next big move. Maybe they’ve already gone so far as to transition email and collaboration systems into the Software-as-a-Service (SaaS) cloud, have been evaluating alternatives in lieu of pursuing expensive on-premises LOB application upgrades, have been migrating to and standardizing on more innovative computing platforms like WS2008 R2 and Azure, and have signed up with an inexpensive hosting provider to manage their growing collection of legacy systems. What else can be done?

To answer one question with another, have they considered moving their non-production (back-end supporting) mission-critical SAP, Microsoft, Oracle (Siebel, JD Edwards, Peoplesoft), and other enterprise business systems to an Infrastructure-as-a-Service (IaaS) cloud? Pundits warn that the cloud is not ready for your business applications, much less your mission-critical ones. After all, traditionally hosted on-premises business applications reflect too many complex integration points (connecting ERP to your analytics, reporting, faxing, warehousing, tax calculation, and other bolt-on systems, for example) subject to potential performance, security, and availability issues. Combined with data complexities, the need to meet stringent SLAs, and all the risks associated with change, “clouding” enterprise applications might seem like the last place a CIO should look to make changes.

Ah, but here’s the thing: while these issues or challenges certainly hold true for production systems (and are starting to be well-addressed, by the way), the arguments against using the cloud for enterprise applications really start to fall apart when we consider moving behind-the-scenes non-production systems to the cloud. Non-production systems used for training technical teams and end user communities, piloting or demonstrating new systems, installing and quickly evaluating functional enhancements, or quickly smoke testing new functionality used automated test tools, can be ideal cloud candidates.

Let’s back up a moment. What is the cloud but simply a term reflecting a number of alternative deployment options or methods of delivering IT, platforms, or finished software solutions? It's just another platform, another option, and a quickly provisioned relatively cheap one at that. The new question on the lips of IT leaders everywhere should be: “why can’t we toss this into the cloud?”

The cloud can dramatically increase a business’s time-to-value. Most IaaS providers can turn around an organization’s request for compute resources, i.e. the servers and storage resources along with network and other infrastructure services, in minutes to hours. With all the hassles surrounding new system procurement, OS and database licensing, rack-and-stack, and the requisite installation services, the best that most IT departments can promise is several days to weeks or longer. Such time-frames do little to appease business leaders or executive teams that a CIO is doing everything possible to decrease time-to-deploy, reduce the risk of delaying new projects, and show quickly delivered quantifiable business value.

And the cloud can offer financial alternatives as well. From pay as you go models, to converting CAPEX to OPEX, cloud providers give IT organizations a level of financial flexibility they probably don’t have available today. When you consider the acquisition and operational costs of servers, OS licenses, related disk space, and so on (for each of your non-production SAP, Oracle, and Microsoft business applications), the numbers grow quickly. Factor in the benefits related to rapid deployment and the ability to spin up new and wind down no-longer-needed computing resources, and it shouldn’t be difficult to not reduce costs by 1/3 to 1/10th vs traditionally hosted, internally managed systems but to deliver real business value faster than you've done before. True, you lose the tax deductions for depreciation. And there’s cases where regulatory bodies simply eliminate the cloud as a hosting or platform alternative. But in many cases an organization can gain both business flexibility and increased IT employee bandwidth at precisely the time they need it - as our global economy begins to recover and an organization’s business teams begin pushing IT to dust off all those projects shelved over the last 3 years.

A typical Fortune 2000 company running a mix of ERP, Supply Chain, Customer Relationship Management, Portal, and similar mission-critical business applications is likely running a combination of development, test, technical sandbox, business sandbox/prototyping, demonstration, upgrade, and end-user training systems for every production system. To put the cloud opportunity into perspective, there’s conservatively a 3:1 ratio of “cloudable” non-production systems to production systems (because systems in the promote-to-production process – like development and test – might be more effectively housed alongside the production system). Once we start doing the math, the full impact of these lower costs @ rapid time-to-value @ little to no risk should become apparent. Factor in the ability to move supporting bolt-on non-production (and perhaps production?) systems to the cloud, and the numbers grow even more impressive.

Let me know if you’d like to walk through a real-world risk-informed cost savings model. I could share some work we're developing, and together we could build something very realistic. In the meantime, think about and share with me here the non-production systems in your own workplace that you could transition to the cloud, and all the benefits (besides increasing time-to-value and reducing costs) you might reap in the process…

Best to you!

George

JD Edwards & SQL 2008 R2 Unicode Compression

jacksmccullough — Tue, 18 Jan 2011 16:47:00 GMT

One of the most important new features introduced in SQL Server 2008 was database compression (row and page). JD Edwards Business Data databases in particular are great candidates for compression. Customers can expect anything from a 55% to 80% reduction in the size of their databases just by using row compression. Compression in SQL Server 2008 however is one of those gifts that just keeps on giving, not only does it result in much smaller databases but in the vast majority of cases it also results in improved performance. A smaller database means fewer IOs, the number of pages that needed to be written to disk is now smaller and every page read from disk now contains more data. This reduction in IO usually more than offsets the slight increase in CPU utilization (1-3%) needed to compress and decompress the data. SQL Server compressions provides another significant benefit, not only are the data pages compressed on disk but they are also compressed when they are read into memory. This means that the buffer pool is effectively doubled, and in some cases trebled, all without adding a single byte of RAM to the server.

You can probably guess that I am a big fan of compression, and many JD Edwards customers who are now using it are big fans of it as well. However there was one slight *** in SQL Server's Compression armour. In SQL 2008 compression only compressed non-Unicode data. From EnterpriseOne 8.9 and onwards Unicode has been enabled by default. If you upgraded from an earlier release you could get away without using Unicode. The likelihood however is if you upgraded to or installed EnterpriseOne 8.9 or higher the database is now using Unicode. You can tell by looking at the table definitions, if you see NCHAR or NVARCHAR columns then you are using Unicode. If you have a Unicode database that didn't mean however that there was no benefit in using compression, it is still possible to achieve 50%+ compression of a JD Edwards Unicode database when using SQL Server 2008's non-Unicode compression.

However with the release of SQL Server 2008 R2 Unicode compression was now included and the *** in compression’s armour removed. How much of a difference though does Unicode compression now make on a compressed JD Edwards database? To answer that question I ran some tests on a database that contained real customer data. The database was from an ERP 8 installation that was upgraded to ERP 9.0, some data was truncated from the database in order to speed up the upgrade process. The cut down database was still a healthy 371GB in size, uncompressed. The database was upgraded to ERP 9.0 using the standard JD Edwards upgrade process and after all the table conversions had completed the database was now using Unicode.

The database was first compressed (row compression) in SQL Server 2008 and this saw that size of the DB drop from 371GB to 161GB, that is a significant 56% reduction in size. I got a backup of this database and restored to a SQL 2008 R2 server, I then recompressed the database to take advantage of the Unicode compression now available in SQL 2008 R2. The addition of Unicode compression resulted in the database dropping to 118GB that is 68% smaller than the uncompressed database. Even though those numbers were great I decided to go in search of just a little bit more, so I uncompressed the whole database returning it to its original size of 371GB. I then compressed the whole DB again, using row compression, and the result was a database that was 111GB in size or 70% smaller than the uncompressed database.

These numbers demonstrate the kind of benefits you can achieve when using SQL Server 2008 R2 compression on your JD Edwards databases, and remember these numbers were achieved using real customer data. The test highlighted two things. The first is that Unicode compression in SQL 2008 R2 works and practically justifies an upgrade on its own. The second is that if you are using compression in SQL 2008 today, and you are planning an upgrade to SQL 2008 R2, then all you have to do is restore/attach the database to SQL 2008 R2 and recompress and you will get practically the same results as uncompressing the DB and recompressing it completely from scratch. The only question that remains to be answered is when are you planning your upgrade?

	Uncompressed	SQL 2008 Row Compression non-Unicode	SQL 2008 R2 Row Compression Unicode (recompressed)	SQL 2008 R2 Row Compression Unicode (from scratch)
Size in GB	371	161	118	111
% Smaller		56%	68%	70%

Trace Flag 1117 - A Safety Net You Might Not Be Aware Of

rasmi1 — Wed, 12 Jan 2011 17:20:13 GMT

Are you running SQL 2008 for your SAP instance? If so, are you aware of trace flag 1117?

There are a couple of best practices that are important to keep your SAP instance running well on SQL Server. The first is to ensure that proportional fill is working correctly. A key best practice of proportional fill is to ensure that your freespace is the same across all data files. For this reason, we have always recommended another best practice was to manually grow your datafiles. This was because when SQL Server autogrows data files, it does so one data file at a time in a round robin fashion. When this happens, SQL Server autogrows the first file, writes to it until it is filled and then autogrows the next data file. Now your proportional fill has been corrupted.

In SQL 2008, trace flag 1117 has been created to address this problem. If trace flag 1117 is set, then when SQL Server determines that it needs to autogrow a data file, it autogrows all of the files at the same time. If you want more detail about this trace flag, please refer to SAP Service Marketplace Note 1238993.

We still recommend that you monitor your data files and grow them manually, growing all of you files at one time and by the same amount, but by setting trace flag 1117, you will put a safety net in place that will protect you increase SQL Server beats you to this task.

Mission Critical Support for SAP and LOB Applications

Bryan McCutchan1 — Wed, 29 Dec 2010 15:30:39 GMT

In the IT community, we often talk about mission critical applications with the need to achieve high availability and have proper disaster recovery to achieve business continuity. However, it's amazing to me how many companies running SAP or other LOB applications (that are an integral part of successfully running their business) fail to have the most basic of high availability and support plans and mechanisms in place. This can range from the seemingly ("no-brainer") simple (e.g. have those backups ever been restored from scratch to truly test the backups and plan?) to the more comprehensive aspects of support (e.g. are the right support contracts in place with business partners or vendors to have fully covered, 24x7 support specific to the context of a "mission critical" application and covering ALL technology components). As well, it's important to cover both reactive (picking up the infamous "red bat phone") to proactive (thinking ahead in a holistic manner covering "what-if" scenarios with even regularly scheduled "war games" for simulating system(s) failure).

That being said and specific to an SAP, I find that most SAP customers are familiar with SAP MaxAttention, SAP's tailored and comprehensive support solution. Although not every SAP customer is a MaxAttention support customer, the option for an increased level of support and attention is available for the demands of your business regarding continuity and high availability.

From a Microsoft perspective and specifically for those of you running SAP on the Microsoft Application Platform, you should also be aware of a relatively new offering from Microsoft Services: Microsoft's Premier Mission Critical (PMC) support . As an add-on to Microsoft Services Premier Support (which, in my strong opinion, every Microsoft platform customer running SAP or an LOB/enterprise application must have), PMC offers additional services for mission critical applications like SAP that are running on the Microsoft Application Platform. This enhanced service offering is tailored specific to your needs and has specific services ranging from solution validation and architectural reviews to dedicated proactive and problem resolution services. Working with SAP Support, Microsoft Services will ensure that your entire solution stack is covered.

Finally, a story to wrap up as this topic reminds me of a customer visit to Microsoft several years ago by the U.S. Army. A colleague of mine was presenting to several generals about running SAP on the Microsoft platform and a high availability discussion started. However, the conversation was momentarily preempted by one of the older generals who turned to my co-worker and said, "Son, let me tell you about 'mission critical'. Mission Critical for us is about lives at stake!" Needless to say, the conversation went on from there with a perhaps more serious tone than with other customers! But it also highlighted the fact that the definition of "mission critical" is indeed often different from one company or organization to the next and this fact must also certainly be accounted for (hence not forgetting the business architecture piece to even this equation!).

As 2011 is upon us and New Year's resolutions are made, I encourage you to take a step back and think hard about and review your services and support strategy for your SAP and LOB applications. Microsoft Services and our LOB CoE is here to help.

Until next time,
Bryan...

Thinking for a Change

rasmi1 — Thu, 16 Dec 2010 14:13:00 GMT

Thinking for a Change

I thought I would take a break from all of the technical blogs and write about my favorite book of all time. I am a huge John Maxwell fan. I have read nearly all of his books. In 2003, he released a book called, “Thinking for a Change: 11 Ways Highly Successful People Approach Life and Work”. I devoured this book and since reading it in 2003, I make it a point to re-read it every year.

Albert Einstein once said, “We can’t solve problems by using the same kind of thinking we used when we created them.”

In 2003, when I first read this book, I was working for a company that was in trouble. In a period of 18 months, the business went from $2 billion in revenue to $500 million. We had cut our workforce by nearly 60% and were facing another round of cuts. I had taken the position with this company to lead a team of six people doing SAP Basis and SAP Security work. I had already seen 4 of the 6 people let go and I was facing losing the other two. I knew I had to act quickly to reduce cost, but I didn’t know how.

Thinking for a Change walks you through 11 ways to look at problems and address them. They are:

1. Acquire the Wisdom of Big-Picture Thinking

2. Unleash the Power of Focused Thinking

3. Discover the Joy of Creative Thinking

4. Recognize the Importance of Realistic Thinking

5. Release the Power of Strategic Thinking

6. Feel the Energy of Possibility Thinking

7. Embrace the Lessons of Reflective Thinking

8. Question the Acceptance of Popular Thinking

9. Encourage the Participation of Shared Thinking

10. Experience the Satisfaction of Unselfish Thinking

11. Enjoy the Return of Bottom-Line Thinking

As I read this book, I started to spend some serious time employing these techniques to my situation. I believe I had the “Importance of Realistic Thinking” down pat. If I didn’t find other ways to cut cost, they would continue to cut people. I did begin with Big-Picture thinking, trying to see the big picture and how I can leverage this to show the value of my remaining people to the long term success of the company. If I could show how we could cut costs in other areas without cutting people, then I was hopeful that I could save my two remaining employees.

As the big picture crystalized in my mind, I then turned to focused thinking. Where were the areas that would return the biggest bang for our buck. I knew this would be hardware, but I faced a huge problem, our hardware was in year one of a three year lease. As I focused on this, I realized that while my business unit was doing poorly, other business units were experiencing growth. I contacted the corporate IT department and asked them if they were planning to acquire any new hardware. They were and they were looking to lease the exact servers that I already had. I approached our CIO with the idea to reduce our hardware and by doing so; we would save $1 million. He presented me with a challenge. He told me I could move forward with my plan, but I had to maintain the current performance levels while reducing the hardware.

The next technique I used was the “Energy of Possibility Thinking”. I grabbed my one remaining Basis Administrator and we locked ourselves in a meeting room and began to run through all of the possible ways we could reduce the hardware but maintain current performance levels. At the end of the first day, we had some ideas, but nothing concrete.

A couple of days later he stopped by to pitch an idea he had. While his idea was good, it wouldn’t quite get us to where we needed to go, but it generated an idea for me. We got back in the conference room and starting tweaking both of our ideas and conceived a plan that we both thought would work. The great thing was that if this worked, it increased our savings to $1.2 million. When John Maxwell described this as the “Energy” of Possibility Thinking, he nailed it. We were both very pumped and excited about the possibilities our joint thinking had brought forth.

Next we needed to try “The Power of Strategic Thinking”. We couldn’t just yank all of the systems and software out and hope for the best. We needed to be strategic and we needed a very solid plan. We put together a phased plan that we would remove portions of the hardware everyone weekend for six weeks. This would give us the ability to determine if performance was being impeded before moving on to the next hardware reduction. The CIO liked this idea and we began moving forward with it. Six weeks later, $1.2 million of equipment had been removed and the end users never realized anything had happened. It was an overwhelming success and it saved those two jobs for another 18 months before the business took another hit and we had to reduce our staff again.

While we did not use every technique, we used a few of them. Over the years I have used all of them. “Question the Acceptance of Popular Thinking” has been a key one for me over the years. It helped lead us at my previous employer to accomplish what many had said was impossible. Our project had failed twice because of integration issues. By challenging the accepted path for integration, we got the team members to consider other possible ways of deploying integration. Eventually we changed directions which enable us to be successful. I have used this principle personally to help make sure I have not fallen into traps because of faulty thinking or beliefs.

“Embrace the Lessons of Reflective Thinking” is very important. Whether your project was a complete success or a complete failure you need to reflect on it to determine what you did right and what could have been done differently. It helps you learn from mistakes and solidify positive practices.

These thinking techniques can be applied to your professional or personal life. The book is full of quotes and examples. If you like to read and would like to read a book that might inspire you to a new level of thinking, then this book might be for you. I leave you with this quote from Mahatma Gandhi,

“A man is but the product of his thoughts, what he thinks, he becomes.”

Monitoring the Performance of your SAP SQL Database Files

rasmi1 — Thu, 16 Dec 2010 13:05:00 GMT

Monitoring the Performance of Your SAP SQL Database Files

The driving force behind outstanding performance with SQL Server is a technique called Proportional Fill. This is how SQL Server writes to your file groups. When running SAP on SQL, only the PRIMARY file group can be used. The principal behind this technique is to write the data across all data files proportionally according to the amount of free space that is available for each file. Here are a few examples:

Example 1 – SQL needs to place data on three extents within your database

· File1 has 100 MB free space

· File2 has 100 MB free space

· File3 has 100 MB free space

· SQL Server will place one extent of data on all three files

Example 2 – SQL has 5 extents of data to be placed on the database

· File1 has 50MB free space

· File2 has 100MB free space

· File3 has 100MB free space

· 1 extent on File1 and 2 extents each on File2 and File3 because twice the freespace is available.

Example 1 provides the optimal solution in spreading out data across all files equally. Example 2 is not optimal because you have more data going into file2 and file3 than file1. This can cause hot spots on the disk and unbalanced I/O which can lead to poor performance.

When all of your files are full, SQL Server will extend one file (in round robin fashion) to allow the database to grow. Autogrowth must be enabled to make this happen. This is also not what you want because it will cause your database to be disproportionately filled. If you files are getting close to being filled then you should manually extend all files by the same amount to maintain the proper proportional fill. With SQL Server 2008, this behavior can be modified by trace flag 1117. (See SAP Service Marketplace note 1238993) If trace flag 1117 is turned on, then when SQL Server goes to extend that first data file, it will extend all data files, preventing a possible problem with proportional fill. This trace flag is only valid for SQL Server 2008.

So, how do you determine if you have a problem? Well, there are a few ways that we will look at. You can get a good idea through SAP using transaction DBACOCKPIT.

Validate Data File Sizes

Logon to SAP and then go to transaction DBACOCKPIT. You will the need to extend the SPACE folder and then select Overview. On the Display on the right side of the screen, select the FILES tab. See below.

As you can see from the screenshot #1 above, all of the files are sized at 15.755 MB and the free pct is almost identical at 9.00. NW7DATA1 is at 6.00. This is probably because it has just grabbed some additional space and the other data files will expand very soon too. NWLOG1 is the Tlogs and not a part of this discussion. The second screenshot above is the right side of the screen and shows that AUTOGROWTH is on with enough space to extend.

This is an example of what you want to see. All of the files are the same size with about the same amount of free space.

Next you want to look at your ms/read and ms/writes. If proportional fill is working correctly then the reads across the datafiles should almost be identical and the same should be true of the writes. You can find this in the DBACOCKPIT transaction too. Expand the PERFORMANCE folder on the left and then double click I/O Performance.

The above example is from a lab system and shows you what you don’t want to see. In the ms/Read column you can see the reads vary from 33,923 to 67,207. In the ms/Write column the numbers are closer, ranging from 11,625 to 16,222. Listed below is an export from a system that is running very well:

File	Partition	Type	Ms/Op	Ms/Read	Ms/Write
NW7_01	F:	Data	8.750	7.165	10.519
NW7_02	G:		8.856	7.296	10.424
NW7_03	H:		8.914	7.329	10.784
NW7_04	I:		8.955	7.362	10.848
NW7_05	J:		8.965	7.399	10.734
NW7_06	K:		8.964	7.405	10.628
NW7_07	L:		9.099	7.547	10.578
NW7_08	M:		9.128	7.575	10.831
NW7_log	T:	Log	2.104	9.055	1.740

Notice that the read and write times are very similar. This is exactly what you want to see.

That is two ways from within SAP that you can do a cursory check on your performance. If you would like to go a little deeper, there are a couple of SQL statements that can be executed to provide you with additional information. This information is covered in greater detail in a blog by Juergen Thomas called Proportional fill and how to check whether it works.

The first SQL statement will help you analyze your data files. It will look at the total number of reads and writes against all of your data files and then it will calculate the average number of reads/writes and compare that against the actual reads/writes. This will show you a percentage off each data file is from the average. Here is the SQL:

declare @avg_read bigint, @avg_write bigint

select @avg_read=avg(io.num_of_reads), @avg_write=avg(io.num_of_writes)

from sys.dm_io_virtual_file_stats(db_id(), -1) io, sys.database_files df

where io.file_id=df.file_id and df.type_desc = 'ROWS'

select io.file_id, df.name, io.num_of_reads, @avg_read as 'Avg Reads',

ceiling(((convert(decimal(10,0),num_of_reads)/@avg_read)*100)-100) as 'Percentage off Read Avg', io.num_of_writes, @avg_write as 'Avg Writes',

ceiling(((convert(decimal(10,2),num_of_writes)/@avg_write)*100)-100) as 'Percentage off Write Avg' from sys.dm_io_virtual_file_stats(db_id(), -1) io, sys.database_files df

where io.file_id=df.file_id and df.type_desc = 'ROWS'

You can execute this in SQL Management Studio. It will produce output that will look like the following:

File_Id	Name	Number of Reads	Avg Reads	Percentage off of Read Avg	Number of Writes	Avg Writes	Percentage off of Write Avg
1	NW7DATA1	52640796	43631430	21	2769624	3343182	-17
2	NW7DATA2	56839043	43631430	31	3251608	3343182	-2
3	NW7DATA3	57449320	43631430	32	3403255	3343182	2
4	NW7DATA4	59263332	43631430	36	3118588	3343182	-6
5	NW7DATA5	57061251	43631430	31	2911036	3343182	-12
6	NW7DATA6	58347537	43631430	34	2893586	3343182	-13
7	NW7DATA7	30797062	43631430	-29	4622042	3343182	39
8	NW7DATA8	30716323	43631430	-29	4616088	3343182	39

This is an example of a system that has some problems. In a system that is operating normally, you would expect the reads/writes to be very close in number and the percentages to be -2 to 2. This system had files that had been added, but the free space was not adjusted and proportional fill was out of sync.

The next check is a stored procedure that will analyze a table and see how it is allocated across your data files. Once again, you will execute this within SQL Server Management Studio. You will need to create the stored procedure and then execute it with the table you have selected. I would recommend that you use one of the largest tables in your database. You can easily determine this in DBACOCKPIT, expand the SPACE folder and double click Largest Tables.

Here is the stored procedure code:

drop procedure sp_tablepart

create procedure sp_tablepart @tname sysname as

begin

set nocount on

declare @extentinfo9p table (file_id int, page_id int, pg_alloc int, ext_size int,

obj_id int, index_id int, partition_number int, partition_id bigint, iam_chain_type varchar(20), pfs_bytes varbinary(8))

declare @exec_statement varchar(512), @tname2 sysname, @dbid int, @fname sysname, @page_count bigint

set @dbid=db_id()

set @tname2 = quotename(@tname)

set @exec_statement = 'dbcc extentinfo(' + convert(varchar,@dbid) + ',' + @tname2 + ') with no_infomsgs'

insert into @extentinfo9p exec (@exec_statement)

select @page_count = SUM(pg_alloc) from @extentinfo9p

select distinct (ei.file_id) as 'File ID', df.name as 'Name',

fg.name as 'Filegroup Name', SUM(ei.pg_alloc) as 'Pages allocated',

ROUND(((convert(decimal,SUM(ei.pg_alloc))/convert(decimal,@page_count))*100),1) as Percentage

from @extentinfo9p ei, sys.database_files df, sys.filegroups fg

where ei.file_id = df.file_id and df.data_space_id=fg.data_space_id

group by ei.file_id, df.name, fg.name order by ei.file_id

end

To execute the stored procedure enter this command:

sp_tablepart ‘schema.tablename’

Example: sp_tablepart ‘nw7.BSIS’

You will receive output similar this:

File ID	Name	Filegroup Name	Pages allocated	Percentage
1	Data1	PRIMARY	16928	3
3	Data2	PRIMARY	17327	3
4	Data3	PRIMARY	17154	3
5	Data4	PRIMARY	17430	3.1
6	Data5	PRIMARY	18418	3.2
7	Data6	PRIMARY	17703	3.1
8	Data7	PRIMARY	18808	3.3
…	…	…	…	…
30	Data29	PRIMARY	17825	3.1
31	Data30	PRIMARY	17751	3.1
32	Data31	PRIMARY	17852	3.1
33	Data32	PRIMARY	17545	3.1

This is an example of a table that has been proportionally distributed across all data files and is exactly what you are looking for.

Correcting Proportional Fill Issues

The best way to correct any problems you might be experiencing is by performing a homogeneous system copy. You will find guides on the SAP Service Marketplace to help you with this. Once you have exported your database, you would then re-create your data files ensuring that they are all the same size. Once you perform the import, your data should be proportionally distributed. This is a very time consuming process and many of your will not have the luxury of getting this much down time.

Another alternative is to re-size your existing data files. Make sure that each and every file has the same amount of freespace. This will cause SQL Server to begin to proportionally fill the data files again. It will not correct what has already been done.

All in all, if you setup and maintain your data files correctly, this should not be an issue. If you are running SQL Server 2008 and higher, set trace flag 1117. I hope this blog will help you in maintaining excellent performance on your system.

Configuring your SQL File Systems

rasmi1 — Wed, 15 Dec 2010 17:28:32 GMT

Configuring Your SQL File Systems

SQL Server will perform much better when the file systems are configured correctly. In this blog entry, I will show you how to validate the configuration on your database file system. In my next blog, we will look at the setup and performance of your files.

First, let’s start off with file systems. We need to make sure of the following:

· Partition starting offset

· Stripe Unit Size

· File Allocation Unit (cluster) size

Configuring these three settings has been commonly known to increase I/O performance by 10% to 30%.

Partition Starting Offset

What is this and why is it important? Before Windows Server 2008, when creating a file system the first 63 sectors were hidden. Windows Server stores data in tracks that consist of 64 sectors. Since 63 doesn’t equal 64, this creates a problem. When the operating system goes to write to this file system, it must first fill the 64^th sector before beginning to write on the next track. This causes there to be multiple I/O’s to read this information which is inefficient.

Here is what you want your offsets to be set to:

· 32K - 32,768 (Exactly – if you are set to 32,256 this is misaligned)

· 64K – 65,536

· 128K – 131,072

· 256K – 262,144

· 1M – 1,048,576

To determine the offset for each filesystem, use the wmic command to get this information:

C:> wmic partition get blocksize, startingoffset, name, index

In the example above, Disk #2, Partition 0 is pointing to a file system that is not aligned correctly. Instead of 32,256, it should be set to 32,768. This would be for file systems that are not used by SQL Server. For file systems used by SQL Server, you want the starting offset to be set to 1,048,576 which is shown above Disk #13, Partition 0. In Windows Server 2008 and above, this is the default starting offset.

Caution: This does not work for dynamic disk. You must use the fsutil command which is not covered in this blog.

So what do you do if your file systems are misaligned? You have to backup the files on that file system and then re-format the drive to align it properly. That it is why it is very important to set them up correctly.

Stripe-Unit Size

With RAID technology, data is striped across multiple physical disks. The stripe-unit size is the amount of data stored on one disk before data is subsequently stored on another disk. This parameter should be set to closely match the size of the system I/O requests. The typical stripe-unit size is 8K, 16K, 32K, or 64K. You will need to check with your SAN Engineer to determine what the stripe-unit size is set to.

The formula to determine if these values are in line is Partition_Offset/Stripe_Unit_Size and it must return an integer value(whole number).

Here are two examples, one good and one bad on this configuration:

Example 1

· Partition offset = 32,768 (this disk is properly aligned)

· Stripe Unit Size = 65,536 (this is a proper stripe_unit_size)

· Partition_Offset/Stripe_Unit_Size = 32768/65535 = .5

· Still misaligned, the result of the formula is not an integer

Example 2

· Partition Offset = 65,536

· Stripe Unit Size = 65,536

· Partition_Offset/Stripe_Unit_Size = 65536/65536 = 1.0

· Volume is aligned properly

File Allocation Unit

File Allocation Unit which is sometimes referred to as a cluster, is the unit of disk space allocated for files and directories. This is the smallest logical amount of space that can be allocated to hold a file. On most file systems, you would want this to be as small as possible so there is not much wasted space, but on a file system that holds data files a large file allocation unit is desirable.

The formula to determine proper alignment is Stripe_Unit_Size/File_Allocation_Unit and it must also be an integer.

You can determine the allocation unit size using the chkdsk command:

C:> chkdsk E:

Example 1

· Stripe Unit Size = 65,536

· File Allocation Unit = 65,536

· Stripe_Unit_Size/File_Allocation_Unit = 65536/65536 = 1.0

· This is aligned properly

Example 2

· Stripe Unit Size = 65,536

· File Allocation Unit = 32,768

· Stripe_Unit_Size/File_Allocation_Unit = 65536/32768 = 2.0

· This is aligned properly

Example 3

· Stripe Unit Size = 32,768

· File Allocation Unit = 65,536

· Stripe_Unit_Size/File_Allocation_Unit = 32768/65536 = .5

· This is not properly aligned

By making sure that these parameters are setup correctly, you can ensure that your file systems are configured to maximize your I/O operations.

Caution: Please check with your disk vendor for their guidance on these settings. Most disk and SAN vendors provide best practices and guidance on these settings. In all situations you should test these type of settings to determine the optimal configuration for your company. For information on how to use SQLIO to baseline and test your disk subsystem you can reference the following blog: http://blogs.technet.com/b/lobapps/archive/2010/09/27/using-sqlio-to-baseline-performance-tips-and-tricks.aspx

Best Practices - SAN LUN Creation and Size Expansion for SAP Architectures Continued - Chapter 2

Jock Williams — Tue, 07 Dec 2010 23:01:00 GMT

In the previous blog about LUN Creation and Size Expansion we talked about one of the don'ts for creating large LUNs. So the next question thrown at me is what are the alternatives to SAN concatenantion (LUSE) for volume expansion and what is the performance difference (SAN concatenation vs. Disk Interleaving vs. Host and Sofware Striping)? Well let's first take into consideration that a disk drive today 2.5 inch form factor can deliver up to 200 I/O per second (IOPS). In reality this is much lower, but this is just an example. That means that in a concatenated configuration using two LDEVs from two 7D +1P Array Groups, only 1600 IOPS can be achieved because we are limited to one Array Group for current read/write operations (review first blog regarding concatenation). Take away the additional drive for parity and you have 1400 IOPs. Now take a disk interleave configuration using two LDEVs and you double the amount of IOPs achievable, because we now have the ability to strip the data across both LDEVs instead of being limited to one as with concatenation. Sounds pretty simple right! Well sounds that way, but I should probably explain disk interleaving. Disk interleaving is a SAN Based Striping method available on the HP XP and Hitachi Tagamastore platforms. For older models of the XP and the Tagmastore a certain firmware and microcode level must exist and disk interleaving was originally only available for usage with RAID 5 configurations on those models. In order to upgrade to this version downtime might be required. On the new SAN releases this is a built in feature and can also be accomplished with RAID 1 configurations also. So what can you do if your customer runs on the previously mentioned monolithic SAN version and does not have the right firmware or microcode to accomplish this? Consider host based striping as an option for improving performance.

Host based striping has always been a “no no” in the performance world due to the overhead management required by the operating system. However, when it comes to combating the usage of concatenation for the creation and expansion of a LUN, it is an excellent alternative. In the host based scenario, you would still have the SAN administrator create the LDEVs at the SAN level, but not perform any type of concatenation to create a large LUN. Instead the LDEVs are presented to the Windows operating system as basic disk and stripped together as dynamic disk. Keep in mind that I stated “stripped” which is an option for creating Dynamic Disk and crucial to the performance of this configuration. This configuration allows data be striped (e.g. RAID 0) across multiple LDEVs instead of concatenating the LDEVs together as in a LUSE volume. The chart in the figure below shows IO comparison between a 2D+ 2D (RAID 10), 3D+1P (RAID 5) using 64 disk and 7D+1P utilizing 128 disk.

For cluster configurations customers must use VERITAS Volume Manager. The reason for this is that Dynamic Disk is not support for MSCS. In order to implement host based striping for a cluster environment the customer must Volume Manager (KB816307). As can be seen below, using Volume Manager also yields good performance gains over using concatenation.

An excellent source for more information on host based striping performance can be found in the HP StorageWorks XP12000 Disk Array Configuration Guidelines for Microsoft Exchange Server 2003.

As you can see for the high end performance systems, there are better alternatives than the use of concatenation and they vary from SAN based to Host based configurations. I have only named all three striping methodologies in this blog (SAN, Host and Software Based Striping). This covers the LUN creation and best practice configuration part of the discussion. My next blog I will discuss growing those LUNs as the database grows. This is an area that has serious performance implications if done incorrectly. As always consult your customer’s storage vendor regarding the previously stated striping options and what is available for your customers today.

Note: All Pics in this document comes from published HP documents.

SAP TCO/Risk Analysis and my Session at the SAP Insider IT 2011 Conference

-ByGeorge- — Thu, 02 Dec 2010 16:56:00 GMT

I’m looking forward to presenting at the SAP Insider IT 2011 conference March 15-18 in Las Vegas...and have been developing my presentation this week. One of my passions over the last 15 years is SAP and other LOB application Total Cost of Ownership (TCO) analysis. For many IT organizations, the decision to ‘replatform’ or migrate from one computing platform to another is based first on cost. Will we save money moving to Windows/SQL? Does the Linux/Oracle platform really reduce my costs compared to AIX/DB2?

The idea of saving money is simple, but the reality is complex. A simple TCO analysis breaks out the technology stack into its various layers and seeks to assign costs to each layer. At the simplest level, an LOB application technology taxonomy comprises:

· Data center/hosting facilities

· Hardware (servers, disk subsystems, network infrastructure, and other required physical assets)

· Operating system (license cost)

· Database (license cost)

A very basic TCO model might be described as:

TECHNOLOGY A+B+C+D Acquisition Costs

Interestingly, the SAP application layer doesn’t really need to be included in the list above, as it’s essentially the same cost regardless of the underlying computing platform (HW+OS+DB). This simple model is fine for starters, but it has significant shortcomings. Smarter TCO models extend the technology taxonomy to include middleware and other integration technologies, client devices, and so on. But at the end of the day, such a ‘technology-focused’ view of cost is inherently limited in that it only captures point-in-time costs (or more specifically, the acquisition costs associated with each technology). A better TCO model factors in operations costs across a reasonable timeframe, i.e. 3 years. By including these lifecycle costs, we can arrive at a much stronger estimation of what one platform costs vs a different platform.

TECHNOLOGY Acquisition + TECHNOLOGY Operations Costs

Capturing technology acquisition costs plus ongoing lifecycle costs is good, but it’s still limited. What about the “process costs” associated with migrating, installing, patching, administering, operating, and generally running each platform? These costs can vary considerably. In fact, they vary over time as well (as an organization grows adept at managing and maintaining its IT platforms). So a better TCO model might look like this:

TECHNOLOGY Acquisition & Operations + PROCESS Acquisition & Operations Costs

Who makes sure all these processes are executed? Who installs the technologies? Who maintains and operates the platform? People! And as we all know, the cost of people can vary significantly. Windows and SQL administrators are more readily available and less expensive than their UNIX, Oracle, and DB2 counterparts. Various hosting providers charge different rates for different skillsets. And so on…. Thus, a really good TCO model should factor in the cost of people – a cost that varies over time just as technology and process costs vary over time. Typically, we people get more expensive….though that’s not always the case. For example, a North American firm that transitions to a global support model should see its costs decrease). Regardless, a pretty strong TCO model can then be described as follows:

TECHNOLOGY A&O + PROCESS A&O + PEOPLE A&O Costs

This Technology+Process+People (or TPP) representation of TCO has become something of a defacto standard for identifying and presenting costs models. We can apply this model to SAP platforms just as easily as collaboration platforms, BI/Analytics platforms, Manufacturing Execution System (MES) platforms, and so on.

But there’s a problem with the TPP model, which brings me to my session I’ll be presenting at the BI/IT conference in March 2011. As most of us realize, TCO is only one of a CIO’s hot buttons. What about time-to-value? What about the impact of change? And perhaps most importantly, what about RISK??? A really effective TCO model should incorporate TPP costs as well as the ‘costs’ or impact of various risks, giving us a formula like this:

T/P/P A&O Costs + RISK Costs

In my SAP Insider IT2011 session, I’ll walk through what I’ve described above and then I’ll outline how we can better inform our traditional TCO models with this concept of risk. I call my approach “Cost and Risk Modeled Architecture” analysis, or CARMA analysis. CARMA is based on a Microsoft-developed taxonomy of 10 primary risk factors which in turn are comprised of 40 unique and quantifiable subfactors. The 10 factors include:

1. AppPlat Deployment/Migration (Deploy)

2. AppPlat Hosting (Host)

3. AppPlat Availability (Down)

4. AppPlat Agility/Flexibility/Scalability (Agility)

5. AppPlat Performance (Perf)

6. AppPlat User Community (Users)

7. AppPlat Management (Mgmt)

8. AppPlat Strategic Vendor Alignment (Align)

9. AppPlat People/Org (People)

10. AppPlat Process Maturity (ProcMat)

Check out my session abstract:

Thought leadership: Using risk factors to better quantify SAP architecture design TCO

Dr. George W. Anderson, Microsoft

Enterprise architecture designs are notorious for hidden costs, problematic value propositions, and uncertain return on investment (ROI). With regard to technology platforms for mission-critical applications like SAP, there’s simply no clear cut answer as to which truly offers the lowest cost of ownership or most rapid ROI. This session offers a proven methodology for calculating and credibly comparing the weight of risks inherent to various technical platforms. Learn how to assess total cost of ownership (TCO) using Cost and Risk-Modeled Architecture (CARMA), a method of informing traditional TCO analysis through the application of risk factors. Understand how CARMA enables business and technology architects to quickly identify the technology, people, and process areas that most likely affect their TCO. Explore a method of “weighting” SAP TCO analyses to enable better-informed vendor-agnostic decisions as to the ideal technical platform for a proposed ERP implementation or platform migration.

At Microsoft, we’ve begun developing a tool that will help our customers and prospective customers capture all these cost and risk data to provide better visibility into what it means to replatform. It comprises TPP as well as the 10 risk factors/40 subfactors. We like the idea of quantifying these risks of change, i.e. the risks of going against the status quo or "going out on an unfamilar limb" (which is what Frank Scully said, completing his though with "after all, isn't that where the fruit is?!"). So stay tuned! And I hope to see you in Vegas where we can talk more about this model, what's missing, and how to make it more effective….

george

LOB Workload Testing: Single Unit, Load, Stress, and Smoke Testing

-ByGeorge- — Wed, 24 Nov 2010 04:48:00 GMT

Testing the performance of a particular mission-critical workload on your Line of Business application (SAP ERP or SCM, Dynamics GP, Oracle Siebel CRM, Peoplesoft HR, JDE, and so on) is part of a smart IT organization’s life. Based on your IT organization’s budget, headcount, bandwidth, and other available resources, though, the particular type of workload testing you have the time and money to execute might vary.

We all know the drill – sure, it makes sense to load test SAP or any enterprise application prior to “going live” or after making significant upgrades in functionality or the application’s technology layers. After all, the risk of not doing so is enormous, and is reflected in poor response times, missed SLAs, unplanned downtime, and even pointed articles in the Wall Street Journal. But complete end-to-end load testing takes time, and isn’t cheap, especially when it comes to testing cross-application business processes or evaluating the impact made by thousands of concurrent SAP users. Indeed, with investments in people, software, and testing hardware exceeding hundreds of thousands of dollars a year, even some of the most risk-averse LOB applications IT shops “ignore” load testing, choosing to cross their fingers or pray for smooth sailing instead.

Don’t let the challenges sway you into avoiding load testing altogether, though. There are quite a few alternatives to expensive traditional methods of load testing, four of which are compared and contrasted here. Before moving forward, set aside any preconceived notions you might have, and continue reading with an open mind and a willingness to consider a few alternative methods and approaches to LOB workload testing.

Four Types of Workload Testing

Load testing is a fairly misused term. To many, it’s synonymous with “stress testing” or “performance testing.” Others lump it into user testing or some kind of technology-specific validation testing. All of these have merit, and I’ve used them interchangeably myself over the years. For our purposes here it makes sense to define load testing, though, as one of four different types of what is better described as SAP workload testing, as follows:

Single-unit testing

Average workload load testing

Peak workload stress testing

Maximum sustainable workload smoke testing

These four types of workload testing are easier to differentiate when they’re mapped relative to one another; refer to Figure 1 below.

Figure 1: The Four Types of Workload Testing

Single Unit Testing

The simplest type of workload testing is sometimes called single unit end-user testing. It's very basic but can be useful. Single unit testing is appropriate for very simple system changes where the maximum acceptable load can be easily extrapolated from the performance of a single end user. For example, a customer changing a bit of functionality in a single module within their SAP ERP system might be a good candidate for such an approach (maybe...). It might be as easy as running a sample transaction before it's changed, running it again after the change, and comparing run-times or the delta in "wall clock" time. This is often a good approach for testing batch job changes as well.

Average Workload Load Testing and Peak Workload Stress Testing

The next two types of workload testing – “average workload” and “peak workload” – may be more appropriate when thousands of users are hosted by a system (the former case), or validating SLAs for mission-critical business processes (the latter case). Such workload testing requires an investment in application-aware virtual load testing tools like those available from HP (used to be Mercury), Compuware, Borland (used to be Segue), and others. My personal favorites are LoadRunner and SilkPerformer. Freeware like OpenSTA is really useful too. Alternatively, IT shops may conduct real-world average load/peak stress testing by coordinating the activities of actual users in an ‘off-prime’ time-frame (prior to virtual testing capabilities, this was a fairly common though expensive and inconvenient option).

Maximum Workload Stress Testing

The fourth type of workload testing – “smoke testing” – is rare for most customer environments. But for systems where raw processing speed is of the essence, and time saved (i.e. reducing the execution window for key transactions or business processes that enable key decision-making) equates to a huge pile of cash, smoke testing may be the key type of workload testing to conduct. Smoke testing is all about identifying potential risks so they can presumably be mitigated. In my eyes, it's the coolest kind of testing, too. It's all about pushing a workload to the max to identify the first bottleneck that actually throttles system capacity. Picture pushing your servers and disks until they're "smoking" from all the work....you get the idea.

Conclusion

Based on an IT organization’s budget, headcount, bandwidth, and other available resources, a particular type of workload testing is called for. I suggest starting with single unit testing and then obtaining a test tool to do some kind of minimal average load testing. An excellent practice is to perform load testing as part of your quarterly promote-to-production process. In this way, you’re more likely to catch the really big showstoppers before your end-users do! That’s certainly a great place to start…. J

Best to you! (and for my friends in the States, enjoy Thanksgiving and Black Friday!)

George

SAP user and groups requirements in the active directory

GuidoSchmitt — Mon, 22 Nov 2010 08:58:05 GMT

Today for an SAP installation on Windows in an existing AD domain, some user and groups requirements are mandatory from SAP.

This article gives an overview of the necessary user and groups including the rights and permission.

Domain or local installation:

Before you install the SAP system, you have to decide whether you want to perform a domain or local installation, since this affects how the user account information is stored and accessed.

Domain Installation

In a domain installation, the user account information is stored centrally in one database on the domain controller and is accessible to all hosts in the system.

You have to perform a domain installation if one of the following applies:

- You install a distributed system (strongly recommended to avoid authorization problems).

- You install a high-availability system with WSFC

- You want to use Single Sign-On.

- You use a common transport host for several SAP systems running on different computers.

Local Installation

In a local installation, all Windows account information is stored locally on one host and is not visible to any other hosts in the system.

- If the SAP system is to run on a single machine (central system), you can perform a local installation.

- If your SAP system was installed as a local installation and you want to later change to a domain installation, you must perform a homogeneous system copy.

Normally in a domain installation, the SAPINST tries to create all the necessary users and groups in the AD, but therefore the user which runs the installations needs to be a domain admin user. If this is not possible you have to go for the following steps:

Performing a Domain Installation without Being a Domain Administrator

You normally perform a domain installation of the SAP system with a user who is a member of the domain Admins group, but if for any reason, the account used for the installation is not a member of the domain Admins group, you can perform the installation with a domain user who is a member of the local Administrators group. In this case, the domain administrator has to prepare the system appropriately for you. The domain administrator can perform the following steps either using SAPinst or manually:
(<SAPSID> is the SAP system ID, which identifies the whole SAP system)

- Create the new global group SAP_<SAPSID>_GlobalAdmin.

- Create the two new SAP system users <sapsid>adm and SAPService<SAPSID>.

- Add the users <sapsid>adm and SAPService<SAPSID> to the newly created group SAP_<SAPSID>_GlobalAdmin.

For the SAP installation itself (user, which is running the SAPINST program) you need the following rights (User Rights Assignment in Local Security Policy) on the local server:

- SeTcbPrivilege (Act as part of the operating system)

- SeIncreaseQuotaPrivilege (Adjust memory quotas for a process)

- SeAssignPrimaryTokenPrivilege (Replace a process level token)

The user, which is running the administration, must be a member of the local admin group of the server.

Overview of the operating system users including rights and permission necessary for an SAP Installation:

<sapsid>adm

This is the SAP system administrator account that enables interactive administration of the system.

Is local with the following rights:

- SeTcbPrivilege (Act as part of the operating system)

- SeIncreaseQuotaPrivilege (Adjust memory quotas for a process)

- SeAssignPrimaryTokenPrivilege (Replace a process level token)

- SeServiceLogonRight (Log on as a Service)

SAPService<SAPSID>

This is the user account that is required to start the SAP system.

Not member of the local admin group with the following rights:

- SeServiceLogonRight (Log on as a Service)

- SeNetworkLogonRight (Access this computer from the network)

- SeDenyInteractiveLogonRight (Deny Logon Locally and Deny log on through Terminal Services)

- SeRestorePrivilege (Restore files and directories)

sapadm

Not member of the local admin group with the following rights:

- SeNetworkLogonRight (Access this computer from the network)

- SeServiceLogonRight (Log on as a Service)

- SeDenyInteractiveLogonRight (Deny Logon Locally and Deny log on through Terminal Services)

Be careful: The user and group names has to be entered excatly as specified in the correct uppercase an lowercase.

Often this naming conventions conflicts with the requirements of the customer, but it is not possible to change the names of the users and groups, because all the upgrade and installation procedures of SAP a belonging on this convention.

UKOUG JD Edwards 2010 November 10th and 11th

jacksmccullough — Tue, 02 Nov 2010 11:04:00 GMT

I will be presenting a session at the UK Oracle Users Group JD Edwards conference on Getting the best out of SQL Server and JD Edwards. Co-presenting with me will be Terry Graham from Constellation Europe. Terry has recently upgraded CEs OneWorld ERP 8 instance from Windows 2003/SQL 2000 IA64 to Windows 2008, Hyper-V and SQL 2008. Terry will be discussing their architecture and sharing lessons learned and the outcome of the upgrade. The session is on Wednesday the 10th at 12:15pm at Twickenham Stadium just outside London. I have included the link for the conference for those who are interested in learning more:

http://jdedwards.ukoug.org/default.asp

Is there more to LOB than SAP?

jacksmccullough — Wed, 27 Oct 2010 10:13:39 GMT

Thomas's opening blog implied that LOB wasn't just about SAP but also JD Edwards, Peoplesoft and Siebel. So where is all the JDE, PSFT and Siebel content? It is time for a change, time to slow the tidal wave that is SAP, time for Iron Man to stand upto the Teutonic giant that is SAP! Well that might be a bit dramatic but it is certainly time for the Oracle Apps to get some coverage on this blog.

So why is Microsoft interested in Oracle Apps, specifically JD Edwards, Peopelsoft and Siebel? The short answer is because you are, a lot of Microsoft customers are running their Oracle Apps on our platform. When I speak about our platform I mean Windows Server and SQL Server but some of you might also be running Biztalk and Sharepoint as well. Those of you running your Oracle Application on SQL Server maybe considering an upgrade to SQL 2008 R2, especially if you are on SQL 2000 but even if you are one SQL 2005 (hopefully no one is on SQL 7.0 ort 6.5). We also know there are customers who want to migrate off their current platform to Windows/SQL Server. This might be from IBM iSeries/DB2 (AS/400) or Oracle (Windows/AIX/HP-UX/Solaris/Linux). Whatever you are on today Windows 2008 R2/SQL Server 2008 R2 is the perfect platform for running your Oracle Application on. There might be some reluctance (and even opposition) from those who are entrenched in the past, but Microsoft has silenced its critics, and in SQL 2008 we have an enterprise class, scalable, reliable, secure database that is better value both in licensing costs and total cost of ownership than our competitors.

Enough of the sale pitch I hear you cry! Okay but what about my original question - why is Microsoft interested in Oracle Applications? Because we have a great solution that we believe is not only the equal but in many cases better than our competitors. We are not just focused on existing users but are helping new users migrate to our platform and start reaping the benefits for themselves.

I will be back, with more technical content and also hope to provide some insight into what some customers are doing on our platform today. For now if there is any specific items of interest then please let me know.

Cheap vs. Cost-Effective: The Underestimated Role of the DBA

Thomas Dreller — Sun, 24 Oct 2010 00:40:10 GMT

Several weeks ago one of our joint customers with SAP complained about the capabilities of SQL Server and announced to migrate his SAP landscape to a competitive database platform if Microsoft wouldn’t fix “the problem”. The key issues, for which the database was blamed: Unacceptable performance, frequent downtime of the SAP application causing significant friction between the business and the IT organization.

Well, somebody who’s a little bit trained on isolating the potential root cause for such a scenario might ask some questions, e.g. following this generic approach:

Unfortunately, nobody was available to answer even basic technical questions: The customer has outsourced his infrastructure operations to a third party hosting provider.

Here’s what happened: Three years ago the customer had migrated his SAP landscape from Oracle/UNIX, originally running on expensive proprietary hardware, to SQL Server/Windows Server, now deployed on commodity hardware. This move yielded significant cost savings. The IT organization was then slashed, and the majority of operational tasks were outsourced – yielding further cost savings. Unfortunately that second step had two consequences which were not immediately visible: The DBA (Database Administrator) role was made redundant. And, since no infrastructure- or operations-competence remained in the customer’s IT organization, nobody was available to notice the absence of the DBA role in the Service Level Agreement (SLA) with the hosting provider. In the meantime several additional SAP applications were installed; since the initial re-platforming of the SAP system the size of the database has more than doubled, the number of concurrent users nearly tripled. The hosting provider literally met his obligations, as defined in the SLA – and nobody ever touched the database, except for back-ups.

Isn’t that a great confirmation of SQL Server’s product quality? An engine, running for nearly three years, supporting thousands of users 24x7 – without anybody being in the engine room?

The cheapest solution is never the most cost-effective solution: The opportunity cost resulting from slow application performance, plus the cost of unplanned downtime, regularly outweigh the savings from not employing a DBA. Running a business-critical application without any maintenance may be possible, but it’s definitely not a recommended practice. Although SQL Server is easier to operate and maintain than competitive databases, it still requires somebody to look after this important engine from time to time. And that exactly is the role of the DBA.

If you accept this line of thinking, than you’ll certainly scratch you head and ask yourself: Where would I find a good DBA, and what exactly are the accountabilities of that role?

Here’s a another DBMS vendor’s comprehensive overview of the DBA’s role. Microsoft’s understanding of the role goes beyond this basic description, and the importance of the role is demonstrated by the Microsoft SQL Server certifications: An MCITP has at least demonstrated that he has the basic knowledge for operating a business-critical system. Would that mean that he could actually operate such a system in the most effective way? No, not really. Additional guidance, some application specific training, plus lots of experience will be necessary. Nothing can compensate for experience, but the former two issues can be addressed by two means:

Brad McGehee has a nice check-list and best practices for the SQL Server DBA, and regularly blogs about this topic. And this is just one example: PASS - The Professional Association for SQL Server, has much more guidance to offer.
Microsoft offers a training specifically enabling Oracle DBAs – Practical SQL Server 2008 for the Oracle DBA - in addition to the normal SQL Server readiness courses.

But there is another question: If infrastructure operations are outsourced, how could an organization then be sure that the third party’s infrastructure staff meets the skill and capacity needs that are required when operating a business-critical application, such as SAP? How could this requirement be embedded into an SLA, in such a way that defined business availability, measurable performance characteristics, and minimum operational cost are maintained?

One alternative would be to be much more precise when defining the SLA. Unfortunately we regularly notice that most customers don’t seem to be mature enough to define SLAs precisely, and to include all necessary KPIs. A brief test for your own organization:

What are your three most mission-critical business processes?
Which SAP transactions are bound to these three processes, and which response times do you guarantee for each of these transactions?
Which interfaces are connected to the three processes, and how have you defined the SLAs for these interfaces?
Which batch jobs are related to these three business processes, and how do you guarantee that the maximum duration for these batch jobs is not exceeded? And, btw, what happens if one of these batch jobs is aborted because your batch window is closed?

The other – recommended - alternative: The DBA is not a member of the infrastructure team, the role is instead integrated into the SAP Basis team. And, additionally, the DBA is empowered to enforce infrastructure modifications, as appropriate. In customer environments where these two conditions meet we see significantly less performance issues, our unplanned outages. It’s quite obvious that the DBA then also needs to be involved in the KPI definition for the SLA. And, sometimes, it then turns out that the cheap outsourcing solution is not that cost-effective at all, at least not when the business requires the continuation of the same service level. Two more questions everybody in this business should ask himself: What’s the total cost to the business per hour downtime? And how much insurance coverage are you willing to pay for mitigating such risk? (Or, in tech lingo: Do you really want 5 nines – and not measured at the infrastructure level, but at the UI level?)

Somebody who underestimates, or even ignores, the role of an DBA in this kind of environment accepts a huge operational risk – and that doesn’t go well with business-critical, or even mission-critical systems. This risk has nothing to do with the products or technologies involved, but with governance. And it has to do a lot with compliance.