comprehensive explanation:
Because database mirroring is so fast, it is much more suitable for keeping a hot standby of a publisher database.
Database mirroring involves redoing every insert, update, and delete operation that occurs on the principal database onto the mirror database as quickly as possible. Redoing is accomplished by sending a stream of active transaction log records to the mirror server, which applies log records to the mirror database, in sequence, as quickly as possible. Unlike replication, which works at the logical level, database mirroring works at the level of the physical log record. Beginning in SQL Server 2008, the principal server compresses the stream of transaction log records before sending it to the mirror server. This log compression occurs in all mirroring sessions
There are two mirroring operating modes:
high-safety mode, high-performance mode
One of them, high-safety mode supports synchronous operation. Under high-safety mode, when a session starts, the mirror server synchronizes the mirror database together with the principal database as quickly as possible. As soon as the databases are synchronized, a transaction is committed on both partners, at the cost of increased transaction latency.
The second operating mode, high-performance mode, runs asynchronously. The mirror server tries to keep up with the log records sent by the principal server. The mirror database might lag somewhat behind the principal database. However, typically, the gap between the databases is small. However, the gap can become significant if the principal server is under a heavy work load or the system of the mirror server is overloaded.
2. restore mirror database with norecovery on both database and logs
3. if sql server runs under network service, it need to create login for machine
create login [domain\machine$] from windows with default_database=[a database, maybe master]
later, after an endpoint was created, connect privilege should be granted
grant connect on endpoint::endpointname to [domain\machine$]
If windows are in domain, it'd be easier to just run under same domain user, create
login in sql server for the domain user, and grant connect on endpoint to it.
4. on principle db, you can set up mirroring with wizard. if it's successful, the status should be
fully synchronized. Extra information will also be added in database name in the management studio, such as (Principle, Synchronized)
5. it can also be configured with scripts. here are some statements for reference.
restore database xxx from disk='xxx' with norecovery
restore log xxx form disk='xxx' with norecovery
create endpoint xxx state=started as tcp(listener_port=xxxx, listener_ip=all or xxx)
for database_mirroring (role=parnter|all)
create login [xxx] xxx
grant connect on endpoint:xxx to [xxx]
alter database xxx set partner='tcp://xxx.xxx.xxx:port'
--turn off mirroring
alter database xxx set partner off
drop endpoint mirroring
--check status
select * from sys.database_mirroring_endpoints
select * from sys.database_mirroring
OR USE DATABASE MIRRORING MONITOR which requires host name instead of ip
manual fail over
On the former principal, clients are disconnected from the database and in-flight transactions are rolled back.
1. using SSMS
2. using t-sql
use master
go
ALTER DATABASE database_name SET PARTNER FAILOVER
where database_name is the mirrored database.
Client side redirect
example:
"Data Source=A;Failover Partner=B;Initial Catalog=AdventureWorks;Integrated Security=True;"
Force service
ALTER DATABASE SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
No comments:
Post a Comment