Windchill Install with Amazon RDS?

[tbone7281]

Version: Windchill 13.0

Use Case: Installing Windchill in Amazon AWS. Preferred architecture is EC2 (Windchill App server) and an RDS instance.

Description:

Looking for clarification on options to install Windchill (13.1) on Amazon AWS/RDS.  The research I've done implies that people have done this, but I've not seen any specifics on how this was accomplished.

SQL Server

In a perfect world, I'd like to be using Microsoft SQL Server on Amazon RDS.  The short of it is that this would allow Windchill and ThingWorx to share a database server (while having their own databases, obviously).  This would also help limit the number of EC2 servers that need manually maintained down the road.  I'm aware that PTC documentation states that "SQL Server RDS is currently not supported".  https://www.ptc.com/en/support/article/CS255536

"Not supported" doesn't mean "won't work", so I've continued down this path for a while.  The issues I've found trying to leverage SQL Server on RDS:

• PSI requires password for 'sa' account.  Amazon RDS does not provide 'sa' users permissions.  I was able to get around this by naming the administrative user that is defined during the creation of the RDS instance as "sa" instead of the default "admin".  Not sure what/if any ramifications could result in this.
• PSI requires path location for SQL Server installation (i.e. C:\Program Files\Microsoft SQL Server\ Client SDK\ODBC\170\Tools)  I believe the installer assumes this location is local (EC2 server), not the referenced RDS server, which is a problem because of the next issue...
• Installation of Windchill using SQL Server necessitates running the PSI on the database server before installing Windchill on the app server.  Amazon RDS does not give access to the actual database server.  The only access is through SSMS that I have installed on the EC2 instance.

Oracle

After a fair amount of time trying to get the installer working with SQL Server, I decided to look at Oracle as I have not seen any documentation explicitly saying Oracle RDS is not supported/doesn't work.  However, stepping through the installer, I believe there are similar issues with Oracle as well:

• PSI requires the password for the 'SYSTEM' account.  Amazon RDS does not provide access to log in as the SYSTEM user.  (RDS for Oracle users and privileges - Amazon Relational Database Service)  I have also seen RDS documentation that basically says if you trick the system, for example, by creating another user called 'SYSTEM', then your entire DB instance could break.
• PSI requires path location for "Oracle Server Installation Directory" (ORACLE_HOME).  With RDS, I don't have access to the actual server the db is hosted on.  Similar scenario to needing the SQL Server Installation path.

As I mentioned in the beginning of my post, I've seen several other articles/threads stating that people have had success installing Windchill while using Amazon RDS.  I'd be very grateful if anyone who has done so could chime in and fill me in with what I'm missing.  TIA!

-Tony

[avillanueva · Accepted solution]

I am not familiar with Amazon RDS but I think the PSI gives you options if it would created the user and database or if you provide it a pre-existing user to an empty database and it creates the schema. There also should be options to manually create the database and schema yourself.  I've done installs and are never give SYSTEM account creds. I have my DBA create me a windchill DB user and I provide that to the PSI. It then creates the schema and loads the database as that user. 

[TomU]

Ditto.  Don't use the PSI to create the database.  Can easily create it yourself and no 'sa' account is required.

https://www.ptc.com/en/support/article/CS28197

https://www.ptc.com/en/support/article/CS28194

Creating an Azure SQL Database

[avillanueva]

Just for clarification, the PSI does not offer for Amazon RDS option to take an existing DB user?

[tbone7281]

The PSI behaves like it does any other time.  It has no knowledge that the db uses RDS compared to just being hosted on any other virtual server, the difference is simply the ability (or inability in this case), to access the underlying db server.  RDS is a managed service... it's Amazon's way of automating a lot of the overhead of a database server... the tradeoff being that to do so, they limit your access.  (I.e. You can't log on to the "actual" server, you don't have access to system level dba stuff, etc.)

[tbone7281]

This makes sense to me, trying to work through it now.  The errors I get now are misleading:

  

• It doesn't ask and shouldn't need any info about the sa account now.
• Named instance doesn't apply
• TCP Port number is definitely correct.

So, I think the issue is related to mapping between the servers.  (App and db.)  It's mentioned in this post:
https://community.ptc.com/t5/Windchill/Windchill-and-SQL-Server-on-Amazon-Web-Services/m-p/552533

Unfortunately, it's not quite as straightforward as they make it sound.  Amazon hides the private ip address for RDS servers.  The internet at large gives some options for tracking it down (via associated security groups) but in the same breath tells you how terrible an idea this is because the IP can and will change.

I think this is the right track though, I'll keep at it and post back here once I have a definitive solution worked out.

[tbone7281]

Just wanted to loop back around to this in case anyone stumbles upon this thread in the future.  The further issues I described above were NOT caused by server mapping issues.  In fact, if you set things up correctly when creating the RDS instance, there should be no need to add any entries to the host file for this purpose.  I errors I was getting was my own fault - because of the bouncing around I was doing trying to troubleshoot various problems while simultaneously trying to run installers I failed to realize that I had manually created the db user... but not the Windchill db itself.  Once I did that, it worked fine.

[MikeLockwood]

I tried learning about this a while back - had to abandon at that time. Rough notes attached.