February 22, 2012 at 5:37 am
Did a little research and ran across this previous QotD
http://www.sqlservercentral.com/questions/Model+Database/69457/
"With SQL Server 2005 or 2008. Wnen (sic) working with the Model database what can you NOT do? Select all that apply."
In the subsequent discussion I located this:
http://www.sqlservercentral.com/Forums/FindPost904259.aspx
As others have also noted
http://technet.microsoft.com/en-us/library/ms186388%28v=sql.105%29.aspx#2
Hence answer of Yes/No...
So much for searching :hehe:
February 22, 2012 at 6:03 am
m mcdonald (2/22/2012)
Did a little research and ran across this previous QotDhttp://www.sqlservercentral.com/questions/Model+Database/69457/
"With SQL Server 2005 or 2008. Wnen (sic) working with the Model database what can you NOT do? Select all that apply."
...
Except for the fact that this question is about 2008 R2 which apparently works differently than 2005 or 2008.
February 22, 2012 at 6:34 am
cengland0 (2/22/2012)
Except for the fact that this question is about 2008 R2 which apparently works differently than 2005 or 2008.
Tried it on 2005 and 2008 as well and it worked just the same as described in previous posts.
Edit: SSMS propagates read-only, T-SQL does not
February 22, 2012 at 6:35 am
If only every one would read the question carefully it states
In SQL Server 2008 R2, can I set the model database to read only? Will new databases created default to read only status?
Please note that it does not say how to set the model db to read only.
And we should all know that you could use SSMS (which apparently does not set the new DB to read only) or we could use a T-SQL statement which does configure the Model DB as read only, and then when we create a new DB it is a read only DB
February 22, 2012 at 6:41 am
Seems like a lot of people are learning something new.
Incorrect answers: 70% (234)
Total attempts: 332
February 22, 2012 at 6:48 am
bitbucket-25253 (2/22/2012)
Please note that it does not say how to set the model db to read only.
Is it meant:
a) Will all new databases …
b) Will any of new databases …
This gives different answer :doze:
Does anybody know where to find default for this kind of questions? (no answer needed)
February 22, 2012 at 6:54 am
bitbucket-25253 (2/22/2012)
Seems like a lot of people are learning something new.
Yes, but learning to mistrust the documentation is not something that I particularly enjoy as I rarely have the time to learn by experiment except where it directly concerns an actual work-related issue. And, frankly, there are quite a few experiments that I've seen here, either in discussions like this one or in articles, that I don't have the skill set to have conceived and/or implemented. Fortunately, there are people here that do have the skills and are willing to share their results. Hats off to you guys!:w00t:
BTW, just for the record, I AM happy that the truth is out there but I do hope that MS pays attention to things like this and updates the documentation.
February 22, 2012 at 7:19 am
bitbucket-25253 (2/22/2012)
If only every one would read the question carefully it statesIn SQL Server 2008 R2, can I set the model database to read only? Will new databases created default to read only status?
Please note that it does not say how to set the model db to read only.
And we should all know that you could use SSMS (which apparently does not set the new DB to read only) or we could use a T-SQL statement which does configure the Model DB as read only, and then when we create a new DB it is a read only DB
Either I misunderstand you, or you misunderstand the situation.
1. You can set model to read-only. It does not matter if you use the SSMS GUI or a T-SQL statement for this.
2. If model is read-only, newly created databases still default to read-write. However, the SSMS GUI "cheats" by sending an ALTER DATABASE to change this to read-only after the DB is created, giving you the impression that the DB is created as read-only. But in fact, it was still created as read-write and then changed to read-only immediately after that.
bitbucket-25253 (2/22/2012)
Seems like a lot of people are learning something new.Correct answers: 30% (98)Incorrect answers: 70% (234)
Total attempts: 332
I'd rather say that almost half the people already knew the actual correct answer:
February 22, 2012 at 7:24 am
I selected "NO and NO" 🙁
NoteToSelf: When it says R2 then refer only the R2-BOL. Referring 2005-BOL will not help.
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
February 22, 2012 at 7:29 am
Michael Riemer (2/22/2012)
I guess the answer really should be it depends! and the lesson (or in this case points) should be in the form of knowledge. I have definitely learnt something today, which I guess is the point of QOTD! Thanks
+ 1
February 22, 2012 at 7:43 am
Hugo Kornelis (2/22/2012)
bitbucket-25253 (2/22/2012)
Seems like a lot of people are learning something new.Correct answers: 30% (98)Incorrect answers: 70% (234)
Total attempts: 332
I'd rather say that almost half the people already knew the actual correct answer:
Yes and No: 47%
Except a certain percentage of those are people who tested to get the answer and another percentage are just guesses. Either way, a lot of people are learning about this behavior.
February 22, 2012 at 7:53 am
Hugo Kornelis (2/22/2012)
I'd rather say that almost half the people already knew the actual correct answer:Yes and No: 47%
+1
From recollecting very likely the same discussion as you, I looked for it depends as answering option and then went straight to Yes and No--only to find that Steve chose to consider this incorrect ;-).
Regards,
Michael
February 22, 2012 at 7:55 am
Hugo Kornelis Posted Today @ 9:19 AM
2. If model is read-only, newly created databases still default to read-write. However, the SSMS GUI "cheats" by sending an ALTER DATABASE to change this to read-only after the DB is created, giving you the impression that the DB is created as read-only. But in fact, it was still created as read-write and then changed to read-only immediately after that.
Are you saying that if one uses T-SQL commands to alter the Model DB and then a T-SQL statement to create a new DB the same action of create - then immediately change takes place?
February 22, 2012 at 8:16 am
bitbucket-25253 (2/22/2012)
Are you saying that if one uses T-SQL commands to alter the Model DB and then a T-SQL statement to create a new DB the same action of create - then immediately change takes place?
I am saying that:
(1) if you use the T-SQL CREATE DATABASE statement to create a database it will be executed as a single unit of work. After the statement finishes, you have a database that is in read-write mode, even if model happens to be read-only;
(2) if you use the SSMS GUI to create a new database, it will execute a series of statements; CREATE DATABASE will be one of the first. If model is read-only and you didn't change the settings in the GUI, SSMS will later execute an ALTER DATABASE statement to set the database to read-write. And since these statements are not in a transaction, it is even possible to end up with a read-write database, if the server halts or the connection is list somewhere between the CREATE DATABASE and the ALTER DATABASE that sets it to read-write.
All of this is totally unrelated to when the model database was set to read-only. As you may have guessed from one of my earlier replies in this topic, I think you could make a case for changing model to read-only and leaving it like that after changing whatever settings you want to change to have new databases comply to company policy.
February 22, 2012 at 8:35 am
The answer seems to be Yes and no. It is documented and I tried it. That's the correct answer and should receive the points
Viewing 15 posts - 31 through 45 (of 62 total)
You must be logged in to reply to this topic. Login to reply