Sharepoint databases?

Question

Sharepoint databases?

EllieDBA

SSCommitted

Points: 1786
More actions
March 19, 2009 at 9:23 am

#129115

can you tell me if you deal with administrating sharepoint databses and give some tips on.
Our sharepoint have been semi-live but we will go live by the end of this month nad i wnat to be prepared as much as I can for maintenance plans. Currently we have 3 full backups done during the night (local, share, evault) and 1 nightly transaction log backup (which I am trying to convince manager to increase frequency to at least 4 times daily) :
1- If you do any maintenance scheduled reboots for your SQL cluster enviornments (as this sharepoint environment is always live)
2- if your tempdb is big and if you don't do scheduled reboots or SQL service retarts,
how do you advise on the size of temp db in this environment?
3- what are the pros and cons of the databases that are set in simple recovery yet are very important to the business, like search databases? how do you deal with them?
4- most important how often do you backup your transaction logs on these databases?
5- Is there any of the following key databases that would have more tranaction once we go full live or require more looking after (e.g. teranaction log manual backup?)
Config_db
adminContent
SSP Databases
SSP search
Content
ANY advice or precaution would be greatly appreciated.
Ellie

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Tony-382973 SSC Enthusiast Points: 124 More actions · Answer 1

1- If you do any maintenance scheduled reboots for your SQL cluster enviornments (as this sharepoint environment is always live)

We do reboot our cluster environments one server at the time. Just wait for the rebooted server to fully come backup (without any errors) and then reboot the other server.

2- if your tempdb is big and if you don't do scheduled reboots or SQL service retarts,

how do you advise on the size of temp db in this environment?

Partition your tempdb based on the number of CPUs. So, if you have 4 CPUs create 4 data files for your temp db.

3- what are the pros and cons of the databases that are set in simple recovery yet are very important to the business, like search databases? how do you deal with them?

All of our sharepoint databases were put back to the full recovery mode for us to be able to take transactional logs.

4- most important how often do you backup your transaction logs on these databases?

On the sharepoint farm we create transaction logs every 20 minutes (we just have a requirement for the recovery purposes)

5- Is there any of the following key databases that would have more tranaction once we go full live or require more looking after (e.g. teranaction log manual backup?)

Config_db

adminContent

SSP Databases

SSP search

Content

Config_db shouldn't grow. Your Content database will grow depending on the use of the Sharepoint.

Gift Peddie SSC Guru Points: 73570 More actions · Answer 2

I don't do Sharepoint but start with these two links which will help you to understand what you need to maintain Sharepoint database.

http://blogs.technet.com/wbaer/archive/2008/02/27/just-published-whitepaper-database-maintenance-for-microsoft-sharepoint-products-and-technologies.aspx

http://technet.microsoft.com/en-us/library/cc262731.aspx

Kind regards,
Gift Peddie

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Make sure you read this one too. http://www.sqlskills.com/BLOGS/KIMBERLY/post/Database-Maintenance-Best-Practices-Part-I-e28093-clarifying-ambiguous-recommendations-for-Sharepoint.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

EllieDBA SSCommitted Points: 1786 More actions · Answer 4

Tony,

thanks for all the information. how often do you reboot your clusters? Does that help the tempdb (s) size?

Also, do you do Re-index for the databases and if so can you tell me which of the aforementioned databases would be more in need of re-index maintenance schedule plan?

again thnak you for all the information

Sudiendra SSCommitted Points: 1839 More actions · Answer 5

I cannot give point to point answer with my expertise. But here are few i know

Create a scheduled task using task scheduler and take backup of your sites and whole sharepoint as needed. In the next step, keep moving them to another server regulary. You can use central administration to take this backup or create a batch file. This will take the backup of Database as well.

Doing this on regular basis will help your site rather than database backups (i feel).

Tony-382973 SSC Enthusiast Points: 124 More actions · Answer 6

Hi,

We have to reboot all of our sql servers once a month. It does help with tempdb size but give it more space initially.

I have a script that gives us a repot for all indexes on all databases. Any index with over 20% of logical defragmentation we should be rebuilding but it is had to get an approval time for that since we run that job for all databases on that cluster (one db at the time).

T.

EllieDBA SSCommitted Points: 1786 More actions · Answer 7

Gila,

thank you so much for the article. I already have update statistics and create updates, I just added DBCC in maintenance plan wizard set to run for Sunday morning in the developement database, I will see how it goes on and how long it will take for my 50 Gb database, if so , then I will apply it for a few other databases. Then the next step would be to add re-indexing schedule.

Thanks as always.

Ellie

EllieDBA SSCommitted Points: 1786 More actions · Answer 8

Tony (3/19/2009)
Hi,
We have to reboot all of our sql servers once a month. It does help with tempdb size but give it more space initially.
I have a script that gives us a repot for all indexes on all databases. Any index with over 20% of logical defragmentation we should be rebuilding but it is had to get an approval time for that since we run that job for all databases on that cluster (one db at the time).
T.

Thanks Tony. I guess I should convince the SE team to plan for once a month reboot.

EllieDBA SSCommitted Points: 1786 More actions · Answer 9

Sudiendra (3/19/2009)
I cannot give point to point answer with my expertise. But here are few i know
Create a scheduled task using task scheduler and take backup of your sites and whole sharepoint as needed. In the next step, keep moving them to another server regulary. You can use central administration to take this backup or create a batch file. This will take the backup of Database as well.
Doing this on regular basis will help your site rather than database backups (i feel).

Sudiendra,

thank you for the input.

We Do have an evault backup of site that is an online and takes care of backing up the whole sites and server as well as SQL backups along with my usual SQL backups. Does that help? Meaning, would that assurance help me to better sleep at night:)

EllieDBA SSCommitted Points: 1786 More actions · Answer 10

Gift Peddie (3/19/2009)
I don't do Sharepoint but start with these two links which will help you to understand what you need to maintain Sharepoint database.
http://blogs.technet.com/wbaer/archive/2008/02/27/just-published-whitepaper-database-maintenance-for-microsoft-sharepoint-products-and-technologies.aspx
http://technet.microsoft.com/en-us/library/cc262731.aspx

Thanks Gift Peddie, I am already going through these white papers. There are even hellpful information for our non_sharepoint based databases.

Thank you,

Ellie

Sudiendra SSCommitted Points: 1839 More actions · Answer 11

Ellie - i never tasted disaster but during deployments, i use to take the site backup. Once deployment went wrong and we have tried something on config database which forced us to reinstall sharepoint. We just did a restore to bring back site to original form.

Now, its only a suggestion. Please test it on other environment and try it on prod 🙂