Shulou(Shulou.com)06/01 Report--

Sql server 2012 database mirroring

The following configure database mirroring through a domain account:

I. Environmental preparation

1. Prepare the computer

AD02 is a domain controller / DNS server (sz1card1.com), and IP:192.168.2.218/24,AD02 is installed as a domain controller and DNS service (not demonstrated here)

W67 is the principal server, IP:192.168.2.67/24, install SQL SERVER 2012 Enterprise Core Edition (no demonstration here)

W65 is the mirror server, IP:192.168.2.65/24, install SQL SERVER 2012 Enterprise Core Edition (no demonstration here)

Note: all firewalls are turned off in the test environment, and the database for mirroring is test library.

2. The test directly uses the administrator domain control administrator user and password (*). It is recommended to create a new domain user account on the domain controller (make sure the password of the account never expires).

II. Deployment experiment

1. On the SQL database of W65 and W67 servers, open "Services" in "Administrative tools" (or use "SQL Server configuration Manager"), modify the properties of the SQL Server instance, and change the login identity to set sz1card1\ administrator. After modification, restart the SQL Server instance.

2. SQL database of W65 and W67 servers. Right-click the instance to select "aspect" and select "Peripheral Application Configurator"-change the value of the property "RemoteDACEnabled" to "True".

3. Confirm that the W67 server test database must be the "full recovery model", select "complete" for the backup type, click "OK" for the backup type, and click "OK" to complete the backup of the transaction log. After the full backup and log backup are complete, copy to the W65 server to prepare for restore.

4. W65 server creates a test library (the database name must be the same as the database name created in W67), and modify it to "full recovery model", right-click the newly created database "test"-select "Task"-"restore"-"Database", select device-> add, select the backup file just copied from W67 (including backup database and log), click "OK" Check "overwrite existing database" on the "options" page, select "RESTORE WITH NORECOVERY" for recovery status, uncheck "perform tail-log backup before restore", click "OK", and the test database on W65 is in a restored state.

5. Database mirroring configuration. Right-click "test" on the principal server W67, select "Task"-"Mirror", click "configure Security", and follow up the wizard to configure the principal server configuration. The default image is synchronous (the advantages and disadvantages of the mirror mode type can be found on Microsoft's official website).

6. After the W67 mirror is configured successfully, start mirroring, when the test database on the W67 server is "principal, synchronized", and the mirrored database test database on the W65 server is "mirrored, synchronized\ restoring"

Note: the mirror database test is in a restoring state and cannot be read or written. It can be used as a failover and cannot be read or written.

7. On the W67 server, you can select the test database right-click "Task" to start the mirror monitor to view the synchronization status.

8. Test: the principal database is in test library, users table, and add a record

INSERT users VALUES (11pr)

Note: due to the mirror synchronization mode, the current synchronization status is normal, and there is not much test data, the data will soon be synchronized to the image library, but because the image library cannot be read or written, you can not see the data just added. You can only use the following simulated failover image library as the main database to verify the data.

9. Manually switch over failure, W67 server test database, right-click-task-mirror-"failover". After the transfer is successful, the current W67 server test database status is "mirrored, synchronized\ restoring", W65 server test database is the principal database, the status is "principal, synchronized"

Note: the switch is successful and the data verification is normal.

If: the occurrence of the principal server, downtime situation! For example, the main body W65 is down at this time, and the status of the W67 mirror library is "recovering", which is also inaccessible. At this point, you can only perform the command switch manually, and execute the following command on 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 (50)-- variable for destination directory DECLARE MyCursor CURSOR FOR-- used for cursor allocation SELECT name FROM master.sys.databases an 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'

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

When the restore starts W65, the test library status is "Mirror, pending", and W67 is "Principal, suspend". You need to manually click "continue" in "Task"-Mirror-click "continue", and then the synchronization resumes to the original mirror synchronization state!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.