Configuration with Login
Suppose we use a Windows account to log in to SQL Server, in the case of using a SQL Server account the same way.
CREATE LOGIN [PRICIPAL-SRVAdministrator]
FROM WINDOWS
GO
Create endpoints:
CREATE ENDPOINT Partner
STATE = STARTED
AS TCP (LISTENER_PORT = 5022)
FOR DATABASE_MIRRORING (
AUTHENTICATION = WINDOWS NEGOTIATE,
ENCRYPTION = SUPPORTED,
ROLE = ALL)
GO
Note that creating endpoint with ROLE = ALL needs to perform on both principal and mirror servers, on the witness server, you replace with ROLE = WITNESS .
Configuring by Certificate:
Instead of using a login account to let each endpoint identify each other, it is possible to use an alternative solution to create certificates - the certificate.
- Create master key encryption (required to export certificate):
tạo người dùng key xác thực từ mật khẩu = 'abc123 !!';
- Create a cerfiticate:
create certificate PRINCIPAL_cert
with subject = 'PRINCIPAL certificate',
start_date = '2007/11/01',
expiry_date = '2020/11/01';
- Create the endpoint corresponding to the certificate:
Create endpoint endpoint_mirroring state = started
as tcp (listener_port = 7024, listener_ip = all)
for database_mirroring (authentication = certificate PRINCIPAL_cert, encryption = disabled, role = all);
- Export the certificate to a separate file:
Backup certificate PRINCIPAL_cert to file = 'c: PRINCIPAL_cert.cer';
Do the same on the mirror server and witness, pay attention to changing the role = witness when needed.After creating the Endpoints and exporting the certificates on all 3 instances, returning to principal server:
- Create a login for the mirror server:
create login MIRROR_login with PASSWORD = 'abc123 !!';
GO
- Create a user corresponding to that Login
create user MIRROR_user from login MIRROR_login;
GO
- Create a certificate from the server's .cer file:
Create certificate MIRROR_cert
Authorization MIRROR_user
From file = 'c: MIRROR_cert.cer';
GO
- Granting permission to connect to the endpoint for the server's login mirror:
Grant CONNECT ON Endpoint :: endpoint_mirroring to [MIRROR_login];
GO
Do the same thing for the witness server certificate, as well as on mirror and witness servers so that 3 computers can identify and authenticate each other.
After creating the endpoints, you can check them with the query:
SELECT name, state_desc, role_desc
FROM sys.database_mirroring_endpoint
The final task is to initiate a session for the DM:
* On principal server:
ALTER DATABASE AdventureWorks
SET PARTNER = 'TCP: //mirror-srv.deltax.com: 5022'
GO
* On the mirror server:
ALTER DATABASE AdventureWorks
SET PARTNER = 'TCP: //pricipal-srv.deltax.com: 5022'
GO
* On principal server, set up a witness server:
ALTER DATABASE AdventureWorks
SET WITNESS = 'TCP: //witness-srv.deltax.com: 5022'
GO
After the system has been in operation, it can be monitored with Database Mirroring Monitor tool :
4. Programming middleware:
The use of DM is said to be almost transparent for database connection from the middleware side. If you use the ADO.NET library, simply modify the ConnectionString to add the ' failover partner ' field to the mirror server, for example:
Data Source = pricipal.database.com; Failover Partner = mirror.database.com; Initial Catalog = AdventureWorks;
Integrated Security = True;
In addition, ADO.NET creates a ' connection pool ' that allows caching of connections that have been initiated, so in the event of a problem that leads to a server switch, you need to actively implement additional operations. delete this cache.
SqlConnection.ClearPool (conn);
Epilogue
DM in SQL Server is quite simple, easy to configure, use and monitor, but its capabilities are relatively limited. It is only suitable for medium-sized and lower-level databases, and for large databases that have strict requirements for continuity, the proposed approach has not been met, but the overall operating system solutions are needed. hardware system, network.
The article hopes to help you get an overview of how to build a highly available database management system in SQL Server.
Reference: http://technet.microsoft.com/en-us/library/cc917680.aspx
Good luck.