High Availability and Disaster Recover in SQL Server

1.      Basic concepts
a.      Availability % =Online Time/(Online time + down time), where down time = offline time + repair time
b.      HA vs. Disaster Recovery. They are not the same.
HA focus on the SQL Server service as a whole
D.R focus on the data

2.      SQL Server Failover Cluster
a.      Based on Windows Cluster Service
b.      SQL Server Failover Cluster (Single Site)

                            i.           Primarily an HA feature
                          ii.           Uses time tested Windows Clustering technology. Advanced heartbeat, failover, quorum model, and quorum voting options available in the latest versions of Windows.
                        iii.           Protects against Machine failures (ex. CPU/motherboard causes machine to become unresponsive or failed)
                        iv.           Protects against OS failures (ex. blue screens)
                          v.           Protects against SQL Instance failures (ex. SQL hangs/ AV’s)
                        vi.           Application connects using one virtual server name, which is not tied to a specific machine name. The current owning node is abstracted via the virtual server name.
                       vii.           Works well with most other features  such as log shipping, replication,  and asynchronous availability groups
                     viii.           Manages external dependencies well upon failover. All system and user databases reside on a shared drive – failover simply provides access to the same shared drives to the new owning node. Registry keys are replicated via cluster checkpoints.
                         ix.           Supports rolling upgrades scenarios
                          x.           Instance-level protection without data redundancy (Instance [-]DATA RLO)
                         xi.           Considerations:
·  There is no concept of a secondary database. Does not maintain a redundant copy of the data and so does not protect against  an I/O subsystem failure
·  No special requirements with respect to database recovery models
·  Must ensure nodes are maintained properly (patching levels should match)
·  A major outage like a datacenter power failure, or failure of the network link to the primary data center is not addressed because all nodes are within the same datacenter.

c.      When to failover – “looksalive check” and “is alive check”
d.      SQL Server Cluster Topology
                                          i.     Two nodes: active – passive: one instance is running the active node in the cluster
                                         ii.     Two nodes: active – active: two instances are running on the two nodes, competing the resources
                                        iii.     N+1 nodes: N active – 1 passive
e.      SQL Server 2012 Improvement - Support multi-subnet if installed on Windows Server 2008R2
                                          i.     Multi-Site Clustering with NO SAN Replication and with NO shared storage

a.       SQL 2012 supports multi-subnet environments, eliminating the need for a VLAN
b.       Will become even more relevant:
                                                                                       i.          SQL 2012 now supports databases on SMB
                                                                                      ii.          Windows 2012 clusters support 64 nodes
                                                                                     iii.          Hyper-V Replicas feature on Windows 2012
                                                                                     iv.          Hyper-V support for SMB
c.       Used often to support DR within a SQL 2012 Availability  Group topology

                                         ii.     Multi-Site Clustering with SAN Replication

a.      HA and DR solution using Failover Clustering in combination with SAN replication
b.      Protects against I/O subsystem failure on the primary datacenter
c.      Data disks can be synchronously or asynchronously mirrored
d.      External dependencies handled well as in typical single site failover clusters
e.      Instance-level protection WITH  data redundancy (Instance [+] DATA RLO)
f.       SQL 2012 supports multi-subnet environments, eliminating the need for a VLAN
g.      Considerations:
                                                    i.     Additional expense of SAN replication technology
                                                   ii.     Additional complexity of managing and configuring SAN  replication technology
                                                  iii.     Quorum model and node vote options should be configured to ensure an outage at the DR site will not cause the cluster to lose quorum
f.       Troubleshooting
                                          i.     Resources not available
a.       Can SQL Server start on the node?
1.      All resources needed by SQL server in the Failover Cluster Manager are online? If not, check windows event log.
2.      Then start the sqlservr.exe file in the BINN folder on the node. If it cannot be started, check the error log in the Log folder on the shared disk. If sql server can start, then it is the connection problem as below
b.       Can Windows connect to SQL Server and run sp_server_diagostics?
1.      Use sqlcmd to start SQL Server, if there is an error, it is probably an authentication problem
2.      If sqlcmd works, it likely related to Kerbero/NTLM authentication problem.
                                         ii.     Failover unexpectedly  
a.       Check Windows Cluster Log
1.      Caused by required resources?
2.      Caused by resources that have assigned with “affect the group”?
3.      Cannot do isalive check
                                                                                                      i.     Windows cluster cannot find SQL Server
                                                                                                     ii.     SQL Server cannot respond to Windows request of cannot execute sp_server_diagostics
4.      Run cluster log /g to create Windows Server 2008 Cluster log
b.       Check SQL Server Error Log
c.       Check Windows Event Log (mainly for on I/O and network errors)
d.       Check SQLDiag log (Extended  Events)
1.      In the same folder as the error log file
2.      SQL Server 2012 has three extended events
                                                                                                      i.     SQLDiag
                                                                                                     ii.     AlwaysOn
                                                                                                    iii.     System Health
3.      All the health diagnostics SQL Server Resource DLL receives from sp_server_diagnostics are automatically saved in the default Log directory of the SQL Server instance (%PROGRAMFILES%\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log). These logs are known as SQLDIAG logs and are saved in the XEL (extended events) file format. These files in the SQL Server Log directory have the following format: <HOSTNAME>_<INSTANCENAME>_SQLDIAG_X_XXXXXXXXX.xel. By looking at the SQLDIAG logs, you may be able to determine the root cause of availability group resource failure or failover event.

To view a SQLDIAG log, drag the .xel file into SQL Server Management Studio.

3.      Log Shipping
a.      Overview

                                          i.     Primarily a DR solution
                                         ii.     Can use compressed backups
                                       iii.     Very good way to validate the transaction log backups you are already taking
                                       iv.     Can run in a delayed restore mode to protect secondary from human error on primary site (ex. If TableX was deleted on Primary by mistake, and there is a 2-hour delay, you may have enough time to be able to recover TableX from the Secondary)
                                         v.     DB Level protection (database RLO)
                                       vi.     Can have multiple secondaries
                                      vii.     Very good option when other DR options are not possible or supported by a vendor since it is essentially a constant transaction log backup and restore sequence.
                                    viii.     Considerations:
a.       Database must be in Full or Bulk Logged recovery model
b.       No automatic failover
c.       No automatic redirection
d.       Can only failover at database granularity - cannot group a set of databases to failover together
e.       Database on secondary not readable during restore
f.        Database on secondary not writeable
g.       Databases have dependencies on objects that reside in other databases such as the master database. These objects often include logins, jobs, certificates, and server audits but can include other objects. A process (normally involving scripts) needs to be adopted to keep objects that are external to the database synchronized across the servers. Suitable for environments where these external dependencies are not changed very often (which tends to be true in most environments).

b.      Four jobs
                                           i.     backup job
A SQL Server Agent job that performs the backup operation, logs history to the local server and the monitor server, and deletes old backup files and history information. When log shipping is enabled, the job category "Log Shipping Backup" is created on the primary server instance.
                                         ii.     copy job
A SQL Server Agent job that copies the backup files from the primary server to a configurable destination on the secondary server and logs history on the secondary server and the monitor server. When log shipping is enabled on a database, the job category "Log Shipping Copy" is created on each secondary server in a log shipping configuration.
                                        iii.     restore job
A SQL Server Agent job that restores the copied backup files to the secondary databases. It logs history on the local server and the monitor server, and deletes old files and old history information. When log shipping is enabled on a database, the job category "Log Shipping Restore" is created on the secondary server instance.
                                        iv.     alert job
A SQL Server Agent job that raises alerts for primary and secondary databases when a backup or restore operation does not complete successfully within a specified threshold. When log shipping is enabled on a database, job category "Log Shipping Alert" is created on the monitor server instance. If no monitor server, SQL Server will create an alert job on the principal and secondary servers The job name is LSAlert_<instance Name>, which the following the sp every 2 minutes by default: EXEC sys.sp_check_log_shipping_monitor_alert.

The other three jobs run the backup, copy, or restore as below:

sqllogship -server instance_name { -backup primary_id |
                                 -copy secondary_id |
                                   -restore secondary_id }
           [ –verboselevel level ]
           [ –logintimeout timeout_value ]
           [ -querytimeout timeout_value ]

Primary_ID, Secondary_ID can be found from

SELECT * FROM msdb.dbo.log_shipping_primary_databases
SELECT * FROM msdb.dbo.log_shipping_secondary_databases

c.      Major Steps
                                           i.     Back up the transaction log at the primary server instance.
                                         ii.     Copy the transaction log file to the secondary server instance.
                                        iii.     Restore the log backup on the secondary server instance (the full backup of the database on the principal server and the restore on the secondary server is decided in the initial setup of log shipping)

For instance, in the above figure, the log shipping configuration has a primary server instance, three secondary server instances, and a monitor server instance. The figure illustrates the steps performed by backup, copy, and restore jobs, as follows:
1.      The primary server instance runs the backup job to back up the transaction log on the primary database. This server instance then places the log backup into a primary log-backup file, which it sends to the backup folder. In this figure, the backup folder is on a shared directory—the backup share.
2.      Each of the three secondary server instances runs its own copy job to copy the primary log-backup file to its own local destination folder.
3.      Each secondary server instance runs its own restore job to restore the log backup from the local destination folder onto the local secondary database.

The primary and secondary server instances send their own history and status to the monitor server instance.

d.      Interval: default 15 minutes, minimum – 10s
e.      Manual Failover
                                          i.     Tail log backup
                                         ii.     Restore all open backups
                                        iii.     Restore database…with recovery then delete the copy and restore Jobs on the secondary server
                                        iv.     Transfer the logins and passwords to the secondary server (see http://support2.microsoft.com/kb/918992)
                                         v.     Modify the connection string to use the new server
                                        vi.     After repairing the original primary (now secondary), you can use re-establish the log shipping.
f.       Log Shipping Monitoring
                                          i.     Primary server: SSMS|Reports|Standard Report|xLog Shipping Status; Secondary server: SSMS|Reports|Standard Report|xLog Shipping Status (for copy and restore jobs)
                                         ii.     Run EXEC sys.sp_help_log_shipping_monitor from the master database
                                        iii.     Check Job History
SELECT * FROM msdb.dbo.sysjobs WHERE category_id=6
SELECT * FROM msdb..sysjobhistory
SELECT * FROM msdb.dbo.log_shipping_monitor_history_detail
SELECT * FROM msdb.dbo.log_shipping_monitor_error_detail
g.      Trouble Shooting     
                                          i.     Failed copy job
a.       The SQL Server Agent account does not have permission on the shared folder
b.       The shared fold/file does not exist or is moved
c.       The file name has been changed or the timestamp has been messed up
                                         ii.     Failed restore job
a.       The restore job cannot use the log file as it is being used - use Process Explorer to check, antivirus program maybe is scanning it.
b.       Permission issue for the SQL Agent account
c.       Log backup is damaged – re-establish the log shipping
d.       LSN are out of the range, possibly interrupted by the manual log backup. Need to recreate log shipping.
4.      Database Mirroring  - HA (with a witness server) + DR
a.      Overview
                                          i.     数据库镜像功能首次出现在SQL Server 2005 SP1中。它设计的目的是试图为SQL Server提供一个具有实时性数据同步的灾难恢复技术,即能提供数据冗余备份,切换起来也比较方便。相比较故障转移群集,数据库镜像同样也能够为客户端应用提供统一的连接方式,但同时它又具备故障转移群集所没有的抵御数据损失的能力。相对日志传送,它进行灾难切换要快很多,又基本对客户端应用透明。这些优点都使得数据库镜像成为了一项兼备高可用性和灾难恢复功能的技术。现在,数据库镜像技术在企业客户环境中,使用得越来越多了。
                                         ii.     Without a witness
All database mirroring sessions support only one principal server and one mirror server. This configuration is shown in the following illustration.

                                        iii.     With a witness
High-safety mode with automatic failover requires a third server instance, known as a witness. Unlike the two partners, the witness does not serve the database. The witness supports automatic failover by verifying whether the principal server is up and functioning. The mirror server initiates automatic failover only if the mirror and the witness remain connected to each other after both have been disconnected from the principal server.
The following illustration shows a configuration that includes a witness.

                                        iv.     Summary
a.       Primarily a DR solution. Can be used as HA when used with synchronous/automatic failover options.
b.       Protects against I/O subsystem failure on the primary server or datacenter
c.       Log stream compression available for increased performance
d.       Automatic page repair
e.       Automatic failover is possible, requires a 3rd witness server
f.        Automatic redirection to secondary is possible
g.       Can choose between several mirroring and failover options: synchronous or asynchronous streaming, automatic or manual failover
h.       DB Level protection (database RLO)
i.         Considerations:
1.      Database must be using the FULL recovery model
2.      Database on secondary is not writeable
3.      Database on secondary is not readable (can create database snapshots to work around this, but this can quickly become burdensome)
4.      Additional management overhead of third witness server
5.      Deprecated in SQL 2012
6.      Only 1 secondary allowed
7.      Can only failover at database granularity - cannot group a set of databases to failover together
8.      If automatic client redirection is required, the client must change the connection string to include the new FAILOVER_PARTNER option. Also, the client connectivity components must be at a version which supports the new connection string – so may not be suitable for legacy applications.
9.      Database failover occurs at the user database level, not at the server level. Note that the system databases master, tempdb, and model cannot be mirrored and that only one secondary database is supported with database mirroring in SQL Server 2008. Databases have dependencies on objects that reside in other databases such as the master database. These objects often include logins, jobs, certificates, and server audits but can include other objects. A process (normally involving scripts) needs to be adopted to keep objects that are external to the database synchronized across the servers. Suitable for environments where these external dependencies are not changed very often (which tends to be true in most environments).
10.  Some features are not supported, such as cross database transactions.
11.  Special configuration considerations when using Replication (http://msdn.microsoft.com/en-us/library/ms151799.aspx).
b.      How does database mirroring work?

c.      Differences between Log Shipping and Database Mirroring
                                          i.     Log shipping uses backup and restore
                                         ii.     Database mirroring uses the log records, more granular than log shipping thus, more timely.
d.      Operation modes

e.    客户端连接重定向及超时控制
ADO.NETSQL Native Client这两个数据库驱动程序添加一项特性:你可以在连接字符串中指定首选的服务器,同时也可以指定故障切换后的备选数据库。那些使用ADO.NET或者SQL Native Client来连接SQL Server的应用,通过这个特性可以获得自动重定向连接的能力。
"DataSource=SeverA;Failover Partner=ServerB;InitialCatalog=AdventureWorks;Integrated Security=True;"
 如果应用程序尝试连接Data Source指定的主体服务器并且成功了,那么连接字符串中的Failover Partner属性指定的服务器就会被忽略掉。此时驱动程序会从通过主体服务器获得它的故障转移伙伴的名字,把这个名字缓存在应用程序的内存中。要注意的是,这个被缓存的名字会覆盖连接字符串中的failoverpartner属性值。也就是说,即使你在连接字符串中没有指定failover partner,甚至你有意把failover partner设置为一个错误的、非镜像服务器的实例,只要应用程序第一次能成功连接到主体服务器,应用程序就能获得正确的镜像服务器名称。
1    如果成功连接,那么就会读取新的主体服务器的镜像配置信息,得到新的镜像服务器名,并缓存在应用程序的内存里。
2    如果连接失败,那么驱动程序会继续尝试去访问Data Source中指定的首选服务器。驱动程序就会这样不断的尝试这两个服务器,直到达到了连接超时的阈值。
这就是数据库镜像的“连接重试算法”。由于连接重试算法的存在,连接一个镜像数据库的时候,计算“超时”错误的方法也和连接普通的SQL Server数据库不同。当数据库是镜像数据库的时候,你在连接字符串中指定的connectiontimeout值和非镜像数据库的connection timeout值尽管大小一样,但实际上效果是不一样的。
RetryTime = PreviousRetryTime + ( 0.08 * TotalLoginTimeout )
PreviousRetryTime 初始值为0
例如,如果使用默认的登录超时期限 15 秒,则 LoginTimeout = 15。在这种情况下,前三轮中分配的重试时间如下:
RetryTime 计算
+ (0.08 * 15)
1.2 + (0.08 * 15)
2.4 + (0.08 * 15)
3.6 + (0.08 * 15)
 也就是说,对于15秒的timeout值,实际连接服务器的最大timeout值是3.6秒! 如果前面3轮连接(1.2秒,2.4秒,3.6秒)都不成功,在你还没有用尽第四轮的4.8秒时间之前(实际上此时第四轮只剩下0.4秒),15秒超时时间就到了,你的应用程序就会报超时错误。
f.       Troubleshooting
                                          i.     Major problems in DB Mirroring
a.       诊断发生故障转移的原因(对于非高可用模式的镜像,就是诊断镜像停止工作的原因)。
b.       性能问题。
                                         ii.     What to check for troubleshooting?
a.       Check the ErrorLog on the principal, mirror, and witness servers.
b.       Performance Monitor – check the counters for SQL Server: Database Mirroring
1.  On the principal server, check:
·        Transaction Delay - 以确定数据库镜像是否影响主体服务器的性能。
·        Log Send Queue KB - 以确定发送队列的大小。如果send queue很长的话,有可能是镜像数据库处理日志慢导致的,也可能是由于网络传输慢导致的。
·        Log Bytes Sent/sec - 显示了每秒发送的日志量。可以计算“(Log Send Queue)/( Log Bytes Received /sec)”来估算镜像数据库需要多长时间才能追赶上主体数据库。
2.  On the mirroring server, check:
·        Redo Queue KB - 计数器来确定重做队列的大小,就知道有多少日志没有被重做。Log Bytes Received/sec - 显示了每秒接收到的日志量。你可以检查如果Redo queue很大的话,这往往意味着镜像数据库存在I/O瓶颈。
·        Redo Bytes/sec- 显示了执行重做操作的速率。发生故障转移的时候,原始的镜像数据库需要把重做队列里的日志都重做完才能真正上线运行。我们可以通过计算“(RedoQueue)/(Redo Bytes/sec)”来估算一旦发生故障转移,需要多长时间才能完成全部重做操作。
c.       Database Mirroring Monitor
1.      How to start it?
To launch the Database Mirroring Monitor
After connecting to the principal server instance, in Object Explorer, click the server name to expand the server tree.
Expand Databases, and select the database to be monitored.
Right-click the database, select Tasks, and then click Launch Database Mirroring Monitor.
In the Database Mirroring Monitor dialog box, click Register Mirrored Database to register one or more mirrored database.
2.      What can you do with it?
来监视镜像镜像伙伴以及镜像会话的状态,利用这些信息你可以评估在当前状况下完成故障转移所需的时间,和潜在的数据丢失量。你也可以为一些您认为很重要的指标(如send queueredo queue)配置警告阈值,一旦超过你设置的阈值就触发警告来通知数据库管理员。
d.       System stored procedures and system tables
1.      Run sp_dbmmonitorresults - 查看当前数据库镜像的状态。
sp_dbmmonitorresults database_name 
   , rows_to_return 
, update_status

Specifies the database for which to return mirroring status.
Specifies the quantity of rows returned:
0 = Last row
1 = Rows last two hours
2 = Rows last four hours
3 = Rows last eight hours
4 = Rows last day
5 = Rows last two days
6 = Last 100 rows
7 = Last 500 rows
8 = Last 1,000 rows
9 = Last 1,000,000 rows

Specifies that before returning results the procedure:
0 = Does not update the status for the database. The results are computed using just the last two rows, the age of which depends on when the status table was refreshed.
1 = Updates the status for the database by calling sp_dbmmonitorupdate before computing the results. However, if the status table has been updated within the previous 15 seconds, or the user is not a member of the sysadmin fixed server role, sp_dbmmonitorresults runs without updating the status.
2.     sp_dbmmonitorresultsçdbm_monitor_dataçsp_dbmmonitorupdateçsys.dm_os_performance_counters。而sys.dm_os_performance_counters中的数据和性能监视器中SQL Server:Database Mirroring下的计数器是一一对应的。
5.      Replications
a.      Overview
Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Using replication, you can distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet.
Transactional replication is typically used in server-to-server scenarios that require high throughput, including: improving scalability and availability; data warehousing and reporting; integrating data from multiple sites; integrating heterogeneous data; and offloading batch processing. Merge replication is primarily designed for mobile applications or distributed server applications that have possible data conflicts. 

                                          i.     Not designed for disaster recovery or HA
                                         ii.     Mainly focus on data synchronization
                                        iii.     Powerful in data synchronization and merge from many data sources
                                        iv.     It uses the magazine metaphor.
Although the magazine metaphor is useful for understanding replication, it is important to note that SQL Server replication includes functionality that is not represented in this metaphor, particularly the ability for a Subscriber to make updates and for a Publisher to send out incremental changes to the articles in a publication.

A replication topology defines the relationship between servers and copies of data and clarifies the logic that determines how data flows between servers. There are several replication processes (referred to as agents) that are responsible for copying and moving data between the Publisher and Subscribers. The following illustration is an overview of the components and processes involved in replication.

                                         v.     Replication Agents
The Snapshot Agent is typically used with all types of replication. It 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. The Snapshot Agent runs at the Distributor. For more information, see Replication Snapshot Agent.
The Log Reader Agent is used with transactional replication. It moves transactions marked for replication from the transaction log on the Publisher to the distribution database. Each database published using transactional replication has its own Log Reader Agent that runs on the Distributor and connects to the Publisher (the Distributor can be on the same computer as the Publisher). For more information, see Replication Log Reader Agent.
The Distribution Agent is used with snapshot replication and transactional replication. It applies the initial snapshot to the Subscriber and moves transactions held in the distribution database to Subscribers. The Distribution Agent runs at either the Distributor for push subscriptions or at the Subscriber for pull subscriptions. For more information, see Replication Distribution Agent.
The Merge Agent is used with merge replication. It applies the initial snapshot to the Subscriber and moves and reconciles incremental data changes that occur. Each merge subscription has its own Merge Agent that connects to both the Publisher and the Subscriber and updates both. The Merge Agent runs at either the Distributor for push subscriptions or the Subscriber for pull subscriptions. By default, the Merge Agent uploads changes from the Subscriber to the Publisher and then downloads changes from the Publisher to the Subscriber. For more information, see Replication Merge Agent.
The Queue Reader Agent is used with transactional replication with the queued updating option. The agent runs at the Distributor and moves changes made at the Subscriber back to the Publisher. Unlike the Distribution Agent and the Merge Agent, only one instance of the Queue Reader Agent exists to service all Publishers and publications for a given distribution database. For more information about the Queue Reader Agent, see Replication Queue Reader Agent. For more information about updatable subscriptions, see Updatable Subscriptions for Transactional Replication.
b.      Types of replications
                                           i.     Snapshot replication - Snapshot replication is most appropriate when data changes are substantial but infrequent.
By default, all three types of replication use a snapshot to initialize Subscribers. The SQL Server Snapshot Agent always generates the snapshot files, but the agent that delivers the files differs depending on the type of replication being used. Snapshot replication and transactional replication use the Distribution Agent to deliver the files, whereas merge replication uses the SQL Server Merge Agent. The Snapshot Agent runs at the Distributor. The Distribution Agent and Merge Agent run at the Distributor for push subscriptions, or at Subscribers for pull subscriptions (Note: All replication types allow push -推送订阅and pull –请求订阅subscriptions). 

Snapshots can be generated and applied either immediately after the subscription is created or according to a schedule set at the time the publication is created. The Snapshot Agent prepares snapshot files containing the schema and data of published tables and database objects, stores the files in the snapshot folder for the Publisher, and records tracking information in the distribution database on the Distributor. 

                                         ii.     Transactional replication

a.       When to use it?
Transactional replication is typically used in server-to-server environments and is appropriate in each of the following cases:
·        You want incremental changes to be propagated to Subscribers as they occur.
·        The application requires low latency between the time changes are made at the Publisher and the changes arrive at the Subscriber.
·        The application requires access to intermediate data states. For example, if a row changes five times, transactional replication allows an application to respond to each change (such as firing a trigger), not simply the net data change to the row.
·        The Publisher has a very high volume of insert, update, and delete activity.
·        The Publisher or Subscriber is a non-SQL Server database, such as Oracle.
b.       What if you want to update data from the subscriber? – updatable subscription and peer-to-peer subscription
Transactional replication supports updates at Subscribers through updatable subscriptions and peer-to-peer replication. The following are the two types of updatable subscriptions:
·        Immediate updating. The Publisher and Subscriber must be connected to update data at the Subscriber.
·        Queued updating The Publisher and Subscriber do not have to be connected to update data at the Subscriber. Updates can be made while the Subscriber or Publisher is offline.
c.       Peer-to-Peer replications

1.      Primarily a scalability solution
2.      With Peer-To-Peer replication, we have the capability to keep multiple copies of the data on multiple sites, providing a scale-out, HA (if bandwidth permits) and DR solution. Because data is maintained across the nodes, peer-to-peer replication provides data redundancy, which increases the availability of data.
3.      Database can be in Simple recovery model
4.      Database on secondary is both readable and writeable
5.      No automatic redirection
6.      No concept of a failover since all  “nodes” are always “active”
7.      Can have multiple nodes participate in the topology
8.      Can choose to replicate only a subset of tables within the database (table level or filtered rows from table RLO)
9.      Since all databases are writeable, you can create different indexes optimized for reporting environment, optimal solution if you are running very expensive reporting queries which need custom indexes
10.  Considerations:
                                                                                                      i.     Client will have to change connection string to an available node if the node it is connected to becomes unavailable
                                                                                                     ii.     Replication is mainly intended for reporting offloading. Therefore the architecture builds an interim queue in the distribution database, causing higher latencies than would be observed with the Availability Groups architecture.
                                                                                                   iii.     Does not handle conflicts gracefully, application owner will need to ensure data modified at different sites does not conflict (app should logically partition the updates). P2P replication will simply overwrite data (lost updates) unless configured to fail when a conflict occurs, which requires manual intervention (http://technet.microsoft.com/en-us/library/bb934199.aspx).
                                                                                                   iv.     Does not support initialization and re-initialization via a snapshot
                                                                                                     v.     Replicated tables must have a primary key
                                        iii.     Merge replication

a.       When to use it?
Merge replication is typically used in server-to-client environments. Merge replication is appropriate in any of the following situations:
·        Multiple Subscribers might update the same data at various times and propagate those changes to the Publisher and to other Subscribers.
·        Subscribers need to receive data, make changes offline, and later synchronize changes with the Publisher and other Subscribers.
·        Each Subscriber requires a different partition of data.
·        Conflicts might occur and, when they do, you need the ability to detect and resolve them.
·        The application requires net data change rather than access to intermediate data states. For example, if a row changes five times at a Subscriber before it synchronizes with a Publisher, the row will change only once at the Publisher to reflect the net data change (that is, the fifth value). 
b.       How does it work?
1.      Merge replication allows various sites to work autonomously and later merge updates into a single, uniform result. Because updates are made at more than one node, the same data may have been updated by the Publisher and by more than one Subscriber. Therefore, conflicts can occur when updates are merged and merge replication provides a number of ways to handle conflicts.

2.      Merge replication is implemented by the SQL Server Snapshot Agent and Merge Agent. If the publication is unfiltered or uses static filters, the Snapshot Agent creates a single snapshot. If the publication uses parameterized filters, the Snapshot Agent creates a snapshot for each partition of data. The Merge Agent applies the initial snapshots to the Subscribers. It also merges incremental data changes that occurred at the Publisher or Subscribers after the initial snapshot was created, and detects and resolves any conflicts according to rules you configure.

3.      To track changes, merge replication (and transactional replication with queued updating subscriptions) must be able to uniquely identify every row in every published table. To accomplish this merge replication adds the column rowguid to every table, unless the table already has a column of data type uniqueidentifier with the ROWGUIDCOL property set (in which case this column is used). If the table is dropped from the publication, the rowguid column is removed; if an existing column was used for tracking, the column is not removed. A filter must not include the rowguidcol used by replication to identify rows. The newid() function is provided as a default for the rowguid column, however customers can provide a guid for each row if needed. However, do not provide value 00000000-0000-0000-0000-000000000000.

                                        iv.     Summary
不管是那种类型,它们最终的目的都是把数据从发布服务器同步到订阅服务器。但是每种类型的复制跟踪数据更改的方式都不同。快照复制不会跟踪快照生成后任何的数据更改,因此要同步数据变更就需要每次都把新的快照应用订阅服务器上,完全覆盖现有数据。事务复制通过SQL Server的 事务日志跟踪更改,复制以事务为单位将更改发送到订阅服务器。而合并复制则通过触发器和系统表来跟踪数据和架构更改。一般情况下,数据更改都发生在发布服务器上,订阅服务器上不应有数据更改;但是合并复制、对等事务复制以及可更新订阅的事物复制允许在订阅服务器上进行更改,并使这些更改流向发布服务器。
                                         v.     Replication and Disaster Recovery



                                        vi.     Disaster Recovery Options
a.       Failover Clustering
You have high server availability. Failover clustering automatically occurs if the primary server fails.
·        You incur a greater expense. The maintenance of two servers is two times the cost of maintaining a single server. Because you have to maintain two servers at the same time, it is more expensive to install and maintain clustered nodes.
·        Servers should be in the same location. If the branches of the organization are across the globe and the Active/Active clusters must be implemented in the branches, the networking and the storage infrastructure that you have to use is very different from a standard quorum device server cluster. Therefore, although it is possible, it is best not to use geographically distant servers.
·        You have no protection against a disk array failure.
·        Failover clustering does not allow you to create failover clusters at the database level or at the database object level, such as the table level.

b.       Database Mirroring
·        Database mirroring increases data protection.
·        Database mirroring increases availability of a database.
·        Database mirroring improves the availability of the production database during upgrades.
·        The mirror database should be identical to the principal database. For example, all objects, logins, and permissions should be identical.
·        Database mirroring involves the transfer of information from one computer to another computer over a network. Therefore, the security of the information that SQL Server transfers is very important.

c.       Peer-to Peer Replications - designed for applications that might read or might modify the data in any database that participates in replication. 
·        Read performance is improved because you can spread activity across all nodes.
·        Aggregate update performance, insert performance, and delete performance for the topology resembles the performance of a single node because all changes are propagated to all nodes.
·        Peer-to-peer replication is available only in SQL Server 2005 Enterprise Edition.
·        All participating databases must contain identical schemas and data.
·        We recommend that each node use its own distribution database. This configuration eliminates the potential for SQL Server 2005 to have a single point of failure.
·        You cannot include tables and other objects in multiple peer-to-peer publications within a single publication database.
·        You must have a publication enabled for peer-to-peer replication before you create any subscriptions.
·        You must initialize subscriptions by using a backup or by setting the value of the subscription synchronization type toreplication support only.
·        Peer-to-peer transactional replication does not provide conflict detection or conflict resolution.
·        We recommend that you do not use identity columns.
d.       Maintenance of a warm standby server
You can create and maintain a warm standby server by using either of the following methods:
·        Log shipping
·        Transactional replication

More information about each of these two methods follows.
1.      Log Shipping
·        You can recover all database activities. The recovery includes any objects that were created such as tables and views. It also includes security changes such as the new users who were created and any permission changes.
·        You can restore the database faster. The restoration of the database and the transaction log is based on low-level page formats. Therefore, log shipping speeds up the restoration process and results in the fast recovery of data.
·        The database is unusable during the restoration process because the database is in exclusive mode on the standby server.
·        There is a lack of granularity. During the restoration process, all the changes in the primary server are applied at the standby server. You cannot use log shipping to apply changes to a few tables and to reject the remaining changes.
·        There is no automatic failover of applications. When the primary server fails because of a disaster, the standby server does not failover automatically. Therefore, you must explicitly redirect the applications that connect to the primary server to the standby (failover) server.

Note If your main purpose is to maintain a warm standby server, Microsoft recommends that you use log shipping. The warm standby server reflects all the transactions that occur on the primary server. However, you cannot use the standby server when the primary server is available. 
2.      Transactional replications
You can also use transactional replication to maintain a warm standby server. You should use transactional replication to maintain a warm standby server only when you do not implement schema changes or you do not implement other changes to the database such as security changes that replication does not support. 

Note Replication is not designed for the maintenance of warm standby servers. With replication, you can use replicated data at the subscriber to generate reports. You can also use replication for other general uses without having to perform processing on the relatively busy publisher.

Advantages and disadvantages of using transactional replication

·        You can read data on a subscriber while you apply changes.
·        Changes are applied with less latency.

Note This advantage may not be applicable if either of the following is true:
o   Replication agents are not set to Continuous.
o   Replication agents are stopped because of errors that may occur during replication.

Transactional replication may take more time to apply changes because large batch updates must be performed during the replication.
·        Schema changes or security changes that are performed at the publisher after establishing replication will not be available at the subscriber.
·        The distributor in transactional replication uses an Open Database Connectivity (ODBC) connection or an OLE Database (OLEDB) connection to distribute data. However, log shipping uses the RESTORE TRANSACTION low-level Transact-SQL statement to distribute the transaction logs. A RESTORE TRANSACTION statement is much faster than an ODBC connection or an OLEDB connection.
·        Typically, switching servers erases replication configurations. Therefore, you have to configure replication two times:
When you switch to the subscriber.
When you switch back to the publisher.
·        If a disaster occurs, you must manually switch servers by redirecting all the applications to the subscriber.
3.      Backup and restore
·        You can back the database up to removable media to help protect against disk failures.
·        You do not have to depend on the network as you do when you use failover clustering or log shipping.
·        When you back up the database, you cannot perform operations such as table creation, index creation, database shrinking, or non-logged operations.
·        If a failure occurs, you may lose your most recent data.
·        If a disaster occurs, you must manually restore the database.
4.      RAID
·        You do not lose data if any one disk fails.
·        It may take a long time to recover the data.
·        If multiple disks fail, you may not be able to recover valuable data.

                                      vii.     High Availability Options
a.       Failover clustering
Failover clustering and database mirroring both provide the following:
·        Automatic detection and failover
·        Manual failover
·        Transparent client redirect
Failover clustering has the following constraints:
·        Operates at the server instance scope
·        Requires signed hardware
·        Has no reporting on standby
·        Utilizes a single copy of the database
·        Does not protect against disk failure
b.       Database mirroring
·      Operates at the database scope.
·      Uses a single, duplicate copy of the database
·      Uses standard servers
·      Provides limited reporting on the mirror server by using database snapshots.
·      When it operates synchronously, provides for zero work loss through delayed commit on the principal database.
·      Database mirroring offers a substantive increase in availability over the level previously possible with SQL Server and offers an easy-to-manage alternative to failover clustering.
c.       Log shipping
Log shipping can be a supplement or an alternative to database mirroring. Although similar in concept, asynchronous database mirroring and log shipping have key differences. Log shipping offers the following distinct capabilities:
·        Supports multiple secondary databases on multiple server instances for a single primary database.
·        Allows a user-specified delay between when the primary server backs up the log of the primary database and when the secondary servers must restore the log backup. A longer delay can be useful, for example, if data is accidentally changed on the primary database. If the accidental change is noticed quickly, a delay can let you retrieve still unchanged data from a secondary database before the change is reflected there.
Asynchronous database mirroring has the potential advantage over log shipping of a shorter time between when a given change is made in the primary database and when that change is reflected to the mirror database.
An advantage of database mirroring over log shipping is that high-safety mode is a no data loss configuration that is supported as a simple failover strategy.

d.       Replication
Replication offers the following benefits:
·        Allows filtering in the database to provide a subset of data at the secondary databases because it operates at the database scope
·        Allows more than one redundant copy of the database
·        Allows real-time availability and scalability across multiple databases, supporting partitioned updates
·        Allows complete availability of the secondary databases for reporting or other functions, without query recovery.

c.      Summary





约等于SQL Server服务重启的时间+数据库恢复时间







SQL Server 2000及以后
SQL Server 2000及以后
SQL Server 2005及以后
SQL Server 2000及以后

6.      Combinations of HA and DR
a.     Failover Clustering + Log Shipping
                                          i.     这个组合是在数据库镜像出现之前最常见的方案。
                                         ii.     整个方案需要至少3台服务器。两台服务器用于实现故障转移群集。故障转移群集实例需要被配置为日志传送的主服务器,而第三台服务器作为日志传送的辅助服务器维护一个冗余的副本数据库。一旦故障转移群集的数据库出了问题,你有两个选择:
a.       将第三台服务器(日志传送的辅助服务器)上的数据库恢复使其上线。将应用程序重定向到这台服务器上。如果故障转移群集上数据库的问题是由于磁盘问题导致的,短期内很难解决,这个选择会比较合适。这个选择需要你在发生故障转移后将应用程序重定向到新的主服务器上。
b.       将第三台服务器上的数据库复制到故障转移群集实例上,替代损坏的数据库上线。这个方案适用于仅是数据库文件损坏而磁盘系统正常的情况。虽然复制数据库可能需要花费一定的时间,但是好处是不用重定向应用程序,而且问题解决后你的SQL Server依旧拥有群集提供的高可用性。
                                        iii.     另外要注意的是,一旦做过日志传送的故障转移,日志传送就被破坏了。因此无论你的选择上面哪种办法,都要在完成灾难恢复后重新配置日志传送。
b.     Failover Clustering + Database Mirroring
1.  两台服务器的方案。两个服务器配置成一个群集,在该群集上安装两个SQL Server实例,分别运行在两个节点,即所谓的活跃/活跃模式。而镜像的主体服务器在其中一个实例上运行,镜像服务器在另一个实例中运行。这个方案虽然很省硬件但是风险也很大。一旦某一个群集节点失败,就会造成镜像的主体服务器和镜像服务器运行在同一个节点上,该节点的硬件资源很可能捉襟见肘,严重影响主体服务器的性能。另外,主体服务器和镜像服务器作为一个Windows群集上的两个实例,意味它们其实是共用一个硬件存储器的。一旦这个存储器出了问题,那么主体服务器和镜像服务器上的两个数据副本就都不可用了,灾难恢复和高可用也无从谈起。
2.  三个服务器的方案。这个方案中,故障转移群集作为主体服务器,而镜像服务器驻留在一个单独的非群集的服务器上。
3.  四台服务器的方案。四台服务器分别组成两个故障转移群集,一个群集作为镜像的主体服务器实例,另一个群集作为镜像的镜像服务器实例。最佳情况下,这两个群集使用两个独立的存储器,避免存储区损坏导致整个方案失败的可能性。
数据库镜像的故障转移是可逆的,因此在解决故障后可以比较简单地将一切都恢复到出问题前的样子。另外,对于使用ADO.NETSQL Native Client的应用,无论是使用哪种方案,无论是镜像还是群集发生了故障转移,都不需要重定向连接。
·        高可用操作模式
假设现在有两个群集:ClusterAClusterB,还有另一台非群集的服务器ServerC作为见证服务器 (6 Servers!!! See below)ClusterA上的SQL Server实例作为主体服务器,而ClusterB上的SQL Server实例作为镜像服务器。如果ClusterA的活跃节点失败,将在几秒钟内先开始进行数据库镜像自动故障转移,同时群集也会开始启动故障转移。数据库镜像的故障转移完成后, ClusterB就从镜像服务器变为了主体服务器。在ClusterA群集故障转移完成(通常需要几分钟)之后,ClusterASQL Server就被切换到另一个节点,并且该实例变为了镜像服务器。

The following illustration shows an automatic failover between clusters in a mirroring session running in high-safety mode with a witness (which supports automatic failover).

(1)  镜像的故障转移速度比群集更快,因此停止时间更短。
(2)  一旦在短时间内ClusterB也出了问题,而原来ClusterA的活跃节点上的故障还没来得及解决,SQL Server还能切换到ClusterA上新的活跃节点继续工作。如果是单机环境的数据库镜像就无法实现这点。 
·        高保护操作模式
·        高性能操作模式
c.      Failover Clustering + Database Mirroring + Log Shipping
1.  你的主机房设在城市A,在主机房内,你选择使用故障转移群集+数据库镜像的第三套方案,即双群集和数据库镜像的方案,这样可以提供最好的高可用性。
2.  由于双群集都在一个机房里,当面临一些不可抗的因素,比如全市范围的停电,整个SQLServer系统还是会受到影响。为了进一步提高可用性和抵御灾难的能力,你可以为数据库镜像的主体数据库配置日志传送。主体服务器作为日志传送的主服务器,然后为其配置一个或多个远程的辅助服务器。你可以考虑配置两个远程的辅助服务器,一个设立在离城市A比较近的城市B,另一个设立在离城市A很远的城市C。这样你的数据在面对一些自然灾害时也有了一定的抵御能力。
3.  若要使日志传送在数据库镜像故障转移后仍能继续进行,还必须使用相同的配置将镜像服务器也配置为日志传送的主服务器(使用同样的备份共享目录,同样辅助服务器等)。由于镜像数据库平时处于还原状态,这样可以防止备份作业备份镜像数据库中的日志。这确保了镜像数据库配置的日志传送不会影响主体数据库的日志传送。
4.  如果数据库镜像进行故障转移,以前的镜像数据库将作为主体数据库并联机。此时,该数据库也将作为日志传送主数据库进入活动状态。以前无法在该数据库中完成的备份作业也将开始执行备份并传送日志。相反,故障转移将使以前的主体数据库进入还原状态,同时该数据库上日志传送的备份作业也将停止备份日志。