by Richard Siddaway

Database mirroring was introduced with SQL Server 2005 and enhanced in SQL Server 2008. It is a method of replicating a database to another server. That server can be in the same location or a distant location. Mirroring provides database-level protection as it is configured at the database level rather than at the server.

In mirroring terminology, the source server is known as the principal and the target server is known as the mirror. An individual server can function as both a principal and a mirror. The individual databases on a server can be configured to mirror to different target servers.

There are two ways to configure mirroring: asynchronous for high performance and synchronous for high safety. In asynchronous mirroring, a transaction is committed on the principal as soon as it has been sent to the mirror. Conversely, in synchronous mirroring, the transaction isn’t committed on the principal until it has been written to the transaction log on the mirror database. This setup gives a higher level of protection but could impact performance.

A further level of complexity is added by the failover options. The options previously described require a manual or forced failover. A forced failover is used in disaster recovery situations where communication has been lost with the principal but synchronization may not be complete. This can lead to some data loss. However, if synchronous mirroring with a witness (think quorum in clustering) is configured, failover can be automatic if the application will support it.

Setting up mirroring is a straightforward task comprising the following steps:

1. Logons and jobs must be configured on the mirror server. Mirroring only replicates the database; it doesn’t configure any other part of the SQL Server configuration. It is not possible to replicate the Master or MSDB databases, so logons and jobs must be replicated prior to mirroring commencing. If this doesn’t happen, the users won’t be able to access the database in the event of a failover.
2. The principal database must be in full recovery mode so that log backups can be taken.
3. Back up the principal database.
4. Copy the backup to the mirror server.
5. Restore the database WITH NO RECOVERY.
6. Create mirroring end points.
7. Start mirroring.

It is assumed that the Windows Firewalls on the servers are either turned off (not recommended) or are configured to allow SQL Server traffic. The TCP port selected for use by the mirroring endpoints must also be open on the firewalls.

If there is only a single database to mirror, you can use the wizard to configure the mirroring. However, you still need to perform the backup and restore to seed the mirroring.

The following script can be used to back up the principal database.

## Invoke-Backup.ps1
## This uses the SMO assemblies to back up the AdventureWorksLT2008 database
## This is written for SQL Server 2008## load SMO assemblies
## use $null to prevent display of assembly load information
$null = [reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.ConnectionInfo”)
$null = [reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.SmoExtended”)
$null = [reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”)

clear-host

## set SMO variable
$Smo = “Microsoft.SqlServer.Management.Smo.”

$server = New-Object ($Smo + “Server”) “W08R2SQL08″

## database to back up
$bkup = New-Object ($smo + “Backup”)
$bkup.Database = “AdventureWorksLT2008″

## set backup device and type
$file = “C:BackupAdventureWorksLT2008.bak”

$bkup.Devices.AddDevice($file, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
$bkup.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database

This script uses SMO to perform this task. It would be equally feasible to modify the code to create an endpoint (see later) so that you use a TSQL command to perform the backup.

The first task is to load the SMO assemblies. If the SQL Server 2008 tools are loaded, you could use SQL Server PowerShell and avoid this step. The assembly names are slightly different in SQL Server 2005. I would put these steps in the profile if doing a lot of SQL Server work with PowerShell. Alternatively, use SQL Server PowerShell, which already has them loaded.

Next, define the server and a database to back up. The AdventureWorksLT2008 is a sample database available for download from www.codeplex.com. The file you will use as a backup is defined, and you configure the backup to be a full database backup and then invoke it.
Once you have a backup, you need to copy it to the target server.

Copy-Item -Path c:BackupAdventureWorksLT2008.bak -Destination w08r2sql08ac$Backup

This is readily achieved using Copy-Item. Note the use of a UNC path for the destination. The copy could be included in the backup script as a final step.

These scripts were run on the principal server but could be easily run from an administration workstation with the SQL Server tools installed or even the mirror server. It would also be possible to run the scripts using PowerShell remoting.

The next step is to restore the database on the mirror server.

## Invoke-Restore.ps1
## This uses the SMO assemblies to restore the AdventureWorksLT2008 database
## This is written for SQL Server 2008## load SMO assemblies
## use $null to prevent display of assembly load information
$null = [reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.ConnectionInfo”)
$null = [reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.SmoExtended”)
$null = [reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”)

## set SMO variable
$Smo = “Microsoft.SqlServer.Management.Smo.”

$server = New-Object ($Smo + “Server”) “W08R2SQL08A”

## database to restore
$rstre = New-Object ($smo + “Restore”)
$rstre.Database = “AdventureWorksLT2008″
$file = “C:BackupAdventureWorksLT2008.bak”

$rstre.Devices.AddDevice($file, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
$rstre.Action = [Microsoft.SqlServer.Management.Smo.RestoreActionType]::Database
$rstre.NoRecovery = $true

This is the inverse of the backup script. Note that you are using the mirror server name rather than the principal. The other important line is $rstre.NoRecovery = $true. This line ensures that the database is left in a state suitable for mirroring (that is, so that you can restore backup logs).

The three previous steps could be combined into a single script with the server and database names used as parameters. This would be beneficial if configuring mirroring for multiple databases on multiple principals and mirrors.

Mirroring endpoints are the connections used for communication and data transfer during mirroring. The endpoint is defined by a name and a TCP port. A server should only have a single endpoint defined.
The script to create endpoints takes a server name as a parameter. This example uses ADO.NET to connect to the server and run the creation statement. I’ve hardcoded the endpoint name and port, but they could be parameters as well.

param ([string]$server)
$conn = “Server=$server; Integrated Security=SSPI; Database=Master”
$cn = New-Object “System.Data.SqlClient.SqlConnection” $conn
$cn.Open()$cmd = New-Object “System.Data.SqlClient.SqlCommand”
$cmd.CommandType = [System.Data.CommandType]“Text”
$cmd.CommandText = “CREATE ENDPOINT endpoint_mirroring STATE = STARTED AS TCP ( LISTENER_PORT = 7022 ) FOR DATABASE_MIRRORING (ROLE=PARTNER)”
$cmd.Connection = $cn
$cmd.ExecuteNonQuery()
$cn.Close()

The script has to be run on both the principal and mirror server. You can use the script like this:

PS C:scripts> . .New-EndPoint.ps1 W08R2SQL08
-1
PS C:scripts> . .New-EndPoint.ps1 W08R2SQL08A
-1

A return of -1 is normal as data is not returned in these queries.

If the two SQL Servers involved use the same service accounts, you can initialize the mirroring. If the accounts are different or the servers are in different domains, you need to explicitly create a logon for the account on its opposite server and grant the connect permission on the endpoint.

Once that has been done, you can inform the mirror database that it has a mirroring partner.

$conn = “Server=W08R2SQL08A; Integrated Security=SSPI; Database=Master”
$cn = New-Object “System.Data.SqlClient.SqlConnection” $conn
$cn.Open()$cmd = New-Object “System.Data.SqlClient.SqlCommand”
$cmd.CommandType = [System.Data.CommandType]“Text”

$cmd.CommandText = “ALTER DATABASE AdventureWorksLT2008 SET PARTNER = ‘TCP://W08R2SQL08:7022′”
$cmd.Connection = $cn
$cmd.ExecuteNonQuery()
$cn.Close()

The script can be run like this:

PS C:scripts> . .Set-Mirror.ps1
-1

The final step is to start mirroring:

$conn = “Server=W08R2SQL08; Integrated Security=SSPI; Database=Master”
$cn = New-Object “System.Data.SqlClient.SqlConnection” $conn
$cn.Open()$cmd = New-Object “System.Data.SqlClient.SqlCommand”
$cmd.CommandType = [System.Data.CommandType]“Text”

$cmd.CommandText = “ALTER DATABASE AdventureWorksLT2008 SET PARTNER = ‘TCP://W08R2SQL08A:7022′”
$cmd.Connection = $cn
$cmd.ExecuteNonQuery()
$cn.Close()

Notice that these last two scripts are very similar; they just swap the server that is being connected to and the partner server that is being defined. It would be possible to combine these two steps into a single script.

I have separated out the steps involved in creating a mirrored database in SQL Server 2008 into several scripts to make the explanation easier. It is not a difficult exercise to combine these steps so that a single script is used to perform the backup, copy the file, and restore on the target server. It could then configure the endpoints and start mirroring.

## invoke backup
$bkup.SqlBackup($server)

## invoke restore
$rstre.SqlRestore($server)

About the Author

Richard Siddaway is Microsoft Practice Leader for Centiq Ltd, a Microsoft partner specialising in optimisation, measurement, management, and migration involving Microsoft technologies. With more than 20 years experience in various aspects of IT, Richard is currently concentrating on the Microsoft environment at an architectural level -- especially around Active Directory (AD), Exchange, SQL Server, and Infrastructure Optimisation. Much of his recent experience has involved migrating enterprises from Windows NT\Exchange 5.5 to Windows 2003\Exchange 2003. Richard has administration experience with several database systems, especially SQL Server. His initial programming background is still useful, as Richard is a very experienced scripter always looking for the opportunity to automate a process. Having discovered PowerShell 18 months ago, he has enthusiastically adopted it in preference to VBScript. Richard founded and currently leads the UK PowerShell User Group. Richard has presented to the Directory Experts Conference 2007, at various events at Microsoft UK, including the last Community Day, and for other UK User Groups. Richard can be contacted through email at rsiddaw@hotmail.com or via his blog at http://richardsiddaway.spaces.live.com/