If you install SQL server by following click next, next, next button, then you probably should read this.
1. Make sure each SQL service has their own dedicate account.
2. Choose OS Power Options to “Performance” type.
3. Add SQL Service account to “Perform Volume Maintenance Tasks” (Before Install SQL Server, or you’ll have to restart the service to make it effect.)
4. Add SQL Service account to “Lock Pages in Memory” (Before Install SQL Server, or you’ll have to restart the service to make it effect.)
5. The server has been added to a windows domain, not workgroup. (If it’s not a dedicate Database Server, you could ignore this one)
6. Configure Anti-virus program to skip SQL Server data files (MDF, LDF, NDF)
7. If you need FileStream Feature,
Disable 8.3 filename generation (fsutil behavior set disable8dot3 1)
Disable updating of last file access time (fsutil behavior set disablelastaccess 1)
8. As for the storge, always try to use RAID 10 or RAID 1 if possible, it has better writing performance.
9. Use SQLIO to test disk performance.
10. Use Microsoft Data Link to test SQL Server connectivity.
- Create a blank text file on the remote machine.
- Change the file extension to ".udl"
- Double-click on the file to start the testing.
- If it is failed, check if you enabled TCP/IP protocol in SQL Server Configuration Manager.
- Check if SQL Server Instance is allow the remote connection by open SQL Server Management Studio property dialog.
- Check windows firewall setting.