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 3rd
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 3rd 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:
- Write
a blog post about the topic. Don’t have a blog? Start one!
- Include
the T-SQL Tuesday Logo and link it back to the original invitation post.
- Publish
your blog post Tuesday, July 14, between 00:00 GMT & 23:59 GMT.
- Leave
a reply on the original invite with a link to your blog post (for the
round-up).
- Share
you post with the community! Tweet it out using the #tsql2sday hashtag!
Oh, and ping Adam Mechanic (b|t), the creator
of this booming blog party, if you too would like to host your own T-SQL
Tuesday!
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
