Yann Neuhaus

A few weeks ago, I went to the Swiss Database Days 2013 and followed the "Practical SQL Server Performance Troubleshooting" workshop. During this workshop, I have seen a different way of troubleshooting, but I was surprised to see that some participants were not aware that some basic SQL Server Best Practice do not depend on the instance settings. This blog posting is a reminder of some of these points.

 

Swiss Database Days 2013: SQL Server Best Practice

As I said, I was suprised to see that some workshop participants were not aware of (or did not pay attention to) the fact that some basic SQL Server Best Practice do not depend on the instance settings:

1. The power options of Windows Server
2. NUMA settings
3. Disks layout and configuration

 

Windows Server power options

Let's start with the Windows Server "power options". In the "Control Panel" under the "Power Options", you can change the settings to reduce the energy consuption.

I do understand that for laptop or desktop computers (using Windows 7 or 8) some energy saving options should be actived.

But I don't understand why Microsoft sets the "balanced" power plan for Windows Server per default, as this is a OS that will be used on servers.

The setting "Balanced" will reduce the performance and reaction of the server. In the worst case, people will buy a bigger server with more energy consuption to get more performance. So the recommandation is to put it on "high performance".

 

Non-Uniform Memory Access (NUMA)

My second point is NUMA. As the number of processors is increasing, it becomes more difficult to install a parallel access to the (shared) memory of all these additional processors.

NUMA tries to reduce this problem by dividing memory in different parts and giving a restricted access to groups of processors (Nodes). As memory is splitted, the number of parallel access will be increased and will reduce the number of conflicts accessing the memory. Memory allocated to another group of processesors can still be accessed, but this is done through the memory bus of the other processor group and will be slower.

I will not go into details here, if you want to know more about it, I have added some links below. What is important here is to make sure that the server hardware is used in the best way if it is NUMA compatible, so that it gives the best performances.

To verify the configuration, you can use the "coreinfo.exe" tool.
This can be downloaded here: http://technet.microsoft.com/en-us/sysinternals/cc835722.aspx

 

Disks layout and configuration

And now the last point: the disks. This is a very important point, but many people think that SANs or other disk systems have resolved this problem and that it does not need to be considered anymore.

Unfortunately, this is wrong! You should always take in consideration "why" the disk is used and "how". The responses to these questions will give you an answer of what can be used and also what cannot be used!

Then, depending on price and (needed) performance, the final choiCe can be made. Follow this link to have the different possible RAID configurations depending on needed performance and the amount of disks (price): http://blogs.technet.com/b/sql_server_isv/archive/2010/11/11/sql-server-drive-configurations.aspx

If you need more performance, you can also replace the RAID1 or RAID5 with RAID10 disks.

You also need to pay attention to the alignement of the MasterBoot sectors. If your disks have been formated in NTFS with Windows Server 2008 or higher, this is automatically done. If your disks were formated with older Windows versions, please check it with the following command in "cmd" and if needed align them.

• wmic.exe partition get BlockSize, startingOffset, Name, Index

To align them you need to reformate them, which means that you will loose everthing that is on the drive. Move your files somewhere else or make a backup of them, before formating the drives.

As I have stated before, these are only three basic points of SQL Server Best Practice that you should consider before any installation.

Here are some links with background information:

 

NUMA

http://en.wikipedia.org/wiki/Non-Uniform_Memory_Access
http://msdn.microsoft.com/en-us/library/ms178144%28v=sql.105%29.aspx
http://technet.microsoft.com/en-us/sysinternals/cc835722.aspx

Disks

http://technet.microsoft.com/en-us/library/cc966412.aspx
http://technet.microsoft.com/en-us/library/cc966534.aspx
http://blogs.technet.com/b/sql_server_isv/archive/2010/11/11/sql-server-drive-configurations.aspx
http://msdn.microsoft.com/en-us/library/dd758814%28v=sql.100%29.aspx

Good news for all SQL Server DBAs: DMVs and DMFs poster are now available for SQL Server 2012.

You can download them here.

 

You can choose between 2 formats: pdf and xps.

 

What's new?

You will notice the new category AlwaysOn:

You can see the ColumnStore Index in the Partitioning section:

If you want to know more about the new features of SQL Server 2012, please note that we are organizing an SQL Server 2012 New Features workshop.

During North America TechEd, Microsoft released some details on the upcoming release of Windows Server.
This new version is called Windows Server 2012 R2 and will be at the heart of the Microsoft Cloud OS.

Windows Server 2012 R2 is currently in preview and will be soon available for evaluation purposes.
Register to be notified when the new product evaluation will be available.

Apparently two versions will be available:

• Windows Server 2012 R2
• Windows Server 2012 R2 Essential (For SMBs)

You can get more information about these new versions here.

And if you want to have a very good introduction to Windows Server 2012 R2, watch the presentation of Jeff Woolsey (Principal Program Manager at Microsoft for Windows Server and Cloud) he performed two days ago during the North America TechEd 2013.

I will also certainly come back from the Madrid TechEd with some news on this subject and on the future SQL Server 2014 release...

See you soon!

To be compliant with the customer's security rule, I had to configure SSL for a JBoss application server that holds the Documentum Foundation Services (DFS). I used the following procedure:

 

1. Generate a keystore

Change it to a temporary location, e. g. /var/tmp/SSL:

keytool -genkey -dname "cn=dms.test.org, ou=DEV, o=NICE, l=Delemont, s=Switzerland, c=CH" -keyalg "rsa" -validity 730 -alias tomcat -keysize 2048 -keystore dfs.keystore
Enter keystore password:  xxxx
Enter key password for
        (RETURN if same as keystore password): xxxx

Important:

• always use tomcat for the alias
• enter a validity value, otherwise it will be 90 days

Warning: Due the Bugzilla issue 38217, both keystore and key passwords have to be the same!

 

2. Create a Certificate request

keytool -certreq -alias tomcat -file jbossDfs.csr -keystore dfs.keystore
Enter keystore password:  xxxx
Enter key password for xxxx

 

3. Send the jbossDfs.csr file to the service that will signe the certificate

In our case, the customer has its own CA system and will return the signed certificate plus the trusted chain composed of root.cer and user.cer.

 

4. Import the certificates

Once all needed certificates are delivered, they can be imported into the keystore. Before importing the signed certificate, import the chain one.

4.1 Import the root certificate

keytool -import -alias cert1 -file root.cer -keystore dfs.keystore
Enter keystore password:  xxxx
Owner: CN=....
Issuer: CN=....
Serial number: .....
Valid from: Mon Feb 04 09:23:02 CET 2013 until: Wed Feb 04 09:33:01 CET 2037
Certificate fingerprints:
         MD5:  94:40:.....
         SHA1: D0:10:....Trust this certificate? [no]:  yes
Certificate was added to keystore

4.2 Import the user certificate

keytool -import -alias cert2 -file user.cer -keystore dfs.keystore
Enter keystore password:  xxxx
Certificate was added to keystore

 

4.3. Import the signed certificate

keytool -import -trustcacerts -alias tomcat -file jbossDfs.cer -keystore dfs.keystore
Enter keystore password:  xxxx
Enter key password for xxxx
Certificate reply was installed in keystore

 

5. Update server.xml to activate SSL

cd $DOCUMENTUM_SHARED/jboss4.2.0/server/DctmServer_MethodServer/deploy/jboss-web.deployer/

With vi update server.xml:

• uncomment the SSL definition
• set SSLEnabled from false to true
• Change the default password "changeit" to the one you used to secure the keystore password:

maxThreads="150" scheme="https" secure="true"
keystoreFile="${jboss.server.home.dir}/conf/dfs.keystore" keystorePass="xxxx"
clientAuth="false" sslProtocol="TLS" />

 

6. Change the server.xml permission

If groups and users can read the file, change the file permission to allow only the owner to read/write:

chmod 600 server.xml

 

7. Copy the keystore to the correct location

cp
/var/tmp/SSL/dfs.keystore
$DOCUMENTUM_SHARED/jboss4.2.0/server/DctmServer_MethodServer/conf/

 

8. Restart the JBoss server

You can check the server.log to see if everything is starting fine.

 

9. Test it

Perform a test using using the URL https//dms.test.org:9082/services/core/ObjectService.

The JBoss server now should be ready to manage https requests!

Please note that the client part is not covered in this post.

Microsoft has just published the Fourth Cumulative Update (CU4) for Microsoft SQL Server 2012 SP1. If this CU covers an issue you are experiencing, you should install it quickly. Otherwise, it is not mandatory.

Which issues are covered by CU4?

This CU4 contains 39 Hotfixes:

• 24 for the Engine
• 3 for SSIS
• 7 for SSAS
• 5 for SSRS

If you need more information and/or details on these hotfixes, click here.

Historic of SQL Server 2012

The build number of this fourth Cumulative Update is 11.00.3368.

Let's resume the different versions of SQL Server 2012 we have for the moment:

Date SQL Server 2012 version Build

November 2010

Community Technology Preview 1 (CTP1)

11.00.1103

July 2011

Community Technology Preview 3 (CTP3)

11.00.1440 

November 2011

Release Candidate 0 (RC0)

11.00.1750 

December 2011

Release Candidate 1 (RC1)

11.00.1913 

March 2012

RTM

11.00.2100

April 2012

Cumulative Update 1 (CU1)

11.00.2316

June 2012

Cumulative Update 2 (CU2)

11.00.2325

August 2012

Cumulative Update 3 (CU3)

11.00.2332

October 2012

Microsoft Security Bulletin MS12-070

11.00.2376

October 2012

Cumulative Update 4 (CU4)

11.00.2383

November 2012

Service Pack 1 (SP1)

11.00.3000

November 2012

Cumulative Update 1 (CU1)

11.00.3321

January 2012

First Hotfix for SP1

11.00.3335

January 2013

Cumulative Update 2 (CU2)

11.00.3339

March 2013

Cumulative Update 3 (CU3)

11.00.3349

April 2013 Second Hotfix for SP1 11.00.3350 May 2013 Cumulative Update 4 (CU4) 11.00.3368

On the same time, a Cumulative Update has also been published by Microsoft for SQL Server 2008 SP3. It is the 11th (CU11). You can have a look at it here.

A hotfix has been also released in April. It covers the impossibility to create or open SSIS projects or maintenance plans after applying Cumulative Update 3 for SQL Server 2012 SP1. More information about this hotfix is available here.

At TechEd North America 2013, Microsoft introduced a lot of new versions: Windows Server 2012 R2, System Center 2012 R2, Windows 8.1, Visual Studio 2013 and, and, and… SQL Server 2014, of course!

On a WebLogic Server, I had to write a WLST script to find the current number of consumers accessing a specific JMS Queue destination as this is not monitored by Oracle Grid Control 11g or Oracle Cloud Control 12c. Knowing only the short name of the queue, I uses a regular expression in my WLST script to find the needed information.

To find the correct queue destination I had to search the Queue Destination Name. But in the Runtime tree, the fully-qualified name of this queue resource is preceded by the parent module name, separated by an exclamation point (!) (e.g DocumentApplicationJMSSystemModule!SOAReqMSQ). So how can I find an object using the short name SOAReqMSQ?

 

Finding the object via WLST script

To achieve this task, you have to:

Step 1: Use a regular expression by adding in your WLST script:

import re

Step 2: Use the primitive operation:

re.search(pattern, string[, flags])

Step 3: Search only for the defined word and not more:

If you search for SOAReqMSQ using...

cQueueName='SOAReqMSQ'

result = re.search(cQueueName,destination.getName());

...the result will be:

DocumentApplicationJMSSystemModule!SOAReqMSQDocumentApplicationJMSSystemModule!SOAReqMSQ_ErrorQ

 To find the correct one, use the $ to match the end of the string

cQueueName='SOAReqMSQ$'

result = re.search(cQueueName,destination.getName());

This way, DocumentApplicationJMSSystemModule!SOAReqMSQ may be found.

 

Step 4: In the end, your script should look similar to this (the important information related to the search issue is underlined):

 

import re


# check queue name
cQueueName='SOAReqMSQ$'


# check only the queue for the Managed Server Name containing soa01
cServer='soa01'

connect(userConfigFile='/opt/user_projects/domains/testdomain/security/uc',userKeyFile='/opt/user_projects/domains/testdomain/security/uk',url='t3://testhost:8101')

# get server list
servers = domainRuntimeService.getServerRuntimes();

if (len(servers) > 0):
  for server in servers:
    # find if ServerName contains soa01
    result = re.search(cServer,server.getName());
    if result != None:
      jmsRuntime = server.getJMSRuntime();
      jmsServers = jmsRuntime.getJMSServers();
      jmsName = jmsRuntime.getName();
      print 'n';
      print 'Name',jmsName;
      print '----------------------------';
      for jmsServer in jmsServers:
        destinations = jmsServer.getDestinations();
        for destination in destinations:
           result = re.search(cQueueName,destination.getName());
           if result != None:
            print 'DESTINATION:',destination.getName();
            print 'JMS Server:',jmsServer.getName();
            print 'ConsumersCurrentCount:',destination.getConsumersCurrentCount()

Step 5: Here is the script output:

 

Name testdomainsoa01.jms
----------------------------
DESTINATION: DocumentApplicationJMSSystemModule!SOAReqMSQ
JMS Server: DocumentApplicationJMSServer
ConsumersCurrentCount: 0

 

This python script can be then called by a shell script that will be integrated, for instance, as a UDM script in Grid Control 11g.

There are three new geospatial features in Microsoft SQL Server 2012: CircularString, CurvePolygon, and CompoundCurve. For the fun and as an example, I will test CompoundCurve, a set of circular strings that may be combined with line strings, and store a "dbi" character set in a table.

In this post, I would like to share with you what I have learned about Oracle licensing particularities on a virtualization infrastructure (software partitioning) based on VMware ESX. Recently, I faced an Oracle licensing problem on a VMware ESX cluster on which I had to find a workaround in order to make the hardware comply with the customer's licensing model. Without entering in details, the customer had a VMware cluster with three nodes and had not enough licenses to meet the Oracle licensing requirement.

 

Remember that Oracle offers two types of licensing:

• Named user, not described in this blog
• Physical processor, which I will discuss here
  

The physical processor licensing model depends on the Oracle product.

For Standard database edition, the processor is licensed per socket: You need as many licenses as sockets used by the server running the Oracle product.

For Enterprise database edition, the processor is licensed per core. A core factor is applied according to the model, the type, and the number of cores of the processor.

The formula is:
 

number of processor x number of cores x core factor

 

For example, the core factor of an Intel or AMD processor is 0.5. The number of licenses required for an Intel or AMD quad core processor is 2 (1 x 4 x 0.5 = 2).

You can find the complete Oracle processor core factor table on http://www.oracle.com/us/corporate/contracts/processor-core-factor-table-070634.pdf.

 

But what happens if the database is running on a virtual machine, such as VMware ESX virtual machine? Maybe the licensing is based on the virtual processors allocated to the virtual machine? Unfortunately, not.

 

In the case of ESX virtualization, the licensing is always based on the physical hardware: If the database is running on a virtual machine, hosted on a multi-processor physical host, all processors of the physical host must be licensed, even if the database has only one virtual processor.

Worse, if the database is running on a virtual machine within an ESX cluster, all physical processors of this cluster must be licensed. Why? Remember that the aim of a cluster is to allow load balancing or failover of resources (here, we speak about database virtual machines) between the cluster members. So a database virtual machine can potentially run on any of the hosts in the cluster.

 

The reason of this licensing policy is that Oracle does not recognize software partitioning (virtualization) as a valid partitioning for the processor licensing. The following document explains what is partitioning, and which technology is an Oracle trusted partition: http://www.oracle.com/us/corporate/pricing/partitioning-070609.pdf

 

VMware published a white paper last year, about Oracle certification, support and licensing specific to VMware environments: http://www.vmware.com/files/pdf/solutions/oracle/Understanding_Oracle_Certification_Support_Licensing_VMware_environments.pdf

 

This white paper pointed out the DRS Host and CPU affinity rules benefits within a cluster.

 

DRS Host affinity

The VMware DRS Host affinity allows to specify one host or a group of hosts on which one or a group of virtual machine can run, in order to limit the virtual machines movement within a cluster. This can be very useful for software which requires fully licensed hosts (this is the case of Oracle). By this way, we can limit the number of physical hosts on which a software can run within the cluster, and at the same time, the number of CPU to license in the cluster.

 

CPU affinity

The VMware CPU affinity allows to specify physical processor(s) of an ESX to use with one or a group of virtual machine. It makes it possible to virtually "disable" one or many processors of an ESX Server, by hiding these processors in some virtual machines in order to reduce the number of CPU to license in the cluster.

 

What VMware says

According to VMware and its white paper, the CPU affinity is not recognized by Oracle as a valid partitioning solution. But the DRS Host affinity is neither accepted nor rejected by Oracle, since Oracle has no official position about it.

Based on this, it is absolutely possible to use the DRS Host affinity rules to limit the movement of Oracle virtual machines within the cluster, and to reduce the number of processors to license for Oracle products. As long as the customer keeps tracks of the virtual machine movements and can demonstrate that its virtual machines only run on licensed hosts, a fully licensed Oracle-dedicated ESX cluster is not necessary anymore.

 

Oracle's position

To find out Oracle's position on this topic, we asked an Oracle LMS consultant (License Management Services) if the VMware DRS Host affinity rules represent a valid partitioning solution for the Oracle physical processor licensing model. The response clearly was negative. I have not found any official document or Metalink note to confirm this response. But, based on the simple fact that VMware is soft partitioning, Oracle says that the whole ESX cluster must be licensed.

 

Conclusion

Running an Oracle database on a virtual environment always requires to license all the physical processors of the physical host. Within a cluster, all physical processors of any physical hosts member of the cluster must be licensed, even if DRS Host / CPU affinity rules are enabled.

One positive thing is that once the physical host is fully licensed, the number of virtual machines running Oracle database instances is not limited.

Recently, I had the chance to install the SQL Server Plug-in for Oracle Entreprise Management Grid Control 11g to monitor a SQL Server instance based on a windows host. Our client, who monitors these Oracle databases via OEM Grid Control 11g, wanted to use the same tool to also monitor SQL Server databases.

In this blog posting, I will describe how to install the 11g agent on a Windows host and how to import as well as use the SQL Server plug-in on Grid Control 11g.

 

Installation Agent 11g on windows host

First, we have to download the 11g agent here. Note that we have to use a 64 bit platform.

 

Unzip this file on the Windows host, for example in c:temp.

 

Create a one-time registration password on the grid for our Windows host.
Go to Grid Control 11g, open the Setup menu and Registration Passwords

 

Click on Add Registration Password

 

Enter a password, confirm it, enter a description, select the type One-time, enter the expiration date of today and click OK

We will use this password to establish a secure connection to the OMS in the additional_agent.rsp file just below.

Now go to C:Temp where we have unzipped our 11g agent and open c:Windows_X64_Grid_Control_agent_download_11_1_0_1_0windows_x64responseadditional_agent.rsp file to modify it as follow - then save and close it:

####################################################################

## copyright (c) 1999, 2010 Oracle. All rights reserved.         ##

##                                                               ##

## Specify values for the variables listed below to customize     ##

## your installation.                                             ##

##                                                               ##

## Each variable is associated with a comment. The comment       ##

## identifies the variable type.                                 ##

##                                                               ##

## Please specify the values in the following format:             ##

##                                                                ##

##         Type         Example                                   ##

##         String       "Sample Value"                           ##

##         Boolean     True or False                             ##

##        Number       1000                                     ##

##         StringList   {"String value 1","String Value 2"}       ##

##                                                               ##

## The values that are given as need to be       ##

## specified for a silent installation to be successful.         ##

##                                                               ##

##                                                               ##

## This response file is generated by Oracle Software             ##

## Packager.                                                     ##

###################################################################

 

RESPONSEFILE_VERSION=2.2.1.0.0

 

#################################################################################

#Inputs for Oracle Configuration Manager

#################################################################################

#-------------------------------------------------------------------------------

#SECURITY_UPDATES_VIA_MYORACLESUPPORT: Whether security updates are reqi-

#       uired via My Oracle Support

#DECLINE_SECURITY_UPDATES: Whether security updates should be declined.

#MYORACLESUPPORT_USERNAME & MYORACLESUPPORT_PASSWORD: User name and passw-

#       ord for My Oracle Support access, these will be effected only when SECU-

#       RITY_UPDATES_VIA_MYORACLESUPPORT=true

#COLLECTOR_SUPPORTHUB_URL: If there is not direct connection and Support

#       hub is deployed. Need to specify that URL.

#-------------------------------------------------------------------------------

SECURITY_UPDATES_VIA_MYORACLESUPPORT=FALSE

DECLINE_SECURITY_UPDATES=TRUE

MYORACLESUPPORT_USERNAME=

MYORACLESUPPORT_PASSWORD=

COLLECTOR_SUPPORTHUB_URL=

 

################################################################################

#PARAMETERS FOR SOFTWARE UPDATES

################################################################################

#-------------------------------------------------------------------------------

#INSTALL_UPDATES_SELECTION:

# Option 1. If you want to skip the software updates, provide

#     INSTALL_UPDATES_SELECTION="skip"

# Option 2. If you have already downloaded the updates then provide

#     INSTALL_UPDATES_SELECTION="staged"

# If you choose the Option 2 then make sure you also provide STAGE_LOCATION.

#STAGE_LOCATION: Stage location for software updates. It will be effected

#       only when INSTALL_UPDATES_SELECTION is set to "staged"

#-------------------------------------------------------------------------------

INSTALL_UPDATES_SELECTION="skip"

STAGE_LOCATION=

 

################################################################################

#PROXY DETAILS FOR SECURITY UPDATES/ SOFTWARE UPDATES

################################################################################

#-------------------------------------------------------------------------------

#PROXY_USER: User name for proxy access.

#PROXY_PWD: Password for proxy access.

#PROXY_HOST: Server providing proxy access.

#PROXY_PORT: Port for proxy access.

#------------------------------------------------------------------------------

PROXY_USER=

PROXY_PWD=

PROXY_HOST=

PROXY_PORT=

 

################################################################################

#Various inputs required for Installation and Configuration

################################################################################

#-------------------------------------------------------------------------------

#ORACLE_AGENT_HOME_LOCATION: The location of oracle agent home.

#Agent home "agent11g" would be created under the directory specified by

#ORACLE_AGENT_HOME_LOCATION.

#-------------------------------------------------------------------------------

ORACLE_AGENT_HOME_LOCATION=c:oracleagent11g

 

#-------------------------------------------------------------------------------

#DEINSTALL_LIST: List of components to be deinstalled during a

#     deinstall session.The following choices are available. The value should

#     contain only one of these choices.The choices are of the form

#     Internal Name, Version : External name. Please use the internal name and

#     version while specifying the value.

#   oracle.sysman.top.agent, 10.2.0.0.0 : Oracle Management Agent 10.2.0.0.0

#   oracle.sysman.top.em_seed, 10.2.0.0.0 : Oracle Enterprise Manager Repository Database 10.2.0.0.0

#   oracle.sysman.top.oms, 10.2.0.0.0 : Oracle Enterprise Manager Grid Console 10.2.0.0.0

#-------------------------------------------------------------------------------

DEINSTALL_LIST={"oracle.sysman.top.agent","11.1.0.1.0"}

 

#-------------------------------------------------------------------------------

#b_silentInstall: The user should not change this value

#CLUSTER_NODES: This variable represents the cluster node names

#       selected by the user for installation.

#-------------------------------------------------------------------------------

b_silentInstall=true

#CLUSTER_NODES=

 

#-------------------------------------------------------------------------------

#TOPLEVEL_COMPONENT: The top level component to be installed in the

#     current session.Value should contain only one of the following available

#     choices.The choices are of the form Internal Name, Version : External name.

#     Please use the internal name and version while specifying the value.

#   oracle.sysman.top.agent, 10.3.0.0.0 : Oracle Management Agent 10.3.0.0.0

#   Example: TOPLEVEL_COMPONENT = {"oracle.sysman.top.agent","10.3.0.0.0"}

#-------------------------------------------------------------------------------

TOPLEVEL_COMPONENT={"oracle.sysman.top.agent","11.1.0.1.0"}

 

#-------------------------------------------------------------------------------

#SELECTED_LANGUAGES: Languages in which the components will be installed.

#     The following choices are available. The value should contain only one of

#     these choices.The choices are of the form Internal Name : External name.

#     Please use the internal name while specifying the value.

#   en,   : English

#   de,   : German

#   es,   : Latin American Spanish

#   es_MX,   : Mexican Spanish

#   es_ES,   : Spanish

#   fr,   : French

#   fr_CA,   : Canadian French

#   it,   : Italian

#   iw,   : Hebrew

#   ja,   : Japanese

#   ko,   : Korean

#   pt_BR,   : Brazilian Portuguese

#   zh_CN,   : Simplified Chinese

#   zh_TW,   : Traditional Chinese

#Example: SELECTED_LANGUAGES = {"en"}

#-------------------------------------------------------------------------------

SELECTED_LANGUAGES={"en"}

 

#-------------------------------------------------------------------------------

#COMPONENT_LANGUAGES: Languages in which the components will be installed.

#     The following choices are available. The value should contain only one of

#     these choices.The choices are of the form Internal Name : External name.

#     Please use the internal name while specifying the value.

#   en,   : English

#   de,   : German

#   es,   : Latin American Spanish

#   es_MX,   : Mexican Spanish

#   es_ES,   : Spanish

#   fr,   : French

#   fr_CA,   : Canadian French

#   it,   : Italian

#   iw,   : Hebrew

#   ja,   : Japanese

#   ko,   : Korean

#   pt_BR,   : Brazilian Portuguese

#   zh_CN,   : Simplified Chinese

#   zh_TW,   : Traditional Chinese

#Example: COMPONENT_LANGUAGES = {"en"}

#-------------------------------------------------------------------------------

COMPONENT_LANGUAGES={"en"}

 

#-------------------------------------------------------------------------------

#OMS_HOST: OMS host info required to connect to OMS

#OMS_PORT: OMS port info required to connect to OMS

#AGENT_REGISTRATION_PASSWORD: Agent Registration Password needed to

#     establish a secure connection to the OMS.

#-------------------------------------------------------------------------------

OMS_HOST=vmtestoraem1.it.dbi-services.com

OMS_PORT=7799

AGENT_REGISTRATION_PASSWORD=manager1

 

#-------------------------------------------------------------------------------

#s_agentSrvcName: Sets the agent Service Name and this variable can be

#     used to overrite the agent service name calculated by the install. This is

#     required for only Windows.

#     Example:

#     s_agentSrvcName   = "Oracleagent11gAgent"     ; default value

#     s_agentSrvcName = "GridAgent"                 ; User specified value

#-------------------------------------------------------------------------------

#s_agentSrvcName=""

 

####################################################################################

#Please Don't change the values of these variables

####################################################################################

#-------------------------------------------------------------------------------

#FROM_LOCATION: Complete path to the products.xml.

#b_upgrade: "whether it is Upgrade or not"

#EM_INSTALL_TYPE: install type

#-------------------------------------------------------------------------------

FROM_LOCATION="C:/Temp/Windows_X64_Grid_Control_agent_download_11_1_0_1_0/windows_x64/agent/stage/products.xml"

b_upgrade=false

EM_INSTALL_TYPE="AGENT"

 

Now, open a windows command prompt as administrator and go to C:TempWindows_X64_Grid_Control_agent_download_11_1_0_1_0windows_x64ouiDisk1 and execute “setup.exe -silent -responseFile C:TempWindows_X64_Grid_Control_agent_download_11_1_0_1_0windows_x64responseadditional_agent.rsp”

 

The installation is processing…

 

When the screen disappears, the installation is finished. To see if the installation completes successfully, go to c:oracleagent11gagent11gbin and execute the command “emctl status agent”.

If you want stop or restart the agent, you can use this command: “emctl stop agent” or “emctl start agent”.

 

The installation is done, the agent is running!

 

Installation of SQL Server plug-in on Grid control 11g

First, download the plug-in by clicking here.

Go to Grid Control 11g and open the Setup menu:

 

Go to Management Plug-ins

 

Click the Import button

 

And select the file we have downloaded before and click Open:

 

Click the button “List Archive”:

 

Select the Microsoft_sqlserver_database plug-in and click OK:

 

The plug-in is ready to be deployed:

 

Before deployment go to Preferences menu, preferred credentials:

 

Click on the “Set credentials” icon of the agent:

 

For our new host, enter the host username and his password and click apply:

 

The credentials have been applied:

 

Now go back to Setup, Management Plug-ins, select the Microsoft_sqlserver_database plug-ins and click on the Deploy button:

 

Click on Add agents, and do a search for Agent on the new screen. Select the new SQL Server Host and click on the button Select:

 

Click Next:

 

Click Finish:

 

Deployment complete!

 

Add an instance to an SQL Server host

Now, we will add an SQL Server instance to our new SQL Server host.

Click on Setup:

 

Click on Agents

 

Click on the new SQL Server host:

 

On the dropdown list, select “Microsoft SQL Server” and click Go:

 

Now, you have to enter:

• JDBC URL: jdbc:sqlserver://vmtestsqlwin03.it.dbi-services.com:1433 (take care to fix the port for named instance)
• JDBC Driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
• Database username(for instance connection): sa or Admistrator or login with sysadmin right
• Password: username password
• Connect Using Windows Integrated Authentication (Yes/No): no
• Role: sysadmin

 

Click on the button “Test connection”:

 

Click on the button OK:

 

A new instance has been added to the SQL Server host:

 

Conclusion

We are now able to monitor the Microsoft SQL Server instance and the databases.

Enjoy!

Last February, I performed an operating system rolling upgrade on a four-nodes RAC cluster (11.2.0.2). I then faced a strange problem when restarting the operating system...

The first step of the procedure was to stop all Grid Infrastructure and Database services running on the first node as well as to disable Cluster and ASM autostart. The following command is supposed to prevent Oracle High Availability Service (OHAS) to be run at operating system startup:
 

# crsctl disable crs

 
Then, I powered off the server and asked the storage administrator to disable all LUNs attached to this server, including the one containing Oracle binaries (/u00).

This step was necessary because the storage bay requires specific drivers which are not shipped with the Linux installation media. Without drivers, all mountpoints attached to LUNs and detected during the upgrade process would have displayed errors.

However, when starting the server again to check that all mountpoints were disabled, I saw that the startup procedure was blocked to the OHASD service, preventing the server to finish the startup.

In fact, even if crs autostart is disabled, a deamon called "ohasd" is still running at server startup. Among other things, it checks and indefinitively waits for the presence of CRS binaries. No luck, the LUN attached to the mountpoint containg CRS binaries was disabled...

We can see this check in /etc/init.d/ohasd file:

 

# Wait until it is safe to start CRS daemons
  while [ ! -r $CRSCTL ]
  do
    $LOGMSG "Waiting for filesystem containing $CRSCTL."
    $SLEEP $DEP_CHECK_WAIT
  done

Where $CRSCTL corresponds to /u00/app/11.2.0/grid/bin/crsctl

 

What is crazy is that the loop is performed no matter if autostart is enabled or not. Just after the loop is done, the script checks if autostart is enabled - thanks to the file /etc/oracle/scls_scr/$MY_HOST/root/ohasdstr, which contains "enable" or "disable" depending of the autostart configuration.

Why do not check if autostart is enabled before looking for CRS binaries? A question that Oracle does not seem to have answered in 11.2.0.3, because we can see that, even if the ohasd startup mecanism was updated, ohasd is still waiting for CRS binaries:

 

# Wait until it is safe to start CRS daemons.
  # Wait for 10 minutes for filesystem to mount
  # Print message to syslog and console
  works=true
  for minutes in 10 9 8 7 6 5 4 3 2 1
  do
    if [ ! -r $CRSCTL ]
    then
      works=false
      log_console "Waiting $minutes minutes for filesystem containing $CRSCTL."
      $SLEEP $DEP_CHECK_WAIT
    else
      works=true
      break
    fi
  done

 

As you can see, in 11.2.0.3, the server will now finish to start, but will be waiting 10 minutes. A message is displayed in the log startup:

 

Apr 30 15:31:29 rac1 logger: Waiting 10 minutes for filesystem containing /u00/app/11.2.0/grid/bin/crsctl.
Apr 30 15:32:29 rac1 logger: Waiting 9 minutes for filesystem containing /u00/app/11.2.0/grid/bin/crsctl.
Apr 30 15:33:29 rac1 logger: Waiting 8 minutes for filesystem containing /u00/app/11.2.0/grid/bin/crsctl.
Apr 30 15:34:29 rac1 logger: Waiting 7 minutes for filesystem containing /u00/app/11.2.0/grid/bin/crsctl.
[...]

 

At this time, the workaround I found to allow the server to start immediatly is to prevent "ohasd" service to run by renaming or moving the /etc/init.d/ohasd file, or to comment the loop section in order to skip the infinite loop.

Hopefully, SSH deamon (if enabled) runs before OHAS deamon. It is possible, if the startup procedure is blocked, to access the machine through an SSH session in order to apply this workaround and to restart the server.

I finally added this pre-requisite to the upgrade procedure for the remaining nodes of the cluster.

OpenERP release 7.0, which just came out, shows some real advantages in terms of accessibility. By refining the website, OpenERP wants to prove that the new release is not merely an update but a new product with a new mind-set.

 

The new features

Indeed, the ERP’s web interface is now purified and simplified to match the fashions. Today’s user interfaces are intended to be as simple and as designed as possible. The new web interface displays only the needed information, it loads fast, it navigates fast and it only encompasses the modules that you have chosen. If you want a new functionality, you just have to install the right module by pressing one button.

OpenERP was built as a container and is extensible thanks to the modules. It now states this desire by providing a real Apps container. Modules are now considered as applications that you can plug to your ERP to extend its behaviour and functionalities. Some new applications are emerging such as Social Network, which facilitates the internal communications by providing a messaging tool for chatting and allowing user authentication thanks to Google and Facebook accounts.

But unlike these questionable utility applications, some of them can become business critical such as the redesigned Point of Sale module that provides a new useful interface. A lot of partner projects are emerging around this feature; I guess this is the most wanted module for this release.

 

It is meant to facilitate and speed up business processes. In the same registry, the contract management has been enhanced to facilitate usage and to improve functionalities and possibilities:

• Manage the validity of the contract (duration, maximum number of hour/tickets, terms and conditions)
• Handle the invoicing spread in time (fixed price, on time/materials basis, invoice by phases)
• Cover the contract’s price (price by hour by user, conditions according to quantities)
• Forecast of invoices, define budgets and analyse costs and revenues
• Link contracts to analytic accounts (costs and revenues) and projects (manage related tasks, timesheets or issues)
• Follow up (once a week, alerts for contracts renewals are sent automatically to a salesperson with the indication of what to do for each contract)

Some other interesting applications are explained in the release note available on the website.

 

Migrating to the new release

As told before, the new release is not just a minor update - a lot of things have changed: from the user interface point of view and from developers point of view.

First, the traditional web server is merged to the main server; you no longer have two servers to start - and that’s good news.

Secondly, the internal model is changing. For example partners are now considered as contacts. And users have a contact profile created automatically. In addition, internal objects may change (such as osv.osv becoming osv.Model) and behaviour too (such as the need to store a calculated field if you want it to be shown in a graph).

So the migration of a module from version 6.x to 7.0 might be difficult; as such OpenERP proposes to handle your migration for fees, but note that an audit is made to ensure the migration is possible.

The support on version 6.x is coming to the end, obliging companies to migrate. OpenERP wants to show that the new release is a new departure and we are excited to work with this kind of software. But I hope new major updates will not come too fast since we can’t rebuild modules every month!

This way, OpenERP shows that they want to provide a stable and professional open source ERP to make it accessible for companies with a small infrastructure and to provide an alternative to huge, abstract and costly ERPs.

In this bog posting, I will present a boot sequence that works for all Linux-based operating systems - from switching on the power to the login screen.

 

7 steps

There are 7 steps for Linux startup:

 

Power on

Obviously, first of all, you have to switch on your computer, then:

• Each sub process will execute the following one
• It is the same process for all Linux distributions
• Close to BSD and Unix style from which it derives
• Same as Microsoft's procedure until MBR stage

BIOS (Basic Inpout Output System)

• First firmware executed at startup
• Inits motherboard hardware components
• Performs some system integrity checks (i. e. memory)
• Abstraction layer between hardware and software
• Searches and executes the boot loader program from MBR disk

MBR (Master Boot Record), bootlader stage 1

• Located on the first sector of booted disk (512 bytes)
• Contains location and data about 2nd bootloader stage, inside bootloader code section

• Partition table (MS DOS limited to 4 primary partitions)
• Signature helps BIOS to identify from which disk it executes the bootloader code

Bootloader, stage 2

• Located on a disk partition
• Loads operating system kernel and options

 You can choose several bootloader software:

• Grub, GRand Universal Bootloader
• Lilo, LInux LOader
• ...

Grub configuration (< v1.0) :

Grub version < v1.0 uses a singe configuration file, in which all operating systems and kernel options have to be written by hand.

[root@oel-test ~]# cat /boot/grub/menu.lst

default=0

timeout=5

splashimage=(hd0,0)/grub/splash.xpm.gz

hiddenmenu

title Oracle Linux Server-uek (2.6.39-200.24.1.el6uek.i686)

root (hd0,0)

kernel /vmlinuz-2.6.39-200.24.1.el6uek.i686

...

...

Grub 2 configuration (> v1.0) :

Grub version > v1.0 (Grub 2) allows to use automatic search and configuration mechanism that updates the menu.lst file:

[root@oel-test ~]# grub-mkconfig

[root@oel-test ~]# upgrade-grub2

Moreover, if needed, we can add customization during these processes:

[root@oel-test ~]# vim /etc/default/grub # common configuration file for all Operating systems

[root@oel-test ~]# ls /etc/grub.d/* # search and configuration scripts location

[root@oel-test ~]# upgrade-grub2

#> upgrade-grub2
will overvrite /boot/grub/menu.lst

 

Kernel

• Located on a disk partition
• Contains drivers for hardware support
• Lowest Operating System software layer
• Enables multi-task support (scheduler)
• Mounts root file system
• Executes init program

• Usually, an “initrd”, INITial RamDisk (filesystem mounted temporarely in RAM memory), is needed

Init process

The Init process comes first in the operating system startup and defines the running state (Runlevel):

0 – Halt
1 – Single user mode
2 – Multiuser, without NFS
3 – Full multiuser mode
4 – Unused
5 – Full multiuser and graphical mode
6 – Reboot

Default configuration file location:

[root@oel-test ~]# cat /etc/inittab

id:5:initdefault:

Runlevel service script organization:

Runlevel scripts are organized acoording to a strict naming convention, allowing their execution order and purpose:

It is possible to invoke a manual action related to a service:

[root@oel-test ~]# /etc/init.d/service_name start|stop|restart|status|reload

Starting service : [ OK ]

[root@oel-test ~]# service service_name start|stop|restart|status|reload

Runlevel service script configuration

It is possible to manage service execution scripts using more high-level commands:

chkconfig [--level levels ] service_nqme on|off|reset

[root@oel-test ~]# chkconfig --level 2345 ntpd on

[root@oel-test ~]# chkconfig --list ntpd

ntpd 0:off 1:off 2:on 3:on 4:on 5:on 6:off

It is also possible to use a graphical tool:

[root@oel-test ~]# ntsysv --level 5

That's it!

Vor einigen Wochen hatte ich im Rahmen der Frankfurter Datenbanktage Gelegenheit zu einem Interview mit Andrea Held, der fachlichen Leiterin des Events und Geschäftsführerin der Firma Held Informatik.

Das Ergebnis des Gesprächs ist dieses Video, in dem Sie mehr über dbi services, das schnelle Wachstum unserer jungen Firma und unser Dienstleistungsportfolio erfahren. Gucken Sie doch mal rein, falls Sie wissen wollen, wie dbi services "tickt" .

Nochmals vielen Dank an Andrea Held für das interessante und anregende Gespräch! Und natürlich auch für die super organisierten Frankfurter Datenbanktage, an denen dbi services auch 2014 wieder als Partner auftreten wird.

In January, I promised to show you the next step of a procedure to manage templates with Virtual Box. Don't worry, I have not forgotten but I was very busy! This step, the final one, consists on customizing of a virtual machine configuration at the start. VMware does it with Templates on ESX, using for example Sysprep for MS Windows guests. I am going to show you how to do it with one simple text file.

 

Sorry for Windows adepts, my example is based on an Oracle Linux 6. But the scenario is the same for both environments, there are just different commands and syntaxes on Windows.

 

Last time, we looked at the startup step. You have one or more cloned virtual machines, all identical: same hostname, same IP, etc. The purpose here is to customize each one in order to have machines based on a single template, but with proper network configuration, without causing for instance any Oracle issues.

 

When you use the script to clone the template virtual machine, a parameter file (conf.ini) is generated in a dedicated shared folder for each new virtual machine, containing a new hostname, IP and netmask, and an init flag. This file will allow the virtual machine to be reset at its first startup. The init flag will prevent the script to be run at each startup:

• 0: script is not executed
• 1: script is executed.

 

Let's see how it works. Here is an example of the parameter file:

 

vmtest01:192.168.1.101:255.255.255.0:1

 

You have to create a new script to read information on this file. The script will:

 

• Create a mountpoint to the shared folder created at previous steps

 

mount -t vboxsf vmform /mnt/vmform

 

• Parse the file conf.ini in the mount point

 

CONF=`cat /mnt/vmform/conf.ini`
NEW_HOSTNAME=`echo $CONF | cut -d":" -f1`
NEW_IP=`echo $CONF | cut -d":" -f2`
NEW_MASK=`echo $CONF | cut -d":" -f3`
FLAG=`echo $CONF | cut -d":" -f4`

 

• Change the hostname by editing /etc/sysconfig/network file

 

cat /etc/sysconfig/network | sed -e "s/$HOSTNAME/$NEW_HOSTNAME/I"

 

• Update the /etc/hosts file

 

cat /etc/hosts | sed -e "s/$HOSTNAME/$NEW_HOSTNAME/I"

 

• Update Oracle configuration (tnsnames.ora and listener.ora files)

 

cat $TNS_ADMIN/listener.ora | sed -e "s/$HOSTNAME/$NEW_HOSTNAME/I"
cat $TNS_ADMIN/tnsnames.ora | sed -e "s/$HOSTNAME/$NEW_HOSTNAME/I"

 

• Change the current IP and Netmask

 

IP=`ifconfig eth0 | awk '/^ *inet / {print substr($2, 6)}'`
MASK=`ifconfig eth0 | awk '/^ *inet / {print substr($3, 6)}'`
cat /etc/sysconfig/network-scripts/ifcfg-eth0 | sed -e "s/$IP/$NEW_IP/g"
cat /etc/sysconfig/network-scripts/ifcfg-eth0 | sed -e "s/$MASK/$NEW_MASK/g"

 

• Delete the /etc/udev/rules.d/70-persistent-net.rules file, in order to reinitialize the network interface allias

 

rm -f /etc/udev/rules.d/70-persistent-net.rules

 

Remark: To avoid any problem, delete the HWADDR parameter from scripts of any network interfaces (/etc/sysconfig/network-scripts/ifcfg-ethX).

 

• Reset the init flag to a null value, to avoid the script to be run at each startup

 

cat /mnt/vmform/conf.ini | sed -e "s/1$/0/"

 

• Restart the virtual machine

 

 reboot

 

Of course, I only give you the main commands here. You have to adapt the script to check errors, make copies before editing files, create the temporary files required by the said command, etc.

 

The script can be copied after creating cloned virtual machines, but it can be fastidious if you have to create a lot of VMs. My advice is to copy it on the template before the clonage. This way, the machines will automatically be customized at the first startup and restarted when script is finished.

 

It will be completely automatic once your template is ready. When I created a new lab for our dbi InSite workshops, I clicked on the main script (duplicate_script.cmd) and was able to go drink a coffee afterwards - a big coffee because the process is not that fast :-) When I came back, all of my virtual machines were running and completely ready for work. I was able to delete the virtual machines after the training sessions and recreate them very, very easily!

 

Contact me if you need help or if you have any remarks on my tool.

My last blog was about installing and configuring Microsoft SQL Server 2012 Distributed Replay. This time, I will show you how you can create a trace and replay it with Distributed Replay Clients on a specific SQL Server target.

This is my Distributed Replay configuration:

 

Creation of the trace

I will use the two Distributed Replay clients to generate a workload against the target server with two PowerShell Windows which will execute diverse SQL scripts. To capture the trace, I will use the SQL Server profiler tool which is known in SQL Server to create, watch, store, and replay a trace.

Go to Microsoft SQL Server Management Studio, Tools and Open SQL Server Profiler:

 

Connect to the target and a screen for trace properties opens:

 

Add a name to your trace, save it to a file, change the property "Maximum file size" to avoid the split of your trace file. You should also enable a stop time for your trace which will save the workload during 20 minutes.

When the trace is done, you have to copy it on your Distributed Replay controller (vmtestsqlwin01) to perform the preprocess task.

 

Preprocess the trace

To preprocess the trace we have first to open a command prompt windows and start the Distributed Replay controller services:

Now that the service is started, we have the possibility to edit the preprocess configuration file to add System Sessions during our replay. This configuration file (DReplay.Exe.Preprocess.config) is an XML file which looks like this:

 

The syntax of the prepross command is:

 

dreplay preprocess[-m controller] -i input_trace_file     -d controller_working_dir[-c config_file][-f status_interval] 

To preprocess your trace, you should go back to your command prompt windows, execute 'cd "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn" ' and run the preprocess command:

 

This process will create two files on c:\distributedreplay which will be necessary to perform the replay. Those two files are ReplayEvents.irf and TraceStats.xml.

You can see the complete preprocess below:

 

Replay the trace

Now that the trace is in preprocess, your Distributed Replay environment is ready to replay the trace on the target SQL Server vmtestsqlwin03.

First, you have to start the Distributed Replay client services on each client(vmtestsqlwin01 and vmtestsqlwin02) via the net start command on a command prompt window:

 

NET START "SQL Server Distributed Replay Client"

 

When, done you can go to the log file to see if both clients are connected to the controller (I explained how to do that in my first blog on this topic: SQL Server 2012: Installing and configuring Distributed Replay)

Before running the replay, you have the possibility to edit the replay configuration file to change parameters. This configuration file (DReplay.Exe.Replay.config) is an XML file which looks as follow:

 

The syntax of the replay command is:

 

dreplay replay[-m controller] -d controller_working_dir [-o]     [-s target_server] -w clients [-c config_file]     [-f status_interval]

• c is mandatory is you change the Replay configuration file or if you store it in a different place as the normal one
• o is to capture  the clients' replay activity and saves it to a result trace file in the path specified by the "resultdirectory" element in the client configuration file
• f is to specifie the frequency (in seconds) at which to display the status, by default each thirty secondes

 

Go to "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn" and execute the following replay command:

 

DReplay replay -s “vmtestsqlwin03" -d "c:\distributedreplay" -w “vmtestsqlwin01,vmtestsqlwin02“ -o -f 15

This command will execute a replay of the trace from the working directory c:\distributedreplay where the preprocessed files are stored to the target SQL Server vmtestsqlwin03 via the two clients vmtestsqlwin01 and vmtestsqlwin02.

An output file will be created on each client in the Result Directory and the screen will be updated each fifteen secondes during the replay:

 

During the replay process, you can follow the status on the screen. When the replay is finished, you have the possibility to check the replay trace result on the ResultDir directory of each client.

 

Conclusion

Microsoft SQL Server Distributed Replay is in my opinion the best way to simulate a mission critical workload with the availability to run the trace up to sixteen clients with different modes like stress mode or synchronisation mode.

The drawback of this solution is that the implementation is more complex than with SQL Server profiler and also needs more resources (one controller, several clients and a target).

Three weeks ago, Microsoft published the third Cumulative Update (CU3) for Microsoft SQL Server 2012 Service Pack one. As usual, if this Cumulative Update solves an issue you are facing, install it quickly - otherwise, do not install it.

Which issues are covered by this CU3?

It contains 38 Hotfixes:

• 27 for the Engine
• 8 for SSAS
• 3 for SSRS

To have more details or information about these hotfixes, click here.

 

Historic of SQL Server 2012

The build number of this Third Cumulative Update is 11.00.3349.

Let's resume the different versions of SQL Server 2012 we have for the moment:

 

Date SQL Server 2012 version Build

November 2010

Community Technology Preview 1 (CTP1)

11.00.1103

July 2011

Community Technology Preview 3 (CTP3)

11.00.1440 

November 2011

Release Candidate 0 (RC0)

11.00.1750 

December 2011

Release Candidate 1 (RC1)

11.00.1913 

March 2012

RTM

11.00.2100

April 2012

Cumulative Update 1 (CU1)

11.00.2316

June 2012

Cumulative Update 2 (CU2)

11.00.2325

August 2012

Cumulative Update 3 (CU3)

11.00.2332

October 2012

Microsoft Security Bulletin MS12-070

11.00.2376

October 2012

Cumulative Update 4 (CU4)

11.00.2383

November 2012

Service Pack 1 (SP1)

11.00.3000

November 2012

Cumulative Update 1 (CU1)

11.00.3321

January 2012

First Hotfix for SP1

11.00.3335

January 2013

Cumulative Update 2 (CU2)

11.00.3339

March 2013

Cumulative update 3 (CU3)

11.00.3349

 

On the same date, a Cumulative Update has been also published by Microsoft for SQL Server 2008 SP3. It is the 10th (CU10). You can have a look at it here.

A few weeks ago, I posted an article about Oracle Database Appliance, focusing on ODA version 2.5.0.0.0. However, a new update of ODA, version 2.5.5.0.0, was released by Oracle on 5 March. This posting will focus on the new ODA release, which leads to a new hardware architecture.

 

So, what's new with this new ODA version? According to the Release Notes, ODA now supports the new X3-2 architecture which allows additional hardware and storage capabilities.

 

Physical interconnection

Remember, the previous ODA version was shipped in the form of two 2U nodes. Each one was hosting multiple storage devices, and the nodes were already linked to each other. The front was composed of disks and the host was accessible from the rear. The cluster interconnect was included inside the rack. Users just had to plug in power as well as network and start the rack.

With the X3-2 architecture, ODA still comes in a 4U form, but each node is now independant and 1U sized. You can access the host by the front side, and each one is attached to a 2U storage shelf. This means that the user has to plug in network and power, but the interconnect and the storage also has to be configured by the user. The connection becomes a little bit more difficult!

 

Evolution perspective

When I reviewed ODA last week, I pointed out a major drawback: the hardware limitation. The new architecture now is evolutive: the storage shelf shipped with ODA can be upgraded with a second 2U storage shelf to get more storage capabilities. Other hardware components are still limited, but have been strongly upgraded with X3-2.

 

Hardware updates

The hardware is clearly more powerful. Here are the new hardware specifications of ODA X3-2 for each node - in comparison to the old configuration:

Item Old configuration New configuration CPU 2 x X5675 2 x E5-2690 Cores per CPU 6 8 Memory 96 GB 256 GB SSD Disks for REDO Logs 4 x 73 GB 4 x 200 GB SAS Disks for data files 20 x 600 GB 15k rpm 20 x 900 GB 10k rpm SATA disks for operating system 2 x 500 GB 2 x 600 GB Cluster interconnect 2 x 1 GbE fibre 2 x 10 GbE copper

 

We can see that Oracle has put more powerful components on its ODA: more CPU, more Memory, and more disk space to host more databases.

 

To conclude

ODA virtualization support is still limited to the local storage of the host (2x600GB SATA disks) - no update on this point. This ODA version does not provide new features. In fact, this new release of ODA is "just a boost" of the old ODA architecture, with twice more CPU power and four times more storage capabilities.

Distributed Replay is a new fonctionality of Microsoft SQL Server 2012. It helps you assess the impact of future upgrades (SQL Server, hardware, OS) or SQL Server tunings by replaying a trace captured from a productive SQL Server environment to a new SQL Server test environment.

This new functionality is similar to SQL Server Profiler, but with more possibilities: e. g. replaying the trace from multiple clients (up to sixteen), use a stress or  synchronization mode, configure options like think time, or connect time etc.

 

Distributed Replay installation and configurations

To use Distributed Replay, we have to install a Distributed Replay Controller and 1 to 16 Distributed Replay Clients. In this exemple, I will use a Distributed Replay controller and a Distributed Client on server VMTESTSQLWIN01, a second Distributed Replay Client on server VMTESTSQLWIN02 and a target instance with SQL Server 2008 R2 on WMTESTSQLWIN03.

Do not fortget to also install Management Tools which provides Distributed Replay administration tools!

First, we have to create two Active Directory accounts.

1) One for the Distributed Replay Controller: MSTESTDR_controller:

2) And another for the Distributed Replay Client: MSTESTDR_client:

 

Distributed Replay installation

We now have to install Distributed Replay Controller, Distributed Replay Client, and Management Tools on our VMTESTSQLWIN01 server which will be the Controller of our Distributed Replay and also the Client:

On the Server Configuration, add the two Actice Directory accounts created for Distributed Replay Controller and Distributed Replay Client:

On the Distributed Replay Controller Page, add the Active Directory account previously created for the Controller. On the Distributed Replay Client page, add the Controller name:

Continue the installation until the Complete Screen appears:

Now, our VMTESTSQLWIN01 has Distributed Replay installed.

We have to install the second Distributed Replay Client on VMTESTSQLWIN02:

On the Server Configuration, add the Actice Directory account created for Distributed Replay Client:

Configure the Distributed Replay Controller for the client:

Finish the installation:

 

Windows Firewall configurations

To allow Distributed Replay Clients to connect and register to the Distributed Replay controller, inbound connection for DReplayClient.exe application has to be allowed on each Distributed Replay Client through the Windows firewall.

Go to Windows firewall, Inbound Rules, and add a new rule for a program:

Browse to the DReplayClient.exe location:

Allow the connection:

Give a name to this new rule and click Finish:

Now, configure the Windows firewall on the Distributed Replay Controller to allow inbound connection for DReplayController.exe application using the same way.

In the target Server VMTESTSQLWIN03, we also have to configure the Windows firewall for inbound on port TCP 1433 to let the Distributed Replay Client write on the target. Select the port:

Protocol TCP and port number 1433:

Allow the connection:

Apply this rule for Domain, Private, and Public:

Specify a name for this new inbound and click Finish to save it:

Now the firewall is configured on each server!

 

Start Services and check client registrations

You first have to start the Controller service on the Distributed Replay Controller.
Go to VMTESTSQLWIN01, open a command prompt screen and enter:

 

NET STOP "SQL Server Distributed Replay Controller"
NET START "SQL Server Distributed Replay Controller"

 

You should see the result of this command in the log folder of Distributed Replay Controller under C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayController\log.

You have now to start Distributed Replay Client service on your Distributed Replay Clients VMTESTSQLWIN01 and VMTESTSQLWIN02. Verify that they are correctly synchronised with your Distributed Replay Controller.

On both servers, open a command prompt screen and run:

 

NET STOP "SQL Server Distributed Replay Client"
NET START "SQL Server Distributed Replay Client"

 

To see if the Client is successfully registred with the controller, open the last log file in the location C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient\log and check the last line of the file. This could be something like that:

If this was successul for the two clients, the configuration between the controller and clients is finished and the Distributed Replay is ready to work.

If you have a log file like this...

...it means the registration failed, so:

• check if the controller name is right in the DReplayClient.config file
• check if, for each client, there is an inbound rule to allow connections for the DReplayClient.exe application
• check if, for the controller, there is an inbound rule to allow connections for the DReplayController.exe application

  Conclusion

At this point, our Distributed Replay application is ready to use, clients and controller are registred together.

You now have to create a trace, preprocess it and replay it from our clients to the target SQL Server.

This will be the content of my next blog posting.

Last february, I had the chance to attend an Oracle presentation about ODA: Oracle Database Appliance. I found it interessant to share what I have learned about this technology. Which, I confess, convinced me.

 

Summary

Before developing the software and hardware components, here is a small overview of ODA.

ODA is an all in one solution providing Hardware, Operating System, and database components. It comes in a 4U rack server form, composed of two distinct nodes linked each other by the rear of the rack.

Oracle called it “Database Appliance”, but I remember the first thing I said to myself was: Why not call this technology Oracle Cluster Appliance? Indeed, ODA is a high availability database solution and provides two nodes, with Automatic Storage Management (ASM) and Grid Infrastructure, to implement Oracle Real Application Cluster (RAC) or RAC One Node with Enterprise Edition databases.

 

ODA offers the complete stack for databases hosting:

 

 

The solution is shipped with Operating System and an Appliance Manager pre-installed. The ASM storage is also pre-configured. So, the user just has to plug in power and network, and to configure the system through a wizard (install clusterware, create databases, etc.).

Another positive point is the ability to enable or disable CPU cores from ODA, thus to limit the licensing impact. It is the only Oracle offer allowing to acquire processor licensing in relation to the need, just by limiting processor activation in the BIOS. Even if CPU cores are disabled, all memory and storage remain available, without restriction.

To resume, ODA saves time and money by simplifying deployment, maintenance, and support of high-availability database solutions.

 

Hardware

ODA has the following specifications:

 

Item Per node Total CPU 2 x Intel Xeon x5675 4 Cores 12 (6 per CPU) 24 Memory 96 GB 192 GB Storage 2 x 250 GB 7.2 rpm SATA Raid 1 1 TB 2 x 73 GB SSD for Redo 292 GB 10 x 600 GB 15k rpm SAS2 for Datas 12 TB

 

No network storage system can be attached to ODA, except Sun ZFS Storage Appliance. But the rack has a high level of built in redundancy to compensate:

 

• Storage
• Network
• Power
• Cooling
• Server

 

Note that the interconnect, for the cluster, is also built in.

  

Software

ODA comes with following software:

 

• Oracle Enterprise Linux 5.8 UEK
• Appliance Manager 2.x
• Grid Infrastructure 11g R2
• Oracle Rdbms 11g R2

 

What is Appliance Manager?

Appliance Manager is responsible of Provisioning, Storage Management, Patching and Validation and Diagnostics.

 

Provisioning

The OS is deployed in factory. Devices, partitions and file systems are in place, and OS best practices are configured.

To deploy the database, just download latest version of Grid Infrastructure and RDBMS, install and configure them through Appliance Manager wizard.

All is fully tested and validated by Oracle.

 

Storage Management

The user has no storage to create or configure. ASM is already configured, and 3 diskgroups are created per default: DATA for datas, RECO for archive logs, and REDO for redo logs.

Storage errors and problems are monitored by Appliance Manager. If a disk fails, just change it and ODA makes the rest.

 

Patching

Patches all the components: firmware, OS, Appliance Manager itself, Grid Infrastructure, and Database.

To patch ODA, just download the bundle patch and select what to patch (in reality, this is a little bit more difficult because patching is run via command line...).

The patch bundle is fully tested by Oracle.

 

Validation and Diagnostics

Auto Service Request (ASR) can automatically open Service Requests.

Some command tools allow to easily see best practices violations or components failure.

 

A word about virtualization

The latest version of ODA now offers a virtualization platform. This is a good point, because it allows to use your performant environment (ODA) to do something else than database activities. For example, host an application server.

The feature is based on Oracle VM and can use CPU cores that are not licenced for database.

But the first aim of ODA is to provide database services. So, in any case, priority is given to databases. Virtualization support will not benefit of SSD disks or 12 TB of storage, which are all dedicated to database.

Templates and VM can only use the local storage of the node and there is no redundancy of the virtual machines: nodes are standalone hypervisors.

Also note that there is no GUI but only command line to manage virtual machines.

 

Advantages & drawbacks

Advantages

• One provider for all the stack
• Fully tested and compatible
• Reduced costs compared to a complete deployment
• Simplicity of deployment and administration
• Performance
• Virtualization support since last release
• Possibility to disable CPU cores to reduce licencing

 

Drawbacks

• Not expandable to larger solutions. In that case, you can have a look at the Oracle Exadata machines.
• Limited virtualization storage and performances
• Virtualization administration limited to command line

 

Conclusion

ODA is quite a performant solution and I would certainly talk about it to some of my customers. It also offers a virtualization support, which allows to share performances with other services than databases.

But I think that this type of technology is dedicated to small companies or small environments, because of its limitations:

• Memory or storage cannot be upgraded
• No network storage system can be attached
• Limited to two nodes

It is a good alternative when you do not need a big installation and want to maximize deployment and administration simplicity without sacrificing performance. Otherwise, I would highly recommend to take a look at the Oracle Exadata machines.