Load Balancing

Data Stream supports load balancing between two computers running Microsoft SQL Server and configured for database mirroring.
Installation
1.​Create Primary Master Key​
2.​Create Primary Certificate​
3.​Create Primary Endpoint​
4.​Backup Primary Certificate​
5.​Create Secondary Login​
6.​Create Secondary Master Key​
7.​Create Secondary Certificate​
8.​Create Secondary Endpoint​
9.​Backup Secondary Certificate​
10.​Create Primary Login​
11.​Map Primary Certificate to Primary User​
12.​Grant Connect to Primary User​
13.​Map Secondary Certificate to Secondary User​
14.​Grant Connect to Secondary User​
15.​Alter Primary Database Set Partner​
16.​Alter Secondary Database Set Partner​
Primary
Create Primary Master Key
USE master;
​
CREATE MASTER KEY ENCRYPTION
  BY PASSWORD = 'secret password';
GO
Create Primary Certificate
USE master;
​
CREATE CERTIFICATE PRIMARY_cert
   WITH SUBJECT = 'Primary certificate for database mirroring',
   EXPIRY_DATE = '1/1/2038';
GO
Create Primary Endpoint
USE master;
​
CREATE ENDPOINT Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT = 5022
      , LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE PRIMARY_cert
      , ENCRYPTION = REQUIRED ALGORITHM AES
      , ROLE = ALL
   );
GO
Backup Primary Certificate
USE master;
​
BACKUP CERTIFICATE PRIMARY_cert 
  TO FILE = 'C:\PRIMARY_cert.cer';
GO
Create Secondary Login
USE master;
​
CREATE LOGIN SECONDARY_login
   WITH PASSWORD = 'secondary secret password';
GO
​
CREATE USER SECONDARY_user FOR LOGIN SECONDARY_login;
GO 
Map Secondary Certificate to Secondary User
USE master;
​
CREATE CERTIFICATE SECONDARY_cert
   AUTHORIZATION SECONDARY_user
   FROM FILE = 'C:\SECONDARY_cert.cer';
GO
Grant Connect to Secondary User
USE master;
​
GRANT CONNECT ON ENDPOINT::Mirroring TO [SECONDARY_login];
GO
Alter Primary Database Set Partner
USE master;
​
ALTER DATABASE [DataStream] SET PARTNER = 'TCP://PARTNER:5022';
GO
Secondary
Create Secondary Master Key
USE master;
​
CREATE MASTER KEY ENCRYPTION
  BY PASSWORD = 'secret password';
GO
Create Secondary Certificate
USE master;
​
CREATE CERTIFICATE SECONDARY_cert
   WITH SUBJECT = 'Secondary certificate for database mirroring',
   EXPIRY_DATE = '1/1/2038';
GO
Create Secondary Endpoint
USE master;
​
CREATE ENDPOINT Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT = 5022
      , LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE SECONDARY_cert
      , ENCRYPTION = REQUIRED ALGORITHM AES
      , ROLE = ALL
   );
GO
Backup Secondary Certificate
USE master;
​
BACKUP CERTIFICATE SECONDARY_cert 
  TO FILE = 'C:\SECONDARY_cert.cer';
GO
Create Primary Login
USE master;
​
CREATE LOGIN PRIMARY_login
   WITH PASSWORD = 'primary secret password';
GO
​
CREATE USER PRIMARY_user FOR LOGIN PRIMARY_login;
GO
Map Primary Certificate to Primary User
USE master;
​
CREATE CERTIFICATE PRIMARY_cert
   AUTHORIZATION PRIMARY_user
   FROM FILE = 'C:\PRIMARY_cert.cer';
GO
Grant Connect to Primary Login
USE master;
​
GRANT CONNECT ON ENDPOINT::Mirroring TO [PRIMARY_login];
GO
Alter Secondary Database Set Partner
USE master;
​
ALTER DATABASE [DataStream] SET PARTNER = 'TCP://PARTNER:5022';
GO
Validation
Primary
Secondary
Client Update Fails
Manual Application Restart
Last modified 4yr ago