May 12, 2011 at 2:33 am
A consultant has installed and configured Sharepoint 2010. He has recommended that the system databases incl. Tempdb are moved from C:\ drive to another drive.
I have never been asked to do this before. Has anyone else and what should I look out for?
Madame Artois
May 12, 2011 at 2:39 am
tempdb is re-created each time the instance of SQL Server is started, you do not have to physically move the data and log files. i would do the following:
a - find the logical names
SELECT name, physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO
b- then do the location change
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'F:\SQLLog\templog.ldf');
GO
then stop and start sql service
June 9, 2011 at 4:28 am
Thanks for the help
Madame Artois
June 20, 2011 at 5:46 am
The alter database works for TempDB, model and MSDB (and manually copy the files for model and MSDB to the new drive once SQL is stopped) but we always say dont use that method for master.
Instead when SQL is stopped, use the SQL Server Configuration Manager to change the startup parameters of where it looks for the master database and then copy the files.
Dont know if you did successfully manage to alter master and move the files ok? If so let me know and I will try it out.
June 21, 2011 at 1:47 am
I haven't tried to move the databases yet. I have asked the Development team to ask the consultant why he wants these databases moved. The location he wanted them moved to has the same amount of space as their current location so I don't see the reason.
The consultant has pread the databases over a number of drives on the server. Does Sharepoint 2010 need its databases in separate locations?
Madame Artois
June 21, 2011 at 2:04 am
If you really wanted to you could put every database mdf/ndf/ldf on the same drive but I wouldnt as you might as well just say can I have my P45 now please.
It is generally best practice to seperate all databases onto drives which can cope with the load and/or capacity and/or redundancy to which that database or databases need to operate correctly.
We have a standard database server template which all servers must comply to before we as DBA's will even touch the server and install SQL.
OS Drive at 40GB
Page File Drive at RAM * 1.5 rounded to the nearest 10GB, so for 6GB of RAM you want a page file of 9GB so a page file drive of 10GB
System Database Data Drive at 20GB (for master/model/msdb mdf's only)
System Database Log Drive at 10GB (for master/model/msdb ldf's only)
TempDB Database Data Drive at 40GB (for tempdb mdf only)
TempDB Database Log Drive at 20GB (for tempdb ldf only)
User Database Data Drive at ???GB (for all user database mdf's and ndf's)
User Database Log Drive at ???GB (for all user database ldf's)
Backup Drive at ???GB (for all BAK/TRN files)
All of these drives must be RAID 1+0, be in a SAN RAID set of atleast 18 disks and have a RAID group IO minimum limit of 2700 IOPS and each disk must be in a different RAID set.
The reason for this is that if you loose one drive you can still recover your database / server, if for example all your databases where on the D drive and the D drive got corrupted you would have no functioning server, but if it was just for example the user database log drive, you can recover quite quickly.
Obviosuly spliting out doesnt mean you dont do backups, also make sure that everything including all system databases are backed up, its the old saving backups are worthless, but restores are priceless.
June 21, 2011 at 7:37 am
Still trying to get hold of the consultant but will update as soon as I have got hold of the little sweetheart!
Madame Artois
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply