?
Solved

Pre Creat and Pre Size SQL Databases for Sharepoint

Posted on 2009-12-17
16
Medium Priority
?
772 Views
Last Modified: 2012-05-08
I need to get some clarification regarding the pre-creating and pre-sizing of the SQL databases for SharePoint.  Many online documents reference that you should pre-create the databases and pre-size them, such as the content db's.  they also mention that you should pre-size the tempdb.  However, I need some clarification regarding the sizing process.  For example, if I pre-size all of my content db's to be 100GB (which is the max I plan to have per DB, based on MS recommendations), but then these references say that you should have multiple data files per DB and they recommend 4-8 datafiles if the physical server has 8 processors, which it does.  They then provide a formula for determining the file size per datafile and in this equation they say to take the max db size and multiple by .25 then divide for the number of datafiles.  Here is where I am confused, with this formula I would have (4) datafiles of size 6.25GB each, for a total of 25GB, but it seems to me that they need to equal 100GB if that is what I am pre-sizing the db to.  I need some clarification on this process from some of you SQL/Sharepoint guys out there.  Thanks.
0
Comment
Question by:shockey
  • 7
  • 7
14 Comments
 

Author Comment

by:shockey
ID: 26075373
the article I am referencing in regards to the pre-sizing and pre-creating of the databases and the formula for sizing the data files is below...

http://www.knowledgelake.com/whitepaper/Scaling%20SharePoint%202007%20-%20Storage%20Architecture.pdf

0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 1600 total points
ID: 26077278
>> they recommend 4-8 datafiles if the physical server has 8 processors

Ideally this is the recommended configuration for tempdb..
Split the tempdb into files equal to that of the processor count.

>> Here is where I am confused, with this formula I would have (4) datafiles of size 6.25GB each, for a total of 25GB, but it seems to me that they need to equal 100GB if that is what I am pre-sizing the db to.

Ok..
Pre-growing it to 100 GB is recommended if data growth in your database is high.
Else its not that much useful and hence that calculation applies.
If you have 4 processors, then 4 * 6.25 or 4 * 12.5 files would be helpful
If you have 8 processors, then 8 * 6.25 would be helpful.

In addition to pre-growing data files, you also need to take care of Auto-growth property..
Set it to grow at 1 or 2 GB instead of the default 10% or 1 MB which would also impact performance..
0
 

Author Comment

by:shockey
ID: 26080356
sorry, I'm still confused.  if I know that we will have large databases because we have alot of data to import into Sharepoint, then I was planning to pre-size the ContentDBs to 100GB each.  However, using the formula from the available documentation:

"With a multi-core SQL Server, the content database should consist of multiple data files.
The number of data files can be calculated as between ¼ to ½ the number of CPU cores.Typically, we see 2 data files being sufficient for a 2 or 4 core SQL Server, and 4 data files being ideal for 8 or 16 core SQL Server.

The size of each data file can be calculated using this formula: [MAX DB SIZE (KB)] X [.25] / [# DATA FILES] = DATA FILE SIZE (KB)

With this formula you always start by taking the max size, 100GB in this case, and multiply it by .25, which means that when you add up the size of your data files you are always going to be at only 25% of the size that you want to pre-size the DB to.  If I want to pre-size the db to 100GB, and the article says that 4 data files is optimal for 8-16 processors, then shouldnt I have (4) 25GB data files for a total of 100GB???

Also, everything I've read only recommends autogrowing by 10% instead of a fixed size as you stated, where did you get this recommendation?

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:shockey
ID: 26096901
anyone w/ clarification on this topic?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26097937
Kindly post out the link for the article you are referring to so that I can find out what they meant to explain..

>> If I want to pre-size the db to 100GB, and the article says that 4 data files is optimal for 8-16 processors, then shouldnt I have (4) 25GB data files for a total of 100GB???

Ok.. As per your explanation, I understood that your Max file size might reach 100GB..
So growing it to 100GB is not ideal and if you do so then there is no need to set Autogrowth property at all..

And hence you can grow to some value say 25 GB is itself a maximum value..



>> Also, everything I've read only recommends autogrowing by 10% instead of a fixed size as you stated, where did you get this recommendation?

Autogrowth by 10% is not recommended if you have Lesser Initial file size..
Since you set it to a Maximum value, 10% would be manageable if your 10% is at least greater than 1 GB..
Your Data files growing by 1 GB would create 10 Virtual Log Files if it extends 10 times..
Overall, if you have lesser no. of Virtual Log Files, then it is ok..

Or as recommended by me, you can have Autogrowth property set to 1 / 2 GB, then it should be ok too..
0
 

Author Comment

by:shockey
ID: 26104457
this is the link to the article describing the recommended approaches to database files for Sharepoint and the formulas for pre-sizing them:

http://www.knowledgelake.com/whitepaper/Scaling%20SharePoint%202007%20-%20Storage%20Architecture.pdf

maybe I am not clear on the process of autogrowing and pre-sizing.  if microsoft recommends keeping the content databases below 100GB, basically I just need the best approach for pre-creating and sizing them.  Also, I need the best approach for TempDB since, according to the formula in this article, it should be pre-sized based on a formula of some other max DB size, which I assume would be the 100GB content DB.  Thanks again.

-mcbride
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26104754
So my First Question:

* What would be your Max content db size expected?

>> Typically, we see 2 data files being sufficient for a 2 or 4 core SQL Server, and 4 data files being ideal for 8 or 16 core SQL Server.

They have recommended these kind of environmental setup for content databases having size more than 100 GB..
Even we can do it for databases lesser than 100 GB for better performance provided the files splitted are placed in separate LUN / drive with individual spindles for better performance..
Placing two files in a single disk with logical fragmentation doesn't help.

If you look at 2 data files for a 2 or 4 core machine, then it would be around 1 to 1/2 for your processor count to the data files..

But kindly note that this would make significant performance improvement if it is placed in separate disks with individual spindles..

My Second Question here:

* How much Physical Drives you have in your environment

Hope I clarified you..
0
 

Author Comment

by:shockey
ID: 26105148
I have about 1 TB of raw content that will eventually be imported into Sharepoint over time.  Since micosoft recommends a max of 100GB for the contentDB's, I was assuming I would precreate and presize about (10) 100GB contentDB's.

My plan so far to start with was to have (2) 450GB drives in RAID 1 for the OS/Logs.  To have (4) 450GB drives in RAID 5 for TEMPDB and have (6) 450GB drives in RAID 5 for the ContenDB's.  So the content DB's would all be on the same array spread over (6) physical disks.

With this scenario does it still make sense to split into multiple data files?  What if this (6) disk RAID 5 array only has a single drive letter?

-mcbride
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26105472
>> I have about 1 TB of raw content
>> With this scenario does it still make sense to split into multiple data files?  What if this (6) disk RAID 5 array only has a single drive letter?

Yes.. You can and should definitely split into multiple data files..
No issues with drive letter since it has 6 disk spindles in the array.

I would recommend you the following

1. Split your data files into 4 or 6 files in that array
2. Pregrow your Data Files to 100GB so you would have 6 * 100 = 600 GB
3. Set Autogrowth to 50 GB per file, so that it would grow in an efficient manner..
0
 

Author Comment

by:shockey
ID: 26106212
You are saying to pre-grow the data files to 100GB, however if the max content DB size is going to be 100GB, then that means there is only going to be 1 data file per content DB, which contradicts the direction I thought we were going.  According to the documentation is appears that each content DB should be 100GB max and that each of these content DB should consist of at least (4) data files.  So, this brings me back to the original question:  for each 100GB content DB do I pre-grow the database to consist of (4) 25GB data files and turn off autogrowth?  Or do I follow the formula on the documentation which in this formula they say to take the max db size and multiple by .25 then divide for the number of datafiles.  Here is where I am confused, with this formula I would have (4) datafiles of size 6.25GB each, for a total of 25GB?

Perhaps I am thinking too far into it, maybe I should just plan to pre create and pre size the content DB's to (4) 25GB data files per DB and move on with that.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26106263
In simple words, if your database is going to grow upto 1 TB immediately ( as you  mentioned about your import process) , then it would grow to that size even though you set it to 25 GB per file now..

And that's why I recommended 6 * 100 GB files..
If 1 TB growth as mentioned by you is going to be there, then even these 6 * 100 GB files would grow much higher to accomodate those data and hence having 4 / 6 * 100 GB is not a problem

But if it would not grow to 100 GB then set it to 4 * 25 GB files...
Hope this clarifies..
0
 

Author Comment

by:shockey
ID: 26106978
as stated, Microsoft recommends a max single content database size of 100GB, which is why I said I would pre-create (10) 100GB content DB's.  What I want to know is why the documented formulas regarding pre-sizing and pre-creating DB's have you pre-size them to .25 of their max size.  If you read the article, like  other articles available online, you will see this formula.  
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26109585
Yes, I understood your scenario.

You can create 10 * 100GB content data files..
But placing them in your array which shares a common disk spindle would not improve the performance as I mentioned earlier..

And hence I recommended to allow it to grow after 100 GB which would give better performance compared to 10 * 100 GB files..
And you need to pro-actively create new data files to hold data greater than 1 TB in that case..

Hope this clarifies.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26388203
angelIII,
     I recommend

Accept 26077278
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question