January 21, 2009 at 6:00 pm
Does anyone have experience on optimizing the configuration of SQL server for very large sharepoint databases? Can you partition tables? Is file group splitting the only option? Just curious what can be done for 100+ GB databases in sharepoint.
April 28, 2009 at 1:04 pm
Hello, with respect to MOSS, I think you already know the best way to optimise it already - just split off your indexes onto another physical disk/spindle etc. The rest is pretty standard maintenance, with a warning about how backups are dealt with (see end).
If you are able to connect MOSS to a DB on SQL 2008, then you could go for some compression - my blog post about Page and Row compression should help.
I will try that out (using sql v 100 ) in my current mandate when a SQL 2008 instance is made available, very interested to see how to optimise the backend of MOSS by means of page and row compression also, but I will have to wait out the worst of the crisis probably (end of summer if we are lucky).
With Sharepoint, Backups are done through the interface, so I have had to query the system tables to be sure there has been a full backup taken of the database before running some index rebuilding/reorganising.
begin
IF exists( select top 1*
from msdb.dbo.backupset
where datepart(dd, backup_finish_date)=datepart(dd, getdate())
and datepart(mm, backup_finish_date)=datepart(mm, getdate())
and datepart(yyyy, backup_finish_date)=datepart(yyyy, getdate())
and name ='SharePoint_DB'
order by database_backup_lsn desc )
begin
ALTER DATABASE --- perform maintenance here
end
return
end
hope this helps,
Huggy Bear
[font="Verdana"]Town of Mount Royal, QC
SQL Server DBA since '99
MCDBA, MCITP, PMP, MVP '10, Azure Data Platform Data Engineer
hugo@intellabase.com [/font]
https://drive.google.com/file/d/1qnyiGWyGvDz6Q2VtLPGEsRufy9CUqw-t/view (MCDBA 2001, data eng associate coming asap)
April 28, 2009 at 1:25 pm
Hugo Shebbeare (4/28/2009)
Hello, with respect to MOSS, I think you already know the best way to optimise it already - just split off your indexes onto another physical disk/spindle etc. The rest is pretty standard maintenance, with a warning about how backups are dealt with (see end).If you are able to connect MOSS to a DB on SQL 2008, then you could go for some compression - my blog post about Page and Row compression should help.
I will try that out (using sql v 100 ) in my current mandate when a SQL 2008 instance is made available, very interested to see how to optimise the backend of MOSS by means of page and row compression also, but I will have to wait out the worst of the crisis probably (end of summer if we are lucky).
With Sharepoint, Backups are done through the interface, so I have had to query the system tables to be sure there has been a full backup taken of the database before running some index rebuilding/reorganising.
beginIF exists( select top 1*
from msdb.dbo.backupset
where datepart(dd, backup_finish_date)=datepart(dd, getdate())
and datepart(mm, backup_finish_date)=datepart(mm, getdate())
and datepart(yyyy, backup_finish_date)=datepart(yyyy, getdate())
and name ='SharePoint_DB'
order by database_backup_lsn desc )
begin
ALTER DATABASE --- perform maintenance here
end
return
end
hope this helps,
Huggy Bear
I'd use something closer to this, even though there isn't an index on backup_finish_date. 😉
begin
IF exists( select top 1*
from msdb.dbo.backupset
where
backup_finish_date >= dateadd(dd, datediff(dd, 0, getdate()), 0) and -- Beginning of this day
and backup_finish_date < dateadd(dd, datediff(dd, 0, getdate()) + 1, 0) -- Beginning of next day
and name = 'SharePoint_DB'
order by database_backup_lsn desc )
begin
ALTER DATABASE --- perform maintenance here
end
return
end
April 28, 2009 at 2:40 pm
Check out the whitepaper.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply