# Load Balancing

Data Stream supports load balancing between two computers running Microsoft SQL Server and configured for [database mirroring](https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/database-mirroring-sql-server?view=sql-server-2017).

## Installation

1. [Create Primary Master Key](#create-primary-master-key)
2. [Create Primary Certificate](#create-primary-certificate)
3. [Create Primary Endpoint](#create-primary-endpoint)
4. [Backup Primary Certificate](#backup-primary-certificate)
5. [Create Secondary Login](#create-secondary-login)
6. [Create Secondary Master Key](#create-secondary-master-key)
7. [Create Secondary Certificate](#create-secondary-certificate)
8. [Create Secondary Endpoint](#create-secondary-endpoint)
9. [Backup Secondary Certificate](#backup-secondary-certificate)
10. [Create Primary Login](#create-primary-login)
11. [Map Primary Certificate to Primary User](#map-primary-certificate-to-primary-user)
12. [Grant Connect to Primary User](#grant-connect-to-primary-login)
13. [Map Secondary Certificate to Secondary User](#map-secondary-certificate-to-secondary-user)
14. [Grant Connect to Secondary User](#grant-connect-to-secondary-user)
15. [Alter Primary Database Set Partner](#alter-primary-database-set-partner)
16. [Alter Secondary Database Set Partner](#alter-primary-database-set-partner)

### Primary

#### Create Primary Master Key

```sql
USE master;

CREATE MASTER KEY ENCRYPTION
  BY PASSWORD = 'secret password';
GO
```

#### Create Primary Certificate

```sql
USE master;

CREATE CERTIFICATE PRIMARY_cert
   WITH SUBJECT = 'Primary certificate for database mirroring',
   EXPIRY_DATE = '1/1/2038';
GO
```

#### Create Primary Endpoint

```sql
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

```sql
USE master;

BACKUP CERTIFICATE PRIMARY_cert 
  TO FILE = 'C:\PRIMARY_cert.cer';
GO
```

#### Create Secondary Login

```sql
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

```sql
USE master;

CREATE CERTIFICATE SECONDARY_cert
   AUTHORIZATION SECONDARY_user
   FROM FILE = 'C:\SECONDARY_cert.cer';
GO
```

#### Grant Connect to Secondary User

```sql
USE master;

GRANT CONNECT ON ENDPOINT::Mirroring TO [SECONDARY_login];
GO
```

#### Alter Primary Database Set Partner

```sql
USE master;

ALTER DATABASE [DataStream] SET PARTNER = 'TCP://PARTNER:5022';
GO
```

### Secondary

#### Create Secondary Master Key

```sql
USE master;

CREATE MASTER KEY ENCRYPTION
  BY PASSWORD = 'secret password';
GO
```

#### Create Secondary Certificate

```sql
USE master;

CREATE CERTIFICATE SECONDARY_cert
   WITH SUBJECT = 'Secondary certificate for database mirroring',
   EXPIRY_DATE = '1/1/2038';
GO
```

#### Create Secondary Endpoint

```sql
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

```sql
USE master;

BACKUP CERTIFICATE SECONDARY_cert 
  TO FILE = 'C:\SECONDARY_cert.cer';
GO
```

#### Create Primary Login

```sql
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

```sql
USE master;

CREATE CERTIFICATE PRIMARY_cert
   AUTHORIZATION PRIMARY_user
   FROM FILE = 'C:\PRIMARY_cert.cer';
GO
```

#### Grant Connect to Primary Login

```sql
USE master;

GRANT CONNECT ON ENDPOINT::Mirroring TO [PRIMARY_login];
GO
```

#### Alter Secondary Database Set Partner

```sql
USE master;

ALTER DATABASE [DataStream] SET PARTNER = 'TCP://PARTNER:5022';
GO
```

## Validation

### Primary

<div align="left"><img src="https://490106859-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LTSxUDgh5E8mrrG8_9m%2F-LTctAR3ADTNC74wyYtF%2F-LTd8ONaYo3fVWUzLcsx%2FMirroringValidationPrimary.png?alt=media&#x26;token=d5175139-423d-47d6-8bfe-60528a05b39c" alt=""></div>

### Secondary

<div align="left"><img src="https://490106859-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LTSxUDgh5E8mrrG8_9m%2F-LTctAR3ADTNC74wyYtF%2F-LTd8ONZZsJftLcX7q0_%2FMirroringValidationSecondary.png?alt=media&#x26;token=25aa9cf0-4383-440a-8aa3-efa45c7e4508" alt=""></div>