Compression after Refresh

[former_member206857]

Hi All,

I have the following scenario;

I am getting new hardware for my Production server.

Because of all my integration points (PI,SOLMAN(Charm-ITSM,Monitoring) I decided the best way is to do a HOMO system Export and import onto the new server using the same IP-Hostname-SID. Hopefully avoiding allot of config.

ANyways my question is SQL specific.

My Production has been upgraded in the following the last year

1- SQL 2008 R2

2- compressed

3- EHP6 (NW7.31)

4- SQL 2012

Here is my question.

Since the method I am using, I will need to re-compress again after done all my import. I will be importing directly to MSSQL2012 then compression.

Does anyone see an issue having all my other system compressed with 2008? My new server being compressed with 2012?

I do refreshes often from PRD to Sandboxes, anyone forsee some issues?

I don't think so, but worth asking.

I'm going to try the parallelism during export for fun to see how much it speeds up.

[luisdarui · Accepted solution]

Hi Joshua,

I didn't understand the part of "HOMO system Export and import" I mean homogeneous would be backup/restore or detach/attach and heterogeneous would be the import/export option, but whatever it is I'll explain in the two methods:

1) HOMOGENEOUS (backup/restore - detach/attach):

If your database is already compressed, once you restore the database it should be compressed already. No changes are necessary;

2) HETEROGENEOUS (export/import):

When installing the database instance from the exported data, database compression will be active by default (it is default as of May 2011 I believe).

In the case 1) you should after the restore run the Database Upgrade Completion as per SAP Note 683447.

Best Regards,

Luis Darui

[Former Member]

Hi Joshua,

As far as I understand the terms homogeneous and heterogeneous system copy / migration for quite some years now, the type MAINLY depends on your source and target platform. You can have a look at this:

System Copy - Technical Operations Manual for SAP NetWeaver - SAP Library

● You perform a homogeneous system copy if your target system is on the same operating system and database system as your source system.

● You perform a heterogeneous system copy if you want to change the operating system or database system.

The fact that there are two technical ways of copying/migrating a system is a different thing in my opinion. The two technical ways are:

1) database-specific (detach-attach, backup-restore, .., also depending on the RDBMS in use)

2) R3load-based migration

If your source DB platform equals the target DB platform you can use database-specific methods, e.g. backing up the DB on the source system and simply restoring it on the target system. If it doesn't you simply can't use the database-specific method as your scenario then comprises of two different database platforms which have different DB-specific methods. If you have source SQL 2008 R2 and target SQL 2012 this is homogeneous and you can use the database-specific methods listed in the system copy guide (detach-attach/backup-restore).

However, even if the system copy is homogeneous, you can STILL use the R3load-based migration method which will export all data from the source system into dump files during system export and will import all data again on the target system from these dump files. Sometimes, you might even prefer to use the R3load-based method even though your scenario is homogeneous: e.g. you want to use a different number of database files on your target DB or you want to improve data distribution over all DB files on target system, etc.

Luis is right, even if you use the R3load-based method (export-import) you will NOT have to explicitly run any kind of DB compression (MSSCOMPRESS) on the target system. If you make sure to use a recent SWPM and kernel on the source system for the export then the export files will be created in a way that the objects are created with compression directly on target side.

If you change your mind and go for the DB specific method (backup-restore or detach-attach) you don't need to compress anything on target side either - as it's already compressed in the source DB it will remain compressed after restore or attach on target side. You can even restore or reattach backed up or detached databases to a higher target DB instance release - SQL Server will take care of upgrading all internal structures during restore / attach.

To cover the remaining questions:

- NO there is no issue at all with compressing some systems with SQL2008 and some with SQL2012. Compression is transparent. SQL Server handles it for you - the application does not have to know how exactly this works.

- NO, no issues for Q- or sandbox refresh

and finally:

YES, please don't only try parallelism but simply ALWAYS use it if you perform an R3load-based export / import. I can't believe you've ever ran this with a single process in parallel ever... I rather assume you just didn't change the default parallelism of (depending on several factors) usually 3.

If you want to see how much it can speed up for fun (and also for saving a lot of time ) you might rather additionally want to:

- run the export and import in parallel (the System Copy guide explains how to do this)

- monitor the CPU usage during export on the source system and during import on the target system and adjust the number of processes on each side to make sure you seize your CPU capabilities on both sides without overloading the systems. Note that depending on what an R3load process is doing its CPU consumption might differ (e.g. inserting data into a table, creating an index on a table, reading data from a table and writing it into a dumpfile)

- use table splitting for top size tables (which can be identified in transaction DB02)

- optimize package splitting parameters to your needs

- adjust the order_by file on source system side to make sure the export of large table starts first

All of these options are quite well documented in the guides but if you've never used them it might be hard to try applying them all at once for the first time. If you decide to at least try one or two of them in your next migration I highly recommend table splitting in the first place and parallel export/import in second.

cheers,

Beate