Rules to Better SQL Server Administration

Hold on a second! How would you like to view this content?
Just the title! A brief blurb! Gimme everything!

  1. Do you know to never give SQL Server all your RAM?

    ​Microsoft SQL Server is made to use all the available memory in a server for itself. It will eat all the memory you throw at it. This can be a problem because your other applications may suffer performance problems as all the system memory is gone. To limit this behaviour you can limit the maximum amount of memory SQL is allowed to use.
    1. Open SQL Server Management Studio
      ​​Figure: SQL Server Management Studio - Login Screen
    2. Right click on the server name and select “Properties”
      Figure: SQL Database options and properties menu
    3. Select the “Memory” tab
      Figure: Server Properties showing the ridiculously large value set for the maximum server memory​
    4. You will see that the default number is HUGE. Change this to something more realistic. Let SQL use half of the memory in your server.Leave about 1024MB headroom. For example, if you server has 4GB of RAM, give the SQL server a Maximum server memory of 2048mb.
      Figure: Maximum server memory settings in server properties
    This will prevent SQL from “owning” all of the RAM on your system,leaving some memory left for your other applications to use.
    Read more about Do you know to never give SQL Server all your RAM?

  2. Do you keep SharePoint databases in a separate SQL instance?

    Because SharePoint server will create quite a few databases, it’s easier to manage them in a separate SQL instance rather than mixing it with other system’s databases:

    ​​​sharepoint-database-bad.png
    Bad example - mixed with other systems' database
    ​​​sharepoint-database-good.png
    Good example - SharePoint related databases are in a separate SQL instance from other systems' databases​
    Read more about Do you keep SharePoint databases in a separate SQL instance?

  3. Do you turn off auto-update on your servers?

    It is not a good idea to have Windows Update automatically updating your servers.  There are a few reasons.

    1. The hotfix could bring down a production environment. (This issue previously happened)
    2. In fact, even in a development environment, this could be hours of lost work as the development team struggles to understand why only some of the developers' servers magically and mysteriously broke overnight.
    3. Windows Update could restart your server, or put your server in a state where it requires restarting - preventing any urgent MSI installs without bringing down the server.

    Windows Update remains the best thing for end-users to protect their systems.  But in a server, especially a production server environment - Windows Update patches are just like any new versions of the software that's built internally.  It should be tested and then deployed in a controlled manner.

    So recommendations for managing updates are as follows:

    1. Use WSUS to approve/deny updates for your servers.
    2. Update Staging/Development servers first to see if any issues arise from the updates.
    3. Roll these updates out to Production once confident there are no issues​.
    4. Windows Updates may be critical and should be kept relatively up to date.
    5. Do all of this on a schedule - have an email sent to your SysAdmins to remind them to review and reboot needed machines:
    WSUSReport.png
    Good Example: Scheduled email showing clear action points and WSUS stats

    ​​Related Rules

    Read more about Do you turn off auto-update on your servers?