How to configure the filegroup for SDE GDB

RobertHu · ‎06-08-2010

We are moving from SDE/Oracle/Unix to SDE/SQLServer/Windows environment. I am working on a couple of SDE databases on a test server, and have a question regarding the filegroup configuration.

In ESRI SDE/SQL Server class, I learned the configuration roughly as these. The wizard driven post installation created database and loaded SDE repositry in the PRIMARY filegroup. Then, we manually created GIS_data filegroup to store vector data, versioning filegroup for A and D tables, and raster filegroup for BLK related objects.

While working on our test instance, we talked to an ESRI consultant. His thought is that the storage architecture from the training may be too complicated. He even cited a product specialist opinion as "there is not much to be gained from using multiple file groups period."

From my SDE/Oracle experience, I'd like to make the storage structure of the new SDE GDB simpler, but am wondering if it is a good idea to put everything, SDE repositry and all GIS data (vector, raster, etc.), in the only primary filegroup. How did you guys design and build your SDE/SQL Server databases?

Any suggestion, opinion and thought will be appreciated. Thanks!

p.s.
Our storage device is SAN 8000 with one channel. The publication GDB is about 120 GB, all standalone FCs, no versioning. Two maintenance GDBs each should be 30 GB or less.

VinceAngelo · ‎06-08-2010

I'd have to see (and reproduce) research that showed that using multiple filegroups
(I leave the primary to the database, place sde in one, raster in two, and vector in
at least two) hurt performance before I stopped using them. Using one big file
puts you at the mercy of fragmentation, and doesn't allow you to split data across
multiple independent disks. You can always defragment the files on disk if you've
got them. Just my $0.0204 (with inflation).

- V

RobertHu · ‎06-09-2010

Hi Vince,

Thanks for your opinion.

I am surprised that not many SDE users have issues with filegroup configuration. Maybe the storage structure isn't critical in SQL Server?!

Thanks again.

VinceAngelo · ‎06-09-2010

SQL-Server is certainly more forgiving than Oracle, but it also has a completely different
storage architecture. It still takes a little work to obtain optimal performance; I'd like to
say I'm amazed that more folks don't make the effort, but when ease of administration
is emphasized, tuning is always going to take a back seat.

- V

ShannonShields · ‎06-22-2010

I'd have to see (and reproduce) research that showed that using multiple filegroups
(I leave the primary to the database, place sde in one, raster in two, and vector in
at least two) hurt performance before I stopped using them. Using one big file
puts you at the mercy of fragmentation, and doesn't allow you to split data across
multiple independent disks. You can always defragment the files on disk if you've
got them. Just my $0.0204 (with inflation).

- V

You can still make multiple files for an individual database and place them wherever you want. SQL Server will write across all the files proportionately. What filegroups give you is a way of logically grouping one or more database files so that you can control which objects get written to which files. But you can still have multiple smaller files without having to use filegroups. Once you create filegroups, however, then you must direct objects to those filegroups explicitly, or they will never get written to.

For users with a single RAID configuration there is basically nothing to be gained by creating multiple filegroups on the array & directing individual objects to one filegroup or another - you'd be emulating RAID, and RAID can do a better job of striping.

If you are spreading a single database across multiple storage arrays then you may want to give some thought to access patterns and possibly divide data accordingly. Also, there are backup strategies that use filegroups which are useful if your database has actively edited data as well as large amounts of static read-only data. The static data can be written to specific filegroups which are then designated read-only and excluded from the more frequent backups of the read-write data.

That said, the majority of users are probably not going to see much benefit from micro-tuning object placement on individual filegroups.

-Shannon

RobertHu · ‎06-23-2010

For users with a single RAID configuration there is basically nothing to be gained by creating multiple filegroups on the array & directing individual objects to one filegroup or another - you'd be emulating RAID, and RAID can do a better job of striping.

-Shannon

Hi Shannon,

Thanks for your input.

In my case, the SA setup one logical drive (D) which points to the SAN system. I don't know exactly how they configured the SAN, but believe it is RAID 5. It looks like our configuration matches the "single RAID configuration" you mentioned.

From my SQL Server training class and a few SQL Server books, I got the impression that the .mdf file, which associates with the Primary filegroup, contains the database info. It is a good practice to create user-defined filegroup to store user data. Is my impression wrong? My SQL Server experience is only about 4 months, and no other DBA in my agency knows it better unfortunately.

Thanks again!

TedCronin · ‎06-23-2010

Hi Shannon,

Thanks for your input.

In my case, the SA setup one logical drive (D) which points to the SAN system. I don't know exactly how they configured the SAN, but believe it is RAID 5. It looks like our configuration matches the "single RAID configuration" you mentioned.

From my SQL Server training class and a few SQL Server books, I got the impression that the .mdf file, which associates with the Primary filegroup, contains the database info. It is a good practice to create user-defined filegroup to store user data. Is my impression wrong? My SQL Server experience is only about 4 months, and no other DBA in my agency knows it better unfortunately.

Thanks again!

Hey Rob -

Don doesn't even know the answer. This migration will be good for the TLMA team.

Our setup down here is one filegroup with multiple files (Well for our Production and Pub databases), using RAID, along with Database Mirroring. Are you wanting to setup a multiple file instance akin to your current oracle setup?

ShannonShields · ‎07-02-2010

Hi Shannon,

From my SQL Server training class and a few SQL Server books, I got the impression that the .mdf file, which associates with the Primary filegroup, contains the database info. It is a good practice to create user-defined filegroup to store user data. Is my impression wrong? My SQL Server experience is only about 4 months, and no other DBA in my agency knows it better unfortunately.

Robert,

you can create a second database file & filegroup, and then make that the default filegroup. All subsequently created data will go to the default filegroup. This is a simple way to make sure all your system tables are separate from your user data without having to worry about dbtune entries.

-Shannon

RobertHu · ‎07-06-2010

Hi Shannon,

Thanks for your advice!