Moving from SQL Server to Oracle DB

AlbertoDangelo

AlbertoDangelo

Member
Hi everyone,

Our E1 implementation has several environments/databases around 2Gb in size each, and we are planning to consolidate some of them in a single one, therefore the total size will be increased to a several Gb per database. Since we are also planning for replatforming the infrastructure this is probably a good time to evaluate moving from SQL Server to Oracle DB due to the database size increase. I wonder if you, experienced members, would eventually support the idea of moving to Oracle DB due to this database size change. Is 4 Gb or more too much for SQL Server in a JDE environment?

Thanks in advance for your comments.
 
We upgraded second half of 2012 from E1 9.0 to 9.1 and switched from E1 9.0 and Oracle DB running on a HP-UX to using E1 9.1 and SQL DB on Windows 2008. We also added the HR/Payroll module,(this is in addition to the many other modules we use) which increased the number of users. Our current prod db is 120 Gb and we have very good performance. Also, the data refresh from prod to test/dev takes far less time using SQL. We went through the process of making sure the servers were speced correctly.
You did not mention your configuration so not sure what E1 version, etc you are running

Hope this information helps

Dave Rammer
Sheboygan County E1 9.1 with Tools Release 9.1.3, SQL 2008 on Windows 2008 server, E1 running on Windows 2008 server and we have two WebLogic servers running Windows 2008 server one internal and one for external
 
Hola Alberto,

A few things to consider.

1). Current editions of ms SQL will easily support a 4 gb database. The argument for moving to an Oracle database is when you start measuring in the terabytes.

2). Take a look at costs. Traditionally, ms SQL licensing is much less than Oracle.

3). Support. Do you have in house experience supporting an Oracle database? It is quite different to support than ms SQL. It will take a bit of time to adjust.

4). Case sensitivity. Ms SQL is not case sensitive. Oracle is. This will impact apps like address book. For example. If you had a vendor "Gary's Fish Market", in address book with SQL, you could search on "gary's fish market" and get a hit. If your database is switched from MS SQL to Oracle, your search would have use the way the data was entered using the specific capitalization.

The technical explanation is that Oracle converts the letters into their ASCII equilient to do the search. ASCII uses different codes for upper and lower case. MS SQL is a bit more easy going for its searches. This isn't a show stopper, but it will add to the complexity of changing database platforms. Oracle does have a setting to allow for case insensitivity, but it comes with some major warnings and caveats.

I hope this gave you some additional food for thought. Saludos,

- Gregg
 
As much as I love the internals of Oracle, there's no reason to use it unless you have huge amounts of data or have very specific requirements.

Your database is relatively tiny. Oracle skillsets are expensive. SQL will plug into your general Microsoft architecture (depending on how much of a M$ shop you are) in a more simple way enabling everything from virtualization to monitoring depending on how far you go.

Oracle licensing often means that you can't afford their software with hardware you can afford. That's something I have difficulty accepting.

Malcolm
 
First, my disclaimer; I am NOT a DBA or a systems architect, I am a developer - but we are a small enough IT shop that I am not completely removed from those areas. Just weighing in to give you some perspective.

I believe our production database running on MS SQL Server is around 800 Gb (+/- a 100 Gb). In your case, it wouldn't appear to me that DB size alone would be a compelling reason to move to Oracle.
 
I have to apologize, I meant "2 TB" instead of "2 Gb". My current DB size is around 2 TB each. So in my question I should have said "Is 4 Tb or more too much for SQL Server in a JDE environment?"

We are planning to upgrade to E1 9.x sometime next year, therefore I still have time to propose an architectural change of this sort.

Once again, thanks for your comments.
 
[ QUOTE ]
Hola Alberto,

A few things to consider.

1). Current editions of ms SQL will easily support a 4 gb database. The argument for moving to an Oracle database is when you start measuring in the terabytes.

2). Take a look at costs. Traditionally, ms SQL licensing is much less than Oracle.

3). Support. Do you have in house experience supporting an Oracle database? It is quite different to support than ms SQL. It will take a bit of time to adjust.

4). Case sensitivity. Ms SQL is not case sensitive. Oracle is. This will impact apps like address book. For example. If you had a vendor "Gary's Fish Market", in address book with SQL, you could search on "gary's fish market" and get a hit. If your database is switched from MS SQL to Oracle, your search would have use the way the data was entered using the specific capitalization.

The technical explanation is that Oracle converts the letters into their ASCII equilient to do the search. ASCII uses different codes for upper and lower case. MS SQL is a bit more easy going for its searches. This isn't a show stopper, but it will add to the complexity of changing database platforms. Oracle does have a setting to allow for case insensitivity, but it comes with some major warnings and caveats.

I hope this gave you some additional food for thought. Saludos,

- Gregg

[/ QUOTE ]

Hi Gregg,

I meant "2 Tb" in size, my apologies for the typo. I truly appreciate your insight and comments, so far I'm most concerned on the technical aspects than the financial ones (performance and supportability is the final goal). That's why I'm inclined to consider Oracle DB instead of upgrading to SQL Server 2012 (or any other future version)

Thanks

Alberto
 
Switching from SQL Server to Oracle for the simple reason that "you don't think SQL Server can handle large databases" doesn't seem like a logical conclusion.

SQL Server easily supports JD customers with databases (just production) that would make your database look small.

If you're looking to switch for some other reason then yes ..........consider a switch.

Other than that that simply:

1) Upgrade to SQL Server 2012 ENTERPRISE
2) Invest in SSD for TempDB and Transaction logs as a minimum
3) Separate your Production and Non-Production Databases
4) Compress the tables and indices of your largest files

This will shrink your 2 TB Production database to about 350 GB - 400 GB........yep atleast 1/4 the current size.

If you're moving to Oracle then I suggest you get someone who's done it before to help you stage this so that the go-live window is just a few hours for the switch.

Overall the SIZE of the databse has no impact on either Oracle or SQL Server until you get into the 50 TB+ size.

What DOES matter is how you architect the storage and infrastructure so spend your time there instead of worrying about 'what' database because the question is really 'what storage'.



Colin
 
[ QUOTE ]
Switching from SQL Server to Oracle for the simple reason that "you don't think SQL Server can handle large databases" doesn't seem like a logical conclusion.

SQL Server easily supports JD customers with databases (just production) that would make your database look small.

If you're looking to switch for some other reason then yes ..........consider a switch.

Other than that that simply:

1) Upgrade to SQL Server 2012 ENTERPRISE
2) Invest in SSD for TempDB and Transaction logs as a minimum
3) Separate your Production and Non-Production Databases
4) Compress the tables and indices of your largest files

This will shrink your 2 TB Production database to about 350 GB - 400 GB........yep atleast 1/4 the current size.

If you're moving to Oracle then I suggest you get someone who's done it before to help you stage this so that the go-live window is just a few hours for the switch.

Overall the SIZE of the databse has no impact on either Oracle or SQL Server until you get into the 50 TB+ size.

What DOES matter is how you architect the storage and infrastructure so spend your time there instead of worrying about 'what' database because the question is really 'what storage'.



Colin

[/ QUOTE ]

Hi Colin,

Thanks for your comments. You are right, database size should not be the only reason, although I was a bit worried about it. We already compressed some tables and indexes (not all, we selected the large ones with a high ratio of reads over writes, plus F0911), and our non-Prod is in a separate database server and storage. However we never did a POC on faster disks for TempDB and Logs which sounds very good to me, I'll try to make that happen.

Thanks

Alberto
 
Hi Alberto,

4 Tb is not too large for SQL 2005 and above.
I concur with Colin's comments : plan carefully your storage strategy and engage well-experienced MSSQL DBAs.

Any database running on the wrong storage, or managed
by the wrong DBAs may turn into a nightmare.
 
[ QUOTE ]
Hi Alberto,

4 Tb is not too large for SQL 2005 and above.
I concur with Colin's comments : plan carefully your storage strategy and engage well-experienced MSSQL DBAs.

Any database running on the wrong storage, or managed
by the wrong DBAs may turn into a nightmare.

[/ QUOTE ]

Hi Sebastian ! Good to hear from you ! Thanks for the comments, I'll have that in mind.

Un abrazo !

Alberto
 
Back
Top