Model DB

Question

Post reply

Model DB

SQLRNNR

SSC Guru

Points: 281334
More actions
June 9, 2010 at 9:14 pm

#387120

Comments posted to this topic are about the item Model DB
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
June 10, 2010 at 1:38 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 41 total)

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

UMG Developer SSChampion Points: 13482 More actions · Answer 1

Great question, thanks! Though it would be nice for a little explanation behind the results.

Considering that BOL states:

"The following operations cannot be performed on the model database:

•Setting the database or primary filegroup to READ_ONLY."

Which obviously isn't true, you can set it, it just doesn't propagate it to new DBs.

Though the first option for script 4 should be updated to say NewReadOnlyDB instead of TestA. (That is what gave it away for me.)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

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.

Thanks for pointing out the DB name too.

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

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 3

hi without the script i set the model database into read only.then i create the new database but i am not able to create the new table because it is read only it gives an error as one of the option mentioned.but with the script i am able to create new table.

but it shows the model database as read only.

Malleswarareddy
I.T.Analyst
MCITP(70-451)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

malleswarareddy_m (6/9/2010)
hi without the script i set the model database into read only.then i create the new database but i am not able to create the new table because it is read only it gives an error as one of the option mentioned.but with the script i am able to create new table.
but it shows the model database as read only.

When you attempt to create a table, make sure you have switched context to the new database.

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 5

UMG Developer (6/9/2010)
Though the first option for script 4 should be updated to say NewReadOnlyDB instead of TestA.

Steve, could you update that option? It is the answer option with the error message.

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

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 6

I put it newreadonly but still it allows to create a table. which is not possible with setting with tool. (database properties----readonly---true.)

then create new db with wizard. It automatically creates new database with Readonly.

Here with the script newreadonly database will not create with read only.It create database as normal database.It does not inherit the properties from model.

Malleswarareddy
I.T.Analyst
MCITP(70-451)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

malleswarareddy_m (6/9/2010)
I put it newreadonly but still it allows to create a table. which is not possible with setting with tool. (database properties----readonly---true.)
then create new db with wizard. It automatically creates new database with Readonly.
Here with the script newreadonly database will not create with read only.It create database as normal database.It does not inherit the properties from model.

Interesting.

If I understand correctly, you:

1) Set the Model DB to readonly

2) Use the wizard to create a new database

3) Switch context to the new database

4) execute a create table statement - which fails

What version and SP of SQL server? When I do those steps I see the same results as the script (sql 2005 and 2008).

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

michael.kaufmann SSCrazy Points: 2816 More actions · Answer 8

Just verified the finding in SQL Server 2005:

- With MODEL being in READ_ONLY mode, when creating a database using the wizard, it is created read_only. No more changes are possible.

- With MODEL still in READ_ONLY, when creating a database via a SQL script, it is created in read/write mode and tables etc. can be added.

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).

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 9

Interesting find, Jason. I missed my point because I believed Books Online. The only small critisism (sp?) I have on this question is the incorrect use of uppercase ("MODEL" instead of "model"), which will cause errors on a server with a case sensitive collation.

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.

It does of course make sense that the READ_ONLY property won't propagate. After all, creating a database involves writing by itself! And I can also see the value in using READ_ONLY to protect from accidental changes to the model database.

It's just a big shame that the description in Books Online is completely wrong!

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/

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 10

I love QOTD. Whether I get them right or I get them wrong, I pretty much always learn something. Thanks for an interesting question.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

Hugo Kornelis (6/10/2010)
Interesting find, Jason. I missed my point because I believed Books Online. The only small critisism (sp?) I have on this question is the incorrect use of uppercase ("MODEL" instead of "model"), which will cause errors on a server with a case sensitive collation.
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.
It does of course make sense that the READ_ONLY property won't propagate. After all, creating a database involves writing by itself! And I can also see the value in using READ_ONLY to protect from accidental changes to the model database.
It's just a big shame that the description in Books Online is completely wrong!

Thanks Hugo. Also, thanks for correcting the case. I think the BOL description should be updated. It is still possible to interpret the findings of this test as supporting BOL (if you stretch it a bit). Think about it, you can set model to READ_ONLY however you can't enforce that setting on a new DB - which makes it appear that the setting is indeed not in effect on model.

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.

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

stewartc-708166 (6/10/2010)
Good question.
I found the inconsistency with BOL interesting.
Thanks

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

DavidSimpson Hall of Fame Points: 3562 More actions · Answer 13

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