Data Stream
Search
⌃K

Load Balancing

Data Stream supports load balancing between two computers running Microsoft SQL Server and configured for database mirroring.

Installation

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