SQL build standards
Following are the standards recommended by me after considering best practices and configurations specific to SQL Server Infrastructure
This setting cannot be changed after installation easily and without disruption to service therefore it is absolutely necessary to ask about it before starting. Getting this wrong will require rebuild, reinstall or total discarding of the whole effort.
Backup Retention Policy
The Standard backup retention policy is 2 weeks for both full and log backups unless otherwise specified.
Unless otherwise specified, standard is to perform a full back up every night. For systems requiring point in time recovery log backups are setup to be performed every hour. Both subjected to 2 weeks retention policy.
Server should have access to backup path folder where the
Even with deduplication and compression of the storage management systems like Data Domain that is in place in ASTA with everything considered such as backup window and Iops etc. Keeping Backup compression turned ON, on server level and explicitly or on individual jobs.
As a precaution when setting up for the first time testing backup job is necessary to ensure no errors will occur on scheduled events.
Net 3.5/4 installation
As per standard, there should be 3 different drives unless performance and organization is not much of concern e.g. deployments where SQL Servers role is limited.
Security is based on AD accounts therefore AD accounts created by Infrastructure are required where passwords are saved in central passwords storage.
“Perform Volume Maintenance Operations” set for the main Database engine service account is always set at the build stage for Instant File Initialisation.
For dedicated servers after leaving 2-4 GB for OS operations… 70% should be dedicated to SQL Server for reasonable performance.
Any setting set on this database is inherited by every new database that is locally created on this instance. Therefore things like recovery models, data file growth parameters, log file growth parameters should be set here.
Fixed sizes in case of dedicated disks eliminates potential performance issues in case of growth events.
Having additional data files on Tempdb removes contention issues. Usually it is number of cores but do look into this in detail.
Usually keep this setting turned ON, on all new deployments. So that the execution plans are used efficiently. But depending the on the situation the DBA can decide himself.
Changing from default manual to Automatic is necessary.
Confirmation with Infrastructure that on the new / existing server .mdf and .ldf files and any other directories or files excluded from scanning. This can cause performance issues.
Leaving it to default isn’t too bad but sometime the products require certain settings therefore this is included on the list of things to check.
The standard is 50. This is the recommendation in the industry as opposed to defaults since the processing power of servers has increased.
For troubleshooting especially in case of irresponsive systems.
Local Admin group and “Gbn Inf DBA” Group (diff for Duw and TCS) set as SA.
Any other groups/users given access
Any installation only accounts to be removed?
Full or Simple? Ask RPO and RTO questions
Will affect all new databases created.
Local instance SA user password resetted
For performance and security.
It’s best to start with the latest release provided the third party product (as per official documentation) or the in-house development tools support it.
Bouncing services a couple of times is a recommended test.
Even if it’s not asked for after installation or Windows patching. Just in case.
For advanced settings needed by ISV
Or whatever monitoring tool in use for SQL instances.