?
Solved

pros and cons of moving master and msdb dbs

Posted on 2006-06-05
2
Medium Priority
?
673 Views
Last Modified: 2008-02-01
Hi All

What are the pro's and con's of moving the master/msdb dbs. I know how to move them but wonder if this is the best option.

A typical server has 3 disk sets (C:, D: and E:).
C: is internal ATA drives, hardware mirror.
D: is external and resides on EMC SAN (fibrechannel, RAID 5 etc).
E: is also on SAN.

All my SQL data files sit on D: and log files on E: What I what to know, should I place the master/msdb data file on D: and the master/msdb log files on E: like my user databases? The tempdb will remain on C: as well as the SQL install files. I figure if C: fails, u can rebuild SQL and attach the unchanged copies of master/msdb and user databases after fresh install using the correct procedures. I'll still backup master/msdb and copy off server and I think the chances of SAN failing are much less than internal ATA drives. I also feel if C: fails with the master/msdb in default locations, you loose both your data and log file in one.

Thanx
Adsony
0
Comment
Question by:Adsony
2 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 750 total points
ID: 16833782
you don't really need to move master and/or msdb databases. the loads on them are so low, that it is not worth investigating.
even if you have some 10-20 jobs defined (which are stored in the msdb database), you can neglect it.
master database will only be used when you add/drop/edit database settings, logins, ie when users login.

to move the master database is simple:
stop the sql server, change the startup settings of the sql server service to use the new location of the master database files, move the file, and start the sql server.

to move msdb database is more tricky, but as said, moving it...

regarding the failure scenario:
usually, master database should be fully recoverable by 1) resinstalling sql server, 2) adding all the logins from backup scripts of the existing logins 3) restoring / attaching all the user databases. msdb database can be restore like a normal user database (should be last one to be restored)

0
 
LVL 27

Accepted Solution

by:
ptjcb earned 750 total points
ID: 16835048
>> should I place the master/msdb data file on D: and the master/msdb log files on E: like my user databases?

No. That is not neccessary. It is a good practice to backup them up on a regular basis.

>>The tempdb will remain on C: as well as the SQL install files. I figure if C: fails, u can rebuild SQL and attach the unchanged copies of master/msdb and user databases after fresh install using the correct procedures.

It depends on what the user load is - the tempdb is your scratch database and almost every connection uses it. I would consider moving the tempdb to a larger drive, if needed, rather than the master/msdb. This link gives some examples about moving master/tempdb/msdb: http://www.databasejournal.com/features/mssql/article.php/3379901 I do not think that you can do an attach/detach on the master since it is the one that keeps that information. If you do this, I would test it several times to be sure that it works. No master, no SQL Server.

0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

850 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