This homework series really has two purposes. It’s a primer for beginners. These are tasks I think most if not all database developers and DBAs should know how to do. And giving it at least one shot means you’ll be more comfortable when the task inevitably comes up. On the other hand, there are also a number of tasks that you may not need often, but you absolutely want to try them at least once before doing them even in a test environment. This one is the second type.
In a stable environment, you should be changing the instance configurations rarely if ever. That said, it does happen. And for new environments or even when you upgrade your environment you may need to change something. Heck, something as simple as adding a new application to an existing instance may require changes.
So let’s give this a shot. Remember you want to be making these changes in a private sandbox, or your home lab.
Change the following settings (read the rest of the blog before making any changes):
- Minimum and maximum memory
- Maximum degree of parallelism (MAXDOP)
- Minimum threshold for parallelism
- Default fill factor
- Cross database ownership chaining
- Enable CLR
- Remote Admin Connections
- Turn on database mail
- Enable xp_cmdshell
Things to think about here:
- Some of these are considered advanced settings.
- Some will require a reboot, some won’t.
- What are the defaults?
- How do I tell what the current settings are?
- What are some of the other options?
- And of course what does each of these do.
Now, before you make any change you always want to make a note of what the previous setting was. (Always have a backout plan.) And of course you always want to know exactly what the setting you are changing is going to do. For example the minimum memory setting does not mean that SQL is going to start up with that amount of memory.
And as always, none of this is particularly hard, but just because it’s not hard doesn’t mean that you’ve done it before. There is absolutely nothing wrong if you haven’t. No time like the present right?
