topic Re: Storage consumed in Oracle and SQL Server for geodatabase in Geodatabase Questions

Storage consumed in Oracle and SQL Server for geodatabase

SherrieKubis — Tue, 17 Oct 2017 20:07:58 GMT

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

Re: Storage consumed in Oracle and SQL Server for geodatabase

Asrujit_SenGupta — Tue, 17 Oct 2017 20:14:18 GMT

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.

Re: Storage consumed in Oracle and SQL Server for geodatabase

KevinDunlop — Tue, 17 Oct 2017 20:24:32 GMT

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.

Re: Storage consumed in Oracle and SQL Server for geodatabase

SherrieKubis — Thu, 19 Oct 2017 12:03:24 GMT

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?

Re: Storage consumed in Oracle and SQL Server for geodatabase

MichaelVolz — Thu, 19 Oct 2017 13:29:16 GMT

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?

Re: Storage consumed in Oracle and SQL Server for geodatabase

KevinDunlop — Thu, 19 Oct 2017 13:48:24 GMT

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.

Re: Storage consumed in Oracle and SQL Server for geodatabase

Asrujit_SenGupta — Thu, 19 Oct 2017 15:59:11 GMT

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

Re: Storage consumed in Oracle and SQL Server for geodatabase

SherrieKubis — Thu, 19 Oct 2017 19:32:47 GMT

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

Re: Storage consumed in Oracle and SQL Server for geodatabase

MichaelVolz — Thu, 19 Oct 2017 19:39:43 GMT

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.

Re: Storage consumed in Oracle and SQL Server for geodatabase

SherrieKubis — Thu, 19 Oct 2017 19:44:40 GMT

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.

Re: Storage consumed in Oracle and SQL Server for geodatabase

VinceAngelo — Thu, 19 Oct 2017 21:27:48 GMT

It's really difficult to predict the difference in storage between RDBMS implementations. That said, I haven't noticed huge differences (except with respect to the RDBMS binary catalog overhead -- Oracle is a *huge* install).

Note that porting an SDO_GEOMETRY-storage geodatabase to Geometry-storage would produce different characteristics than an SDE.ST_GEOMETRY source (due to compression in the SDE.ST_GEOMETRY implementation).

- V

Re: Storage consumed in Oracle and SQL Server for geodatabase

SherrieKubis — Fri, 20 Oct 2017 12:43:29 GMT

Hello Vince! Oracle's a beast, but I'm focusing on geodatabases and the spatial implementation. We use ST_GEOMETRY, nothing SDO. In MS SQL, we are using the native spatial type. In both Oracle and MS SQL we've not modified DBTUNE, but the default in there is PCTFREE 0. We are using Oracle ASM and ASSM Tablespaces. In MS SQL we also use the default DBTUNE and it uses FILLFACTOR 75. I'm going to look in that direction, that seems a likely place.

Re: Storage consumed in Oracle and SQL Server for geodatabase

KevinDunlop — Fri, 20 Oct 2017 13:05:58 GMT

Our Oracle is about 60 GB total. The outside contract we have was in place before I took my job as the SDE admin. That contract handles patches, back ups, and other software relate stuff for all Oracle in our organization (more than just the SDE Oracle). So while I could manage the Oracle software myself, the policy is for the Oracle support contract to do it since the other area's DBAs are not interested in doing it themselves. The decision to move all databases to SQL Server affects more than GIS especially since other 3rd party (non-GIS) applications are dropping Oracle integration support in favor of SQL Server. But there is also a big cost savings which is the main reason.

Re: Storage consumed in Oracle and SQL Server for geodatabase

TinaMorgan1 — Fri, 20 Oct 2017 15:51:14 GMT

Take a look at these Microsoft resources discussing files and filegroups, as well as growing log files...

Using Files and Filegroups to Manage Database Growth

Help! My SQL Server Log File is too big!!! - TechRepublic

See the part about database recovery model in ^ article.

Re: Storage consumed in Oracle and SQL Server for geodatabase

SherrieKubis — Sat, 21 Oct 2017 13:32:53 GMT

Thanks Tina, it's really about the ArcGIS objects in the datafiles, logfiles aren't the issue. I'm looking for other experiences when migration from Oracle to MS SQL with ArcGIS, and if anyone has seen a size difference -- good, bad, big or small. There are things to do in MS SQL like FILLFACTOR for indexes, compression, revising datatypes, shrinking the datafile to get to a target size, but the only think I'm looking at is FILLFACTOR, they others are not something I want to mess with. To me, they are different RDBMS systems with different storage, and what is required, is, well, required. But I can't help wondering why others haven't seen this, or at least I haven't found them.

Re: Storage consumed in Oracle and SQL Server for geodatabase

SherrieKubis — Thu, 26 Oct 2017 13:56:21 GMT

As it turns out, this issue is not caused an object's storage characteristics, but the datafile. I created the datafile at a reasonable size and allowed autogrowth just in case, but at a percentage instead of size. Each time space is added, it's an exponential size, but there was unused space in there, and each time it was more unused space. Everything I've read MS SQL uses a Global Allocation Map to find an unused block, but that didn't seem to be happening, just a bunch of white space everywhere with bad performance.

We started over and I made the datafile bigger with an autogrowth of 1MB and the behavior went away.

This is staging, when we have a handle on how much storage is required for each of our databases, development through production will be adequately sized from the get go.

The motto is don't use a percentage autogrowth size ...

Sherrie

Re: Storage consumed in Oracle and SQL Server for geodatabase

VinceAngelo — Thu, 26 Oct 2017 14:06:07 GMT

1Mb is probably too small, and will likely result in fragmentation if there is any other activity on that device.
Try using fixed 100Mb or 200Mb segments (this will also likely improve insert performance).

- V