SQL Server by Remote DBA expert – Post Installation Tips

Usually it requires a Remote DBA expert with extensive knowledge about the SQL Server and the operating system to successfully install the server. After the installation is complete, one needs to modify the default settings before rolling out production databases. Lets discuss some of the steps a user can take after installation of the server.

Update

Updates bring with them a lot of bug fixes, stability and sometimes even new features. Once the SQL server is successfully installed, make sure you run the update tool in order to look for new Service Pack, Hotfixes and Cumulative Updates. The new service packs are available for free download from the Microsoft Website and other updates can be applied from within the operating system. Once the server is ready with all the patches and the latest database application, it is prudent to back-up the entire system for future use and reboot the server.

Unnecessary SQL Services

It is a good idea to determine all the SQL services which you will require, during the installation itself. However, there are often new services which are rolled out occasionally or current services which need to be installed but not used immediately. In the latter case, one should disable such services after installation.

For instance, the browsing service is most likely one which can be disabled. It is installed by default but many users do not need named or multiple instances on the same machine. The service can hence be disabled from the SQL Server Configuration Manager. The VSS writer is another such service which can be safely disabled in case the user does not need to use the Windows Volume Shadow Copy infrastructure to backup SQL databases. However, it is not accessible from the Configuration Manager and needs to be accessed from the the Services applet in the control panel.

Default Directories – Configuration

During installation, the SQL server creates a lot of directories for things like storing data, log files, replication, full text indexes and one for backup files.The location of these files is determined during installation, either by default or set by the user. A user needs to plan the location of these files to keep them protected.  The best option is to allocate a separate physical drive and directory for these data files, including the transaction logs of user databases. The SQL Server creates folders for few more components in the same location as the data files. However, to keep dedicated space intact for the disk activity, default location can be specified by the user for the following:

1. Backup files
2. Replication files
3. Full text index files
4. Job output files

These alternate locations can not specified using the front end Graphic User Interface (GUI). Since the path is saved as registry values, one has to change these registry entries manually or through an extended stored procedure like xp_regwrite. These registry keys will have different addresses for named instances and will change as per the SQL Server version.

The following script was used in a default instance of SQL Server 2005 on a Windows Server 2003 system, to change the default locations:

Configuring_Default_Directories.sql

Make sure you specify as registry key values, exist and then reboot the SQL services to makes sure that the changes are applied.

There are numerous parameters which need to be considered while installing an SQL server and many of them depend on the requirements of the user. Hence, it is quite unlikely that anyone would be able to list down all possible installation conditions. Each installation can be customized by a Remote DBA expert through changes in the settings to adapt the applications to the users’ needs and requirements.

Author Bio

James is a highly experienced Remote DBA expert. He loves writing about his job and often shares the knowledge he has gained through years of experience. He currently lives with his wife and son and loves to try new cuisines.