After SQL Server 2012 is installed, you must verify that it is possible for remote clients to interact with the server. The simplest way of doing this is to attempt a connection by using the SQL Server 2012 client tools. For example, Figure 1-24 illustrates making a connection to a remote instance running on server SQL-CORE.
Installing SQL Server 2012 does not automatically configure firewall rules for the appropri-ate services installed; you must configure firewall rules manually on each server or configure them through the application of Group Policy. You can create firewall rules on each server by using the Netsh.exe command-line utility in the advfirewall firewall context. You can choose one of two approaches when configuring firewall rules for SQL Server 2012. The first approach is to configure program rules. The second is to configure port rules.
Lesson 2: Installing SQL Server and Related Services CHAPTER 1 41 FIGURE 1-24 Remote connection using SQL client tools
When you configure program rules, you specify the program to which you want to allow access. For example, Figure 1-25 shows a firewall rule for the Database Engine that includes the path to the Sqlservr.exe binary.
FIGURE 1-25 Database Engine firewall rule
To configure program rules for Database Engine, Analysis Services, Reporting Services, and Integration Services when installed in their default location in the default instance:
■ Database Engine: C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER
\MSSQL\Binn\sqlservr.exe
■ Analysis Services: C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER
\OLAP\bin\msmdsrv.exe
■ Integration Services: C:\Program Files\Microsoft SQL Server\110\DTS\Binn
\MsDtsSrvr.exe
■ Reporting Services: C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER
\Reporting Services\ReportServer\bin\ReportingServicesService.exe
By default, SQL Server 2012 named instances use dynamic ports. Because the port used by a named instance can change each time the Database Engine is started, configuring port-based rules can be challenging. To resolve this situation, you can create a program rule, or you can configure the Database Engine for an instance to use a fixed port.
To configure the Database Engine to use a fixed port, perform the following steps:
1. Open SQL Server Configuration Manager. Expand SQL Server Network Configuration.
Expand Protocols for the Database Engine instance you want to configure. Double-click TCP/IP.
2. On the IP Addresses tab, shown in Figure 1-26, locate the IP address for which you will configure a port. Remove any area in the TCP Dynamic Ports text box for that IP address. Specify the TCP port to be used in the TCP Port text box. Restart the SQL Server instance to apply the changes.
FIGURE 1-26 Configure Database Engine ports
MORE INFO CONFIGURING THE DATABASE ENGINE TO USE A SPECIFIC PORT You can learn more about using Database Engine on a specific port at http://msdn .microsoft.com/en-us/library/ms177440(v=SQL.110).aspx.
Lesson 2: Installing SQL Server and Related Services CHAPTER 1 43 SQL Database Engine uses the ports listed in Table 1-4.
TABLE 1-4 Database Engine Ports
Item Port
Default Instance TCP port 1433.
Named instances in default configuration Dynamic port. You can configure named instances to use fixed TCP ports.
Dedicated Admin Connection TCP Port 1434.
SQL Server Browser service UDP port 1434.
SQP Server instance running over an HTTP
end-point TCP port 80 for CLEAR_PORT traffic.
TCP port 443 for SSL_PORT traffic.
Service Broker TCP Port 4022.
Database Mirroring Chosen by the administrator. To determine the port, execute the following query:
Replication TCP port 1433 for default instance.
Transact-SQL Debugger TCP port 135.
EXAM TIP
Remember what TCP ports 1433 and 1434 are used for.
The ports that Reporting Services, Analysis Services, Integration Services, and other SQL features use are listed in Table 1-5.
TABLE 1-5 Additional Ports that SQL Server 2012 Features Use
Item Port
Analysis Services TCP port 2383 for default instance
SQL Server Browser Service TCP port 2382: used only for an Analysis Services named instance
Reporting Services Web Services TCP port 80 Reporting Services configured for use through HTTPS TCP port 443 Integration Services: Microsoft remote procedure calls TCP port 135
Integration services run time TCP port 135
Microsoft Distributed Transaction Coordinator (MS DTC) TCP port 135 SQL Server Management Studio browse connection to
browser service UDP port 1434
When configuring firewall rules, ensure that you limit the firewall rule scope so that the feature you are providing to the network can be accessed only by hosts on a specific net-work segment. For example, you might configure firewall rules so that inbound traffic will be accepted only from your organization’s internal network or only from specific hosts on your organization’s internal network. You can configure firewall rule scope on the Scope page of a rule’s properties in Windows Firewall With Advanced Security or by using the Netsh.exe com-mand in the advfirewall firewall context.
MORE INFO CONFIGURE FIREWALL TO ALLOW SQL SERVER ACCESS
You can learn more about configuring Windows Firewall to support SQL Server 2012 at http://msdn.microsoft.com/en-us/library/cc646023(v=SQL.110).aspx.
Connectivity problems can be more basic than traffic being blocked by Windows Firewall With Advanced Security. Sometimes connectivity problems are caused by your organiza-tion’s network infrastructure suffering a failure such as a DNS server, router, or switch going offline. If you suspect that the connectivity problem isn’t with the remote SQL server but is, instead, with the network infrastructure, perform the following tests to determine where the problem lies:
■ Use the Ipconfig.exe command-line utility to verify IP address configuration. Your client or the target SQL server might be incorrectly configured with an incorrect address, subnet mask, default gateway, or DNS server assigned.
■ Use the Nslookup.exe command to query DNS resolution for the name of the target SQL server. Prior to attempting this diagnostic step, flush the DNS cache by running the ipconfig /flushdns command to ensure that the query is run against the current DNS zone data.
■ Use the Ping.exe command to verify network connectivity between your local host and the IP address of the server. Ping the server by using both IP address and fully qualified domain name (FQDN).
■ Attempt to establish a connection by using the telnet utility to connect to port 1433.
■ Use Sqlcmd.exe to connect to the server. For example, to connect to server SQL-B, issue the command:
Sqlcmd.exe –S SQL-B
MORE INFO CHECKING REMOTE SERVER CONNECTION OPTIONS
You can learn more about checking remote server connection options at http://msdn .microsoft.com/en-us/library/ms179383(v=SQL.110).aspx.
Lesson 2: Installing SQL Server and Related Services CHAPTER 1 45 PRACTICE
Prepare for and Install SQL Server 2012
In this practice, you prepare computers for the installation of SQL Server 2012, install the SQL Server Database Engine, install SQL Server 2012 on a computer running the Server Core installation option, add features to an existing SQL Server 2012 deployment, and verify client connectivity.
EXERCISE 1 Prepare Computers for the Installation of SQL Server 2012
In this exercise, install the roles and features required to support SQL Server 2012 Database Engine. To complete this exercise, perform the following steps:
1. Log on to computer SQL-A with the Kim Akers domain account.
2. Use the Server Manager console to install the .NET Framework 3.5.1 feature.
3. Log on to computer SQL-B with the Kim Akers domain account.
4. From an elevated Windows PowerShell session, install the .NET Framework 3.5.1 feature.
EXERCISE 2 Install the SQL Server Database Engine
In this exercise, install the SQL Server Database Engine feature on servers SQL-A and SQL-B.
To complete this exercise, perform the following steps:
1. While logged on to computer SQL-A with the Kim Akers domain account, use Windows Explorer to navigate to the location that hosts the SQL Server 2012 installation files and execute Setup.exe. Provide consent when prompted by UAC.
2. In SQL Server Installation Center, choose Installation. Choose New SQL Server Stand-Alone Installation Or Add Features To An Existing Installation.
3. After the Setup Support Files have installed, on the Product Key page, choose Evaluation as the Specify A Free Edition option.
4. Accept the license terms. Click Next when warned that SQL Server Setup could not search for updates.
5. Click Next on the Setup Support Rules page. Choose SQL Server Feature Installation, as shown in Figure 1-27.
FIGURE 1-27 Setup Role
6. On the Feature Selection page shown in Figure 1-28, choose to install the following features and shared features:
■ Database Engine Services
■ SQL Server Replication
■ Full-Text and Semantic Extractions For Search
■ Data Quality Services
■ SQL Server Data Tools
■ Client Tools Connectivity
■ Integration Services
■ Management Tools – Basic
■ Management Tools – Complete
Lesson 2: Installing SQL Server and Related Services CHAPTER 1 47 FIGURE 1-28 Feature Selection
7. On the Instance Configuration page, choose Default Instance.
8. On the Server Configuration page, accept the default Service Accounts settings.
9. On the Database Engine Configuration page shown in Figure 1-29, click Add Current User and ensure that Windows Authentication Mode is selected.
FIGURE 1-29 Default Service Accounts
10. Continue through the SQL Server 2012 Setup Wizard and install SQL Server 2012.
11. When installation completes, restart the computer and log on with the Kim Akers domain account.
12. While logged on to computer SQL-B with the Kim Akers domain account, repeat steps 1 through 5 of this exercise and then move on to the next step.
13. On the Feature Selection page shown in Figure 1-30, choose to install the following features and shared features:
■ Database Engine Services
■ Client Tools Connectivity
FIGURE 1-30 Features on SQL-B
14. On the Instance Configuration page, choose Default Instance.
15. On the Server Configuration page, accept the default Service Accounts settings.
16. On the Database Engine Configuration page, click Add Current User and ensure that Windows Authentication Mode is selected.
17. Continue through the SQL Server 2012 Setup Wizard and install SQL Server 2012.
18. When installation completes, restart the computer and log on with the Kim Akers domain account.
Lesson 2: Installing SQL Server and Related Services CHAPTER 1 49 EXERCISE 3 Install SQL Server 2012 on a Computer Running Server Core
In this exercise, install the SQL Server 2012 Database Engine, integration services, and connec-tivity features on a computer running the Server Core installation option of Windows Server 2008 R2 Server Core with SP1. To complete this exercise, perform the following steps:
1. Log on to computer SQL-CORE with the Kim Akers domain account.
2. Ensure that the SQL Server 2012 installation media are accessible.
3. In the command prompt window, change to the volume hosting the SQL Server 2012 installation media.
4. In the command prompt window, type the following command:
Setup.exe /qs /Action=install /Features=SQLEngine,IS,Conn
/InstanceName=MSSQLServer /SQLSYSADMINACCOUNTS="Contoso\kim_akers"
/IAcceptSQLServerLicenseTerms
5. When the installation completes, log off computer SQL-CORE.
EXERCISE 4 Add Features to SQL-B
In this exercise, you add features to the existing SQL Server 2012 instances on servers SQL-A and SQL-B. To complete this exercise, perform the following steps:
1. Log on to computer SQL-B with the Kim Akers domain account.
2. Open the SQL Server Installation Center from the Microsoft SQL Server 2012
\Configuration Tools folder of the Start menu.
3. Choose New SQL Server Stand-Alone Installation Or Add Features To An Existing Installation from the Installation page.
4. Specify the location of the SQL Server 2012 installation media.
5. After the Setup Support rules have run, click Next on the Product Updates page.
6. On the Installation Type page as shown in Figure 1-31, select the Add Features To An Existing Instance Of SQL Server 2012 and choose the MSSQLSERVER instance.
FIGURE 1-31 Add Features to an Existing Instance
7. On the Feature Selection page, add the Integration Services feature.
8. Review the Disk Space requirements and accept the default settings for the SQL Server Integration Services service account. Perform the installation and then log off server SQL-B.
EXERCISE 5 Verify Connectivity and Install AdventureWorks
In this exercise, verify remote connectivity by remotely connecting from server SQL-A to the SQL Server 2012 instances on servers SQL-B and SQL-CORE and also install the AdventureWorks sample database. To complete this exercise, perform the following steps:
1. Log on to computer SQL-A with the Kim Akers domain account.
2. Open SQL Server Management Studio and connect to the Database Engine on server SQL-A by using Windows Authentication.
3. Click the Connect button in Object Explorer. Select Database Engine and then connect to the Database Engine on server SQL-B.
4. Click the Connect button in Object Explorer. Select Database Engine and then connect to the Database Engine on server SQL-CORE.
5. Verify that Object Explorer has connections to the Database Engines on all three serv-ers, as shown in Figure 1-32.
Lesson 2: Installing SQL Server and Related Services CHAPTER 1 51 FIGURE 1-32 Object Explorer with connections to three servers
6. Download the AdventureWorks sample database file to server SQL-A from the follow-ing location: http://go.microsoft.com/FWLink/?Linkid=251256.
7. Copy the AdventureWorks2012_Data.mdf file to the C:\adventureworks2012 directory.
8. In Object Explorer, right-click the Databases node. Click Attach and specify the location of AdventureWorks2012_Data.mdf.
9. On the Attach Databases page, click the location of the log file and click Remove.
Verify that the Attach Databases dialog box matches Figure 1-33 and then click OK.
FIGURE 1-33 Attach AdventureWorks database
Lesson Summary
■ The volume that hosts the operating system needs a minimum of 4 GB of available space to host the temporary SQL Server 2012 installation files.
■ If you do not enter a product key, SQL Server 2012 will be installed as an evaluation edition.
■ You can install up to 50 instances of the Database Engine on a single server.
■ You can install only one instance of SSIS on a server.
■ You can add features to an existing installation by using Windows Explorer, by using Setup.exe from the command line, or by invoking SQL Server Setup through SQL Server Installation Center.
■ You can remove features from an existing installation by using Setup.exe from the command line or by using Programs And Features in Control Panel.
■ You must create firewall rules to enable remote connections to SQL Server 2012 features.
■ You can verify connectivity to a remote instance of SQL Server 2012 features by using SQL Server Management Studio or SQLCMD.
Lesson 2: Installing SQL Server and Related Services CHAPTER 1 53
Lesson Review
Answer the following questions to test your knowledge of the information in this lesson. You can find the answers to these questions and explanations of why each answer choice is correct or incorrect in the “Answers” section at the end of this chapter.
1. You are planning on deploying a server that will be dedicated for ETL (Extraction, Transformation, and Loading) processes. You want to ensure that SSIS (SQL Server Integration Services) packages will run on this dedicated ETL server and not on any other server on which they were started. Which of the following features must you install on the ETL server in addition to SSIS to accomplish this goal?
A. Database Engine
B. SQL Server Reporting Services C. SQL Server Analysis Services D. Client Tools SDK
2. You have installed the SQL Server Integration Services (SSIS) feature on a server run-ning Windows Server 2008 R2 SP1. Which of the following features must you install if you want to ensure that you can run packages in 32-bit mode?
A. Client Tools SDK B. Data Quality Client C. SQL Server Data Tools
D. Client Tools Backwards Compatibility
3. You want to allow access to SQL Server Analysis Services on a server running Windows Server 2008 R2 for a client running SQL Server Management Studio on Windows 7. You have installed SQL Server Analysis Services in the default location. Which of the fol-lowing paths should you use when creating the firewall rule by using Windows Firewall With Advanced Security?
A. C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn
\sqlservr.exe
B. C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\bin
\msmdsrv.exe
C. C:\Program Files\Microsoft SQL Server\110\DTS\Binn\MsDtsSrvr.exe
D. C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services
\ReportServer\bin\ReportingServicesService.exe
4. You want to remove SQL Server Integration Services from a server running the Windows Server 2008 R2 operating system that also has the Database Engine and SQL Server Analysis Services installed. Which of the following tools can you use to accom-plish this goal?
A. SQL Server Management Studio B. SQL Server Configuration Manager
C. Add/Remove Programs in Control Panel D. SQL Server Installation Center
5. You want to reproduce the same SQL Server 2012 installation configuration across five servers. Which of the following files will you generate by using SQL Server Setup to accomplish this goal?
A. Configuration.xml B. Setup.ini
C. Setup.xml
D. ConfigurationFile.ini