Storage consumed in Oracle and SQL Server for geodatabase

[SherrieKubis]

Oracle 12.1   SDE 10.4.2

and 

MS SQL 2016 SP1 

SDE 10.5.0 

As we migrate from Oracle to SQL Server I see that MS SQL takes more storage, but I don't see a pattern.  In one geodatabase Oracle consumed 10GB, SQL Server 15GB.  In our largest geodatabase, Oracle is consuming about 100GB, Sql Server is almost at 200GB and the data is less than half migrated.  That would make it about 5x more in SQL Server.  What am I missing?  I'm not as well versed in SQL Server, but it seems pretty straightforward.  

In either Oracle or SQL Server we don't change DBTUNE and take the defaults.  

Any insights or suggestions are appreciated. 

Sherrie 

[Asrujit_SenGupta]

Check the Log file size for the SQL Server databases. Sometimes the data file size will not be that much, but the Log file could be large and thus the total database size shows up to be quite larger than expected.

Note: ArcGIS 10.4.2 does not exist. ArcGIS 10.4.1 was released after ArcGIS 10.4 and then came 10.5.

[KevinDunlop]

We are currently migrating too.  We have SDE 10.4.1, Workflow manger, and Data reviewer in our database.  We do not have any rasters in the database.  Here is sizes:

Oracle 11.2.0.4: 49897 MB

SQL Server 2016: 55341 MB

One thing we have notice is that the SDE_Log tables make the log files swell and take a TON of space in Oracle (often 1 to 3 times total datafiles size)  when doing large selections and heavy data reviewer sessions.  In SQL Server, they are stored in the TempDB and take almost no space at all.  

[SherrieKubis]

Sorry, I did mean 10.4.1 - typo.  

Are you referring to the Transaction Log in SQL Server?  That is not included in the size of the database file that I am describing.  We allocate our T-Logs to as disk, and our database files to another disk. It is the database size that I am referring to.  

Your sizes look to be what I expected, a little more in SQL Server but not a great amount.  That's why I think I'm doing something not quite right.  I'm taking the defaults of DBTUNE.  

In Oracle, we use a pool of log file tables and pre-create the number that we need so that editors or viewers don't need the create table privilege.   In our heavily edited database where sometimes the log pool can be exhausted, we create a nightly job that does an sdelog command line that 0's out the log pool and then recreates it at the number we need.  I agree that SDE log files are better in MS SQL because they use tempdb.  

Do you take the defaults in DBTUNE or change something?

[MichaelVolz]

Sorry to get off topic, but why are you migrating databases from Oracle to SQL Server?  Are you finding performance advantages to the GIS software when using a SQL Server SDE database compared to an Oracle SDE database?  Do you get better support from ESRI for a SQL Server SDE database compared to an Oracle SDE database?

[SherrieKubis]

HI Michael, no problem. 

We've been running ArcGIS/SDE with Oracle for about 22 years, I like our implementation, performance is great, and support has been good -- although we don't open many tickets for SDE because it's smooth sailing.  

Our management wants to move to SQL Server because of cost, it has something to do with we used to have a bigger Oracle footprint, then downsized, and now we can't renegotiate our contract (for some reason) and we are still paying a higher price.  

I'd love to hear experiences of anyone who has migrated from Oracle to SQL Server.  I'm well-versed on the Oracle side, and many of the SQL Server concepts are the same, mostly reckoning back to older Oracle versions.  Management was under the impression that SQL Server administration is easier, but so far my experience isn't that it's easier, it's just different.  There will be growing pains.  

So it comes down to we are migrating not for a technical reason, but for a dollar amount reason.  

Sherrie

[MichaelVolz]

Thanks for the info Sherrie.

Do you have other enterprise systems besides GIS where you are moving away from Oracle in favor of SQL Server?

At my org, the implications of migrating from Oracle to SQL Server, for GIS alone, would mean rewriting many scripts that are Oracle based to SQL Server based, so it would be quite a large undertaking.  In the end I'm not sure there would be any saving of money as other projects would need to be put on hold until this large task is completed as there is a large amount of risk involved with major modifications to enterprise systems.

[Asrujit_SenGupta]

Maybe use something like the below to get a rough idea as to which all objects are consuming the most space:

SQL Server – Get Sizes of All Tables and Indexes in a Database | My Adventures in Coding 

[KevinDunlop]

We are moving from Oracle to SQL Server for three reasons.  First and most important, the licensing cost is much higher for Oracle compared to SQL Server.  By switching, we have over 50% on licensing fees.  Secondly, Oracle boxes are UNIX while every other server is Windows.  While we could run Oracle on Window's it was determined that our organization will use Microsoft software as much as possible.  Lastly, Oracle is overkill for what we do and is not very user friendly compared SQL Server.  We have an additonal outside contract to manage the Oracle software which will go away when we move to SQL Server.  This is an additional saving for us.

[SherrieKubis]

Kevin, 

What size are your geodatabases?  Do you have other enterprise data in SQL Server, or are you just concerned with spatial data?  How has your conversion from Oracle to SQL Server worked, are you also seeing storage differences? 

Oracle on Oracle Linux is The Bee's Knees, I think it's straightforward, and mature, with the added benefit of opensource Linux. Oracle's Grid Infrastructure with its Automatic Storage Management is very efficient.  It takes some expertise, and for a good implementation you need an Oracle DBA. 

Some points-of-view is that going to MS SQL means you don't really need a DBA (as much) because MS SQL is easier, and a lot is next-->next-->next.  Databases that we have supporting COTS applications are like that -- if there is an issue, go to the vendor for support.  I don't agree that a MS SQL DBA skills aren't needed for a large SDE implementation, where we have in-house applications, ETL, and a lot of other things hitting it.  

Money is usually the driving factor.