Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


understand following concepts of resouce database:


understand following concepts of resouce database:

Author
Message
luckysql.kinda
luckysql.kinda
Old Hand
Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)

Group: General Forum Members
Points: 314 Visits: 659
I am looking forward to understand following concepts of resouce database:

•If the DBA needs to apply a Service Pack to multiple instances, then the mssqlsystemresource.mdf and mssqlsystemresource.ldf needs to be copied by the DBA to the target instance

-Does this mean that we don't need to apply the service pack to all the instances we need to. We shall apply the service instance to only one server and then if this works fine then we shall replace the mssqlsystemresource.mdf and mssqlsystemresource.ldf files of other instances with this.

•If the DBA needs to rollback the changes performed by the Service Packs then they need to replace the Resource Database related MDF and LDF files with that of the previous backed up version

How do we get the backup file of resource database. Do we need to manually copy mssqlsystemresource.mdf and mssqlsystemresource.ldf and put at some local location?
G.R.Prithiviraj Kulasingham
G.R.Prithiviraj Kulasingham
SSC-Addicted
SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)

Group: General Forum Members
Points: 427 Visits: 367
I am not sure from where did you get those statements, but the data files (and the log files) of internal resource databases should be touched only by SQL Server and PSS engineers. Normally when you apply the service packs they automatically get updated.

Copying those files from one instance to another can lead to failure of an instance at times.

Cheers,
Prithiviraj Kulasingham

http://preethiviraj.blogspot.com/
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47271 Visits: 44392
The resource database, especially in SQL 2008, should be treated as if it were a binary file, a .dll file or the like. It should not be moved, messed with, copied or touched.

You wouldn't move .dll files from one SQL instance to another, not unless you wanted to break the install completely. Same with the resource DB


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


luckysql.kinda
luckysql.kinda
Old Hand
Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)

Group: General Forum Members
Points: 314 Visits: 659
Hi GR and Gilla,

Could you go through following article where I found these statements under the heading of 'Advantages of Resource db'.

http://www.sql-server-performance.com/articles/dba/Importance_of_the_Resource_Database%20_p1.aspx

Could anyone go through them and tell me if I understood the same (I wrote in my first note in the eve)?

-Lucky
G.R.Prithiviraj Kulasingham
G.R.Prithiviraj Kulasingham
SSC-Addicted
SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)

Group: General Forum Members
Points: 427 Visits: 367
Interesting.
I am not sure why those statements were mentioned. I have done enough upgrades, and never went with this approach.


May be you need to send a note to him asking for the reasons.
in my opinion, you can take file based backup of the files, treating them as another file, like a .dll or program file, which will allow you to restore the system, in case of a system (I mean the Windows system) failure, you can restore the system.

Assuming that you need to take some precautions, you need to stop the SQL Server instance and perform a file backup.
I still believe that you should not restore them unless you have a crash.

Cheers,
Prithiviraj Kulasingham

http://preethiviraj.blogspot.com/
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47271 Visits: 44392
luckysql.kinda (1/24/2010)
Could you go through following article where I found these statements under the heading of 'Advantages of Resource db'.

http://www.sql-server-performance.com/articles/dba/Importance_of_the_Resource_Database%20_p1.aspx


I'm going to call that complete garbage and dangerous information.

While service packs do change the resource db, they don't only change the resource DB. They change the SQL exe file, the various dlls and other files within the SQL installation. Hence there is no way to copy or undo a service pack just by replacing the resource DB.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


luckysql.kinda
luckysql.kinda
Old Hand
Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)

Group: General Forum Members
Points: 314 Visits: 659
Thanks Gila.
G.R.Prithiviraj Kulasingham
G.R.Prithiviraj Kulasingham
SSC-Addicted
SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)

Group: General Forum Members
Points: 427 Visits: 367
The only argument anyone can bring is in case of a failure during the middle, (which is equal to a crash I mentioned) how can you resolve it. In that case, you need to replace not only the resource database but also the DLLs, exe and many files in addition to registry entries.

Cheers,
Prithiviraj Kulasingham

http://preethiviraj.blogspot.com/
luckysql.kinda
luckysql.kinda
Old Hand
Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)

Group: General Forum Members
Points: 314 Visits: 659
Yes resource DB gets updated automatically when serivce packs are applied. But along with resource DB there are many other .dll files which get udpated.
SQLBalls
SQLBalls
Old Hand
Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)

Group: General Forum Members
Points: 396 Visits: 630
If you want to see what the service pack is going to touch or replace, if you do a search on the service pack on google, and go to the Microsoft page to download that service pack, there should be a link to a technet article that will tell you exactally what the service pack is fixing, and the DLL's being replaced.

When you look in SSMS you will see the version number of SQL Server(2005 & 2008).

9.0.2047 is SQL 2005 Service Pack 1
9.0.3042 is SQL 2005 Service Pack 2

10.0.2531 is SQL 2008 Service Pack 1

There are a multitude of build numbers out there these are just 3 different examples.

The Dll's the build of the SQL Software is updated and changed just like the other folks said. It is not something you can move from server to server.

You could take Ghosts images or Tape Backups or some other image backup of your server's drives before an upgrade, in order to revert back to your previous installation should you encounter a critical failure after performing a Service Pack upgrade.

But you would only restore those backups if a critical failure occured to your system. DLL's, and system dll's would not be enough, you would also need the registry and possibly COM+ services to be restored in order to revert back to an previous service pack version of SQL.

Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search