Where to begin?

There are number of concepts that could be discussed and I think the links to Jen’s site will run the gambit on ideas.  My concept is a simple one, but one I think worth pursing as I continue to see a variety of environments–inconsistent SQL Server setups.  There are two facets of consistent setups.  The first being the settings and options you choose when you install SQL Server.  This could be the way tempdbs are setup, your service accounts, naming conventions, and what features you choose for an installation.  Some of this can be changed after you install SQL Server–like default parameters, but some of it can’t be changed–or at least not changed very easily.  Each of these are specific to SQL Server and important; however, I wish to address a second facet of SQL Server setups–the pieces that are specific to your organization and the tools/scripts you use to manage your environment.

Consistent Setup

After SQL Server is installed, this is a great time to run through a few other setup items before letting in the environment loose.  Backup jobs and other maintenance tasks are a given, but here are few others to consider.

• Database Mail setup
• Extended Events traces
• A job to recycle the SQL Logs each day.
• The number of SQL Server logs to keep
• Creating operators for notifications
• Adding Alerts Error Number 823, 824, and 825

Need help starting your script collection?  Don’t forget to script out your options as you create them through the GUI.  This is a great way to start building your configuration nest egg.

Junk Drawer

While there may still be some debate about whether you should store items in the master database, you will need to have a place to stick your stuff.  Remember, we are talking about creating some consistency here and you will need to have a bit of order to make that work.  Growing up, my mom reserved a little pull out drawer in the kitchen for my dad.  Only his stuff was in it and if something was left on the counter, it would wind up in the ‘junk drawer’, but then my dad would always know where to go look for something when he couldn’t find it.  I suggest you do the same with your database environments–create a database dedicated to ‘admin’ stuff.  This stuff isn’t for use in other database; however, I have caught myself using my admin database for a staging table, but that should not be the norm.

This database will be for your scripts you run in your enterprise.

What’s in there?

Examples of scripts I put in my admin database include:

•  backup scripts
• certain common Dynamic Management View I use for performance monitoring
• sp_whoisactive
• System Log files–If I am auditing something or getting backup history, those tables go in the admin database.

Having all the admin utilities in a single database just makes it easier for when other folks come into the environment and you need to collaborate.  Good luck to you and I hope to see you on the SQL Trail.