Installing and Configuring SQL Server 2014 on Windows Server 2012 R2

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. 

4. Click on the “Installation” option on the left side menu and then on “New SQL Server stand-alone installation or add features of an existing installation”. This option serves to install SQL Server on only one machine and is the one that 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 the availability of the service, since if one of the servers fails, the others take over the activities than failed), select the second option, “New SQL Server failover cluster installation”.
6. The third installation option “Add node to a 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. 

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. 

12. Here you define whether to use automatic SQL Server updates or not. I recommend enabling this option, as updates often bring fixes for features and security holes, performance improvements and the addition of new features. Click "Next".
13. 

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 everything is right, click on “Next”.
15. 

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 the Database, Analysis Services (Cubes / BI), Reporting Services (Data extraction and reporting tool) and Integration Services (Programming tool using T-SQL and other resources such as file manipulation, Excel generation, etc.). When selecting this option, we will configure each of the services on the next screens and it does not come with anything pre-selected.
    
    - In the option “SQL Server PowerPivot for SharePoint”, you will install the PowerPivot feature on the Sharepoint server and optionally you can choose to install the database on the server.
    
    - In the option “All Features with Defaults”, the installation will select all resources (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 the one I will use in the tutorial.
17. 

18. Select the components you want to install, the installation path and click "Next".
19. 

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 activate .NET features if 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 start the SQL Server installation again by repeating the previous steps.
22. 

23. In this screen we can define what will be the name of the instance to access SQL Server. Setting the instance as the default “Default instance”, it is not necessary to enter the name of the instance to connect to the SQL Server server. Otherwise, to connect to the database, you will have to enter the name of the server and instance. Ex: home_server \ test instance.

    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. 

25. On this screen, we configure the way to start the SQL Server services (Automatic, Disabled or Manual). For those using only the database, check mainly the SQL Server Agent services, which are responsible for the Jobs (automated routines) that run on the server and which are usually configured by default as Manual startup and the 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. 

27. In this screen, we define the Collation of the server, which is the input and output language of the texts. This is where we define whether the bank will support accentuation, 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: The collation of tables can be different from the collation of a specific column, which can also be different from the collation of the database. The Collation of the database is used to define the default collation of tables and columns when not specified.
28. 

29. By clicking on the “Customize” button (optional), we can customize the database collation according to the need. I don't particularly like to use the Case-Sensitive bench, although it is a good practice, but it is a matter of taste.
30. 

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, just click on the “Add Current User” button.
    The 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. 

33. In the tab "Data Directories" we can configure the directories where they will be physically stored on the 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 whether this feature will be enabled in the database. FILESTREAM integrates SQL SERVER with the NTFS file system by storing objects of type (BLOB) with type varbinary (max) using that of the NTFS file system for this storage. It is possible through T-SQL to perform the insertion, deletion, update and consultation and backup of this data natively (After I create a post about it and post it here xD).
35. 

36. In the Analysis Services configuration screen, you can define the type of data modeling (Multidimensional - Standard 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. 

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. 

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. 

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

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. 

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

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!