SQL Server best practices for: installation, delete files after service pack installation, applications on instances and security
Questions and answers regarding SQL Server installation best practices, the files we can delete after a service pack is installed, user rights and how many instances are necessary for different application.
1. After you installed a Service Pack for SQL Server 2008 R2, is it possible to delete part of the files without altering the functioning of SQL Server? If yes, what files?
The files that are added during the installation of a Service Pack are absolutely necessary for the SQL Server instance (for which you installed the service pack) to function properly: https://msdn.microsoft.com/en-us/library/dd638062(v=SQL.105).aspx
”The recommended disk space requirements are approximately 2.5 times the size of the package to install, download, and extract the package. After installing a service pack, you can remove the downloaded package. Any temporary files are removed automatically.”
So the files that are added or modified during the installation of the Service Pack are absolutely necessary and is not indicated to delete them once the installation is finished.
The folder « C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Update Cache » is used to verify what are the service packs or cumulative updates already installed and it is also not indicated to delete it.
Normally, as the article mentioned above explains, after the service pack is installed he will delete the temporary files he created and that are no longer necessary.
The only thing that can be deleted after the service pack is installed is the installation kit that was used to install the service pack.
2. Does the number of instances installed on a server can impact the performance or the storage size? If so, how much space is needed for each instance?
Each installation of SQL Server will create one independent SQL Server Instance on the same server. That means, each installation will copy and use its own folders with its own system databases (master, model, msdb, tempdb) and the configuration choosen during the installation of after. So it is necessary a minimum required disk space for each instance depending on the components or features that are chosen during the installation:
For each SQL Server installation you will need the following disk space: https://msdn.microsoft.com/en-us/library/ms143506(v=sql.105).aspx#HardDiskSpace
3. Is there a maximum number of instances that can be installed on an environment? What is the optimum number of instances that can be installed?
The maximum number of instances for a standalone server is 50 and for a clustered environment is 25: https://msdn.microsoft.com/en-us/library/ms143432(v=SQL.100).aspx
The Database Engine component of SQL Server is the core service for storing, processing, and securing data. The Database Engine provides controlled access and rapid transaction processing to meet the requirements of the most demanding data consuming applications in your enterprise. To create a typical SQL Server installation, see How to: Install SQL Server 2008 R2 (Setup).
You can install multiple SQL Serve instances on the same machine but not more than 50. For each new installation you have to make sure that you have enough resources for the instance to function correctly.
Depending on the applications you have in place and the necessary configuration needed by each application you can have one or more SQL Server instances with one or more databases that correspond to your needs.
4. Is it necessary to have one instance for each application?
Considering the characteristics of each application, the SQL Server configuration needed for each application and the resources on the server, you can use one instance for all the applications or multiple instances for all the applications you have.
It is not absolutely necessary or a requirement to have one instance for each application.
5. What are the necessary rights for each user?
You can find in the following link the rights you can give each user: https://social.technet.microsoft.com/wiki/contents/articles/4433.database-engine-permission-basics.aspx#Grant_the_Least_Permissions
Securing SQL Server can be viewed as a series of steps, involving four areas: the platform, authentication, objects (including data), and applications that access the system. The following topics will guide you through creating and implementing an effective security plan: Securing SQL Server.
6. What are the requirements and steps of installing correctly SQL Server?
The requirements for installing correctly SQL server are detailed in the article: https://msdn.microsoft.com/en-us/library/bb500442(v=sql.105).aspx
Describes SQL Server hardware and software requirements, operating system support, network and Internet considerations, and hard disk space requirements.
Describes requirements for the SQL Server System Configuration Checker.
Describes security considerations for a SQL Server installation.
Also to have a step by step installation you can access: How to: Install SQL Server 2008 R2 (Setup)