Minor one but we’ve had a couple of ISVs encounter this.

 When you upgrade an existing SQL Server 2005 or 2000 database instance, the size of your “MODEL” database increases. For example, 2000 model is under 1MB while 2005 is just over. 2008 is about 2MB. The log file size increases also.

 Why is this worth noting? Well, most users will not be affected by this but if you create applications that run on portable devices whether it’s laptops or handheld devices and part of your application setup involves creating a database, you might care. Also, if your database is deployed in very tightly controlled environments as far as disk space consumption goes, you might want to care.

The problem arises when scripts are used to create these databases and like the good dev/DBA that you are, you include all the options you want including initial file sizes. If your scripts were created for a 2000 or 2005 environment and you set your minimum file size based on what you know the model DB to be in those versions, they will not work in 2005 since the file size of the MODEL database exceeds your definition.

This is not detected by Upgrade Advisor but the number of users affected by this is likely to be VERY low.  Users typically only hardcode a large file sizes (many good reasons to start big) in their CREATE DATABASE statements or leave it to the default (MODEL size). Few actually hardcode a small file size value.

If you do fall into this category, it takes under 30 seconds to fix but if you don’t, your upgraded application’s setup process will fail.

joe yong.

Most admins like to build out a fresh box when upgrading a major application like SQL Server (I do too) and based on what we’ve seen, this is a popular approach. Sure, in-place upgrades are fully supported and they are generally easier to work with since your environment and system objects are upgraded automatically. However, if your server has been running for many years, it may have accumulated more unwanted bits than you’d like or know about. That’s why fresh installs are popular and that means you’ll be upgrading SQL Server side-by-side (aka. migrate).

We will cover this subject in detail over the next weeks but one thing I’d like to share real quick is the Service Accounts used for SQL Server services. First off, “Local System” is no longer permitted. During setup, you will not be allowed to select “Local System” for your SQL Server services. This is a positive move towards a secure by default and in deployment though many users will probably have to modify their automated setup scripts for 2008 (c’mon, admit it; you use local system cos it’s convenient). Local Service and Network Service are still permitted but as mentioned in numerous best practice documents, you really should create a specific local machine account for the SQL Server service or a domain account if it needs to communicate with other servers across the network.

 Now, you can still force the use of whatever account you want (at least as of current CTP build) after installation is complete by changing the properties using the Configuration Manager (I hope nobody still uses services.msc) but that’s probably not a good idea even in dev/test environments. Remember, security is not about production servers only. Many dev/test databases use copies of production data for testing which is a good thing as it provides a “real-world” sample but that also means your dev/test databases are now storing sensitive data that may be subject to the same security rules & regulations as your production server.

 For more information about Windows service accounts and recommended practices, check out http://www.microsoft.com/technet/security/guidance/serversecurity/serviceaccount/default.mspx

joe yong.