Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

understand following concepts of resouce database: Expand / Collapse
Author
Message
Posted Saturday, January 23, 2010 10:22 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, May 23, 2013 10:41 PM
Points: 310, Visits: 649
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?
Post #852680
Posted Saturday, January 23, 2010 11:43 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, March 10, 2014 9:05 AM
Points: 421, Visits: 363
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/
Post #852688
Posted Sunday, January 24, 2010 12:49 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:00 PM
Points: 42,301, Visits: 35,356
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 2008, MVP
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

Post #852695
Posted Sunday, January 24, 2010 6:58 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, May 23, 2013 10:41 PM
Points: 310, Visits: 649
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
Post #852721
Posted Sunday, January 24, 2010 7:22 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, March 10, 2014 9:05 AM
Points: 421, Visits: 363
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/
Post #852724
Posted Sunday, January 24, 2010 7:47 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:00 PM
Points: 42,301, Visits: 35,356
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 2008, MVP
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

Post #852725
Posted Sunday, January 24, 2010 7:49 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, May 23, 2013 10:41 PM
Points: 310, Visits: 649
Thanks Gila.
Post #852726
Posted Sunday, January 24, 2010 7:56 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, March 10, 2014 9:05 AM
Points: 421, Visits: 363
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/
Post #852728
Posted Monday, January 25, 2010 2:58 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, May 23, 2013 10:41 PM
Points: 310, Visits: 649
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.
Post #852889
Posted Monday, January 25, 2010 7:42 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 2:26 PM
Points: 386, Visits: 626
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
Post #853005
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse