In this practice, apply what you learned about deploying an instance.
Lesson 2: Managing SQL Server Instances CHAPTER 2 95 EXERCISE 1 Deploy Additional Instances on Computers SQL-A, SQL-B, and
SQL-CORE
In this exercise, you install additional instances of the SQL Server Database Engine on com-puters SQL-A, SQL-B, and SQL CORE. To complete this exercise, perform the following steps:
1. Log on to computer SQL-A with the Kim_Akers user account.
2. Use SQL Server Installation Center to install a new Database Engine instance named ALTERNATE with the default settings.
3. Log on to computer SQL-B with the Kim_Akers user account.
4. From an elevated command prompt, install a new Database Engine instance named ALTERNATE with the default settings.
5. Log on to computer SQL-CORE with the Kim_Akers user account.
6. From the command prompt, install a new Database Engine instance named ALTERNATE with the default settings.
7. Log on to server DC. Edit the SQL-POLICY GPO. Create an inbound rule in the Windows Firewall With Advanced Security node in the Computer Configuration\Windows Settings\Security Settings node. This inbound rule should have the following properties:
■ Program Rule
■ Program Path: C:\Program Files\Microsoft SQL Server\MSSQL11.ALTERNATE
\MSSQL\Binn\sqlservr.exe
■ Allow the connection
■ Domain, Private, and Public profiles
■ Name: SQL-AlternateManagement-Inbound
8. Right-click the SQL-AlternateManagement-Inbound rule. On the Scope tab, in the Remote IP Address section, select These IP Addresses and enter the 10.10.10.0/24 subnet.
EXERCISE 2 Resource Governor Configuration
In this exercise, you configure Resource Governor on instance SQL-A\Alternate. To complete this exercise, perform the following steps:
1. Log on to server SQL-A and use SQL Server Management Studio to connect to instance ALTERNATE.
2. Enable Resource Governor on instance ALTERNATE.
3. Use appropriate Transact-SQL code to create a resource pool named poolAlpha. Set the minimum CPU allocation for this pool to 5%.
4. Use appropriate Transact-SQL code to create a Workload Group named workAlpha associated with pool poolAlpha.
EXERCISE 3 Error Log Configuration
In this exercise, you reconfigure the properties of the SQL Server error log so that the last 20 error logs are kept and then you cycle the log. To complete this exercise, perform the follow-ing steps:
1. Connect to the SQL-A\ALTERNATE instance by using SQL Server Management Studio.
2. Configure the SQL Server error log so that 20 error log files will be kept before being recycled.
3. Use Transact-SQL to cycle the SQL Server error log.
Lesson Summary
■ SQL Server 2012 supports up to 50 instances on a single host and up to 25 instances on a failover cluster.
■ You must update all features associated with a SQL Server 2012 instance at the same time.
■ Shared features must be updated to the most recent update.
■ Run a software update with the /InstanceName parameter to update a specific instance; run the update with the /AllInstances parameter to update all instances.
■ Resource Governor enables you to manage processing and memory resources within a Database Engine instance.
■ You cannot use Resource Governor to manage Analysis Services, Integration Services, or Reporting Services.
■ Resource pools host workload groups. The categorizer assigns particular sessions to workload groups.
■ You assign resources to a resource pool by specifying either a minimum or maximum value for both the processor and memory resources.
■ Windows System Resource Monitor enables you to manage and allocate processor and memory resources across instances.
■ You can use the sp_cycle_errorlog stored procedure to cycle the SQL Server error log.
■ You can use sp_cycle_agent_errorlog from the msdb system database to cycle the SQL Server Agent error log.
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 want to cycle the SQL Server Agent error log. From which of the following system databases must you run the sp_cycle_agent_errorlog stored procedure to accomplish this goal?
Lesson 2: Managing SQL Server Instances CHAPTER 2 97 A. master
B. msdb C. model D. tempdb
2. Which feature should you enable and configure so session requests addressed to a specific instance can be allocated different processor resources based on session request properties?
A. Resource Governor
B. Windows System Resource Manager C. Processor affinity
D. I/O affinity
3. What is the maximum number of SQL Server 2012 Enterprise edition Database Engine instances that you can deploy on a non-clustered server running Windows Server 2008 R2 Enterprise edition?
A. 10 B. 25 C. 50 D. 100
4. You have configured Resource Governor with three resource pools. You have assigned the first resource pool a minimum CPU and memory value of 20%. You have assigned the second resource pool a minimum CPU and memory value of 30%. You want to assign maximum CPU and memory values to the third resource pool. What is the maxi-mum CPU and memory value you can assign to this resource pool?
A. 30%
B. 50%
C. 70%
D. 100%
5. A server that has four processors has three SQL Server 2012 Database Engine instances installed. Which feature should you configure to assign 60% of a host server’s proces-sor resource to the first instance, 20% to the second instance, and 15% to the third instance?
A. Resource Governor
B. Windows System Resource Manager C. Processor affinity
D. I/O affinity
Case Scenarios
In the following case scenarios, apply what you have learned about planning server installs and upgrades. You can find answers to these questions in the “Answers” section at the end of this chapter.