12 Essential Steps After Installing SQL Server

Step 9: Configure Database Mail

This step is optional. If your databases are going to send e-mails, you will first need to enable database mail from the Surface Area Configuration tool (SQL 2005) or the Server Properties’ Surface Area Configuration Facet (for SQL 2008).

Once enabled, you can easily configure it using a wizard in the Management Studio.

You can also enable the mail profile for SQL Agent service.

If your apps are not going to use the mail feature – don’t enable or configure it.

Step 10: Configure SQL Agent & Operator

We are almost at the end of our configuration process. It is now worth having a look at the SQL Agent properties.  The three most important things that can be done here are:

a)    Configuring SQL and Agent services to restart when they stop unexpectedly (discussed before)

b)    Enabling the mail profile for SQL Agent. You can do it from the SQL Agent’s Alert System properties

c)    Increasing the size of the job history log in the msdb database

Changing the size of the job history log in the msdb database is usually a good idea if your server is going to host a large number of scheduled jobs.

Define DBA Operators:

Every database server should have one standard DBA operator defined. This operator will be notified via e-mail when jobs fail or alerts fire. Do not create yourself as this operator – if you leave the company, your e-mail account is likely to be deleted and those who come after you will need to spend time for reconfiguring everything as the notifications will fail. That’s why you should ask your system administrators to create a standard DBA account and assign a mailbox to it.

Step 11: Configure Operating System

You can also make some OS level changes to give some extra power to SQL. This works if you have administrative privilege on the Local Windows server.

If the database application is going to participate in distributed transactions through linked servers, ensure that Network DTC is enabled.

To reach this screen in a Windows Server 2003 system,

1.    Start the Component Service applet (Start > Administrative Tools > Component Services)

2.    Expand Component Services node and then Computers node in the left side pane

3.    Right Click on My Computer and choose Properties

4.    Choose the MS DTC tab

5.    Click on Security Configuration screen

The steps are similar for Windows Server 2008

You should also give your SQL Service account the right to “Lock pages in memory”. This ensures that Windows will not swap out SQL pages from the memory on to the paging file on disk. In fact from version 2005, SQL Server can detect a memory pressure from OS signal and can dynamically allocate / de-allocate pages itself.

To configure this property, assign the SQL service account to this privilege from the Local Security Policy.

Step 12: Set up Maintenance Plans for System Databases

Your server is now ready for production database rollouts. To wrap it up all, take a moment to create a maintenance plan for the system databases. This maintenance plan should reorganise the indexes, run DBCC CHECKS and update the statistics. It should also back up the master, model and the msdb databases.  As a best practice, the plan should backup the system databases once every day. When you have created the maintenance plan, run each of the tasks manually to see if they succeeded.

Conclusion

Although it is not possible to describe every possible installation scenario, the steps discussed here should be common in every installation. You should customise this list to incorporate any extra configuration necessary. For example, you may need to configure Reporting Services after SQL configuration is complete. Perhaps there are custom jobs or DBA stored procedures that you roll out in every SQL Server in the company. In each case, these extra steps will form parts of your post-installation configuration.