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