January 23, 2010 at 10:22 pm
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?
January 23, 2010 at 11:43 pm
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/
January 24, 2010 at 12:49 am
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
January 24, 2010 at 6:58 am
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
January 24, 2010 at 7:22 am
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/
January 24, 2010 at 7:47 am
luckysql.kinda (1/24/2010)
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
January 24, 2010 at 7:49 am
Thanks Gila.
January 24, 2010 at 7:56 am
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/
January 25, 2010 at 2:58 am
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.
January 25, 2010 at 7:42 am
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
Channel: https://www.youtube.com/@Tales-from-the-Field
January 25, 2010 at 7:48 am
You can see which files are changed in the kb article for each SP/CU. You will see quite a few files have their sizes and versions updated.
January 25, 2010 at 2:48 pm
Just a quick question, related somewhat, if the mssqlsystemresource.mdf and mssqlsystemresource.ldf cannot be backed up by SQL and I am assuming as SQL has them open, while the service is running, it cannot be backed up to tape, isn't this a point of failure that cannot be easily be remedied whenever the mssqlsystemresource.mdf and mssqlsystemresource.ldf ever become corrupt?
Andrew
January 26, 2010 at 1:35 am
The database serves as read only database and will not be written when SQL Server instance is running.
Like any other file backup, or registry backup, backup of these files too can help you in some extend, but will not help you in service pack upgrades.
This thread is mainly concentrating on the comments of the article.
Cheers,
Prithiviraj Kulasingham
http://preethiviraj.blogspot.com/
January 26, 2010 at 1:50 am
andrewkane17 (1/25/2010)
isn't this a point of failure that cannot be easily be remedied whenever the mssqlsystemresource.mdf and mssqlsystemresource.ldf ever become corrupt?
Yes, but since those only change when service packs/hotfixes are installed, you can just stop SQL as part of the SP installation and take file backups of them.
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
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply