SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by bitbucket / Resource Database / Latest Posts

RE: Resource Database

Neha05 — Thu, 09 Aug 2012 08:49:37 GMT

Nice question!

RE: Resource Database

Britt Cluff — Mon, 04 Jun 2012 19:52:16 GMT

Good question. Thanks for submitting.

RE: Resource Database

Koen Verbeeck — Fri, 01 Jun 2012 01:01:03 GMT

Nice question, thanks.

RE: Resource Database

bitbucket-25253 — Thu, 31 May 2012 16:19:30 GMT

[quote][b]EL Jerry (5/31/2012)[/b][hr][quote][b]bitbucket-25253 (5/31/2012)[/b][hr][quote][b]Cliff Jones (5/31/2012)[/b][hr][quote][b]EL Jerry (5/31/2012)[/b][hr]Thank you for the question, Ron.I also got the right answers by elimination of the first 2 false statements."El" Jerry.[/quote]+1 but like Tom I was wondering about the 32767. Didn't seem like a logical choice for an ID but knew the other 2 were wrong.[/quote]Go back to the very first days of Microsoft when they were working for IBM to develop a relational DB. In those days, Cobol and Fortran were perhaps the 2 most used languages in computing (outside of the basic Assembly language used by IBM to develop their operating system). In those days 18K of memory was huge. Indirect addressing of memory had yet to be developed, with all those restrictions the largest value that could stored in 16 bits was 32767. So, I am only guessing, that someone at MS with a long memory picked it as the value least likely to create a problem. No facts to back up my assumption, a pure guess on my part, take it for what it is worth .... which of course could be nothing. If you are curious enough use google to look up the IBM 900 or 901 model to learn what the "good old days" of computing were.[/quote]Correct me if I'm wrong. Back in the good old days of GW-BASIC the highest line number a program could have was precisely 32767."El" Jerry[/quote]The restriction was the fact that 16 bits (remember 1 bit was reserved - in most systems the high order bit - numbering being from right to left - was reserved to indicate when set that the number was negative, if not set the number was of course positive. So GW-BASIC was limited by memory construction.. the chips themselves .. Boy oh boy this is taking me down memory lane to the days long past. When Hewlett Packard computers .. first model a 2116C, no hard drive, data read in and output using punched paper tape. An IBM 1401 using a RAMAC hard drive ... drive consisted of multiple platters each about 3 foot in diameter, stacked in a vertical housing standing about 6 ft tall, read / write heads (1 set) driven from platter to platter using of all things a steering gear shaft from a Ford motor vehicle. to raise and lower the heads to the required disc platter. Language used know as auto-coder ... but it did have its bright spots .. got chewed out for a printer miss alinement of its print heads by Admiral Rickover the founder of the U.S. nuclear navy and his people developing the first nuc submarine ... The one individual I wish I had the good fortune to meet, but never did was Adm Grace Hopper the founder of COBOL, (an acronym for Common Business Orientated Language ). Well enough of the so called Good Old Days.

RE: Resource Database

Cliff Jones — Thu, 31 May 2012 16:11:04 GMT

[quote][b]george sibbald (5/31/2012)[/b][hr]the maximum number of databases per instance is 32767, therefore the maximum DBID = 32767, I would say thats the reason this number was chosen for the resource database, its as far out of the way of potential user database dbids as you can get.[/quote]If I were using a sequence or an identity to implement new database ID's then 0 would be out of the way. That's what I was thinking was illogical.

RE: Resource Database

george sibbald — Thu, 31 May 2012 15:59:42 GMT

the maximum number of databases per instance is 32767, therefore the maximum DBID = 32767, I would say thats the reason this number was chosen for the resource database, its as far out of the way of potential user database dbids as you can get.

RE: Resource Database

EL Jerry — Thu, 31 May 2012 15:44:39 GMT

[quote][b]bitbucket-25253 (5/31/2012)[/b][hr][quote][b]Cliff Jones (5/31/2012)[/b][hr][quote][b]EL Jerry (5/31/2012)[/b][hr]Thank you for the question, Ron.I also got the right answers by elimination of the first 2 false statements."El" Jerry.[/quote]+1 but like Tom I was wondering about the 32767. Didn't seem like a logical choice for an ID but knew the other 2 were wrong.[/quote]Go back to the very first days of Microsoft when they were working for IBM to develop a relational DB. In those days, Cobol and Fortran were perhaps the 2 most used languages in computing (outside of the basic Assembly language used by IBM to develop their operating system). In those days 18K of memory was huge. Indirect addressing of memory had yet to be developed, with all those restrictions the largest value that could stored in 16 bits was 32767. So, I am only guessing, that someone at MS with a long memory picked it as the value least likely to create a problem. No facts to back up my assumption, a pure guess on my part, take it for what it is worth .... which of course could be nothing. If you are curious enough use google to look up the IBM 900 or 901 model to learn what the "good old days" of computing were.[/quote]Correct me if I'm wrong. Back in the good old days of GW-BASIC the highest line number a program could have was precisely 32767."El" Jerry

RE: Resource Database

bitbucket-25253 — Thu, 31 May 2012 15:32:03 GMT

[quote][b]Cliff Jones (5/31/2012)[/b][hr][quote][b]EL Jerry (5/31/2012)[/b][hr]Thank you for the question, Ron.I also got the right answers by elimination of the first 2 false statements."El" Jerry.[/quote]+1 but like Tom I was wondering about the 32767. Didn't seem like a logical choice for an ID but knew the other 2 were wrong.[/quote]Go back to the very first days of Microsoft when they were working for IBM to develop a relational DB. In those days, Cobol and Fortran were perhaps the 2 most used languages in computing (outside of the basic Assembly language used by IBM to develop their operating system). In those days 18K of memory was huge. Indirect addressing of memory had yet to be developed, with all those restrictions the largest value that could stored in 16 bits was 32767. So, I am only guessing, that someone at MS with a long memory picked it as the value least likely to create a problem. No facts to back up my assumption, a pure guess on my part, take it for what it is worth .... which of course could be nothing. If you are curious enough use google to look up the IBM 900 or 901 model to learn what the "good old days" of computing were.

RE: Resource Database

Cliff Jones — Thu, 31 May 2012 14:45:00 GMT

[quote][b]EL Jerry (5/31/2012)[/b][hr]Thank you for the question, Ron.I also got the right answers by elimination of the first 2 false statements."El" Jerry.[/quote]+1 but like Tom I was wondering about the 32767. Didn't seem like a logical choice for an ID but knew the other 2 were wrong.

RE: Resource Database

EL Jerry — Thu, 31 May 2012 13:25:27 GMT

Thank you for the question, Ron.I also got the right answers by elimination of the first 2 false statements."El" Jerry.

RE: Resource Database

sestell1 — Thu, 31 May 2012 12:44:55 GMT

Wow, great question! Thanks!

RE: Resource Database

KWymore — Thu, 31 May 2012 12:39:57 GMT

Nice question, thanks!

RE: Resource Database

L' Eomot Inversé — Thu, 31 May 2012 10:57:19 GMT

Nice interesting question, thanks.Very surprised that more than half the answers so far got it wrong.I didn't know the database id was always 32767, but didn't need two as it said "select 4" and I knew 2 that weren't true out of the six options provided, so that 32767 had to be one of the four true ones.Agree with Hugo that the use of "persisted" might be a bit misleading.

RE: Resource Database

SQLRNNR — Thu, 31 May 2012 10:26:22 GMT

Thanks Ron

RE: Resource Database

OzYbOi d(-_-)b — Thu, 31 May 2012 09:48:10 GMT

good questions - cheers

RE: Resource Database

Revenant — Thu, 31 May 2012 09:45:39 GMT

Great question, Ron, Thanks!On a second thought, should not a correct answer be worth four points? ;-)

RE: Resource Database

SQLDCH — Thu, 31 May 2012 09:06:58 GMT

Thanks for the question.

RE: Resource Database

rfr.ferrari — Thu, 31 May 2012 06:07:02 GMT

good question!!! and great comments about it!!!thanks !!!

RE: Resource Database

Nakul Vachhrajani — Thu, 31 May 2012 04:19:11 GMT

[quote][b]Hugo Kornelis (5/31/2012)[/b][hr][quote][b]george sibbald (5/31/2012)[/b][hr]I think we should clarify that you should not remove a service pack simply by replacing the resource database with a previous version of it. I'm sure there is more to it than that and service packs should be backed out using add\remove programs.I'm not saying the question suggested that but it seems possible people might infer it.[/quote]A very good addition!A service pack involves changes to the actual executables (sqlservr.exe, DDLs, etc) and corresponding changed to system objects. The install/uninstall program of a service pack will stop the SQL Server service, replace the executables, replace the resource database, and then restart the service. In older (pre-SQL 2005) versions of SQL Server, the installer/uninstaller of a service pack would also replace executables, but replacing the system objects would be done in a much more elaborate way (by running scripts that alter all the affected procedures, views, etc). Simply replacing a database was not possible, since the definitions of system objects then lived in the master database, which also holds the metadata of user databases, plus all logins and other server-level objects.[/quote]In fact, that's what threw me off a bit. Then Hugo kindly reminded me of the fact that a SP = code + system object change, which resolved the confusion.

RE: Resource Database

Hugo Kornelis — Thu, 31 May 2012 04:14:59 GMT

[quote][b]george sibbald (5/31/2012)[/b][hr]I think we should clarify that you should not remove a service pack simply by replacing the resource database with a previous version of it. I'm sure there is more to it than that and service packs should be backed out using add\remove programs.I'm not saying the question suggested that but it seems possible people might infer it.[/quote]A very good addition!A service pack involves changes to the actual executables (sqlservr.exe, DDLs, etc) and corresponding changed to system objects. The install/uninstall program of a service pack will stop the SQL Server service, replace the executables, replace the resource database, and then restart the service. In older (pre-SQL 2005) versions of SQL Server, the installer/uninstaller of a service pack would also replace executables, but replacing the system objects would be done in a much more elaborate way (by running scripts that alter all the affected procedures, views, etc). Simply replacing a database was not possible, since the definitions of system objects then lived in the master database, which also holds the metadata of user databases, plus all logins and other server-level objects.

RE: Resource Database

george sibbald — Thu, 31 May 2012 04:01:39 GMT

I think we should clarify that you should not remove a service pack simply by replacing the resource database with a previous version of it. I'm sure there is more to it than that and service packs should be backed out using add\remove programs.I'm not saying the question suggested that but it seems possible people might infer it.

RE: Resource Database

Hardy21 — Thu, 31 May 2012 02:42:13 GMT

Nice question.. Thanks for submitting...

RE: Resource Database

Stewart "Arturius" Campbell — Thu, 31 May 2012 02:32:10 GMT

Good question, thanks, Ron.

RE: Resource Database

Nakul Vachhrajani — Thu, 31 May 2012 01:37:45 GMT

[quote][b]Hugo Kornelis (5/31/2012)[/b][hr][quote][b]Nakul Vachhrajani (5/30/2012)[/b][hr]It is my understanding that the service packs/upgrades have the potential to modify all databases on the SQL Server instance, therefore, simply replacing the resource database to rollback an SP/update is not enough.The reasoning behind this is that if we take a backup of a database on let's say SQL Server 2008 SP1, we cannot restore it in SQL Server 2008 RTM.Please correct me if wrong.[/quote]I'm not sure if I understand the question. Service pack upgrades (and downgrades in the case you roll back an upgrade) always apply to the entire instance, and affect all databases on the instance. As far as I know, data and log files are never modified as part of a service pack install.If what you say is true (backkups on SP1 can't be loaded on RTM), then the reason is that the code that is executed when you perform a backup or restore statement has been changed as part of the service pack. This code lives partly in the sql server executables (sqlservr.exe and a whole bunch of DLLs), and partly in the defintions of system objects in resourcedb. The latter part is mostly the code for system stored procedures and system views.Does this address your question?[/quote]Thank-you, Hugo. It does address my question completely.Once again, thank-you (as always!)

RE: Resource Database

Hugo Kornelis — Thu, 31 May 2012 01:31:49 GMT

[quote][b]Nakul Vachhrajani (5/30/2012)[/b][hr]It is my understanding that the service packs/upgrades have the potential to modify all databases on the SQL Server instance, therefore, simply replacing the resource database to rollback an SP/update is not enough.The reasoning behind this is that if we take a backup of a database on let's say SQL Server 2008 SP1, we cannot restore it in SQL Server 2008 RTM.Please correct me if wrong.[/quote]I'm not sure if I understand the question. Service pack upgrades (and downgrades in the case you roll back an upgrade) always apply to the entire instance, and affect all databases on the instance. As far as I know, data and log files are never modified as part of a service pack install.If what you say is true (backkups on SP1 can't be loaded on RTM), then the reason is that the code that is executed when you perform a backup or restore statement has been changed as part of the service pack. This code lives partly in the sql server executables (sqlservr.exe and a whole bunch of DLLs), and partly in the defintions of system objects in resourcedb. The latter part is mostly the code for system stored procedures and system views.Does this address your question?

RE: Resource Database

Hugo Kornelis — Thu, 31 May 2012 01:27:26 GMT

Good question! One minor nag (isn't there always one?) - I don't like the wording "System objects such as sys.objects are physically persisted in the Resource database" (and yes, I know that similar wording is used in BOL). This suggests to me that the result of "select * from sys.objects" is persisted (as the same term is used in documentation about indexed views, aka persisted views - where that is exactly what happens).Reality is that metadata about user objects (which is what you see when you query sys.objects) is stored in hidden tables in user databases; sys.objects is not a system table but a system VIEW, and the source code of that views (the query on the hidden actual system tables) is what's stored in the resource database.

RE: Resource Database

Nakul Vachhrajani — Wed, 30 May 2012 21:36:13 GMT

It is my understanding that the service packs/upgrades have the potential to modify all databases on the SQL Server instance, therefore, simply replacing the resource database to rollback an SP/update is not enough.The reasoning behind this is that if we take a backup of a database on let's say SQL Server 2008 SP1, we cannot restore it in SQL Server 2008 RTM.Please correct me if wrong.

Resource Database

bitbucket-25253 — Wed, 30 May 2012 21:23:10 GMT

Comments posted to this topic are about the item [B]Resource Database[/B]