Model DB

Question

Model DB

Viewing 15 posts - 16 through 30 (of 41 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

DavidSimpson (6/10/2010)
Good question which I missed because I thought that the model db could not be set to Read_only... very interesting. A good start to the day when you learn something new. As Hugo points out, though, it makes sense because there is value to set the model to read only to protect it from accidental changes once you have it set like you want.
David

Thanks

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

johnf_amic Mr or Mrs. 500 Points: 532 More actions · Answer 2

I got 3 out of 4 right. Don't I get partial credit? 😀

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

johnf_amic (6/10/2010)
I got 3 out of 4 right. Don't I get partial credit? 😀

You did. You got .75 pts and then it is rounded down for your final score. 😉

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

paul.knibbs SSCoach Points: 15320 More actions · Answer 4

Mark me down as another believer in what Books Online said...I assumed that when it said the model database couldn't be set READ_ONLY that SQL would, you know, actually give you some sort of error when you tried it!

Having said that, it's kind of an odd restriction anyway. The contents of the model database are copied to create a new database--what part of that would be broken if the model database actually WERE read only?

Michael Poppers SSCrazy Points: 2149 More actions · Answer 5

CirquedeSQLeil (6/9/2010)
Thanks. True - BOL does state that the MODEL DB cannot be set to ReadOnly, that's why I found this to be an interesting behavior.

Agreed. Good QotD, Jason!

Thanks for pointing out the DB name too.

The radiogroup choice still says "TestA" -- if the proper database name had been listed, I would have chosen it and, by virtue of then receiving FLOOR(0.75) credit, would have missed my QotD point! 🙂

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 6

CirquedeSQLeil (6/10/2010)
However, I will have to investigate a bit further due to the behavior of the wizard. I need to see if I can duplicate the results of michael.kaufmann.

That's actually quite simple.

When you use SSMS's graphical interfact to create a database, SQL Server copies various settings from the model database in the dialog window. If you start profiler trace, you can see the queries fired from SSMS at the model DB before the window opens.

You can then change the options or leave them as they are. When you clcik OK to create the DB, SSMS will generate and execute a series of ALTER DATABASE statements to explicitly set each of the options. This is not limited to only the options that need to be changed; it simply explicitly sets all the options. This too can be checked from profiler, or you can choose to generate the script instead of executing it.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Trey Staker SSCarpal Tunnel Points: 4796 More actions · Answer 7

Good question Jason. I missed my point on this one. I selected the correct answers then decided to re-type all of your sample code into ssms but I for got the final use database newReadOnlyDB and stayed in the context of model. I went and re-selected the options and bam! missed got the question wrong.

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 8

The answer has been corrected to show the correct database name. Good catch, and you can submit changes to BOL through Connect.microsoft.com

UMG Developer SSChampion Points: 13482 More actions · Answer 9

Hugo Kornelis (6/10/2010)
UMG's comment is not entirely correct. SQL Server does nog ignore the READ_ONLY property in model; it just does not propagate it to the new database. Try createing a table in model after setting it to READ_ONLY; you'll see that this fails.

True, I have corrected my post, thanks!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

Michael Poppers (6/10/2010)
CirquedeSQLeil (6/9/2010)
Thanks. True - BOL does state that the MODEL DB cannot be set to ReadOnly, that's why I found this to be an interesting behavior.
Agreed. Good QotD, Jason!
Thanks for pointing out the DB name too.
The radiogroup choice still says "TestA" -- if the proper database name had been listed, I would have chosen it and, by virtue of then receiving FLOOR(0.75) credit, would have missed my QotD point! 🙂

Thanks - working on correcting that choice.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

Hugo Kornelis (6/10/2010)
CirquedeSQLeil (6/10/2010)
However, I will have to investigate a bit further due to the behavior of the wizard. I need to see if I can duplicate the results of michael.kaufmann.
That's actually quite simple.
When you use SSMS's graphical interfact to create a database, SQL Server copies various settings from the model database in the dialog window. If you start profiler trace, you can see the queries fired from SSMS at the model DB before the window opens.
You can then change the options or leave them as they are. When you clcik OK to create the DB, SSMS will generate and execute a series of ALTER DATABASE statements to explicitly set each of the options. This is not limited to only the options that need to be changed; it simply explicitly sets all the options. This too can be checked from profiler, or you can choose to generate the script instead of executing it.

Makes sense. Thanks for doing that bit of leg-work.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

Trey Staker (6/10/2010)
Good question Jason. I missed my point on this one. I selected the correct answers then decided to re-type all of your sample code into ssms but I for got the final use database newReadOnlyDB and stayed in the context of model. I went and re-selected the options and bam! missed got the question wrong.

Thanks Trey.

I hate it when that happens (miss one little thing in the script and it changes the results, then you change your answer).

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

Steve Jones - Editor (6/10/2010)
The answer has been corrected to show the correct database name. Good catch, and you can submit changes to BOL through Connect.microsoft.com

Thanks Steve.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Festeron SSC Eights! Points: 927 More actions · Answer 14

READ_ONLY on model is not copied to new databases? Good to know.

Being able to put READ_ONLY on model so that your changes are not modified? Darn useful.

However, you'd think that both the contents and the properties of model would be moved over [yeah, that's why I got it wrong]. So here's a supplemental:

Are there any other properties of model that are ignored during database creation?

ANSI nulls ?

Auto shrink ?

Restricted Access?

nelsonj-902869 SSCarpal Tunnel Points: 4636 More actions · Answer 15

michael.kaufmann (6/10/2010)
Just verified the finding in SQL Server 2005:
Very interesting behavior--especially given the fact that BOL states one cannot set the MODEL database to READ_ONLY (that's why I did not get my point).

I found the same statement in BOL....and missed my point as well. I think SQLCentral should give those of us who answered script one as producing an error should get 15 extra points:w00t:.....just for finding the error in BOL!!!:-D

....my 2 cents worth......