Recently I’ve installed MS Sql Server (don’t remember how many times I’ve done that already :) ) on the new virtual machine and realized that I always perform the following steps after each Sql Server installation:
1. Disable “sa” login
This step was added to mandatory tasks list after sad story: in hte times of beginning of my IT carrier one of my Sql Server instances ran with enabled “sa” login. Password was not very strong and after some time it was brute forced and I got malicious Sql Server job which tried to download and execute remote code on my PC. Fortunately I noticed that in time and made necessary actions. After that I always disable built-in “sa” login on Sql Server – this is the first things hackers will try to brute force on your instance.
2. Configure backups
This step is quite obvious. Remember that you should not only test that your backups work by specified schedule (e.g. if you configured nightly backups – check after few days that backups are really made during last nights) but also test restore scenario. Some time these simple steps will save you from a lot of problems. You may configure backups in Sql Server Management Studio > Management > Maintenance plans > New maintenance plan > Add Back Up Database Task from toolbox: After configuring backup set schedule in the same window.
Another important note is that you should not store backups on the same PC. Safest option is to move backups from local PC to the cloud storage using some cloud backup tool.
3. Limit Sql Server trace log size
If Sql Server runs long period of time it may flood hard drive with trace logs (don’t mix it with transaction logs – these are different). By default they are stored in MSSQL/Logs subfolders under your Sql Server instance folder. In order to configure their size go to Sql Server Management studio > Management > Right click on SQL Server Logs > Configure. Set some value in “Limit the number of error log files before they are recycled” and/or “Maximum size for error log file in KB”:
This simple action will allow to keep Sql Server logs size controlled. If you have similar mandatory steps in your practice please share them in comments.
What version of the server do you install usually?
ReplyDeleteAs far as I remember, by default setup configures the new server instance to use Windows authentication mode and the 'sa' login is disabled.
Quite recent. But in my cases I need Sql authentication - and when you enable both Windows and Sql, sa got enabled and yuo need to disable it explicitly.
Delete