SQL Server Installation Top 10

  1. Change the sa password – As soon as you have finished installing SQL Server set a password for the sa login. Login as sa and use sp_password NULL, [your new password] to add a password.
  2. Change the default database – After install the default database is set to be master. This is a bad thing and can lead to user databases being mistakenly created on the master device. Use sp_diskdefault master, defaultoff to do this.
  3. Increase the size of tempdb – Double click on tempdb and then hit Expand. Choose the correct device and enter the increase in MB. Click Expand Now and voila..its done. I normally set it to be at least 20MB or 25% of the largest database..
  4. Drop the diskdump device – The diskdump device is a NULL device, Microsoft recommends using it as a bit bucket. However to save heart ache from mistakenly backing up to this device drop it now. Use sp_dropdevice diskdump to do this.
  5. Set up basic configuration – This includes:
    • Number of Open databases
    • Memory
    • Number of User Connections

    By Configuring at least these options you are on the way to tuning the system.

  6. Drop the pubs database – Unless you plan to use the pubs database for learning (all the documentation refers to it) drop it as its just taking up space! DROP DATABASE pubs to delete the thing.
  7. Configure the model database – As all new databases are built using this as a model add users and groups here so you wont have to add them to every new database.
  8. Create scheduled maintenance tasks – At the very least set up some scripts to update statistics and rebuild indexes.
  9. Import your data/users – bcp or transfer in your user data and set up user accounts then its ready to go…
  10. Go to pub – have a drink and relax 🙂

Copyright ) 1998-99 G.h.van den Berg. All rights reserved.
These pages may not be resold or redistributed without prior written permission from Guy van den Berg

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles