Modify Model Database
Every once in a while I get an Email from Feodor Georgiev highlighting recent blogs he has posted on the web. Last week I received a new Email pointing out that you cannot change the file groups in the model database. His interest in making this change was based on a previous study he performed for scenarios restoring a database incrementally for critical data.
His article determined that breaking up user data into different file groups other than the default/primary filegroup allowed for a faster restoration of the database as a whole, while incrementally backing up the data according to priority as well. You probably need to read the article to follow that last sentence.
As a result of his study, Feodor thought it would be a good practice to have all databases created with a new filegroup for data, having only metadata stored on the primary file group. In an attempt to make this a standard, he tried to add a new filegroup to his Model database. Because all new databases are created by making a clone of the Model database, this would result in any new database automatically having a new filegroup intended for user data.
Feodor found that an attempt to add a new filegroup to the Model database resulted in an SQL Error. Microsoft has chosen to not allow this kind of modification to the Model database. Feodor asks in his Email, “Is there a particular reason why the model database does not support custom filegroups?“ I didn’t know this was the case, so I tried to add a file group myself. I received the error message, “User-defined filegroups are not allowed on “Model”. (Microsoft SQL Server, Error : 1826).”
I have no idea why Microsoft would disallow this practice. You can change the default data file and log file size, autogrow policy, etc. But, you cannot create a new filegroup or file object. Perhaps someone closer to the Microsoft team could explain the reasoning behind this functionality.
In the meantime, enjoy the lessons learned from Feodor’s study on Resilient Databases.
Do you have an answer for Feodor or perhaps a question of your own? Share it with us by writing to btaylor@sswug.org.
PowerShell Comment
Jack writes:
I enjoyed your newsletter on Powershell. I use Powershell extensively in managing large amounts of SQL Server Instances . Check here for some of the Powershell scripts I use . It simplifies the standardisation of servers, gathering data , and creating reports. Even though DBAs use DIY solutions with Powershell to monitor SQL Server, Powershell doesn’t offer built in processes for monitoring. Working on the assumption monitoring is a critical aspect of managing database servers – I rely on integrated monitoring solutions for dealing in fatal situations.
One other weakness of Powershell is it’s lack of integration with Linux. In a mixed environment – for example DB2 on Linux – a smoother way of connecting to Linux would be useful .
Cheers,
Ben
$$SWYNK$$
Featured White Paper(s)
Top 10 Tips for Optimizing SQL Server Performance
read more)