Model DB

  • 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

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

  • 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

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

  • 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

  • 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

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

  • 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

  • This was removed by the editor as SPAM

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

  • 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/

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

  • 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

  • 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

  • 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

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

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