Data Stream
Search…
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

1
USE master;
2
3
CREATE MASTER KEY ENCRYPTION
4
BY PASSWORD = 'secret password';
5
GO
Copied!

Create Primary Certificate

1
USE master;
2
3
CREATE CERTIFICATE PRIMARY_cert
4
WITH SUBJECT = 'Primary certificate for database mirroring',
5
EXPIRY_DATE = '1/1/2038';
6
GO
Copied!

Create Primary Endpoint

1
USE master;
2
3
CREATE ENDPOINT Mirroring
4
STATE = STARTED
5
AS TCP (
6
LISTENER_PORT = 5022
7
, LISTENER_IP = ALL
8
)
9
FOR DATABASE_MIRRORING (
10
AUTHENTICATION = CERTIFICATE PRIMARY_cert
11
, ENCRYPTION = REQUIRED ALGORITHM AES
12
, ROLE = ALL
13
);
14
GO
Copied!

Backup Primary Certificate

1
USE master;
2
3
BACKUP CERTIFICATE PRIMARY_cert
4
TO FILE = 'C:\PRIMARY_cert.cer';
5
GO
Copied!

Create Secondary Login

1
USE master;
2
3
CREATE LOGIN SECONDARY_login
4
WITH PASSWORD = 'secondary secret password';
5
GO
6
7
CREATE USER SECONDARY_user FOR LOGIN SECONDARY_login;
8
GO
Copied!

Map Secondary Certificate to Secondary User

1
USE master;
2
3
CREATE CERTIFICATE SECONDARY_cert
4
AUTHORIZATION SECONDARY_user
5
FROM FILE = 'C:\SECONDARY_cert.cer';
6
GO
Copied!

Grant Connect to Secondary User

1
USE master;
2
3
GRANT CONNECT ON ENDPOINT::Mirroring TO [SECONDARY_login];
4
GO
Copied!

Alter Primary Database Set Partner

1
USE master;
2
3
ALTER DATABASE [DataStream] SET PARTNER = 'TCP://PARTNER:5022';
4
GO
Copied!

Secondary

Create Secondary Master Key

1
USE master;
2
3
CREATE MASTER KEY ENCRYPTION
4
BY PASSWORD = 'secret password';
5
GO
Copied!

Create Secondary Certificate

1
USE master;
2
3
CREATE CERTIFICATE SECONDARY_cert
4
WITH SUBJECT = 'Secondary certificate for database mirroring',
5
EXPIRY_DATE = '1/1/2038';
6
GO
Copied!

Create Secondary Endpoint

1
USE master;
2
3
CREATE ENDPOINT Mirroring
4
STATE = STARTED
5
AS TCP (
6
LISTENER_PORT = 5022
7
, LISTENER_IP = ALL
8
)
9
FOR DATABASE_MIRRORING (
10
AUTHENTICATION = CERTIFICATE SECONDARY_cert
11
, ENCRYPTION = REQUIRED ALGORITHM AES
12
, ROLE = ALL
13
);
14
GO
Copied!

Backup Secondary Certificate

1
USE master;
2
3
BACKUP CERTIFICATE SECONDARY_cert
4
TO FILE = 'C:\SECONDARY_cert.cer';
5
GO
Copied!

Create Primary Login

1
USE master;
2
3
CREATE LOGIN PRIMARY_login
4
WITH PASSWORD = 'primary secret password';
5
GO
6
7
CREATE USER PRIMARY_user FOR LOGIN PRIMARY_login;
8
GO
Copied!

Map Primary Certificate to Primary User

1
USE master;
2
3
CREATE CERTIFICATE PRIMARY_cert
4
AUTHORIZATION PRIMARY_user
5
FROM FILE = 'C:\PRIMARY_cert.cer';
6
GO
Copied!

Grant Connect to Primary Login

1
USE master;
2
3
GRANT CONNECT ON ENDPOINT::Mirroring TO [PRIMARY_login];
4
GO
Copied!

Alter Secondary Database Set Partner

1
USE master;
2
3
ALTER DATABASE [DataStream] SET PARTNER = 'TCP://PARTNER:5022';
4
GO
Copied!

Validation

Primary

Secondary