Lesson 4/6
SQL Server: multiple instances, CPU and memoryCreating a second SQL Server instance to keep applications apart. Delegating security per instance. Splitting the CPU precisely with processor affinity, and capping the memory. Enabling the SA account in mixed mode. Configuring SQL Agent for scheduled jobs.
☰
Contents
30
▾
0:03 An overview of SQL Server instances 0:15 The use case: separating security from payroll 0:58 Memory, CPU and services 1:21 A reminder of the demo platform 1:34 Checking the services in PowerShell 2:22 Starting the install of the second instance 2:46 Adding features to the setup 3:22 Selecting the database engine 3:29 Naming the Pay instance 3:47 Windows or SQL authentication 4:15 Setting a static IP after the install 4:49 Checking the installed services 5:11 Setting TCP port 14441 5:38 Starting SQL Agent automatically 6:03 Connecting to the new instance 6:28 Working out how to split CPU and RAM 7:09 Keeping resources back for the system 7:43 Allocating differently according to need 8:54 Capping memory in Management Studio 9:24 Configuring processor affinity 10:02 Setting MAXDOP for parallelism 10:10 Applying it to the second instance 10:42 Strategies for splitting the CPU 11:11 Switching to mixed authentication mode 12:00 Enabling the SA account 12:30 Setting the password and the sysadmin role 12:52 Restarting the Pay instance 13:19 Connecting with the SA account 13:54 Why applications like SA 13:59 Why SQL Server Agent matters
