Using Model to Change Default Database Settings
During a Twitter conversation today Malik Alamin (Twitter) asked about the Autogrow settings on his databases. The conversation naturally progressed to the question of how you change the default behavior and set all newly created databases to have user defined settings. Brent Ozar (Blog | Twitter) was able to point us in the right direction to find a solution. Modifying the model database.
About the model Database
When a new database is created either by using the CREATE DATABASE statement, that mean via SSMS as well, the model database is used as a template. This means that change made to the model database settings, including tables, permissions, stored procedures, and functions will all transfer over to the newly created database.
According to the TechNet article about the model database some file sizes may look different depending on what version of SQL Server is installed. Also, there are some things that cannot be modified in the model database. Items like CHANGE_TRACKING, ENCRYPTION, and TRUSTWORTY cannot be modified. A full list of restrictions can be found in the TechNet Article.
Example
I need to set the auto grow on my data file to Autogrow by 10% instead of 1 MB as was the default on my instance. I can run the ALTER DATABASE command or use SSMS against the model database
USE [master] GO ALTER DATABASE [model] MODIFY FILE ( NAME = N'modeldev', FILEGROWTH = 10%) GO USE [master] GO ALTER DATABASE [model] MODIFY FILE ( NAME = N'modeldev', FILEGROWTH = 10%) GO
Book Review: Microsoft SQL Server 2008 - Implementation and Maintenance - Self Pace Training Kit
Price
The recommended price on the cover is $69.99. At the time of publishing this article the book could be found for around $45.00 at many bookstores including amazon.com. The book comes with a 180 day trial copy of SQL Server 2008 Enterprise, ebook, practice test, and example scripts and databases.The Good
The book was set up in a very logical progression of technical building blocks. It started out with the very basics of proper setup and configuration. The author then progressed through some database basics and how they related specifically to SQL Server 2008. More advanced topics such as Policy Based Management, Automation, Monitoring and Performance Tuning were then covered. Finally ending with a very in depth look at High Availability solutions offered by SQL Server 2008.
The Bad
Some topics, partitioning in particular, covered basic information but could have been emphasized better as they had a very large presence on the test. That being said, Mike does cover partitioning in an entire chapter of the book but it seemed that this material was more prominent on the test that 1 chapter worth of material.
Overall
I would rate the book at 4.5 out of 5 stars. What made this book really shine was the inclusion of the practice test. After completion of a practice test, users are presented with a reading list based on the areas of the test where the user needs to straighten their skills. This reading list includes material not only in the book but aggregates information from across the web.
I strongly recommend that people interested in taking Exam 70-432 purchase this book.
MS Access ODBC Connetion Timeout Error
The Problem
I manage a Microsoft Dynamics NAV database with over 26,000 tables. When one of our users was trying to create a linked table in Access 2007 he was presented with a ODBC--call failed Timeout expired (#0) error.
The Environment
Physical Server HP DL380 Windows Server 2008 Enterprise SQL Server 2008 SP1 Enterprise 12 GB of RAM Lefthand Networks iSCSI SANThe Solution
Disable the ODBC Timeout value
On the client computer, click Start -> Run -> type "regedit" (without double quotes) and press Enter, locate the registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\ODBCIn Access
Click to highlight QueryTimeout, and then on the Edit menu, click Modify. Change the value to 0 (zero). Close and re-open AccessSQL Server Trace Flags
| 4616 | Makes server-level metadata visible to application roles. In SQL Server, an application role cannot access metadata outside its own database because application roles are not associated with a server-level principal. This is a change of behavior from earlier versions of SQL Server. Setting this global flag disables the new restrictions, and allows for application roles to access server-level metadata. Scope: global only |
Microsoft Dynamics NAV Trace Flag 4616
Connecting to a Navision Database on a new server will generate an error about the trace flag 4616 not being set. Below are the instructions on how to correct this error.
The trace flag is a global flag that is set on the entire SQL server instance through the advanced properties tab in the SQL Server Configuration Manager.
1. Open the SQL Server Configuration Manager
All solutions are provided as-is.
