January 13, 2009 at 7:19 am
From: SQL Server 2008 Books Online (August 2008)
In SQL Server 2008, the default location of the Resource database is \Binn\. The database can be moved; however, we recommend against moving it for two reasons:
January 19, 2009 at 5:36 am
All things are possible, but not all things are beneficial
Paul, early ADs
It may be possible to move the system databases, but for most of them it is not beneficial. Experience with SQL Server 2005 Service Packs is there is a risk of failure if master or mssqlsystemresource are moved. Because Microsoft have a history of problems in this area, IMHO we have to assume a similar risk in SQL Server 2008.
My advice is to always leave master and mssqlsystemresource in their original locations, in order to minimise the risk of problems when applyinmg fixes.
There is also no integrity or performance advantage in moving model. It just gives you work. Therefore my advice is to leave model in its original location.
The msdb database often becomes very important, particularly where a site makes extensive use of SSIS or DTS. Whereever this is the case, msdb should be treated the same as any user database, and moved to the most appropriate location. In order to have a consistant configuration on all servers, it is worth considering a site standard that says always move msdb.
The tempdb database very often needs to be isolated from other databases, in order to maximise SQL Server performance. A site standard should always include moving tempdb to the best location.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
January 19, 2009 at 7:46 am
EdVassie (1/19/2009)
The msdb database often becomes very important, particularly where a site makes extensive use of SSIS or DTS. Whereever this is the case, msdb should be treated the same as any user database, and moved to the most appropriate location. In order to have a consistant configuration on all servers, it is worth considering a site standard that says always move msdb.
quote]
IMHO, the important phrase here for msdb is 'worth considering'. I think we should emphasise this is something you might do, but would most likely not.
I think it would be exceptional circumstances where the msdb was so heavily hit you had to treat it like a user database, (and indicative you were using it for things it was not intended). And even with frequent backups, and DTS\SSIS log activity would not be such that a noticeable performance gain would be acheived by separating the logs and data of msdb, even if that sort of performance was required with msdb.
Even moving just msdb is going to complicate procedures you normally want to acheive in a hurry, like master rebuilds or a reinstall, so I would stick with the KISS principal.
....er, mucked up quote somehow, for clarity first para is Eds
---------------------------------------------------------------------
February 5, 2009 at 12:43 am
Hi All,
Now we can move resource database also. Please refer the link below in MSDN.
http://msdn.microsoft.com/en-us/library/ms345408(SQL.90).aspx
thanks
February 5, 2009 at 2:38 am
Omprakash K Deshpande (2/5/2009)
Hi All,Now we can move resource database also. Please refer the link below in MSDN.
http://msdn.microsoft.com/en-us/library/ms345408(SQL.90).aspx
thanks
that tells you HOW to do it, but we still recommend you don't, as it causes problems with upgrades later on.
---------------------------------------------------------------------
February 5, 2009 at 4:22 am
Part of being a good DBA is risk asessment. We know that Microsoft have hardcoded assumptions about where the resource db is located within the SP2 install, so we have to assess the risks they may do something similar in future maintenance.
A good DBA will therefore assess the risks that future maintenance may fail if they move various system databases against a desire to have all databases managed in the same way (e.g. in a similar folder structure).
I am someone who saw no problems in moving system database location in SQL 2000 and did move them. I am someone who has hit problems after moving system databases in SQL 2005, and now advise against moving them on the basis that the risk outweighs the benefits.
IMHO in most installations thare are no performancve or integrity benefits gained in moving the system databases. It just adds work and risk. I have discussed this with a few SQL MVPs and most agree that best practice on SQL 2005 and SQL 2008 has moved to say 'leave master and mssqlsystemresource in their original locations'.
Some installations may install SQL to a local disk while other disks are held on the SAN, and for these there may be some integrity benefit in moving the system DBs. There may be a better case made that the install disk should also be on the SAN, to avoid potential unplanned downtime due to maintenance that failed because the system DBs were moved. Each site needs to look at the risks and their own costs and make the decision that is right for them.
BTW In SQL Server 2008, the resource database is held in the \Binn folder, not with the rest of the system databases.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 5, 2009 at 4:45 am
what Ed just said, seconded.
with the addition of place the system dbs where you want them as part of the install process, then you don't have to worry about the work and risk of moving them afterwards.
---------------------------------------------------------------------
February 27, 2009 at 7:59 am
The answer marked as "correct" for this question is wrong. Actually one can move the Resource database, in a similar way with moving the master database ("start the instance of SQL Server in master-only recovery mode" = "NET START MSSQL$instancename /f /T3608"). this issue is covered by the SQL 2008 BOL, I answered accordingly, but still the page considered my answer to be wrong...
Viewing 8 posts - 31 through 37 (of 37 total)
You must be logged in to reply to this topic. Login to reply