Setup tips for installing a new SQL Server Database

Question

For cost savings, we're moving from an managed SQL Server DB to a dedicated db on our own server.

What configuration checklist would you perform to ensure maximum security and reliability?

Answer 1

For starters, remember to remove the sa login - it's amazing how many places I've worked that hadn't done this!

Apart from that, if you have the money, invest in fast disks - it makes a huge difference IMO. A lot of the other configuration depends on your individual application/customer needs.

Answer 2

There is a ton of stuff, here is just a partial list

log and data files on different disks

tempdb on its own disk

buy as much memory as you can afford (RAM is alway faster than IO from disk)

after doing the restore or attach make SURE that you update the statistics with fullscan and rebuild all the indexes (this is very important if you want to have optimal plans)

See also SQL Server Best Practices on the microsoft site, there are a ton of white papers available

Answer 3

Don't MS have a "best practice" tool?

Also, avoid SQL Server accounts and stick to Windows authentication, this allows you to keep usernames and passwords out of your connection strings. Then assign the operational user accounts specific permissions on the database objects they need, and nothing else (e.g. can execute SProcs but not look at the source).

Also, read through blog.stackoverflow.com, Jeff et al have had to do some work in this area.

Answer 4

Make sure you set the default server collation to what you need. This will stop you from creating new databases in the future and having to hard code collation changes between that and otehr databsaes.

Answer 5

1. Set up a job or create a maintenance plan for doing full backups of all your databases daily, system databases weekly.
2. If you're using Full recovery mode, add a transaction log backup job to run at least hourly.
3. Make sure you have a process to know if the backup jobs aren't running (oops, someone stopped the agent a month ago, now we have no backups).
4. Test your backups by restoring them on another server.
5. You may need optimization jobs to keep your indexes under control daily/weekly/monthly depending on usage.