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
- Insert the media containing the SQL Server installation or unzip the ISO image into a directory.
- Open the root directory of the SQL Server installation files and run the file "setup.exe"
- 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.
- 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”.
- The third installation option “Add node to SQL Server failover cluster” is for you to add a machine to an existing cluster.
- 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.
- Enter the SQL Server 2014 activation key or choose the first option (specify a free edition) to use in product trial mode.
- 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."
- 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."
- 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.
- Select the components you want to install, the installation path and click "Next."
- On this screen, the installation will check some more prerequisites to proceed with the installation.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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).
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- Wait… Installation takes several minutes…
- If everything went as expected, you should see this screen at the end of the installation process.
- 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:
netsh firewall add portopening UDP 137 "NetBIOS UDP Porta 137"
netsh firewall add portopening UDP 138 "NetBIOS UDP Porta 138"
netsh firewall add portopening TCP 139 "NetBIOS TCP Porta 139"
netsh firewall set portopening protocol=TCP port=1433 name=SQLPort mode=ENABLE scope=SUBNET profile=CURRENT
Releasing Firewall ports on Windows Server 2008, 2012, Windows Vista, 7, 8 and above:
netsh advfirewall firewall add rule name="NetBIOS UDP Porta 137" dir=in action=allow protocol=UDP localport=137
netsh advfirewall firewall add rule name="NetBIOS UDP Porta 137" dir=out action=allow protocol=UDP localport=137
netsh advfirewall firewall add rule name="NetBIOS UDP Porta 138" dir=in action=allow protocol=UDP localport=138
netsh advfirewall firewall add rule name="NetBIOS UDP Porta 138" dir=out action=allow protocol=UDP localport=138
netsh advfirewall firewall add rule name="NetBIOS TCP Porta 139" dir=in action=allow protocol=TCP localport=139
netsh advfirewall firewall add rule name="NetBIOS TCP Porta 139" dir=out action=allow protocol=TCP localport=139
netsh advfirewall firewall add rule name="SQL Server Porta 1433" dir=in action=allow protocol=TCP localport=1433
netsh advfirewall firewall add rule name="SQL Server Porta 1433" dir=out action=allow protocol=TCP localport=1433
That's it guys,
To the next!