T-SQL Tuesday #68 - Just Say No to Defaults & Stay Healthy
您当前位置： &
[ 所属分类
| 时间 2015 |
作者 红领巾 ]
Happy T-SQL Tuesday, July 07, 2015! @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. (pla-). 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:
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
本文数据库（mssql）相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库
转载请注明本文标题：本站链接：
分享请点击：
1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责；
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性，不作出任何保证或承若；
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
把一件件小事做好,最后就做成了大事。
手机客户端
，专注代码审计及安全周边编程，转载请注明出处：http://www.codesec.net
转载文章如有侵权，请邮件 admin[at]codesec.net}