Happy T-SQL Tuesday,

July 14, 2015! (I have been time traveling, and now back on this continuum 😉  @SQLBek, Andy Yun, has launched his own “Just Say No” campaign,

and that is in the form of this month’s terrific T-SQL Tuesday topic, #68, “Just

Say No To Defaults”  (To view the

original invite, click on the previous hyperlink) So, what does he mean?

Perhaps he had Greece’s economic chaos and vote in mind, featured in the news, Greece

is about to default on its debt? 

Nah, that’s a serious crisis brewing over there, but not as much a

crisis for a DBA taking on a SQL Server installed, with all default settings!

Click, Next, Next, Finish, might be ok for some 3^rd

party vendors looking to install SQL Server, as the app’s backend, and get out

quickly.  This is known as “Set It, and

Forget It”, but it’s something the DBA will remember quite well when

performance begins to suffer. As discussed in my new book Healthy SQL – A Comprehensive Guide to

Healthy SQL Server Performance, in a situation of “Set it and forget it,”

as the application usage increases, as new records are continuously inserted,

updated, and deleted, and as users place demand on the application, response

times will eventually slow down, as will the overall performance of the SQL

Server database back end. Indexes become fragmented, excessive page splitting

occurs, and backups are not properly configured.  This is almost always due to accepting

installation defaults.

Key to making sure your SQL Servers are properly

configured from the outset is creating and using an installation checklist. Not

only will this checklist be used for your own DBA deployment endeavors, but one

that should be completed by the application owner as well, so you know which

components to install, what the instance name should be, special collation

requirements, ports, etc.

 In fact, Andy asks us the following,

<< Do you have a checklist

of “always change” settings?
    

Do you have a preferred method for mass-deploying your changes?
   

Do you have a specific default setting you ALWAYS change? >> 

 And the answer to all the above should be, and for me, is

YES! All DBAs should have a checklist of “always change” settings, and key default

settings changed.

For example, don’t accept the default installation paths

for SQL Server data, log and temp files! The Disk Layout for SQL Server should

ideally separate Data, Log, tempdb, Binaries, and SQL Backups to separate

physical disks. We don’t want to C: this drive for anything but the binaries.

(play on words intended ;-). Please create additional tempdb files accordingly,

that are sized way larger than the 8mb default. In most cases, enable the SQL

Server configuration setting ‘Optimize For AdHoc Workloads’ for better query

performance – it’s the closest SQL Server comes to having a magic turbo button.  🙂 Always

set the SQL Server configuration options Maximum Server Memory and Minimum

Server Memory. Even consider when creating indexes to modify the default fill

factor (0 or 100) according to whether your SQL Server is read or write performance

heavy. There are certainly many more, and my book discusses them. Simple best

practices will keep our SQL Servers healthy!

Furthermore, DBAs that will be deploying multiple SQL

Servers throughout your company, should consider a way to deploy changes to

multiple SQL Servers. In fact, you should have a standard build that you can

deploy as well. You can use a silent unattended installations (using the /Q or

/QS parameters) with the /IAcceptSQLServerLicenseTerms parameter to accept and get

past the licensing terms. Better yet you can create

a silent unintended install using the configuration file and a batch file. You

would step through the SQL Server installation program, which will gather your

preferred configuration options, and stop short of the final installation step,

to capture the configuration.ini file and path. 

For a complete step-by-step demo on how to do this, click on the

aforementioned hyperlink.

 There are many ways to rapidly deploy code to multiple

databases and multiple SQL Servers. 

Natively, as mentioned in HealthySQL

the book, you can also use central management

server (CMS), on versions SQL Server 2008 and newer, to run T-SQL scripts and

queries against multiple instances of SQL Server simultaneously by creating

server groups that contain the connection information for one or more instances

of SQL Server. You designate one instance to manage all the others and serve as

the CMS. You can create a CMS in SSMS by selecting View > Registered Servers > Central Management Servers. Right-click and select Register

Central Management Server to register an instance of the CMS.

Or you can use 3^rd party software

solutions, such as Red Gate’s

Multi-Script, or ApexSQL Build. Apex also writes

about deploying multiple sql scripts to multiple SQL Servers using a batch

file, as well as their ApexSQL build product.

So I heartily agree with Mr. Yun, just say no to

defaults!  Thank you, Andy, for hosting

this month’s INSTALLment of T-SQL Tuesday! If you want to participate, use

these DEAFAULT settings:

1. Write

   a blog post about the topic. Don’t have a blog? Start one!
   

2. Include

   the T-SQL Tuesday Logo and link it back to the original invitation post.

3. Publish

   your blog post Tuesday, July 14, between 00:00 GMT & 23:59 GMT.

4. Leave

   a reply on the original invite with a link to your blog post (for the

   round-up).

5. Share

   you post with the community! Tweet it out using the #tsql2sday hashtag!

  For all things SQL, news, events, jobs, info, and other

fun tweets, follow me on twitter @Pearlknows

  And for a comprehensive guide to healthy SQL Server

performance, how to conduct a health check, and efficiently manage your SQL

Server environment, pick up a copy of my    

  book on Amazon: http://bit.ly/HealthySQLonAmazon or

direct from Apress: http://bit.ly/orderHealthySQLnow

and join the #HealthySQL campaign to keep your SQL Servers 

  healthy