Contents
Document Overview: 4
Assumptions. 4
Installing Microsoft MSQQL Server 2008 R2. 5
Background on Database Mirroring. 22
Database Mirroring Overview.. 22
Database mirroring is a simple strategy that offers the following benefits: 22
How Database Mirroring Works. 22
Transaction Safety and Operating Modes. 23
Operating Modes. 23
Transaction Safety. 23
Topology – Synchronous High-Safety Mode without Automatic Failover. 24
Role Switching. 24
Configuration of Database Mirroring. 25
Microsoft SQL Server Management Studio. 25
Using Microsoft Transact-SQL (T-SQL) {UNVERIFIED}. 27
Restoring a Backup Copy of the Principal Server’s Database on the Mirror Server. 29
Using Microsoft SQL Server Management Studio. 29
Using Microsoft Transact-SQL (T-SQL) {UNVERIFIED}. 33
Configuring Database Mirroring. 35
Using Microsoft SQL Server Management Studio. 35
Using Microsoft Transact-SQL (T-SQL) {UNVERIFIED}. 46
Administering MSSQL. 48
Managing Logins and Jobs After Role Switching. 48
Mirroring States. 49
How to Manually Failover the Database between Principal and Mirror. 50
Using Microsoft SQL Server Management Studio. 50
Using Microsoft Transact-SQL (T-SQL). 52
How to: Force Service in a Database Mirroring Session. 53
Using Microsoft Transact-SQL (T-SQL). 54
References: 54
Microsoft MSSQL Server 2008 R2 can be setup in a High Availability model in a few different ways. The simplest way to this with two server is to use database mirroring. This document will only cover how to setup database mirroring with manual failover in synchronous high-safety mode between two server running Microsoft MSSQL Server 2008 R2.
· A server is already installed with Microsoft Windows Server 2003 or 2007
· Microsoft Server 2008 R2 Software to perform the install.
· The two servers are in a workgroup and NOT a domain.
· The two servers are on the same IP network.
· Remote access has been setup for Administrator.
· Access to the administrator account.
WARNING: I have removed the server name from all images so make sure you pay close attention to what server you are connected to.
1. Make sure you have Microsoft .NET Framework 3.5 installed before you proceed.
2. Create the Service Accounts that will be used for each MSSQL process.
a. Start->Control Panel->Administrative Tools->Computer Management->Local Users and Groups
b. Use the table below for an example of the user names that need to be created for each SQL Service Account and what check boxes should be checked. You can use different passwords for each or the same one. Make sure you uncheck “User must change password at next logon”.
c. Right click “Users” under “Computer Management (Local)”->”System Tools”->”Local Users and Groups” and left click “New User”.
d. Below is a table that provides some recommend SQL Service Account names and the groups which they should belong to. The assignment to each group will be done during installation. If you don’t do it at installation you can do it afterwards, but it is a bit more complicated.
| Service Account |
Service Account Description |
Group Membership |
General Settings |
| sqlsa |
SQL Server Agent Service Account (mssql agent) |
· SQLServerSQLAgentUser$<servername>$<instancename>· Users |
· Password never expires· User cannot change password |
| sqlsde |
SQL Server Database Engine Service Account (mssqlserver) |
· SQLServerMSSQLUser$<servername>$<instancename>· Users |
· Password never expires· User cannot change password |
| sqlsas |
SQL Server Analysis Services Service Account |
· SQLServerMSASUser$<servername>$<instancename>· Users |
· Password never expires· User cannot change password |
| Sqlsrs |
SQL Server Reporting Services Service Account |
· SQLServerReportServerUser$<servername>$<instanceid>· Users |
· Password never expires· User cannot change password |
| Sqlffdl |
SQL Full-text Filter Daemon Launcher Service Account |
· SQLServerFDHostUser$<servername>$<instancename>· Users |
· Password never expires· User cannot change password |
3. The group membership bit you will not be able to do at this stage since these groups get created when you install Microsoft MSSQL Server 2008. You can see the memberships that should exists after installation if you look at the table above.
Let’s begin the install now we have SQL Service Accounts.
1. Locate the “Setup” file and double click it.
2. It is recommended to run “System Configuration Checker” before going forward, but not required.
3. This is the output from the “System Configuration Checker”.
4. After you have verified you meet all the requirements for “System Configuration Checker” click “Installation” on the left. On the right side click “New installation and add features to an existing installation”.
5. The first thing that happens is the install does some checks.
6. Click “Install”.
7. Some more checks. Click “Next”.
8. Enter your “Product key” and click “Next”.
9. Check “I accept the license terms” and click “Next”.
10. Choose “SQL Server Feature Installation” and click “Next”.
11. Click “Select All” and then click “Next”.
12. Yet another set of checks. Click “Next”.
13. Select “Default instance” unless you want to specifically name your instance.
14. Disk Space check is preformed. If you have enough space click “Next”.
15. This is where we use the SQL Service Accounts we created earlier. Refer to the table at the beginning of the section to properly populate these account names.
16. Choose “Mixed Mode (SQL Server authentication and Windows authentication)”, click “Add Current User”, and then click “Next”.
17. Click “Add Current User”, and then click “Next”.
18. Make sure “Install the native mode default configuration” and click “Next”.
19. Click “Next”.
20. Yet again we have more checks done. Click “Next”.
21. Click “Install” to do begin the actual installation process.
22. At the end of “Installation Process” click “Next”.
23. If everything went good you should have a similar screen. Click “Close” to finish.
Database mirroring is a primarily software solution for increasing database availability. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model.
 Note |
| You cannot mirror the master, msdb, tempdb, or model databases. |
Database mirroring maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine. Typically, these server instances reside on computers in different locations. One server instance serves the database to clients (the principal server). The other instance acts as a hot or warm standby server (the mirror server), depending on the configuration and state of the mirroring session. When a database mirroring session is synchronized, database mirroring provides a hot standby server that supports rapid failover without a loss of data from committed transactions. When the session is not synchronized, the mirror server is typically available as a warm standby server (with possible data loss).
- Increases data protection.
- Database mirroring provides complete or almost complete redundancy of the data, depending on whether the operating mode is high-safety or high-performance.
- Increases availability of a database.
In the event of a disaster, the database administrator has the alternative of forcing service (with possible data loss) to the standby copy of the database.
· Improves the availability of the production database during upgrades.
The principal and mirror servers communicate and cooperate as partners in a database mirroring session. The two partners perform complementary roles in the session: the principal role and the mirror role. At any given time, one partner performs the principal role, and the other partner performs the mirror role. Each partner is described as owning its current role. The partner that owns the principal role is known as the principal server, and its copy of the database is the current principal database. The partner that owns the mirror role is known as the mirror server, and its copy of the database is the current mirror database. When database mirroring is deployed in a production environment, the principal database is the production 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.
A database mirroring session runs with either synchronous or asynchronous operation. Under asynchronous operation, the transactions commit without waiting for the mirror server to write the log to disk, which maximizes performance. Under synchronous operation, a transaction is committed on both partners, but at the cost of increased transaction latency.
There are two mirroring operating modes.
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. This mode requires MSSQL Server 2008 Enterprise.
This document will only cover how to setup/administer the synchronous high-safety mode.
Whether an operating mode is asynchronous or synchronous depends on the transaction safety setting. If you exclusively use SQL Server Management Studio to configure database mirroring, transaction safety settings are configured automatically when you select the operation mode. Since SQL Server Management Studio is used in this document the end result will be synchronous.
If you use Transact-SQL to configure database mirroring, you must understand how to set transaction safety. Transaction safety is controlled by the SAFETY property of the ALTER DATABASE statement. On a database that is being mirrored, SAFETY is either FULL or OFF.
- If the SAFETY option is set to FULL, database mirroring operation is synchronous, after the initial synchronizing phase.
- If the SAFETY option is set to OFF, database mirroring operation is asynchronous. The session runs in high-performance mode.
All database mirroring sessions support only one principal server and one mirror server. The following figure shows the configuration of synchronous high-safety mode without automatic failover. The configuration consists of only the two partners. This configuration is shown in the following illustration.
When the partners are connected and the database is already synchronized, manual failover is supported. If the mirror server instance goes down, the principal server instance is unaffected and runs exposed (that is without mirroring the data). If the principal server is lost, the mirror is suspended, but service can be forced to the mirror server (with possible data loss).
| Note |
| Establishing a new mirroring session requires that all involved server instances run the same version of SQL Server. |
Within the context of a database mirroring session, the principal and mirror roles are typically interchangeable in a process known as role switching. Role switching involves transferring the principal role to the mirror server. In role switching, the mirror server acts as the failover partner for the principal server. When a role switch occurs, the mirror server takes over the principal role and brings its copy of the database online as the new principal database. The former principal server, if available, assumes the mirror role, and its database becomes the new mirror database. Potentially, the roles can switch back and forth repeatedly.
The following three forms of role switching exist.
· Automatic failover
This requires high-safety mode and the presence of the mirror server and a witness. The database must already be synchronized, and the witness must be connected to the mirror server.
The role of the witness is to verify whether a given partner server is up and functioning. The witness role is not discussed further in this document.
· Manual failover
This requires high-safety mode. The partners must be connected to each other, and the database must already be synchronized.
· Forced service (with possible data loss)
Under high-performance mode and high-safety mode without automatic failover, forcing service is possible if the principal server has failed and the mirror server is available.
In any role-switching scenario, as soon as the new principal database comes online, the client applications can recover quickly by reconnecting to the database.
WARNING: I have removed the server name from all images so make sure you pay close attention to what server you are connected to.
To configure mirroring you will need to have two databases that are identical. If you have not preformed a backup of your database you will want to follow these short steps.
1. Right click on your database in the “Object Explorer”, go to “Tasks”, and click on “Backup…”.
2. In the “Source” section select your “Database” in the dropdown.
In the “Backup set” section give the backup a name and provide a description if you like.
In the “Destination” section click “Add” and pick a location for your backup to be saved to.
| Note |
| The backup file name needs to end in “.bak”. |
Click “Ok” to start the backup.
3. Repeat Steps 1-2 but in the “Source” section change the “Backup Type” to “Transactional” and in the “Destination” section use the same file you used for the Full backup (the default backup setting is “appended to the backup file”).
4. Copy the backup file you just made or created before to the server that will start out as the “mirror server”.
1. Create a database/transactional log backup on the Principal server.
| Note |
| The default database recovery model is “Simple” but we want “Full”. |
On the Principal server set the recovery type for each database you wish to mirror.
USE master;
GO
ALTER DATABASE <DatabaseName>
SET RECOVERY FULL;
GO
Example:
PRINCIPAL SERVER:
USE master;
GO
ALTER DATABASE TempDB
SET RECOVERY FULL;
GO
2. Backup the Database on the Principal Server.
BackupType:
Full = Full Database Backup
Log = Transaction Log Backup
To do a Full Database Backup:
BACKUP DATABASE <DatabaseName>
TO DISK = 'C:<DatabaseName-BackupType>.bak'
WITH FORMAT
GO
Example:
PRINCIPAL SERVER:
BACKUP DATABASE TempDB
TO DISK = 'C:TempDB-Full.bak'
WITH FORMAT
GO
Backup the log database too.
BACKUP DATABASE TempDBLog
TO DISK = 'C:TempDBLog-Full.bak'
WITH FORMAT
GO
To do a Transactional Log Backup:
BACKUP LOG TempDB
TO DISK = 'C:TempDB-Log.bak'
GO
Backup the log database too.
BACKUP LOG TempDBLog
TO DISK = 'C:TempDBLog-Log.bak'
GO
Copy the backup file over to the Server2 that we want to be the Mirror server.
WARNING: I have removed the server name from all images so make sure you pay close attention to what server you are connected to.
1. Open “Microsoft SQL Server Management Studio” on the “mirror server”, right click “Databases”, and left click “Restore Database…”.
2. In the “Destination for restore” section populate the “To database:” box with the exact same database name that you preformed the backup on, on the principal server.
In the “Source for restore” section click the radio button “From device:” and click the button on the far right.
3. You should get a “Specify Backup” window. Click “Add”, locate the backup file you copied to this server, and click “Ok”. Click “Ok” one more time.
4. You should be back to the “Restore Database – <database name>” window. If you are you need to check the box in the restore column for the file you just finished added.
On the left hand window under “Select a page” click “Options”.
Make sure you select the transactional log and the full backup.
5. In the “Recovery state” section click the radio button for “Leave the database non-operational, and do not roll back uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY)” and click “Ok”.
6. You should now see your databases in a state of “Restoring”.
1. Restore database/transactional log backups.
| Assumption: |
| We are going to assume you have copied it over to Server2 and have it in the root of the C: drive. |
BackupType:
Full = Full Database Backup
Log = Transaction Log Database Backup
2. To Restore a Full Database Backup:
RESTORE DATABASE <DatabaseName>
FROM DISK = 'C:<DatabaseName-Full>.bak'
WITH NORECOVERY
GO
Restore Full Database Backup Example:
RESTORE DATABASE TempDB
FROM DISK = 'C:TempDB-Full.bak'
WITH NORECOVERY
GO
Restore the log database too.
RESTORE DATABASE TempDBLog
FROM DISK = 'C:TempDBLog-Full.bak'
WITH NORECOVERY
GO
3. To Restore a Transactional Database Backup:
RESTORE LOG <DatabaseName>
FROM DISK = 'C:<DatabaseName-Log>.bak'
WITH FILE=1, NORECOVERY
GO
Restore Transactional Log Backup Example:
RESTORE DATABASE TempDB
FROM DISK = 'C:TempDB-Log.bak'
WITH FILE=1, NORECOVERY
GO
Restore the log database too.
RESTORE DATABASE TempDBLog
FROM DISK = 'C:TempDBLog-Log.bak'
WITH FILE=1, NORECOVERY
GO
Restore Transactional Log Backup Example:
WARNING: I have removed the server name from all images so make sure you pay close attention to what server you are connected to.
1. If you are not already on the Principal server login now. Open “Microsoft SQL Server Management Studio” and click on the plus symbol next to “Databases”. The database(s) we are going to be mirroring should be visible. Right click on the database you want to setup database mirroring. Go to “Tasks” and then to “Mirror…”.
2. Click on “Configure Security”.
3. Click “Next”.
4. Choose the radio button “No” because we will not be using a witness server. Click “Next”.
5. The dropdown “Principal server instance” is likely already selected to be the name of the server you are connected to and if not make sure it is.
I used the default port “5022” for the “Listener port” but you can use a different port. If you have a firewall enabled on your servers that will be the Principal/Mirror server you will want to disable it first to make sure everything comes up. After everything is working you can enable it again and troubleshoot your rules by allowing the proper ports (i.e. 5022,etc).
Check “Encrypt data sent through this endpoint”.
The “Endpoint name” is a friendly name so name is so it makes sense to you. I normally name it like so <mirror server hostname> <DatabaseToMirror> Mirroring (i.e. Server2 TempDB Mirroring).
Click “Next”.
6. The dropdown “Mirror server instance” needs to be set to the name of the mirror server. After setting it click connect.
I used the default port “5022” for the “Listener port” but you can use a different port. If you have a firewall enabled on your servers that will be the Principal/Mirror server you will want to disable it first to make sure everything comes up. After everything is working you can enable it again and troubleshoot your rules by allowing the proper ports (i.e. 5022,etc).
Check “Encrypt data sent through this endpoint”.
The “Endpoint name” is a friendly name so name is so it makes sense to you. I normally name it like so <PrincipalServerHostname> <DatabaseToMirror> Mirroring (i.e. Server1 TempDB Mirroring).
Click “Next”.
7. Leave all of these boxes blank and click “Next”.
8. Click “Finish”.
9. MSSQL will create a Data Mirroring object under “Server Objects”->”Endpoints”->”Database Mirroring” on the Principal and Mirror servers and make the necessary database changes. If those changes are done successfully you should have a screen similar to what I have below.
Click “Close”.
10. You will receive a warning similar to the one shown below. Click “Start Monitoring” to tell MSSQL to attempt to initiate/complete the mirroring connection.
11. Click “Yes”. MSSQL is just warning you that you are not using a Fully Qualified Domain Name (FQDN). To use a FQDN you need to connect to everything using a FQDN via “Microsoft SQL Server Management Studio”. This also means you need to have a domain setup. This guide is intended for setup outside a domain.
12. If mirror was setup successfully you wil see the status window now says “Synchronized: the databases are fully synchronized.” If you have the same status I have described you can click “Ok”. If you do not you will need to trouble shoot the issue.
13. You should now see the following status “Principal, Synchronized” on the “Principal Server”.
14. You should now see the following status “Mirror, Synchronized / Restoring…” on the “MIrror Server”.
1. Create endpoints on both servers
CREATE ENDPOINT EndPointName
STATE=STARTED AS TCP(LISTENER_PORT = PortNumber, LISTENER_IP = ALL)
FOR DATA_MIRRORING(ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4)
Example:
PRINCIPAL SERVER:
CREATE ENDPOINT “Server2 TempDB Mirroring”
STATE=STARTED AS TCP(LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING(ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4)
MIRROR SERVER:
CREATE ENDPOINT “Server1 TempDB Mirroring”
STATE=STARTED AS TCP(LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING(ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4)
2. Set partner and setup job on mirror server
ALTER DATABASE DatabaseName SET PARTNER = N'TCP://PrincipalServer:PortNumber'
ALTER DATABASE TempDB SET SAFETY FULL
EXEC sys.sp_dbmmonitoraddmonitoring
| Note |
| The Database monitoring default interval is 1 minute. |
Example:
MIRROR SERVER:
ALTER DATABASE TempDB SET PARTNER = N'TCP://Server1:5022'
ALTER DATABASE TempDB SET SAFETY FULL
EXEC sys.sp_dbmmonitoraddmonitoring
GO
ALTER DATABASE TempDBLog SET PARTNER = N'TCP://Server1:5022'
ALTER DATABASE TempDB SET SAFETY FULL
EXEC sys.sp_dbmmonitoraddmonitoring
GO
3. Set partner and setup job on principal server
ALTER DATABASE DatabaseName SET PARTNER = N'TCP://MirrorServer:PortNumber'
ALTER DATABASE DatabaseName SET SAFETY FULL
EXEC sys.sp_dbmmonitoraddmonitoring
| Note |
| The Database monitoring default interval is 1 minute. |
Example:
PRINCIPAL SERVER:
ALTER DATABASE TempDB SET PARTNER = N'TCP://Server2:5022'
ALTER DATABASE TempDB SET SAFETY FULL
EXEC sys.sp_dbmmonitoraddmonitoring
GO
ALTER DATABASE TempDBLog SET PARTNER = N'TCP://Server2:5022'
ALTER DATABASE TempDB SET SAFETY FULL
EXEC sys.sp_dbmmonitoraddmonitoring
GO
Only content within the principal database is mirrored. Associated information in the master or msdb system databases cannot be mirrored. Such associated information includes jobs set up against the principal database and logins that are added to the principal server.
If such information is important to supporting role switching, the information should be duplicated at the mirrored site. If it is possible, after roles are switched it is best to programmatically reproduce the information on the new principal database. The most common issues are logins and jobs.
· Logins
For users to be able to access the database after a role switch, a login on the principal server that has permission to access the principal database, must also be defined on the mirror server. However, the master database cannot be mirrored. Therefore, if on the current principal server, you create a new login to this login for the principal database, you must do the same on the mirror.
The login of every user of the database must be manually defined on the mirror server and on the principal server. Otherwise, when the principal role switches and the former mirror server offers its database as the principal database, users whose logins are not defined on the former mirror cannot access the new principal. The users are orphaned.
· Jobs
Jobs, such as backup jobs, require special consideration. Typically, after a role switch, the database owner or system administrator must re-create the jobs for the new principal database.
When the former principal server is available, you should also delete the original jobs from the new mirror database. Jobs on the mirror database fail because it is in the RESTORING state and so it is unavailable.
| Note |
| The partners might be configured differently, with different tape drive letters or such. The jobs for each partner must allow for any such differences. |
During a database mirroring session, the mirrored database is always in a specific state (the mirroring state). The state of the database reflects the communication status, data flow, and the difference in data between the partners. The database mirroring session adopts the same state as the principal database.
Throughout a database mirroring session, the server instances monitor each other. The partners use the mirroring state to monitor the database. With the exception of the PENDING_FAILOVER state, the principal and mirror database are always in the same state.
The possible mirroring states of the database are as follows:
| Mirroring state |
Description |
| SYNCHRONIZING |
The contents of the mirror database are lagging behind the contents of the principal database. The principal server is sending log records to the mirror server, which is applying the changes to the mirror database to roll it forward.At the start of a database mirroring session, the database is in the SYNCHRONIZING state. The principal server is serving the database, and the mirror is trying to catch up. |
| SYNCHRONIZED |
When the mirror server becomes sufficiently caught up to the principal server, the mirroring state changes to SYNCHRONIZED. The database remains in this state as long as the principal server continues to send changes to the mirror server and the mirror server continues to apply changes to the mirror database.If transaction safety is set to FULL, automatic failover and manual failover are both supported in the SYNCHRONIZED state, there is no data loss after a failover.If transaction safety is off, some data loss is always possible, even in the SYNCHRONIZED state. |
| SUSPENDED |
The mirror copy of the database is not available. The principal database is running without sending any logs to the mirror server, a condition known as running exposed. This is the state after a failover.A session can also become SUSPENDED as a result of redo errors or if the administrator pauses the session.SUSPENDED is a persistent state that survives partner shutdowns and startups. |
| PENDING_FAILOVER |
This state is found only on the principal server after a failover has begun, but the server has not transitioned into the mirror role.When the failover is initiated, the principal database goes into the PENDING_FAILOVER state, quickly terminates any user connections, and takes over the mirror role soon thereafter. |
| DISCONNECTED |
The partner has lost communication with the other partner. |
To manually failover the database between the principal and mirror server you will need do the following.
WARNING: I have removed the server name from all images so make sure you pay close attention to what server you are connected to.
1. If you are not already on the Principal server login now. Open “Microsoft SQL Server Management Studio” and click on the plus symbol next to “Databases”. The database(s) we are mirroring. Right click on the database you want to failover to the mirror server. Go to “Tasks” and then to “Mirror…”.
2. If you simply click the button “Failover” the failover will happen. If you are mirroring the log database for your database you will want to failover that database first (i.e. database = TempDB, database log = TempDBLog).
When the mirrored database is synchronized (that is, when the database is in the SYNCHRONIZED state), the database owner can initiate manual failover to the mirror server. Manual failover can be initiated only from the principal server.
On the principal server first we will set the database context to the master database and then initiate an immediate transition of the mirror server to the principal role.
| Note |
| On the former principal, clients are disconnected from the database and in-flight transactions are rolled back (meaning lost). |
USE master
GO
ALTER DATABASE <DatabaseName> SET PARTNER FAILOVER
GO
Example:
PRINCIPAL SERVER:
Failover any <DatabaseName>Log databases first.
ALTER DATABASE TempDBLog SET PARTNER FAILOVER
Failover the main database after you have successfully failed over the Log database.
ALTER DATABASE TempDB SET PARTNER FAILOVER
 Caution |
| Forced service is strictly a disaster recovery method. Forcing service may involve some data loss. Therefore, force service only if you are willing to risk losing some data in order to restore service to the database immediately. If forcing service risks losing significant data, we recommend that you stop mirroring and manually resynchronize the databases.Forcing service suspends the session and starts a new recovery fork. The effect of forcing service is similar to removing mirroring and recovering the former principal database. However, forcing service facilitates resynchronizing the databases (with possible data loss) when mirroring resumes. |
In high-performance mode and high-safety mode without automatic failover, if the principal server fails while the mirror server is available, the database owner can make the database available by forcing service to fail over (with possible data loss) to the mirror database. This option is available only under all the following conditions:
Or said differently
We are going to assume Server1 (Principal Server) is dead and now we want to change Server2 (currently in a mirror mode) into a “Principal Server”. To do this we will have to execute a SQL Query because I cannot find any documentation on how to do it within “Microsoft SQL Server Management Studio”.
To check the current failover state and Safety Settings:
SELECT SD.name, SD.database_id, SD.state_desc, SDM.mirroring_state_desc, SDM.mirroring_role_desc, SDM.mirroring_safety_level_desc, SD.recovery_model_desc
FROM sys.database_mirroring AS SDM
JOIN sys.databases AS SD
ON (SDM.database_id = SD.database_id)
WHERE mirroring_guid IS NOT NULL
Example output (Principal Server):
Example output (Mirror Server):
To force service in a database mirroring session
Connect to the mirror server and issue the following statement:
ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
The mirror server immediately transitions to principal server, and mirroring is suspended.
Example:
MIRROR SERVER:
ALTER DATABASE TempDB SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
ALTER DATABASE TempDBLog SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
Technical Information:: http://technet.microsoft.com/en-us/library/ms187875.aspx
Gheek.net 