SQL Server 2012 Database Mirroring

SQL Server 2012 Database Mirroring

　　

The following configures database mirroring through a domain account:

First, the Environment preparation

1. Prepare the computer

AD02 is a domain controller/dns server (sz1card1.com), IP:192.168.2.218/24,AD02 installed as a domain controller, DNS service (no demo here)

W67 for the principal server, IP:192.168.2.67/24, install SQL Server 2012 Enterprise Core (no demo here)

W65 for the mirror server, ip:192.168.2.65/24, install SQL Server 2012 Enterprise Core (no demo here)

Note: The test environment shuts down all firewalls, preparing the database for mirroring as test library

2, test directly using Administrator domain control Administrator user and password (*********), it is recommended to create a new domain user account on the domain controller (to ensure that the account password never expires)

II. Deployment of experiments

1. On the SQL database of the W65, W67 Server, open Services in Administrative Tools (or SQL Server Configuration Manager), modify the properties of the instance of SQL Server and change the login status to set Sz1card1\administrator. After the modification, restart the SQL Server instance.

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M00/8A/FA/wKiom1g_7SqS1cjSAAEgtY_RGxU046.jpg-wh_500x0-wm_3 -wmp_4-s_3917873687.jpg "title=" 2.jpg "alt=" Wkiom1g_7sqs1cjsaaegty_rgxu046.jpg-wh_50 "/>

2, W65, W67 Server SQL database, right-click instance Select "Facet", select "Surface area Application Configurator"-Change the value of the property "remotedacenabled" to "True"

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M01/8A/FA/wKiom1g_7Tjgp2OKAAD3lkKyLlM424.jpg-wh_500x0-wm_3 -wmp_4-s_2403060675.jpg "title=" 1.jpg "alt=" Wkiom1g_7tjgp2okaad3lkkyllm424.jpg-wh_50 "/>

3, confirm that the W67 Server test database must be "full recovery model", Backup type Select "Full", click "OK", select backup Type "Transaction log", click "OK" to complete the backup of transaction log, complete backup and log backup, copy to W65 server ready to do restore

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M01/8A/F6/wKioL1g_7VGQ4NiNAADZ2u-NHMs044.jpg-wh_500x0-wm_3 -wmp_4-s_25607930.jpg "style=" Float:none; "title=" 3.jpg "alt=" Wkiol1g_7vgq4ninaadz2u-nhms044.jpg-wh_50 "/>

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M01/8A/F6/wKioL1g_7VHj2FQCAACc6Lib9H0774.jpg-wh_500x0-wm_3 -wmp_4-s_819412655.jpg "style=" Float:none; "title=" 4.jpg "alt=" Wkiol1g_7vhj2fqcaacc6lib9h0774.jpg-wh_50 "/>

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M02/8A/FA/wKiom1g_7VLhHn-jAACpw2z8KTs644.jpg-wh_500x0-wm_3 -wmp_4-s_2256292985.jpg "style=" Float:none; "title=" 5.jpg "alt=" Wkiom1g_7vlhhn-jaacpw2z8kts644.jpg-wh_50 "/>

4, W65 server Create test library (this database name must be consistent with the database name created in W67), and modified to "Full recovery Model", right click on the newly created database "test"--select "Task"-"Restore"-"database", select Device-Add, Select the backup file (including backup database and log) that you just copied from W67, click "OK", tick "overwrite existing database" on the "Options" page, restore status Select "Restore with NORECOVERY", uncheck "End log backup before Restore", click "OK", At this point the test database on W65 is in a restored state

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M02/8A/FA/wKiom1g_7XDjd_DiAAEHnL8Pg8A783.jpg-wh_500x0-wm_3 -wmp_4-s_1231036036.jpg "style=" Float:none; "title=" 6.jpg "alt=" Wkiom1g_7xdjd_diaaehnl8pg8a783.jpg-wh_50 "/>

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M02/8A/F6/wKioL1g_7XOQs4kSAAEo7LFUi2o969.jpg-wh_500x0-wm_3 -wmp_4-s_994204444.jpg "style=" Float:none; "title=" 7.jpg "alt=" Wkiol1g_7xoqs4ksaaeo7lfui2o969.jpg-wh_50 "/>

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M02/8A/F6/wKioL1g_7XXyBDr8AAD7z5Hnb24952.jpg-wh_500x0-wm_3 -wmp_4-s_1402089519.jpg "style=" Float:none; "title=" 8.jpg "alt=" Wkiol1g_7xxybdr8aad7z5hnb24952.jpg-wh_50 "/>

5, database mirroring configuration, in the principal server W67 right-click "Test" Select "Task"-"Mirror", click "Configure Security", follow-up wizard to configure the principal server configuration, the default image is synchronous (mirror mode type advantages and disadvantages can refer to Microsoft Official website documents)

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M00/8A/FA/wKiom1g_7amDqkevAAERwAM3_7E151.jpg-wh_500x0-wm_3 -wmp_4-s_609763241.jpg "style=" Float:none; "title=" 9.jpg "alt=" Wkiom1g_7amdqkevaaerwam3_7e151.jpg-wh_50 "/>

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M01/8A/FA/wKiom1g_7a_QizZBAAE94Pi5lwI043.jpg-wh_500x0-wm_3 -wmp_4-s_1062277981.jpg "style=" Float:none; "title=" 10.jpg "alt=" Wkiom1g_7a_qizzbaae94pi5lwi043.jpg-wh_50 "/>

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M01/8A/F6/wKioL1g_7bWizHtHAAFZ-wXg7wg266.jpg-wh_500x0-wm_3 -wmp_4-s_929945759.jpg "style=" Float:none; "title=" 12.jpg "alt=" Wkiol1g_7bwizhthaafz-wxg7wg266.jpg-wh_50 "/>

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M02/8A/FA/wKiom1g_7byCbWIkAAE3Zg50pYY351.jpg-wh_500x0-wm_3 -wmp_4-s_467688830.jpg "style=" Float:none; "title=" 13.jpg "alt=" Wkiom1g_7bycbwikaae3zg50pyy351.jpg-wh_50 "/>

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M00/8A/F6/wKioL1g_7cLiAqTfAAFoqCncu5I860.jpg-wh_500x0-wm_3 -wmp_4-s_386803748.jpg "style=" Float:none; "title=" 14.jpg "alt=" Wkiol1g_7cliaqtfaafoqcncu5i860.jpg-wh_50 "/>

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M02/8A/F6/wKioL1g_7dSilrzvAAEw5n2xXOk005.jpg-wh_500x0-wm_3 -wmp_4-s_4228253103.jpg "style=" Float:none; "title=" 15.jpg "alt=" Wkiol1g_7dsilrzvaaew5n2xxok005.jpg-wh_50 "/>

6. After the W67 image is successfully configured, start mirroring is started, and the test database on the W67 server is "subject, synchronized", W65 server mirror database test database is "mirrored, synchronized \ Restoring"

NOTE: The mirror database test is in the restoring state, is not read-write, as a failover switch, can not share read or write!

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M01/8A/F6/wKioL1g_7guDJc0lAAF0u8s6Zm4289.jpg-wh_500x0-wm_3 -wmp_4-s_4233067959.jpg "style=" Float:none; "title=" 17.jpg "alt=" Wkiol1g_7gudjc0laaf0u8s6zm4289.jpg-wh_50 "/>

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M00/8A/FA/wKiom1g_7hDQbKNRAAFHfYe1tuU577.jpg-wh_500x0-wm_3 -wmp_4-s_4269002063.jpg "style=" Float:none; "title=" 18.jpg "alt=" Wkiom1g_7hdqbknraafhfye1tuu577.jpg-wh_50 "/>

7, the W67 server can choose the test database right--task--Start the mirror monitor, view the synchronization status

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M00/8A/F6/wKioL1g_7injGfXhAAFImFtCWYM441.jpg-wh_500x0-wm_3 -wmp_4-s_1364220775.jpg "title=" 19.jpg "alt=" Wkiol1g_7injgfxhaafimftcwym441.jpg-wh_50 "/>

8, test: The main database in the test library, the Users table, add a record,

INSERT users VALUES (one, ' TEST ')

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M02/8A/FA/wKiom1g_7j6SIqb3AADaaNopl0k672.jpg-wh_500x0-wm_3 -wmp_4-s_2707073687.jpg "style=" Float:none; "title=" 20.jpg "alt=" Wkiom1g_7j6siqb3aadaanopl0k672.jpg-wh_50 "/>

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M02/8A/F6/wKioL1g_7kCioeGpAADDlAKWhJg954.jpg-wh_500x0-wm_3 -wmp_4-s_4027064543.jpg "style=" Float:none; "title=" 21.jpg "alt=" Wkiol1g_7kcioegpaaddlakwhjg954.jpg-wh_50 "/>

Note: Due to the mirror synchronization mode, and the current synchronization state is normal, the test data is not many, the data is quickly synchronized to the mirror library, but because the mirror library is not currently read and write, and cannot see the data just added, only the following analog failover mirror Library as the main library, verify the data.

9, manual switchover failure, W67 Server test database, right--task--mirror--"failover", the current W67 server test database status is "mirror, synchronized \ Restore", W65 Server test database, the principal database, the status of "principal, is synchronized "

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M01/8A/FA/wKiom1g_7oDCHqo7AAFzQtnEiIo818.jpg-wh_500x0-wm_3 -wmp_4-s_130953990.jpg "style=" Float:none; "title=" 22.jpg "alt=" Wkiom1g_7odchqo7aafzqtneiio818.jpg-wh_50 "/>

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M01/8A/F6/wKioL1g_7oGDe8JwAAD0i35pf3k800.jpg-wh_500x0-wm_3 -wmp_4-s_1640814166.jpg "style=" Float:none; "title=" 23.jpg "alt=" Wkiol1g_7ogde8jwaad0i35pf3k800.jpg-wh_50 "/>

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M00/8A/FA/wKiom1g_7oLyPV3eAAEr--nneo0902.jpg-wh_500x0-wm_3 -wmp_4-s_2280591.jpg "style=" Float:none; "title=" 24.jpg "alt=" Wkiom1g_7olypv3eaaer--nneo0902.jpg-wh_50 "/>

Note: The switchover is successful and the data is validated correctly

If: The main server, the outage situation! If the principal W65 is down at this point, the W67 Mirror library status is "Recovering" and is also inaccessible. At this point the command switch can only be performed manually, and the following command is executed in the Mirror Library Master System:

--this script does a failover of all the databases in a  database mirroring session   --to the mirror server. this  will be used in cases where the principal server is no  longer available   --and the mirrored databases have to  be brought online.     --note: run this script in  the MIRRORED server instance   SET NOCOUNT OFF     DECLARE  @strSQL  nvarchar ( --variable for dynamic sql statement)  - variable size should change depending on the     DECLARE  @strDatabasename  nvarchar ( --variable for destination directory)     declare mycursor cursor for --used for cursor allocation        SELECT name FROM master.sys.databases a       INNER JOIN master.sys.database_mirroring b      ON  A.database_id=b.database_id      where not mirroring_guid is  null      and mirroring_role_desc= ' MIRROR '    OPEN  MyCursor    FETCH Next FROM MyCursor INTO  @strDatabasename     while @ @Fetch_Status  = 0    BEGIN        ---run the alter database databasename set partner  force_service_allow_data_loss      set  @strSQL  =  ' alter  database  '  +  @strdatabasename +  '  set partner force_service_allow_data_loss '         EXEC sp_executesql  @strSQL             PRINT  ' bringing  '  +  @strDatabaseName  +  '  online '         PRINT  ' ======================================== '         FETCH Next FROM MyCursor INTO  @strDatabasename      end     close mycursor    deallocate mycursor

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M02/8A/FA/wKiom1g_7pazn46OAAD5-G71LRE677.jpg-wh_500x0-wm_3 -wmp_4-s_3536310907.jpg "title=" 25.jpg "alt=" Wkiom1g_7pazn46oaad5-g71lre677.jpg-wh_50 "/>

After successful execution, the W67 becomes the principal and the current state is "principal, disconnected" (because W65 has not yet recovered)

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M00/8A/FA/wKiom1g_7qWDiMLKAAGL-OCTszg562.jpg-wh_500x0-wm_3 -wmp_4-s_1475695670.jpg "title=" 26.jpg "alt=" Wkiom1g_7qwdimlkaagl-octszg562.jpg-wh_50 "/>

Resume startup W65 At this time, the test library status of "mirroring, hang", W67 for "body, hang", and need to manually in the "task"-----------click "Continue", then sync to the original mirror sync state!!

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M00/8A/F6/wKioL1g_7rySZakTAAGbUHSBl9Q564.jpg-wh_500x0-wm_3 -wmp_4-s_846223575.jpg "style=" Float:none; "title=" 27.jpg "alt=" Wkiol1g_7ryszaktaagbuhsbl9q564.jpg-wh_50 "/>

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M01/8A/F6/wKioL1g_7r_SZOgfAAGZtJLIU1M261.jpg-wh_500x0-wm_3 -wmp_4-s_2026749444.jpg "style=" Float:none; "title=" 28.jpg "alt=" Wkiol1g_7r_szogfaagztjliu1m261.jpg-wh_50 "/>

This article is from the "10,000-hour Law" blog, be sure to keep this source http://daisywei.blog.51cto.com/7837970/1878599

SQL Server 2012 Database Mirroring