Installing and Configuring SQL Server 2014 on Windows Server 2012 R2

Views: 16.953
Reading Time: 8 minutes

Hello readers,
Good Morning!

Today I will show you how to install Microsoft SQL Server 2014 on Windows Server 2012 R2 in a virtual machine. The process for installing on a physical machine is the same, without differences.

If you are looking for the updated version of this post, with the most current versions of SQL Server and Windows Server, learn more by visiting the post. How to install and configure Microsoft SQL Server 2016 on Windows Server 2016

Installing SQL Server

  1. Insert the media containing the SQL Server installation or unzip the ISO image into a directory.
  2. Open the root directory of the SQL Server installation files and run the file "setup.exe"
  3. SQL Server 2014 - Home Screen

  4. Click the "Installation" option in the left side menu and then "New SQL Server stand-alone installation or add features of an existing installation". This option is for installing SQL Server on only one machine and is what I will use in this tutorial.
  5. If you want to create a cluster of SQL Server servers (the database will run on more than 1 machine simultaneously, load balancing and increasing service availability, since if one server fails, the others take over the activities). failed), select the second option, “New SQL Server failover cluster installation”.
  6. The third installation option “Add node to SQL Server failover cluster” is for you to add a machine to an existing cluster.
  7. The last option on the installation screen is for those who already have an older version of SQL server installed and want to upgrade their version to 2014.
  8. SQL Server - Activation Key

  9. Enter the SQL Server 2014 activation key or choose the first option (specify a free edition) to use in product trial mode.
  10. Accept the terms of use and click "Next."
  11. SQL Server - Updates

  12. Here you define whether to use automatic SQL Server updates or not. I recommend enabling this option, as updates often bring fixes for functionality and security holes, performance enhancements, and new features. Click on "Next."
  13. SQL Server - Requirements Validation Screen

  14. In this screen, SQL Server will list the prerequisites for the installation. For me, it generated a Warning warning that I must configure ports in Firewall to allow remote connections (Learn more here). If all is right, click "Next."
  15. SQL Server - Installation Type

  16. Here we will define the type of SQL Server installation.

    - In the “SQL Server Feature Installation” option, the installation will allow you to select each of the components, which include Database, Analysis Services (Cubes / BI), Reporting Services, and Integration Services. (Programming tool using T-SQL and other features like file manipulation, Excel generation, etc.). By selecting this option, we will configure each of the services on the next screens and it does not come with anything preselected.

    - In the “SQL Server PowerPivot for SharePoint” option, you will install the PowerPivot feature on the Sharepoint server and optionally choose to install the database on the server.

    - In the “All Features with Defaults” option, the installation will select all features (Database, Analysis Services, Reporting Services and Integration Services) with their default settings on the next screen. It is the easiest way to install SQL Server and what I will use in the tutorial.
  17. SQL Server - Components

  18. Select the components you want to install, the installation path and click "Next."
  19. SQL Server - .NET Error

  20. On this screen, the installation will check some more prerequisites to proceed with the installation.
  21. If I fail in the .NET Framework 3.5 Service Pack step 1, as in my case, where I had just installed Windows Server, Download the installation file or enable .NET features if you are installing on Windows Server (Server Management> Adding Roles and Features logged in as ADMINISTRATOR). Install, restart the server for the installation to take effect, and restart SQL Server installation by repeating the previous steps.
  22. SQL Server - Instance Configuration

  23. In this screen we can define what will be the instance name to access SQL Server. By setting the default instance to “Default instance”, you do not need to enter the instance name to connect to the SQL Server server. Otherwise, to connect to the database, you have to enter the server name and instance. Ex: home_server \ instanceTest.

    An instance, either standard or named, has its own set of program and data files, as well as a set of common files shared between all instances of SQL Server on the computer. For this reason, even if you install SQL Server as the default instance, you MUST enter the instance ID.

  24. SQL Server - Services Configuration

  25. In this screen, we configure how to start SQL Server services (Automatic, Disabled, or Manual). For those using only the database, check primarily SQL Server Agent services, which is responsible for the jobs (automated routines) that run on the server and which is usually configured by default as Manual startup and SQL Server Database Engine, which is the database engine.

    Both of these services must be set to autostart if you want to turn on the server / computer automatically when the database is available automatically.

  26. SQL Server - Collation Configuration

  27. In this screen, we define the server's collation, which is the input and output language of texts. This is where we define whether the bank will have accent support, whether it will be case-sensitive (lowercase letters are different from uppercase letters. This is the default setting) or case-insensitive (Uppercase and lowercase letters are interpreted by the bank as the same thing) .

    Note: Collation of tables may differ from the collation of a specific column, which may also differ from the collation of the database. Database Collation is used to set the default collation of tables and columns when not specified.
  28. SQL Server - Collation Customization

  29. By clicking on the “Customize” button (optional), we can customize the collation of the database as needed. Particularly, I don't like using Case-Sensitive bank very much, although it's good practice, but it's a matter of taste.
  30. SQL Server - Configured Database Screen

  31. In the next screen, we will configure the database engine. Here we define the database authentication mode and the AD users who will be administrators. To add the logged in user who is installing SQL Server, simply click the "Add Current User" button.
    SQL Server authentication modes are:

    - Windows authentication: Uses Active Directory to authenticate users and only allows users who are registered with AD. No user and password entry required.

    - Mixed Mode: Allows both Active Directory authentication and SQL Server authentication, where the login is created in the database and is accessed using username and password. This is the most common option because most of the time, applications and systems do not use AD users, and configuration within the application to use SQL Server users is much simpler.
  32. SQL Server - Database Directories

  33. In the tab “Data Directories” we can configure the directories where they will be physically stored on disk, the system databases, the default Backup directory and the TempDB (data and log) and User (data and log) databases.
  34. In the “FILESTREAM” tab, it is possible to define if this feature will be enabled in the database. FILESTREAM integrates SQL SERVER with the NTFS file system by storing type objects (BLOB) with the varbinary (max) type using the NTFS file system for this storage. It is possible through T-SQL to insert, delete, update and query and backup this data natively (After I create a post about it and post here xD).
  35. SQL Server - Analysis Services - Type

  36. In the Analysis Services configuration screen, you can define the data modeling type (Multidimensional - Default Option) and the new Tabular mode, present from SQL Server 2012 (See the difference between the two here) and specify Analysis Services Administrator users.
  37. SQL Server - Analysis Services - Directories

  38. Again, SQL Server allows us to define the directories where we will be physically written to disk, the cube data, the log files, the temporary directory and the default backup directory.
  39. SQL Server - Reporting Services

  40. In the Reporting Services setup screen, we can select the first option to Install and already configure the reporting tool or select the second option (which I did), where it will just be installed and will be configured later.
  41. SQL Server - Distributed Replay Controller

  42. Here we can define the Administrator users of the Distributed Replay Controller, SQL Server's impact testing and evaluation and simulation tool, to complement SQL Server Profiler.
  43. SQL Server - Distributed Replay Client

  44. In this screen we can define the name of the created Controller, the Distributed Display installation path and the default path where the evaluation results will be stored.
  45. SQL Server - Installation Validation

  46. In this last screen, we will just review everything that will be installed, what is already installed on the server and the settings we made in the installation. If everything is OK, click "Install" to start the installation of Microsoft SQL Server 2014.
  47. Wait… Installation takes several minutes…
  48. SQL Server - End of Installation

  49. If everything went as expected, you should see this screen at the end of the installation process.
  50. READY! SQL Server 2014 installed!

Enabling Remote Connections

  • Now that we have set up our server, we need to enable connections outside of SQL Server, since in most cases access is made from machines other than the server, as in my case where the server is on the virtual machine and I access the database through Management Studio on the physical machine.

    Although we have already enabled external connections during installation, our server's firewall is probably blocking the ports needed for SQL Server from another machine to access our server. For this, I will explain below how to release these ports in Firewall.

Releasing Firewall Ports in Windows XP:

Releasing Firewall ports on Windows Server 2008, 2012, Windows Vista, 7, 8 and above:

That's it guys,
To the next!