understand following concepts of resouce database:

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » understand following concepts of resouce...

14 posts, Page 1 of 2

1 2 »»

understand following concepts of resouce database:

Rate Topic

Display Mode

Topic Options

Author

Message

luckysql.kinda

Posted Saturday, January 23, 2010 10:22 PM

Old Hand

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 11:19 AM
Points: 309, Visits: 645

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

G.R.Prithiviraj Kulasingham

Posted Saturday, January 23, 2010 11:43 PM

SSC-Addicted

Group: General Forum Members
Last Login: Sunday, October 21, 2012 8:59 PM
Points: 421, Visits: 362

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

GilaMonster

Posted Sunday, January 24, 2010 12:49 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:21 AM
Points: 37,686, Visits: 29,943

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

luckysql.kinda

Posted Sunday, January 24, 2010 6:58 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 11:19 AM
Points: 309, Visits: 645

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

G.R.Prithiviraj Kulasingham

Posted Sunday, January 24, 2010 7:22 AM

SSC-Addicted

Group: General Forum Members
Last Login: Sunday, October 21, 2012 8:59 PM
Points: 421, Visits: 362

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

GilaMonster

Posted Sunday, January 24, 2010 7:47 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:21 AM
Points: 37,686, Visits: 29,943

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

luckysql.kinda

Posted Sunday, January 24, 2010 7:49 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 11:19 AM
Points: 309, Visits: 645

Thanks Gila.

Post #852726

G.R.Prithiviraj Kulasingham

Posted Sunday, January 24, 2010 7:56 AM

SSC-Addicted

Group: General Forum Members
Last Login: Sunday, October 21, 2012 8:59 PM
Points: 421, Visits: 362

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

luckysql.kinda

Posted Monday, January 25, 2010 2:58 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 11:19 AM
Points: 309, Visits: 645

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

SQLBalls

Posted Monday, January 25, 2010 7:42 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:21 AM
Points: 385, Visits: 610

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 »

14 posts, Page 1 of 2

1 2 »»

Permissions