GP SQL database growth

(0) Share

Report

Posted on by Clint Spann

I've been re-directed to post here after having posted in two other places, so please forgive me if it's not a good spot to ask:

I'm a SQL server DBA, and have around 3 yrs experience doing so. I do not have any experience with Great Plains.

Our company is currently migrating from MAS90 to Great plains, and I would like to be able to determine how big each database will grow so I can pre-size them. I don't know what type of back-end database that MAS90 uses, but I think it's proprietary. I can probably determine how much data is currently in our MAS90 system, but I don't know how that will translate to the 5 databases we have for our Great Plains installation.

Also, any help/links/articles/white papers would be appreciated! Thanks!

*This post is locked for comments

All responses (7)

Answers (0)

Suggested answer

Richard Whaley 25,195 on at

Like (0)

Report

Re: GP SQL database growth

Pre-sizing and preallocating resources was a required process years back to ensure optimum server performance. I have not seen it used in recent years. The dbs will be automatically created and set to grow as needed. They do not grow record by record as in past dbs but by blocks of space, optimizing disk storage and movements. The server then manages the db during "down time" to balance pages and decrease block reads.

The dbs will grow as data is entered. When data is deleted, the space is not automatically freed, holding the disk space for new records being added. This somewhat automates what you are trying to manually accomplish.
Brian P Connell 859 on at

Like (0)

Report

Re: GP SQL database growth

Clint - you bring up a good point about auto growth and defragmentation of the database. It is the same issue when you use the shrink feature....The database becomes physically fragmented on the drives. Maybe it is a good practice to set the autogrowth at something like 90 to100% untill it reaches a larger size (2-3 GB ). Since they start out at only a few hundred MB then it will take quite a while before they hit a few GBs. Then you can scale it down. to 50%. then 25%,etc. The alternative is to use a SQL disk defrag utility. Does anyone have commnets on this? these are just thoughts I'm throwing out there.
Suggested answer

John Lowther 5,122 on at

Like (0)

Report

Re: GP SQL database growth

Hey Clint,

As far as the initial migration I agree with all the previous post. Just create the company and let autogrowth take care of it. However, once you get up and running I agree with you completly. While I still have autogrowth on I use SQLCheck from Idera Software to monitor the size of the databases and how full they are. It is a great little program that also runs as a screen saver. And the best part is that it is FREE! When I see a database getting close to filling up I grow the database myself when most of the users are off the system. By taking this approach my GP databases have not hit the autogrowth in years.
Clint Spann 70 on at

Like (0)

Report

Re: GP SQL database growth

Thanks for your reply!

So, the problem I have with that, is that I have been taught to never rely on autogrowth, except for emergencies. Normally, I pre-size all DBs, because autogrowth can cause fragmentation and performance hits. I don't see how it should be any different, just because they are GP databases...
Brian P Connell 859 on at

Like (0)

Report

Re: GP SQL database growth

you dont size them. use gp utilities to create the company databases and the default sql options will most likely set them to grow at 20% when the reach xx% of the full size. they will most likely start out at 150 MB without any conversion data. (but you already know that).

depending on how much data you have for each company they could be between 500MB and 5 GB. over 5 GB is typically a larger database with lots of years history. Some 9very few) company DB's we use get up to 50 GB and larger. both answers from Richard and K day are accurate.

PS - its always an argument, but we set our DB's to simple recovery. (Feel free to argue that one anyone. )
K Day 7,365 on at

Like (0)

Report

Re: GP SQL database growth

When you say pre-size the databases, how were you going to do that? When you create the companies in GP, you use GP Utilities and I'm not a dba, but I have looked the sizes before and it automatically sets a size and allows auto growth.

The size of the database would definitely be a function of how much activity you will have and how much history you bring over from MAS90. From conversion I have done in the past, you might bring over summary financial information for financial statements historically, but still use MAS90 as your system of record for looking up historical detail. In this case, the amount of data is very small. Just really the GL Tables. But if you wanted to bring over historical subledger data (all invoices, payments, cash receipts, POs) they you are talking about a lot of data, also depending on how long they have been on MAS90

I have seen setups everywhere from 4 users and one company database to 100+ users and 100+ company databases.

Here's an example. One company I know has been using GP since 2008 with about 8 users using PO's, Sales Orders and nothing else really fancy. Their main company database just went over 1 gig. Some of their low activity subsidiary companies are in the 300 to 400 mb range.

Another company has been using GP since the late 90s Upgraded a few times and has activity tracking turned on and their DYNAMICS database is about 10 Gigs. One company database is 37 GB (Very High Activity) and another one is 30 MB (a few journal entries a month).

Hope that is somewhat helpful.
Richard Wheeler 75,730 on at

Like (0)

Report

Re: GP SQL database growth

I have only done a couple of MAS90 conversions to GP. In both cases we migrated GL,AP and AP and they both grew about 1.5 to 2.0 times the size. Do you have the MAS90 conversion kit? The last one we did was to GP 9. I do not know if there is one going to GP 10 or GP 2010.