Powershell Script to Get SQL Instances Information Remotely

Retrieves SQL server information from a local or remote servers.
Also Pulls all instances from a SQL server and detects if in a cluster or not.
Local or remote systems to query for SQL information.
If specified, try to pull and correlate WMI information for SQL in matching up the service info to registry info.

Follow below process to execute the PS Script  :

Open Powershell ISE with Run as Administrator and copy the below script in powershell ISE and hit execute . After executing copy below command and paste it in powershell by changing server names :

Example :

Get-SQLInstance -Computername DC1   – for Single server information

Get-SQLInstance -Computername Server1, Server2 -WMI –  For multiple servers Information

PS script below :

Function Get-SQLInstance {
Retrieves SQL server information from a local or remote servers.

Retrieves SQL server information from a local or remote servers. Pulls all
instances from a SQL server and detects if in a cluster or not.

.PARAMETER ComputerName
Local or remote systems to query for SQL information.

If specified, try to pull and correlate WMI information for SQL

I’ve done limited testing in matching up the service info to registry info.
Suggestions would be appreciated!

Name: Get-SQLInstance
Author: Boe Prox, edited by cookie monster (to cover wow6432node, WMI tie in)
DateCreated: 07 SEPT 2013


Get-SQLInstance -Computername DC1

Version : 10.0.1600.22
isCluster : False
Computername : DC1
FullName : DC1
isClusterNode : False
Edition : Enterprise Edition
ClusterName :
ClusterNodes : {}
Caption : SQL Server 2008

Version : 10.0.1600.22
isCluster : False
Computername : DC1
isClusterNode : False
Edition : Enterprise Edition
ClusterName :
ClusterNodes : {}
Caption : SQL Server 2008

Retrieves the SQL information from DC1

#Get SQL instances on servers 1 and 2, match them up with service information from WMI
Get-SQLInstance -Computername Server1, Server2 -WMI

Computername : Server1
Edition : Enterprise Edition: Core-based Licensing
Version : 11.0.3128.0
Caption : SQL Server 2012
isCluster : False
isClusterNode : False
ClusterName :
ClusterNodes : {}
FullName : Server1
ServiceName : SQL Server (MSSQLSERVER)
ServiceState : Running
ServiceAccount : domain\Server1SQL
ServiceStartMode : Auto

Computername : Server2
SQLBinRoot : D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn
Edition : Enterprise Edition
Version : 10.50.4000.0
Caption : SQL Server 2008 R2
isCluster : False
isClusterNode : False
ClusterName :
ClusterNodes : {}
FullName : Server2
ServiceName : SQL Server (MSSQLSERVER)
ServiceState : Running
ServiceAccount : domain\Server2SQL
ServiceStartMode : Auto
Param (
[string[]]$ComputerName = $env:COMPUTERNAME,

Begin {
$baseKeys = “SOFTWARE\\Microsoft\\Microsoft SQL Server”,
“SOFTWARE\\Wow6432Node\\Microsoft\\Microsoft SQL Server”
Process {
ForEach ($Computer in $Computername) {

$Computer = $computer -replace ‘(.*?)\..+’,’$1′
Write-Verbose (“Checking {0}” -f $Computer)

#This is Boe’s code. He outputs it outright, I’m assigning to allInstances to correlate with WMI later
$allInstances = foreach($baseKey in $baseKeys){
Try {

$reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey(‘LocalMachine’, $Computer)
$regKey= $reg.OpenSubKey($baseKey)

If ($regKey.GetSubKeyNames() -contains “Instance Names”) {
$regKey= $reg.OpenSubKey(“$baseKey\\Instance Names\\SQL” )
$instances = @($regkey.GetValueNames())
ElseIf ($regKey.GetValueNames() -contains ‘InstalledInstances’) {
$isCluster = $False
$instances = $regKey.GetValue(‘InstalledInstances’)
ElseIf ($regKey.GetValueNames() -contains ‘InstalledInstances’) {
$isCluster = $False
$instances = $regKey.GetValue(‘InstalledInstances’)
Else {

If ($instances.count -gt 0) {
ForEach ($instance in $instances) {
$nodes = New-Object System.Collections.Arraylist
$clusterName = $Null
$isCluster = $False
$instanceValue = $regKey.GetValue($instance)
$instanceReg = $reg.OpenSubKey(“$baseKey\\$instanceValue”)
If ($instanceReg.GetSubKeyNames() -contains “Cluster”) {
$isCluster = $True
$instanceRegCluster = $instanceReg.OpenSubKey(‘Cluster’)
$clusterName = $instanceRegCluster.GetValue(‘ClusterName’)
$clusterReg = $reg.OpenSubKey(“Cluster\\Nodes”)
$clusterReg.GetSubKeyNames() | ForEach {
$null = $nodes.Add($clusterReg.OpenSubKey($_).GetValue(‘NodeName’))
$instanceRegSetup = $instanceReg.OpenSubKey(“Setup”)
Try {
$edition = $instanceRegSetup.GetValue(‘Edition’)
} Catch {
$edition = $Null
Try {
$SQLBinRoot = $instanceRegSetup.GetValue(‘SQLBinRoot’)
} Catch {
$SQLBinRoot = $Null
Try {
$ErrorActionPreference = ‘Stop’
#Get from filename to determine version
$servicesReg = $reg.OpenSubKey(“SYSTEM\\CurrentControlSet\\Services”)
$serviceKey = $servicesReg.GetSubKeyNames() | Where {
$_ -match “$instance”
} | Select -First 1
$service = $servicesReg.OpenSubKey($serviceKey).GetValue(‘ImagePath’)
$file = $service -replace ‘^.*(\w:\\.*\\sqlservr.exe).*’,’$1′
$version = (Get-Item (“\\$Computer\$($file -replace “:”,”$”)”)).VersionInfo.ProductVersion
} Catch {
#Use potentially less accurate version from registry
$Version = $instanceRegSetup.GetValue(‘Version’)
} Finally {
$ErrorActionPreference = ‘Continue’
New-Object PSObject -Property @{
Computername = $Computer
SQLInstance = $instance
SQLBinRoot = $SQLBinRoot
Edition = $edition
Version = $version
Caption = {Switch -Regex ($version) {
“^12” {‘SQL Server 2014’;Break}
“^11” {‘SQL Server 2012’;Break}
“^10\.5” {‘SQL Server 2008 R2’;Break}
“^10” {‘SQL Server 2008’;Break}
“^9” {‘SQL Server 2005’;Break}
“^8” {‘SQL Server 2000’;Break}
“^7” {‘SQL Server 7.0’;Break}
Default {‘Unknown’}
isCluster = $isCluster
isClusterNode = ($nodes -contains $Computer)
ClusterName = $clusterName
ClusterNodes = ($nodes -ne $Computer)
FullName = {
If ($Instance -eq ‘MSSQLSERVER’) {
} Else {
} | Select Computername, SQLInstance, SQLBinRoot, Edition, Version, Caption, isCluster, isClusterNode, ClusterName, ClusterNodes, FullName
} Catch {
Write-Warning (“{0}: {1}” -f $Computer,$_.Exception.Message)

#If the wmi param was specified, get wmi info and correlate it!

#Get the WMI info we care about.
$sqlServices = $null
$sqlServices = @(
Get-WmiObject -ComputerName $computer -query “select DisplayName, Name, PathName, StartName, StartMode, State from win32_service where Name LIKE ‘MSSQL%'” -ErrorAction stop |
#This regex matches MSSQLServer and MSSQL$*
Where-Object {$_.Name -match “^MSSQL(Server$|\$)”} |
select DisplayName, StartName, StartMode, State, PathName

#If we pulled WMI info and it wasn’t empty, correlate!

Write-Verbose “WMI Service info:`n$($sqlServices | Format-Table -AutoSize -Property * | out-string)”
foreach($inst in $allInstances){
$matchingService = $sqlServices |
Where {$_.pathname -like “$( $inst.SQLBinRoot )*” -or $_.pathname -like “`”$( $inst.SQLBinRoot )*”} |
select -First 1

$inst | Select -property Computername,
@{ label = “ServiceName”; expression = {
else{“No WMI Match”}
@{ label = “ServiceState”; expression = {
else{“No WMI Match”}
@{ label = “ServiceAccount”; expression = {
else{“No WMI Match”}
@{ label = “ServiceStartMode”; expression = {
else{“No WMI Match”}
Catch {
Write-Warning “Could not retrieve WMI info for ‘$computer’:`n$_”

else {

Questions Before Performing Migration

We have tried to list a number of areas that the DBA should be looking at during a database’s migration. To help the process, answers to a number of questions should also be sought:

  1. How many databases are involved in the migration?

    Obviously migrating ten different databases will entail more effort and planning than migrating only one database.

  2. What is the nature of the database(s) being migrated?

    This should help you decide the instance where the database should be migrated to. For example, you would not want to install a data-mart in a high traffic OLTP system. Similarly, you will not probably be bothering about backups and disaster recovery if your migration is part of a regular refresh process in a test environment.

  3. If the database is already in production, what timeframe will be available for the migration?

    Backup/restore or detach/attach will take some time and you would want to minimise this time as much as possible. Also, you may want to know what services and applications are accessing the source database and how they need to be stopped. For example if you are migrating a web based application, you will probably want to talk with your system administrators about shutting down Apache or IIS. Users will need to be made aware of any system unavailability.

  4. Do you need to consider the size and growth characteristics of the database once it has been migrated?

    If the database has been growing fast and it needs to be migrated because the existing hardware resources are insufficient for the performance required, you will probably be changing the database file size, growth increments etc. once it has been migrated. You will also probably be tuning CPU and memory in the new instance.

  5. Has the destination server enough hardware resources (disk, CPU, memory, network capacity) to serve the new, migrated database? Will the database be competing for resources with any existing database application?

    Management may want you to install a new instance for the database in a server where one or more instances could already be running. You need to be aware (and make management aware) that additional instances will consume additional hardware resources.

  6. If the destination server is a separate physical system, does it have an optimal disk configuration?

    If your old database server had separate physical disks for data, log, backup and tempdb files, you probably would not want to migrate it to a server where only two physical drives of C: and D: are present.

  7.  What other “peripheral” components will be involved with the migration of the database?

    As we mentioned at the very beginning, migrating a database application can be a complex task. Nevertheless, you will still have to go through the process of migrating jobs, logins, custom error messages, file shares, reporting services solutions, SSIS or DTS packages etc.

  8. What user accounts will be accessing the migrated database? Will there be any new user accounts? Will there be any fewer accounts?

    When migrating a development database to a production server, you may need to account for end-users who were not involved during the testing phase and whose user accounts did not exist in the development database.

  9. Will the migrated database have any special backup or disaster recovery requirements?

    Business requirements may dictate a database to be backed up more frequently than other databases in the same instance.

  10. Is there any provision for post migration testing? Have test cases been developed?

    You would want to ensure everything has been migrated and all configurations have been checked before switching the applications to the new database.


Replication Topology


Transactional replication is the mechanism that Microsoft® SQL Server® provides to publish incremental data and schema changes to subscribers. The changes are published (the replication stream) in the order in which they occur, and typically there is low latency between the time the change is made on the Publisher and the time the change takes effect on the Subscriber. This enables a number of scenarios, such as scaling out a query workload or propagating data from a central office to remote offices and vice-versa. This form of replication always uses a hierarchical hub and spoke topology.

The addition of peer-to-peer transactional replication in SQL Server 2005 simplifies the implementation of a bi-directional transactional replication topology, where the replication stream flows both ways. In this topology, any participating node may read or update the data. Properly partitioned modifications are propagated between all nodes in a full mesh topology (as shown in Figure 1), allowing the data to be highly available in the event that one server is unavailable. This feature has been further improved in SQL Server 2008 with conflict detection and online changes for peer-to-peer topologies.

Transactional replication topologies can be made more resilient to server failures, and hence more highly available, by adding redundant copies of the various databases involved. This is especially important for hub and spoke topologies. Care must be taken, however, because replication is reliant on the server names of the servers in the topology, so any failover to another server can result in the replication stream being broken.

Various mechanisms in SQL Server provide database-level redundancy, such as backup/restore, log shipping, and database mirroring (in SQL Server 2005 and later). Database mirroring is the only mechanism that provides a real-time, exact copy of the protected database with the guarantee of zero data loss (when the mirror is synchronized).

This white paper describes how to use database mirroring to increase the availability of the replication stream in a transactional environment. It covers setting up replication in a mirrored environment, the effect of mirroring partnership state changes, and the effect that mirroring failovers have on replication. In addition, it describes how to use LSN-based initialization to recover from the failover of a mirrored subscriber database.


Transactional Replication Architecture

Transactional replication and peer-to-peer replication use the same architecture to move changes between the servers in a replication topology. The following illustration is an overview of the components involved in transactional replication.

A minimum of three server roles are required for transactional replication:

  • Publisher, hosting the publication database
  • Distributor, hosting the distribution database
  • Subscriber, hosting the subscription database

Depending on the complexity of the replication topology, there may be multiple Subscriber servers or, in the case of peer-to-peer replication, multiple peer servers with the replication stream flowing in both directions between the peers. Furthermore, the roles of the various replication servers can be played by one server or by individual servers (the more common case), and it is possible for a server to play any combination of roles. Regardless, the various servers and databases must be protected to ensure that the replication stream is highly available.

Transactional replication relies on various agents to perform the tasks associated with tracking changes and distributing data. These agents are:

  • Snapshot Agent, which runs at the Distributor. This agent prepares schema and initial data files of published tables and other objects, stores the snapshot files, and records information about synchronization in the distribution database.
  • Log Reader Agent, which runs at the Distributor. This agent connects to the Publisher and moves transactions marked for replication from the transaction log of the publication database to the distribution database.
  • Distribution Agent, which runs at the Distributor for push subscriptions, and at the Subscriber for pull subscriptions. This agent applies the (optional) initial snapshot to the Subscribers and moves transactions held in the distribution database to Subscribers
  • Queue Reader Agent, which runs at the Distributor. This agent is only used for transactional replication with updateable subscriptions and moves changes made on the Subscribers back to the Publisher.

It should be mentioned that there is also a Merge Agent, but it is used only for merge replication, which is not covered in this paper.

This white paper focuses mainly on the Log Reader Agent and the Distribution Agent.

For more detailed information on SQL Server Replication, see the following “SQL Server Replication” topics in SQL Server Books Online:

Database Mirroring Architecture

Database mirroring works at the database level and provides a single copy of the mirrored database that must reside on a different server instance, usually on a separate physical server in a different location. 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 of the database mirroring session and the mirroring state of the mirrored databases. The two servers are said to be partners in the mirroring session.

When the mirrored database is synchronized, database mirroring provides a hot standby server that supports rapid failover without loss of data from committed transactions. When the database is not synchronized, the mirror server is typically available as a warm standby server (with possible data loss).

A database mirroring session runs with either synchronous or asynchronous operation. Under asynchronous operation (also called high-performance mode), transactions commit without waiting for the mirror server to write the log to disk, which maximizes performance. Under synchronous operation (also called high-safety mode), a transaction is committed on both partners, but at the cost of increased transaction latency. In high-safety mode, it is possible to allow automatic failovers by adding a third witness server. In all other configurations, failovers must be performed manually.

The transaction safety level of a mirroring session is controlled by the SAFETY property of the ALTER DATABASE statement. Synchronous mirroring is when SAFETY is FULL; asynchronous is when SAFETY is OFF.

For example, the mirrored database is Db_1, with data flowing from the principal to the mirror server. Database mirroring maintains an exact copy of the database on the mirror server by 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 every active transaction log record to the mirror server, which applies log records to the mirror database, in sequence, as quickly as possible. Unlike replication, which works at the transaction level (by harvesting and forwarding INSERT, UPDATE, and DELETE operations from the transaction log of the publication database), database mirroring works at the level of the physical log record (by sending the actual log records to the mirror server).

This white paper discusses mirroring the publication and subscription databases, and details the behavior of the various replication agents in each case.

For more detailed information on database mirroring, see the “Database Mirroring” topic in SQL Server Books Online:

Deploying Database Mirroring and Replication Together

The degree to which database mirroring can be combined with transactional replication depends on which replication database is being considered, as the level of support varies by database. Peer-to-peer replication with database mirroring is not supported. The following table lists the replication databases and corresponding level of integration with database mirroring.

Replication database Integration with database mirroring
Distribution No. Not a supported combination of technologies.
Publication Yes, with automatic failover of replication agents.
Subscription Yes, with manual failover and configuration of the replication agent.

Table 1: Mirroring support for the various replication databases.

The degree to which database mirroring is supported depends on whether the replication agents that connect to it are designed to cope with a mirroring failover.

The distribution database is the simplest to consider. The distribution database is also where the replication configuration is stored, and it is tightly coupled with the server name where replication is configured. Hence, any failover of the distribution database cannot be tolerated and so it cannot be mirrored. In the interests of high availability, this means that it is advisable to configure the Distributor to be on a different physical server from the Publisher (called a remote Distributor) rather than on the same server (called a local Distributor). If a remote Distributor is configured, it should be clustered for maximum availability.

All replication agents that connect to the publication database (that is, Snapshot Agent, Log Reader Agent, Queue Reader Agent, and for completeness, the Merge Agent) are mirroring-aware. They can be configured such that if a mirroring failover occurs, they automatically reconnect to the new principal server, and then replication continues. Therefore, mirroring the publication database is fully supported, but the state of the mirroring partnership and the specific failover scenario can affect the behavior of the Log Reader Agent. This is discussed more fully in the next section, Mirroring the Publication Database.

The subscription database is more complicated. None of the agents involved in replication are designed to cope with a failover of the subscription database. Prior to SQL Server 2008, the only way you could reliably restart the replication stream using supported methods was to drop and recreate the subscription to utilize the new principal Subscriber database, or you needed to bring back the original principal within the replication retention period. SQL Server 2008 added the capability to create a subscription to a Subscriber mirror using an LSN as the starting point from which to start synchronization. This allows you to use the data stored in the mirror as of the last synchronization before the principal failed. Initializing a subscription from an LSN can significantly reduce the time needed to return the Subscriber to service compared to full initialization of the Subscriber from a snapshot or backup. To use the method described in Mirroring the Subscription Database, the Publisher, Distributor, and Subscriber must all use SQL Server 2008 or later.

This white paper looks at some of the concepts, best practices, and steps that help redirect the Distribution Agent’s subscription database connection to the mirror. These steps allow a mirrored subscription database to exist in the topology and a failover of the subscription database to be achieved with minimal downtime.

Important: Care should be taken to ensure that database mirroring will perform as expected. For more information, see Database Mirroring Best Practices and Performance Considerations on Microsoft TechNet.

Mirroring the Publication Database

Configuring Replication with a Mirrored Publication Database

This section provides an overview of the steps to configure replication when the publication database is mirrored.

Note:  This mechanism does not work for peer-to-peer topologies, as the Publishers are also Subscribers.

For the purposes of this section, assume the following environment, with database mirroring already configured:

  • SERVERPP – the database mirroring principal server and initial replication Publisher
  • Database TicketOrdersPub – the publication database that is mirrored
  • Publication name TicketOrders
  • SERVERPM – the database mirroring mirror server
  • SERVERD – the replication Distributor
  • SERVERS – the replication Subscriber
  • Database TicketOrdersSub – the subscription database

Replication can be configured by using either SQL Server Management Studio or Transact-SQL.

To configure replication with a mirrored publication database

  1. Configure SERVERD as the Distributor for SERVERPP and SERVERPM

It is highly recommended that you use a remote Distributor to increase the availability of the replication stream. If a local Distributor is used and the Publisher becomes unavailable, the replication stream is also unavailable.

  1. On SERVERD:
  • Configure SERVERD to be a Distributor, with a named distribution database and working directory.
  • Add SERVERPP and SERVERPM as Publishers.
  1. On both SERVERPP and SERVERPM, configure SERVERD to be the Distributor, using the distribution database and working directory previously specified.

Note:  The principal and mirror must be configured to use the same Distributor, with identical settings.

For more information on configuring a Distributor, see the following SQL Server 2008 Books Online topics:

  1. Create a publication and configure replication:
    1. On SERVERPP, create a publication using snapshot, transactional, or merge replication.
    2. On SERVERS, create a push or pull subscription.
  2. To configure the replication agents for failover, set the –PublisherFailoverPartner parameter for any of the following replication agents that exist:
  • Snapshot Agent
  • Log Reader Agent
  • Queue Reader Agent
  • Merge Agent
  • SQL Server Replication Listener (replisapi.dll)
  • SQL Merge ActiveX Control

The recommended method for specifying this parameter is to include it in an agent profile. Create a custom profile by using a system-defined profile as a template, and then add this parameter to the profile. The custom profile can then be used when running the replication agents.

For more information, see Replication and Database Mirroring in SQL Server 2008 Books Online.

Effect of the Mirroring State on the Replication Log Reader

When a publication database is mirrored, Log Reader Agent behavior is governed by the mirroring state of the database. By default, the Log Reader Agent can only replicate log records that have been hardened in the log on the mirror server (the process in which the mirror server writes a transaction log record to the transaction log file is termed hardening the log). This means it cannot replicate log records with a Log Sequence Number (LSN) that is higher than the LSN of the last log record that was hardened on the mirror.

It is possible for the principal to become exposed (the mirror is accessible but there are log records that have not yet been hardened on the mirror) or isolated (the mirror is inaccessible). In both cases, if the principal is still able to serve the database, any changes made to the database are not replicated until the corresponding log records are hardened on the mirror.

This behavior introduces latency in the replication stream, and is done so that if a mirroring failover occurs, it is guaranteed that replication Subscribers cannot be “ahead” of the new principal database.

When the Log Reader Agent is waiting for transaction log records to be hardened on the mirror, the following message is entered in the Log Reader Agent history:

Replicated transactions are waiting for next Log backup or for mirroring partner to catch up.

The following table lists the default Log Reader Agent behavior for various mirroring states. The information assumes that the principal is able to serve the database (either because there is no witness server, or there is a witness and there is a quorum).

Safety Level Initial Mirroring State Event Resulting State Principal Exposed? Log Reader Agent Behavior
FULL or OFF Mirroring partnership established SYNCHRONIZING N Waiting (for synchronization to complete)
FULL or OFF SYNCHRONIZING Synchronization completes SYNCHRONIZED N Running
FULL or OFF SYNCHRONIZED Session is paused SUSPENDED Y Waiting
FULL or OFF SYNCHRONIZED Redo errors on the mirror SUSPENDED Y Waiting
FULL or OFF SUSPENDED or DISCONNECTED Session is resumed SYNCHRONIZING Y Waiting (for synchronization to complete)
OFF SYNCHRONIZED SEND queue grows SYNCHRONIZING Y Switching between Waiting and Running

Table 2: Default Log Reader Agent behavior in various mirroring states.

Changing the Replication Log Reader Behavior by Using Trace Flag 1448

In some situations, the effect of mirroring on the latency of the replication stream is not acceptable. A new trace flag, 1448, allows replication to continue even when the principal database is running exposed or is isolated. In such situations, the Log Reader Agent usually waits for log records to harden on the mirror before replicating them to the Distributor. When the server is started with trace flag 1448 enabled, this restriction is removed so the Log Reader Agent can continue replicating changes regardless of the mirroring state.

The trace flag is available in SQL Server 2008, and as a hot-fix in SQL Server 2005. For more information on obtaining the fix that implements this trace flag in SQL Server 2005, and how to enable the trace flag in either version, see Knowledge Base article 937041.

Note:  Although replication latency may be of paramount importance, and so it may seem worthwhile to always have this trace flag enabled in configurations involving database mirroring and replication, be aware that there are some situations where using this trace flag could cause issues after a mirroring failover. These are explained in the next section.

Effect of a Mirroring Failover on the Replication Log Reader

When a mirroring failover occurs, whether automatically or manually, the Log Reader Agent should automatically connect to the new principal server and continue replicating transactions (as long as the –PublisherFailoverPartner parameter was set correctly, as described above).

There are two situations in which this may not happen. The first is where the failover does not succeed because the mirror server is unable to become the new principal for some reason. This is described in Log Reader Agent Behavior if the Mirroring Partnership is Broken later in this paper. The second case can only occur when trace flag 1448 is enabled, as described in this section.

In a mirroring session where transaction safety is set to OFF, or the principal is running exposed or isolated, it is possible for there to be committed transactions on the principal that have not yet been hardened on the mirror. If the principal database goes offline and the mirror is brought online by manually forcing service with failover, it is recovered with data loss (transactions that were committed on the principal but not yet hardened on the mirror are essentially lost to the application when the mirror becomes the new principal).

Furthermore, if trace flag 1448 is enabled and this situation arises, it is possible that some of the committed transactions from the old principal may have been replicated but not hardened on the mirror. This means that the Distributor is effectively “ahead” of the new principal when it comes online after the manual failover. This causes the Log Reader Agent to fail with the following message:

The process could not execute ‘sp_repldone/sp_replcounters’ on ‘SERVERB’.

There will also be a more detailed message showing the log sequence number that the Log Reader Agent tried to read. This indicates that the Log Reader Agent has already retrieved log records further ahead in time than the new principal has in its transaction log.

The Log Reader Agent can be recovered by using the sp_replrestart stored procedure, which re-synchronizes the metadata between the Publisher and the Distributor. This can result in Subscribers potentially having more data than the Publisher. In the event that these changes are made to the publication database again and subsequently replicated, the Distribution Agent will fail with data consistency errors. To override these errors use the Distribution Agent profile “Continue on data consistency errors.”

Note:  Care should be taken when overriding these errors. For more information, see Skipping Errors in Transactional Replication in SQL Server 2008 Books Online.

Log Reader Agent Behavior if the Mirroring Partnership is Broken

As previously described, the Log Reader Agent will failover gracefully to the new mirroring principal server, providing the –PublisherFailoverPartner parameter is configured correctly. As long as the mirroring partnership remains in place, the Log Reader Agent can even be restarted after a mirroring failover and it will still connect to the correct server (the new principal server).

If the original principal server is likely to be unavailable for a long period of time, it is common practice to remove mirroring so that problems with transaction log growth on the mirror database do not occur. If the mirroring partnership is broken after a failover occurs, the Log Reader Agent continues to function as long as it is running at the time that mirroring is removed. If it is subsequently restarted, the Log Reader Agent tries to connect to the original principal server, which is no longer serving as the Publisher, and fails. The Log Reader Agent history will then contain a message such as:

User-specified agent parameter values: -Publisher SERVERA

There will also be more detailed messages about the connection failure.

To force this situation for testing purposes:

  1. Configure database mirroring and replication.
  2. Manually fail over from Server A to Server B.
  3. On Server B, remove mirroring.
  4. On Server D (the Distributor), stop and restart the Log Reader Agent job.
  5. Observe the failure by looking at the job’s history.

In this case, it is possible to easily make the Log Reader Agent continue to work by creating an alias for the original Publisher on the Distributor. The steps for doing this are as follows (continuing with the same test situation previously described), all on Server D.

  1. Start SQL Server Configuration Manager, and then:
    1. Expand the SQL Native Client Configuration section.
    2. Right-click Aliases and add a new alias, from the original principal/Publisher to the new principal/Publisher. (In this example, the Alias Name is SERVERA and the Server is SERVERB.)
  2. Restart the Log Reader Agent job.
  3. Observe that replication continues to work as expected.

When it comes time to reestablish the mirroring partnership, the sequence of steps should be the following (continuing with the example situation):

  1. Reestablish mirroring between Server B and Server A.
  2. Delete the alias on the Distributor.
  3. Restart the Log Reader Agent.
  4. Optionally failover from Server B back to Server A.

Note:  If the Distributor is not a dedicated server, the addition of an alias for the original mirroring principal server may cause problems for other applications running on the Distributor. For maximum flexibility, we recommend that you have a dedicated, remote Distributor.

Mirroring the Subscription Database

Even though you can mirror the subscription database, there is no support for automatic failover of the Distribution Agent if a mirroring failover occurs. After a mirroring failover of the subscription database, the Distribution Agent fails because it can no longer connect to the original subscription database on the original Subscriber.

Prior to the release of SQL Server 2008, mirroring the subscriber was not a practicable solution because the potential advantages of database mirroring were mostly negated by the requirement to perform a full initialization of the Subscriber after a failover.

The subscriber failover procedures described in this document take advantage of the initialize from lsn option introduced in SQL Server 2008. Creating the new subscription with initialize from lsn allows you to quickly synchronize the new subscription database with the publication database without resorting to initializing from a snapshot or backup, and without taking the publication or distribution databases offline. This technique enables you to integrate replication with database mirroring to achieve rapid failover with zero data loss.

Despite the advantages of mirroring the subscription database, a few words of caution are in order. All of the steps needed to perform a successful failover of a mirrored subscriber database are supported; however, the success or failure of an actual attempt to failover with initialize from lsn will depend on careful preparation and execution of the subscriber failover procedure described in this document.

Failover to the mirrored subscription database is a manual procedure. This is a relatively complex procedure, and to achieve reliability and zero data loss, you must perform all of the steps correctly and in the proper order.

To successfully initialize from an LSN, the following considerations apply:

  • The Publisher, Distributor, and Subscriber must all be running SQL Server 2008 or higher.
  • The distribution retention period must be set to an appropriate, non-zero value. For more information, see Configuring the Distribution Retention Period.
  • To avoid lost transactions and inconsistencies between the publication and subscription, re-initialization of the subscription using initialize from lsn must occur before the distribution retention period expires.
  • You must identify and record the LSN of the last update that was successfully applied to the mirrored (secondary) subscription database before the principal mirror failed. For more information, see How Does Initializing from an LSN Work.
  • Ensure that no event occurs to cause the min_autonosync_lsn value in the publication database to be updated after the principal subscription mirror fails.

It is necessary to immediately halt activities that could cause min_autonosync_lsn to be updated before the subscription can fail over to the new database. These activities can be safely resumed after you have pointed the successfully initialized the new Subscriber. Activities that will cause min_autonosync_lsn to be updated include the following:

If any of the conditions are not met, it may be impossible to initialize the subscription from an LSN without loss of data, and a full re-initialization of the Subscriber from a snapshot or backup will be required to restore the subscription database to a state consistent with the publication database.

The rest of this section describes how to recover a Distribution Agent that failed due to a subscription database failover, without having to perform a full re-initialization of the Subscriber.

For the purposes of this section, assume the following example environment:

  • SERVERSP – the database mirroring principal server and initial replication Subscriber
  • SERVERSM – the database mirroring mirror server
  • SERVERD – the replication Distributor
  • SERVERP – the replication Publisher
  • Database TicketOrdersPub – the publication database
  • Database TicketOrdersSub – the subscription database that is mirrored
  • Publication name TicketOrders

The steps for configuring replication are similar to those previously described in Mirroring the Publication Database.

To configure replication

  1. Configure SERVERD to be a Distributor and allow SERVERP to publish through it.
  2. Configure SERVERP to use SERVERD as the remote distributor.
  3. Configure the transaction retention period on the SERVERD (see the next section, Configuring the Distribution Retention Period).
  4. Configure the publication on SERVERP.

So that it will be easy to redirect the Distribution Agent to the new subscription database after a mirroring failover, the publication must be set to allow initialization from a backup. For instructions on setting this option when creating or modifying a publication, see the following:

  1. Configure the subscription on SERVERSP, which can be either a push subscription or a pull subscription.

Database mirroring between SERVERSP and SERVERSM can be enabled before or after replication is configured, but if the Subscriber will be initialized with a snapshot, it is more efficient to configure mirroring after the subscription database has been initialized. This is to avoid all the logging activity as a result of the subscription initialization process being mirrored.

Configuring the Distribution Retention Period

The default minimum retention period for transactions on the Distributor is zero, which means that transactions can be cleaned up as soon as they have been replicated to all Subscribers.  The steps required to redirect the Distribution Agent to the new subscription database after a mirroring failover rely on the availability of replicated transactions in the distribution database even after they have been sent to the Subscriber.  There are two main reasons why this is required:

  • If the subscription database is mirrored using a high-performance configuration, and if the mirror is far behind the principal, in the event of a failover with data loss, the recovered mirror (that is, the new subscription database) will be behind the original principal. To bring the new subscription database to a consistent state for replication, the Distribution Agent must re-apply all transactions that the new subscription database has not seen. This includes the set of transactions that were already applied to the original subscription database on the old mirroring principal server, but had not yet been mirrored. This avoids having to completely re-initialize the new subscription database.
  • In the scenario above, the Publisher server may also be lost, or the data from the publication database is no longer available to re-initialize the new subscription database. In that case, data loss will occur if the missing transactions are not available on the Distributor.

To avoid data loss or extended downtime when mirroring the subscription database, we recommend that you set the minimum transaction retention period to a reasonable time period. Using the above example, a reasonable time period would be the maximum amount of time that the mirror server could be behind the principal server, plus additional time to complete the Subscriber fail over procedure, thus guaranteeing that no replicated transactions are lost.

For more information on configuring Distributor properties, see the following:

Manual Synchronization Types for Subscriptions

SQL Server 2005 introduced two new synchronization types that allow easier ways to create subscriptions by using manual synchronization (sometimes called no-sync subscriptions). These synchronization types were introduced to address two key aspects of manual synchronization:

  • Automate the tasks of preparing the subscription database for replication. This involves automatically creating the objects required by replication when setting up a no-sync subscription.
  • Reduce the down time required to set up a no-sync subscription using a backup. In SQL Server 2000, the Publisher had to be taken offline until all the steps related to setting up a subscription using a backup were completed.

The two synchronization types introduced in SQL Server 2005 were replication support only and initialize with backup. These are both widely known and used.

SQL Server 2008 introduces a new synchronization type, initialize from lsn, which is used internally to support online changes to a peer-to-peer topology. It is a powerful option that is similar to initializing from a backup except that initialization proceeds from a supplied LSN (a Log Sequence Number that identifies a point within a database’s transaction log). This means the transactions that must be applied to the new subscription database are limited to those after the supplied LSN, providing the transactions still exist in the distribution database. This makes the option suitable for disaster recovery scenarios (such as in the example configuration used previously in this paper) because the initialization of the subscription database can be much faster than a full re-initialization, and the Distribution Agent starts from where it left off at the time of the mirroring failover.

After a mirroring failover of a subscription database to the mirror server, it is necessary to redirect the replication stream to the new subscription database on the new mirroring principal server (SERVERSM in the example configuration). Although this involves creating a new subscription, the subscription does not require a full re-initialization in this case, because the new synchronization option in SQL Server 2008 can be used.

The following section describes how initialization using an LSN (either user-specified or retrieved from a backup) works and how it enables the replication stream to be re-directed to the new subscription database after a mirroring failover without a full re-initialization.

How Does Initializing from an LSN Work?

In the simplest sense, all three manual synchronization methods rely on a consistent starting point for replicating transactions after the subscription has been set up. When a subscription is created using manual synchronization, the Distribution Agent must know from what point transactions should be replicated to guarantee that no changes are lost between the time the subscription is enabled and the changes are replicated.

This starting point is an LSN. The LSN is determined automatically in the case where the synchronization type is replication support only. With initializing from a backup, the LSN is retrieved from the header of the backup used to initialize the subscription. When initializing from an LSN, the LSN is user-specified.

When specifying the LSN, the important thing is to ensure that the LSN is valid so that the transactions corresponding to the LSN still exist in the distribution database. In a disaster recovery situation, this is why it is important that a non-zero transaction retention period is set. The following diagram and explanation show the various LSNs involved after the mirroring failover of a subscription database.

Figure 4: LSNs within the distribution database

Figure 4 shows the LSNs of interest within the distribution database after a mirroring failover of the subscription database. The LSNs are defined as follows:

  • LSN 1: The LSN of the oldest transaction in the distribution database. This represents the transaction at the start of the retention period.
  • LSN 2: The LSN of the last transaction that was applied to the subscription database on the mirroring principal server AND was mirrored to the mirror server.
  • LSN 3: The LSN of the last transaction that was applied to the subscription database on the mirroring principal server.
  • LSN 4: The most recent transaction that was read from the Publisher by the Log Reader Agent.

The difference between LSN 2 and LSN 3 represents how far the mirror server was “behind” the principal server before the mirroring failover. In the case where the principal and mirror were synchronized before the mirroring failover, LSN 2 and LSN 3 will be equal. When manually initializing the new subscription after the mirroring failover, LSN 2 should be used with the initialize from LSN method and can be found from the replication metadata in the subscription database.

There are two reasons why the LSN on the subscriber database from the current principal server (LSN 3) cannot be used to initialize the subscription:

  • There are no LSNs greater than the specified LSN in the distribution database.
  • The specified LSN must be less than the value of min_autonosync_lsn in the syspublications table in the publication database.

Note:  The min_autonosync_lsn value is updated to the current database LSN whenever certain events occur; for more information, see Mirroring the Subscription Database. If the mirroring failover happens after the min_autonosync_lsn value was updated but before all changes after that point have been replicated, it is not possible to reinitialize by using the LSN value on the subscriber.

Care must be taken after a mirroring failover because transactions will continue to be cleaned from the distribution database, potentially allowing the transactions between LSN 2 and LSN 3 to be lost if the re-initialization of the new subscription database does not happen within the configured distribution retention period. You should allow extra time in the distribution retention period for the failure to be detected and the failover procedure to be completed.

If initializing from LSN is not possible, a full re-initialization of the subscription database must be performed by using a snapshot or a backup.

Recovering the Replication Stream Following a Mirroring Failover

This section presents the steps and code required to perform a manual initialization of the new subscription database after a mirroring failover, by initializing from an LSN. For clarity, these steps use the example environment previously described.

To recover the replication stream

  1. To find the LSN of the last transaction that was applied to the new subscription database, run the following code on the new mirroring principal server (SERVERSM) after the mirroring failover, which will be the new Subscriber.

This code examines the Distribution Agent information for the old subscription in the subscription database. The “old subscription” is the one existing on the mirroring principal server before the failover occurred (SERVERSP). The LSN returned (transaction_timestamp in the following code) should be saved, as it will be used in step 3, and corresponds to LSN 2 in Figure 4.

USE TicketOrdersSub;


SELECT transaction_timestamp, *

FROM dbo.MSreplication_subscriptions

WHERE publisher     = ‘SERVERP’

AND publisher_db = ‘TicketOrdersPub’

AND publication  = ‘TicketOrders’;


  1. To clean up the old subscription from the new subscription database, run the following code on the new Subscriber.

This code cleans up the old subscription so that there is no entry for it in the replication metadata in the subscription database. If it still exists when the new subscription is created, the transaction timestamp is reset to 0x00, resulting in all transactions from the distribution database being re-applied to the subscription database, causing data consistency errors.


USE TicketOrdersSub;


EXEC sp_subscription_cleanup

@publisher    = ‘SERVERP’,

@publisher_db = ‘TicketOrdersPub’,

@publication  = ‘TicketOrders’;


  1. To create the new subscription, run the following code on the Publisher (SERVERP).

The code defines the new subscription on the new Subscriber, using the initialize from LSN synchronization method with the LSN/timestamp saved in step 1. This must be done for both pull and push subscriptions. Ensure that the correct subscription type is set in the @subscription_type parameter. Substitute the saved LSN/timestamp from step 1 as the value for the @subscriptionlsn parameter.


USE TicketOrdersPub;


EXEC sp_addsubscription

@publication       = N’TicketOrders’,

@subscriber        = N’SERVERSM’,

@destination_db    = N’TicketOrdersSub’,

@subscription_type = N’pull’,

@sync_type         = N’initialize from LSN’,

@article           = N’all’,

@update_mode       = N’read only’,

@subscriber_type   = 0,

@subscriptionlsn   = 0x00000013000013380004000000000000;


  1. If the subscription type is pull, go to step 5. Otherwise, go to step 6.
  2. To finish configuring the pull subscription, if appropriate, run the following code on the new Subscriber.

This code finalizes creating a continuously running pull subscription.


USE TicketOrdersSub;


EXEC sp_addpullsubscription

@publisher         = N’SERVERP’,

@publication       = N’TicketOrders’,

@publisher_db      = N’TicketOrdersPub’,

@independent_agent = N’True’,

@subscription_type = N’pull’,

@description       = N”,

@update_mode       = N’read only’,

@immediate_sync    = 0;


EXEC sp_addpullsubscription_agent

@publisher      = N’SERVERP’,

@publisher_db   = N’TicketOrdersPub’,

@publication    = N’TicketOrders’,

@distributor    = N’SERVERD’,

@frequency_type = 64



Go to step 7.

  1. To finish configuring the push subscription, if appropriate, run the following code on the Publisher.

This code finalizes creating a continuously-running push subscription.

USE TicketOrdersPub;


EXEC sp_addpushsubscription_agent

@publication              = N’TicketOrders’,

@subscriber               = N’SERVERSM’,

@subscriber_db            = N’TicketOrdersSub’,

@job_login                = NULL,

@job_password             = NULL,

@subscriber_security_mode = 1,

@frequency_type           = 64,

@dts_package_location     = N’Distributor’;



Continue to step 7.

  1. To remove the old subscription, run the following code on the Publisher.

This code drops the old subscription (from SERVERSP) to ensure that its agents do not run automatically and cause problems with the new subscription.

USE TicketOrdersPub;


EXEC sp_dropsubscription

@publication    = N’TicketOrders’,

@subscriber     = N’SERVERSP’,

@destination_db = N’TicketOrdersPub’,

@article        = N’all’;



This is how database mirroring can be combined with transactional replication to achieve higher availability of the replication stream.

It describes the mechanism for mirroring the publication database, along with in-depth explanations of setup steps and options. The behavior of the Log Reader Agent is analyzed, and trace flag 1448 discussed. By using this trace flag it is possible for replication to continue from a mirrored publication database if the mirroring partnership is not synchronized.

The most important part of this white paper is the description of the initialize from LSN synchronization method introduced in SQL Server 2008. By using this new feature, it is possible to effectively mirror the subscription database without performing a full re-initialization in the event of a mirroring failover, leading to less downtime in the event of a disaster.

Mirroring Overview

SQL Server 2005 provides a set of high availability methods that the users can use to achieve fault tolerance and to prevent server outages and data loss. The selection of the high availability method depends on various factors.

Some DBAs need the servers to be available 24/7, while others can afford an outage of a couple of hours. Cost also plays a role in the selection.

For example, Clustering is an expensive high availability method when compared to Database Mirroring, but it allows the user to failover immediately.

The following high availability features are available with the Enterprise edition:

  • Failover Clustering
  • Multiple Instances(up to 50)
  • Log shipping
  • Database Snapshots
  • Database Mirroring

The following high availability features are available with Standard Edition:

  • Failover Clustering(maximum two nodes)
  • Multiple instances(up to 16)
  • Log shipping
  • Database Mirroring

In this article, we will be discussing about Database Mirroring high availability method.

Overview of Database Mirroring:

Database Mirroring is a primarily software solution for increasing database availability. Mirroring is implemented on a per-database basis. Mirroring works only with full recovery model. Database mirroring is available in the Enterprise edition and in the Standard edition. The user can mirror only the user databases.

Mirroring allows the user to create an exact copy of a database on a different server. The mirrored database must reside on different instance of SQL Server Database engine. Microsoft fully supports database mirroring with SQL Server 2005 SP1 onwards. For the RTM release (prior to SP1), Microsoft support services will not support databases or applications that use database mirroring. The database mirroring feature should not be used in production environments.

Prior to SP1, database mirroring is disabled by default, but can be enabled for evaluation purposes by using trace flag 1400.  The following T-SQL statement can be used to achieve this:


Database Mirroring is only available in the Standard, Developer and Enterprise editions of SQL Server 2005. These are the required versions for both the principal and mirror instances of SQL Server. The witness server can run on any version of SQL Server. In addition, there are some other features only available in the Developer and Enterprise editions of SQL Server, but the base functionality exists in the Standard edition.

Benefits of Database Mirroring:

  1. Implementing database mirroring is relatively easy. It does not require any additional hardware in terms of clustering support. So it proves to be a cheaper implementation instead of clustering a database.
  2. Database mirroring provides complete or nearly complete redundancy of the data, depending on the operating modes.
  3. It increases the availability of the database.

Understanding Database Mirroring Concepts:

Principal: The principal server is the primary database. This acts as a starting point in a database mirroring session. Every transaction that is applied to the principal database will be transferred to the mirrored database.

Mirror: Mirror is the database that will receive the copies from the principal server. There should be consistent connection between the mirrored and the principal server.

Standby Server: In the process of database mirroring, a standby server is maintained. This is not accessible to the users. In case of the principal server failing; the users can easily switch over.

Modes of Database Mirroring: Database Mirroring can work in two ways: synchronous or asynchronous

  1. A) Synchronous mode: This is also called as high safety mode. In this mode, every transaction applied to the principal will also be committed on the mirror server. The transaction on the principal will be released only when it is also committed on the mirror. Once it receives an acknowledgement from the mirror server, the principal will notify the client that the statement has been completed. The high safety mode protects the data by requiring the data to be synchronized between the principal and the mirror server.
  1. High safety mode without automatic failover:


Transaction Safety set to full

When the partners are connected (Principal and Mirror) 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 manually forced to the mirror server (with possible data loss).

2. High Safety mode with automatic failover:



Transaction Safety set to full:

Automatic failover provides high availability by ensuring that the database is still served after the loss of one server. Automatic failover requires that the session possess a third server instance, the witness, which ideally resides on a third computer. The above figure shows the configuration of a high-safety mode session that supports automatic failover.

B) Asynchronous mode: This is also known as the high performance mode. Here performance is achieved at the cost of availability. In this mode, the principal server sends log information to the mirror server, without waiting waiting for an acknowledgement from the mirror server.

Transactions on the principal server commit without waiting for the mirror server to commit to the log file. The following figure shows the configuration of a session using high-performance mode.

                  PRINCIPAL SERVER —–DataFlow—-> MIRROR SERVER

Transaction Safety set to off

This mode allows the principal server to run with minimum transactional latency and does not allow the user to use automatic failover. Forced service is one of the possible responses to the failure of the principal server. It uses the mirror server as a warm standby server. Because data loss is possible, one should consider other alternatives before forcing service to the mirror.

 Types of Mirroring:

 To provide flexibility when dealing with different requirements, SQL Server 2005 offers three operating modes, which are determined by presence of the witness and transaction safety level, configurable on per mirroring session basis.

The safety level can be turned either on or off. With the safety level set to ON, committed transactions are guaranteed to be synchronized between mirrored partners, with the safety turned OFF, synchronization is performed on a continuous basis, but without assurance of full consistency between transaction logs of both databases.

High availability operating mode: synchronous with a witness (with transaction safety set to ON) – In this case, transactions written to the transaction log of the database on the principal are automatically transferred to the transaction log of its mirrored copy. The principal waits for the confirmation of each successful write from its mirror before committing the corresponding transaction locally, which guarantees consistency between the two (following the initial synchronization). This type of synchronous operation is the primary prerequisite for the automatic failover – the other is the presence and proper functioning of the witness server (which means that only the synchronous mode with a witness offers such capability).

Additionally, availability of the witness also impacts operations in cases when the mirror server fails. In such a scenario, if the principal can still communicate with the witness, it will continue running (once the witness detects that the mirror is back online, it will automatically trigger its resynchronization), otherwise (if both mirror and witness are not reachable from the principal), the mirrored database is placed in the OFFLINE mode.

High protection operating mode: synchronous without a witness (with transaction safety set to ON) – uses the same synchronization mechanism as the first mode, however, the lack of the witness precludes automatic failover capability. The owner of the database can perform manual failover as long as the principal is present, by running ALTER DATABASE statement with SET PARTNER FAILOVER option from the principal).

Alternately, the owner can force the service to the mirror the database by running the ALTER DATABASE statement with the SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS option from the mirror, with potential data loss (if databases are not in synchronized state). Unavailability of the mirror (due to server or network link failure) causes the primary to place the mirrored database in OFFLINE mode (in order to prevent the possibility of having two mirroring partners operating simultaneously as principals).

High performance operating mode: asynchronous without a witness (with transaction safety set to OFF) – In this case, a transaction is committed on the principal before it is sent to its partner, which means that it is not uncommon for the source database and its mirror to be out of synch. However, since the process of transferring transaction log entries to the mirror is continuous, the difference is minor. In the case of principle failure, the database owner can force service to the mirror database, resulting in the former mirror taking on the role of the principal.

Forcing the service can result in data loss (encompassing all transaction log entries that constituted the difference between the mirror and the principal at the time of its failure), so it should be used only if such impact can be tolerated. Another choice when dealing with the principal failure in this mode (which reduces possibility of data loss) is terminating the mirroring session and recovering the database on the principal. Unlike in the synchronous mode with a witness, unavailability of the mirror leaves the principal operational.


  • Database mirroring is limited to only two servers.
  • Mirroring with a Witness Server allows for High Availability and automatic fail over.
  • You can configure your DSN string to have both mirrored servers in it so that when they switch you notice nothing.
  • While mirrored, your Mirrored Database cannot be accessed. It is in Synchronizing/Restoring mode.
  • Mirroring with SQL Server 2005 standard edition is not good for load balancing

Steps in Mirroring:

 SQL Server 2005 – Mirror Server:

In this tutorial you will learn about Mirror Server in SQL Server 2005 – Preparing the Principal and Mirror Server, Establishing a Mirroring Session, Establishing a Witness Server, Executing Transactions, Simulating Principal Server Failure, Restarting the Failed Server, Terminating the Mirror Session and Configuring Database Mirroring.

Preparing the Principal and Mirror Server:

Database mirroring is easy to set up and can be made self monitoring for automatic failover in the event of the principal server being unavailable. The first step is to configure the relationship between the principal server and the mirror server. This can be a synchronous mirroring with a witness server that provides the highest availability of the database.

A drawback in this type of configuration is the need to log transactions on the mirror before such transactions being committed to the principal server may retard performance. Asynchronous mirroring with a witness server provides high availability and good performance. Transactions are committed to the principal server immediately.

This configuration is useful when there is latency or distance between the principal server and the mirror. The third type of mirroring configuration is the Synchronous mirroring without the witness server. This guarantees that data on both servers is always concurrent and data integrity is of a very high order. However, automatic failover cannot occur as there are not enough servers to form a quorum decision on which server is to take the role of the principal server and which should be the mirror server.

Establishing a Mirroring Session:

Database mirroring is done within a mirror session. A mirror session maintains information about the state of the databases, the mirroring partners and the witness server. The mirror server identifies the most recent transaction log record that has been applied to the mirror database and requests for subsequent transaction log records from the principal server. This phase is called the synchronizing phase.

Once synchronization is complete the principal server will transmit the transaction logs to the mirror server even as changes are made. The mirror database is continually rolled forward to match the principal database. The operating mode of the mirror database (synchronous or asynchronous) will determine whether the transaction log records are applied to the mirror database immediately or after the transactions have been recorded in the principal server.

The mirror session maintains information about the state of any witness servers. It ensures that the witness server is visible both to the principal and the mirror servers.

A mirroring session is terminated by a number of causes. There may be a communication or server failure. The principal server may fail and the mirror may become the principal server. This can happen automatically or manually depending on the operating mode.

The session may also be terminated by the manual intervention of the Database Administrator using the TRANSACT-SQL ALTER DATABASE command. Mirroring may be terminated or suspended in the process

Establishing a Witness Server:

A witness server is a must where the DBA wants to implement automatic failover and the configuration must be in the synchronous operating mode. The witness server is usually on a different computer from the principal and the mirror servers. However, one server can act as a witness for multiple mirror partnerships.

The ALTER Database command with the SET WITNESS clause is used on the principal server to create a witness server. The Witness server address is specified and the endpoint port is defined to act as the witness for the server_network_address parameter.

A witness server can be disabled. However, the mirroring session will continue even when the witness server is disabled. Automatic failover will no longer be possible.

Information about the witness server can be viewed in sys.database_mirroring_witnesses catalog view.

Executing Transactions:

The ALTER DATABASE command has to be run on the mirror server specifying the principal server endpoint address and then the same has to be done on the principal server so that synchronization can commence. The operating mode has to then be selected. By default the operating mode is synchronous.

This can be changed by running the ALTER DATABASE command with SET PARTNER SAFETY clause on either partner server.

The saftety_mode parameter can be either OFF or FULL. The mirror partnership information can be viewed by running a query on sys.databases catalog view.

If the transaction safety is set to full, the principal and mirror servers operate on synchronous transfer mode. The transaction logs are hardened in the principal server and transmitted to the mirror and then the principal waits for the mirror to harden its logs and send its response. When the safety is OFF the principal does not wait for the acknowledgement of the mirror. In this instance the principal and the mirror may not be synchronized at all times.

Synchronous transfer guarantees that the mirror is a faithful image of the principal database transaction log

Simulating Principal Server Failure:

A principal server failure can be simulated in test scenarios to ensure that failover is smooth. Failover implies that the mirror server takes over as the principal server and the mirror database will have to act as the principal database. The failover can be manual, automatic or forced.

Automatic failover occurs when the high availability operating mode is synchronous and the safety is FULL and a witness is part of the session. Manual occurs in high availability and high protection operating modes. Safety has to be full and the partner databases are synchronized. Forced service is used primarily in the High Performance mode with safety off.

Simulating Principal Server failure can be done by manual intervention of the DBA in an orderly way. The safety will have to be first set to FULL and the principal and the mirror databases synchronized. Manual failover can be invoked by invoking the ALTER DATABASE command on the principal server or by clicking the failover button in the Database Properties/Mirroring dialog in the Management Studio.

A manual failover causes current users to be disconnected and all unfinished transactions to roll back. These transactions will then be recovered from the redo queue. The mirror assumes the role of the principal server and the two servers will negotiate a new starting point for mirroring based on their mirroring failover LNS.

If the principal server is no longer operating, and safety is OFF, forced service can be resorted to. This service causes some data loss.

Restarting the Failed Server:

A failed server can be restarted and can be synchronized with the principal server or the mirror server as the case may be. Any suspending of transactions causes the log on the principal server to grow with the transactions being logged and stored. Once the mirror session is resumed, the principal transaction log is synchronized and written on to the mirror database log.

Terminating the Mirror Session:

A mirror session can be manually terminated and the relationship between the servers can be ended. When a session is ended, all information about the session is removed from all servers and leaves both the principal server and the independent server with an independent copy of the database. The mirror server database will remain in the restoring state until it is manually recovered or deleted.

Configuring Database Mirroring:

Configuring a mirror server includes configuring the mirror server and the database.

The server designated as the mirror must be accessible and trusted by the principal database server. Ideally both servers should belong to the same domain. The mirror server should also have sufficient memory and processing power to act as the principal server in the event of failover. It should be able to support users and applications without noticeable difference in the quality of service.

The mirror database must be created manually. The file structure must match the principal database file structure. Both databases must implement full recovery model. Once the mirror database is created, the latest full database backup of the principal database must be applied to the mirror using the RESTORE DATABASE command with the WITH NONRECOVERY clause.

The next step is to enable the communication mechanism through which the mirroring will take place. This implies creation of endpoints on both servers. The endpoint controls the Transmission Control Protocol (TCP) port on which the server listens for database mirroring messages. The endpoint also defines the role that it must perform.

A server needs to have only one configured endpoint regardless of the number of mirroring sessions it participates in. However, each instance requires a unique port on which to listen.

The next step is to establish a mirroring session. The process of establishing a mirroring session has been discussed above. It involves creating a mirroring session using the ALTER DATABASE command on the mirror server first and then on the principal server. The server_network_address parameter will have to be specified. Then a partnership will have to be created on the mirror server, the operating mode will have to be changed and so on.

Preparing for mirroring:

To prepare for database mirroring, user has to perform three configuration steps:

    1. Configuring Security and communication between instances:

To establish a database mirror connection’s Server uses endpoints to specify the connection between servers. SQL Server performs authentication over the endpoints. This can be achieved by using Windows authentication or certificate based authentication.

If witness server is also in the picture, and then we need to specify the communication and authentication between the principal and the witness and between the mirror and witness.

Here, since we will be creating the end point for database mirroring; only TCP can be used as transport protocol. Each database mirroring endpoint listens on a unique TCP port number.

The endpoints can be created with the CREATE ENDPOINT TSQL statement.


AS { HTTP | TCP } (

<AS TCP_protocol_specific_arguments> ::=
LISTENER_PORT = listenerPort
[ [ , ] LISTENER_IP = ALL | ( 4-part-ip ) | ( “ip_address_v6” ) ]


<FOR DATABASE_MIRRORING_language_specific_arguments> ::=
| CERTIFICATE certificate_name
[ ALGORITHM { RC4 | AES | AES RC4 | RC4 AES } ] }



Authentication= <authentication_options>

Specifies the TCP/IP authentication requirements for connections for this endpoint. The default is WINDOWS. Along with the authentication the user has to mention the authorization method (NTLM or Kerberos).By default, the NEGOTIATE option is set, which will cause the endpoint to negotiate between NTLM or Kerberos.

CERTIFICATE certificate_name
the user can also specify that the endpoint has to authenticate using a certificate. This can be done by specifying the CERTIFICATE keyword and the name of the certificate. For certificate based authentication, the endpoint must have the certificate with the matching public key

Specifies that endpoint has to first try to connect by using Windows Authentication and, if that attempt fails, to then try using the specified certificate.

Specifies that endpoint has to first try to connect by using the specified certificate and, if that attempt fails, to then try using Windows Authentication.


Next, we will take a look at the encryption option. By default, database mirroring uses RC4 encryption.

Specifies whether encryption is used in the process. The default is REQUIRED.

Encryption options:

Option Description
DISABLED Specifies that data sent over a connection is not encrypted.
SUPPORTED Specifies that the data is encrypted only if the opposite endpoint specifies either SUPPORTED or REQUIRED.
REQUIRED Specifies that connections to this endpoint must use encryption. Therefore, to connect to this endpoint, another endpoint must have ENCRYPTION set to either SUPPORTED or REQUIRED




Encryption Algorithm.

Option Description
RC4 Specifies that the endpoint must use the RC4 algorithm. This is the default.
AES Specifies that the endpoint must use the AES algorithm.
AES RC4 Specifies that the two endpoints will negotiate for an encryption algorithm with this endpoint giving preference to the AES algorithm.
RC4 AES Specifies that the two endpoints will negotiate for an encryption algorithm with this endpoint giving preference to the RC4 algorithm.

RC4 is a relatively weak algorithm, and AES is a relatively strong algorithm. But AES is considerably slower than RC4. If security is a higher priority than speed, then AES is recommended.


We have to specify the endpoint’s role in the Database mirroring option. Role can be Partner, Witness or All. Using the ALL keyword as the role specifies that the mirroring endpoint can be used for witness as well as for a partner in the database mirroring scenario.

We can inspect the database mirroring endpoints on a server by querying the sys.database_mirroring_endpoints catalog view:
FROM sys.database_mirroring_endpoints;

2.Creating the Mirror Database:

To create a mirror database, we have to restore the full backup of a principal including all other types of backup (transactional logs) created on the principal before establishing a session.

The NORECOVERY option has to be used when restoring from backup so that the mirrored database will remain in non usable state. The mirror database needs to have the same name as the principal database.

    1. Establishing a mirror session:
      The next step in setting up database mirroring is to set up the mirror session on the database by identifying the mirroring partners.

We have to identify the partners involved in the mirroring process on the principal database and on the mirror database.

Let us consider an example.

We will take Adventure Works as the sample database. This database has simple recovery model by default. To use database mirroring with this database, we must alter it to use the full recovery model.

USE master;
ALTER DATABASE Adventure Works

We have two server instances which act as partners (Principal and Mirror) and one server instance which acts as witness. These three instances are located on different computers.

The three server instances run the same Windows domain, but the user account is different for the example’s witness server instance.

  1. Create an endpoint on the principal server instance

CREATE ENDPOINT Endpoint_Mirroring
–Partners under same domain user; login already exists in master.
–Create a login for the witness server instance,
–which is running as XYZ\witnessuser:
USE master ;
— Grant connect permissions on endpoint to login account of witness.
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [XYZ\witnessuser];

2.Create an endpoint on the mirror server instance

CREATE ENDPOINT Endpoint_Mirroring
–Partners under same domain user; login already exists in master.
–Create a login for the witness server instance,
–which is running as XYZ\witnessuser:
USE master ;
–Grant connect permissions on endpoint to login account of witness.
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [XYZ\witnessuser];

  1. Create an endpoint on the witness server instance

CREATE ENDPOINT Endpoint_Mirroring
–Create a login for the partner server instances,
–which are both running as Mydomain\dbousername:
USE master ;
CREATE LOGIN [Mydomain\dbousername] FROM WINDOWS ;
–Grant connect permissions on endpoint to login account of partners.
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Mydomain\dbousername];

  1. Create the mirror database. Refer step 2 in the “Preparing for Mirroring” block.
  2. Configure the principal as the partner on the mirror.

ALTER DATABASE <Database_Name>

The syntax for a server network address is of the form:
TCP: // < system-address> : < port>

< system-address> is a string that unambiguously identifies the destination computer system. Typically, the server address is a system name (if the systems are in the same domain), a fully qualified domain name, or an IP address.

< Port> is the port number used by the mirroring endpoint of the partner server instance.

A database mirroring endpoint can use any available port on the computer system. Each port number on a computer system must be associated with only one endpoint, and each endpoint is associated with a single server instance; thus, different server instances on the same server listen on different endpoints with different ports. In the server network address of a server instance, only the number of the port associated with its mirroring endpoint distinguishes that instance from any other instances on the computer.


ALTER DATABASE Adventure Works

  1. Configure the mirror as the partner on the principal.

    ALTER DATABASE Adventure Works

  2. On the principal server, set the witness

ALTER DATABASE Adventure Works

Switching Roles:

When the principal server fails, we have to switch roles over to the mirror and from then on specify that the mirror should become the principal database. This concept is called role switching. The three options for role switching are:

  1. Automatic failover: – When the witness server is present in the database mirroring session, automatic failover will occur when the principal database becomes unavailable and
    when the witness server confirms this. During the automatic failover, the mirror will be automatically promoted to principal, and whenever the principal comes back on, it will automatically take the role of mirror.
  2. Manual Failover: – The user can perform manual failover only if both the principal and mirror are alive and in synchronized status. DBAs use this operation most frequently to perform maintenance tasks on the principal. The failover is initiated from the principal and later the roles are reverted after the database maintenance job is done.

The statement used to switch database roles (manual failover) is shown below:


  1. Forced Service: – When the witness server is not used and if the principal database goes down unexpectedly, then the user has to initiate manual failover to the mirror. In asynchronous mode of operation, user does not have any idea whether the transaction that have got committed on the principal have made it to the mirror or not. In this scenario, when the user wants to switch roles, there is possibility of losing data.

To achieve this, we need to invoke an ALTER DATABASE statement as shown below:


Logshipping Overview

For distributed database application environment, it is always required to synchronize different database servers, back up, copy Transaction Logs, etc. If we are going to implement using application we have to put lots of efforts to build up the application. SQL Server 2005 provides an advanced feature called Log Shipping. Log shipping is an Automated Process for backing up, restoring, copying the transaction logs and synchronizing the database for distributed database server application which can improve the application performance and availability of database. In my recent project, I have done some short of experiment on it. I am going to explain it in this article.

What is Log Shipping?

Log Shipping is used to synchronize the Distributed Database Server. Synchronize the database by copying Transaction logs, Backing up, Restoring data. SQL Server used SQL Server Job Agents for making those processes automatic. Log Shipping does not involve automatic transfer of server if there is any failure. This means it has just synchronized the databases but if the primary server fails, it will not redirect your application to a secondary server. This has to be done manually.

Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. But this is not all.

The key feature of log shipping is it will automatically backup transaction logs throughout the day (for whatever interval you specify) and automatically restore them on the standby server. This in effect keeps the two SQL Servers in “synch”.

Should the production server fail, all you have to do is point the users to the new server, and you are all set. Well, it’s not really that easy, but it comes close if you put enough effort into your log shipping setup.

The Need for Standby Servers:

In a perfect world we wouldn’t need standby servers for our SQL Servers. Our hardware would never fail, NT Server 4.0 or Windows 2000 would never blue screen, SQL Server would never stop running, and our applications would never balk.

In a partially perfect work, we could afford very expensive clustered SQL Servers that automatically failover our wounded and dead production SQL Servers, reducing our stress and keeping our users very happy.

But for most of us, the closest thing we can afford to implement when it comes to SQL Server failover are standby servers that we have to manually fail over. And even some of us can’t afford this. But for this article, I am going to assume that you can afford a standby server.

The concept of standby servers is not a new one. It has been around a long time and been used by many DBAs. Traditionally, using a standby server for failover has involved manually making database and log backups on the production server and then restoring them to the standby server on a regular basis. This way, should the production server fail, then users could access the standby server instead, and downtime and data loss would be minimized.

This article is about log shipping, a refined variation of the traditional manual standby failover server process. Its two major benefits over the traditional methods are that it automates most of the manual work and helps to reduce potential data loss even more.

Benefits of Log Shipping:

While I have already talked about some of the benefits of log shipping, let’s take a more comprehensive look:

Log shipping doesn’t require expensive hardware or software. While it is great if your standby server is similar in capacity to your production server, it is not a requirement. In addition, you can use the standby server for other tasks, helping to justify the cost of the standby server. Just keep in mind that if you do need to fail over, that this server will have to handle not one, but two loads. I like to make my standby server a development server. This way, I keep my developers off the production server, but don’t put too much work load on the standby server.

Once log shipping has been implemented, it is relatively easy to maintain.

Assuming you have implemented log shipping correctly, it is very reliable.

The manual failover process is generally very short, typically 15 minutes or less.

Depending on how you have designed your log shipping process, very little, if any, data is lost should you have to failover. The amount of data loss, if any, is also dependent on why your production server failed.

Implementing log shipping is not technically difficult. Almost any DBA with several months or more of SQL Server 7 experience can successfully implement it.

Problems with Log Shipping:

Let’s face it, log shipping is a compromise. It is not the ideal solution, but it is often a practical solution given real-world budget constraints. Some of the problems with log shipping include:

Log shipping failover is not automatic. The DBA must still manually failover the server, which means the DBA must be present when the failover occurs.

The users will experience some downtime. How long depends on how well you implemented log shipping, the nature of the production server failure, your network, the standby server, and the application or applications to be failed over.

Some data can be lost, although not always. How much data is lost depends on how often you schedule log shipping and whether or not the transaction log on the failed production server is recoverable.

The database or databases that are being failed over to the standby server cannot be used for anything else. But databases on the standby server not being used for failover can still be used normally.

When it comes time for the actual failover, you must do one of two things to make your applications work: either rename the standby server the same name as the failed production server (and the IP address), or re-point your user’s applications to the new standby server. In some cases, neither of these options is practical.

Log Shipping Overview:

Before we get into the details of how to implement log shipping, let’s take a look at the big picture. Essentially, here’s what you need to do in order to implement log shipping:

Ensure you have the necessary hardware and software properly prepared to implement log shipping.

Synchronize the SQL Server login IDs between the production and standby servers.

Create two backup devices.

One will be used for your database backups and the other will be used for your transaction log backups.

On the production server, create a linked server to your standby server.

On the standby servers, create two stored procedures. One stored procedure will be used to restore the database. The other stored procedure will be used to restore transaction logs.

On the production server, create two SQL Server jobs that will be used to perform the database and transaction log backups. Each job will include multiple steps with scripts that will perform the backups, copy the files from the production server to the standby server, and fire the remote stored procedures used to restore the database and log files.

Start and test the log shipping process.

Devise and test the failover process.

Monitor the log shipping process:

Obviously I have left out a lot of details, but at least now you know where we are headed.

To make my explanations easier to understand in this article, all my examples assume you will be failing over only one database from the production server to the standby server. In the real world you will probably want to failover more than just one. Once you have implemented log shipping for one database, it should be obvious how to implement others.

Generally, I just add additional databases to my already existing scripts and jobs. But if you prefer, you can create separate scripts and jobs for each database you want to failover using log shipping.

As you read the details of how I implement log shipping below, you may think of other ways to accomplish the same steps

Hardware and Software Requirements:

The hardware and software requirements for log shipping are not difficult. The hardware for the production and the standby server should be as similar as you can afford. If your production server only handles a couple of dozen simultaneous users, then you probably don’t need to spend a small fortune on making the standby server just like the production server.

On the other hand, if your production server handles 500 simultaneous users, or has multi-gigabyte database, then you may want to make your standby server as similar to the production server as you can afford.

As far as software is concerned, I just try to ensure than I have NT Server and SQL Server at the same level of service packs. In addition, the two servers must have SQL Server 7 configured similarly. For example, the code page/character set, sort order, Unicode collation, and the local all must be the same on both servers.

In order to help reduce any potential data loss during server failover from the production server to the standby server, your production server should have its transaction logs stored on a separate physical drive array than the database files. While this will boost your server’s performance, the main reason for this is to help reduce data loss.

For example, if the drive array with your database files on it goes down, then hopefully the drive array with the log files will be OK. If this is the case, then you should be able to recover the transaction log and move it to the standby server, significantly reducing any data loss. But if the transaction logs are on the same drive array as the database files, and the drive array fails, then you have lost any data entered into the system since the last log file was shipped to the standby server.

The main functions of Log Shipping are as follows:

  • Backing up the transaction log of the primary database
  • Copying the transaction log backup to each secondary server
  • Restoring the transaction log backup on the secondary database

Components of Log Shipping

For implementing Log Shipping, we need the following components – Primary Database Server, Secondary Database Server, and Monitor Server.

  • Primary Database Server: Primary Sever is the Main Database Server or SQL Server Database Engine, which is being accessed by the application. Primary Server contains the Primary Database or Master Database.
  • Secondary Database Server: Secondary Database Server is a SQL Server Database Engine or a different Server that contains the backup of primary database. We can have multiple secondary severs based on business requirements.
  • Monitor Server: Monitor Server is a SQL Server Database Engine which Track the Log shipping process.

Log Shipping Prerequisites

  • Must have at least two Database Servers or two SQL Server 2005 Database Engines.
  • Configuration user should have Admin privilege on that server
  • SQL Server Agent Service Configured properly
  • Configuration mode of Primary database should be a Full or Bulk Logged recovery model.
  • Shared folder for copying the transaction logs.

SQL Server 2005 Version that Supports Log Shipping

SQL Server 2005 Version Available
SQL Server 2005 Enterprise Edition Yes
SQL Server 2005 Workgroup Edition Yes
SQL Server 2000 Standard Edition Yes
SQL Server 2005 Developer Edition Yes
SQL Server 2005 Express Edition No

Background Tables in Log shipping:


  1. log_shipping_monitor_alert
  2. log_shipping_monitor_error_detail
  3. log_shipping_monitor_history_detail
  4. log_shipping_monitor_primary
  5. log_shipping_monitor_secondary
  6. log_shipping_primaries
  7. log_shipping_primary_databases
  8. log_shipping_primary_secondaries
  9. log_shipping_secondaries
  10. log_shipping_secondary
  11. log_shipping_secondary_databases