Moving System Databases for Sharepoint 2010

  • 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

  • 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

  • Thanks for the help

    Madame Artois

  • 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.

  • 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

  • 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.

  • 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