Model Database

Question

Model Database

Viewing 15 posts - 46 through 60 (of 62 total)

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

mtassin SSC-Insane Points: 23099 More actions · Answer 1

tilew-948340 (2/22/2012)
dawryn (2/22/2012)
Is there a correct answer at all :hehe:
In theory you can't set or maybe you can, depends on which article you read.
In practice you can set and new database gets option or not, depends on method.
:w00t: All of the above
+1
Question: if you restart an engine SQL server 2008 R2 with its model set to read-only, would the model stays read-only or it return automatically to its default?

I just did that because it seemed like an interesting question.

set the model database to read_only, restarted the engine

model database returned to read_write

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

TomThomson SSC Guru Points: 104773 More actions · Answer 2

Henrico Bekker (2/22/2012)
After the comments, I think its safe to say:
Yes, model can be set to Read_Only
No, it wont affect new databases created?

It depends how they are created.

If they are created by calling create database from an sql script in an SSMS query window, they will not be read only.

If they are created by right-clicking "databases" in SSMS Object Explorer pane and selecting "new database", they will be read only.

If it is tempdb, it will not be created read only on SQLS restart (which is the only way to create tempdb).

If it's created by some other mechanism, I don't know - have only tested the three cases above.

I guess the right answe to the QoTD this time is "Yes and It depends". I got it wrong because I made the mistake of believing QoTD; but I refused to believe the "Yes and Yes" answer because it makes no sense at all for tempdb, so first I validated that. Then I checked dbs newly created dbs by using object explorer were read only, and found they were (not just shown as read only by object explorer - actually can't write top them). Then I checked using SQL, and they aren't (you can wfrite to them).

I think this is an awful mess that MS has handed us, so I'm very grateful for today's question as it has caused me to learn something.

Tom

TomThomson SSC Guru Points: 104773 More actions · Answer 3

Koen Verbeeck (2/22/2012)
Carlo Romagnano (2/22/2012)
Koen Verbeeck (2/22/2012)
Damn you MSDN!
Any documentation that supports the correct answer?
I found this: http://msdn.microsoft.com/en-us/library/ms190249.aspx
It states that:
To change the default values for any one of the database options for all newly created databases, change the appropriate database option in the model database.
In the list of options there is READ_ONLY
Close enough 🙂

Except that it doesn't work - the database won't be read only if created by calling create database in an sql script. Another piece of somewhat misleading BoL documentation.

Tom

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 4

mtassin (2/22/2012)
tilew-948340 (2/22/2012)
Question: if you restart an engine SQL server 2008 R2 with its model set to read-only, would the model stays read-only or it return automatically to its default?
I just did that because it seemed like an interesting question.
set the model database to read_only, restarted the engine
model database returned to read_write

What version and edition because that didn't happen to me:

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86)
Jun 17 2011 00:57:23
Copyright (c) Microsoft Corporation
Developer Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)

TomThomson SSC Guru Points: 104773 More actions · Answer 5

mtassin (2/22/2012)
tilew-948340 (2/22/2012)
dawryn (2/22/2012)
Is there a correct answer at all :hehe:
In theory you can't set or maybe you can, depends on which article you read.
In practice you can set and new database gets option or not, depends on method.
:w00t: All of the above
+1
Question: if you restart an engine SQL server 2008 R2 with its model set to read-only, would the model stays read-only or it return automatically to its default?
I just did that because it seemed like an interesting question.
set the model database to read_only, restarted the engine
model database returned to read_write

Which version of 2008 R2 do you have? In my 32bit 10.50.2500.0 system if I set model model read only it stays that way over an engine restart (assuming a service restart causes a data engine restart).

Tom

mtassin SSC-Insane Points: 23099 More actions · Answer 6

RTM... interesting... 🙂

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64)

Apr 22 2011 19:23:43 Copyright (c) Microsoft Corporation

Developer Edition (64-bit) on Windows NT 6.1

<X64> (Build 7601: Service Pack 1)

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 7

Interesting, and thanks to the Hugo for digging into SMSS. I never expected the tool to override the behavior of the system.

Points awarded back, and the question clarified to say SSMS.

When I thought of this questions, I tested it in SSMS, thinking that the tool would not allow me to set model to read only. When it did, I was curious, would it propagate to new databases and right clicked in SSMS to add a new database, to my surprise, it was read only.

The fact that the tool moves the property from model without just allowing the engine to set the properties seems like a bug to me. It's a confusing issue, and while it shouldn't be a problem, I do not think that that should be the behavior. In terms of whether we should allow model databases to be read only, I lean towards allowing it, but it tends to make no sense. A new database will need something beyond what is in model, so for some period of time, it needs to be read/write, so allowing model to move to read only is a bug.

I have submitted a couple bugs about this:

https://connect.microsoft.com/SQLServer/feedback/details/726113/ssms-not-properly-respecting-model-property-read-only

https://connect.microsoft.com/SQLServer/feedback/details/726115/model-can-be-set-to-read-only

OzYbOi d(-_-)b SSCarpal Tunnel Points: 4054 More actions · Answer 8

Tks Steve - I answered Yes/No also and see that in the time to read all of the comments today you have addressed this.

Tks everyone for the input again today - cheers!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

Very Similar to a question from the past.

http://www.sqlservercentral.com/questions/Administration/70062/

The difference being that, as Hugo described, if you use scripts you will not get new Databases created as Read_Only. If you use the GUI, new databases will end up Read_Only.

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

lbrigham SSCommitted Points: 1567 More actions · Answer 10

So the correct answer isn't "No and No" nor is it "Yes and No" but rather "Yes and It Depends" - I think everyone who got a point should have theirs taken away 🙂

crashdan SSCommitted Points: 1990 More actions · Answer 11

lbrigham (2/22/2012)
So the correct answer isn't "No and No" nor is it "Yes and No" but rather "Yes and It Depends" - I think everyone who got a point should have theirs taken away 🙂

lol