Just completed my first system copy on SQL Server 2012 using SWPM SP09

[Former Member]

After a little fumbling around, I was able to complete my first system copy on SQL Server (I've done dozens on AIX/Oracle before but never with SWPM)

I needed to create a Sandbox (SB1) copy from our test (QAS) system (which was recently refreshed from PRD).

Also, our test environment is Distributed MCOD. Appservers on one virtual machine and DB server on another virtual machine. So the database target was on the the same DB Instance as the source system.

So my question is about LOGICAL FILE NAMES for sql server database files. After detaching the the SOURCE system database and copying the files to our SB1 file location, we decided to leave the SOURCE system down while we ran the SWPM for the 1) ASCS,  2) Database Instance, and  3) Central Instance. After completing all three steps, the system came up and everything looked great. I could login and begin post processing.

But when we looked "under the covers" in the SQL Server Studio, we right-clicked properties on the new target (SB1) database, we noticed that the Logical File Names still had the same names as the the original source database. We changed them manually. But I was wondering why the SWPM did not change them. In note SAP 151603 it mentioned changing logical file names as a manual step, but I was under the impression that the SWPM would do that for me.

Any feedback is greatly appreciated.

Thank you

Stephen Ferracioli

SAP Basis Consultant

[luisdarui · Accepted solution]

Hi Stephen,

When you copy a SQL Server database, in the target system, even if you're going to perform a system copy with target installation, you need to adjust all the database before you perform the installation with SWPM.

I have a system named E83. I copied the database to another host, installed SQL Server, and restored the database (backup/restore). The database remained with the same name, data files and log file name, and with the same target directory.

Well, I started SWPM to perform the target installation. I choose the SID of 'E84'.

At the end of my system copy, my SID is E84, but my database still E83. The data files are still E83* named. The logical file name still E83* named.

The system copy guide does not mention anything about this. It assumes that your database is already prepared to be installed in the target system:

The system copy guide direct you to SAP Note 151603. The note itself doesn't provide the commands you need to rename the database, the logical name or the file names. It simply suggest you that you

"You may rename the files if required." when attaching or when you chose to restore the database, either by using SSMS GUI or SQL scripts.

You can use the following commands to rename the logical name of the data files and log file:

USE [<SID>]
GO
ALTER DATABASE [<SID>] MODIFY FILE (NAME=N'<OLDSID>DATA1', NEWNAME=N'<NEWSID>DATA1')
ALTER DATABASE [<SID>] MODIFY FILE (NAME=N'<OLDSID>DATA2', NEWNAME=N'<NEWSID>DATA2')
ALTER DATABASE [<SID>] MODIFY FILE (NAME=N'<OLDSID>DATA3', NEWNAME=N'<NEWSID>DATA3')
ALTER DATABASE [<SID>] MODIFY FILE (NAME=N'<OLDSID>DATA4', NEWNAME=N'<NEWSID>DATA4')
GO
ALTER DATABASE [<SID>] MODIFY FILE (NAME=N'<OLDSID>LOG', NEWNAME=N'<NEWSID>LOG')
GO

I would expect SWPM to do it if I was performing a system rename. When I perform a System Rename with SWPM, yet he does it automatically. After the target installation I performed a System Rename with E84 to N84. The database, was renamed from E83 to N84, including the files and logical name of the files.

I see that the current way, this is by design, and it is working according to the system copy guide.

Regards,

Luis Darui

[Former Member · Accepted solution]

Luis,

Thank you for the prompt and thorough reply.Your example for renaming the LOGICAL FILE NAME is exactly what (in my opinion) is missing from and should be added to SAP Note 151603. That note gives an example of renaming the physical file names in a new location but mentions nothing about when and where the LOGICAL FILE NAMES should be/are changed. I knew the the LOGICAL NAMES hadn't changed before running SWPM. I just wasn't sure if I should have changed them before or whether SWPM would do it when it converted the schema. I had talked to a former associate who in his limited time to talk on the phone said "Don't worry, SWPM does everything for you".

Like I said, it was my first time doing a homogenous copy with SQL Server. And I learned a lot from doing it. I just wanted clarification on the LOGICAL FILE NAME change which you have done. I still think SAP should spell it out better in note 151603.

Thank you again.

Sincerely,

Stephen Ferracioli

[luisdarui · Accepted solution]

Hi Stephen,

Thank you for the feedback.

I'll contact with the responsible for the SAP Note to check whether we can add the information about renaming the logical names through this process.

Kind regards,

Luis

[Former Member · Accepted solution]

Another small complaint is with the system copy guide itself. I don't understand why SAP tried to create one guide that can be everything to everybody. The version of the system copy guide I used is 137 pages, but for me to do a homogeneous system copy all I really needed to read were two or three pages that were scattered around. If you're doing a OS/DB migration, the system copy guide looks great. But for a homogenous system copy (especially on SQL Server) it's a lot of dead weight. I'd rather go to a separate homogeneous documents for an Oracle system or SQL Server or any other DBMS. It's like trying to "connect dots" with the current format.

Thanks again for your help and concern.

[Matt_Fraser · Accepted solution]

Lots of great observations. I totally agree about the system copy guide. I've been doing homogeneous system copies on SQL Server for ages, since long before SWPM was around, so I have a "cheat sheet" that I use, especially for the post-SWPM manual tasks.

I have always setup the new physical file names and paths manually during the target DB restore so as to properly match the new SID. For logical file names -- well, the guide doesn't go into it much, I guess, because if it's the only SAP database on the SQL Server installation, then they don't matter.

The logical file names don't impact anything at all about the running of SAP on SQL Server, so you can just ignore them, leave them alone entirely. If you are running MCOD, however, then you need to make sure they don't conflict, so I can see how in your situation, restoring to a new DB on the same DBMS, that could be an issue. It's very easy to rename the logical file names after the restore via the Management Studio (no need to get into SQL Query at all), and you can do it before running SWPM, after running SWPM, or just not do it at all, as I mentioned.

[Former Member · Accepted solution]

Thank you very much Matt for replying and for the excellent technical feedback. As I mentioned, I've done homogeneous system copies many times over the years on Oracle systems, most on AIX, and more recently on RHEL. I always performed them manually. This was not only my first opportunity on SQL Server but also using SWPM. Throw in the MCOD confirguaration that my current client is using and I was scrambling to sort out all the technical requirements.

I've found SCN to be a tremendous resource over the past several years. But I had never posted on SCN before. I wanted to post this thread because I wanted to clarify the requirements for LOGICAL FILE NAMES in particular with a distributed MCOD environment in which I am currently consulting in.

You're response in addition to Luis' response is extremely helpful, and I hope that if other admins ever find themselves in a similar situation, they may benefit from this thread. In addition to updating SAP note 151603 about logical file names, it would also be beneficial in my opinion to describe the pitfalls of doing a homogeneous database copy on the same SQL Server as the source in an MCOD environment. If one is to follow 151603 as a guide, they will not be able to execute the CREATE DATABASE command and successfully ATTACH the newly cloned database because of the conflict which you have clarified. That is why I left the source database detached until I was able to complete the system copy on the new target.

Again, your response is greatly appreciated and I am very thankful to the SCN community for its help.

Sincerely,

Stephen Ferracioli

[luisdarui · Accepted solution]

Hi Stephen,

I didn't comment the MCOD part because I understood it was not the case, because MCOD in SQL Server would be like SCM, SRM, CRM and ERP in the SAME database, where each system would have its own schema inside the same database, and not many databases inside the same database instance. This case of One instance, Multiple databases is often used in test systems and the restore process that really is relevant is the File Name being unique. Other concerns regarding separate disks/mount points is a matter of further discussion, but the point is that, currently, it does not affect the system copy process. I must say that I've never seen a MCOD system.

So far it won't affect at all your system to have different logical names of your database. See the following examples:

They will co-exist without problems.

Regards,

Luis

[Former Member · Accepted solution]

Yes Luis, I agree. It would have probably been better if I had had a completely separate VM for a Sandbox environment. But my client has limited resources and has already installed all their DEV and TST systems into one MCOD. They are a small company and only startup the systems when they need them. They want to perform an ECC upgrade, and it was I who insisted in doing that in a Sandbox system first. So yes, you are correct. This is not a typical MCOD environment.

[luisdarui · Accepted solution]

Hi Stephen,

I didn't mean that it is not a good choice/implementation. I meant that it is not MCOD (Multiple Components, One Database) in its very foundations, it is just "One Instance, Multiple Databases". If we would have an anagram for it, it would be then OIMD.

I believe this is usually confused with MCOD because SQL Server concept is that the service that runs a database instance can host one or more databases, which has pros and cons.

Other database vendors would have a service running for each database you want to host.

With SQL Server you can host under the same database instance (not suggested) database for each systems like ERP, SCM, CRM, etc and they would be isolated from each other. I mean you can put offline the CRM database without bringing offline the SCM database for example. That is not possible in MCOD  because once you put the database OFFLINE you wouldn't be able to access any of the systems because they're under the same database (MCOD).

Kind regards,

Luis

[Matt_Fraser · Accepted solution]

OIMD looks a little like OMG!

Yeah, not really MCOD, but co-located databases in a single host.

Part of the issue, for me, with Note 151603 is that, despite having been regularly kept up-to-date, it essentially describes a manual and error-prone way to achieve things. Why would you type out all those SQL commands when you can do all that using the graphical Management Studio tool? Now, if you regularly refresh the same test system from backups of the same prod system, you might want to write it out in SQL so you can save it as a reusable script, but otherwise for one-offs it's quicker and easier to just use the tool that Microsoft provides.

Anyway, Stephen, welcome to the wacky world of posting on SCN. Watch out -- it gets habit-forming.

[Former Member · Accepted solution]

Well, I am honored and thankful to have had two very knowledgeable individuals reply to my very first SCN post.

[Former Member]

For clarification purposes:

I used 70SWPM_SP09 and my system is SAP Netweaver Ehp2.

Attach/Detach method