Use the image of certificate configuration to install Microsoft order

http://msdn.microsoft.com/zh-cn/library/ms191140.aspx

Backup and restore should first be converted to full backup mode

 USE master;
GO
ALTER DATABASE
SET RECOVERY FULL;
GO

Direct backup database cannot be written directly C disc , Create your own catalog

 BACKUP DATABASE [AdventureWorks2012]
TO DISK = 'C:\HOSTA\AdventureWorks2012.bak'
WITH FORMAT
GO

Also, restore needs to specify , Be sure to use WITH NORECOVERY

 USE [master]
RESTORE DATABASE [AdventureWorks2012]
FROM DISK = N'C:\HOSTB\AdventureWorks2012.bak'
WITH NORECOVERY,
MOVE N'AdventureWorks2012_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2\MSSQL\DATA\AdventureWorks2012_Data.mdf',
MOVE N'AdventureWorks2012_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2\MSSQL\DATA\AdventureWorks2012_log.ldf'
GO

In fact, the Certificate creation and inversion part can be done step by step , Pay attention to the port number , The path is just the path . You can be lazy without changing your password , In principle, do not set the expiration date for the password used by the machine

HOSTA On

 USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<1_Strong_Password!>';
GO
USE master;
CREATE CERTIFICATE HOST_A_cert
WITH SUBJECT = 'HOST_A certificate';
GO
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=7024
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE HOST_A_cert
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
);
GO
BACKUP CERTIFICATE HOST_A_cert TO FILE = 'C:\HOSTA\HOST_A_cert.cer';
GO

HOSTB On

 USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Strong_Password_#2>';
GO
CREATE CERTIFICATE HOST_B_cert
WITH SUBJECT = 'HOST_B certificate for database mirroring';
GO
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=7024
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE HOST_B_cert
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
);
GO
BACKUP CERTIFICATE HOST_B_cert TO FILE = 'C:\HOSTB\HOST_B_cert.cer';
GO

HOSTA Certificate on copy To HOSTB On ,HOSTB Certificate on copy To HOSTA On , Then create the user import certificate

HOSTA

 USE master;
CREATE LOGIN HOST_B_login WITH PASSWORD = '1Sample_Strong_Password!@#';
GO
CREATE USER HOST_B_user FOR LOGIN HOST_B_login;
GO
CREATE CERTIFICATE HOST_B_cert
AUTHORIZATION HOST_B_user
FROM FILE = 'C:\HOSTA\HOST_B_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];
GO

HOSTB

 USE master;
CREATE LOGIN HOST_A_login WITH PASSWORD = '=Sample#2_Strong_Password2';
GO
CREATE USER
FOR LOGIN HOST_A_login;
GO
CREATE CERTIFICATE HOST_A_cert
AUTHORIZATION HOST_A_user
FROM FILE = 'C:\HOSTB\HOST_A_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];
GO

stay HOSTB Run first on , have access to IP Address or host name

 --At HOST_B, set server instance on HOST_A as partner (principal server):
ALTER DATABASE [AdventureWorks2012]
SET PARTNER = 'TCP://192.168.100.101:7024';
GO

Later on HOSTA Up operation

 --At HOST_A, set server instance on HOST_B as partner (mirror server).
ALTER DATABASE [AdventureWorks2012]
SET PARTNER = 'TCP://192.168.100.102:7024';
GO

The main library should be displayed as , The principal is synchronized , The standby database shows that it is recovering .

closed HOSTA, stay HOSTB Use the following statement to switch , It takes a few seconds . The database shows that the principal is disconnected

 USE master;
alter database [AdventureWorks2012] set partner FORCE_SERVICE_ALLOW_DATA_LOSS;

http://msdn.microsoft.com/zh-cn/library/bb522476.aspx

Recover at this time HOSTA, The database state is mirror , Hang up / Restoring ... stay HOSTB In the operation

 USE master;
alter database [AdventureWorks2012] set partner resume;

such HOSTB It becomes the subject , If you want to put HOSTA Set as principal in , stay HOSTB Run the statement on , Switch to a HOSTA The theme of

 USE master;
alter database [AdventureWorks2012] set partner resume;

Reference

http://msdn.microsoft.com/zh-cn/library/ms189852(v=sql.110).aspx

http://msdn.microsoft.com/zh-cn/library/ms189053.aspx

http://msdn.microsoft.com/zh-cn/library/ms189921.aspx

http://www.mssqltips.com/sqlservertip/2464/configure-sql-server-database-mirroring-using-ssms/

Configure Database Mirroring More articles about

  1. Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)

    Company one SQL Server The mirror has failed over ( Active standby switching ), Check SQL Server The reason for the active / standby switch of the image , The following errors were found in the error log : Date        2019/8/31 14:09:17   ...

  2. T-SQL Sentence creation Database Of SQL mirroring Relationship

    1 Certificate section :principle and secondary Do the same thing on the client side , Change the corresponding name that will do USE master; --1.1 Create the database Master Key, if ...

  3. Ubuntu On the configuration SQL Server Always On Availability Group(Configure Always On Availability Group for SQL Server on Ubuntu)

    Here's a brief introduction to how to Ubuntu Step by step create a SQL Server AG(Always On Availability Group), And the filling method of the pit encountered in the configuration process . Currently in Linux You can take it on ...

  4. Configure Always On Availability Group for SQL Server on RHEL——Red Hat Enterprise Linux On the configuration SQL Server Always On Availability Group

    Here's a brief introduction to how to Red Hat Enterprise Linux Step by step create a SQL Server AG(Always On Availability Group), And the pits encountered in the configuration process ...

  5. Configure Always On Availability Group for SQL Server on Ubuntu——Ubuntu On the configuration SQL Server Always On Availability Group

    Here's a brief introduction to how to Ubuntu Step by step create a SQL Server AG(Always On Availability Group), And the filling method of the pit encountered in the configuration process . Currently in Linux You can take it on ...

  6. Configure Always On Availability Group for SQL Server on Ubuntu

    Here's a brief introduction to how to Ubuntu Step by step create a SQL Server AG(Always On Availability Group), And the filling method of the pit encountered in the configuration process . Currently in Linux You can take it on ...

  7. SQL Mirroring[Hot back up with Double machine]

    Background: It's fairly common for businesses to want to provide some high availability for their SQ ...

  8. Database 2 Day DBA guide_Chapter2

    website:http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/2day_dba/install/install ...

  9. Lerning Entity Framework 6 ------ Defining the Database Structure

    There are three ways to define the database structure by Entity Framework API. They are: Attributes ...

Random recommendation

  1. BestCoder Round #90

    For the first time in my life COGS Playing outside of the city . Hang up dog 了 . The main reason is that I have no experience , In addition, the code ability is too weak . And the last blind hack Three times ,Too Young Too Simple...... Get down to business . ( Copy a question first ...

  2. How to self-study Android

    See a lot of people ask non science class how to learn programming , In fact, classes are basically self-taught . There is a saying " It's up to the master to lead him to practice ", No matter how powerful a teacher is, what he can teach you is very limited , The real practice still depends on oneself . The blogger is majoring in mathematics , Although research ...

  3. Inversion of control (Inversion of Control) My understanding

    About inversion of control (Inversion of Control), There are many other names in the concrete implementation , Such as dependence inversion (Dependency Inversion Principles, DIP). Dependency injection (Depend ...

  4. c# Force memory recovery

    [DllImport("psapi.dll")] private static extern int EmptyWorkingSet(int hProcess); GC.Colle ...

  5. [MySql] Set up UTF8, Question marks still appear in the Chinese database

    Run the command :SHOW VARIABLES LIKE 'character_set_%'; result 'character_set_client', 'utf8' 'character_set_connect ...

  6. Rapha&#235;l.js Learning notes

    Rapheal.js It's a vector drawing library . For support HTML5 SVG Browser usage for SVG mapping , I won't support it SVG Of IE(ie6,7,8) Use VML mapping . therefore Raphael.js Compatibility is very good . Raphael ...

  7. POJ 1873 The Fortified Forest [ convex hull enumeration ]

    The Fortified Forest Time Limit: 1000MS   Memory Limit: 30000K Total Submissions: 6400   Accepted: 1 ...

  8. 201421123042 《Java Programming 》 The first 11 Weekly learning summary

    1. This week's learning summary 1.1 In the way you like ( Mind mapping or something ) Summarize the related content of multithreading . 2. Written work This time PTA The problem set is multithreaded 1. Source code reading : Multithreaded program BounceThread 1.1 BallR ...

  9. webpack in hash、chunkhash、contenthash difference

    webpack There are three kinds of output file names in hash value : 1. hash 2. chunkhash 3. contenthash What's the difference between the three ? hash If you use both hash Words , Because it's engineering level ...

  10. C# Request.Params And Request.QueryString The difference between

    1.Request.Params contain Request.QueryString,request.form.request.cookies and request.servervariables. These kinds of searches will ...