Published on

August 31, 2005

SQL Server Installation Best Practices

Installing SQL Server and Visual Studio can be a complex process, but with the right knowledge and best practices, you can avoid common installation issues. In this article, we will discuss some tips and recommendations for a smooth SQL Server installation.

Installation Order

It is recommended to install SQL Server 2005 before installing Visual Studio 2005. This ensures that all the necessary components and dependencies are properly installed.

Service Accounts

When configuring the Windows Service accounts for SQL Server and Reporting Services, it is best to follow these best practices:

  • Run the Windows Service under NT AUTHORITY\SYSTEM (XP & 2000)
  • Run the Windows Service under NT AUTHORITY\NETWORK SERVICE (2003)
  • Use a Domain Account for the services to access SQL Report Database

Internet Information Services (IIS)

Prior to installing the reporting services portion of Visual Studio 2005, make sure that Internet Information Services (IIS) is preinstalled, set to automatic, and running. This is necessary for the reporting services to function properly.

If you encounter a 403.9 error message when exceeding ten connections in IIS, you can increase the maximum connections to 40 by executing the following script in a command prompt:

CScript.Exe C:\Inetpub\AdminScripts\adsutil.vbs set w3svc/MaxConnections 40

Named Pipes

If you have a client application that uses SQL Server 2000 and the named pipes protocol, it is recommended to deselect named pipes during the SQL Server 2005 installation. Named Pipes are turned on by default in SQL Server 2005 and can cause conflicts during installation.

Security Considerations

During SQL Server installation, it is important to consider security best practices:

  • Do not select “Use the Local System account” for running the SQL services. Instead, use a Domain User account with no special privileges beyond being a “user”.
  • Select “Windows” as the Authentication Mode instead of “Mixed Mode”. While Mixed Mode is easier to use, Windows Authentication provides better security.
  • Disable Port 1433 in your firewall for added security.

SQL Server Management Studio

When using SQL Server Management Studio, be aware that there is a bug that prevents you from attaching to both the Integration Services and Reporting Services at the same time. This is likely a GUI issue and should not affect programmatically attaching to the services.

Reporting Services Symmetric Encryption Key

It is recommended to extract and backup the symmetric encryption key using the rskeymgmt utility. This key is essential for storing encrypted data in the report server database and/or catalog. Make sure to store the key on removable media for safekeeping.

SSL Configuration

If you choose to enable SSL for Reporting Services after the initial installation, there are some configuration changes required:

  • Edit the RSReportServer.config file and change the UrlRoot from http://… to https://…
  • Change the “SecureConnectionLevel” value from 0 to 2 or 3, depending on your security requirements.

Conclusion

These are just a few best practices and recommendations for installing SQL Server and Visual Studio. It is important to carefully follow the installation instructions and consult the official documentation for more detailed guidance. By following these best practices, you can ensure a successful and secure installation of SQL Server.

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.