Install SQL Server Guidelines


MENU
By:

Getting the software

In the old days you installed SQL Server from a CD on a server located in your office, but nowadays there are many different methods for using SQL Server, some that don't even require downloading software.  With that said, you can still download and install the software on a local/on-premises server or on a server that is hosted in the cloud.  Azure also offers Saas, Iaas and Paas options, SQL Database PAAS vs SQL Server IAAS, which gives you as much or as little flexibility/manageability as you require when it comes to the database infrastructure.  There are also Windows and Linux docker containers that you can use to run SQL Server in your docker environment.  Whichever method you decide on using, the following link will get you started, SQL Server Downloads, and also contains more detailed information on each of the different installation options.

SQL Server Hardware and Software Requirements

With so many different versions and editions of SQL Server, as well as the many different on-premises and cloud options for hardware, it would not really be practical to list all of the different requirements for each option in this tutorial.  That said, the following are some basic minimum guidelines that apply to almost every type of installation.

  • Processor speed - 2.0 GHz
  • Processor type - x64
  • Server Memory - 1 GB
  • Storage space - 8GB (if installing all features, 1.5GB for just database engine)
  • Storage type - Local, Shared, SMB File Share
  • Network - Supports one of Shared memory, Named Pipes, TCP/IP
  • Operating System - Windows 8 and higher or Server 2012 and higher
  • Software - .NET Framework

For a complete listing of all of the requirements the following link can get you started: Hardware and Software Requirements for SQL Server.

SQL Server Installation Best Practices

One of the main reasons that SQL Server is so popular has to be attributed to the fact that even the newest IT professional can install the software with just a few clicks using the GUI installer (accepting all the defaults) and after a few minutes they have a usable database installed.  Even when following the default install there are however some basic best practices that should be followed with any SQL Server installation. 

  • Software - Both the operating system and database software should have the latest service pack and all security patches installed
  • Storage - Benchmark performance testing should be performed on your IO subsystem to ensure that it can handle the amount of IO that your SQL Server installation will generate.
  • Memory - In a perfect world, SQL Server would be the only application running on your system so there should not be any contention but even if that is the case you should enable the "Lock Pages in Memory" option and set the "Min Memory" and "Max Memory" database server settings to guarantee that SQL Server will be able to acquire at least the required minimum amount of memory and once acquired it will not be swapped out.
  • Instance Type - The are two different instances type in SQL Server, default and named. If you are going to be installing more than one SQL Server instance on a single host then you will need to use named instances when they are created.  This then requires clients to provide the server and instance name when connecting so they are connected to the correct instance.  You can still use the default instance if you have multiple instances on the server, but you can only have one default instance.  In this case, clients simply provide the server name when connecting to the instance.  When looking at the services dialog a default instance will be named "SQL Server (MSSQLSERVER)" and a named instance will be displayed as "SQL Server (#INSTANCENAME#)".  Note: The same naming convention applies to the Agent, Analysis Services and Reporting Services.
  • Feature Selection - When you get to the feature selection dialog during the installation process it's a good idea to make sure you only install the features that you are going to use as many of the features require their own services and use resources even if you aren't actually doing anything with the service.  Missing features can always be installed at a later time when it's determined that they are required, simply by re-running the installer.
  • Network Protocols - Only enable the protocols that will be used by your application.
  • Authentication Mode - Using Windows authentication is preferred and is generally much easier to manage but there are cases where you would require that the server enable SQL Server (for SQL Server) or Basic (for Reporting Services) authentication, eg. computer not on a domain.  In these cases, you need to take into account the extra security risk in having these types of authentication enabled.  Note: Analysis Services uses Windows authentication exclusively.
  • Selection of Data Directories - Keeping the data and log files on separate drives allows you to reduce the amount of IO contention when reading and writing data to and from SQL Server.
  • Configuring TempDB - Along with data and log files, the tempdb should also be on it's own drive (also to reduce contention).  In addition to this the default installation only has one tempdb file. Generally speaking, you should have one file per core, but depending on your hardware this can in some cases hurt performance.  SQL Server tempdb One or Multiple Data Files and as stated by Microsoft creating more than 8 (if you have more than 8 cores) rarely gives you much in the way of performance improvement.
  • Server Collation - This setting affects the sort order within your SQL Server environment.  You should ensure this is set correctly as it's very difficult to change after the fact.
  • Maintenance Plan - As with any software platform it's always a good idea to have a backup plan in place as soon as your system has been installed in order to protect you against any loss.

Security

When it comes to the security of any environment, I like to break things down into 3 different categories.  Physical Security, OS Level Security and Software Level Security. 

  • The first category deals with the access to your physical hardware.  If yours is located in the cloud then you have one less thing to worry about but you should ensure that your vendor has the necessary controls around who can gain access to the actual hardware just as you would if you hosted on premises.
  •  The second level is with regards to the operating system.  As we mentioned above you should ensure that the latest service pack and security patches have been installed on all of your software.  You should also ensure that you have a firewall enabled which at the very least restricts access to only the required ports.  Ideally you would also, if possible, limit the IP addresses that can access your system.  Finally, at this level, you should also ensure there are appropriate controls around the users who have access to the system.  Generally, this would be limited to system administrators and those who provide other support for the system. 
  • The last level, software security, is where you need to make sure that there are appropriate controls around the users who have access to your database and the objects within it.  Here you can also address any encryption needs your system might require.  Whether it be at the database level, whole or table/column level data encryption, or with respect to the encryption of traffic between your server and the client application.  In order to provide a secure system all of these items should be addressed.
Additional Information

<< Previous

Next >>



Comments For This Article

















get free sql tips
agree to terms