Setting up Mirroring in SQL Server (MSSQL)

From ben.goodacre.name/tech

Jump to: navigation, search

SQL Mirroring is a native replication feature of SQL Server 2005 SP2+ which can setup two SQL servers in a automatic/hot failover capacity and thereby giving redundancy to each server.

Contents

Definition of Terms

The definition of terms for mirroring that Microsoft use are slightly different for that used by other systems. An example of this is shown with a comparison of Mirroring terms with MySQL:

SQL Server Mirroring MySQL
Principal Master
MirrorSlave
WitnessManagement
LogTransaction/Binary Log

Failover mechanism

The principal server answers queries as usual. A mirror server which is a standby/slave server which will become the principal should the principal stop working. A witness server which monitors the state of the principal server and will automatically setup the mirror server to become a principal on-the-fly.

Hardware required

To achieve automatic/hot failover three servers are required:

1 x Principal
1 x Mirror
1 x Witness

As mirroring is at the database level and not the instance level it is possible to mix principal databases and mirror instances on the same SQL Server instance and indeed the witness instance could be on either the principal or mirror but this would be meaningless given the witness' function of providing hot failover.

Setting up SQL Mirroring

Create databases on the Principal server and the Mirror server

It is recommended to create databases with the database and transaction log locations specifically stated as to avoid having to use a WITH MOVE context when restoring databases to either server. Full recovery (use transaction logs) must be enabled as mirroring itself utilises transaction logs to replicate data.

USE [master]
GO
CREATE DATABASE [ExampleDB] ON  PRIMARY 
( NAME = N'Data', FILENAME = N'D:\SQLDatabases\ExampleDB.mdf' , SIZE = 68544KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 LOG ON 
( NAME = N'Log', FILENAME = N'C:\SQLTransactionLogs\ExampleDB.ldf' , SIZE = 1024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
GO
alter database [ExampleDB] set recovery full

Migrate existing data

Migrate the exisitng data in any preferred manner, an example of which is shown below.

restore database [ExampleDB] from disk='\\server\share\ExampleDB.bak' with replace

Backup Database and Transaction Logs

Each database that will be a principal needs to be backed up. The backups used to migrate data to it initially cannot be used to migrate (restore) data to the mirror. Else errors relating to to the transaction logs on the remote copy either not being rolled forward to a point in time that is encompassed in the local copy of the transaction log or or being rolled forward to a point in time that beyond reach of the transaction log. The transaction logs needed to be backed up as otherwise when the data is restored to the mirror SQL will change the recovery model backup model back to 'simple.' It is recommended to backup the data to a share on the mirror, as shown below:

backup database [ExampleDB] to disk='\\SQLMirror\share\ExampleDB.bak'
backup log [ExampleDB] to disk='\\SQLMirror\share\ExampleDB.log'

Restore with no recovery and replace

The restore of DBs and transaction logs to the mirror server must be done without the rolling forward of any transaction logs or brought back online else it will be in a different state from the principal database. The replace option is required as MSSQL will refuse the restore as the backup is from a different server.

restore database [ExampleDB] from disk='\\SQLMirror\share\ExampleDB.bak' with norecovery, replace
restore log [ExampleDB] from disk='\\SQLMirror\share\ExampleDB.log' with norecovery, replace 

The database when viewed in Management Studio it will be shown as a Restoring... state.

Troubleshooting

Errors referring to the transaction logs either not being rolled forward to a point in time that is encompassed in the transaction log or or being rolled forward to a point in time that beyond reach of the transaction log are usually due to the backup and restore being done too far apart. The longer the amount of time between the principal being backed up and the restore done to the mirror the greater the chance of a problem occurring. In some cases just retrying the backup and restore can allow it to work. Always ensure that the backups are deleted before re-backing up further to setting up mirroring as if SQL has to overwrite files it can cause problems.

Create Endpoints

Mirroring cannot use the existing endpoint(s), a dedicated endpoint for mirroring must be configured.

Execute the following T-SQL on both the mirror, principal and witness:

CREATE ENDPOINT Mirroring
    STATE=STARTED 
    AS TCP ( LISTENER_PORT = 5123 ) 

    FOR DATABASE_MIRRORING 
    ( AUTHENTICATION = WINDOWS NTLM, ENCRYPTION = REQUIRED, ROLE = ALL

    )

The port number, in this case 5123 can of course be any non-privileged or already in use port.
See also: How to: Create a Mirroring Endpoint for Windows Authentication (Transact-SQL)

Enable Mirroring

Mirroring is configured through setting up a 'partner' on each database. The mirror databases' will have the principal as its partner and vica-verca. Each database also has the witness configured. The 'partner' parameter has no option to specify whether it is the principal or the mirror in the mirroring configuration, it is the order in which the T-SQL is applied that determines this. The first to receive the alter database command will become the mirror, the second will become the master.

An FQDN must be used as shown in the following T-SQL statements. The preferable way to achieve this is to setup the necessary internal DNS records. The hosts file can be used as an alternative but the default domain DNS suffix is not referenced by these commands.

--Run the following on the mirror:
ALTER DATABASE [ExampleDB] SET PARTNER = 'TCP://SQLPrincipal.company.local:5123'
ALTER DATABASE [ExampleDB] SET WITNESS = 'TCP://SQLWitness.company.local:5123'
--Run the following on the principal:
ALTER DATABASE [ExampleDB] SET PARTNER = 'TCP://SQLMirror.company.local:5123'
ALTER DATABASE [ExampleDB] SET WITNESS = 'TCP://SQLWitness.company.local:5123'

The principal database when viewed in Management Studio will be shown as a Principal, Synchronized state. The mirror will be shown as a Mirror, Synchronized / Restoring... state.

Troubleshooting

Error 1

Database Mirroring Transport is disabled in the endpoint configuration.
The mirroring endpoint has not been created, check your configuration

Error 2
An error occurred while starting mirroring.
------------------------------
ADDITIONAL INFORMATION:
Alter failed for Database 'exampledb'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.4035.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The server network address "TCP://SQLMirror.companyabc.local:5123" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4035&EvtSrc=MSSQLServer&EvtID=1418&LinkId=20476

Check firewalling, but this is usually not an issue with making a connection on the specified port. If you use the exact statements to create the endpoints as shown in this article there should be no problem. Check the application log on the machine that cannot be connected to.

Error 3

In the application log

Database Mirroring login attempt failed with error: 'Connection handshake failed.
There is no compatible encryption algorithm. State 22.'.  [CLIENT: 2.2.2.2]

This occurs when one endpoint is setup to do encryption and the other is not, or the algorithms are setup differently. The endpoint encryption configuration for both endpoints must match.

Error 4

When performing the final ALTER statement on the Principal:

Msg 1412, Level 16, State 0, Line 1
The remote copy of database "ExampleDB" has not been rolled forward to a point in time that is encompassed in the 
local copy of the database log.

This when the LSN (Log Sequence Number) of the mirror DB is less than the LSN of the principal. - If this were a DR scenario we would refer to it as a gap in the log chain. Ensure that the restore is done straight after the backup and that you are backing up your transaction log to a different file name!

Testing

--Run on current principal
USE master
GO
ALTER DATABASE [ExampleDB] SET SAFETY FULL
GO
ALTER DATABASE [ExampleDB] SET PARTNER FAILOVER
GO

--Run on new principal
USE master
GO
ALTER DATABASE [ExampleDB] SET SAFETY OFF
GO

This will setup the mirror as a principal.

Forced failover

If the principal has died and the witness for whatever reason has not setup the mirror to become a principal this can be done manually:

--Run on mirror if principal isn't available
USE
master
GO
ALTER DATABASE [ExampleDB] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
GO

This is not as gruesome as it sounds as it is rare for data loss to occur when issuing this T-SQL. - So long as mirroring was working up to the point that the principal died.

Failover database back to the principal following an outage finishing

DBs are never automatically failed back from the old-mirror-now-master following an outage finishing.

--Run on old-mirror-now-master (new principal)
alter database [ExampleDB] set partner failover

As this has to be run on every database individually it can be quicker to create a cursor loop. There are better ways of using a cursor though. Presuming that all of your DBs are setup for mirroring the following can be used:

Declare @mirrordbs table (dbname char(100))
Declare @fetcher as char(100)
Insert @mirrordbs
select name from sys.databases where name <> 'master' and name <> 'tempdb' and name <> 'msdb' and name <> 'tempdb' and name <> 'model'

DECLARE reader CURSOR FOR SELECT dbname FROM @mirrordbs
OPEN reader
FETCH NEXT FROM reader INTO @fetcher
WHILE @@FETCH_STATUS=0
BEGIN
  exec('alter database '+@fetcher+'set partner failover')
FETCH NEXT FROM reader INTO @fetcher
END

Troubleshooting

Orphaned User mapping

Symptom:

Error 15023: User already exists in current database.

User mapping setup was incorrect which resulted in orphaned permissions for some of the databases. This was fixed by:

USE [ExampleDB]
GO
EXEC sp_change_users_login 'Report'
EXEC sp_change_users_login 'Auto_Fix', 'user', NULL, 'pass'
EXEC sp_change_users_login 'update_one', 'user', 'user'
GO

http://blog.sqlauthority.com/2007/02/15/sql-server-fix-error-15023-user-already-exists-in-current-database/

Cannot open database requested by the login. The login failed.

If you are not using domain accs this problem could affect you: Despite having the same login details for both servers the credentials fail when the mirror is setup as a principal as the SID gets cached, and is normally randomly generated. Use different usernames for the two server to resolve this or create the login using a T-SQL command where the SID can be specified:

CREATE LOGIN WITH PASSWORD ="password",SID ="sid for same login on principal server" 

To retrieve the SID for each login from the principal server query the sys.sql_logins catalog view.

http://deepakrangarajan.blogspot.com/2008/02/failover-in-database-mirroring.html

http://bradmarsh.net/index.php/2008/07/29/sql-2005-mirroring-automatic-failover/

Config ASP.net 2.0+ applications to utilise Mirroring

The failover mechanism will not automatically change the database server your applications use. The failover partner parameter must be used in your connection string:

<Setting Name="ConnectionString" Value="data source=SQLPrincipal\PROD; failover Partner=SQLMirror\PROD; Initial Catalog=ExampleDB;uid=user;pwd=pass" />

http://www.connectionstrings.com/sql-server-2005

The Initial catalog parameter must be used in preference to the database parameter as 'database' is not recognised by the ActiveX Data Objects (ADO) SQL driver.

By configuring your application to use these settings, when the application makes a request to the database and the Principal server is not available, .NET will automatically send the request to the mirrored server. This should happen transparently so your website user should not notice any outage. Sessions should also be kept alive, depending upon your application.

If you're using .NET 1.1, this Failover Partner is not present, so you'll need to roll your own code to transfer the client. This could be a simple try...catch around the database connection, and in the catch, it retries using the mirrored server. Or use a script to substitute your connection string as shown below.

Failover mechanism

http://msdn.microsoft.com/en-us/library/ms366348.aspx

Allowing .NET 1.1 sites to recognise failover

.NET 1.1 sites do not have the failover partner parameter in the SQL connection string as it natively uses an older version of SQLClient and not the ODBC route as defined in .NET 2.0+ . Natively a .net 1.1 site cannot automatically recognise the failover and will still try to connect to the principal.

One way around this is to create a SQL agent alert on the mirror server to listen for a 1440 in the Application log. 1440 indicates that it is now the principal - indicating that a failover has occurred. This alert runs a job which runs a cmdexec step that can run a BAT script that substitutes the configuration file that contains your connection string with a file that points to the mirror.

Mechanics of auto failover: Principal to Mirror

  1. The SQLMirror has a SQL agent alert that listens for an event ID that indicates that any database has failed over to SQLMirror from SQLPrincipal.
  2. The SQL agent runs a job which has a cmdexec step that runs a bat file on SQLMirror: \\sqlmirror\c$\webConfigtosqlmirror.bat

This bat file substitutes the web.config files for all the sites. This script must be updated when new sites are created.

Notes

The SQL agent will trigger the script once per database as mirroring is at the database (not instace) level. In order to stop the IBConfigtoMinnie.bat script being run ~30 times the script iself only allows itself to be run once, as per this extract:

If exist BlockwebConfigFailovers.txt goto eof
if not exist BlockwebConfigFailovers.txt (
 echo Block future web.config failovers, delete me to undo >>BlockwebConfigFailovers.txt
)

In order for the failover script to be re-enabled the BlockwebConfigFailovers.txt file must be deleted. Accidentally not deleting this file post a manual failover back to SQLPrincipal following an outage will disable the automatic failover.

WebConfigtosqlmirror.bat script

@echo off
If exist BlockwebConfigFailovers.txt goto eof
if not exist BlockwebConfigFailovers.txt (
 echo Block future web.config failovers, delete me to undo >>BlockwebConfigFailovers.txt
)


if not exist g:\maptester.txt (
  net use g: \\1.1.1.1\WebConfigs
  if not exist g:\maptester.txt (
    echo %date% %time% Tried to map drive but could not find g:\maptester.txt on web1 >>web.configToMirror.log
    goto eof
  )
)

if not exist h:\maptester.txt (
  net use h: \\2.2.2.2\IBConfigs
  if not exist h:\maptester.txt (
    echo %date% %time% Tried to map drive but could not find h:\maptester.txt on web2 >>web.configToMirror.log
    goto eof
  )
)


call :checkfiles g:\client1\Config
call :checkfiles g:\client2\Config
call :checkfiles g:\client3\Config

call :checkfiles h:\client1\Config
call :checkfiles h:\client2\Config
call :checkfiles h:\client3\Config


goto eof


:checkfiles
if not exist %1\webSQLMirror.config (
  echo %date% %time% Cannot find webSQLMirror.config for %1 >>web.configToMirror.log
)


if not exist %1\webSQLPrincipal.config (
  echo %date% %time% Cannot find IBMinnie.config for %1 >>web.configToMirror.log
)
if exist %1\webSQLMirror.config (
  del %1\web.config
  copy %1\webSQLMirror.config %1\web.config
  echo %date% %time% Setup web.config for SQLMirror for %1 >>web.configToMirror.log
)

:eof

See Also

Monitor SQL Server using Nagios Show perfmon counters for Mirroring that can be monitored.

Links

Personal tools
Namespaces
Variants
Actions
Navigation
Toolbox